Worum geht es beim Zusammenfassen von Werten in Pivot-Tabellen?

Typische Fragen für die Datenanalyse in Unternehmen sind:

  • Wie viel Umsatz erzielt eine Produktgruppe?
  • Wie viele Bestellpositionen stehen dahinter?
  • Welchen Wert hat eine Position im Durchschnitt?

Eine Pivot-Tabelle kann diese Kennzahlen nebeneinander anzeigen. Jede beantwortet eine andere Frage.

Werte aus einer Datenquelle können Sie als Summe, Anzahl, Mittelwert, Minimum oder Maximum in der Pivot-Tabelle schnell zusammenfassen und als Kennzahl angeben. Welche Berechnungsart passt, hängt von der Geschäftsfrage ab.

Die wichtigsten Möglichkeiten zur Zusammenfassung von Werten in der Pivot-Tabelle im Überblick:

FunktionBedeutungBeispiel
SummeAddiert die Zahlenwerte.Umsatz einer Produktgruppe
AnzahlZählt nicht leere Einträge im gewählten Feld.Zahl der belegten PositionsIDs
MittelwertBerechnet den arithmetischen Durchschnitt.Durchschnittlicher Umsatz je Position
Min / MaxLiefert den kleinsten bzw. größten Einzelwert.Kleinster bzw. größter Positionsumsatz

Was ist bei der Zusammenfassung von Werten zu beachten?

In der folgenden Beispieldatei steht jede Zeile für eine Bestellposition. Die PositionsID bezeichnet diese einzelne Position; die BestellID ordnet sie einer Bestellung zu.

Weil eine Bestellung mehrere Positionen enthalten kann, kommt dieselbe BestellID in mehreren Zeilen vor. Ein Mittelwert des Felds Umsatz beschreibt hier deshalb den durchschnittlichen Positionswert, nicht den durchschnittlichen Auftragswert. 

Nennen Sie deshalb bei Durchschnitten und anderen Werteberechnungen immer die Bezugsgröße: „Durchschnitt je Position“ und „Durchschnitt je Bestellung“ sind unterschiedliche Kennzahlen.

Die Bezeichnung sollte diesen Unterschied auch dann erkennen lassen, wenn jemand Ihren Bericht ohne weitere Erläuterung liest.

Die Felder für die Auswertung

Grundlage für die folgenden Anleitungen ist die folgende Beispieldatei mit Daten aus Bestellvorgängen.

In der Pivot-Tabelle des Tabellenblatts UMSATZ_PRODUKTGRUPPEN (4) liegen Produktgruppe in Zeilen, Summe von Umsatz in Werte und Vertriebskanal im Filter. Der Berichtsfilter Vertriebskanal steht auf (Alle). Ohne weitere Filtereinschränkung beträgt der Gesamtumsatz 1.877.213,65 Euro. 

Pivot-Tabelle mit Umsatz (Kennzahl) nach Produktgruppe

Umsätze summieren und Positionen zählen

Die Berechnungsart wird in den Wertfeldeinstellungen festgelegt. Sie öffnen den Dialog über den Pfeil neben dem Eintrag im Bereich Werte.

Auswahl der Berechnungsart

Für den Gesamtumsatz wählen Sie unter Werte zusammenfassen nach die Funktion Summe und bestätigen mit OK.

Summe als Berechnungsart für Werte in Pivot-Tabellen

Für die Anzahl der Bestellpositionen ziehen Sie PositionsID zusätzlich in den Bereich Werte. In den Wertfeldeinstellungen dieses Eintrags wählen Sie Anzahl und bestätigen mit OK. Anzahl zählt die nicht leeren Einträge des ausgewählten Felds.

Die PositionsID ist in allen 4.320 Datenzeilen belegt; entsprechend lautet das Gesamtergebnis im Tabellenblatt UMSATZ_BESTELLPOSITIONEN 4.320. 

Sobald mehrere Wertfelder vorhanden sind, ergänzt Excel das Sammelfeld Σ Werte. Im Bereich Spalten sorgt es dafür, dass die Kennzahlen nebeneinander erscheinen. Ihre Reihenfolge richtet sich nach der Reihenfolge der Einträge im Bereich Werte. 

Pivot-Tabelle mit berechneten Werten Umsatz (Summe) und Bestellpositionen (Anzahl)

Beachten Sie:

Anzahl von BestellID liefert keine eindeutige Auftragszahl. Denn dieselbe Bestell-ID steht in mehreren Zeilen. Auch dieses Feld würde 4.320 Einträge zählen, obwohl die Quelle nur 2.160 unterschiedliche BestellIDs enthält. „Anzahl“ entfernt Wiederholungen nicht.

Dasselbe Feld als Mittelwert auswerten

Für eine weitere Kennzahl ziehen Sie das Feld Umsatz erneut aus der Feldliste in den Bereich Werte. So entsteht ein weiterer Eintrag mit eigenen Einstellungen.

Für den zusätzlichen Umsatz wählen Sie in den Wertfeldeinstellungen unter Werte zusammenfassen nach die Funktion Mittelwert und bestätigen mit OK.

Werte in der Pivot-Tabelle Zusammenfassen mit der Funktion Mittelwert

Für Bremsen ergibt sich ein durchschnittlicher Positionswert von 297,97 Euro. Er berechnet sich aus: 339.981,30 Euro geteilt durch 1.141 Positionen.

Das Gesamtergebnis beträgt gerundet 434,54 Euro je Position. Es beruht auf allen 4.320 Positionsumsätzen. Für Bremsen ergibt sich ein durchschnittlicher Positionswert von 297,97 Euro. Er berechnet sich aus: 339.981,30 Euro geteilt durch 1.141 Positionen.

Das Gesamtergebnis beträgt gerundet 434,54 Euro je Position. Es beruht auf allen 4.320 Positionsumsätzen. 

Ergebnis: Mittelwert von Umsatz nach Produktgruppe

Mehrere Kennzahlen übersichtlich anordnen

Mit jeder weiteren Kennzahl wird die Übersicht breiter. Kommt Region zusätzlich in den Bereich Spalten, erscheinen die Kennzahlen für jede Region erneut. Vier Regionen und drei Kennzahlen ergeben bereits zwölf Kennzahlenspalten.

Ergänzen Sie weitere Kennzahlen deshalb nur, wenn die Geschäftsfrage sie braucht. Bei vielen Kennzahlen kann eine Anordnung untereinander lesbarer sein. Ziehen Sie dazu das Sammelfeld Σ Werte von Spalten nach Zeilen. Die gewählten Zusammenfassungsfunktionen bleiben dabei erhalten. 

Zeilenweise Anordnung der zusammengefassten Werte

Kennzahlen benennen und Zahlen formatieren

Die automatisch vergebenen Feldnamen sind für Berichte oft zu lang. Im Dialog Wertfeldeinstellungen können Sie sie unter Benutzerdefinierter Name ersetzen. Das ändert die Beschriftung in der Pivot-Tabelle; die Quellspalte behält ihren Namen.

Für das Beispiel eignen sich folgende Bezeichnungen:

QuellfeldFunktionBenutzerdefinierter NameZahlenformat
UmsatzSummeUmsatz gesamtWährung, €, 2 Dezimalstellen
PositionsIDAnzahlPositionenZahl, 0 Dezimalstellen
UmsatzMittelwertPositionswert im MittelWährung, €, 2 Dezimalstellen

Über Zahlenformat im selben Dialog legen Sie die Darstellung für das gesamte Wertfeld fest.

  • Für die Umsatzkennzahlen wählen Sie Währung mit Euro-Symbol und zwei Dezimalstellen.
  • Für Positionen eignet sich Zahl mit null Dezimalstellen und Tausendertrennzeichen.

Bestätigen Sie beide Dialoge mit OK. 

Benennen Sie die Kennzahl erst, wenn ihre Berechnungsart feststeht. Ändert sich diese später, muss der Name weiterhin dazu passen.

Welche Fehler oder Fallen gibt es?

Anzahl statt Summe: Enthält die Quellspalte Text oder leere Zellen, kann Excel beim Hinzufügen des Felds Anzahl wählen. Das bloße Umschalten auf Summe wandelt als Text gespeicherte Beträge nicht in Zahlen um. Bereinigen Sie deshalb zuerst die Quelle und aktualisieren Sie anschließend die Pivot-Tabelle. 

Eine irreführende Beschriftung: „Aufträge“ wäre für Anzahl von PositionsID falsch. Der Bericht zählt damit Positionen. Der Name der Kennzahl muss zur Berechnung und zur Bedeutung der Datenzeilen passen. 

Die falsche Kennzahl ändert sich: Bei mehrfach verwendeten Feldern besitzt jeder Eintrag eigene Einstellungen. Vor einer Änderung muss deshalb klar sein, ob die Umsatzsumme oder der durchschnittliche Positionswert ausgewählt ist. Eindeutige Namen erleichtern diese Unterscheidung. 

Prozentwerte statt Beträge: Die Registerkarte Werte anzeigen als enthält zusätzliche Berechnungen, etwa Prozentanteile. Für unsere Übersicht ist dort Keine Berechnung vorgesehen. Summe und Mittelwert werden dagegen auf Werte zusammenfassen nach eingestellt. 

Vorlagen nutzen

Weitere Kapitel zum Thema