Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Ereignisabfrage in Abhängigkeit von Jahr, Monat, Wochentag und Zeitraum
#1
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!
Antwortento top
#2
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)
Antwortento top
#3
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
Antwortento top
#4
Hallo und Danke!
Datum und Uhrzeit sind in getrennten Spalten nebeneinader. Brauche ich dann immer noch Pivot?
Antwortento top
#5
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)))
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#6
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!
Antwortento top
#7
Hola,

geraten:


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

Gruß,
steve1da
Antwortento top
#8
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 Smile

Arbeitsblatt mit dem Namen 'Tabelle1'
AB
11112132
2091011
3111009

ZelleFormel
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
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#9
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?
Antwortento top
#10
Hola,


Zitat: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
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste