Siehe auch: | Teilergebnis | 

Autor: Klaus-Dieter Oppermann  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Teilergebnis Menü

Autor: Klaus-Dieter Oppermann - Erstellt: ?      - Letzte Revision: 2016-01
Teilergebnisse - die Menüfunktion
Unter dem Reiter Daten / Gliederung befindet sich der Punkt Teilergebnisse.

Die "Namensgleichheit" mit der Tabellenfunktion Teilergebnis kommt nicht von ungefähr. Die Tabellenformel findet auch hier Verwendung. Trotzdem ist alles ein wenig anders. Denn hier wird die Liste automatisch gegliedert. Dadurch wird es möglich, eine Vielzahl von Informationen zu erhalten.

Wichtig: als erstes muß die Tabelle nach dem Kriterium der gewünschten Gliederung sortiert werden. Das ist in der Beispieltabelle schon geschehen. Sie soll nach den Verkäufern gegliedert werden. Zweitrangig habe ich die Liste nach dem Punkt Status sortiert. Zu dem Grund kommen wir später.


Die Ausgangstabelle (Teilansicht)
 ABCDEFGH
1        
2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
6 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
7 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €
8 LPUN31Y0720.01.1999Kulpeerledigt24.765,73 €9.531,45 €
9 LPUN33F0620.08.1999Kulpeoffen3675,84 €2.027,52 €
10 LPUN33Y7021.07.1999Kulpeoffen12172,06 €2.064,68 €
11 LPUN31S1125.08.1999Kulpeoffen771,55 €500,84 €
12 EKAN13S2005.02.1999Lückeerledigt9200,38 €1.803,46 €

Nach dem Aufruf des Menüpunktes öffnet sich eine Dialogbox. Dort können verschiedene Auswahlen getroffen werden. In den einzelnen Auswahlobjekten finden sich unter anderen die Spaltenüberschriften der Tabelle. Deshalb ist es wichtig beim Erstellen von Tabellen bestimmte Kriterien zu beachten. Siehe hier: Regeln für Listen/Tabellen, sonst funktioniert das nicht.
1. Gruppieren nach: dort habe ich Verkäufer ausgewählt.
2. Unter Verwendung von: Hier finden sich die 11 Funktionen aus der Formel Teilergebnisse wieder. Ich habe Summe gewählt.
3. Teilergebnis addieren zu: Hier wird die Spalte ausgewählt, auf die das Ergebnis der Teilergebnis Formel(n) angewendet werden soll. Zunächst habe ich nur eine Spalte ausgewählt. Es ist die Spalte Gesamtpreis. Man kann auch mehrere Spalten wählen, doch dazu später.

Die gegliederte Tabelle
 ABCDEFGH
1        
2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
6   Blumsa Ergebnis   23.496,42 €
7 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
8 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €
9   Enemene Ergebnis   10.476,38 €
10 LPUN31Y0720.01.1999Kulpeerledigt24.765,73 €9.531,45 €
11 LPUN33F0620.08.1999Kulpeoffen3675,84 €2.027,52 €
12 LPUN33Y7021.07.1999Kulpeoffen12172,06 €2.064,68 €
13 LPUN31S1125.08.1999Kulpeoffen771,55 €500,84 €
14   Kulpe Ergebnis   14.124,49 €
15 EKAN13S2005.02.1999Lückeerledigt9200,38 €1.803,46 €
16 EKAN02S9428.05.1999Lückeerledigt61.027,52 €6.165,15 €
17 EKAN03V6121.05.1999Lückeerledigt10200,59 €2.005,94 €
18 EKAN03W2606.11.1998Lückeerledigt8840,77 €6.726,20 €
19   Lücke Ergebnis   16.700,75 €
20 DHGN27Y8420.01.1999Masserkeerledigt3441,02 €1.323,05 €
21 DHGN13Z3709.07.1999Masserkeoffen9952,79 €8.575,14 €
22   Masserke Ergebnis   9.898,19 €
23 EKAN21S06.A05.10.1999Mülbreedoffen1219,37 €232,45 €
24   Mülbreed Ergebnis   232,45 €
25 EKAN01T7418.11.1998Rosenerledigt5491,74 €2.458,72 €
26 EKAN03S05.A20.01.1999Rosenerledigt10558,63 €5.586,34 €
27   Rosen Ergebnis   8.045,06 €
28   Gesamtergebnis   82.973,74 €

