Formel

Summe pro Quartal

Summe Pro Quartal

Summe pro Quartal

  Excel-Formel: Summe pro Quartal Allgemeine Formel
=SUMIFS(values,quarters,A1)
Zusammenfassung

Um Werte nach Quartal zu summieren, können Sie eine Formel verwenden, die auf dem basiert SUMIFS-Funktion zusammen mit A Hilfsspalte das Viertel enthält. Im gezeigten Beispiel lautet die Formel in G5:





4E4F7AA8E5E39C90BE8349AEEA3DA4674A0BBFA

wo Daten ist ein Excel-Tabelle im Bereich B5:D16, und die Quartale in Spalte E werden mit einer anderen unten beschriebenen Formel generiert.

Erläuterung

In diesem Beispiel besteht das Ziel darin, die Beträge in Spalte C nach Quartal zu summieren, wobei die Daten in Spalte B verwendet werden, um Quartale zu bestimmen. Die Quartalszahlen in Spalte F werden manuell eingegeben. Die endgültigen Ergebnisse sollten in Spalte G erscheinen. Alle Daten sind in einer Excel-Tabelle benannt Daten im Bereich B5:E16. Dieses Problem kann mit gelöst werden SUMIFS-Funktion und einer Hilfsspalte oder ohne Hilfsspalte mit der SUMPRODUCT-Funktion . Beide Vorgehensweisen werden im Folgenden erläutert.





Quartale berechnen

Der erste Schritt bei diesem Problem besteht darin, für jedes Datum in Spalte B ein Quartal zu generieren. In der angezeigten Tabelle ist Spalte D a Hilfsspalte mit Quartalszahlen berechnet eine separate Formel. Die Formel in D5, nach unten kopiert, lautet:

=ROUNDUP(MONTH([@Date])/3,0) // get quarter

Hinweis: Da wir eine verwenden Excel-Tabelle Um die Daten zu halten, erhalten wir automatisch die strukturierte Referenz oben gesehen. Die Referenz [@Date] bedeutet: aktuelle Zeile in der Datumsspalte. Wenn strukturierte Referenzen neu für Sie sind, sehen Sie sich dieses kurze Video an: Einführung in strukturierte Referenzen .



Das MONAT-Funktion gibt für jedes Datum eine Monatszahl zwischen 1-12 zurück, die durch 3 geteilt wird ROUNDUP-Funktion wird dann verwendet, um das Ergebnis auf die nächste ganze Zahl aufzurunden. Diese Formel ist hier näher erklärt .

SUMIFS-Lösung

Der nächste Schritt der Aufgabe besteht darin, die Beträge in Spalte C mit den Quartalszahlen in Spalte D zu addieren. Das geht ganz einfach mit dem SUMIFS-Funktion . Die SUMIFS-Funktion dient zum Summieren von Werten in Bereichen bedingt nach mehreren Kriterien. Die Signatur der SUMIFS-Funktion sieht folgendermaßen aus:

=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)

Beachten Sie die Summenbereich kommt zuerst, gefolgt von Reichweite/Kriterien Paare. Jedes Bereich/Kriterien-Argumentpaar repräsentiert eine andere Bedingung.

In diesem Fall müssen wir SUMIFS so konfigurieren, dass Werte nach Quartalsnummer summiert werden, wobei nur eine Bedingung verwendet wird: Wir müssen das Quartal in Spalte D auf eine Übereinstimmung im Quartal in Spalte F prüfen. Wir beginnen mit dem Summenbereich :

Rundung auf die nächsten 100 in Excel
=SUMIFS(data[Amount]

Als nächstes fügen wir Kriterien als a hinzu Reichweite/Kriterien Paar, wo Kriterien_Bereich1 ist der Datum Spalte und Kriterien1 ist die Quartalszahl in Spalte F:

4E4F7AA8E5E39C90BE8349AEEA3DA4674A0BBFA

Wenn die Formel nach unten kopiert wird, erhalten wir in Spalte F eine Gesamtsumme für jedes Quartal.

SUMPRODUCT ohne Helfer

Es ist auch möglich, dieses Problem ohne Hilfsspalte mit der SUMPRODUCT-Funktion wie folgt zu lösen:

=SUMPRODUCT((ROUNDUP(MONTH(data[Date])/3,0)=F5)*data[Amount])

Der erste Teil des Ausdrucks innerhalb von SUMPRODUCT generiert von innen nach außen eine Quartalszahl für jedes Datum in der Date-Spalte wie folgt:

ROUNDUP(MONTH(data[Date])/3,0)

Dies ist im Grunde dieselbe Formel, die oben verwendet wurde, der Unterschied besteht darin, dass wir die füttern MONAT-Funktion das ganze Daten Datum] Spalte statt einer Zelle. Da in der Spalte 12 Daten stehen, erhalten wir eine zurück Reihe das 12-Monats-Zahlen wie folgt enthält:

{1;2;3;4;5;6;7;8;9;10;11;12}

Dieses Array wird an die ROUNDUP-Funktion als übergeben Nummer Streit:

ROUNDUP({1;2;3;4;5;6;7;8;9;10;11;12}/3,0)

Und ROUNDUP gibt ein Array von 12 Quartalszahlen zurück:

{1;1;1;2;2;2;3;3;3;4;4;4}

Hinweis: Wir verwenden in diesem Beispiel der Einfachheit halber einen sehr kleinen Datensatz, aber der gleiche Ansatz wird Hunderte oder Tausende von Daten verarbeiten.

Als nächstes wird das Array von ROUNDUP mit F5 verglichen und das Ergebnis ist ein Array, das 12 TRUE- und FALSE-Werte enthält. Wenn dieses Array multipliziert wird mit Daten[Menge] , ändert die mathematische Operation die TRUE- und FALSE-Werte in 1s und 0s. An dieser Stelle haben wir:

=SUMPRODUCT({1;1;1;0;0;0;0;0;0;0;0;0}*data[Amount])

Die Multiplikation der beiden Arrays miteinander ergibt ein einzelnes Array. In diesem Array bleiben nur Beträge erhalten, die mit Quartal 1 verknüpft sind – Beträge für andere Quartale werden effektiv „auf Null gesetzt“:

=SUMPRODUCT({127;130;450;0;0;0;0;0;0;0;0;0})

Mit nur einem zu verarbeitenden Array summiert SUMPRODUCT die Werte im Array und gibt das Endergebnis 707 zurück. Wenn die Formel nach unten in Spalte G kopiert wird, gibt sie eine Summe für jedes Quartal zurück, ohne dass eine Hilfsspalte erforderlich ist.

Autor David Bruns


^