Start › Excel-Funktionen › TEILERGEBNIS
TEILERGEBNIS in Excel: gefilterte und sichtbare Zellen addieren
TEILERGEBNIS berechnet eine Summe, eine Anzahl, einen Mittelwert oder eine andere Statistik für einen Bereich, ignoriert aber die von einem Filter ausgeblendeten Zeilen. Filtern Sie Ihre Liste nach einer Region, und die Summe aktualisiert sich so, dass nur diese Region zählt. Im englischen Excel heißt die Funktion SUBTOTAL.
TEILERGEBNIS · In dieser Anleitung
Syntax von TEILERGEBNIS
=TEILERGEBNIS(Funktion; Bezug1; [Bezug2]; …)| Argument | Bedeutung |
|---|---|
| Funktion | Eine Zahl, die TEILERGEBNIS sagt, welche Berechnung es ausführen soll, etwa 9 für SUMME oder 109 für SUMME ohne ausgeblendete Zeilen. Siehe Tabelle unten. |
| Bezug1; Bezug2; … | Der Bereich oder die Bereiche, für die gerechnet wird. |
Funktionsnummern
| Berechnung | Mit manuell ausgeblendeten Zeilen | Ohne manuell ausgeblendete Zeilen |
|---|---|---|
| MITTELWERT | 1 | 101 |
| ANZAHL (Zahlen) | 2 | 102 |
| ANZAHL2 (nicht leer) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUKT | 6 | 106 |
| STABW | 7 | 107 |
| STABWN | 8 | 108 |
| SUMME | 9 | 109 |
| VARIANZ | 10 | 110 |
| VARIANZEN | 11 | 111 |
1–11 oder 101–111: Beide Varianten ignorieren Zeilen, die ein Filter ausblendet. Der Unterschied liegt bei Zeilen, die Sie selbst ausblenden (Rechtsklick › Ausblenden): 1–11 schließen sie weiter ein, 101–111 lassen sie weg. Im Zweifel nehmen Sie 101–111: Sie berechnen immer nur, was Sie sehen.
TEILERGEBNIS üben: selbst ausprobieren
Lesen ist das eine, selbst tippen das andere. Bauen Sie die Formel unten Schritt für Schritt auf: Die Tipps sagen Ihnen, was Sie eingeben müssen, und Sie können auf Zellen klicken oder darüber ziehen, um sie hinzuzufügen, genau wie in Excel.
Aufgabe: Addieren Sie in B7 die Beträge mit TEILERGEBNIS: =TEILERGEBNIS(9;B2:B6)
Beispiel: eine gefilterte Liste addieren
| A | B | C | |
|---|---|---|---|
| 1 | Region | Verkäufer | Umsatz |
| 2 | Nord | Anna | 1.200 |
| 3 | Süd | Bruno | 800 |
| 4 | Nord | Carl | 950 |
| 5 | Ost | Diana | 450 |
| 6 | Süd | Anna | 700 |
| 7 | Summe | 4.100 |
Ohne Filter zeigt C7 4.100, genau wie SUMME. Filtern Sie nun Spalte A nach „Nord“ (Daten › Filtern). Die Zeilen 3, 5 und 6 werden ausgeblendet, und die Summe wechselt auf 2.150: Nur die sichtbaren Zeilen werden addiert. Eine normale =SUMME(C2:C6) würde weiter 4.100 zeigen.
Sichtbare Zeilen zählen oder mitteln
- Sichtbare Zeilen zählen:
=TEILERGEBNIS(103; A2:A6)zählt die sichtbaren, nicht leeren Zellen. Nach dem Filter auf Nord ergibt es 2. - Mittelwert des Sichtbaren:
=TEILERGEBNIS(101; C2:C6)ergibt 1.075 für den Norden. - Höchster sichtbarer Wert:
=TEILERGEBNIS(104; C2:C6)ergibt 1.200 für den Norden.
Tipp: Setzen Sie die TEILERGEBNIS-Formeln über die Liste statt darunter. So bleiben sie sichtbar, wenn die Liste wächst.
TEILERGEBNIS ignoriert andere Teilergebnisse
Enthält ein Bereich Zellen, die selbst TEILERGEBNIS verwenden, werden diese übersprungen. So können Sie Teilergebnisse pro Gruppe und ein Gesamtergebnis über die ganze Spalte setzen, ohne etwas doppelt zu zählen. Genau das macht der Befehl Daten › Teilergebnis in Excel: Er fügt TEILERGEBNIS-Formeln für jede Gruppe und eine für das Gesamtergebnis ein. Auch die Ergebniszeile einer Excel-Tabelle (Strg+T, dann Tabellenentwurf › Ergebniszeile) verwendet TEILERGEBNIS.
Häufige Fehler
- Die Summe ändert sich nicht, wenn Sie Zeilen manuell ausblenden. Sie haben 1–11 verwendet. Wechseln Sie zu 101–111.
- Ausgeblendete Spalten werden weiter mitgezählt. TEILERGEBNIS ignoriert nur ausgeblendete Zeilen. Ausgeblendete Spalten in einem waagerechten Bereich überspringt es nicht.
- #WERT! Die Funktionsnummer liegt nicht zwischen 1–11 oder 101–111.
- Sie müssen auch Fehler ignorieren. Verwenden Sie AGGREGAT, das wie TEILERGEBNIS funktioniert, aber zusätzlich Fehlerwerte überspringen kann:
=AGGREGAT(9; 7; C2:C6)
Häufige Fragen
Was ist der Unterschied zwischen TEILERGEBNIS 9 und 109?
Beide addieren nach einem Filter nur die sichtbaren Zeilen. 109 überspringt auch manuell ausgeblendete Zeilen; 9 schließt sie weiter ein.
Wie addiere ich in Excel nur sichtbare Zellen?
Verwenden Sie =TEILERGEBNIS(109; Bereich). Es ignoriert per Filter und manuell ausgeblendete Zeilen.
Warum TEILERGEBNIS statt SUMME?
SUMME addiert immer alle Zellen des Bereichs. TEILERGEBNIS respektiert Filter und überspringt andere Teilergebnisse: Es liefert also jederzeit die richtige Summe dessen, was sichtbar ist.
Was ist AGGREGAT?
AGGREGAT ist eine leistungsfähigere Version von TEILERGEBNIS, verfügbar seit Excel 2010. Es bietet mehr Berechnungen und kann auch Fehlerwerte ignorieren.