Die Excel-Funktion TEILERGEBNIS

1. Das Problem

Mit dem Tabellenkalkulationsprogramm EXCEL ist es möglich, umfangreiche Tabellen zu erstellen und komplexe Berechnungen durchzuführen.

Oftmals macht es sich erforderlich, bestimmte Zeilen auszublenden. Die Funktionen berücksichtigen dennoch die Werte aus den ausgeblendeten Zellen. Das muss aber nicht immer so gewollt sein.

Wenn Du also möchtest, dass die Funktionen auf ausgeblendete Zellen reagieren, kann die Funktion TEILERGEBNIS durchaus hilfreich sein.

Die Funktion vereint viele Rechenoperationen in sich und ermöglicht Dir mehr Kontrolle darüber, welche Zellen von Excel in die Berechnungen einbezogen werden.

Zur Veranschaulichung eine kleine Wertetabelle:

Du willst die Summe aller Werte wissen. Dazu schreibst Du z.B. in Zelle B17 die Formel:

=SUMME(B3:B16)

und erhältst als Ergebnis 2428.

Blende nun z.B. die Zeilen 6 und 7 aus.

Die Summenbildung in B17 bleibt unverändert, obwohl die sichtbare Summe nur 2177 beträgt. Die ausgeblendeten Werte sind allerdings weiter in der Arbeitsmappe enthalten.

Sollte die Summenbildung nun auf die Ausblendungen reagieren, so müsste eine neue Tabelle erstellt werden, die die ausgeblendeten Werte nicht mehr enthält.

Das allerdings kann sehr umständlich werden, wenn noch andere Ausblendungen vorgenommen werden sollen. Mit der Funktion TEILERGEBNIS hast Du eine Alternative. Du kannst die Funktion kann so einstellen, dass ausgeblendete Zellen nicht in die Berechnung einbezogen werden.

2. Die Syntax der Funktion

Diese lautet:

TEILERGEBNIS(Funktion;Bezug1;[Bezug2];…)

Dabei gelten folgende Argumente:

Das Argument Funktion ist erforderlich.

Die Zahlen 1-11 oder 101-111 geben die für das Teilsumme zu verwendende Funktion an.

1-11 enthält manuell ausgeblendete Zeilen, während 101-111 diese ausschließt.

Herausgefilterte Zellen werden immer ausgeschlossen.

Das Argument Bezug1 ist erforderlich. Es steht für den ersten benannten Bereich oder Bezug, für den das Teilergebnis berechnet werden soll.

Das Argument Bezug2;… ist optional. Es können 2 bis 254 benannte Bereiche oder Bezüge benannt werden.

Für die Funktionskonstanten von 1 bis 11 bezieht die Funktion TEILERGEBNIS die über den Befehl Ausblenden ausgeblendeten Werte ein.

Bei den Funktionskonstanten von 101 bis 111 ignoriert die Funktion TEILERGEBNIS Werte, die über den Befehl Ausblenden ausgeblendet wurden.

Die Funktion TEILERGEBNIS ignoriert alle Zeilen, die nicht im Ergebnis eines Filters enthalten sind, unabhängig vom verwendeten Funktionswert.

Wichtig ist noch, zu wissen, dass die Funktion TEILERGEBNIS für Datenspalten oder vertikale Bereiche konzipiert ist. Sie ist nicht für Datenzeilen oder horizontale Bereiche ausgelegt.

3. Die Anwendung der Funktion mit Ausblendungen

Verwende wieder die anfänglich vorgestellte Wertetabelle.

Berechne nun alle Ergebnisse mit Hilfe der (Argument-)Funktionen 1 bis 11:

Bei allen Berechnungen wurde der gesamte Bereich B3:B16 einbezogen, nur bei der Funktion PRODUKT nicht.

Blende nun die Zeilen 6 und 7 der Wertetabelle aus, wie vorhin schon. Du siehst, die Ergebnisse verändern sich nicht.

Berechne nun alle Ergebnisse mit den (Argument-)Funktionen 101 bis 111:

Blende nun wieder die Zeilen 6 und 7 der Wertetabelle aus. Die Ergebnisse verändern sich.

Du siehst, bei der Wahl der Funktionen 101 bis 111 werden die ausgeblendeten Werte ignoriert.

4. Die Anwendung der Funktion beim Filtern

In diesem Abschnitt wird nur mit der Summe als Argument gearbeitet.

Erstelle hierzu eine weitere Wertetabelle, z.B. so:

Die Tabelle enthält Schreibwarenfilialen in unterschiedlichen Städten in MV.

In D14 und D15 sind die Umsatzsummen gebildet worden, einmal mit der Funktion SUMME und zweimal mit der Funktion TEILERGEBNIS.

Markiere B2:D2 und wähle über Daten / Sortieren und Filtern „Filtern“.

Filtere nun die Stadt Güstrow heraus.

Du siehst, die Funktion SUMME lässt das Ergebnis unverändert.

Die Summe (Funktion 9) aus dem TEILERGEBNIS allerdings verändert sich. Es werden nur die Werte summiert, die zur Stadt Güstrow gehören.

Ebenso ist es bei der Summe (Funktion 109) aus dem TEILERGEBNIS.

5. Die Anwendung der Funktion beim Gliedern von Datentabellen

Das Gliedern einer Tabelle mit der Funktion TEILERGEBNIS habe ich Dir in einem früheren Beitrag vorgestellt.

Über den folgenden Link gelangst Du dahin, wenn es Dich interessiert.