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.