Auswertung mit mehreren Kriterien und Verschachtelungen

[StellarMonkey]

Hallo Zusammen,

ich steh total auf dem Schlauch. Hab schon einige Funktionen ausprobiert, aber da das ganze recht verschachteltet ist, kriege ich einfach nicht die richtige Formel hin, die mich zum Ergebnis führt. Vielleicht habt ihr einen Tipp für mich, welche Funktionen ich verwenden muss und wie diese verschaltet sein müssen.
Ich möchte eine Auswertung einer Hotline machen. Hierzu habe ich eine Exceldatei mit zwei Tabellen (Siehe Screenshot im Anhang: Tabelle1 - lila / Tabelle2 - türkis). 
Tabelle1 wurde mir zu Verfügung gestellt. Hier habe ich zusätzlich die Spalten "Wochentage" und "Schicht" hinzugefügt. In der Tabelle sind die eingehenden und angenommenen Anrufe innerhalb eines halbstündig getakteten Zeitfensters aufgelistet.
In der Spalte "Wochentage" habe ich lediglich mit der Formel =TEXT([@Datum];"TTTT") aus dem Datum in der vorangehenden Spalte den entsprechenden Wochentag eintragen lassen um diese dann mit der Spalte "Wochentage" in Tabelle2 zu vergleichen. In der Spalte "Schicht" soll per Formel eingetragen werden, ob es sich bei dem Tag und der Uhrzeit um die Früh-, Kern- oder Spätschicht handelt. Und diese Formel bekomme ich einfach nicht hin...

In Tabelle2 sind die Hotline-Schichten definiert. Leider sind die Schichten nicht jeden Tag gleich. Montags geht die Frühschicht z.B. von 6:30 bis 8:00, die Kernschicht von 8:00 bis 16:00 und die Spätschicht von 16:00 bis 18:30.
Mittwochs hingegen sind die Schichten wie folgt aufgeteilt: Früh: 6:30 bis 8:00; Kern: 8:00 bis 15:00; Spät: 15:00 bis 18:30.
(Die Frühschichten sind immer gleich, die Kern- und Spätschichten unterscheiden sich je nach Wochentag.)
Ich habe in diese Tabelle die Endzeitpunkte der jeweiligen Schicht eingetragen, damit ich später mit "<" oder ">=" (kleiner oder größer-gleich) arbeiten kann.

Ich brauche also eine Formel, die als erstes Kriterium den Wochentag in Tabelle1 nimmt, mit Spalte "Wochentag" in Tabelle2 vergleicht und die entsprechende Zeile in Tabelle2 findet. Als zweites Kriterium muss die Spalte "von" in Tabelle1 herhalten. Wenn dieser Wert kleiner als der Wert in Tabelle2 Spalte "Früh" ist, soll als Text "Früh" ausgegeben werden. Wenn der Wert größer-gleich dem Wert in "Früh" ist, aber kleiner als der Wert in "Kern" soll der Text "Kern" ausgegeben werden. Ansonsten soll der Text "Spät" ausgegeben werden.

Die Werte brauche ich um dann später die Anzahl der Anrufe zur jeweiligen Schicht auszuwerten. Aber das sollte ich dann (hoffentlich) hinbekommen.

Ich hatte es schon mit verschachtelten =WENN(UND(SVERWEIS und anderen Funktionen probiert.

Hat jemand eine Idee?

Schon einmal vielen Dank für die Hilfe 

[BoskoBiati]

Hi,

mit Bildern kann man nicht viel anfangen. Ich baue auch sowas nicht nach. MUSTERTABELLE!

[StellarMonkey]

Hallo Edgar,

im Anhang eine Mustertabelle, die eine Woche umfasst.

Danke und Grüße

[Sigi.21]

Hallo Julien,

s. Mappe.

Gruß Sigi

[StellarMonkey]

Hallo Sigi,

vielen Dank! Noch ein bisschen auf meine aktive Mappe angepasst und es hat super geklappt. 
Ich hatte es auch mit WAHL(WOCHENTAG([@Datum];2)... versucht, aber meine Tabelle zum Abgleich war so wie die in die Tabelle2 (türkis) in der Musterdatei nur statt dem Namen des Wochentags die entsprechende Nummer. Weiß jetzt nicht genau, ob es nicht geklappt hat, weil ich die Tage senkrecht hatte und die Schichten waagerecht (also genau andersrum wie du), aber egal. Hauptsache es funktioniert jetzt.

Grüße

Julien

[BoskoBiati]

Hi,

wenn Du die Uhrzeiten in B und C in echte Zahlenwerte umwandeln würdest, dann würde diese Formel reichen:

Code:

=WENN(C3<1/3;"Früh";WENN(C3>=INDEX($K$3:$K$7;VERGLEICH(B3;$I$3:$I$7;0));"Spät";"Kern"))

Die Datei von SIgi weist für mein Dafürhalten doch einige Fehler auf, in folgenden Zeilen:

5
21
26
29
45
50
53
67
74
77
96
98
101
110
122


  Mustertabelle.xlsx (Größe: 17,54 KB / Downloads: 4)

[StellarMonkey]

Hallo Edgar,

auch dir ein Dankeschön.
Ich hab mir die Tabelle von Sigi angeschaut und kann keine Fehler entdecken. Hast du die Zeilen-Nummer in seiner Datei mit der in deiner verglichen? Er hat nämlich die oberste Zeile entfernt. D.h. Spalte 5 in seiner Mappe ist bei dir die Spalte 6. Vielleicht kam daher daher dein "Fehlerbericht"? 

Bei Sigi habe ich auch den Vorteil, dass ich die Zeiten in der Tabelle in der die Schichten definiert werden einfach anpassen kann.
Die Auswertung soll nämlich unter anderem auch dafür gemacht werden um zu schauen, ob die bisherigen Zeiten und Schichten so beibehalten werden sollen. Wenn wir an den Schichten was ändern und ich wieder eine Auswertung machen soll, müsste ich bei Sigis Mappe einfach nur die neuen Werte in I2:M4 eintragen. Das würde bei deinem Beispiel nicht klappen.

Grüße