Office: Spitzenstunde berechnen

Hallo zusammen,

ich hoffe, dass mir hier jemand helfen kann. Ich muss berechnen, wann innerhalb von 15 min und innerhalb von 60 min die meisten Gäste eine Ausstellung betreten haben und wie viele es in dem Zeitfenster waren. Ich habe über den Tag zu jedem Gast/ Eintreten einen Zeitstempel. Weiß jemand eine Formel hierfür?

Tausend Dank und Grüße,
Angela

 

Hola,
ohne Exceldatei wird das ein Ratespiel.
Gruß,
steve1da

 

Hallo steve1da,

hier ist die Datei. Es sind wirklich nur de Zeitpunkte, wann ein Gast durch die Tür gegangen ist.

Viele Grüße

 

Hi,

Achtung! Deine Zeiten sind keine echten Zeiten sondern nur Texte, die wie ein Zeit aussehen. Das kannst du z.B. daran erkennen, dass bei einer Umstellung des Zahlenformats auf "Standard" immer noch eine Uhrzeit statt einer Zahl <1 zu sehen ist. Die Texte solltest du zuerst in eine echte Uhrzeit umwandeln. Indem du
- entweder indem du in irgendeine Zelle eine 1 eingibst, diese kopierst, dann alle "Uhrzeiten" markierst, Rechtsklick, "Inhalte einfügen...", Multiplizieren, OK
- oder alle "Uhrzeiten" markierst und dann "Daten"-->"Datentools"-->"Text in Spalten"-->"Fertig"
ausführst.

Die stündlichen Zahlen bekommst du ganz einfach mit einer Pivottabelle (die einzige Spalte in die Zeilen und Werte ziehen, notfalls in den Zeilen die Gruppierung nach Stunden aktivieren). Die Viertelstunden sind in der Pivot leider nicht vorgesehen, kann man also nicht ohne weiteres bekommen.

Wenn du es über Formeln machen willst, dann geht es im Grunde so:
=ZÄHLENWENNS($B$5:$B$22248;">"&ZEIT(0;0;0);$B$5:$B$22248;"<="&ZEIT(0;15;0))

Bei aktuellem Excel liefert die folgende Formel die Werte für alle 1/4-Stunden:

Code:

=LET(
    Bereich;B:B;
    Intervall;ZEIT(0;15;0);
    AnzZeiten;1/Intervall;
    Uhrzeit;SEQUENZ(AnzZeiten;;Intervall;Intervall);
    Stück;ZÄHLENWENNS(Bereich;">"&Uhrzeit-Intervall;Bereich;"<="&Uhrzeit);
    tab;HSTAPELN(Uhrzeit;Stück);
    tab
)

 

Hallo Helmut,

da wird mir beim Eingeben schon schwindelig ;) Dankeschön! Ich bin nur ein Otto-Normal-Nutzer, daher komme ich mit der Pivottabelle schon nicht zurecht. Dort werden nur die ersten 10.000 Einträge angezeigt und selbst da weiß ich nicht, ob das so richtig ist...Die Zeiten habe ich umgewandelt. Wenn ich die Formel mit Zählenwenns so eingebe, bekomme ich nur 0 raus. Bei der zweiten Formel bekomme ich eine Fehlermeldung.
Mir schwirrt ein bisschen der Kopf...
Viele Grüße,
Angela

 

Das liegt daran, dass Du beim Beitragerstellen ein Infofeld nicht ausgefüllt hast!

 

Was denn für ein Feld? Sorry, bin ganz frisch hier...

 

Du hast die Version nicht angegeben. Weil wir Helfer bei 2000 Antworten im Jahr keine Lust mehr haben, nach dieser zu fragen, nehmen wir Excel 365 an.

 

Ok, danke für den Hinweis. Es ist auch Excel 365.

 

Hi,

wenn du die Formel tatsächlich so eingegeben hast, wie im Screenshot unten dargestellt, dann brauchst du dich nicht zu wundern, denn die Klammersetzung am Ende der Formel stimmt nicht. Die vorletzte Klammer gehört direkt hinter Stück und nicht hinter tab. Kopier die Formel doch einfach direkt aus meinem Beitrag in die Formelleiste oder (nach einem Doppelklick) in die Zelle. Die Zeilenumbrüche stören nicht. Und der Bereich darf gerne über die komplette Spalte gehen. Das stört Zählenwenns() nicht.

Übrigens: so verwirrend ist die Formel nicht. Zumindest, wenn du die Zeilenumbrüche stehen lässt. Der erste Parameter jeder Zeile definiert eine Variable, der zweite deren Inhalt. So bleibt die Formel gut lesbar.
Die ersten beiden Zeilen definieren den Datenbereich und das gewünschte Intervall.
Die 3. berechnet die Anzahl der nötigen Zeilen um 24 Stunden (=1 bei 12 Stunden wäre es die 0,5) abzudecken.
Die 4. berechnet die Uhrzeiten
Die 5. berechnet die Stückzahl im Zeitintervall
Die 6. stellt die beiden Ergebnis-Spalten nebeneinander
Die 7. gibt das Ergebnis aus.

Übrigens: LET kann man auflösen, indem man die Variablen jeweils durch ihre Definition ersetzt. Dann sieht die Formel so aus:

Code:

=HSTAPELN(SEQUENZ(1/ZEIT(0;15;0);;ZEIT(0;15;0);ZEIT(0;15;0));ZÄHLENWENNS(B:B;">"&SEQUENZ(1/ZEIT(0;15;0);;ZEIT(0;15;0);ZEIT(0;15;0))-ZEIT(0;15;0);B:B;"<="&SEQUENZ(1/ZEIT(0;15;0);;ZEIT(0;15;0);ZEIT(0;15;0))))

 

Hi,
hier mal noch die Datei mit Pivot und der Let()-Formel und dem echten Monstrum ohne Let()...

 

Hallo,

in der beiliegenden Datei habe ich dir in den Namensmanager die benutzerdefinierte LAMBDA-Funktion
=IntervallZähler( Bereich; MinutenIntevall )
eingefügt. Wie diese aufzurufen ist, habe ich dir an mehreren Beispielen gezeigt:
zB. in Zelle D4: =IntervallZähler($B$5:$B$22248;15) um die Anzahlen in 15-Minutenintervallen anzuzeigen, oder
zB. in Zelle G4: =IntervallZähler($B$5:$B$22248;30) um die Anzahlen in 30-Minutenintervallen anzuzeigen, usw.
Die Uhrzeiten im Bereich dürfen auch als Texte vorliegen (so wie in deiner Originaldatei).

Und hier die LAMBDA-Funktion in strukturierter Form: