Seite 1 von 1

Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 01:46
von 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

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 09:23
von 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

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 14:35
von 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.

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 16:09
von 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

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 17:51
von 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.

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 22:31
von 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

Re: Sverweise mit Zeiträumen

Verfasst: So 17. Aug 2014, 22:40
von 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.