Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

komplizierte Excelformel

komplizierte Excelformel
14.01.2015 09:10:40
Pampel

Hallo zusammen,
ich möchte einen Kalender resalisieren, in den bestimmte Zeiträume und einzelne definierte Tage automatisch eingetragen werden.
In C21 steht ein Datumswert, in C22 der Wert von C21 + 1 usw.
In U8 bis U13 stehen der Anfang und in V8 bis V13 das Ende von Zeiträumen, die in W8 bis W13 beschrieben werden:
02.11.15 06.11.15 Schulung
23.12.15 08.01.16 Weihnachten
08.02.16 12.02.16 Urlaub
24.03.16 01.04.16 Seminar
17.05.16 27.05.16 Urlaub
28.07.16 04.08.16 Messe
In V17 bis V24 stehen einzelne Datumswerte, in W17 bis W24 wieder die Beschreibungen:
14.09.15 frei 1
03.10.15 Tag d. Dt. Einheit
08.02.16 Rosenmontag
01.05.16 1. Mai
05.05.16 Chr. Himmelfahrt
26.05.16 Fronleichnam
05.02.16 frei 2
06.05.16 frei 3
In D21 soll nun untersucht werden, ob der Wert von C21 entweder in einem der Zeitbereiche liegt (die angegebenen Anfangs- und Enddatumswerte zählen dabei mit!) oder aber auf einen der angegebenen Einzeltagen.
Wie kann ich das mit einer Formel hinbekommen? Danke für Hinweise.
Gruß,
Stefan

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: komplizierte Excelformel
14.01.2015 15:20:38
fcs
Hallo Stefan,
Formel in D21:
=WENN(NICHT(ISTFEHLER(VERGLEICH(C21;$V$17:$V$24;0)));"ET";
WENN(SUMMENPRODUKT((C21>=$U$8:$U$13)*(C21<=$V$8:$V$13)*1)>0;"ZB";""))
Gruß
Franz

nachgefragt ...
14.01.2015 18:51:46
neopa C
Hallo Franz,
... woher hast Du Dein Ergebnisdefinition "ET" und "ZB" bzw. was bedeuten diese bei Dir.
Ich hab die Daten jetzt nicht nachgestellt, doch aufgefallen ist mir, das die Multiplikation mit 1 nicht notwendig wäre und anstelle NICHT(ISTFEHLER(...)) würde ich ISTZAHL() nutzen.
Gruß Werner
.. , - ...

AW: nachgefragt ...
14.01.2015 19:50:26
fcs
Hallo Werner,
"ET" und "ZB" sind meine Erfindung für Einzeltag und Zeitbereich. Hier kann man natürlich jeden beliebigen Wert ausgeben.
Die Multiplikation mit 1 ist bei mir wahrscheinlich ein Relikt aus vergangenen Zeiten.
NICHT(ISTFEHLER(...)) oder ISTZAHL() ist bei mir wahrscheinlich auch historisch bedingt. Während über 20 Jahren arbeiten mit Excel hat sich zwar das eine oder andere geändert (oft auch verbessert), aber man hängt doch oft an alten Gewohnheiten und stellt nur um, weil es jetzt schnellere/bessere Funktionen gibt.
Gruß
Franz

Anzeige
jetzt ist es klarer ...
15.01.2015 09:51:46
neopa C
Hallo Franz,
... danke für Deine Antworten.
Gruß Werner
.. , - ...

