Wie erstelle ich eine Summierung für mehrere Spalten in Excel?

Question

Ich habe eine durch Kommas getrennte Datei mit zwei Spalten, Datum/Uhrzeit (die als mm/dd/yyyy hh:mmbenutzerdefiniertes Excel-Format importiert werden) und Status 1 oder 0. Der Status stellt dar, ob ein Gerät ein- oder ausgeschaltet ist. Ich versuche, ein Diagramm zu erstellen, das die Stundenzahl nach Tag zeigt.

HALTEN:

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

Daraus erkenne ich, dass die Anlage drei Stunden lang in Betrieb war, elf Stunden lang ausgefallen und dann vierunddreißig Stunden lang (über zwei Kalendertage hinweg). Ich möchte jedoch ein Diagramm erstellen, das zeigt, wie viele Stunden PRO TAG die Anlage in Betrieb oder ausgefallen war.

HALTEN:

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

Mir scheint, ich muss einen Datensatz generieren, der STUNDEN nach STATUS nach KALENDERTAG summiert … aber ich kann anscheinend keine Art von Pivot-Tabelle oder verschachtelte SUM(IF(SUMIF(...)))Kombination finden, mit der das funktioniert.

Am problematischsten ist die Berücksichtigung von Datumsänderungen. Da in meinem obigen Beispiel meine Betriebszeit, die am 1.1.2012 um 14:00 Uhr beginnt und Mitternacht überschreitet, muss ich wissen, dass sich am 1.1.2012 insgesamt 10 Betriebsstunden und am 2.1.2012 insgesamt 24 Betriebsstunden ergeben.

Ich kann vielleicht etwas mit einer Kalenderliste machen, um die Datumssummierung zu steuern, aber dann brauche ich eine Möglichkeit, 01/01/2012sie 01/01/2012 03:00als gleich zu vergleichen. Es muss einen Weg in dieser Art geben, if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)aber bisher hat nichts funktioniert.

Irgendwelche Vorschläge? Ich kämpfe schon fast den ganzen Tag damit und brauche eine neue Perspektive.

Danke

Answer 1

OMGBBQ, das ist eine Herausforderung! Ich wünschte, ich könnte es besser erklären, aber bitte haben Sie Geduld mit mir.

Meine Lösung erfordert zusätzlichen Platz – 3 Spalten, um genau zu sein – und basiert auf einigen Annahmen:

1. Die Zeitstempeldaten würden abwechselnde Zeilen mit Einsen und Nullen aufweisen und so einen Ein-/Ausschalter emulieren, wie Sie ihn beschrieben haben (siehe Bild 1 unten).
2. Die Zeitstempel werden vom ältesten zum neuesten sortiert.

Der Einfachheit halber verwende ich ganze Stunden. Sie können jedoch auch Zeitstempel auf Minutenebene hinzufügen, wenn Sie dies bevorzugen.

Spalte Everfolgt die Anzahl der verbleibenden Stunden des Tages zu jedem Zeitstempel. Ich habe dies verwendet, um verstrichene Zeiten, die über Mitternacht hinausgehen, zu „SPLITTEN“.

EIN/AUS-Spalten

Hier ist die Formel in der Spalte „ON“:

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

Diese Formel addiert zwei Werte:

1.Die Anzahl der Stunden, in denen der Schalter eingeschaltet ist, wenn der Eintrag nicht der letzte Zeitstempel für den Tag ist, andernfalls 0.

2.Die Anzahl der verbleibenden StundenbisMitternacht, wenn dies diezuletztZeitstempel für den TagODERdie Anzahl der verstrichenen StundenseitMitternacht, wenn dies dieErsteZeitstempel für den Tag.

Die Formel in der Spalte „AUS“ ist ähnlich, außer dass sie prüft, ob der Status = 0 ist.

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

Um das DATUM aus der Zeitstempelspalte zu extrahieren, habe ich die INT()Funktion verwendet. Excel verwendet Ganzzahlen zur Darstellung von Daten und Dezimalzahlen zur Darstellung von Zeiten (ein Bruchteil eines Tages oder von 24 Stunden). Beispielsweise 06/01/2012 18:00ist gleich 41061.75(18 Stunden nach Mitternacht ist 18/24, was 0,75 ist).Ich glaube, das beantwortet Ihren letzten Absatz.

Konsolidierung der Stunden

Hier ist die Formel imStunden EINSpalte (siehe Abbildung unten). Es handelt sich um eine Matrixformel, daher müssen Sie sie mit Ctrl+ Shift+ eingeben Enterund dann nach unten kopieren:

=SUM((INT(stamps)=$G3)*hours_on)

ImStunden OFFSpalte:

=SUM((INT(stamps)=$G3)*hours_off)

wobei
stampsein benannter Bereich ist, der sich auf dieZeitstempelBereich (SpalteAin meinem Beispiel)
hours_onist ein benannter Bereich, der sich auf dieANBereich (SpalteC)
hours off ist ein benannter Bereich, der sich auf denAUSBereich (SpalteD)

Diagramm

Beachten Sie, dass sich für jedes Datum die Anzahl der EIN- und AUS-Stunden auf 24 beläuft.

Wenn Sie die Formeln und das Arbeitsbuch studieren möchten, finden Sie hier eine Kopie:http://db.tt/KZgH7SFV

Answer 2

Angenommen, Sie haben Zeitstempel in A3:A24 und Status in B3:B24 gemäß Kazes Vorschlag, dann können Sie die [Dezimal-]Stunden ONfür ein Datum in D3 mit dieser Formel erhalten

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

keine „Hilfs“-Spalten erforderlich …

Answer 3

Es ist nicht schön, aber hier ist eine Lösung:

Fügen Sie nach Ihren beiden Spalten eine dritte Spalte hinzu, die als Tag ([Datumsspalte]) definiert ist.

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

Dann können Sie Summenspalten definieren als

sumif(D1:D99, 1, C1:C99)

Dadurch wird Ihnen im obigen Fall die Summe der Einsen nur dann angezeigt, wenn Spalte D (zuvor definiert als der „Tag“-Teil des Datums und der Uhrzeit) 1 ist. Wiederholen Sie diese Formel für jeden Tag des Monats (der Teil, der mir nicht so gut gefällt), und Sie sollten Ihre Werte haben.

Stundensumme am 1. des Monats

sumif(D1:D99, 1, C1:C99)

Stundensumme am 2. des Monats

sumif(D1:D99, 2, C1:C99)

Stundensumme am 3. des Monats

sumif(D1:D99, 3, C1:C99)

und so weiter...