Du suchst nach einer Möglichkeit, zusammenfassende Berechnungen in deinen Excel-Tabellen dynamisch anzuzeigen und gleichzeitig die volle Flexibilität bei der Datenanalyse zu behalten? Dieser Text erklärt dir die Excel Teilergebnis Funktion (SUBTOTAL) Schritt für Schritt, damit du deine Berichte und Auswertungen optimieren kannst, insbesondere wenn deine Daten gefiltert oder ausgeblendet sind.
Das sind die beliebtesten Excel Produkte
Microsoft Excel – Formeln und Funktionen kompakt für alle Versionen: Die Wo&Wie Schnellübersicht ...
zum Angebot »
Excel 2024: Das große Excel-Handbuch. Einstieg, Praxis, Profi-Tipps – das Standardwerk für alle Excel-Anwender. ...
zum Angebot »
Gillette Sensor Excel Rasierklingen, Klingen für Männer, 10 Original Ersatzklingen für Herren mit Doppelklinge ...
zum Angebot »
Microsoft 365 Single | 1 Jahr | 1 Person | Office Apps mit KI | 1 TB Cloudspeicher | Abo mit automatischer Verläng ...
zum Angebot »
Microsoft 365 Family | 1 Jahr | bis zu 6 Personen | Office Apps mit KI | bis zu 6 TB Cloudspeicher | Amazon Abonnem ...
zum Angebot »
Excel Mauspad Deutsch Schreibtischunterlage Gaming Mauspad Groß XL Desk Pad Bürozubehör Desk Mat Mousepad 80x30 ...
zum Angebot »
Die Excel Teilergebnis Funktion (SUBTOTAL) im Überblick
Die Funktion TEILERGEBNIS (SUBTOTAL auf Englisch) ist ein mächtiges Werkzeug in Excel, das es dir ermöglicht, Berechnungen wie Summen, Mittelwerte, Zählungen und mehr auf einen bestimmten Bereich von Zellen anzuwenden. Das Besondere daran ist, dass sie automatisch erkennt, ob Zellen in diesem Bereich gefiltert oder ausgeblendet sind, und diese nur optional in die Berechnung einbezieht. Dies ist ein entscheidender Vorteil gegenüber Funktionen wie SUMME oder MITTELWERT, die immer alle Zellen im angegebenen Bereich berücksichtigen, unabhängig von ihrem Sichtbarkeitsstatus.
Wann solltest du die TEILERGEBNIS Funktion nutzen?
Die Hauptanwendung der TEILERGEBNIS Funktion liegt in Szenarien, in denen du mit dynamischen Datensätzen arbeitest. Stell dir vor, du hast eine große Liste von Verkaufsdaten und möchtest:
- Die Summe der Umsätze für eine bestimmte Region anzeigen, nachdem du die Daten nach dieser Region gefiltert hast.
- Die Anzahl der Produkte zählen, die einen bestimmten Status haben, wobei nur sichtbare Produkte berücksichtigt werden sollen.
- Den Durchschnittspreis von Artikeln berechnen, die aktuell in einer Tabelle angezeigt werden, nachdem du unerwünschte Einträge ausgeblendet hast.
In all diesen Fällen liefert dir TEILERGEBNIS genau die Ergebnisse, die du für deine aktuelle Ansicht benötigst, ohne dass du die Formeln manuell anpassen musst, wenn du Filter änderst oder Zeilen manuell ausblendest.
So funktioniert die Excel Teilergebnis Funktion
Die Syntax der TEILERGEBNIS Funktion ist relativ einfach. Sie hat zwei Hauptargumente:
TEILERGEBNIS(Funktion_num; Bezug1; [Bezug2]; ...)
Lass uns die Argumente genauer betrachten:
Funktion_num: Dies ist eine Zahl, die angibt, welche Berechnung die TEILERGEBNIS Funktion durchführen soll. Hier liegt der Kern der Flexibilität der Funktion. Excel bietet eine breite Palette von Zahlen für verschiedene Funktionen, und eine wichtige Unterscheidung ist, ob du ausgeblendete Zeilen einschließen oder ignorieren möchtest.Bezug1; [Bezug2]; ...: Dies sind die Zellenbereiche, auf die die ausgewählte Funktion angewendet werden soll. Du kannst mehrere Bezugsbereiche angeben, genau wie bei vielen anderen Excel-Funktionen.
Die Funktionsnummern: Der Schlüssel zur Flexibilität
Die Funktion_num ist entscheidend für das Verhalten der TEILERGEBNIS Funktion. Die Zahlen von 1 bis 11 beziehen sich auf Funktionen, die ausgeblendete Zeilen in ihrer Berechnung einschließen. Die Zahlen von 101 bis 111 beziehen sich auf Funktionen, die ausgeblendete Zeilen ignorieren. Dies ist der Hauptunterschied und der Grund, warum TEILERGEBNIS so nützlich ist, wenn du mit gefilterten oder sortierten Daten arbeitest.
Hier ist eine Übersicht der wichtigsten Funktionsnummern:
| Funktionsnummer (Ausblenden ignorieren) | Funktionsnummer (Ausblenden einschließen) | Funktion | Beschreibung |
|---|---|---|---|
| 1 | 101 | MITTELWERT (AVERAGE) | Berechnet den Mittelwert der sichtbaren Zellen. |
| 2 | 102 | ANZAHL (COUNT) | Zählt die sichtbaren Zellen, die Zahlen enthalten. |
| 3 | 103 | MAX (MAX) | Gibt den größten Wert in den sichtbaren Zellen zurück. |
| 4 | 104 | MIN (MIN) | Gibt den kleinsten Wert in den sichtbaren Zellen zurück. |
| 5 | 105 | PRODUKT (PRODUCT) | Multipliziert alle sichtbaren Zellen. |
| 6 | 106 | STABW.N (STDEV.N) | Berechnet die Standardabweichung basierend auf einer Stichprobe der sichtbaren Zellen (ignoriert Text und Logikwerte). |
| 7 | 107 | STABW.S (STDEV.S) | Berechnet die Standardabweichung basierend auf einer Grundgesamtheit der sichtbaren Zellen (ignoriert Text und Logikwerte). |
| 8 | 108 | VARIANZ.N (VAR.N) | Berechnet die Varianz basierend auf einer Stichprobe der sichtbaren Zellen. |
| 9 | 109 | VARIANZ.S (VAR.S) | Berechnet die Varianz basierend auf einer Grundgesamtheit der sichtbaren Zellen. |
| 10 | 110 | ZEILENZAHL (ROWS) | Zählt die Anzahl der Zeilen in den sichtbaren Zellen. |
| 11 | 111 | SUMME (SUM) | Summiert die Werte der sichtbaren Zellen. |
| 14 | (keine entsprechende Funktion) | ZÄHLENWENN (COUNTIF) | Zählt Zellen innerhalb eines Bereichs, die ein bestimmtes Kriterium erfüllen. (Beachte: Diese Funktion ist nicht direkt in TEILERGEBNIS integriert, aber TEILERGEBNIS kann mit bestimmten Argumenten ähnliche Ergebnisse erzielen). |
| 15 | (keine entsprechende Funktion) | MITTELWERTWENN (AVERAGEIF) | Berechnet den Mittelwert von Zellen, die ein bestimmtes Kriterium erfüllen. |
Wichtiger Hinweis: Bei der Verwendung der TEILERGEBNIS Funktion mit Filtern ist es fast immer ratsam, die Funktionsnummern von 101 bis 111 zu verwenden, um sicherzustellen, dass nur die aktuell sichtbaren Daten berücksichtigt werden. Wenn du die Zahlen 1 bis 11 verwendest und Zeilen manuell ausblendest (nicht durch Filtern), werden diese Zeilen dennoch in die Berechnung einbezogen. Wenn du jedoch einen Auto-Filter anwendest und Zeilen ausblendest, werden diese bei den Nummern 1-11 ignoriert, aber die manuell ausgeblendeten Zeilen werden immer berücksichtigt. Die Nummern 101-111 ignorieren immer alle ausgeblendeten Zeilen, egal ob durch Filter oder manuelles Ausblenden.
Beispiel: SUMME mit gefilterten Daten
Stell dir vor, du hast eine Liste von Monatsumsätzen in Spalte B (von B2 bis B100) und möchtest nur die Umsätze des ersten Quartals summieren. Du wendest einen Filter auf eine andere Spalte (z.B. Spalte A) an, um nur die Daten für Januar, Februar und März anzuzeigen.
Wenn du einfach =SUMME(B2:B100) verwenden würdest, würde Excel alle Umsätze von B2 bis B100 summieren, auch die der Monate, die du nicht sehen möchtest.
Mit der TEILERGEBNIS Funktion erzielst du das gewünschte Ergebnis:
Gib in eine leere Zelle folgende Formel ein: =TEILERGEBNIS(111; B2:B100)
Hierbei ist:
- 111 die Funktionsnummer für SUMME, die ausgeblendete Zeilen ignoriert (was bei einem aktiven Filter der Fall ist).
- B2:B100 der Bereich, der summiert werden soll.
Sobald du deinen Filter anwendest, um nur die Daten des ersten Quartals anzuzeigen, wird die Formel automatisch nur die sichtbaren Umsätze dieses Quartals summieren.
Fortgeschrittene Anwendungsmöglichkeiten
Die TEILERGEBNIS Funktion kann noch mehr als nur einfache Summen berechnen. Du kannst sie auch für komplexe Analysen verwenden, indem du sie mit anderen Excel-Funktionen kombinierst oder sie in bedingten Formatierungen einsetzt.
Kombination mit anderen Funktionen
Obwohl TEILERGEBNIS viele Funktionen abdeckt, gibt es Szenarien, in denen eine Kombination sinnvoll ist. Zum Beispiel, wenn du die Anzahl der sichtbaren Zellen zählen möchtest, die ein bestimmtes Kriterium erfüllen. Hierfür könntest du TEILERGEBNIS indirekt nutzen, indem du die gefilterten Daten in einem Hilfsbereich summiert oder zählst und dann TEILERGEBNIS auf diesen Hilfsbereich anwendest.
Eine sehr häufige Anwendung ist die Erstellung von dynamischen Zwischensummen oder Überschriften, die sich an den gefilterten Daten orientieren. Du könntest zum Beispiel in einer Zelle oberhalb einer gefilterten Liste anzeigen lassen, welche Kriterien gerade angewendet werden, indem du Werte aus den Filtern ausliest und sie in einer Textzeichenkette verwendest.
Dynamische Berichte mit TEILERGEBNIS
Erstelle dynamische Berichte, die sich automatisch an deine Filterkriterien anpassen. Dies ist besonders nützlich für Dashboards oder Präsentationen, bei denen du dem Publikum erlauben möchtest, verschiedene Ansichten der Daten zu erkunden, ohne dass sich die zugrundeliegenden Berechnungen ändern.
Stell dir eine Tabelle mit Verkaufsdaten vor, die nach Produktkategorien, Regionen und Verkaufsmitarbeitern aufgeteilt ist. Mit TEILERGEBNIS kannst du:
- Die Gesamtumsätze für die aktuell angezeigte Produktkategorie anzeigen.
- Die Anzahl der sichtbaren Verkäufe für die ausgewählte Region berechnen.
- Den durchschnittlichen Bestellwert für den aktuell ausgewählten Verkaufsmitarbeiter ermitteln.
Dies ermöglicht eine interaktive und intuitive Datenexploration.
Tipps und Best Practices für die Excel Teilergebnis Funktion
Um das volle Potenzial der TEILERGEBNIS Funktion auszuschöpfen und häufige Fehler zu vermeiden, beachte die folgenden Tipps:
- Verwende konsequent die Nummern 101-111: Wenn du mit gefilterten oder manuell ausgeblendeten Zeilen arbeitest, sind diese Nummern die sicherste Wahl, um sicherzustellen, dass nur sichtbare Daten einbezogen werden.
- Achte auf den Zellbezug: Stelle sicher, dass der Zellbezug, den du in der TEILERGEBNIS Funktion angibst, korrekt ist und alle relevanten Daten abdeckt.
- Vermeide verschachtelte TEILERGEBNIS Funktionen: Wenn du TEILERGEBNIS in einer Zelle verwendest, die selbst Teil des Zellbereichs einer anderen TEILERGEBNIS Funktion ist, kann dies zu unerwarteten Ergebnissen führen oder die Berechnung verlangsamen. Vermeide diese Verschachtelung, wann immer möglich. Wenn du z.B. eine SUMME mit TEILERGEBNIS (111) berechnest und das Ergebnis dieser Formel in einer Zelle liegt, die Teil des Bereichs einer anderen TEILERGEBNIS (111) ist, wird die erste TEILERGEBNIS Berechnung standardmäßig ignoriert.
- Benenne deine Bereiche: Wenn du mit größeren oder komplexeren Tabellen arbeitest, ist es sinnvoll, die Zellbereiche, die du in deinen TEILERGEBNIS Formeln verwendest, zu benennen. Dies macht deine Formeln lesbarer und einfacher zu verwalten. Gehe dazu zu „Formeln“ > „Namens-Manager“ und erstelle dort einen neuen Namen für deinen Bereich.
- Verstehe den Unterschied zwischen Filtern und manuellem Ausblenden: Wie bereits erwähnt, hat die Wahl der Funktionsnummer einen entscheidenden Einfluss darauf, wie ausgeblendete Zeilen behandelt werden. Nur die Nummern 101-111 ignorieren generell ausgeblendete Zeilen.
Häufige Fehlerquellen und deren Behebung
Obwohl die TEILERGEBNIS Funktion sehr nützlich ist, können Anwender auf einige Schwierigkeiten stoßen. Hier sind die häufigsten Fehler und wie du sie beheben kannst:
- Unerwartete Ergebnisse bei gefilterten Daten: Dies liegt oft an der falschen Wahl der
Funktion_num. Wenn deine Ergebnisse nicht mit den sichtbaren Daten übereinstimmen, überprüfe, ob du eine Nummer zwischen 101 und 111 gewählt hast, um sicherzustellen, dass ausgeblendete Zeilen ignoriert werden. - Fehlerhafte Berechnungen bei manuell ausgeblendeten Zeilen: Wenn du Zeilen manuell ausblendest (nicht mit dem Auto-Filter) und diese dennoch in die Berechnung einbezogen werden, verwende die Funktionsnummern 101-111.
- Zirkelbezüge: Wie bei jeder anderen Excel-Funktion kann die Verwendung von TEILERGEBNIS zu Zirkelbezügen führen, wenn die Formel sich selbst referenziert. Stelle sicher, dass die Zellbezüge in deiner TEILERGEBNIS Formel nicht auf die Zelle verweisen, in der sich die Formel selbst befindet. Excel wird dich in der Regel warnen, wenn ein Zirkelbezug erkannt wird.
- Text in Zahlenbereichen: Wenn dein Datenbereich auch Textwerte enthält, werden diese von den meisten TEILERGEBNIS Funktionen (wie SUMME, MITTELWERT) ignoriert. Bei Funktionen wie ANZAHL werden sie nicht mitgezählt. Stelle sicher, dass deine Datenbereiche konsistent formatiert sind.
FAQ – Häufig gestellte Fragen zu Excel Teilergebnis Funktion verständlich erklärt
Was ist der Unterschied zwischen TEILERGEBNIS und SUMME?
Der Hauptunterschied liegt darin, wie sie mit gefilterten und ausgeblendeten Zeilen umgehen. Die SUMME Funktion berücksichtigt immer alle Zellen im angegebenen Bereich, unabhängig davon, ob sie sichtbar oder ausgeblendet sind. Die TEILERGEBNIS Funktion kann so konfiguriert werden (durch die Wahl der richtigen Funktionsnummer), dass sie nur die sichtbaren Zellen in ihre Berechnung einbezieht.
Kann ich TEILERGEBNIS auch für ausgeblendete Zeilen verwenden, die nicht gefiltert sind?
Ja, das ist möglich. Wenn du Zeilen manuell ausblendest (nicht mit dem Auto-Filter), werden diese Zeilen von Funktionen mit den Funktionsnummern 101-111 ebenfalls ignoriert. Funktionen mit den Nummern 1-11 würden diese manuell ausgeblendeten Zeilen jedoch standardmäßig einschließen.
Welche Funktionen sind mit TEILERGEBNIS am häufigsten anzutreffen?
Die am häufigsten verwendeten Funktionen sind SUMME (mit der Nummer 111), ANZAHL (mit der Nummer 102) und MITTELWERT (mit der Nummer 101). Diese ermöglichen es, schnell zusammenfassende Statistiken über gefilterte Daten zu erhalten.
Was passiert, wenn die TEILERGEBNIS Formel auf eine Zelle verweist, die bereits eine TEILERGEBNIS Formel enthält?
Wenn die TEILERGEBNIS Funktion auf eine Zelle verweist, die selbst eine TEILERGEBNIS Funktion enthält, wird die innere TEILERGEBNIS Funktion standardmäßig ignoriert. Dies dient dazu, Zirkelbezüge und unerwartete Ergebnisse zu vermeiden. Wenn du dieses Verhalten ändern möchtest, was aber selten notwendig ist, müsstest du spezielle Techniken anwenden.
Wie kann ich sicherstellen, dass meine TEILERGEBNIS Formel auch auf neue Daten korrekt reagiert?
Stelle sicher, dass deine Zellbezüge in der TEILERGEBNIS Formel dynamisch sind. Wenn du beispielsweise einen Bereich von A1 bis A100 hast, aber erwartest, dass die Datenliste wächst, verwende einen Zellbezug, der den erwarteten Wachstumsbereich abdeckt (z.B. A1:A1000), oder verwende benannte Bereiche, die sich automatisch erweitern (z.B. durch die Verwendung von Excel-Tabellen).
Gibt es eine Möglichkeit, die Fehlerwerte von TEILERGEBNIS zu ignorieren?
Ja, die TEILERGEBNIS Funktion selbst ignoriert Fehlerwerte in den von ihr ausgewerteten Bereichen, ähnlich wie die SUMME oder MITTELWERT Funktion. Wenn die Funktion selbst einen Fehler zurückgibt (z.B. #WERT!), dann liegt das wahrscheinlich an einem Problem mit der Funktionsnummer oder den Bezügen.
Kann ich TEILERGEBNIS verwenden, um Daten mit mehreren Filtern zu analysieren?
Ja, die TEILERGEBNIS Funktion ist dafür konzipiert, mit gefilterten Daten zu arbeiten. Wenn du mehrere Filter auf deine Tabelle anwendest, wird die TEILERGEBNIS Funktion automatisch nur die Daten berücksichtigen, die allen angewendeten Filterkriterien entsprechen.
