Teilergebnisse - neue Versionen |
Ein weiteres mächtiges Werkzeug von Excel ist die Funktion Teilergebnisse. Über die Möglichkeit hinaus, die ich auf der Seite Autofilter vorgestellt habe, bietet die Funktion noch einiges mehr.
Zunächst wird die Tabelle nach dem Begriff sortiert, der die Grundlage für die Gruppierung der Tabelle bildet. In meinem Beipiel ist das die Spalte Verkäufer. Anschließend wird eine beliebige Zelle in der Tabelle selektiert.
Der Aufruf erfolgt über den Reiter Daten / Gliederung / Teilergebnisse Es öffnet sich das nachfolgende Fenster:
Dort werden verschiedene Einstellungen vorgenommen. Unter Gruppieren nach: wird die Spaltenüberschrift eingestellt, nach der die Tabelle sortiert wurde. Bei Unter Verwendung von: wird ausgewählt welche Teilergebnisse ermittelt werden sollen. Als letzte Einstellung werden unter Bezogen auf: die Spalten ausgewählt, von denen Teilergebnisse gesucht werden. Zu diesem Zweck habe ich Stück Einzelpreis und Gesamtpreis ausgewählt. Mit einem Klick auf OK wird der Vorgang beendet.
Leider kann man sich immer nur für eine "Statistik"funktion entscheiden. Es ist aber möglich, in den verschiedenen Spalten zu unterschiedlichen Teilergebnissen zu kommen. Da muß man ein wenig nachhelfen. In meinem Beispiel wollte ich unter Stück nicht die Summe der Werte haben, sondern die Anzahl der Vorgänge.
Zunächst einmal mußte ich aber die Summen hinnehmen. Danach habe ich dann die entsprechende Spalte (in diesem Fall Spalte E) selektiert und unter Start / Bearbeiten / Suchen und Auswählen / Ersetzen folgende Eingaben getätigt:
Damit wurden die Formeln: =TEILERGEBNIS(9;E2:E87) auf =TEILERGEBNIS(2;E2:E87) geändert. Sie geben nun die Anzahl der Zeilen (sprich Vorgänge) und nicht mehr die Summe der Werte aus.
Die Bedeutung der 11 möglichen Werte, zur Steuerung der Teilergebnisformel habe ich am Ende der Seite aufgelistet.
Leider werden von Excel die automatisch erzeugten Ergebniszeilen nicht besonders gut hervorgehoben:
Mit der Standardfunktion bedingte Formatierung lässt sich das allerdings mit wenig Aufwand korrigieren:
Nun noch einige Anmerkungen zu den "Gruppierungsköpfen" (siehe Abbildung):
Am linken Rand der Tabelle ist eine graue Leiste mit einigen kleinen Schaltflächen entstanden. Wir wollen uns hier kurz mit den Schaltflächen ganz oben, erkennbar durch die Zahlen 1 bis 3 beschäftigen. Drückt man auf die Schaltfäche 1, wird die gesamte Tabelle "versteckt", man sieht nur noch das Gesamtergebnis aus den Teilergebnissen. Drückt man auf die Schaltfläche mit der 2, bekommt man alle Teilergebnisse und die Gesamtsumme(n) angezeigt. Ein Druck auf die Schaltfläche 3 entfaltet die Tabelle wieder zu ihrer vollen Größe.
So sieht die Tabelle aus, wenn die Schaltfläche 2 gedrückt wurde:
Die Schaltflächen mit dem Plus- bzw. Minuszeichen öffnen und schließen den jeweiligen Gruppierungsbereich.
Zum Thema manuelles Gruppieren von Tabellen, werde ich auch noch eine Seite veröffentlichen.
Hier die Auflistung aller möglichen Funktionen in der Formel Teilergebnisse:
Der Wert ersetzt den Buchstaben X in dieser Formel: =TEILERGEBNIS(X;E2:E87)
Funktion | Bezeichnung | Beschreibung |
1 | Mittelwert | Durchschnitt aller Werte |
2 | Anzahl | (Anzahl der Zahlen) |
3 | Anzahl2 | (Anzahl der Werte) |
4 | Max | (Maximalwert) |
5 | Min | (Minimalwert) |
6 | Produkt | Multipliziert alle Argumente |
7 | Stabw | (Standardabweichung von einer Stichprobe) |
8 | Stabwn | (Standardabweichung von der Grundgesamtheit) |
9 | Summe | Summe aller Werte |
10 | Varianz | (Varianz von einer Stichprobe) |
11 | Varianzen | (Varianz von der Grundgesamtheit) |
Weitere Möglichkeiten |
Zunächst betrachten wir noch mal die Dialogbox Teilergebnisse, die wir ja schon ganz zu Anfang kennen gelernt haben. Dort befinden sich am unteren Rand drei Kontrollkästchen mit der jeweils zugehörigen Beschriftung. Das erste Kontrollkästchen hat die Beschreibung Vorhandene Teilergebnisse ersetzen. Stellt sich also die Frage, was passiert, wenn diese Option nicht gesetzt ist?
Zunächst heben wir in unserer Liste die eventuell noch vorhandenen Teilergebnisse auf. Das geht am besten mit der Schaltfläche Alle entfernen am unteren Rand der Dialogbox.
Danach sortieren wir die Liste erstrangig nach den Verkäufern und zweitrangig nach der Spalte Status. Danach wiederholen wir die Anfangs durchgeführte Prozedur zum Erzeugen der Teilergebnisse. Wenn das durchgeführt ist, ändern wir die Parameter in der Dialogbox, so wie es auf dem nächsten Bild zu sehen ist.
Das Ergebnis:
Wie man unschwer erkennen kann, wurde den "alten" Teilergebnissen ein neues zugefügt, das die erledigten und offenen Vorgänge je Verkäufer in ihrer Anzahl darstellt. Die Bedingte Formatierung habe ich gleich mit eingebaut. Das Verfahren ist das gleiche wie beim ersten Mal. Deshalb gehe ich darauf nicht weiter ein. Die Gliederungsebenen am linken Rand haben sich um eine Ebene erweitert.
Eine weitere Option beim Erstsellen von Teilergebnissen ist die Möglichkeit am Ende der Gruppierungen einen Seitenumbruch einzufügen. Dazu wird beim entsprechenden Parameter das Kontrollkästchen angehakt:
Auf dem nächsten Bild sind die gestrichelten Linien zu erkennen, die die Seitenumbrüche kennzeichnen.
Als Ergebnis wird jede Gruppe auf ein separates Blatt gedruckt. Wenn man unter Seitenlayout / Seite einrichten / Drucktitel / Blatt die Überschriftenzeile(n) als Wiederholungszeilen eingerichtet hat, kommt man zu einer perfekten Separierung der Daten, ohne dafür mehrere Seiten anlegen zu müssen.
So sieht das in der Druckvorschau aus:
Die letzte der drei Optionen erklärt sich eigentlich von selbst. Wird hier der standardmäßig gesetzte Haken entfernt, werden die Ergebniszeilen oberhalb der einzelnen Gruppen angezeigt:
Auch zu diesem Beispiel gibt es eine Beispieltabelle im Downloadbereich.