Sverweise mit Zeiträumen

[hullen]

Hallo, liebe Wissenden,

in meiner Tabelle sind einzelne unterrichtsfreie Tage sowie Beginn und Ende von unterrichtsfreien Zeiträumen (Ferien, Klassenfahrt) aufgelistet.

Nun möchte ich an anderer Stelle ein Datum eingeben, welches mit den unterrichtsfreien Zeiten abgeglichen wird. Wenn frei ist, soll im Nachbarfeld der entsprechende Text (z.B. Klassenfahrt, Fronleichnam etc.) angegeben werden.

Wie geht das?

Ich kann mit =summenprodukt herausfinden, ob der Termin in der Liste irgendwo auftaucht - aber wie bastel ich das mit dem Sverweis zusammen?

Anbei die Datei zur Veranschaulichung:

Zeitraum mit sverweis.ods

(67.43 KiB) 281-mal heruntergeladen

Ich danke für jeden sachdienlichen Hinweis!

Liebe Grüße
hullen

[HeritzP]

Hallo,

er genügt die SVERWEIS() Funktion. Der Suchbegriff muss halt vorhanden sein.(05.06.14 Fronleichnam)

Code: Alles auswählen

=SVERWEIS(F18;B$3:D$45;3;0)

Gruß HeritzP

[mikele]

Hallo,

um die Fehlermeldung abzufangen, falls das Datum in der Ferienliste nicht existiert:

Code: Alles auswählen

=WENN(ISTFEHLER(SVERWEIS(F18;B$3:D$45;3;0));"";SVERWEIS(F18;B$3:D$45;3;0))

und dann die Formel nach unten ziehen.

[hullen]

Danke für die Hinweise, aber noch funktioniert es nicht.
Der bloße sverweis hilft nur bei eintägigen Ereignissen, aber nicht bei der Fahrtenwoche (oder bei anderen Ferien):
In meiner Beispiel-Tabelle wird nur der 1. Tag der Fahrtenwoche als "Fahrtenwoche" gebrandmarkt, nicht aber die weiteren Tage dieser Woche.

Wie könnte das gemacht werden?

lg hahu

[mikele]

Hallo,

anbei eine Lösung.
In G18:

Code: Alles auswählen

=VERSCHIEBUNG($D$2;SUMMENPRODUKT($B$3:$B$45<=F18;WENN($C$3:$C$45="";$B$3:$B$45;$C$3:$C$45)>=F18;ZEILE($C$3:$C$45)-2);0)

Damit bei den Tagen an denen kein Ereignis vorliegt keine Null erscheint, habe ich der Zelle D2 den Wert "" zugewiesen.

[hullen]

Hallo Mikele,
toll, funktioniert wunderbar.

Ich versuche noch, deine Formel nachvollziehen zu können:

=VERSCHIEBUNG($D$2;

Ok, von D2 soll die Verschieberei losgehen

SUMMENPRODUKT

Das bedeutet, alle diese Bedingungen müssen gemeinsam eintreten, richtig?

($B$3:$B$45<=F18;

Klar. Der Beginn eines Zeitraums ist kleiner/gleich dem gesuchten Termin.

WENN($C$3:$C$45="";$B$3:$B$45;$C$3:$C$45)>=F18;

Auch nachvollziehbar.

ZEILE($C$3:$C$45)-2)

Den Teil verstehe ich nicht mehr. Hier wird doch zuerst angegeben, um wie viel Zeilen von D2 aus verschoben wird. Zeile(c3:c45) ist aber doch immer 3, oder? Woher weiß der Rechner, um welche Zeile genau es aus dem Bereich gehen soll?

;0)

Klar, Spalten, Höhe oder Breite bleiben unverändert.

Liebe Grüße und nochmal ein dickes Dankeschön!
Hullen

[mikele]

Hallo,
das ganze funktioniert praktisch als Matrixformel. Es wird nacheinander mit den Zellen c3 bis c45 gearbeitet, d. h. Zeile (C3:C45) liefert die Werte 3 bis 45.