Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Einträge zählen innerhalb Zeitfenster

Einträge zählen innerhalb Zeitfenster
23.02.2021 15:39:05
Blooregard
Hi,
ich bin zum ersten Mal in diesem Forum und in Sachen Excel eine relative Anfängerin.
Ich habe eine Tabelle mit verschiedenen Subjekten zu denen an verschiedenen Zeitpunkten (Datum) unterschiedliche Ereignisse aus bestimmten Kategorien zugeordnet sind (Kategorien = Gruppen in welche die Ereignisse eingeordnet werden).
Über die SUMMEWENNS Funktion kriege ich ohne weiteres die Anzahl an bestimmten Ereignissen in der jeweiligen Kategorie zu einem Subjekt ausgezählt.
Gewisse Ereignisse sind jedoch an mehreren Tagen hintereinander wiederholt hinterlegt. Ich möchte das Ereignis jedoch nur einmal in einem gewissen Zeitraum zählen. Also die Bedingung hinzufügen: zähle das Ereignis nur wenn es nicht schonmal in den letzten X Tagen aufgetaucht ist.
Ich habe einen Beispieldatensatz anhand der Originaldaten erstellt (https://www.herber.de/bbs/user/144178.xlsx).
Im Feld "B2" kann man die Subjektnummer eingeben. Ich habe 7 Beispielsubjekte angelegt (Nr 1-7; die Originaldatei umfässt ca. 500 Subjekte und 40 000 Zeilen).
In den Feldern D5-12 werden die Anzahl Ereignisse in den jeweiligen Kategorien zum jeweiligen Subjektes gezählt. Ich möchte nun die Bedingung einfügen, dass ein Ereignis nur einmal in einem bestimmten Zeitraum gezählt wird. Der Zeitraum (Anzahl Tage) sollte zu jedem Ereignis individuell zugeordnet werden können (im Tabblatt "Ereignisse").
Ich freue mich auf ein Feedback.

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Einträge zählen innerhalb Zeitfenster
23.02.2021 16:45:00
ChrisL
Hi
Nur mal eine Idee. Die Inputdaten nach Datum sortieren und folgende Formel in Zelle E18 (und runter ziehen):
=WENN(ZÄHLENWENNS(B$17:B18;">"&B18-7;A$17:A18;A18;D$17:D18;D18)>1;"ignorieren";"")
Damit hättest du ein zusätzliches Kriterium für dein ZÄHLENWENNS. Minus 7 = im Zeitraum von 7 Tagen. Vielleicht wäre die genaue Rechenlogik auch noch mal zu überdenken z.B. alle 4 Tage ein Termin würde so mehrfach zum Ignorieren führen.
cu
Chris
PS: Bitte immer eine XL-Version angeben
AW: Einträge zählen innerhalb Zeitfenster
24.02.2021 10:49:02
Blooregard
Lieber Chris,
vielen Dank für die rasche Beantwortung! Ich habe die WENN-Abfrage in mein Beispiel eingebaut und um einen SVERWEIS ergänzt. Ich habe im Tabblatt "Ereignisse" nun Dummyzahlen für ein Zeitfenster je Ereignis in Tagen eingegeben (extra Spalte). Der SVERWEIS in der WENN-Abfrage greift jetzt auf das entsprechende Zeitfenster für jedes einzelne Ereignis separat zurück.
=WENN(ZÄHLENWENNS(B$17:B18;">"&B18-(SVERWEIS(D18;Ereignisse!$A$2:$C$1079;3;FALSCH));A$17:A18;A18; D$17:D18;D18)>1;"ignorieren";"")
https://www.herber.de/bbs/user/144199.xlsx
Ich denke damit funktioniert es. Vielen Dank. Ich bin begeistert :)
Ich habe den Befehl gestern in meiner Tabelle mit 40.000 Einträgen umgesetzt. Es scheint sehr gut zu funktionieren. Ihr hattet das Problem der "Performance" angesprochen. Ich habe in das original-Datenblatt einen kleinen Macro eingebaut, der die Tabelle nach dem gewählten Subjekt sortiert, sobald ein neues Subjekt abgefragt wird (darauf hatte ich im Dummy verzichtet).
Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D2") Then
Call Tabelle3.DatenFiltern
End If
End Sub
Sub DatenFiltern()
Dim Bereich As Range
Dim Variable As Long
Set Bereich = Tabelle3.Range("A36:K4000")
Bereich.AutoFilter Field:=1, Criteria1:=Range("D2").Value
End Sub