AW: komplizierte Excelformel
15.01.2015 01:02:44
pampel
Hallo Franz,
vielen Dank für Deinen Ansatz. Das mit ET und ZB als Ausgabe habe auch hinbekommen, wenngleich nicht so elegant.
Statt ET / ZB möchte ich jedoch hinter dem Datum in D21, D22, ... die Beschreibung stehen haben (Herbst, Fastnacht oder Chr. Himmelfahrt.
Meinst Du, das geht auch?
Gruß,
Stefan

AW: komplizierte Excelformel
15.01.2015 09:33:49
fcs
Hallo Stefan,
in Excel 2007 und neuer geht's mit folgender Formel:
= WENNFEHLER(WENN(SUMMENPRODUKT((C21>=$U$8:$U$13)*(C21<=$V$8:$V$13)*1)>0;
INDEX($W$8:$W$13;VERGLEICH(C21;$U$8:$U$13;1));"");"") &
WENNFEHLER(INDEX($W$17:$W$24;VERGLEICH(C21;$V$17:$V$24;0));"")
Soll die Formel auch in älteren Excelversionen funktionieren, dann wird es komplizierter, da es die Funktion WENNFEHLER früher noch nicht gab.
= WENN(ISTFEHLER(INDEX($W$8:$W$13;VERGLEICH(C21;$U$8:$U$13;1)));"";
WENN(SUMMENPRODUKT((C21>=$U$8:$U$13)*(C21<=$V$8:$V$13)*1)>0;
INDEX($W$8:$W$13;VERGLEICH(C21;$U$8:$U$13;1));"")) &
WENN(ISTFEHLER(VERGLEICH(C21;$V$17:$V$24;0));"";
INDEX($W$17:$W$24;VERGLEICH(C21;$V$17:$V$24;0)))
Gruß
Franz

Anzeige
AW: komplizierte Excelformel
16.01.2015 08:55:43
pampel
Hallo Franz,
vielen Dank. Mit INDEX und VERGLEICH kenn ich mich leider noch nocht so gut aus. Kannst Du (wirklich nur kurz) mal beschreiben, wie Du die Formel zusammengebaut hast, oder setzt Du Dich hin und schreibst drauf los?
Eine Kleinigkeit doch noch: wenn ein einzelner Tag (z.B. Chr. Himmelfahrt) in einem Zeitbereich (z.B. Urlaub) liegt, dann kommt bei dem betreffenden Tag "UrlaubChr. Himmelfahrt" als Rückgabe Deiner Formel, da Du beide Ausgaben mit "&" verknüpft hast. Kann man das auch trennen (je nachdem, was einfacher geht), dass entweder der einzelne Tag vom Zeitbereich "überschrieben" wird (also nur Urlaub da steht), oder aber, dass *nur* der einzelne Tag ausgegeben wird wie
Urlaub
Urlaub
Chr. Himmelfahrt
Urlaub
Urlaub
Vielen Dank für weitere Hinweise. Für Excel 2003 usw. brauche ich diese Formel nicht.
Gruß,
Stefan

Anzeige
AW: komplizierte Excelformel
16.01.2015 10:16:40
fcs
Hallo Stefan,
nach über 20 Jahren Arbeiten mit Excel weiss ich so langsam welche Funktionen oder Kombination von Funktionen ich verwenden kann, um zum Ziel zu kommen. Oft kann ich dann "einfach drauf los schreiben". In deinem Fall ist es aber schon etwas komplizierter, die erforderlichen Prüfungen einzubauen.
Bezüglich INDEX und VERGLEICH solltest du dir mal die Hilfe zu diesen Funktionen ansehen. Viel mehr kann ich dir dazu auch nicht schreiben. Per Vergleich wird hier hier immer die Zeile innerhalb des Zellbereichs ermittelt in der der gesuchte Wert steht.
DIe Formel kann man noch erweitern, so dass bei Doppeltreffern jeweils nur ein Wert ausgegeben wird. Hier der Einzeltag.
= WENNFEHLER(WENN(SUMMENPRODUKT((C21>=$U$8:$U$13)*(C21<=$V$8:$V$13)*1)>0;
WENN(WENNFEHLER(INDEX($W$17:$W$24;VERGLEICH(C21;$V$17:$V$24;0));"")<>"";"";
INDEX($W$8:$W$13;VERGLEICH(C21;$U$8:$U$13;1)));"");"") &
WENNFEHLER(INDEX($W$17:$W$24;VERGLEICH(C21;$V$17:$V$24;0));"")
Das kann man natürlich auch anders herum aufbauen.
Gruß
Franz

Anzeige
vielleicht geht ja noch was anderes ...
16.01.2015 12:06:04
neopa C
Hallo Franz,
... ich würde es mir gern auch mal anschauen (am (späteren Nachmittag). Doch bin ich zu faul, mir die Tabelle nachzubauen. Kannst Du diese mal hier einstellen/hochladen?
Gruß Werner
.. , - ...

AW: Datei - Verweis Zeitbereiche, Einzeltage
16.01.2015 12:51:45
fcs
Hallo Werner,
hier meine Testdatei.
https://www.herber.de/bbs/user/95047.xlsx
Ich hab auch noch eine etwas einfacher strukturierte Formel eingebaut.
Gruß
Franz

günstiger mit SVERWEIS() und VERWEIS() und ...
16.01.2015 17:07:38
neopa C
Hallo Franz,
... die "Abfrage" umgestellt.
Für Excel vor Version 2007:
=WENN(ISTZAHL(VERGLEICH(C21;V$17:V$24;));SVERWEIS(C21;V$17:W$24;2;);WENN(ISTFEHLER(VERWEIS(9; 1/(U$1:U$13<=C21)/(V$1:V$13>=C21)));"";VERWEIS(9;1/(U$1:U$13<=C21)/(V$1:V$13>=C21);W:W)))
und natürlich noch viel kürzer für Excel ab Version 2007
=WENNFEHLER(SVERWEIS(C21;V$17:W$24;2;);WENNFEHLER(VERWEIS(9;1/(U$1:U$13<=C21)/(V$1:V$13>=C21);W:W);""))
Gruß Werner
.. , - ...

Anzeige
AW: günstiger mit SVERWEIS() und VERWEIS() und ...
18.01.2015 22:48:00
pampel
Hallo an alle,
war am Wochenende viel unterwegs, vielen Dank an alle, die Hinweise gegeben haben.
Viele Grüße,
Stefan

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige