Wochtentagsverteilung in Datumsliste

[Sephan]

Hallo, ich habe ein Problem, was ich bisher noch nicht lösen konnte.

Ich habe eine Tabelle vom Format:

Code: Alles auswählen

ID   Datum       Zeit
1    11.02.2015  23:37
2    12.02.2015  21:02
3    20.02.2015  20:50
4    20.02.2015  21:21

Nun möchte ich wissen wie oft jeder Wochentag in der Liste auftaucht als auch wie oft es 20:xx Uhr, 21:xx Uhr, 22:xx Uhr usw. gibt.

[lorbass]

Etwa so?

Ereignisverteilung.ods

(66.34 KiB) 321-mal heruntergeladen

Gruß
lorbass

[Sephan]

Es sieht so aus, aber ich verstehe nicht wie es funktioniert
Mit Datenpilot hab ich noch nie was gemacht, aber jetzt weiß ich zumindest wozu er gut sein kann
Spannende Funktion das

EDIT:
Ah, ich glaube ich verstehs langsam. Gar nicht so kompliziert nur total ungewohnt

[Sephan]

Habe allerdings noch eine einfachere Methode gefunden (Anzahl Montage):

Code: Alles auswählen

=SUMMENPRODUKT(WOCHENTAG(Semester.B2:B9999;2)=1)

Da ich nicht weiß wie lang die Tabelle mit den Daten geht, habe ich einfach 9999 angegeben. Mehr Daten wird es nicht geben. Allerdings gibt WOCHENTAG() bei einem leeren Feld dann 6 aus, wodurch meine Samstage völlig falsch werden. Wie kann man das verhindern?

EDIT:
So scheint es zu gehen:

Code: Alles auswählen

=SUMMENPRODUKT(WOCHENTAG(Semester.B2:B9999;2)=6;Semester.B2:B9999<>0)

Für Zeiten 20:xx sähe so aus:

Code: Alles auswählen

=SUMMENPRODUKT(STUNDE(Semester.C2:C9999)=20;Semester.C2:C9999<>0)

Ich hoffe das ist korrekt so?!

[Sephan]

Was mir jetzt nur noch fehlt ist, dass ich auch den zu betrachtenden Zeitbereich anhand eines Datumstarts- und -endpunktes festlegen kann.

Aktuell versuche ich das so:

Code: Alles auswählen

=WENN(UND(Daten.B2:B9999>=B6;Daten.B2:B9999<=C6);SUMMENPRODUKT((Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999)))

In B6 steht das Startdatum und in C6 das Enddatum.
Funktionieren tut das aber leider nicht

Da das ein neues Thema ist, werde ich am Besten auch einen Thread aufmachen

[lorbass]

Die Bedingungen

Code: Alles auswählen

UND(Daten.B2:B9999>=B6;Daten.B2:B9999<=C6)

aus der WENN-Funktion kannst du als Argumente mit in die SUMMENPRODUKT-Formel nehmen. Ein bisschen aufgeräumt und Zeitbereich durch absolute Adressierung fixiert, sieht die dann so aus:

Code: Alles auswählen

=SUMMENPRODUKT(
➀  (Daten.B2:B9999 >= $B$6);
➁  (Daten.B2:B9999 <= $C$6);
➂  (Daten.C2:C9999 > Daten.D2:D9999);
➃  (Daten.D2:D9999 - Daten.C2:C9999)
)

oder so:

Code: Alles auswählen

=SUMMENPRODUKT(
➀  (Daten.B2:B9999 >= $B$6) * 
➁  (Daten.B2:B9999 <= $C$6) *
➂  (Daten.C2:C9999 > Daten.D2:D9999) *
➃  (Daten.D2:D9999 - Daten.C2:C9999)
)

Die Argumente in den Zeilen ➀ und ➁ kann ich nachvollziehen. Aber was willst du mit denen in den Zeilen ➂ und ➃ erreichen? Was steht denn in den Spalten C und D? In deinem ersten Beitrag gibt's keine Spalte D. — Dein ursprüngliches Anliegen war doch:

Nun möchte ich wissen wie oft jeder Wochentag in der Liste auftaucht als auch wie oft es 20:xx Uhr, 21:xx Uhr, 22:xx Uhr usw. gibt.

Wie sieht es mit der Reduktion der Zeiten auf die Stunden und der Daten auf die Wochentage aus?

Lade bitte zukünftig eine Beispieldatei zusammen mit deinen Fragen hoch. Das spart mühsame Spekulationen und Rückfragen.

Gruß
lorbass

[Sephan]

Danke dir für deine Hilfe.

Spalte D ist eine Enduhrzeit. Die hatte ich hier jetzt noch nicht angegeben, weil ich mich von einem Problem zum nächsten hangeln wollte, oder gehofft hatte anderes dann selbst zu regeln, oder mir weitere Probleme noch nicht bewusst waren.

Gelöst habe ich das Problem jetzt so:

Code: Alles auswählen

=SUMMENPRODUKT((Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999);(Daten.B2:B9999<B6))

Ein Anfangs- und Enddatum ist gar nicht nötig. Weil es eigentlich nur zwei Bereiche gibt die mich interessieren. Ein vor einem bestimmten Datum und einer danach.

Im Anhang auch eine Beispieltabelle mit den mir noch fehlenden Berechnungen in rot. Ich bin mir noch nicht sicher, aber so wie du das jetzt gemacht hast, sieht es so aus als könnte man das vlt auch mit Summenprodukt machen?

