2 Ways to Sum nach Monat in Excel
Oft möchten wir einige Werte pro Monat berechnen. Wie viel Umsatz wurde in einem bestimmten Monat gemacht. Nun, dies kann einfach mithilfe von Pivot-Tabellen durchgeführt werden. Wenn Sie jedoch versuchen, einen dynamischen Bericht zu erstellen, können Sie eine SUMPRODUCT- oder SUMIFS-Formel verwenden, um die Summe nach Monat zu berechnen.
Beginnen wir mit der SUMPRODUCT-Lösung.
Hier ist die generische Formel, um die monatliche Summe in Excel
zu erhalten
=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text))
Sum_range: Dies ist der Bereich, den Sie nach Monat summieren möchten.
Datumsbereich: Dies ist der Datumsbereich, in dem Sie nach Monaten suchen.
Month_text: Dies ist der Monat im Textformat, dessen Werte Sie summieren möchten.
Sehen wir uns nun ein Beispiel an:
Beispiel: Summenwerte nach Monat in Excel Hier haben wir einen Wert, der mit Datumsangaben verknüpft ist. Diese Daten sind von Januar, Februar und März des Jahres 2019.
Wie Sie im Bild oben sehen können, stammen alle Daten aus dem Jahr 2019. Jetzt müssen wir nur noch die Werte in E2: G2 nach Monaten in E1: G1 summieren.
Um nun die Werte nach Monaten zu summieren, schreiben Sie diese Formel in E2:
=SUMPRODUCT(B2:B9,--(TEXT(A2:A9,"MMM")=E1)))
Wenn Sie es in benachbarte Zellen kopieren möchten, verwenden Sie link: / excel-generals-relative-and-absolute-reference-in-excel [absolute Referenzen]
oder `link: / excel-range-name-all-about- named-range-in-excel [benannte Bereiche] `wie Sie im Bild.
Dies gibt uns die genaue Summe jedes Monats.
Wie funktioniert es?
Schauen wir uns von innen die
an === link: / excel-text-formeln-die-text-funktion-in-excel [TEXT]
(A2: A9, „MMM“)
Teil. Hier extrahiert die TEXT-Funktion einen Monat aus jedem Datum im Bereich A2: A9 im Textformat in ein Array. Übersetzung in Formel zu = SUMPRODUCT (B2: B9, [. Custom-span] # – # [. Custom-span] # – # (\ {„Jan“; „Jan“; „Feb“; „Jan“; „Feb. „;“ Mar „;“ Jan „;“ Feb „} = E1))
Als nächstes ist TEXT (A2: A9, „MMM“) = E1: Hier wird jeder Monat im Array mit dem Text in E1 verglichen. Da E1 „Jan“ enthält, wird jedes „Jan“ im Array in TRUE und das andere in FALSE konvertiert. Dies übersetzt die Formel in = SUMPRODUCT ($ B $ 2: $ B $ 9, – \ {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Weiter [.custom-span] # – # [. Custom-span] # – # (TEXT (A2: A9, „MMM“) = E1) konvertiert TRUE FALSE in Binärwerte 1 und 0. Die Formel übersetzt in = SUMPRODUCT ($ B $ 2: $ B $ 9, \ {1; 1; 0; 1; 0; 0; 1; 0}).
Schließlich SUMPRODUCT ($ B $ 2: $ B $ 9, \ {1; 1; 0; 1; 0; 0; 1; 0}): Die SUMPRODUCT-Funktion multipliziert die entsprechenden Werte in $ B $ 2: $ B $ 9 mit Array \ { 1; 1; 0; 1; 0; 0; 1; 0} und addiert sie. Daher erhalten wir die Summe nach Wert als 20052 in E1.
ZUSAMMENFASSUNG WENN Monate aus einem anderen Jahr Im obigen Beispiel stammten alle Daten aus demselben Jahr. Was wäre, wenn sie aus verschiedenen Jahren stammten? Die obige Formel summiert die Werte nach Monat, unabhängig vom Jahr. Zum Beispiel werden Jan von 2018 und Jan von 2019 hinzugefügt, wenn wir die obige Formel verwenden. Was in den meisten Fällen falsch ist.
Dies geschieht, weil wir im obigen Beispiel keine Kriterien für das Jahr haben. Wenn wir auch Jahreskriterien hinzufügen, wird es funktionieren.
Die generische Formel zum Abrufen der Summe nach Monat und Jahr in Excel
=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text),--( TEXT(date_range,"yyyy")=TEXT(year,0)))
Hier haben wir ein weiteres Kriterium hinzugefügt, das das Jahr überprüft. Alles andere ist das gleiche.
Lassen Sie uns das obige Beispiel lösen und diese Formel in Zelle E1 schreiben, um die Summe von Jan im Jahr 2017 zu erhalten.
=SUMPRODUCT(B2:B9,--(TEXT(A2:A9,"MMM")=E1),--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)))
Verwenden Sie vor dem Kopieren in die folgenden Zellen benannte Bereiche oder absolute Referenzen.
Im Bild habe ich benannte Bereiche zum Kopieren in die benachbarten Zellen verwendet.
Jetzt können wir auch die Wertesumme nach Monaten und Jahren sehen.
Wie funktioniert es?
Der erste Teil der Formel ist der gleiche wie im vorherigen Beispiel. Lassen Sie uns den zusätzlichen Teil verstehen, der die Jahreskriterien hinzufügt.
link:bbbb
=== (A2: A9, „yyyy“) = TEXT (D2,0)):
TEXT (A2: A9, „JJJJ“) konvertiert das Datum in A2: A9 als Jahre im Textformat in ein Array. \ {„2018“; „2019“; „2017“; „2017“; „2019“; „2017“; „2019“; „2017“}.
Meistens wird das Jahr im Zahlenformat geschrieben. Um eine Zahl mit Text zu vergleichen, habe ich Jahr-Int-Text mit TEXT (D2,0) konvertiert. Als nächstes verglichen wir dieses Textjahr mit einer Reihe von Jahren als link: / excel-text-formeln-die-text-funktion-in-excel [TEXT]
(A2: A9, „yyyy“) = TEXT (D2,0) .
Dies gibt ein Array von true-false \ {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE} zurück. Als nächstes haben wir true false mit dem Operator [.custom-span] # – # [. Custom-span] # – # in number konvertiert.
Dies gibt uns \ {0; 0; 1; 1; 0; 1; 0; 1}.
Schließlich wird die Formel übersetzt in = SUMPRODUCT (B2: B9, \ {1; 1; 0; 1; 0; 0; 1; 0}, \ {0; 0; 1; 1; 0; 1; 0) ; 1}). Wobei das erste Array Werte sind. Der nächste ist der passende Monat und der dritte das Jahr. Schließlich erhalten wir unsere Wertesumme als 2160.
* Verwenden der SUMIFS-Funktion zum Summieren nach Monat
===
Generische Formel
=SUMIFS(sum_range,date_range,”>=” & startdate, date_range,”<=” & EOMONTH(start_date,0))
Hier Sum_range: Dies ist der Bereich, den Sie nach Monat summieren möchten.
Datumsbereich: Dies ist der Datumsbereich, in dem Sie nach Monaten suchen.
Startdatum: Dies ist das Startdatum, ab dem Sie summieren möchten. In diesem Beispiel ist es der 1. des angegebenen Monats.
Beispiel: Summenwerte nach Monat in Excel
Hier haben wir einen Wert, der mit Daten verbunden ist. Diese Daten sind von Januar, Februar und März des Jahres 2019.
Wir müssen diese Werte nur bis zu diesem Monat summieren. Jetzt war es einfach, wenn wir Monate und Jahre getrennt hatten. Aber das sind sie nicht. Wir können hier keine Hilfsspalte verwenden.
Um einen Bericht zu erstellen, habe ich ein Berichtsformat erstellt, das Monat und Summe der Werte enthält. In der Monatsspalte habe ich tatsächlich ein Startdatum des Monats. Um nur den Monat anzuzeigen, wählen Sie das Startdatum und drücken Sie STRG + 1.
Schreiben Sie im benutzerdefinierten Format „mmm“.
Jetzt haben wir unsere Daten bereit. Summieren wir die Werte nach Monat.
Schreiben Sie diese Formel in E3, um sie nach Monat zu summieren.
=SUMIFS(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMONTH(D3,0))
Verwenden Sie link: / excel-generals-relative-und-absolute-referenz-in-excel [absolute referenzen]
oder `link: / excel-bereichsname-alles-über-benannte-bereiche-in-excel [benannte bereiche ] `vor dem Kopieren der Formel.
Also, endlich haben wir das Ergebnis.
Also, wie funktioniert es?
Wie wir wissen, kann die SUMIFS-Funktion Werte für mehrere Kriterien summieren.
Im obigen Beispiel ist das erste Kriterium die Summe aller Werte in B3: B10, wobei das Datum in A3: A10 größer oder gleich dem Datum in D3 ist. D3 enthält 1-Jan. Dies übersetzt auch.
=SUMIFS(B3:B10,A3:A10,">="& “1-jan-2019” ,A3:A10,"<="EOMONTH(D3,0))
Das nächste Kriterium ist nur dann eine Summe, wenn das Datum in A3: A10 kleiner oder gleich EOMONTH (D3,0) ist.
link: / excel-date-time-formeln-finden-den-letzten-tag-eines-bestimmten-monats [EOMONTH]
Die Funktion gibt nur die Seriennummer des zuletzt angegebenen Monats zurück.
Schließlich übersetzt auch die Formel.
=SUMIFS(B3:B10,A3:A10,">=1-jan-2019” ,A3:A10,"<=31-jan-2019”)
Daher erhalten wir die monatliche Summe in Excel.
Der Vorteil dieser Methode ist, dass Sie das Startdatum für die Summierung der Werte anpassen können.
Wenn Ihre Daten andere Jahre haben als es ist, ist es am besten, Pivot-Tabellen zu verwenden.
Mithilfe von Pivot-Tabellen können Sie die Daten problemlos im jährlichen, vierteljährlichen und monatlichen Format trennen.
Also ja Leute, so können Sie Werte pro Monat summieren. Beide Wege haben ihre eigenen Spezialitäten. Wählen Sie, wie Sie möchten.
Wenn Sie Fragen zu diesem Artikel oder zu anderen Excel- und VBA-Fragen haben, ist der Kommentarbereich für Sie geöffnet.
Artikel in Beziehung setzen:
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]
link: / tips-sumifs-with-date-in-excel [SUMIFS mit Daten in Excel]
link: / tips-excel-sumif-not-blank-cells [SUMIF mit nicht leeren Zellen]
link: / summing-excel-sumifs-function [Verwendung der SUMIFS-Funktion in Excel]
link: / summieren-sumifs-mit-und-oder-Logik [SUMIFS mit UND-ODER-Logik]
Beliebte Artikel
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfung zur Steigerung Ihrer Produktivität]
: Schneller bei Ihrer Aufgabe.
Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]
: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter.
link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]
: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]
: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.