Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Zwischensummen nach Gruppen mit der SUMIFS-Funktion in Excel und Google Sheets berechnen.
Zwischensummentabelle nach Kategorie oder Gruppe
Zuerst demonstrieren wir, wie Sie eine dynamische Zwischensummen-Zusammenfassungstabelle aus einem Datenbereich in Excel 365 aufwärts oder in Google Sheets erstellen.
Wir verwenden die UNIQUE-Funktion und die SUMIFS-Funktion, um automatisch Zwischensummen der Anzahl der Produkte von Produktgruppe:
| 1 | =SUMIFS(C3:C11,B3:B11,E3) |
Um diese Zwischensummentabelle zu erstellen, verwenden wir die Standardanwendung der SUMIFS-Funktion, um die Anzahl der Produkte das passt zu jedem Produktgruppe. Bevor dies jedoch möglich ist, müssen wir eine Liste mit einzigartigen Produktgruppen. Benutzer von Microsoft Excel 365 und Google Sheets haben Zugriff auf die UNIQUE-Funktion, um eine dynamische Liste eindeutiger Werte aus einem Zellbereich zu erstellen. In diesem Beispiel fügen wir Zelle E3 die folgende Formel hinzu:
| 1 | =EINZIGARTIG(B3:B11) |
Wenn diese Formel eingegeben wird, wird automatisch eine Liste unter der Zelle erstellt, um alle eindeutigen Werte anzuzeigen, die innerhalb der Produktgruppe Datenreichweite. In diesem Beispiel wurde die Liste um E3:E5 erweitert, um alle 3 eindeutigen zu zeigen Produktgruppe Werte.
Dies ist eine dynamische Array-Funktion, bei der die Größe der Ergebnisliste nicht definiert werden muss und die automatisch verkleinert und vergrößert wird, wenn sich die Eingabedatenwerte ändern.
Beachten Sie, dass in Excel 365 die UNIQUE-Funktion nicht zwischen Groß- und Kleinschreibung unterscheidet, in Google Sheets jedoch. Betrachten Sie die Liste {"A"; "ein"; "B"; "C"}. Die Ausgabe der UNIQUE-Funktion hängt vom Programm ab:
- {"EIN"; "B"; „c“} in Excel 365
- {"EIN"; "ein"; "B"; „c“} in Google Tabellen
Wenn Sie eine Excel-Version vor Excel 365 verwenden, müssen Sie einen anderen Ansatz wählen. Dies wird im nächsten Abschnitt besprochen.
Zwischensummentabelle nach Kategorie oder Gruppe - Pre Excel 365
Wenn Sie eine Version von Excel vor Excel 365 verwenden, steht die EINMALIGE Funktion nicht zur Verfügung. Um das gleiche Verhalten zu replizieren, können Sie die INDEX-Funktion und die MATCH-Funktion mit einer COUNTIF-Funktion kombinieren, um eine Matrixformel zu erstellen, um eine Liste eindeutiger Werte aus einem Zellbereich zu erstellen:
| 1 | {=INDEX($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Damit diese Formel funktioniert, müssen die festen Zellbezüge sorgfältig geschrieben werden, wobei die ZÄHLENWENN-Funktion auf den Bereich $E$2:E2 verweist, der von E2 bis zur Zelle über der Zelle mit der Formel beginnt.
Die Formel muss nach dem Schreiben auch als Matrixformel durch Drücken von STRG + UMSCHALT + EINGABE eingegeben werden. Diese Formel ist a 1-Zellen-Array-Formel, die dann in die Zellen E4, E5 usw. kopiert werden kann. Geben Sie dies nicht als Matrixformel für den gesamten Bereich E3:E5 in einer Aktion ein.
Auf die gleiche Weise wie im vorherigen Beispiel wird dann eine SUMIFS-Funktion verwendet, um die Zwischensumme der Anzahl der Produkte von Produktgruppe:
| 1 | =SUMIFS(C3:C11,B3:B11,E3) |
Summe nach Kategorie oder Gruppe - Zwischensummen in Datentabellen
Als Alternative zur oben gezeigten Übersichtstabellenmethode können wir Zwischensummen direkt in eine Datentabelle einfügen. Wir werden dies demonstrieren, indem wir die IF-Funktionen zusammen mit der SUMIFS-Funktion verwenden, um a . hinzuzufügen Zwischensumme nach Gruppe zur ursprünglichen Datentabelle.
| 1 | =WENN(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
In diesem Beispiel wird eine SUMIFS-Funktion verwendet, die in einer IF-Funktion verschachtelt ist. Unterteilen wir das Beispiel in Schritte:
Um zusammenfassende Statistiken direkt in eine Datentabelle einzufügen, können wir die SUMIFS-Funktion verwenden. Wir beginnen mit der Summierung der Anzahl der Produkte die zu den entsprechenden passen Produktgruppe:
| 1 | =SUMIFS(C3:C11,B3:B11,B3) |
Diese Formel erzeugt einen Zwischensummenwert für jede Datenzeile. Um Zwischensummen jeweils nur in der ersten Datenzeile anzuzeigen Produktgruppe, verwenden wir die IF-Funktion. Beachten Sie, dass die Daten bereits sortiert sein müssen nach Produktgruppe um sicherzustellen, dass die Zwischensummen korrekt angezeigt werden.
| 1 | =WENN(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
Die IF-Funktion vergleicht die Werte jeder Datenzeile Produktgruppe Wert mit der darüber liegenden Datenzeile, und wenn sie den gleichen Wert haben, wird eine leere Zelle ("") ausgegeben.
Wenn die Produktgruppe Werte unterschiedlich sind, wird die Summe angezeigt. Auf diese Weise, jeder Produktgruppe Summe wird nur einmal angezeigt (in der Zeile ihrer ersten Instanz).
Sortieren von Datensätzen nach Gruppe
Wenn die Daten noch nicht sortiert sind, können wir immer noch dieselbe Formel für die Zwischensumme verwenden.
Der obige Datensatz ist nicht sortiert nach Produktgruppe, also die Zwischensumme nach Gruppe Spalte zeigt jede Zwischensumme mehr als einmal an. Um die Daten in das gewünschte Format zu bringen, können wir die Datentabelle auswählen und auf „Sortieren von A bis Z“ klicken.
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir einige der Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | =WENN(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | =WENN(B3=B2,"",SUMIFS($C$3:$C$11,$B$3:$B$11,B3)) |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
Verwenden von Pivot-Tabellen zum Anzeigen von Zwischensummen
Um die Anforderung zu entfernen, die Daten vorzusortieren nach Produktgruppe, können wir stattdessen die Leistungsfähigkeit von Pivot-Tabellen nutzen, um die Daten zusammenzufassen. Pivot-Tabellen berechnen Zwischensummen automatisch und zeigen Summen und Zwischensummen in verschiedenen Formaten an.
Summe nach Kategorie oder Gruppe in Google Tabellen
Diese Formeln funktionieren in Google Tabellen genauso wie in Excel. Bei der UNIQUE-Funktion in Google Tabellen wird jedoch zwischen Groß- und Kleinschreibung unterschieden.
