Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1668to1672
1668to1672
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zählen wenn, Zeitabhängigkeit

Forumthread: Zählen wenn, Zeitabhängigkeit

Zählen wenn, Zeitabhängigkeit
28.01.2019 08:51:28
Frank
Hallo liebe Excel Gemeinde,
ich habe keine Idee, wie ich das folgende Problem in VBA realisieren kann und hoffe auf ein paar gute Ideen.
Vielen Dank.
In Spalte A stehen Datumsangaben
In Spalte B stehen Uhrzeiten
in Spalte D stehen Ziffern zwischen 1 und 100000
Nun das Problem:
Der Code soll die Spalte D von der ersten bis zur letzten Zeile durchlaufen und Zählen wenn eine Ziffer "X" erfasst wird - doppelte Werte innerhalb von 20 min werden nicht mitgezählt. Wird die Ziffer "X" größer 20min erneut erfasst, wird wieder +1 gezählt.
Sehr gut wäre es, wenn der Tageswechsel mit berücksichtigt wird.
Bezogen auf dem Screenshot müsste der Zähler bei Zeile 23 bei 9 stehen.
Userbild
Bei Bedarf kann ich auch die Datei hochladen.
Vielen Dank.
Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zählen wenn, Zeitabhängigkeit
28.01.2019 13:52:53
Daniel 

Bei Bedarf kann ich auch die Datei hochladen.
Gute Idee!
AW: Zählen wenn, Zeitabhängigkeit
28.01.2019 16:41:24
Daniel
Hi Frank,
das ist für mich eine echt harte Nuss, ich weiß dass der Code wahrscheinlich noch nicht so funktioniert wie gewünscht:
Sub Frank()
Dim cell, cell1 As Range, myRange, myRange1 As Range
Dim Counter As Long, i, j As Long
Set myRange = Range(Cells(1, 1), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
'Spalte A / Datum
Set myRange1 = Range(Cells(1, 4), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 4))
'Spalte D / Zählspalte
'Datum und Zeit zusammenfügen und als Zahl ausgeben
For Each cell In myRange
Cells(cell.Row, 3) = CDbl(Cells(cell.Row, 1) + Cells(cell.Row, 2))
Next cell
'Zahlen in Spalte D zählen
For Each cell1 In myRange1
If cell1.Row > 1 Then
For i = cell1.Row - 1 To 1 Step -1 'Gehe alle früheren Werte durch, bis Ende oder Zä _
hlung
If cell1 = cell1.Offset(-cell1.Row + i, 0) Then 'Bei gleichen aufeinanderfolgenden  _
Werten
If (cell1.Offset(0, -1) - cell1.Offset(-cell1.Row + i, -1)) > 0.014 Then
'0,014 entspricht etwa 20 Minuten
Counter = Counter + 1
'Nur zählen, wenn ein gleicher Wert vor mehr als 20 Min vorhanden ist
Exit For
End If
Else
j = i
'Wenn die Werte ungleich sind, prüfe ob in den letzten 20 Minuten der gleiche  _
Wert vorkam
Do While (cell1.Offset(0, -1) - cell1.Offset(-cell1.Row + j, -1))  1
If cell1 = cell1.Offset(-cell1.Row + j, 0) Then GoTo Weiter
j = j - 1
Loop
Counter = Counter + 1 'Ansonsten Zählung
Exit For
End If
Weiter:
Next i
Else
Counter = Counter + 1 'Erste Zeile wird immer gezählt
End If
Next cell1
MsgBox "Zähler steht bei " & Counter
Set myRange = Nothing
Set myRange1 = Nothing
End Sub
Ich kann schwer prüfen, ob er so zählt wie er soll. Ich vermute aber, dass er bei gleichen aufeinanderfolgenden Werten falsch verfährt. Er schaut dann nämlich, ob irgendwo in der Spalte darüber der gleiche Wert steht mit mehr als 20 Minuten dazwischen. An sich nicht verkehrt, er ignoriert aber eventuelle spätere gleiche Werte und ich vermute, dass nur zwischen dem letzten gleichen Wert mindestens 20 Minuten vergangen sein müssen?
Vielleicht nutzt einer der Experten ja dies als Anstoß, noch weiter zu optimieren?
Grüße
Daniel
Anzeige
AW: Zählen wenn, Zeitabhängigkeit
29.01.2019 08:31:14
Frank
Hallo Daniel,
vorab vielen Dank, dass du dich mit dem Thema auseinandergesetzt hast. Das war schon clever, die Datums - und Zeitspalte jeweils in einer Zelle darzustellen, um den Tageswechsel zu berücksichtigen.
Ich lade nochmals eine Datei hoch, in der in der Spalte K die "Zählerstände" anzeigt werden. Wenn ich mich jetzt nicht verzählt haben sollte, müsste der Zähler beim durchlaufen des Codes bei 49 stehen.
Zu deiner Vermutung:
Angenommen es stehen in Spalte D, drei 7 untereinander dann wäre gut, wenn er die letzte als zeitlichen Bezugspunkt nimmt. Wenn es programmiertechnisch einfacher ist die Erste zu nehmen, dann soll es so sein - ist nicht entscheidend.
Müsste er nicht theoretisch die Abfrage bei jeder Zeile durchführen?
https://www.herber.de/bbs/user/127240.xlsm
Gruß Frank
Anzeige
AW: Zählen wenn, Zeitabhängigkeit
29.01.2019 08:45:42
Daniel
Wenn nur der letzte Wert als Vergleich genommen werden soll, macht das das Ganze tatsächlich wesentlich einfacher. Mit dieser kleinen Änderung klappt es jetzt bei deinem neuen Beispiel:
Sub Frank()
Dim cell, cell1 As Range, myRange, myRange1 As Range
Dim Counter As Long, i, j As Long
Set myRange = Range(Cells(1, 1), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
'Spalte A / Datum
Set myRange1 = Range(Cells(1, 4), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 4))
'Spalte D / Zählspalte
'Datum und Zeit zusammenfügen und als Zahl ausgeben
For Each cell In myRange
Cells(cell.Row, 3) = CDbl(Cells(cell.Row, 1) + Cells(cell.Row, 2))
Next cell
'Zahlen in Spalte D zählen
For Each cell1 In myRange1
If cell1.Row > 1 Then
For i = cell1.Row - 1 To 1 Step -1 'Gehe alle früheren Werte durch, bis Ende oder Zä _
hlung
If cell1 = cell1.Offset(-cell1.Row + i, 0) Then 'Bei gleichen aufeinanderfolgenden  _
Werten
If (cell1.Offset(0, -1) - cell1.Offset(-cell1.Row + i, -1)) > 0.014 Then
'0,014 entspricht etwa 20 Minuten
Counter = Counter + 1
cell1.Offset(0, 2) = Counter
'Nur zählen, wenn ein gleicher Wert vor mehr als 20 Min vorhanden ist
Exit For
Else
Exit For
End If
Else
j = i
'Wenn die Werte ungleich sind, prüfe ob in den letzten 20 Minuten der gleiche  _
Wert vorkam
Do While (cell1.Offset(0, -1) - cell1.Offset(-cell1.Row + j, -1))  1
If cell1 = cell1.Offset(-cell1.Row + j, 0) Then GoTo Weiter
j = j - 1
Loop
Counter = Counter + 1 'Ansonsten Zählung
cell1.Offset(0, 2) = Counter
Exit For
End If
Weiter:
Next i
Else
Counter = Counter + 1 'Erste Zeile wird immer gezählt
cell1.Offset(0, 2) = Counter
End If
Next cell1
MsgBox "Zähler steht bei " & Counter
Set myRange = Nothing
Set myRange1 = Nothing
End Sub

