Oft möchten wir einige Werte nach Monat berechnen. Wie viel Umsatz wurde in einem bestimmten Monat getätigt. Nun, dies kann leicht mit Pivot-Tabellen erfolgen, aber wenn Sie versuchen, einen dynamischen Bericht zu erstellen, können wir eine SUMPRODUCT- oder SUMIFS-Formel verwenden, um nach Monat zu summieren.
Beginnen wir mit der SUMPRODUCT-Lösung.
Hier ist die generische Formel, um die Summe pro Monat in Excel zu erhalten
=SUMMENPRODUKT(sum_range,--( TEXT(date_range,"MMM")=month_text))
Summenbereich : Dies ist der Bereich, den Sie nach Monat summieren möchten.
Datumsbereich : Dies ist der Datumsbereich, in dem Sie monatelang suchen.
Monatstext: 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 gewissen Wert, der mit Daten verbunden ist. Diese Daten sind der Januar, Februar und März Monat 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 durch Monate in E1:G1 summieren.
Um nun die Werte nach Monaten zu summieren, schreiben Sie diese Formel in E2:
=SUMMENPRODUKT(B2:B9,--(TEXT(A2:A9,"MMM")=E1)))
Wenn Sie es in benachbarte Zellen kopieren möchten, verwenden Sie absolute Referenzen oder benannte Bereiche wie im Bild.
Dies gibt uns die genaue Summe jedes Monats.
Wie es funktioniert?
Schauen wir uns von innen an TEXT(A2:A9,"MMM") Teil. Hier extrahiert die TEXT-Funktion den Monat aus jedem Datum im Bereich A2:A9 im Textformat in ein Array. Übersetzen in Formel zu =SUMMENPRODUKT(B2:B9,--({"Jan";"Jan";"Feb";"Jan";"Feb";"Mär";"Jan";"Feb"}=E1) )
Als nächstes 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 andere in FALSE umgewandelt. Dies übersetzt die Formel in =SUMMENPRODUKT($B$2:$B$9,--{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE})
Als nächstes --(TEXT(A2:A9,"MMM")=E1), wandelt WAHR FALSE in die Binärwerte 1 und 0 um. Die Formel wird übersetzt in =SUMMENPRODUKT($B$2:$B$9,{1;1;0; 1;0;0;1;0}).
Schließlich SUMMENPRODUKT($B$2:$B$9,{1;1;0;1;0;0;1;0}): Die SUMMENPRODUKT-Funktion multipliziert die entsprechenden Werte in $B$2:$B$9 mit dem Array {1;1; 0;1;0;0;1;0} und addiert sie. Daher erhalten wir die Summe nach Wert als 20052 in E1.
SUMME 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 2018 und Jan 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, um die Summe nach Monat und Jahr in Excel zu erhalten
=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 gleich.
Lösen wir das obige Beispiel, schreiben Sie diese Formel in Zelle E1, um die Summe von Jan im Jahr 2017 zu erhalten.
=SUMMENPRODUKT(B2:B9,--(TEXT(A2:A9,"MMM")=E1),--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)))
Verwenden Sie vor dem Kopieren in die unteren Zellen benannte Bereiche oder absolute Referenzen. Im Bild habe ich benannte Bereiche zum Kopieren in die angrenzenden Zellen verwendet.
Jetzt können wir auch die Wertsumme 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.
--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)): TEXT(A2:A9,"yyyy") konvertiert 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 year int text mit TEXT(D2,0) konvertiert. Als nächstes verglichen wir dieses Textjahr mit einem Array von Jahren als TEXT(A2:A9,"yyyy")=TEXT(D2,0). Dies gibt ein Array von wahr-falsch {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE} zurück. Als nächstes haben wir mit dem Operator -- true false in eine Zahl umgewandelt. Dies gibt uns {0;0;1;1;0;1;0;1}.
Schließlich wird die Formel übersetzt in =SUMMENPRODUKT(B2:B9,{1;1;0;1;0;0;1;0},{0;0;1;1;0;1;0;1 }). Wobei das erste Array Werte ist. Der nächste ist der passende Monat und der dritte das Jahr. Schließlich erhalten wir unsere Summe der Werte als 2160.
Verwenden der SUMIFS-Funktion, um nach Monat zu summieren
Generische Formel
=SUMIFS(sum_range,date_range“,>=“ & startdate, date_range“,<=“ & EOMONTH(start_date,0))
Hier,Summenbereich : Dies ist der Bereich, den Sie nach Monat summieren möchten.
Datumsbereich : Dies ist der Datumsbereich, in dem Sie monatelang 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 gewissen Wert, der mit Daten verbunden ist. Diese Daten sind der Januar, Februar und März Monat des Jahres 2019.
Wir müssen diese Werte nur bis zum Monat summieren. Jetzt war es einfach, wenn wir Monate und Jahre getrennt hätten. Aber sie sind es nicht. Wir können hier keine Hilfsspalte verwenden.
Um einen Bericht vorzubereiten, habe ich ein Berichtsformat erstellt, das Monat und Summe der Werte enthält. In der Monatsspalte habe ich eigentlich 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. Fassen wir die Werte nach Monat zusammen.
Schreiben Sie diese Formel in E3, um nach Monat zu summieren.
=SUMIFS(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMONTH(D3,0))
Verwenden Sie absolute Referenzen oder benannte Bereiche, bevor Sie die Formel nach unten kopieren.
So, endlich haben wir das Ergebnis.
Also, wie funktioniert es?
Wie wir wissen, kann die SUMIFS-Funktion Werte nach mehreren 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 . ist D3. D3 enthält 1-Jan. Dies übersetzt sich auch.
=SUMIFS(B3:B10,A3:A10,">="& "1-jan-2019",A3:A10,"<="EOMONTH(D3,0))
Nächstes Kriterium ist nur Summe, wenn Datum in A3: A10 kleiner oder gleich EOMONTH(D3,0) ist. EOMONTH-Funktion gibt nur die Seriennummer des letzten Datums des angegebenen Monats zurück. Schließlich übersetzt auch Formel.
=SUMIFS(B3:B10,A3:A10,">=1-jan-2019”, A3:A10,"<=31-jan-2019”)
Daher erhalten wir die Summe pro Monat in Excel.
Der Vorteil dieser Methode besteht darin, dass Sie das Startdatum für die Summierung der Werte anpassen können.
Wenn Ihre Daten andere Jahre haben, ist es am besten, Pivot-Tabellen zu verwenden. Pivot-Tabellen können Ihnen dabei helfen, die Daten einfach in Jahres-, Quartals- und Monatsformat zu trennen.
Also ja, Leute, so können Sie Werte nach 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 Fragen zu Excel und VBA haben, steht Ihnen der Kommentarbereich zur Verfügung.
Artikel beziehen:
So verwenden Sie die SUMIF-Funktion in Excel
SUMIFE mit Daten in Excel
SUMIF mit nicht leeren Zellen
So verwenden Sie die SUMIFS-Funktion in Excel
SUMIFS mit UND-ODER-Logik
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität: Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel: Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel: Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.