Komplizierte (?) Auswertung

[umi_1]

Hallo zusammen,

ich habe Probleme mit folgender Auswertungs-Anforderung:

Wir erhalten eine Excel Tabelle aus unserer Telefonanlage (Rohdatei für 3 Tage als Beispiel ist beigefügt).
Wir wollen wissen, wie viele Personen zu einer Uhrzeit X angemeldet waren, um ggf. unsere Erreichbarkeit zu optimieren.
Die Schwierigkeit ist, dass sich die Personen variabel ein- und ausloggen, z.B. wenn sie eine kurze Pause machen.
D.h. Teilnehmer A kann z.B. 9 Einträge haben, Teilnehmer B nur 6. Das kann sich natürlich auch jeden Tag ändern.

Zielmatrix:
                   08:01 08:02 08:03 08:04 08:05 08:06 08:07 08:08 08:09 usw.
Teilnehmer A    x        x        x       x       x       x       x
Teilnehmer B                                x      x        x       x


Aus den Rohdaten soll also je Eintrag, den Teilnehmer X zu einer Zeit Y eingeloggt war, eine Matrix mit Uhrzeiten und "X für eingeloggt" entstehen.

Bin dankbar für Ideen & Tipps.

VG, Ulrike

[steve1da]

Hola,
das geht zB mit Summenprodukt.
Aber: warum ist die Zielmatrix nicht in der Tabelle? Wo soll die genau stehen? Wo taucht in deiner Zielmatrix das Datum auf?
Gruß,
steve1da

[slowboarder]

im prinzip kannst du hier einfach mit ZählenWenns auswerten.
mehr als 1 und 0 sollte nicht rauskommen.

unter der Annahme, dass in Spalte A ab Zeile 2 die Namen aufgelistet sind und  in Zeile 1 ab Spalte B das Datum+Uhrzeit steht, 

dann wäre es ein einfach ZählenWenns:

=ZählenWenns(Sheet1!$A:$A;$A2;Sheet1!$E:$E;"<="&B$1;Sheet1!$F:$F;">="&B$1)

beachte, die Zelle, die du hier verwendest, muss Datum und Uhrzeit enthalten, so wie die Spalte der Quelldatei.
am besten setzt du hier  nicht die ganze Minute an, sondern immer +30 Sekunden. 
dann passt es besser, wie gezählt wird (falls das relevant ist).

Gruß Daniel

[EA1950]

Hallo,

da du Excel2021 hast, müsste folgende Formel funktionieren:
=PIVOTMIT(A2:B118;TEXT(--E2:E118;"TT.MM. hh:mm");RUNDEN((F2:F118-E2:E118)*24*3600;0);SUMME)

Die Formel ist in Zelle P2 enthalten (mit Resultatbereich P2:EA11).

Damit erhältst du eine Aufstellung der Gesprächsdauern nach Sekunden, summiert nach Gesprächsbeginn (im Minutentakt), summiert nach Teilnehmern. Deine Musterdatei enthält eine Gesamtgesprächedauer von 386796 Sekunden.

[maninweb]

Hallo,

die schnellste Möglichkeit der Auswertung hatte m.E. ja EA1950 schon geschrieben. Falls Du jedoch eine Darstellung wie Deine Zielmatrix
haben möchtest, ginge - sofern ich das richtig verstanden habe - auch Folgendes. Ich würde eher für eine vertikale Darstellung plädieren,
weil ja nun mal das vertikale Scrollen einfacher ist.

vnZeitleistenfilter (0=Nein, 1=Ja) legt fest, ob nur die Minuten angezeigt werden, die tatsächlich eine Belegung hatten.
Die letzte Zeile summiert die Minuten.

PHP-Code:

