Excel verfügt bereits über Funktionen wie SUMIF und SUMIFS zum Summieren von Daten nach Gruppen. Sie funktionieren jedoch nicht, wenn Sie Datums- und Zeitwerte kombiniert haben. Dieser Artikel zeigt, wie man in Excel mit den Funktionen SUMPRODUCT und INT nach Datum ohne Zeit summieren kann.
Syntax
=SUMPRODUCT( Wertebereich * ( INT( Wertebereich Datum-Zeit ) = Zelle Datum ) )
Schritte
- Beginnen Sie mit =SUMPRODUCT( Funktion
- Wählen Sie den Bereich aus, der Werte enthält $C$3:$C$16
- Verwenden Sie Sternchen, um die Werte des Bereichs mit der Bedingung Bereich und öffnen Sie eine Klammer *(
- Fahren Sie fort mit INT($B$3:$B$16), um den Zeitteil zu entfernen
- Bedingung mit Gleichheitszeichen und Kriterienbereich =E3 eingeben
- Typ )), um die Funktion zu schließen und die Formel zu beenden
Wie
Excel behält Datums- und Zeitwerte als Zahlen. Excel geht davon aus, dass die Historie am 1. Januar 1900 beginnt und akzeptiert dieses Datum als 1. Während ganze Zahlen für Tage stehen, steht die Dezimalzahl für die Zeit. Zum Beispiel: 1.1.2018 ist gleich 43101, 12:00 ist gleich 0,5.
Wir können die INT-Funktion verwenden, um die Zeitwerte oder den Dezimalteil von Zahlen loszuwerden. Die INT-Funktion gibt einfach den ganzen Zahlenteil eines numerischen Wertes zurück. In unserem Fall;
INT($B$3:$B$16) gibt {43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Um jedoch die INT-Funktion auf ein Array anzuwenden, müssen wir die SUMPRODUCT-Funktion verwenden. Die Fähigkeit der SUMPRODUCT-Funktion, mit Arrays umzugehen, ohne Array-Formeln zu verwenden, bietet einen Vorteil gegenüber den Einschränkungen der Funktionen SUMIF und SUMIFS. Die SUMPRODUCT-Funktion kann Arrays aus Bereichswertbedingungen auswerten und zurückgeben. Wir verwenden diese Funktionalität, um Kriterienbereich – Kriterienpaare aufzulösen.
(INT($B$3:$B$16)=E3) gibt {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Auch summiert die Funktion SUMPRODUCT die Werte in ihrem Argument-Array, wenn es nur ein Argument gibt. Bedingte Gleichungen geben jedoch boolesche Werte zurück. Sie müssen also in Zahlen umgewandelt werden, die für die SUMPRODUCT-Funktion sinnvoll sind. Glücklicherweise übernimmt der Produktoperator (*) diese Aufgabe.
Durch Multiplikation eines Werte-Arrays mit einem Bedingungs-Array erhalten wir die Array-Werte, die entsprechend unseren Anforderungen erzeugt werden.
$C$3:$C$16*(INT($B$3:$B$16)=E3) gibt {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0}
Schließlich gibt der SUMPRODUCT die Ergebnisse als Summe nach Datum in Excel zurück, was genau das ist, wonach wir suchen.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) gibt 10 für das Datum des 11.7.2018 zurück.
Alternative Option
Eine alternative Möglichkeit ist die Verwendung von PivotTable, die Ihnen mehr Optionen als die Summierung von Werten bieten kann. Detaillierte Informationen finden Sie in Wie man Tage ohne Uhrzeit mit einer Pivot-Tabelle gruppiert.
Siehe auch verwandte Artikel, wie man den gewichteten Durchschnitt mit SUMPRODUCT berechnet, wie man 2d Bereiche mit SUMPRODUCT summiert und wie man Werte mit dem Operator OR mit SUMPRODUCT mit mehreren Kriterien summiert.