EDV Workshops

TEILERGEBNIS

Eine der ersten Funktionen, die Sie in Excel beherrschten, wird die Funktion SUMME sein, die Sie auch über das Symbol "Autosumme" schnell im Griff haben. Leider hat das Summieren in manchen Fällen einen kleinen Nachteil, wie folgendes Beispiel zeigt.

Nehmen wir an, Sie müssen die Umsätze einzelner Filialen vergleichen, die über das gesamte Bundesgebiet verteilt liegen. Sie haben die Filialen in Regionen aufgeteilt.

Excel - Summieren mit der Summenfunktion

Sie nutzen die Summen-Funktion, um die einzelnen Umsätze zu addieren.

Excel - Summieren mit der Summenfunktion

Nach ein paar Klicks steht die Gesamtsumme der Umsätze über alle Filialen vor Ihnen. Das war kurz und schmerzlos. Sie erinnern sich aber auch, dass Sie mit Hilfe des "AutoFilters" die Möglichkeit haben, die Daten z. B. nach der Region zu Filtern. Daher formatieren Sie in Office Excel 2007 den Bereich schnell als Tabelle. Klicken Sie hierzu in der Gruppe "Formatvorlagen" auf "Als Tabelle formatieren" und wählen Sie eines der Layouts.

Excel - Als Tabelle formatieren

Excel - Bereich als Tabelle formatieren

Prüfen Sie, ob Excel den richtigen Bereich erkannt hat und bestätigen Sie die Aktion mit OK.

Excel - Bereich als Tabelle formatieren

Damit ist der Bereich als eigene Tabelle im Tabellenblatt erkannt und die Autofilter wurden aktiviert.

Excel - Autofilter nutzen

Nun können die Daten - z. B. nach den Region - gefiltert werden. Ich deaktiviere die Kontrollkästchen vor Norden und Süden und bestätige diese Aktion mit OK.

Excel - Autofilter nutzen

Der Filter hat sofort alle Zeilen ausgeblendet, die nicht zur Region Mitte gehören. Dies erkennt man zum Einen daran, dass die Zeilenköpfe für die Zeile 3-6 und 10-13 fehlen und zum Andere an der hellblauen Einfärbung der Zeilenköpfe.

Nun aber das Problem: Die Funktion Summe, addiert immer alle Werte im Bereich von C3 bis C13. Auch wenn diese ausgeblendet sind. Das Ergebnis bleibt 12900.

Hier kommt die Funktion TEILERGEBNIS ins Spiel, die genau für dieses Problem eine Lösung bietet.

Starten wir wieder mit der Eingabe der Formel.

Excel - Parameter der Funktion Teilergbnis

Die Funktion TEILERGEBNIS erwartet als ersten Parameter die "Funktion", also dass, was die TEILERGEBNIS mit dem anzugebenden Bereich machen soll. TEILERGEBNIS kann nicht nur Summieren, sondern auch einige andere Funktionen auf die Bereiche anwenden. So bedeutet eine "9", dass der Bereich, der noch anzugeben ist, summiert werden soll.

Excel - Parameter der Funktion Teilergbnis

Nachdem klar ist, was die Funktion machen soll, wird der Bereich angegeben, der auszuwerten ist.

Excel - Parameter der Funktion Teilergbnis

Auch hier kommt natürlich die 12900 raus. Fangen wir nun wieder mit dem Filter an.

Excel - Filtern mit der Funktion TEILERGEBNIS

Bereich prüfen ... OK klicken.

Excel - Filtern mit der Funktion TEILERGEBNIS

Setzen Sie den Filter über die Region auf "Mitte".

Excel - Filtern mit der Funktion TEILERGEBNIS

Achten Sie besonders auf das Ergebnis. Die Funktion "TEILERGEBNIS" summiert nur die Zellen, die eingeblendet sind. Ich filtere einfach mal die Region "Norden".

Excel - Filtern mit der Funktion TEILERGEBNIS

Auch hier wird korrekt berechnet. Und nun den "Süden".

Excel - Filtern mit der Funktion TEILERGEBNIS

Eine Kombination ist selbstverständlich möglich.

Excel - Filtern mit der Funktion TEILERGEBNIS

Die Funktion TEILERGEBNIS achtet nur darauf, ob die Zelle ein- oder ausgeblendet ist. Spielen Sie doch mal mit dem Ein- und Ausblenden der Zeilen herum.

Excel - Funktion Teilergebnis

Achten Sie aber darauf, dass einen Zeile komplett ausgeblendet sein muss (Höhe: 0 Pixel)!

 

Bereiche "Als Tabelle formatiert"

Im ersten Abschnitt haben Sie die Funktionsweise der Funktion TEILERGEBNIS kennen gelernt. Hier soll der Unterschied zwischen Bereichen, die "als Tabelle formatiert" wurden und den Bereichen, die eben nicht "als Tabelle formatiert" wurden noch einmal verdeutlicht werden.

Die TEILERGEBNIS unterscheidet zwischen den Funktionen 1-11 und 101-111! Es wird zwischen zwei "Ausblend"-Möglichkeiten unterschieden.  Betrachten wir aber zunächst die Funktionen, die Möglich sind:

 

Funktions-Nr
(bezieht ausgeblendeten Werte mit ein)
Funktions-Nr
(bezieht ausgeblendete Werte nicht ein)
Funktion
1 101 MITTELWERT
2 102 ANZAHL
3 103 ANZAHL2
4 104 MAX
5 105 MIN
6 106 PRODUKT
7 107 STABW
8 108 STABWN
9 109 SUMME
10 110 VARIANZ
11 111 VARIANZEN

 

Hier auch ein Beispiel aus Office Excel 2003. Die Zeilen 3 bis 5 sind ausgeblendet, der Autofilter ist nicht aktiv!

Excel - Teilergebnis ohne Gruppierung

Nur weil als erster Parameter die 109 gesetzt wurde, werden die ausgeblendeten Zeilen nicht berücksichtigt.

Excel - Teilergebnis ohne Gruppierung

Sollten Sie einfach nur die 9 verwenden, so wird jede Zeile (auch die ausgeblendeten) mit ausgewertet!

Das Office Excel 2003 und 2007 betrachten diesen Parameter in Bereichen, die zuvor "als Tabelle formatiert" wurden etwas anders! Im Office Excel 2003 ist ein Bereich als Tabelle formatiert, wenn der Autofilter in den Spaltenüberschriften aktiv ist. In Office Excel 2007 gibt es hierfür die Möglichkeit über die Multifunktionsleiste einen Bereich "als Tabelle zu formatieren".

Excel 2007 - Breich als Tabelle formatieren

Sie haben dann in einem Tabellenblatt einen Bereich "Als Tabelle formatiert". Diese Tabelle hat sogar einen eigenen Namen. Klicken Sie hierzu einfach mal in die Tabelle selbst hinein und wechseln Sie ins Register "Entwurf" der Multifunktionsleiste.

Excel 2007 - Bereich als Tabelle formatiert

Aber nun zurück zur Funktion TEILERGEBNIS. Das folgende Beispiel zeigt nochmals die Arbeitsmappe mit den ausgeblendeten Zeilen.

Excel - Teilergebnis gruppiert

Wie Sie sehen, wertet Excel - trotz der "9" als ersten Parameter - auch ausgeblendete Zeilen nicht aus! Sowohl die "9" als auch die "109" ...

Excel - Teilergebnis gefiltert

... führen zum selben Ergebnis, sobald ein Bereich "als Tabelle formatiert" ist (bzw. in Office 2003 mit dem Autofilter versehen ist).

Excel 2003 - Teilergebnis im Autofilter

Merke: Wenn der Bereich mit dem Autofilter versehen ist (egal ob Office 2003 oder Office 2007), unterscheidet Excel nicht zwischen 9 oder die 109. Ausgeblendete Zeilen werden _nicht_ ausgewertet!!!

 

TEILERGEBNIS in der Gruppierung

Wer noch nicht genug hat, der schaut sich mal die Möglichkeit der Gliederungen mit TEILERGEBNIS an. Im Register "Daten", dort in der Gruppe "Gliederung" gibt es ebenfalls die Möglichkeit die Liste mit dem Teilergebnis zu berechnen.

Excel - Teilergebnis gegliedert

Hier können Sie weitere Optionen im Dialog "Teilergebnisse" weitere Optionen aktivieren.

Excel - Der Dialog Teilergebnisse

Bestätigen Sie den Dialog mit OK.

Excel - Gliederung mit Zwischenergebnissen

Auch im Office Excel 2003 gibt es im Menü "Daten" den Menüpunkt "Teilergebnisse...".

Excel 2003 - Teilergebnisse

Excel 2003 - Teilergebnisse

Excel 2003 - Teilergebnisse

Excel 2003 - Teilergebnisse

Lesen Sie unbedingt auch die Online-Hilfe zur Funktion "TEILERGEBNIS".

Ein wichtiger Hinweis noch: Die Funktion TEILERGEBNIS funktioniert nur in der Vertikalen (also für ausgeblendete Zeilen). Wer das Prinzip für ausgeblendete Spalten (also horizontal) verwendet, wird merken, dass dies eben nicht funktioniert.

Letztlich ist noch der Zusammenhang zwischen der Funktion TEILERGEBNIS und dem Autofilter im Beitrag "Autofilter - Letzte Zeile wird immer mit angezeigt" von Interesse. Da kommt man nicht so einfach drauf.

ENDE DES WORKSHOPS

Weitere Informationen

Natürlich wird im Internet die Funktion auch hundertfach diskutiert. Klar, sie leistet ja auch einiges. Erfahren Sie mehr auf den folgenden Seiten:

 

Anregungen und Bemerkungen zum Artikel nehme ich gern entgegen.