Anzeige
AW: Zählen wenn, Zeitabhängigkeit
29.01.2019 10:17:02
Frank
Hallo Daniel,
Wahnsinn, vielen Dank für die tolle Arbeit - funktioniert super.
Klasse.
Gruß Frank.
;

Forumthreads zu verwandten Themen

SUMMEWENN oder ZÄHLENNWENN
Heinz

Hallo Leute
Ich bräuchte bitte eine Formel die Sinngemäß so geht.
Zählenwenn( O8:O38) R4
Könnte mir dazu bitte jemand helfen.
Danke & Gruß
Heinz
16.09.2008 18:38:02
Thread-ID: 1010175
12 Beiträge
Summewenn oder wenn oder ...
Karen

Hallo,
ich habe einen "HÄNGER". Ich möchte 9 Summen mit 2 verschiedenen Kriterien bilden.
Aber ich komme nicht drauf. Vielleicht muss ich in meiner Tabelle noch etwas umbauen.
https://www.herber.de/bbs/user/55571.xls
Zahlen Spale B
Text Spalte C
Text Spalte D
Im Kas...
22.09.2008 14:31:41
Thread-ID: 1011479
7 Beiträge
Summenprodukt mit drei Bedingungen
Katja

Hallo,
möchte gern etwas zusammenzählen wenn drei Bedingungen erfüllt sind:
- Saal 1, Saal 18 und dann die Zellen zusammenzählen die nicht leer sind.
Habe es so versucht, es klappt aber nicht. Wo ist mein Fehler?
=SUMMENPRODUKT(($D$6:$D$206="Saal 01")*($D6:$D$206="Saal 18")*(AC$...
10.10.2008 12:16:57
Thread-ID: 1015553
9 Beiträge
WENN/ODER Funktion
Dessin

Guten Abend
ich möchte gerne wissen, wieviele ODER-Fragen in eine WENN Funktion eingebunden werden können.
folgendes Beispiel:
Grundfunktion: wenn (X=wahr,dann A1 sonst B1)
meine Frage: wenn (oder(C1={"x";"y"};D1={"x";"y"});A1;B1)
C1 ist eine ODER Frage die ich, wie ich w...
27.10.2008 20:15:00
Thread-ID: 1019734
3 Beiträge
Anzeige
Wenn-Oder Formel über VBA funktioniert nicht
sharky1000

Hallo,
ich habe eine etwas komplexere Wenn-Oder Formel erstellt, welche ich über VBA in eine Zelle einfügen möchte.
Formel in E2:
=WENN(ODER(LINKS(D2;2)="M1";LINKS(D2;2)="M2";LINKS(D2;2)="M3";LINKS(D2;2)="M4";LINKS(D2;2)="M5"); LINKS(D2;1);WENN(LINKS(D2;2)="NM";LINKS(D2;2);WENN(LIN...
06.11.2008 14:11:01
Thread-ID: 1022285
7 Beiträge
Verbundene benannte Zellen kopieren
Peter

Guten Abend
Kürzlich habe ich einen Beitrag gepostet, in dem es um das Kopieren von Bereichen von Datei A (Upload 57416 mit Makro) nach Datei B (Upload 57417) ging.
Die verbundene Zellen sind in Quell- und Zieldatei mit "verbunden" benannt. Bisher habe ich noch keine Lösung gebunden, wi...
06.12.2008 23:17:04
Thread-ID: 1030780
3 Beiträge
wenn-oder-Funktion
Meli

Hallo,
ich habe hier das Beispieldatei eingefügt: https://www.herber.de/bbs/user/57552.xls
Ich verstehe nicht, was an meiner Formel ~=WENN(ODER(F8>1200;G8>1200;Z8>1200;F8 Ich möchte nämlich in Spalte AB, dass es eine 1 erscheint, wenn in Spalte F, G oder Z die Werte höher als...
11.12.2008 16:03:45
Thread-ID: 1032094
7 Beiträge
Summenprodukt pro Zeile mit Bedingung...
fix

Hallo zusammen!
Habe folgendes Problem: Ich muss ein Summenprodukt bilden. Spalte A ist mit a, b ,c gefüllt. In den Spalten B, C, D ist der Wert 1 oder kein Wert drin. Nun soll gezählt werden, wenn Bedingung in Spalte A erfüllt ist und in B, C oder D eine 1 steht. Das Ganze möchte ich gerne...
29.12.2008 14:51:57
Thread-ID: 1035760
3 Beiträge
Wenn und oder Abfrage
gerhard

Hallo, ich muss bei einer Resultattabelle an Hand einer Abfrage Punkte vergeben
wie kann ich dies am einfachsten bewerkstelligen:
Punktezahl von bis
1 -999.999.999,99 2,85
2 2,85 3,68
3 3,68 4,50
4 4,50 5,34
5 5,34 6,17
6 6,17 7,00
7 7,00 7,83
...
05.01.2009 12:49:02
Thread-ID: 1036944
13 Beiträge
Summewenn oder Produktwenn
Mine

Hallo Ihr lieben Excler,
ein Tag ging schon drauf, aber ich schaffe es nicht. Ich habe ein Beispiel angehängt. Kann sich das mal jemand ansehen. Womöglich geht das gar nicht.
Danke schon mal fürs Ansehen. Gruß Mine
https://www.herber.de/bbs/user/59206.xls
07.02.2009 20:49:02
Thread-ID: 1048145
3 Beiträge
Zählenwenn oder Anzahlwenn geht nicht..
Andreas

Hallo,
ich habe folgende Aufgabenstellung. Werte aus Spalte A und B selektieren. Die Werte der Zellen aus Spalte A sollten nur gezählt werden, wenn in den Zellen der Spalte B nicht das Wort Backup steht, also mit Ausschlusswerte sprich negierung.
Bei der Funktion Zählenwenn weiß ich nic...
04.06.2009 20:02:24
Thread-ID: 1079247
15 Beiträge
Zählenwenn / Summenprodukt aber wie?
Hans

Hallo .. ich habe ein Problem mit dem zusammenzählen bei einer bestimmten Bedingung.
In Spalte A steht das Datum in Form von MM.JJJJ (01.2007, 02.2007 ..... )
In Spalte B stehen Namen
Nun möchte ich für jeden Monat zusammenzählen wie oft z. b. "Hans" in jedem Monat vorkommt.
Ic...
24.06.2009 14:21:02
Thread-ID: 1084128
8 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige