SUBTOTAL() Formel in Excel: Ein vollständiger Leitfaden für die Berechnung

Excel-Nutzer brauchen oft flexible Möglichkeiten, um Daten zusammenzufassen, die auf Filter oder Gruppierungen reagieren. Traditionelle Funktionen wie SUM(), AVERAGE() oder COUNT() berechnen die Ergebnisse auf der Grundlage aller Zellen in einem Bereich, unabhängig davon, ob einige Zeilen ausgeblendet oder herausgefiltert sind. Die Funktion SUBTOTAL() bietet eine dynamische Alternative, die ihre Berechnungen automatisch an das anpasst, was gerade in deinem Arbeitsblatt zu sehen ist.

Wenn du neu in Excel bist und eine solide Grundlage schaffen willst, bevor du dich mit fortgeschrittenen Funktionen wie SUBTOTAL() beschäftigst, vermittelt dir unserKurs Einführung in Excel wichtige Fähigkeiten wie die Navigation in Arbeitsblättern, grundlegende Formeln und Datenformatierungstechniken, die dich auf anspruchsvollere Excel-Arbeiten vorbereiten.

Was ist die Funktion SUBTOTAL() in Excel?

Die Funktion SUBTOTAL() berechnet aggregierte Werte wie Summe, Durchschnitt, Anzahl und andere statistische Operationen für einen Datensatz. Der Unterschied zu den Standard-Excel-Funktionen besteht darin, dass sie dynamisch arbeitet und ihr Ergebnis abhängig von den sichtbaren oder gefilterten Daten ändert. Die Funktion kann sowohl manuell ausgeblendete Zeilen als auch durch Filter ausgeblendete Zeilen ignorieren, je nachdem, welche Funktionsnummer du angibst.

Wenn du einen Filter auf deine Daten anwendest oder bestimmte Zeilen manuell ausblendest, passt SUBTOTAL() seine Berechnung automatisch so an, dass nur die sichtbaren Zellen berücksichtigt werden. Dieses Verhalten macht sie zu einer ausgezeichneten Wahl für die Erstellung von Zusammenfassungszeilen in gefilterten Datensätzen, für die Erstellung interaktiver Dashboards oder für die Erstellung von Berichten, die aufgrund von Benutzerauswahlen aktualisiert werden müssen.

Die Funktion unterstützt dieselben statistischen Operationen wie die Standardfunktionen von Excel - z. B. Summe, Durchschnitt, Anzahl, Maximum, Minimum und einige andere -, fasst sie aber in einer einzigen, kontextabhängigen Funktion zusammen, die sich an den aktuellen Zustand deiner Daten anpasst.

Syntax und unterstützte Operationen

Die Funktion SUBTOTAL() folgt einer einfachen Syntaxstruktur:

=SUBTOTAL(function_num, ref1, [ref2], ...)

Der erste Parameter, function_num, bestimmt sowohl die Art der Berechnung als auch, wie die Funktion mit ausgeblendeten Zeilen umgeht. Die übrigen Parameter (ref1, ref2, etc.) sind die Zellbereiche, die du in die Berechnung einbeziehen möchtest. Du kannst bis zu 254 verschiedene Bereiche angeben.

Die Funktionsnummern lassen sich in zwei verschiedene Kategorien einteilen, je nachdem, wie sie manuell verdeckte Zeilen behandeln:

Die Funktionsnummern 1-11 beziehen Zellen aus manuell ausgeblendeten Zeilen in ihre Berechnungen ein, während die Funktionsnummern 101-111 diese ausschließen. Beide Bereiche ignorieren jedoch immer Zeilen, die von Filtern ausgeblendet werden.

Zum Beispiel berechnet SUBTOTAL(9, A2:A10) die Summe von A2:A10 einschließlich aller manuell ausgeblendeten Zeilen, während SUBTOTAL(109, A2:A10) manuell ausgeblendete Zeilen von der Summenberechnung ausschließt. In beiden Fällen werden die gefilterten Zeilen automatisch aus dem Ergebnis ausgeschlossen.

SUBTOTAL() Funktionsbeispiel in Aktion

Sehen wir uns die Funktionsweise von SUBTOTAL() anhand eines praktischen Beispiels mit den Verkaufsdaten eines Elektronik- und Möbelgeschäfts an.

Ungefilterter Datensatz. Bild vom Autor. 

Unsere Beispieldaten enthalten sieben Produkte in zwei Kategorien - vier Elektronikartikel (Laptop, Telefon, Tablet, Monitor) und drei Möbelstücke (Schreibtisch, Stuhl, Lampe). Ohne Anwendung von Filtern zeigen sowohl die Formel SUBTOTAL(109,C2:C8) als auch die reguläre Formel SUM(C2:C8) das gleiche Ergebnis: 3525 (die Summe aller Verkäufe).

