☰

Wie kann ich serielle Daten in Excel vergleichen, OHNE sie in Daten des Typs TT/MM/JJ zu konvertieren?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Wie kann ich serielle Daten in Excel vergleichen, OHNE sie in Daten des Typs TT/MM/JJ zu konvertieren?

Ich habe eine Tabelle, die eine Reihe von Werten enthält, die serielle Daten in Excel darstellen. Nach mehreren erfolglosen Versuchen, Felder zu vergleichen, besteht mein aktueller Ansatz darin, Vergleiche zwischen seriellen Daten statt zwischen Kalenderdaten durchzuführen. Ich versuche, die Daten – nach TAG – mit Formeln zusammenzufassen.

HALTEN:

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

ERWÜNSCHTES ERGEBNIS:

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

Im Wesentlichen werden für alle Instanzen von SerialDate.SerialTime die mit SerialDate.* verknüpften Datenwerte summiert, unabhängig von *.SerialTime für dieses Datum.

Ich sehe zwar, wie ich das erreichen kann, indem ich zusätzliche Datumsspalten in der Formatierung erstelle, aber =TEXT(<DateField>,"mm/dd/yyyy")ich suche nach einer Lösung, die es mir ermöglicht, diese „Konvertierung“ in der Formel zu handhaben, z. B.SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

Ergibt das Sinn? Hat jemand eine Idee?

Danke

Antwort1

Angenommen, Ihre Serienzeiten befinden sich in Spalte A und die zugehörigen Daten in Spalte B, dann lautet die folgende Formel

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

zeigt die Summe hinter dem ersten ganzzahligen Zeitwert in Spalte C an, wenn er in C1 eingegeben und auf die Spalte erweitert wird. Wenn das alles ist, was Sie brauchen, sind Sie fertig.

Wenn Sie jedoch alle Ihre Summen untereinander und ohne Leerzeilen dazwischen benötigen, müssen Sie eineArray-Formelund fügen Sie eine zusätzliche Spalte für die Referenzbeschriftungen ein. Wählen Sie in Spalte C die Zellen in den Zeilen aus, die Daten enthalten (wenn Ihre Daten also die Zeilen 1 bis 100 ausfüllen, wählen Sie C1 bis C100 aus) und fügen Sie die folgende Formel in die Formelleiste ein (nicht direkt in die Zelle!):

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

Beachten Sie, dass Sie es als Array-Formel speichern müssen, damit es funktioniertindem Sie Ihre Formeleingabe mit bestätigen Ctrl+Shift+Enter(nicht Enter– wenn Sie es richtig gemacht haben, wird die Formel in Klammern angezeigt). Sie müssen auch den Umfang nach Bedarf anpassen, da die Formel für leere Zellen 0 zurückgibt. Beachten Sie schließlich, dass Array-Formeln bei großen Datensätzen ziemlich laa ...

Sobald Sie fertig sind, können Sie hinzufügen

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

zu Spalte D und da sind Sie:

Array-Formel in Aktion

Antwort2

Wenn Sie versuchen, Daten nach Tagen zusammenzufassen (oder eine tägliche Zwischensumme zu erhalten), können Sie diese Matrixformel ausprobieren. Geben Sie diese in die Formelleiste ein und drücken Sie dann Ctrl+ Shift+ Enter.:

=SUM((INT(datetimes)=D3)*somedata)

Wo

  • datetimesenthält Ihre Seriendaten im Format Generalodermm/dd/yyyy h:mm
  • somedataenthält einige zu addierende Zahlen (B2:B25 in meinem Beispiel unten)

Bildbeschreibung hier eingeben

Excel betrachtet Datumsangaben als ganze Zahlen (Serienwerte) und Uhrzeiten als Dezimalzahlen (ein Bruchteil von 24 Stunden). Beispielsweise 06/01/2012 3:00:00 PMist gleich 41061.625(was Sie sehen, wenn Sie das Zellenformat in ändern General). Die ganze Zahl stellt das Datum dar, während oder oder 0.625darstellt . Um das DATE-Segment zu extrahieren, können Sie verwenden .3:00 pm15:0015/24INT(A1)

Antwort3

Am einfachsten geht dies mit den in Excel (2007/2010) integrierten Tabellen- und PivotTabellenfunktionen.

  1. Erstellen Sie aus Ihren Daten eine Excel-Tabelle.
  2. Erstellen Sie eine Hilfsspalte für das Datum mit der Excel-Funktion „INT“, die alle Werte auf den entsprechenden Tag herunterrechnet (den ganzzahligen Teil Ihres Datums-/Uhrzeitfelds). Sie können auch ganz einfach eine Hilfsspalte für den Zeitwert Ihres Felds erstellen.
  3. Erstellen Sie eine Pivot-Tabelle mit Ihrer Datentabelle als Quelle.
  4. Legen Sie Ihre Zeilenbeschriftungen als Hilfsdaten und die Pivot-Werte als Datenpunkte fest (und Sie können das Zeitfeld als Spaltenbeschriftungen hinzufügen).

Wenn Sie dann Ihre Tabelle aktualisieren, können Sie Ihre Pivot-Tabelle mit den aktuellen Zwischensummen aktualisieren. Als Bonus können Sie auch eine der anderen Aggregatfunktionen (Min., Max., Durchschnitt) auswählen und die Beschriftungen der Pivot-Tabelle in einem herkömmlichen Datumsformat formatieren, ohne dass dies Auswirkungen auf die Werte Ihrer Datentabelle hat.

Wenn Sie Excel 2003 verwenden, können Sie dasselbe mit der Listenfunktion (anstelle von Tabellen) erreichen.

Beispiel Excel 2010

Excel 2010

Beispiel Excel 2003

Excel 2003

verwandte Informationen