☰

Wie kann man in Excel eine enorme Zahl von Zeitstempeln zählen und die Zählungen in 5-Minuten-Intervallen nach Wochentagen sortieren?

tag-icon • tag-icon microsoft-excel worksheet-function
Wie kann man in Excel eine enorme Zahl von Zeitstempeln zählen und die Zählungen in 5-Minuten-Intervallen nach Wochentagen sortieren?

Ich habe etwas über 300.000 Zeilen in einer Tabelle und jede dieser Zeilen enthält einen Zeitstempel (HH:MM:SS AM) und ein Datum (TT/MM/JJJJ).

Ich möchte die Anzahl dieser Zeitstempel erhalten und sie in 5-Minuten-Abständen nach Wochentag gruppieren (das heißt, ich hätte gerne die Anzahl der Zeitstempel zwischen 00:00 Uhr und 00:05 Uhr am Sonntag, 00:05 Uhr und 00:10 Uhr am Sonntag usw.). Ich habe das Gefühl, dass ich das COUNTIFirgendwie nutzen muss, aber ich weiß nicht genau, wie ich es implementieren soll. Ich versuche, die Ergebnisse in einem anderen Blatt zu organisieren, das ungefähr so ​​aussieht.

Bildbeschreibung hier eingeben

Die Anzahl für jeden 5-Minuten-Zeitraum würde in die mittlere Spalte unter dem Wochentag eingetragen (also dort würde ich die Formel eingeben).

Vielen Dank im Voraus für jede Hilfe, das weiß ich wirklich zu schätzen!

BEARBEITEN: Ich hätte wahrscheinlich hinzufügen sollen, dass meine Daten so aussehen:

Bildbeschreibung hier eingeben

Antwort1

Sie können Ihren Daten einige Felder hinzufügen und dann eine Pivot-Tabelle generieren, um die Zählungen zu erhalten.

Fügen Sie zunächst ein Feld für den Wochentag ( DOW) hinzu. DOWVerwenden Sie dazu die folgende Formel, wobei die Spalte Bdas Datum enthält.

