Wann sind Details und Hierarchien in Pivot-Tabellen wichtig?

In einer Besprechung stellen Sie Ihre Pivot-Tabelle mit den Umsätzen für Produkte und Regionen vor. Die Umsätze sind aggregiert und zeigen den Gesamtumsatz. 

Teilnehmende haben Fragen zum Umsatz der Region Nord. Sie wollen mehr über die Zusammensetzung wissen. Zum Beispiel:

  • Welche Produktgruppen tragen zum Gesamtumsatz der Region bei?
  • Welche Verkaufspositionen ergeben diese Summe?

Für die erste Frage klappen Sie den Bericht auf. Für die zweite lassen Sie die Datensätze hinter dem Betrag auf einem eigenen Arbeitsblatt anzeigen.

Müssen einzelne Umsätze nachvollzogen werden, hilft ein Auszug mit BestellID, Menge und Einzelpreis. So kann derselbe Bericht sowohl einen Überblick geben als auch bei der Prüfung einzelner Beträge helfen.

Dafür nutzen Sie diese Funktionen für Pivot-Tabellen:

  • Erweitern oder Reduzieren (Hierarchie aufklappen): Die nächste Berichtsebene wird in der Pivot-Tabelle sichtbar oder ausgeblendet, etwa die Produktgruppen unter einer Region.
  • Details anzeigen: Ein neues Arbeitsblatt enthält die Datensätze, aus denen sich der ausgewählte Ergebniswert zusammensetzt.

Der Unterschied liegt in der ausgewählten Zelle: An der Beschriftung „Nord“ steuern Sie die Gliederung. Über die Umsatz-Zelle rufen Sie die zugrunde liegenden Datensätze auf.

Details und Hierarchie in der Pivot-Tabelle: Die Beschriftung steuert die Gliederung, die Wertzelle liefert die Datensätze

Excel-Datei als Muster für die Arbeit mit Pivot-Tabellen

Grundlage für die folgenden Erläuterungen und die Anleitung zu Details und Hierarchie sind die Verkaufsdaten und die angelegten Pivot-Tabellen in dieser Excel-Datei.

Die Berichtsebenen festlegen

Ausgangspunkt ist die Pivot-Tabelle im Tabellenblatt UMSATZ_REG_PRODUKT_GLIEDERUNG der Excel-Datei. Das Feld Zeilen enthält Region und darunter Produktgruppe. Mit dieser Reihenfolge bilden die Regionen die äußere und die Produktgruppen die innere Ebene.

Ziehen Sie Produktgruppe über Region, erscheinen stattdessen die Regionen unter jeder Produktgruppe. „Hierarchie“ bezeichnet die gewählte Reihenfolge und Verschachtelung der Zeilenfelder in der Pivot-Tabelle.

Pivot-Tabellen mit zwei Hierarchieebenen im Feld Zeilen

Eine einzelne Region auf- oder zuklappen

Klicken Sie auf das Minuszeichen neben Nord. Excel blendet die darunterliegenden Produktgruppen aus.

Beim Zuklappen bleibt der Regionsumsatz von Nord bei 491.652,95 Euro. Die Produktgruppen werden nur ausgeblendet; ihre Verkaufspositionen bleiben in der Berechnung enthalten. Auch der Regionsanteil am Gesamtumsatz bleibt gleich.

Mit dem Pluszeichen öffnen Sie die Region wieder und zeigen die darunter liegenden Einträge für die Produktgruppen an.

Alternativ wählen Sie per Rechtsklick auf Nord den Befehl Erweitern/Reduzieren und anschließend Erweitern oder Reduzieren.

Erweitern und Reduzieren von Pivot-Tabellen

Das ganze Regionsfeld umschalten

Klicken Sie mit der rechten Maustaste auf eine Regionsbeschriftung. Über Erweitern/Reduzieren > Gesamtes Feld reduzieren blenden Sie die Produktgruppen unter allen Regionen aus.

Gesamtes Feld erweitern zeigt sie wieder an. Der Befehl bezieht sich auf das Feld der ausgewählten Beschriftung – hier also Region. 

Excel-Funktion für das Erweitern und Reduzieren von Pivot-Tabellen

Während einer Besprechung können Sie zunächst alle Regionen zuklappen und anschließend nur Nord öffnen. Die anderen Regionssummen bleiben zum Vergleich sichtbar. Nord zeigt zusätzlich die Aufteilung nach Produktgruppen.

Vom Regionsumsatz zu den Verkaufspositionen

Beim Aufklappen einer Region erscheinen vier Produktgruppen. Zusammen erklären sie den Regionsumsatz.

