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 Excel - Summieren mit der Summenfunktion](/images/office/2007/excel/teilergebnis/excel-teilergebnis-01.gif)
Sie nutzen die Summen-Funktion, um die einzelnen Umsätze zu addieren.
![Excel - Summieren mit der Summenfunktion Excel - Summieren mit der Summenfunktion](/images/office/2007/excel/teilergebnis/excel-teilergebnis-02.gif)
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 - Als Tabelle formatieren](/images/office/2007/excel/teilergebnis/excel-teilergebnis-16.gif)
![Excel - Bereich als Tabelle formatieren Excel - Bereich als Tabelle formatieren](/images/office/2007/excel/teilergebnis/excel-teilergebnis-03.gif)
Prüfen Sie, ob Excel den richtigen Bereich erkannt hat und bestätigen Sie die Aktion mit OK.
![Excel - Bereich als Tabelle formatieren Excel - Bereich als Tabelle formatieren](/images/office/2007/excel/teilergebnis/excel-teilergebnis-04.gif)
Damit ist der Bereich als eigene Tabelle im Tabellenblatt erkannt und die Autofilter wurden aktiviert.
![Excel - Autofilter nutzen Excel - Autofilter nutzen](/images/office/2007/excel/teilergebnis/excel-teilergebnis-05.gif)
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 Excel - Autofilter nutzen](/images/office/2007/excel/teilergebnis/excel-teilergebnis-06.gif)
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 Excel - Parameter der Funktion Teilergbnis](/images/office/2007/excel/teilergebnis/excel-teilergebnis-07.gif)
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 Excel - Parameter der Funktion Teilergbnis](/images/office/2007/excel/teilergebnis/excel-teilergebnis-08.gif)
Nachdem klar ist, was die Funktion machen soll, wird der Bereich angegeben, der auszuwerten ist.
![Excel - Parameter der Funktion Teilergbnis Excel - Parameter der Funktion Teilergbnis](/images/office/2007/excel/teilergebnis/excel-teilergebnis-09.gif)
Auch hier kommt natürlich die 12900 raus. Fangen wir nun wieder mit dem Filter an.
![Excel - Filtern mit der Funktion TEILERGEBNIS Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-10.gif)
Bereich prüfen ... OK klicken.
![Excel - Filtern mit der Funktion TEILERGEBNIS Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-11.gif)
Setzen Sie den Filter über die Region auf "Mitte".
![Excel - Filtern mit der Funktion TEILERGEBNIS Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-12.gif)
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 Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-13.gif)
Auch hier wird korrekt berechnet. Und nun den "Süden".
![Excel - Filtern mit der Funktion TEILERGEBNIS Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-14.gif)
Eine Kombination ist selbstverständlich möglich.
![Excel - Filtern mit der Funktion TEILERGEBNIS Excel - Filtern mit der Funktion TEILERGEBNIS](/images/office/2007/excel/teilergebnis/excel-teilergebnis-15.gif)
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 Excel - Funktion Teilergebnis](/images/office/2007/excel/teilergebnis/excel-teilergebnis-17.gif)
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 Excel - Teilergebnis ohne Gruppierung](/images/office/2007/excel/teilergebnis/excel-teilergebnis-2003-01.gif)
Nur weil als erster Parameter die 109 gesetzt wurde, werden die ausgeblendeten Zeilen nicht berücksichtigt.
![Excel - Teilergebnis ohne Gruppierung Excel - Teilergebnis ohne Gruppierung](/images/office/2007/excel/teilergebnis/excel-teilergebnis-2003-02.gif)
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 Excel 2007 - Breich als Tabelle formatieren](/images/office/2007/excel/teilergebnis/excel-teilergebnis-16.gif)
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 Excel 2007 - Bereich als Tabelle formatiert](/images/office/2007/excel/teilergebnis/excel-bereich-als-tabelle-formatiert-01.gif)
Aber nun zurück zur Funktion TEILERGEBNIS. Das folgende Beispiel zeigt nochmals die Arbeitsmappe mit den ausgeblendeten Zeilen.
![Excel - Teilergebnis gruppiert Excel - Teilergebnis gruppiert](/images/office/2007/excel/teilergebnis/excel-teilergebnis-2007-02.gif)
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 Excel - Teilergebnis gefiltert](/images/office/2007/excel/teilergebnis/excel-teilergebnis-2007-03.gif)
... 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 Excel 2003 - Teilergebnis im Autofilter](/images/office/2007/excel/teilergebnis/excel-teilergebnis-2003-03.gif)
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 Excel - Teilergebnis gegliedert](/images/office/2007/excel/teilergebnis/excel-teilergebnis-18.gif)
Hier können Sie weitere Optionen im Dialog "Teilergebnisse" weitere Optionen aktivieren.
![Excel - Der Dialog Teilergebnisse Excel - Der Dialog Teilergebnisse](/images/office/2007/excel/teilergebnis/excel-teilergebnis-19.gif)
Bestätigen Sie den Dialog mit OK.
![Excel - Gliederung mit Zwischenergebnissen Excel - Gliederung mit Zwischenergebnissen](/images/office/2007/excel/teilergebnis/excel-teilergebnis-20.gif)
Auch im Office Excel 2003 gibt es im Menü "Daten" den Menüpunkt "Teilergebnisse...".
![Excel 2003 - Teilergebnisse Excel 2003 - Teilergebnisse](/images/office/2007/excel/teilergebnis/excel-2003-menuepunkt-teilergebnisse-01.gif)
![Excel 2003 - Teilergebnisse Excel 2003 - Teilergebnisse](/images/office/2007/excel/teilergebnis/excel-2003-menuepunkt-teilergebnisse-02.gif)
![Excel 2003 - Teilergebnisse Excel 2003 - Teilergebnisse](/images/office/2007/excel/teilergebnis/excel-2003-menuepunkt-teilergebnisse-03.gif)
![Excel 2003 - Teilergebnisse Excel 2003 - Teilergebnisse](/images/office/2007/excel/teilergebnis/excel-2003-menuepunkt-teilergebnisse-04.gif)
Lesen Sie unbedingt auch die Online-Hilfe zur Funktion "TEILERGEBNIS".
{xtypo_info}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.{/xtypo_info}
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.