Gefilterter Datensatz. Bild vom Autor. 

Jetzt haben wir einen Filter angewandt, der nur die Elektronikprodukte anzeigt und die drei Möbelreihen ausblendet. Beachte den entscheidenden Unterschied in unseren Berechnungsergebnissen:

• SUBTOTAL-Formel zeigt 3000 - automatische Anpassung, um nur die sichtbaren Zeilen der Elektronik zu summieren
• Normale SUMmenformel zeigt immer noch 3525 - weiterhin die versteckten Möbelstücke einbeziehen

Dieser Seite-an-Seite-Vergleich zeigt den zentralen Vorteil von SUBTOTAL(): Es reagiert dynamisch auf Filter und Änderungen der Datensichtbarkeit, während traditionelle Funktionen wie SUM() statisch bleiben. Wenn du Daten filterst oder Zeilen ausblendest, berechnet SUBTOTAL() sofort neu, um nur die aktuell sichtbaren Daten anzuzeigen, was es für responsive Dashboards und interaktive Berichte unerlässlich macht.

Wie SUBTOTAL() mit versteckten und gefilterten Daten funktioniert

Die Funktion SUBTOTAL() behandelt verschiedene Arten von versteckten Daten auf eine bestimmte Art und Weise, so dass es wichtig ist, diese Verhaltensweisen zu verstehen, wenn du deine Formeln erstellst.

SUBTOTAL() mit gefilterten Zeilen

Wenn du einen Filter auf deinen Datensatz anwendest, schließt SUBTOTAL() die herausgefilterten Zeilen immer von seinen Berechnungen aus, unabhängig davon, welche Funktionsnummer du verwendest. Dieses Verhalten ist automatisch und einheitlich für alle Funktionsnummern (sowohl für die Bereiche 1-11 als auch 101-111).

Wenn du zum Beispiel Umsatzdaten für Januar bis Dezember hast und einen Filter anwendest, der nur die Monate Q1 (Januar, Februar, März) anzeigt, berechnet SUBTOTAL(9, B2:B13) die Summe nur für diese drei sichtbaren Monate und ignoriert die herausgefilterten Q2-Q4-Daten.

SUBTOTAL() mit manuell ausgeblendeten Zeilen

Manuell ausgeblendete Zeilen werden je nach der von dir gewählten Funktionsnummer unterschiedlich behandelt. Wenn du mit der rechten Maustaste auf eine Zeilennummer klickst und "Ausblenden" wählst, blendest du diese Zeilen manuell aus.

Die Verwendung der Funktionsnummern 1-11 bezieht diese manuell ausgeblendeten Zeilen in die Berechnungen ein. Die Verwendung der Funktionsnummern 101-111 schließt sie aus. So hast du die Kontrolle darüber, ob versteckte Daten deine Ergebnisse beeinflussen sollen.

SUBTOTAL() mit horizontalen und vertikalen Bereichen

Die Funktion SUBTOTAL() funktioniert am besten mit vertikalen Datenbereichen (Datenspalten). Sie kann zwar horizontale Bereiche verarbeiten, ist aber für zeilenbasierte Operationen optimiert, da die Filter- und Zeilenausblendungsfunktionen von Excel vertikal funktionieren.

Wenn du SUBTOTAL() Formeln innerhalb desselben Bereichs verschachtelst, überspringt die Funktion automatisch andere SUBTOTAL() Ergebnisse, um Doppelzählungen zu vermeiden. Dies ist besonders nützlich, wenn du hierarchische Zusammenfassungen oder Zwischensummen innerhalb von Gruppen erstellst, um sicherzustellen, dass deine Summen jede einzelne Gruppierung genau widerspiegeln, ohne sich zu duplizieren.

Fortgeschrittene Verwendungen der Funktion SUBTOTAL()

Erstellen einer dynamischen Dropdown-Zusammenfassung mit SUBTOTAL()

Du kannst SUBTOTAL() mit der Funktion IF() kombinieren, um interaktive Zusammenfassungen zu erstellen, die sich je nach Benutzerauswahl ändern. Bei diesem Ansatz wird eine Dropdown-Liste verwendet, damit die Nutzer zwischen verschiedenen Berechnungsarten wechseln können.

Um eine dynamische Dropdown-Zusammenfassung zu erstellen, richte zunächst ein Dropdown in der Zelle D1 mit Optionen wie "Sum", "Average", "Count" und "Max" ein.

Verwende dann die folgende Formel in einer anderen Zelle (z. B. D2), um die Berechnung dynamisch an die Auswahl des Benutzers anzupassen:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Diese Formel wertet die Dropdown-Auswahl aus und wendet die entsprechende SUBTOTAL() Funktion an. Die Nutzer können zwischen den Berechnungsarten wechseln, ohne Formeln zu bearbeiten, was die Berichte interaktiver und benutzerfreundlicher macht.