Formeln der Tabelle
ZelleFormel
H6=TEILERGEBNIS(9;H3:H5)
H9=TEILERGEBNIS(9;H7:H8)
H14=TEILERGEBNIS(9;H10:H13)
H19=TEILERGEBNIS(9;H15:H18)
H22=TEILERGEBNIS(9;H20:H21)
H24=TEILERGEBNIS(9;H23:H23)
H27=TEILERGEBNIS(9;H25:H26)
H28=TEILERGEBNIS(9;H3:H26)

So sieht die Tabelle aus, nachdem auf der Dialogbox der OK-Button gedrückt wurde. Die Tabelle ist nach den Verkäufern gegliedert, bei jedem Verkäufer wurde eine Gesamtsumme mit der Teilergebnisformel gebildet, die von Excel automatisch eingefügt wurde. Links neben den Zeilenköpfen sind Gliederungssybole in dei Tabelle eingeblendet worde. Die kann man auf unserer Darstellung leider nicht sehen. Damit ist man in der Lage, Teilbereiche der Tabelle ein- und auszublenden. Unten ein Beispiel, wie die Tabelle aussieht, wenn nur noch die Ergebniszeilen sichtbar sind. Auf diese Weise kann man große Tabellen in ihrer Darstellung auf das wichtigste reduzieren.

Die gegliederte Tabelle - Ergebniszeilen
 ABCDEFGH
1        
2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
6   Blumsa Ergebnis   23.496,42 €
9   Enemene Ergebnis   10.476,38 €
14   Kulpe Ergebnis   14.124,49 €
19   Lücke Ergebnis   16.700,75 €
22   Masserke Ergebnis   9.898,19 €
24   Mülbreed Ergebnis   232,45 €
27   Rosen Ergebnis   8.045,06 €
28   Gesamtergebnis   82.973,74 €

Ergebniszeilen formatieren