=CHOOSE(WEEKDAY(B2,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Fügen Sie als Nächstes ein Feld hinzu, das den 5-Minuten-Zeitraum darstellt, in den der Zeitstempel fällt. Time RangeVerwenden Sie zur Berechnung die folgende Formel, wobei sich der Zeitstempel in der Spalte befindet A.

=TIME(HOUR(A2),ROUNDDOWN(MINUTE(A2)/5,0)*5,0)

Nachdem Sie diese Spalten zu Ihren Daten hinzugefügt haben, erstellen Sie eine Pivot-Tabelle, die alle Ihre Daten als Datenquelle verwendet.

Richten Sie Ihre Pivot-Tabelle mit DOWals Spaltenbeschriftungen und Time Rangeals Zeilenbeschriftungen ein. Legen Sie die Werte auf fest Count of Time Range.

Bildbeschreibung hier eingeben

Hinweis: Dieses Beispiel sieht etwas komisch aus, da ich zur Generierung der Pivot-Tabelle nur spärliche Daten (20 Datensätze über eine Woche) verwendet habe. Es sollte eher so aussehen, wie Sie es sich für Ihren großen Datensatz vorstellen.

Antwort2

Ähnlich wie von @Excellll empfohlen, können Sie eine zusätzliche Spalte hinzufügen, die den Wochentag angibt. In Blatt 1, wo Ihre Rohdaten vorhanden sind und die Spalten A und B jeweils Datum und Uhrzeit enthalten, können Sie diese Funktion in Spalte C verwenden.

=WENN(ODER(ISTLEER(A1),ISTLEER(B1)),"",WOCHENTAG(A1))

Der Ansatz, den ich verwende, basiert rein auf Funktionen. Wie das Bild, das Sie geteilt haben, habe ich Formeln für Blatt 2 angepasst, um eine ähnliche Struktur zu erzeugen. Die folgende Bedingung prüft, ob Zeitstempel in einem bestimmten Bereich vorhanden sind, wie in der ersten Spalte der Tabelle angegeben, und zusätzlich, ob es sich um einen bestimmten Wochentag handelt. Dann zählt sie alle diese Werte.

=ZÄHLENWENN(Tabelle1!$B:$B, ">"&$A3,Tabelle1!$B:$B,"<="&$A4,Tabelle1!$C:$C,"="&B$2)

Das Bild zeigt, wie die obige Formel in Zelle B1 eingegeben wird. Es gelten die Bedingungen, um zu überprüfen, ob die Zeit im Bereich zwischen 00:00 und 00:10 Uhr liegt und der Tag auf Sonntag oder 1 eingestellt ist.

Bildbeschreibung hier eingeben

Wie Sie vielleicht bemerkt haben, beträgt der Zeitunterschied hier 10 Minuten. Ich habe ihn anpassbar gemacht, sodass Sie ihn je nach Bedarf auf 5 Minuten oder 2 Stunden einstellen können.

Da wir hier Funktionen verwenden, spielt es keine Rolle, ob Ihre Daten 4 oder 10 leere Zeilen enthalten. Die Funktionen stellen sicher, dass nur nicht leere Daten erfasst werden. Ich hatte nicht genügend Daten, daher sieht das Beispiel ziemlich leer aus. Die Beispiel-XLS finden Sie im AnhangHier.

Antwort3

Hinweis: Ich werde diesen Code heute Abend beheben, da beim Durchlaufen der Werteliste immer noch einige Fehler auftreten. Funktioniert derzeit nicht, ich möchte meine Codezeilen einfach nicht neu formatieren. Probieren Sie ruhig ein bisschen herum. Ich werde gleichzeitig Kommentare und Anweisungen hinzufügen.

Jemand meinte, dass dies mit VBA möglich sei. Ich mag VBA, also habe ich es mal ausprobiert.

Ein paar Annahmen:

1 – Der Zeitstempel und das Datum befinden sich nicht in derselben Zelle (z. B. 00:00:00 Uhr | 21.07.2014 NICHT 21.07.2014 00:00:00)

2 – Sie möchten alle Zeitstempelzählungen in einer einzigen Tagesliste gruppieren (d. h. nur einen Satz von Sonntag bis Samstag anzeigen und nicht für jeden zusätzlichen Tag einen neuen Satz Spalten erstellen – wenn wir an einem Freitag beginnen würden, würden wir die Gruppierung nicht an einem Freitag starten, und wenn wir an einem Dienstag zwei Wochen später enden würden, hätten wir nicht 16 Gruppierungsspalten).

3 – Von der ersten bis zur letzten Zeile sind in Ihren Daten keine Nullzellen vorhanden.

4 - Ihre Zeitstempel- und Datumsstempeldaten haben Header

Sie sollten Alt+F11 drücken, die Arbeitsmappe öffnen, in der sich die Daten und die Quellarbeitsblätter befinden, und diesen Code eingeben. Drücken Sie dann F5.

Public Sub PrintDateGroups()
Dim icontrol As Integer
Dim iweeknum As Integer
Dim ipasscount As Integer
Dim lngwalktimevalues As Long
Dim ipasstimecount As Integer
icontrol = 1

Do Until icontrol = -1:
If ThisWorkbook.Sheets("Data").Cells(icontrol,2).Value = "" Then
    icontrol = -1

Else

    icontrol = icontrol + 1
    iweeknum = Weekday(ThisWorkbook.Sheets("Data").Cells(icontrol, 1).Value, vbSunday)

    For lngwalktimevalues = 0 To 99999999 Step 694444.4375
        If (TimeValue(Format(ThisWorkbook.Sheets("Data").Cells(icontrol, 2).Value, "hh:mm:ss")) * 100000000) <= lngwalktimevalues Then
            If iweeknum = 1 Then
                If ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value <> "" Then
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, 2).Value + 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = 1
                    Exit For
                End If
            Else
                If ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = "" Then
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, ((3 * iweeknum) + (iweeknum - 2))).Value + 1
                    Exit For
                End If
            End If
        End If
    Next lngwalktimevalues
End If
Loop
End Sub

Antwort4

Sie sollten in der Lage sein, jeden Datensatz auf die nächsten fünf Minuten zu runden. Verwenden Sie dann die Zwischensummenfunktion von Excel, um die Zählungen zu erhalten.

Ich würde die Rundung durchführen, indem ich eine neue Spalte mit dem Datum (=Tag(A1)) und eine zweite Spalte mit dem Zeitstempel erstelle.

verwandte Informationen