N1=LET(vnTeilnehmer; A2:A118; 
       vnLogins; WENNFEHLER(--E2:E118; 0); 
       vnLogouts; WENNFEHLER(--F2:F118; 0); 
       vnZeitleistenfilter; 1; 
       vnDaten; SORTIEREN(FILTER(HSTAPELN(vnTeilnehmer; vnLogins - ZEIT(0; 0; SEKUNDE(vnLogins)); 
                vnLogouts - ZEIT(0; 0; SEKUNDE(vnLogouts))); (vnLogins > 0) * (vnLogouts > 0) * (vnLogouts - vnLogins > 0)); 2; 1);       
       vnZeitreihe; LET(T; MIN(INDEX(vnDaten; 0; 2)) + ZEIT(0; 1; 0) * SEQUENZ(1440 * (MAX(INDEX(vnDaten; 0; 3)) - MIN(INDEX(vnDaten; 0; 2))) + 1; 1; 0); 
                        M; NACHZEILE(T; LAMBDA(A; SUMME((A >= INDEX(vnDaten; 0; 2)) * (A <= INDEX(vnDaten; 0; 3))))); 
                           WENN(vnZeitleistenfilter > 0; FILTER(T; M > 0; 0); T)); 
       vnPersonen; SORTIEREN(EINDEUTIG(INDEX(vnDaten; 0; 1))); 
       vnZuordnungen; WEGLASSEN(REDUCE(""; SEQUENZ(ZEILEN(vnPersonen)); 
                      LAMBDA(A;V; LET(F; FILTER(vnDaten; INDEX(vnDaten; 0; 1) = INDEX(vnPersonen; V; 1)); M; 
                      MAP(vnZeitreihe; LAMBDA(T; WENN(SUMME((INDEX(F; 0; 2) <= T) * (INDEX(F; 0; 3) >= T)) > 0; "X"; ""))); HSTAPELN(A; M)))); 0; 1); 
       HSTAPELN(VSTAPELN("Zeit"; vnZeitreihe; "Gesamt"); 
                VSTAPELN(MTRANS(vnPersonen); vnZuordnungen; NACHSPALTE(vnZuordnungen; LAMBDA(A; SUMME(--(LÄNGE(A) > 0))))))) 


Geht auch anders herum, also horizontal. Die Formel wäre dann ...

PHP-Code:

N1=LET(vnTeilnehmer; A2:A118; 
       vnLogins; WENNFEHLER(--E2:E118; 0); 
       vnLogouts; WENNFEHLER(--F2:F118; 0); 
       vnZeitleistenfilter; 1; 
       vnDaten; SORTIEREN(FILTER(HSTAPELN(vnTeilnehmer; vnLogins - ZEIT(0; 0; SEKUNDE(vnLogins)); 
                vnLogouts - ZEIT(0; 0; SEKUNDE(vnLogouts))); (vnLogins > 0) * (vnLogouts > 0) * (vnLogouts - vnLogins > 0)); 2; 1); 
       vnZeitreihe; LET(T; MIN(INDEX(vnDaten; 0; 2)) + ZEIT(0; 1; 0) * SEQUENZ(1; 1440 * (MAX(INDEX(vnDaten; 0; 3)) - MIN(INDEX(vnDaten; 0; 2))) + 1; 0); 
                        M; NACHSPALTE(T; LAMBDA(A; SUMME((A >= INDEX(vnDaten; 0; 2)) * (A <= INDEX(vnDaten; 0; 3))))); 
                           WENN(vnZeitleistenfilter > 0; FILTER(T; M > 0; 0); T)); vnPersonen; SORTIEREN(EINDEUTIG(INDEX(vnDaten; 0; 1))); 
       vnZuordnungen; WEGLASSEN(REDUCE(""; SEQUENZ(ZEILEN(vnPersonen)); 
                      LAMBDA(A;V; LET(F; FILTER(vnDaten; INDEX(vnDaten; 0; 1) = INDEX(vnPersonen; V; 1)); M; 
                      MAP(vnZeitreihe; LAMBDA(T; WENN(SUMME((INDEX(F; 0; 2) <= T) * (INDEX(F; 0; 3) >= T)) > 0; "X"; ""))); VSTAPELN(A; M)))); 1); 
       HSTAPELN(VSTAPELN("Teilnehmer"; vnPersonen); 
                VSTAPELN("Minuten"; NACHZEILE(vnZuordnungen; LAMBDA(A; SUMME(--(LÄNGE(A) > 0))))); 
                VSTAPELN(vnZeitreihe; vnZuordnungen))) 


Gruß

[Andreas Killer]

Hallo Ulrike,

Ich frag erstmal, ist das so nicht sinnvoller?
   

Andreas.

[Andreas Killer]

Ich mach nochmal ein 2tes Bild...
   

Ist vielleicht einfacher zu sehen wo Defizite sind. Wäre das hilfreich(er)?

Andreas.

[umi_1]

Hey Andreas, Danke für die coole Idee und sorry für die späte Rückmeldung.
Das muss ich direkt mit dem zuständigen Kollegen klären, ob er auch mit diesem Layout/Ergebnis klarkommt.
Das gewünschte Ziel, genau zu sehen, wieviele Leute zu welchem Zeitpunkt angemeldet waren, ist auf jeden Fall erreicht.

---

Hey Daniel,
auf die Idee mit ZählenWennS bin ich nicht gekommen - funktioniert wie gewünscht. Danke!
Bin jetzt nur nicht mehr sicher, ob unsere Idee mit der Darstellung in Spalten so sinnvoll ist. Aber die Formel kann ich entsprechend ändern.

Nochmal danke für Deine Rückmeldung.

Ulrike