Ereignisabfrage in Abhängigkeit von Jahr, Monat, Wochentag und Zeitraum

[Effe_Alf]

Liebe Helfende,
mit geringer Excel-Erfahrung und nach Recherche kann ich folgendes Problem nicht überwinden:

Tabelle:
A;B
Datum; Uhrzeit(als hhmmss ohne ':'-Trennungen)


Zwei Abfragen möchte ich erstellen:
1. Anzahl der Ereignisse pro Jahr/Monat in den Nächten von Mo-Fr zwischen 18:00-07:00 Uhr
2. Anzahr der Ereignisse pro Jahr/Monat am Wochende Fr 18:00 - Montag 07:00

Ich vermute das


SUMMENPRODUKT(JAHR(A:A=2010)*MONAT(A:A=1)*WENN(WOCHENTAG(A:A;12)<5;1;0))
---2010,Januar,Di-Fr---

in die richtige Richtung geht, allerdings mit unplausiblen Ergebnissen (Mehr Treffer als Datensätze).
Die Zeitraumeingrenzung fehlt noch in der Formel, sollte aber mit größer/kleiner-Vergleichen gehen, oder?

Gibt es einen anderen Ansatz?
Vielen Dank für die Hilfe!

[Fennek]

Hallo,

falls das Datum ein Xl-Datum (also kein Text) ist, könnte man eine Hilfsspalte für die Trennung der Zeiten einfügen und dann mit Pivot auswerten.

mfg

(alle Fragen, die Datum bzw Zeit beinhalten, sind ohne Beispieldatei kaum zu beantworten)

[steve1da]

Hola,

abgesehen davon dass deine Formel lauter Klammerfehler hat, wird eine leere Zelle in der Monatsabfrage als 1 definiert. Da du komplette Spalten auswertest, was man bei Summenprodukt nicht tun sollte, hast du zum Glück ne Menge davon. Du musst also zusätzlich noch (A:A<>"") abfragen.

Gruß,
steve1da

[Effe_Alf]

Hallo und Danke!
Datum und Uhrzeit sind in getrennten Spalten nebeneinader. Brauche ich dann immer noch Pivot?

[schauan]

Hallöchen,

viele Wege führen nach Rom und auch wieder zurück.
Wenn Du in Deiner Formel die Uhrzeiten nicht berücksichtigst, bekommst Du alle Ereignisse eines Tages und nicht nur die Nachtstunden.
Ansonsten könnte es so klappen, ich hab das jetzt mal bis Zeile 10 reduziert:
=SUMMENPRODUKT((JAHR(A1:A10)=2010)*(MONAT(A1:A10)=1)*(WOCHENTAG(A1:A10;12)<5)*((B1:B10>=18/24)+(B1:B10<=7/24)))

[Effe_Alf]

Das hat geholfen, Danke!

Etwas modifiziert ist es so geworden:

Für 1

=SUMMENPRODUKT((JAHR(Daten!$A:$A)=$A2)*(MONAT(Daten!$A:$A)=C$1)*(WOCHENTAG(Daten!$A:$A;11)<5)*(Daten!$B:$B>Auswertung!$R$2*10000)*(Daten!$A:$A<>""))
+SUMMENPRODUKT((JAHR(Daten!$A:$A)=$A2)*(MONAT(Daten!$A:$A)=C$1)*(WOCHENTAG(Daten!$A:$A;12)<5)*(Daten!$B:$B""))


Für 2

=SUMMENPRODUKT((JAHR(Daten!$A:$A)=$A2)*(MONAT(Daten!$A:$A)=C$1)*(WOCHENTAG(Daten!$A:$A;11)=5)*(Daten!$B:$B>Auswertung!$R$3*10000)*(Daten!$A:$A<>""))
+SUMMENPRODUKT((JAHR(Daten!$A:$A)=$A2)*(MONAT(Daten!$A:$A)=C$1)*(WOCHENTAG(Daten!$A:$A;11)=1)*(Daten!$B:$B""))
+SUMMENPRODUKT((JAHR(Daten!$A:$A)=$A2)*(MONAT(Daten!$A:$A)=C$1)*(WOCHENTAG(Daten!$A:$A;11)>5))


Eine weitere Auswertung soll die Ereignisse/Stunde in der Nacht für die einzelnen Wochentage zeigen.

folgendes war meine Idee:


=SUMMENPRODUKT((WOCHENTAG(Daten!$A:$A;2)=Auswertung2!B$8)*(LINKS(Daten!$B:$B;LÄNGE(Daten!$B:$B)-4)*1=$A9)*(Daten!$A:$A<>""))

Ergebnis=#WERT!

Das 1.Array mit Wochentag geht alleine. Das 2.Array ermittelt aus einer 5bzw.6-Stelligen Zahl (Uhrzeit HHMMSS) die Stunde als ganze Zahl. Ich vermute, dass hier das Problem am Datenformat (Text/Zahl bzw string/int) liegt. Das Multiplizieren mit 1 hilft nicht.

Ideen?
Vielen Dank!

[steve1da]

Hola,

geraten:

Code:

=SUMMENPRODUKT((WOCHENTAG(Daten!$A:$A;2)=Auswertung2!B$8)*(Stunde(Daten!B:B)=$A9)*(Daten!$A:$A<>""))

Gruß,
steve1da

[schauan]

Hallöchen,

warum das:
LÄNGE(Daten!$B:$B)-4)
HHMMSS hätte doch in jedem Fall die beiden linken Stellen als Stundenangabe
Sofern es eine umformatierte Uhrzeit ist, siehe Vorschlag von steve1da

Ansonsten, wenn es einfach nur eine Zahl oder ein "Text" ist, mal wieder ein Ansatz, vorausgesetzt, die HH sind immer zweistellig, also auch vor 10 Uhr

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	111213	2
2	091011
3	111009

Zelle	Formel
B1	=SUMMENPRODUKT(((--LINKS(0&A1:A10;3))=11)*(1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Effe_Alf]

Danke!

Die Uhrzeit ist eine "normale" Zahl und die Zeitpunkte vor 10 Uhr sind 5-Stellig, die anderen 6-Stellig. Darum das Gehampele mit LÄNGE()-4.

Die Funktion STUNDE will nicht, erwartet wohl ein richtiges Uhrzeit-Format.

Oder habe ich etwas falsch verstanden?

[steve1da]

Hola,

Die Funktion STUNDE will nicht, erwartet wohl ein richtiges Uhrzeit-Format.

ja. Wie gesagt, war auch nur geraten weil du keine Beispieldatei zeigst. Da müssen wir uns die Formate immer selber ausdenken.

Gruß,
steve1da