Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie Sie das Excel-ZWISCHENSUMME-Funktion in Excel, um zusammenfassende Statistiken zu berechnen.
ZWISCHENSUMME Funktionsübersicht
Die Funktion SUBTOTAL Berechnet eine zusammenfassende Statistik für eine Reihe von Daten. Verfügbare Statistiken umfassen, sind aber nicht beschränkt auf Durchschnitt, Standardabweichung, Anzahl, Min und Max. Siehe vollständige Liste unten im Abschnitt Funktionseingänge:
Um die Excel-Arbeitsblattfunktion ZWISCHENSUMME zu verwenden, wählen Sie eine Zelle aus und geben Sie Folgendes ein:
(Beachten Sie, wie die Formeleingaben angezeigt werden)
ZWISCHENSUMME Funktionssyntax und Eingaben:
| 1 | =ZWISCHENSUMME(funktion_num,REF1) |
function_num - Eine Zahl, die angibt, welche Operation ausgeführt werden soll.
REF1 - Bereiche oder Referenzen, die zu berechnende Daten enthalten.
Was ist die ZWISCHENSUMME-Funktion?
Die ZWISCHENSUMME ist eine der einzigartigen Funktionen in Tabellenkalkulationen, da sie den Unterschied zwischen ausgeblendeten und nicht ausgeblendeten Zellen erkennen kann. Dies kann sich als sehr hilfreich erweisen, wenn Sie mit gefilterten Bereichen arbeiten oder wenn Sie Berechnungen basierend auf verschiedenen Benutzerauswahlen einrichten müssen. Da es auch andere SUBTOTAL-Funktionen bei seinen Berechnungen ignorieren kann, können wir es auch in großen zusammengefassten Daten verwenden, ohne Doppelzählungen befürchten zu müssen.
Grundlegende Zusammenfassung mit ZWISCHENSUMME
Angenommen, Sie haben eine Tabelle mit sortierten Produktverkäufen und möchten für jedes Produkt Summen sowie eine Gesamtsumme erstellen. Sie können eine PivotTable verwenden oder einige Formeln einfügen. Betrachten Sie dieses Layout:
Ich habe einige ZWISCHENSUMME-Funktionen in die Zellen B5 und B8 eingefügt, die wie folgt aussehen
| 1 | =ZWISCHENSUMME(9, B2:B4) |
Aus der Syntax können Sie verschiedene Zahlen für das erste Argument verwenden. In unserem speziellen Fall verwenden wir 9, um anzugeben, dass wir eine Summe machen möchten.
Konzentrieren wir uns auf Zelle B9. Es enthält diese Formel, die den gesamten Datenbereich von Spalte B umfasst, jedoch nicht die anderen Zwischensummen.
| 1 | =ZWISCHENSUMME(9, B2:B8) |
HINWEIS: Wenn Sie nicht alle Zusammenfassungsformeln selbst schreiben möchten, können Sie zum Menüband Daten gehen und den Assistenten Gliederung - Zwischensumme verwenden. Es fügt automatisch Zeilen ein und platziert die Formeln für Sie.
Unterschied in den ersten Argumenten
Im ersten Beispiel haben wir eine 9 verwendet, um anzugeben, dass wir eine Summe bilden wollten. Der Unterschied zwischen der Verwendung von 9 und 109 besteht darin, wie die Funktion ausgeblendete Zeilen behandeln soll. Wenn Sie die 1XX-Bezeichnungen verwenden, umfasst die Funktion keine Zeilen, die manuell ausgeblendet oder gefiltert wurden.
Hier ist unsere Tabelle von früher. Wir haben die Funktionen verschoben, damit wir den Unterschied zwischen den Argumenten 9 und 109 sehen können. Wenn alles sichtbar ist, sind die Ergebnisse gleich.
Wenn wir einen Filter anwenden, um den Wert 6 in Spalte B herauszufiltern, bleiben die beiden Funktionen gleich.
Wenn wir die Zeilen manuell ausblenden, sehen wir den Unterschied. Die 109-Funktion konnte die versteckte Zeile ignorieren, während die 9-Funktion dies nicht tat.
Ändern Sie die mathematische Operation mit ZWISCHENSUMME
Vielleicht möchten Sie Ihrem Benutzer manchmal die Möglichkeit geben, die Art der durchgeführten Berechnungen zu ändern. Wollen sie zum Beispiel die Summe oder den Durchschnitt erhalten? Da SUBTOTAL die mathematische Operation durch eine Argumentnummer steuert, können Sie dies in einer einzigen Formel schreiben. Hier ist unser Setup:
Wir haben in D2 ein Dropdown-Menü erstellt, in dem der Benutzer entweder "Summe" oder "Durchschnitt" auswählen kann. Die Formel in E2 lautet:
| 1 | =ZWISCHENSUMME(IF(D2="Durchschnitt",1,IF(D2="Summe",9)),B2:B4) |
Hier bestimmt die IF-Funktion, welches numerische Argument der ZWISCHENSUMME übergeben werden soll. Wenn A5 „Durchschnitt“ ist, wird eine 1 ausgegeben und ZWISCHENSUMME gibt den Durchschnitt von B2:B4 an. Oder, wenn A5 gleich „Summe“ ist, dann gibt die IF eine 9 aus und wir erhalten ein anderes Ergebnis.
Sie können diese Funktion erweitern, indem Sie eine Nachschlagetabelle verwenden, um noch mehr Arten von Operationen aufzulisten, die Sie ausführen möchten. So könnte Ihre Nachschlagetabelle aussehen
Dann könnten Sie die Formel in E2 ändern in
| 1 | =ZWISCHENSUMME(SVERWEIS(A5, LookupTable, 2, 0), B2:B4) |
Bedingte Formeln mit ZWISCHENSUMME
SUBTOTAL kann zwar viele Operationen ausführen, kann jedoch die Kriterien nicht selbst überprüfen. Wir können es jedoch in einer Hilfsspalte verwenden, um diesen Vorgang auszuführen. Wenn Sie eine Datenspalte haben, von der Sie wissen, dass sie immer Daten enthalten, können Sie die Fähigkeit von SUBTOTALs verwenden, um versteckte Zeilen zu erkennen.
Hier ist die Tabelle, mit der wir in diesem Beispiel arbeiten werden. Schließlich möchten wir die Werte für „Apple“ summieren können, aber auch den Benutzer die Spalte Menge filtern lassen.
Erstellen Sie zunächst eine Hilfsspalte, die die Funktion ZWISCHENSUMME enthält. In C2 lautet die Formel:
| 1 | =ZWISCHENSUMME(103, A2) |
Denken Sie daran, dass 103 bedeutet, dass wir einen COUNTA machen möchten. Ich empfehle die Verwendung von COUNTA, da Sie dann Ihre Referenzzelle von A2 mit füllen lassen können entweder Zahlen oder Text. Sie haben jetzt eine Tabelle, die wie folgt aussieht:
Dies erscheint zunächst nicht hilfreich, da alle Werte nur 1 sind. Wenn wir jedoch Zeile 3 ausblenden, ändert sich diese "1" in C3 in eine 0, da sie auf eine ausgeblendete Zeile zeigt. Es ist zwar unmöglich, ein Bild zu haben, das den Wert der bestimmten ausgeblendeten Zelle zeigt, Sie können es jedoch überprüfen, indem Sie die Zeile ausblenden und dann eine grundlegende Formel wie diese zur Überprüfung schreiben.
| 1 | =C3 |
Da wir nun eine Spalte haben, deren Wert sich ändert, je nachdem, ob sie ausgeblendet ist oder nicht, können wir die endgültige Gleichung schreiben. Unsere SUMIFS werden so aussehen
In dieser Formel summieren wir nur Werte aus Spalte B, wenn Spalte A gleich "Apple" ist. und der Wert in Spalte C ist 1 (auch bekannt als die Zeile ist nicht ausgeblendet). Nehmen wir an, unser Benutzer möchte die 600 herausfiltern, weil sie ungewöhnlich hoch erscheint. Wir können sehen, dass unsere Formel das richtige Ergebnis liefert.
Mit dieser Fähigkeit können Sie einen Scheck auf ein ZÄHLENWENN, SUMIFEN oder sogar ein SUMMENPRODUKT anwenden. Sie fügen die Möglichkeit hinzu, Ihre Benutzer einige Tabellen-Slicer steuern zu lassen, und Sie sind bereit, ein fantastisches Dashboard zu erstellen.
ZWISCHENSUMME in Google Tabellen
Die ZWISCHENSUMME-Funktion funktioniert in Google Sheets genauso wie in Excel:
ZWISCHENSUMME Beispiele in VBA
Sie können auch die ZWISCHENSUMME-Funktion in VBA verwenden. Typ:application.worksheetfunction.subtotal(function_num,reh1)
Ausführen der folgenden VBA-Anweisungen
| 1234567891011121314151617 | Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Range("D7") = Application.WorksheetFunction.Subtotal(1, Range("D2:D5"))Range("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))Range("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))Range("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Range("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
führt zu folgenden Ergebnissen
Für die Funktionsargumente (function_num usw.) können Sie diese entweder direkt in die Funktion eingeben oder stattdessen Variablen definieren.
Zurück zur Liste aller Funktionen in Excel