Mein Rechner rauscht in der Tat ganz schön, wenn ich ein neues Subjekt abfrage und ich muss einige Sekunden warten. Ich vermute Excel berechnet jeweils alle Spalten mit Formeln neu? Lässt sich das irgendwie verhindern? Wenn nicht, kann ich zurzeit aber gut damit leben.
Vielen Dank dass ihr euch dem Thema widmet. Ich freue mich natürlich auch über weitere Vorschläge. Dieser hier gefällt mir gut, da ich ihn mit meinen begrenzten Fähigkeiten selber gut umsetzen kann.
LG
ps: meine Excel-Version ist nach Recherche die aktuellste, ich habe Microsoft 365.
Anzeige
AW: Einträge zählen innerhalb Zeitfenster
24.02.2021 12:01:41
ChrisL
Hi
Hier eine Alternative mittels Power-Query.
Müsstest dich ein wenig einlesen und versuchen die Einzelschritte im Anhang nachzuvollziehen. Für konkrete Fragen oder Anpassungen kannst du dich gerne melden.
https://www.herber.de/bbs/user/144203.xlsx
Bei Bedarf kann man noch ein kleines Makro zur automatischen Aktualisierung verwenden.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then ThisWorkbook.RefreshAll
End Sub
cu
Chris
AW: Einträge zählen innerhalb Zeitfenster
24.02.2021 13:05:19
Blooregard
Hi
vielen Dank! Mit PQ hatte ich mich noch nicht beschäftigt. Könnte aber auch für einige andere Aspekte in meinem Datensatz eine Lösung darstellen.
Ich werde mich einlesen und bei Bedarf melden!
Hab einen schönen Tag! LG
Anzeige
AW: Einträge zählen innerhalb Zeitfenster
24.02.2021 13:51:42
ChrisL
Habe den Output noch etwas der Vorgabe angeglichen:
https://www.herber.de/bbs/user/144209.xlsx
Die Lösung ist schon ziemlich komplex. Am einfachsten nimmst du den Anhang und ersetzt die Inputdaten. Danach Menü Daten, "Alle aktualisieren".
Ganz grobe Erklärung:
- Hauptabfrage mit Ereignis-Daten zusammenführen
- Inputdaten sortieren
- Mit den Inputdaten je eine Haupt- und Hilfsabfrage erstellt
- Index hinzugefügt, wobei einmal bei 0 und einmal bei 1 beginnend
- Hilfsabfrage zur Hauptabfrage hinzufügen
- Somit besteht ein Versatz um 1 Zeile (aktueller und vorheriger Datensatz)
bedingte Vergleichsspalte:
if [Subjekt] = [Hilfsabfrage.Subjekt] and [Ereignis] = [Hilfsabfrage.Ereignis] and [Datum] - [Ereignisse.Zeit] < [Hilfsabfrage.Datum] then "ignorieren" else "zählen"
- "ignorieren" ausfiltern
- gruppieren
- filtern, wobei das Filterkriterium auf Excel.CurrentWorkbook(){[Name="Subjekt"]}[Content]{0}[Column1]) angepasst wurde (= benannter Bereich B2 in der Tabelle)
- sortieren
Nicht vorhandene Kategorien bilden (HilfeBlankotabelle)
- neue leere Abfrage erstellen
- Zahlenreihe 1-7 wird gebildet = {1...7}
- Anpassen, damit der Max-Wert dynamisch bezogen wird = {1..List.Max(Ereignisse[Kategorie])}
- Liste in Tabelle umwandeln
- Datentyp in Text konvertieren
- Neue Spalte mit Text-Verkettung: = "Kategorie "&[Column1]
HilfeBlankotabelle mit Haupttabelle zusammenführen
- Right Join (alles aus Hilfstabelle)
Total anfügen:
- anstelle einer weiteren Hilfstabelle mit einer erneuten Gruppierung, dieses mal direkt im M-Code eingegriffen
- Virtuelle Gruppierung/Summenbildung:
group = Table.Group(#"Ersetzter Wert", {}, {{"Kategorie", each "Total"}, {"Anzahl", each List.Sum([Anzahl])}}),
- Anfügeabfrage (n.b. APPEND=anfügen, nicht zu verwechseln mit JOIN=zusammenfügen), Variable group zur Hauptabfrage anfügen:
append = Table.Combine({#"Ersetzter Wert", group})
cu
Chris
Anzeige
AW: nachgefragt ...
24.02.2021 15:04:30
neopa
Hallo Chris,
... aber zuerst einmal vielen Dank Dir auch von mir, dass Du Dich dieser Problemlösung so ausführlich angenommen hast.
Nun zu meine Frage:
In meiner XL2016er Version gibt es mit dieser Datei, wie auch mit der vorangegangen Datei (nicht aber in der zuerst von Dir eingestellten) in der Hauptabfrage folgende Fehlermeldung:
"Formula Firewall: Formula.Firewall: Abfrage 'Hauptabfrage' (Schritt 'Gefilterte Zeilen') verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu." womit ich momentan nichts anfangen kann.
Könnte das eine Versionsproblem sein oder was muss ich da tun, den Fehler zu umgehen?
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
24.02.2021 15:24:04
ChrisL
Hi Werner
Kosmisch...
https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-not/td-p/18619
Schau mal im PQ-Editor, Abfrageoptionen, Datenschutz. Ich habe da die Einstellung "Einstellungen auf Datenschutzebene immer ignorieren". Da sich alles innerhalb der gleichen Datei abspielt, gehe ich davon aus dass kein Datenleck entsteht.
Ansonsten, weil der Schritt Filter erwähnt wird.
= Table.SelectRows(#"Gruppierte Zeilen", each ([Subjekt] = Excel.CurrentWorkbook() {[Name="Subjekt"]}[Content]{0}[Column1]))
Ich nehme an, weil ich den Bereichsnamen (Subjekt) als Filterkriterium verwendet habe. Könnte man mal zurück ändern:
= Table.SelectRows(#"Gruppierte Zeilen", each ([Subjekt] = 1))
Übrigens noch zu meiner ursprünglichen Aussage zur Performance von PQ. Da hier nur immer eine Zeile versetzt wird (Abgleich zur vorherigen Zeile), sollte es OK sein. Schwierig wäre die andere Methode (Zeitdifferenz zum letzten gezählten Datensatz), aber das war ja zum Glück nicht die Aufgabenstellung.
cu
Chris
Anzeige
AW: nachgefragt ...
24.02.2021 15:38:10
ChrisL
vielleicht so:
https://www.herber.de/bbs/user/144211.xlsx
Habe jetzt auf den Bereichsnamen verzichtet und das Filterkriterium als Tabelle via Right-Join aufgenommen.
AW: diesen Deinen Beitrag erst jetzt gesehen ...
24.02.2021 15:59:07
neopa
Hallo Chris,
... die Datei geöffnet und funktioniert auch.
Danke auch hierfür.
Gruß Werner
.. , - ...
AW: ja die Filterdefinition war der "Übeltäter"...
24.02.2021 15:51:36
neopa
Hallo Chris,
... diese hab ich jetzt im Editor so geändert, wie von Dir hier angegeben und damit war der Fehler behoben :-) Die Datenschutzebene steht bei mir weiterhin auf der mittleren Option.
Jetzt werde ich etwas später in Ruhe versuchen, Deinen Lösungsweg einigermaßen zu verstehen.
Vielen dank und ich wünsche Dir noch einen schöne Restwoche.
Gruß Werner
.. , - ...
Anzeige
AW: die XL-Vers. wäre hier schon von Relevanz, ...
23.02.2021 19:50:49
Relevanz,
Hallo,
... darauf hatte Chris ja auch schon im PS seines Beitrag hingewiesen.
Denn zumindest eine XL 2016 Version wäre hier wegen der Menge Deiner auszuwertenden Daten mE effizienter als eine Formellösung. Allein für die ca. 500 Subjekten mit bis zu 7 Kategorien ergeben sich schon ohne Hilfsformeln 3.500 notwendige Ergebnisformeln. Und mit diesen müssen 40.000 Datensätze ausgewertet werden, für die zunächst nicht nur erst die Kategorien ermittelt werden müssen, sondern auch noch der Zeitraum an Tagen, welcher nicht zu berücksichtigten ist. Die Tagesangaben sollen ja je nach Ereignis individuell im Blatt: Ereignisse zugeordnet sein. Nur sind da aber in Deiner Beispieldatei bisher noch keine angegeben.
Das alles "schreit" mE gerade nach einer Auswertung mit PowerQuery (PQ), dessen Funktionalität aber erst ab XL 2016 in Excel integriert ist. Mit PQ kennt sich Chris zudem wesentlich besser aus als ich. Sollte also bei Dir mindestens XL2016 im Einsatz sein, würde ich mich auch auf seinen entsprechenden Lösungsvorschlag freuen.
Gruß Werner
.. , - ...
Anzeige
AW: die XL-Vers. wäre hier schon von Relevanz, ...
24.02.2021 08:54:51
Relevanz,
Hi Werner
Ich glaube solange man alle Datensätze gegenseitig abgleichen muss (Multiplikation), wird auch PQ keine performante Lösung bringen. Evtl. liesse sich mit dem "Fuzzy Lookup Add-In for Excel" etwas kreatives machen, aber das Add-In steht mir nicht zur Verfügung.
https://support.microsoft.com/en-us/office/fuzzy-match-support-for-get-transform-power-query-ffdd5082-c0c8-4c8e-a794-bd3962b90649
Was hingegen gut ginge, wäre wenn man den Betrachtungsraum z.B. auf 1 Kalenderwoche eingrenzt.
https://www.herber.de/bbs/user/144192.xlsx
(auf den Subjekt-Filter habe ich im Beispiel verzichtet)
cu
Chris
Anzeige
AW: danke für Deine Information ...
24.02.2021 09:01:29
neopa
Hallo Chris,
... die mich jetzt natürlich etwas überrascht. Damit hätte ich jetzt nicht gerechnet zumal ja der TE auch mit flexiblen Betrachtungszeiträumen auswerten möchte.
ch schau mir später Deine Lösungsvorschlag an, denn ich bin jetzt gleich erst einmal ein paar Stunden offline.
Gruß Werner
.. , - ...
AW: danke für Deine Information ...
24.02.2021 09:44:07
ChrisL
Hi Werner
Der Betrachtungszeitraum muss nicht zwingend 1 KW sein, aber ein fixer Rhythmus (z.B. x Tage). Ansonsten müsstest du immer wieder eine Suche/Relation zum letzten Datensatz herstellen, was die Performance beeinträchtigt.
Zudem wird die Sache noch komplizierter, wenn man Methode 2, also nicht den letzten Datensatz, sondern den letzten gezählten Datensatz sucht.
(angenommen 7 Tage Zeitraum)
1.2.21 - zählen
5.2.21 - ignorieren
10.2.21 - ignorieren
15.2.21 - ignorieren
1.3.21 - zählen
oder
1.2.21 - zählen
5.2.21 - ignorieren
10.2.21 - zählen
15.2.21 - ignorieren
1.3.21 - zählen
cu
Chris
Anzeige

232 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige