Worum geht es bei Teil- und Gesamtergebnissen in der Pivot-Tabelle?

Ein Teilergebnis fasst die Daten einer Gruppe zusammen, etwa die Umsätze einer Region. Ein Gesamtergebnis führt die Daten über die Gruppen hinweg zusammen.

Beide können eine Summe, einen Mittelwert oder eine andere Kennzahl enthalten. Das Wort „Ergebnis“ sagt also noch nichts darüber aus, wie die Zahl berechnet wurde.

Beispiel: Die Vertriebsleitung möchte die Produktgruppen vergleichen und zugleich wissen, welchen Umsatz jede Region insgesamt erzielt. Mit Teilergebnissen stehen diese Angaben im selben Bericht. Das Gesamtergebnis weist zusätzlich den Umsatz aller berücksichtigten Regionen aus.

Aber: Nicht jede Übersicht braucht alle Ergebniszeilen. Beim Vergleich einzelner Produktgruppen unterbrechen zusätzliche Summen den Lesefluss. Für einen Bericht nach Regionen sind sie dagegen nützlich.

Blenden Sie deshalb die Ergebnisse ein, die der Empfänger für seine Auswertung benötigt. So gehen Sie dazu vor.

Die Ausgangsansicht festlegen

In der Feldliste im Bereich Zeilen stehen zuerst Region und darunter Produktgruppe. In Werte steht das Element Umsatz mit der Zusammenfassung Summe. Die Bereiche Spalten und Filter enthalten zunächst keine Felder.

Der Bericht verwendet das Kurzformat als Berichtslayout. Die entsprechende Pivot-Tabelle befindet sich in der folgenden Musterdatei im Tabellenblatt UMSATZ_REG_PRODUKT_ERG.

Teilergebnisse für das Feld Region einstellen

Klicken Sie mit der rechten Maustaste auf einen Regionsnamen und öffnen Sie Feldeinstellungen.

Auf der Registerkarte Teilergebnisse & Filter wählen Sie unter Teilergebnisse Automatisch, Keine oder Benutzerdefiniert. Bestätigen Sie mit OK.  Die Einstellung betrifft das Feld Region und damit alle Regionen im Bericht.

Einstellungen für Teilergebnisse in Pivot-Tabellen

Diese drei Einstellmöglichkeiten bedeuten:

  • Automatisch: Das Teilergebnis verwendet die Zusammenfassungsfunktion des jeweiligen Wertefelds. Bei Summe von Umsatz erscheint der Regionsumsatz.
  • Keine: Die Ergebniszeilen für Region entfallen. Die Produktgruppenwerte bleiben sichtbar.
  • Benutzerdefiniert: Für das Teilergebnis werden eine oder mehrere angebotene Funktionen gewählt, etwa Summe und Mittelwert.

Beispiel: Mit Automatisch ergibt sich für Nord ein Regionsumsatz von 491.652,95 Euro. Darin stecken 86.749,45 Euro für Bremsen, 107.837,10 Euro für Reifen und Antrieb, 158.758,55 Euro für Sitz und Lenkung sowie 138.307,85 Euro für Zubehör. Über alle vier Regionen beträgt der Umsatz 1.877.213,65 Euro.

Benutzerdefinierte Teilergebnisse gezielt verwenden

Soll unter jeder Region neben der Summe auch der durchschnittliche Positionsumsatz stehen, wählen Sie in den Feldeinstellungen von Region die Option Benutzerdefiniert. Markieren Sie Summe und Mittelwert und bestätigen Sie mit OK.

Mehrere Kennzahlen als Teilergebnis in der Pivot-Tabelle ausgeben

Für die Produktgruppenwerte bleibt es bei der Kennzahl Summe von Umsatz. Für die Regionen gibt es nun zwei Teilergebnisse:

  • Summe Umsatz für eine Region
  • Mittelwert Umsatz für eine Region 

Beispiel: Mit Benutzerdefiniert – Summe erscheint für Nord als Umsatz: 491.652,95 Euro. Mit Benutzerdefiniert – Mittelwert erscheint zusätzlich 442,53 Euro als Mittelwert.

Der Mittelwert wird aus den 1.111 Bestellpositionen der Region berechnet. Er bezieht sich auf die einzelnen Umsatzwerte der Quelle, nicht auf die vier Produktgruppensummen.

Da beide Kennzahlen in derselben Wertespalte stehen, ist ihre Zeilenbeschriftung besonders wichtig. 

Mehrere Kennzahlen als Teilergebnis in der Pivot-Tabelle

Die Position der Teilergebnisse festlegen

Unter Entwurf > Teilergebnisse können Sie die Teilergebnisse für den Bericht ausblenden oder am Anfang beziehungsweise Ende der Gruppen anzeigen.

Position der Teilergebnisse in der Pivot-Tabelle festlegen

Soll nur das Feld Region ohne Teilergebnisse erscheinen, wählen Sie stattdessen Teilergebnisse nicht anzeigen in dessen Feldeinstellungen. 

Hinweis: Im Tabellenformat stehen Teilergebnisse unter der Gruppe. Für automatische Teilergebnisse oberhalb der Gruppe ist das Kurz- oder Gliederungsformat erforderlich.

Für den Regionenbericht ist die Position unten gut lesbar: Auf die Produktgruppenumsätze folgt die Regionssumme. Wenn die Empfänger vor allem die Regionalergebnisse benötigen, bietet sich das Gliederungsformat mit automatischen Teilergebnissen oberhalb der Gruppe an.

Gesamtergebnisse für Zeilen und Spalten unterscheiden

Unter Entwurf > Gesamtergebnisse wählen Sie, ob Ergebnisse für Zeilen, für Spalten oder für beide Richtungen erscheinen.

Entscheidend ist die Rechenrichtung: Ein Ergebnis für eine Zeile steht rechts neben den Werten. Ein Ergebnis für eine Spalte steht darunter.

Gesamtergebnisse einer Pivot-Tabelle einblenden

Dabei gibt es vier Auswahlmöglichkeiten für die Darstellung von Gesamtergebnissen:

  • Für Zeilen und Spalten deaktiviert: Beide Gesamtergebnisse sind ausgeblendet.
  • Für Zeilen und Spalten aktiviert: Ergebnisspalte rechts und Ergebniszeile unten.
  • Nur für Zeilen aktiviert: Ergebnisspalte rechts.
  • Nur für Spalten aktiviert: Ergebniszeile unten.

Der bisherige Bericht hat nur eine Umsatzspalte. Hier zeigt die Ergebniszeile unten den Gesamtumsatz.

Anschaulicher wird der Unterschied zwischen beiden Richtungen, sobald die Umsätze zusätzlich auf mehrere Spalten verteilt sind. Dazu wird ein weiteres Element für die Datenanalyse ausgewählt und in der Feldliste im Bereich Spalten dargestellt: der Vertriebskanal.

Die Rechenrichtung erkennen

Ergänzen Sie in der Feldliste den Vertriebskanal in Spalten und wählen Sie unter Entwurf > Gesamtergebnisse die Option Für Zeilen und Spalten aktiviert. Region und Produktgruppe bleiben in Zeilen, Umsatz bleibt als Summe in Werte.

Beispiel: Für Nord stehen im Regionalergebnis 172.803,20 Euro unter Außendienst, 160.366,05 Euro unter Innendienst und 158.483,70 Euro unter Online. Rechts werden daraus 491.652,95 Euro als Gesamtergebnis. Unten fasst die Gesamtergebniszeile jeden Kanal über alle Regionen zusammen, zum Beispiel 646.532,95 Euro für Außendienst.

Pivot-Tabelle mit Gesamtergebnis für Zeilen und Spalten

Durchschnittswerte auf der richtigen Ebene lesen

Ergänzen Sie zunächst das Feld PositionsID in den Bereich Werte und wählen dafür als Berechnungsart Anzahl.

Ziehen Sie dann den Umsatz ein zweites Mal aus der Feldliste nach Werte. Klicken Sie auf den Pfeil dieses zweiten Wertefelds, öffnen Sie Wertfeldeinstellungen und wählen Sie unter Werte zusammenfassen nach die Funktion Mittelwert.

Mittelwerte für den Umsatz als Teilergebnis und Gesamtergebnis in der Pivot-Tabelle

Für die gezeigte Ansicht stehen Summe von Umsatz, Anzahl von ProduktID und Mittelwert Umsatz2 damit in getrennten Spalten.

In der Datenquelle mit allen 4.320 Verkaufspositionen hat jede Zeile eine ausgefüllte PositionsID und einen numerischen Umsatzwert. Deshalb entspricht die Anzahl der Positionen hier auch der Anzahl der Werte, die in den Mittelwert eingehen.

Beispiel für die Region Nord:

Gesamtumsatz 491.652,95 Euro
÷ 1.111 Positionen
= 442,53 Euro Umsatz Mittelwert aus den Bestellpositionen

Der einfache Durchschnitt der vier für die Produktgruppen angezeigten Gruppenmittelwerte beträgt dagegen 443,63 Euro.

Er gewichtet jede Produktgruppe gleich, obwohl die Gruppen unterschiedlich groß sind: Reifen und Antrieb umfasst 282 Positionen, Sitz und Lenkung 272 etc. Für den durchschnittlichen Positionsumsatz muss jede einzelne Position dasselbe Gewicht erhalten.

Wichtig: Beachten Sie deshalb die unterschiedlichen Rechenwege in der folgenden Abbildung.

Wie der Mittelwert in der Pivot-Tabelle berechnet werden kann

Das automatische Teilergebnis wird aus den zugehörigen Quelldaten berechnet, ebenso das Gesamtergebnis des Wertefelds. Excel mittelt die sichtbaren Gruppenmittelwerte daher nicht nochmals. Im Beispiel ist der Wert 442,53 Euro für Nord richtig; die Abweichung von 443,63 Euro ist kein Rechenfehler.

Welche Fehler oder Fallen gibt es?

Positionswert und Auftragswert verwechseln

Die Bestellungen in der Beispieldatei umfassen jeweils eine bis drei Positionen. Deshalb gehören die 4.320 Umsatzwerte zu nur 2.160 unterschiedlichen BestellIDs. Über alle PositionsIDs beträgt der durchschnittliche Positionsumsatz 434,54 Euro. 

Der durchschnittliche Auftragswert beträgt dagegen 869,08 Euro: 1.877.213,65 Euro Gesamtumsatz geteilt durch 2.160 Bestellungen. Die Einstellung Mittelwert für Umsatz liefert diesen Auftragswert nicht. 

Ein Auftragsdurchschnitt lässt sich berechnen, indem zunächst der Gesamtumsatz jeder Bestellung ermittelt und anschließend der Mittelwert dieser Bestellsummen gebildet wird.

Gleichwertig ist Gesamtumsatz geteilt durch die Anzahl unterschiedlicher Bestellungen. Das ist eine andere Auswertung als der hier gezeigte Positionsdurchschnitt.

Feldeinstellungen und Wertfeldeinstellungen verwechseln

Feldeinstellungen für Region steuern die Teilergebnisse der Regionen. Wertfeldeinstellungen für Umsatz legen fest, wie das Wertefeld rechnet. Ein benutzerdefiniertes Teilergebnis Mittelwert bei Region ändert deshalb weder die Produktgruppensummen noch das Gesamtergebnis des Wertefelds.

Beim Ansichtswechsel die Teilergebnisse nicht zurückstellen

Wenn Summe und Mittelwert in getrennten Wertespalten stehen sollen, stellen Sie Region wieder auf Automatisch. Damit folgt jedes Teilergebnis der Berechnung seiner Wertespalte.

Zuvor gewählte benutzerdefinierte Funktionen würden andernfalls weiter angewendet. Für regelmäßig versandte Berichte sind getrennte, eindeutig benannte Wertespalten meist leichter zu lesen.

Ergebniszeilen ein zweites Mal addieren

Wer einen Berichtsausschnitt als Werte kopiert und anschließend Produktgruppenumsätze und Regionssumme gemeinsam addiert, zählt dieselben Umsätze doppelt. Verwenden Sie für eine solche Berechnung entweder die Produktgruppenwerte oder die Regionalergebnisse.

Ein gefiltertes Ergebnis für den gesamten Datenbestand halten

Ein Filter im Zeilenfeld oder in einem Datenschnitt kann den Bericht einschränken, obwohl der Bereich Filter leer ist.

Prüfen Sie bei einem unerwarteten Gesamtbetrag deshalb auch diese Filter. Das Ausblenden von Ergebniszeilen entfernt dagegen keine Datensätze aus der Auswertung.

Vorlagen nutzen

Weitere Kapitel zum Thema