[lorbass]

Code: Alles auswählen

=SUMMENPRODUKT((Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999);(Daten.B2:B9999<B6))

Du magst ja eine gute Begründung dafür haben, aber ohne die ist diese Formel – mit Verlaub – Humbug.

(Daten.C2:C9999>Daten.D2:D9999) ist ein Boolescher Ausdruck, Wertebereich: WAHR oder FALSCH, oder numerisch: 0 oder 1. Dazu addierst du mit (Daten.D2:D9999-Daten.C2:C9999) eine Differenz zweier Uhrzeiten, wenn ich dich richtig verstanden habe.
Mögliche Ergebnisse bis hierhin z.B. WAHR+17:25 Uhr oder FALSCH+19:45 Uhr. Was soll das sein? Ich habe keine Erklärung.

Dazu kommt dann mit (Daten.B2:B9999<B6) ein weiterer Boolescher Ausdruck.

Das Ganze wäre dann sinnvoll, wenn du multiplizieren würdest statt zu summieren.

Code: Alles auswählen

=SUMMENPRODUKT( ( Daten.B2:B9999 < B6 ) * ( Daten.C2:C9999 > Daten.D2:D9999 ) * ( Daten.D2:D9999 - Daten.C2:C9999 ) )

So würde gesteuert, ob die Zeitdifferenz einer Zeile zur Gesamtsumme beiträgt (beide Boolesche Ausdrücke = WAHR = 1 ⇒ 1 * 1 * ΔZeit = ΔZeit) oder nicht (mindestens ein Boolescher Ausdruck = FALSCH = 0 ⇒ 1 * 0 * ΔZeit = 0).
Hinweis: Die Trennung zweier Terme in zwei Argumente durch ein Semikolon ist gleichbedeutend wie ihre Multiplikation und beide wirken wie ein logisches UND, die Addition zweier Terme wirkt wie ein logisches ODER. Die zweite Formel könnte auch lauten

Code: Alles auswählen

=SUMMENPRODUKT( ( Daten.B2:B9999 < B6 ); ( Daten.C2:C9999 > Daten.D2:D9999 ); ( Daten.D2:D9999 - Daten.C2:C9999 ) )

Ein Anfangs- und Enddatum ist gar nicht nötig.

Was ist dann B6 in der Formel?

Gruß
lorbass

PS: Zum Ansehen deiner Beispieldatei ist es noch zu früh

[Sephan]

Mögliche Ergebnisse bis hierhin z.B. WAHR+17:25 Uhr oder FALSCH+19:45 Uhr. Was soll das sein? Ich habe keine Erklärung.

Ehrlich gesagt hatte ich das (Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999) auch nicht ganz verstanden. Das Beispiel hatte ich im Internet gefunden und war nötig, damit Zeiten die über 0 Uhr hinaus gingen richtig berechnet werden und auch, dass er insgesamt über 24h hinauszählt. Ohne das, kamen irgendwelche negativen Zahlen raus die auch positiviert keinen Sinn machten. Mit dem Boolschen Ausdruck stimmen die Ergebnisse. Siehe auch in meiner Beispieldatei.

Hinweis: Die Trennung zweier Terme in zwei Argumente durch ein Semikolon ist gleichbedeutend wie ihre Multiplikation und beide wirken wie ein logisches UND, die Addition zweier Terme wirkt wie ein logisches ODER.

Danke dafür, jetzt verstehe ich das endlich besser.

Code: Alles auswählen

=SUMMENPRODUKT( ( Daten.B2:B9999 < B6 ); ( Daten.C2:C9999 > Daten.D2:D9999 ); ( Daten.D2:D9999 - Daten.C2:C9999 ) )

Damit kommen falsche Ergebnisse raus. Anscheinend muss es mit einem ODER verknüpft werden.

Was ist dann B6 in der Formel?

In B6 steht das Startdatum des zweiten Zeitbereiches der betrachtet werden soll.

In manchen meiner Berechnungen sollen beide Zeitbereiche zusammen betrachtet und in anderen sollen sie getrennt betrachtet werden.

[paljass]

Hi,
das mit den Zeiten passt schon so, wie du es gemacht hast - hab jetzt allerdings keine Zeit darauf einzugehen.

Formeln für
Anzahl Semester

Code: Alles auswählen

=SUMMENPRODUKT(Daten.B2:B9999>=B5;Daten.B2:B9999<=C5)

Anzahl Ferien

Code: Alles auswählen

=SUMMENPRODUKT(Daten.B:B9999>=B6;Daten.B2:B9999<=C6)

Max Dauer Semester

Code: Alles auswählen

=MAX((Daten.B2:B9999>=B5)*(Daten.B2:B9999<=C5)*((Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999)))

als Matrixfprmel
Max Dauer Ferien

Code: Alles auswählen

=MAX((Daten.B2:B9999>=B6)*(Daten.B2:B9999<=C6)*((Daten.C2:C9999>Daten.D2:D9999)+(Daten.D2:D9999-Daten.C2:C9999)))

auch als Matrixformel.

Gruß
paljass

Achtung: die Formeln sind durchs kopieren fehlerhaft; Daten.B3:B10000 muss ersetzt werden durch Daten.B2:B9999 usw.
Ich habs mal editiert.