Sitz und Lenkung liefert mit 158.758,55 Euro den größten Beitrag zum Nord-Umsatz. Soll dieser Betrag untersucht werden, wählen Sie seine Umsatz-Zelle. 

Doppelklicken Sie dazu auf Summe von Umsatz für Sitz und Lenkung in der Region Nord (Zelle B7). Excel legt ein neues Arbeitsblatt mit den zugrunde liegenden Datensätzen an.

Diese Detailtabelle ist ein Auszug  aus der Datenquelle, in dem in der Spalte Region nur Einträge mit „Nord“ und in der Spalte Produktgruppe nur Einträge mit „Sitz und Lenkung“ enthalten sind.

Auszug aus der Datenquelle mit den gewählten Feldern der Pivot-Tabelle

Der gleiche Aufruf ist über Rechtsklick auf die Zahl > Details anzeigen möglich.

In der Beispieldatei umfasst die Region Nord 1.111 Verkaufspositionen. Der Umsatz von 158.758,55 Euro für Sitz und Lenkung in Nord steht für einen kleineren Ausschnitt: 272 Positionen.

Die ausgewählte Ergebniszelle legt fest, welche Datensätze ausgegeben werden.

Welche Informationen im Detailblatt stehen

Im Tabellenblatt DETAIL1 stehen auch Quellfelder, die der Pivot-Bericht nicht zeigt. Die Datenquelle enthält unter anderem PositionsID, BestellID, Kunde, Produkt, Menge, Einzelpreis, Rabatt und Umsatz.

Über die PositionsID lässt sich jede ausgegebene Verkaufsposition eindeutig in der Quelltabelle wiederfinden.

Beachten Sie: Dieser Auszug mit den Details wird nicht aktualisiert, wenn sich die Datenquelle ändert. Notieren Sie deshalb außerhalb der Detailtabelle

  • den Datenstand der Pivot-Tabelle,
  • den Zeitpunkt des Auszugs und
  • gegebenenfalls aktive Filter.

Das Erstellungsdatum allein sagt nicht, wie aktuell die ausgewerteten Daten sind.

Welche Fehler oder Fallen gibt es?

Ein Detailblatt ist eine Kopie

Der Detailauszug ist nicht mit der Quelltabelle oder dem Pivot-Bericht verknüpft. Eine Korrektur auf diesem Blatt wirkt sich deshalb nur auf die Kopie aus.

Berichtigen Sie fehlerhafte Positionen in der Quelle, aktualisieren Sie die Pivot-Tabelle und erstellen Sie anschließend bei Bedarf einen neuen Auszug. 

Zuklappen ist kein Filtern

Das Reduzieren verbirgt die untergeordneten Berichtsebenen. Ein Filter schränkt dagegen die in Berechnungen eingehende Daten ein. Erweitern hebt vorhandene Filter nicht auf.

Wenn nach dem Aufklappen eine erwartete Produktgruppe fehlt, prüfen Sie deshalb auch die Filter des Berichts.

Plus- und Minuszeichen fehlen

Fehlen die Schaltflächen neben Region, klicken Sie in die Pivot-Tabelle und wählen PivotTable-Analyse > Anzeigen > +/− Schaltflächen. 

Ein Doppelklick öffnet nicht die erwarteten Datensätze

Ein Doppelklick auf eine Beschriftung klappt die darunterliegende Ebene auf oder zu.

Beim innersten Feld (im Beispiel die Produktgruppe) öffnet Excel stattdessen das Dialogfeld Details anzeigen, in dem Sie ein weiteres Feld als weitere Ebene (im Beispiel eine 3. Ebene) auswählen können.

Wenn Sie die Details für einen Umsatzwert als Tabelle ausgeben lassen wollen, doppelklicken Sie auf den Umsatzwert in der Pivot-Tabelle – nicht auf das Feld.

Ist Details anzeigen deaktiviert, öffnen Sie per Rechtsklick in der Pivot-Tabelle die PivotTable-Optionen. Prüfen Sie auf der Registerkarte Daten, ob das Kontrollkästchen „Details anzeigen aktivieren“ gesetzt ist. Es steuert den Detailaufruf bei klassischen Pivot-Tabellen. 

Auszug und Bericht passen nicht mehr zusammen

Ein bereits erstelltes Detailblatt wird durch eine Aktualisierung der Pivot-Tabelle nicht mit aktualisiert. Auch ein neuer Auszug verwendet nur den Datenstand, den die Pivot-Tabelle eingelesen hat.

Für eine Prüfung der aktuellen Quelle aktualisieren Sie daher zuerst den Bericht und erstellen anschließend den Auszug mit den Details erneut.

Vorlagen nutzen

Weitere Kapitel zum Thema