Wenn Ihr Excel-Arbeitsblatt ausgeblendete Zeilen, gefilterte Daten oder gruppierte Daten enthält, verwenden Sie die Excel-ZWISCHENSUMME-Funktion. Die Funktion TEILERGEBNIS kann ausgeblendete Werte in Berechnungen einbeziehen oder ausschließen. Excel kann nicht nur die Summe einer Datengruppe ermitteln, sondern auch den Durchschnitt, das Maximum, das Minimum, die Standardabweichung und die Varianz Ihrer Daten berechnen. So fügen Sie Zwischensummen in Excel ein.
Die Anweisungen in diesem Artikel gelten für Excel für Microsoft 365, Excel 2019 und Excel 2016.
Syntax der SUBTOTAL-Funktion
Verwenden Sie die SUBTOTAL-Funktion in Excel, um die Werte in einem Arbeitsblatt auf unterschiedliche Weise zusammenzufassen. Dies ist besonders hilfreich, wenn Ihr Arbeitsblatt ausgeblendete Zeilen enthält, die Sie in die Berechnung einbeziehen möchten.
Die Syntax der SUBTOTAL-Funktion ist: SUBTOTAL(function_num, ref1, ref2, …)
Das Argument function_num ist erforderlich und gibt die Art der mathematischen Operation an, die für die Zwischensumme verwendet werden soll. Die SUBTOTAL-Funktion kann Zahlen addieren, den Durchschnittswert ausgewählter Zahlen berechnen, die maximalen und minimalen Werte in einem Bereich finden, die Anzahl der Werte in einem ausgewählten Bereich zählen und mehr.
Die SUBTOTAL-Funktion ignoriert Zellen, die keine Daten enth alten, sowie Zellen mit nicht numerischen Werten.
Dieses Argument ist eine Zahl und hängt davon ab, ob Sie versteckte Zeilen in das Ergebnis einbeziehen oder versteckte Zeilen aus dem Ergebnis ausschließen möchten. Diese Zeilen können manuell ausgeblendet oder durch einen Filter ausgeblendet werden.
Die function_num Argumente umfassen:
| Funktion Aufgabe | function_num | function_num |
|---|---|---|
| (enthält versteckte Werte) | (schließt versteckte Werte aus) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUKT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUMME | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Die function_num-Referenzargumente 1 bis 11 enth alten nur Werte in ausgeblendeten Zeilen, wenn der Hide-Befehl zum Ausblenden von Zeilen verwendet wird. Bei Verwendung des Befehls „Filter“enth alten TEILERGEBNIS-Berechnungen keine ausgeblendeten Filterergebnisse.
Das Argument ref1 ist erforderlich. Dies sind die Zellen, die zum Berechnen der Ergebnisse des ausgewählten Arguments function_num verwendet werden. Dieses Argument kann ein Wert, eine einzelne Zelle oder ein Zellbereich sein.
Die Argumente ref2, … sind optional. Dies sind zusätzliche Zellen, die in die Berechnung einbezogen werden.
Verwenden Sie die SUBTOTAL-Funktion mit ausgeblendeten Zeilen
Excel-Funktionen können manuell oder mit Hilfe des Dialogfelds Funktionsargumente eingegeben werden. Um zu veranschaulichen, wie Sie die Funktion manuell über die Bearbeitungsleiste eingeben, verwendet das folgende Beispiel das Argument COUNT function_num, um die Anzahl der Werte in sichtbaren Zeilen und sowohl in sichtbaren als auch in verborgenen Zeilen zu zählen.
So verwenden Sie die SUBTOTAL-Funktion, um die Anzahl der Zeilen in einem Arbeitsblatt zu zählen:
-
Beginnen Sie mit einem Arbeitsblatt, das mehrere Datenzeilen enthält.
- Wählen Sie die Zelle aus, die die Anzahl der sichtbaren Zeilen enth alten soll.
-
Geben Sie in der Funktionsleiste =ZWISCHENSUMME ein. Während Sie tippen, schlägt Excel eine Funktion vor. Doppelklicken Sie auf die Funktion ZWISCHENSUMME.
Um das Dialogfeld "Funktionsargumente" zur Eingabe der SUBTOTAL-Funktion zu verwenden, gehen Sie zu Formeln und wählen Sie Math & Trig >ZWISCHENSUMME.
Image -
Doppelklicken Sie im angezeigten Dropdown-Menü auf das Argument 102 – COUNT function_num.
Image -
Geben Sie ein Komma (,) ein.
Image -
Wählen Sie im Arbeitsblatt die Zellen aus, die in die Formel aufgenommen werden sollen.
Image -
Drücken Sie Enter, um das Ergebnis in der Zelle anzuzeigen, die Sie in Schritt 2 ausgewählt haben.
Image - Wählen Sie die Zelle aus, die die Anzahl der sichtbaren und ausgeblendeten Zeilen enth alten soll.
- Geben Sie in der Funktionsleiste =ZWISCHENSUMME ein. Während Sie tippen, schlägt Excel eine Funktion vor. Doppelklicken Sie auf die Funktion ZWISCHENSUMME.
- Doppelklicken Sie im angezeigten Dropdown-Menü auf das Argument 2 – COUNT function_num und geben Sie dann ein Komma (,) ein.
-
Wählen Sie im Arbeitsblatt die Zellen aus, die in die Formel aufgenommen werden sollen, und drücken Sie dann Enter.
Image -
Mehrere Datenzeilen ausblenden. In diesem Beispiel wurden Zeilen mit nur Verkäufen unter 100.000 $ ausgeblendet.
Image
Verwenden Sie die SUBTOTAL-Funktion mit gefilterten Daten
Die Verwendung der SUBTOTAL-Funktion für gefilterte Daten ignoriert Daten in Zeilen, die durch den Filter entfernt wurden. Jedes Mal, wenn sich die Filterkriterien ändern, berechnet die Funktion neu, um die Zwischensumme für die sichtbaren Zeilen anzuzeigen.
So verwenden Sie die SUBTOTAL-Funktion, um die Unterschiede in den Berechnungsergebnissen beim Filtern von Daten anzuzeigen:
-
Zwischensummenformeln erstellen. Erstellen Sie beispielsweise Formeln, um die Zwischensumme und Durchschnittswerte der gefilterten Daten zu ermitteln.
Es spielt keine Rolle, ob Sie das Argument function_num für sichtbare oder versteckte Zeilen verwenden. Beide Argumente liefern das gleiche Ergebnis in gefilterten Daten.
Image - Beliebige Zelle im Datensatz auswählen.
-
Gehen Sie zu Home und wählen Sie dann Sortieren und Filtern > Filter.
Image -
Verwenden Sie die Dropdown-Pfeile, um die Arbeitsblattdaten zu filtern.
Image -
Beachten Sie, wie sich die Werte jedes Mal ändern, wenn Sie andere Filterkriterien auswählen.
Image
Verwenden Sie die SUBTOTAL-Funktion mit gruppierten Daten
Wenn Daten gruppiert sind, gibt es eine Möglichkeit, die SUBTOTAL-Funktion auf jede einzelne Gruppe anzuwenden und dann die Gesamtsumme für den gesamten Datensatz zu berechnen.
- Beliebige Zelle im Datensatz auswählen.
-
Wählen Sie Daten > Zwischensumme, um das Dialogfeld Zwischensumme zu öffnen.
Image - Wählen Sie den Dropdown-Pfeil Bei jeder Änderung in und wählen Sie die Gruppierung aus, für die jede Zwischensumme berechnet werden soll.
- Wählen Sie den Dropdown-Pfeil Funktion verwenden und wählen Sie eine Funktion_num.
- Wählen Sie in der Liste Zwischensumme hinzufügen zu die Sp alte aus, auf die die Formel angewendet werden soll.
-
Auswahl OK.
Image -
Zwischensummen werden für jede Datengruppe eingefügt, und eine Gesamtsumme wird am Ende des Datensatzes eingefügt.
Image - Um die Funktionsnummer zu ändern, markieren Sie eine beliebige Zelle im Datensatz und wählen Sie Daten > Zwischensumme. Treffen Sie dann Ihre Auswahl im Dialogfeld Zwischensumme.