SUBTOTAL() in Excel-Tabellen und Gliederungen

Excel-Tabellen verwenden automatisch SUBTOTAL() in der Funktion Gesamtzeile. Wenn du deinen Datenbereich in eine Tabelle umwandelst (Strg+T) und die Summenzeile aktivierst, fügt Excel standardmäßig SUBTOTAL(109, …) für Summenberechnungen ein. Diese Gesamtzeile wird dynamisch aktualisiert, wenn Zeilen gefiltert werden, und passt die Ergebnisse sofort so an, dass nur sichtbare Daten angezeigt werden.

Du kannst die Berechnungsart ändern, indem du auf den Dropdown-Pfeil in einer beliebigen Zelle der Gesamtzeile klickst und verschiedene Optionen wie Count, Average, Max oder Min auswählst. Excel aktualisiert die Funktionsnummer automatisch und behält dabei die Struktur von SUBTOTAL() bei.

Die Gliederungsfunktion (Daten > Zwischensumme) basiert ebenfalls auf den Formeln von SUBTOTAL(). Wenn du Daten gruppierst und Zwischensummen einfügst, platziert Excel automatisch SUBTOTAL() Funktionen an jedem Gruppenwechsel. Dadurch entstehen hierarchische Zusammenfassungen, die korrekt reagieren, wenn du Gliederungsebenen erweiterst oder reduzierst, da die Funktion verschachtelte Zwischensummenergebnisse ignoriert.

Häufige Fallstricke und Beschränkungen

Wenn du die häufigen Fehler und Grenzen von SUBTOTAL() kennst, kannst du Berechnungsfehler vermeiden und den richtigen Ansatz für deine Datenanalyse wählen.

• #VALUE! Fehler mit ungültigen Funktionsnummern: Der häufigste Fehler tritt bei der Verwendung einer ungültigen Funktionsnummer auf. SUBTOTAL() akzeptiert nur die Funktionsnummern 1-11 und 101-111. Wenn du Zahlen wie 12, 50 oder 200 verwendest, bekommst du einen #VALUE!-Fehler. Überprüfe immer, ob deine Funktionsnummer einem gültigen Vorgang aus den unterstützten Bereichen entspricht.

• Verhalten von ausgeblendeten Spalten und ausgeblendeten Zeilen: Während SUBTOTAL() auf ausgeblendete Zeilen mit der von dir gewählten Funktionsnummer reagiert, verhält es sich bei ausgeblendeten Spalten nicht so. Die Funktion bezieht Daten aus ausgeblendeten Spalten in alle Berechnungen ein, unabhängig davon, ob du die Funktionsnummern 1-11 oder 101-111 verwendest. Diese Einschränkung bedeutet, dass du alternative Ansätze brauchst, wenn du mit Datensätzen arbeitest, bei denen die Sichtbarkeit von Spalten deine Analyse beeinflusst.

• 3D-Referenzen werden nicht unterstützt: Anders als viele Excel-Funktionen kann SUBTOTAL() nicht auf Bereiche in mehreren Arbeitsblättern verweisen. Formeln wie SUBTOTAL(109, Sheet1:Sheet3!A1:A10) liefern Fehler. Als Abhilfe kannst du zunächst Summen oder andere Aggregate auf jedem Blatt separat mit Standardfunktionen wie SUM() berechnen und diese Zwischenergebnisse dann mit einer einzigen ZWISCHENSUMME auf einem Übersichtsblatt konsolidieren.

• Fehlerwerte bleiben in den Berechnungen erhalten: SUBTOTAL() ignoriert keine Zellen, die Fehlerwerte wie #N/A, #DIV/0! oder #VALUE! enthalten. Diese Fehler ziehen sich durch deine Zwischensummenberechnungen und können ganze Ergebnisse ungültig machen. Bereinige deine Daten von Fehlern, bevor du SUBTOTAL() anwendest, oder ziehe in Erwägung, die Funktion AGGREGATE() zu verwenden, die Fehlerwerte auslassen kann.

• Die vertikale Ausrichtung der Daten funktioniert am besten: SUBTOTAL() kann zwar horizontale Bereiche verarbeiten, ist aber für vertikale Datenstrukturen optimiert. Die Excel-Funktionen zum Filtern, Sortieren und Ausblenden funktionieren zeilenweise, so dass vertikale Anordnungen besser mit dem beabsichtigten Verhalten der Funktion vereinbar sind.

Zwischensumme() vs. AGGREGATE()