Nach oben
Leider werden die automatisch erzeugten Ergebniszeile nicht besonders hervor gehoben. Mit der Standardfunktion bedingte Formatierung lässt sich das allerdings mit wenig Aufwand korrigieren. Zunächst wird die Tabelle markiert, die Überschriften spart man dabei am besten aus. Nun wird über den Reiter Start / Formatvorlagen / Bedingte Formatierung das Kontextmenü Bedingte Formatierung aufgerufen. Wir wählen die Option Neue Regel... aus. Es öffnet sich die Diaglogbox Neue Formatierungsregel. Dort wählen wir Formel zur Ermittlung der zu formatiernden Zellen verwenden aus.
In der folgenden Dialogbox wird im Eingabefeld die Formel =RECHTS($D2;8)="Ergebnis" eingetragen. Wichtig ist dabei das Dollarzeichen ($) vor dem Zellbezug, sonst wird nicht die gesamte Zeile formatiert.
Da in diesem Fall nach Spalte D gruppiert wurde, werden in dieser Spalte auch die Texte für die Ergebnisse ausgegeben. Das Wort Ergebnis kommt in jeder dieser Zeilen vor, eignet sich deshalb als Bedingung für die Formatierung.
Nun müssen nur noch die Formate festgelegt werden. Dabei habe ich mich für:
  • Fette Schrift
  • graue Zellrahmen
  • und
  • rosa Zellhintergrund
  • entschieden. So stellt sich das Ergebnis dar (nicht alle gewählten Formate werden hier abgebildet):

    Die formatierte Tabelle

     ABCDEFGH
    1        
    2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
    3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
    4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
    5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
    6   Blumsa Ergebnis   23.496,42 €
    7 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €

    Mehrere Spalten berechnen

    Nach oben
    Was geht noch?
    Natürlich lassen sich Summen auch für mehrere Spalten bilden, das hatte ich weiter oben schon angedeutet. Man muß dazu nur mehrere Spalten auswählen. Soweit ist das kein Problem. Wenn man nun aber zusätzlich noch eine (oder mehrere) andere Formel(n) einbauen möchte, muß man zu einem Trick greifen. Ich möchte nun zusätzich zu den Summen der Anzahl der Artikel, der Einzelpreise und der Gesamtpreise, was alles über die Funktion Summe zu bekommen ist, noch die Anzahl der Positionen pro Verkäufer ermitteln. Wie geht das? Nun ich wähle einfach eine weitere Spalte aus, zum Beispiel die mit dem Status.

    Weitere Ergebnisse


     ABCDEFGH
    1        
    2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
    3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
    4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
    5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
    6   Blumsa Ergebnis0243.888,52 €23.496,42 €
    7 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
    8 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €
    9   Enemene Ergebnis0171.248,54 €10.476,38 €
    10 LPUN31Y0720.01.1999Kulpeerledigt24.765,73 €9.531,45 €
    Jetzt steht in der Spalte Status als Summe eine Null, was ja nicht anders zu erwarten war. Aber.. wenn ich jetzt die Spalte (E) markiere und per suchen - ersetzen die 9 in der Formel gegen eine 3 tausche, ... dann habe ich die Anzahl der Vorgänge, so wie das beabsichtigt war. Nicht schlecht, oder??
    Hinweis: Es ist ratsam, nicht nur die Ziffern einzugeben, sondern die Werte nach diesem Muster: (9 bzw. (3 einzugeben. Sonst besteht die Gefahr, dass Werte getauscht werden, die stehen bleiben müssen. Das wäre in diesem Fall zwar nicht so gewesen, aber der Trick funktioniert natürlich auch, wenn die betreffende Spalte keine Texte enthält.

    Die fertige Tabelle


     ABCDEFGH
    1        
    2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
    3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
    4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
    5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
    6   Blumsa Ergebnis3243.888,52 €23.496,42 €
    7 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
    8 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €
    9   Enemene Ergebnis2171.248,54 €10.476,38 €

    Mit diesen tollen Möglichkeiten ist aber noch lange nicht das Ende der Fahnenstange erreicht. Wir sehen uns nun der Reihe nach die Optionen an, die sich am unteren Rand der Teilergenis-Dialogbox befinden. Auch die haben es in sich.

    Teilergebnisse ersetzen

    Nach oben
    Im unteren Bereich des Teilergebnis-Kontextmenüs befinden sich drei Kontrollkästchen, über die weitere Funktionaltäten aufgerufen werden können. Das erste hat die Bezeichnung Teilergebnisse erstetzen. Es ist stndardmäßig angehakt.
    Das bedeutet, wird eine neue Gliederung aufgerufen, werden die vorherigen Ergebnisse aufgehoben. Was im Umkehrschluss bedeutet, wenn ich den Haken entferne, wird die neue Gliederung über die alte gelegt, ich bekomme eine zusätzliche Gliederungsebene. Das wollen wir uns am voran gegangenen Beispiel ansehen. Eingangs hatte ich ja schon darauf hingewiesen, dass ich die Liste nach einem zweiten Kriterium sortiert habe. Das waren die Inhalte der Spalte Status. Genau diese soll jetzt nach den Inhalten erledigt und offen gegliedert werden. Unten ist das Ergebnis zu sehen. Es werden zusätzlich zu den Gesamtbeträgen auch die Teilergebnisse der Untergliederung "Status" angezeigt.
    Bitte beachten: In diesem Fall habe ich als bei "Unter Verwendung von" die Statistikfunktion Anzahl ausgewählt. Dementsprechend musste nun die oben beschriebene Formeländerung in den drei Summenspalten vorgenommen werden. Der Vorteil dieser Vorgehensweise liegt darin, dass in der Ergebniszeile das Wort Anzahl steht. Somit ist es möglich, über die oben ebenfalls schon beschriebene Vorgehensweise für die Ergebniszeilen eine andere Hintergrundfarbe über die bedingte Formatierung zu erzeugen. Die entsprechende Formel sieht dann so aus: =RECHTS($D2;6)="Anzahl".
    Hinweis: Leider werden bei dieser Vorgehensweise keine Gesamtsummen der Unterergebnisse gebildet. Insofern sollte man die grün markierten Zeilen in diesem Bereich ausblenden. Im Beispiel die Zeilen 35 und 37.

    Zwei Gliederungsebenen

     ABCDEFGH
    1        
    2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
    3 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
    4 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
    5 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
    6   erledigt Anzahl3243.888,52 €23.496,42 €
    7   Blumsa Ergebnis3243.888,52 €23.496,42 €
    8 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
    9 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €
    10   erledigt Anzahl2171.248,54 €10.476,38 €
    11   Enemene Ergebnis2171.248,54 €10.476,38 €

    Seitenumbrüche einfügen

    Nach oben
    Eine weitere Option beim Erstsellen von Teilergebnissen ist die Möglichkeit am Ende der Gruppierungen Seitenumbrüche einzufügen. Dazu wird beim entsprechenden Parameter in der Teilergebnis-Dialogbox das Kontrollkästchen angehakt. Es werden die gestrichelten Linien angezeigt, die die Seitenumbrüche kennzeichnen. Als Ergebnis wird jede Gruppe auf ein eigenes 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.

    Ergebnisse oberhalb der Daten anzeigen

    Nach oben
    Die letzte der drei Optionen erklärt sich eigentlich von selbst. Wird hier der standardmäßig gesetzte Haken bei Ergebnisse unterhalb der Daten anzeigen entfernt, werden die Ergebniszeilen oberhalb der einzelnen Gruppen angezeigt. Sinngemäß gilt auch hier das oben geschriebene, die Zeilen 3 und 5 sollten ausgeblendet werden.

    Ergebnisse über den Gruppen

     ABCDEFGH
    1        
    2 BestellnummerBestelldatumVerkäuferStatusStückEinzelpreisGesamtpreis
    3   Gesamtanzahl181815.555,05 €82.973,74 €
    4   Gesamtergebnis1813715.555,05 €82.973,74 €
    5   erledigt Anzahl333.888,52 €23.496,42 €
    6   Blumsa Ergebnis3243.888,52 €23.496,42 €
    7 DHGN03N4329.09.1999Blumsaerledigt11807,86 €8.886,50 €
    8 EKAN09F5708.12.1998Blumsaerledigt10766,85 €7.668,49 €
    9 EKAN10F1510.12.1998Blumsaerledigt32.313,81 €6.941,43 €
    10   erledigt Anzahl221248,5410476,38
    11   Enemene Ergebnis2171.248,54 €10.476,38 €
    12 EKAN02S2518.11.1998Enemeneerledigt5643,73 €3.218,67 €
    13 EKAN03T2513.10.1997Enemeneerledigt12604,81 €7.257,71 €

    Zum Schluss...

    Nach oben
    ... gibt es am unteren Rand des Teilergebnis-Kontextmenüs drei Schaltflächen. Mit der linken, (alle Entfernen) können die Teilergebnisse und die Gliederungssymbole wieder entfernt werden. Übrig bleibt die Ausgangsliste. Die kann nun ganz normal weiter bearbeitet werden. In der laufenden Sitzung können durch ernauten Aufruf der Teilergebnis-Funktion, die bereits verwendeten Parameter wieder verwendet werden, ohne sie erneut eingeben zu müssen.
    Mit der OK-Schaltfläche wird die Funktion gestartet, wenn alle Parameter ausgewählt sind. mit der Schaltfläche Abbrechen kann man die Funktion vorzeitig verlassen.

    ... noch der Hinweis, dass es unter dem Thema Gruppierung und Gliederung weitere Infomationen zum Thema Gruppieren und Gliedern gibt. Diese Funktionen werden von der Funktion Teilergebnisse mit genutzt. Dort gibt es auch Bilder von den Gliederungssymbolen und Hinweise zu deren Verwendung.

    Fragen stellen im Online-Excel Forum

    Neu in Online-Excel: Online-Excel Newsletter

    Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
    Nach oben