☰

Excel - Summieren Sie aufeinanderfolgende Daten im 30-Minuten-Intervall

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - Summieren Sie aufeinanderfolgende Daten im 30-Minuten-Intervall

Ich habe Daten von einem Regenmesser im folgenden Format gesammelt:

Zeitstempel der Indexdaten

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T01:00:00
3. 1    2020-01-05T01:20:15
4. 0    2020-01-05T02:00:00
5. 1    2020-01-05T02:09:00
6. 1    2020-01-05T02:09:45
7. 1    2020-01-05T02:20:00
8. 1    2020-01-05T02:20:01
9. 0    2020-01-05T03:00:00

Ich möchte alle 30 Minuten die aufeinanderfolgenden Daten summieren. Etwa wie folgt:

Zeitstempel der Indexdaten

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T00:30:00
3. 0    2020-01-05T01:00:00
4. 1    2020-01-05T01:30:00
5. 0    2020-01-05T02:00:00
6. 4    2020-01-05T02:30:00
7. 0    2020-01-05T03:00:00

Ich habe bereits nur die 24-Stunden-Zeit aus dem Zeitstempel erhalten und versucht herauszufinden, ob das Subtrahieren von 2 Zeitstempeln gemäß der folgenden Formel weniger als 30 Minuten ergibt:

=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")

Die Excel-Formel, die ich im Sinn hatte, sollte etwas in der Art einer SUMIFSFunktion ergeben, die alle aufeinanderfolgenden Werte innerhalb eines 30-Minuten-Intervalls summiert

Ich habe versucht, eine Art Überprüfung einzurichten, um innerhalb des 30-Minuten-Intervalls auf aufeinanderfolgende Daten zu prüfen und habe versucht, eine und eine Funktion COUNTIFauszuarbeiten , aber ich habe Schwierigkeiten, die Dinge zusammenzufügen und die Logik zu verstehen.SUMIFSSUMPRODUCT

Danke

Antwort1

Sie können diese Formel verwenden:

=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

Bildbeschreibung hier eingeben

In Schritte unterteilt:

  1. Damit Excel den Text als Datum/Uhrzeit erkennt, entfernen Sie das T:

    =ERSATZ(C2,"T"," ")

  2. So rufen Sie das Datum von diesem Datum/dieser Uhrzeit ab:

    =DATUMWERT(D2)

  3. So ermitteln Sie die Uhrzeit für dieses Datum/diese Uhrzeit:

    =RUNDE(ZEITWERT(D2),5)

(Warum das RUNDE hier nötig ist, erkläre ich weiter unten)

  1. So berechnen Sie das nächste 30-Minuten-Inkrement:

    =DECKE.MATHEMATIK(RUNDEN(ZEITWERT(ERSATZ(C2,"T"," ")),5),"0:30")

Wenn Sie dann das Datum mit dem 30-Minuten-Inkrement verknüpfen, erhalten Sie die Formel oben in der Antwort. Sie können die Tabelle dann pivotieren, um den Wert mit dem Inkrement zu summieren.

Hinweis zum Aufruf von ROUND in Schritt 3:

Als ich die Formel zum ersten Mal schrieb, habe ich in Schritt 3 ROUND nicht verwendet, aber ich habe festgestellt, dass das Inkrement für 2:00:00 2:30:00 betrug, was nicht richtig war. Bei der Überprüfung stellte ich fest, dass die Funktion TIMEVALUE nicht die richtige Genauigkeit zurückgab. Das Runden auf 5 Dezimalstellen hat das Problem behoben.

Bildbeschreibung hier eingeben

BEARBEITEN:

Da Sie über Excel 365 verfügen, gehe ich davon aus, dass Sie auch Zugriff auf die Funktion SEQUENCE haben.

Zum Aggregieren ohne Pivot-Tabelle können Sie Folgendes tun (vorausgesetzt, Sie verfügen nicht über eine bereits vorhandene Liste mit Inkrementen):

  1. Berechnen Sie die Anzahl der 30-Minuten-Schritte zwischen der MIN-Zeitstempelgruppe und der MAX-Zeitstempelgruppe
  2. Erstellen Sie eine Sequenz mit entsprechend vielen Inkrementen (sodass auch Inkremente dargestellt werden, die nicht in den Quelldaten enthalten sind).
  3. Verwenden Sie SUMIF, um auf die Quelldaten zurückzugreifen und die Daten nach TimestampGroup zu summieren
  4. Optionales Erstellen eines GroupIndex für die aggregierte Tabelle

Bildbeschreibung hier eingeben

Die Formeln in der aggregierten Tabelle lauten:

GruppenIndex:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)

Dies sagt Ihnen lediglich, wie viele 30-Minuten-Schritte zwischen der kleinsten und der größten Zeitstempelgruppe liegen, und gibt eine Ganzzahlfolge für diese Anzahl von Schritten zurück.

Zeitstempelgruppe:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)

Geben Sie mithilfe der Sequenzfunktion erneut eine Liste von Zeitstempelgruppen zurück. Anstatt die Sequenz jedoch bei 1 zu starten und um 1 zu erhöhen (was der 3. und 4. Standardparameter ist), starten Sie die Sequenz bei der minimalen Zeitstempelgruppe und erhöhen Sie sie um 30 Minuten, also 1/48 eines Tages.

Wert:

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

Summieren Sie die Datenspalte für die durch die TimestampGroup angegebenen Zeilen in der aktuellen Zeile der aggregierten Tabelle.

verwandte Informationen