Die Funktion AGGREGATE() dient als erweiterte Alternative zu SUBTOTAL() mit zusätzlichen Möglichkeiten zur Fehlerbehandlung und mehr statistischen Operationen. Während SUBTOTAL() 11 Grundoperationen bietet, stellt AGGREGATE() 19 verschiedene Funktionen zur Verfügung, darunter die Berechnung von Perzentilen, Quartilen und Medianen.

Der Hauptvorteil von AGGREGATE() liegt in seiner Fähigkeit, mit Fehlern umzugehen. Im Gegensatz zu SUBTOTAL(), das Fehlerwerte in die Berechnungen einbezieht, kann AGGREGATE() Zellen mit Fehlern wie #N/A, #DIV/0! oder #VALUE! automatisch überspringen. Das macht sie besonders nützlich für Datensätze mit unvollständigen oder problematischen Daten.

AGGREGATE() bietet auch eine genauere Kontrolle darüber, was ignoriert werden soll. Du kannst ihn so konfigurieren, dass er ausgeblendete Zeilen, verschachtelte Zwischensummen, Fehlerwerte oder eine beliebige Kombination dieser Elemente überspringt, indem du den Parameter Optionen verwendest.

Profi-Tipps für die Verwendung von SUBTOTAL()

Immer zuerst sortieren

Wenn du die in Excel integrierte Funktion Daten > Zwischensumme verwendest, sortiere deine Daten zuerst nach der Gruppierungsspalte. So wird sichergestellt, dass alle zusammengehörigen Datensätze zusammen angezeigt werden, und es entstehen saubere Gruppenunterbrechungen für deine Zwischensummenberechnungen. Unsortierte Daten ergeben verstreute Zwischensummen, die keine aussagekräftigen Zusammenfassungen liefern.

Beschriftungen in der ersten Reihe der Spalten verwenden

Setze beschreibende Überschriften in die erste Zeile deines Datenbereichs, bevor du Zwischensummen bildest. Excel verwendet diese Beschriftungen, um aussagekräftige Zwischensummenbeschreibungen zu erstellen und deine Berichte leichter interpretierbar zu machen. Klare Spaltenüberschriften helfen auch bei der Auswahl von Bereichen für manuelle SUBTOTAL() Formeln.

Achte auf die Platzierung der Zusammenfassung (oben oder unten)

Das Excel-Tool "Zwischensumme" platziert die Summenzeilen standardmäßig unter jeder Gruppe, du kannst sie aber auch darüber platzieren. Berücksichtige den Verwendungszweck deines Berichts, wenn du diese Entscheidung triffst. Zusammenfassungszeilen über den Gruppen eignen sich gut für das Dashboard der Geschäftsführung, während Zusammenfassungszeilen unter den Gruppen den traditionellen Buchhaltungsformaten entsprechen.

Ausblenden vs. Filtern von Zeilen für präzise Kontrolle

Verstehe den Unterschied zwischen dem Filtern und dem manuellen Ausblenden von Zeilen, um die erwarteten Ergebnisse zu erhalten. Verwende Filter, wenn du möchtest, dass SUBTOTAL() bestimmte Daten vorübergehend ignoriert. Verwende das manuelle Ausblenden (Rechtsklick > Ausblenden), wenn du dauerhaftere Ausschlüsse möchtest, und wähle dann die Funktionsnummern 101-111, um den ausgeblendeten Zustand zu respektieren.

Dieser Ansatz ermöglicht dir eine mehrstufige Kontrolle: Du kannst nach temporären Datenansichten filtern, semipermanente Ausnahmen manuell ausblenden und geeignete Funktionsnummern auswählen, um deine Ausblendungsabsichten zu berücksichtigen.

Fazit

Wenn du lernst, SUBTOTAL() effektiv zu nutzen, verändert sich deine Herangehensweise an die Datenanalyse in Excel. Anstatt statische Berechnungen zu erstellen, die bei Datenänderungen abbrechen, kannst du belastbare Formeln erstellen, die sich automatisch an Benutzeraktionen und Datenänderungen anpassen. Diese Fähigkeit ist besonders wertvoll, wenn du mit großen Datensätzen arbeitest oder Berichte erstellst, die im Laufe der Zeit von mehreren Nutzern gefiltert und bearbeitet werden sollen.

Um ein umfassendes Excel-Know-how aufzubauen, das über einzelne Funktionen wie SUBTOTAL() hinausgeht, bietet unserLernpfad Excel Fundamentals einen strukturierten 16-stündigen Lernpfad, der dich von der Datenaufbereitung bis zu fortgeschrittenen Analyse- und Visualisierungstechniken führt. Für Leser, die bereit sind, analytische Anwendungen sofort zu erkunden, vermittelt unser Kurs Datenanalyse in Excel die Beherrschung von PivotTables und fortgeschrittene logische Funktionen für tiefere Einblicke.