Zwischensummen und Anordnung in Pivot-Tabellen

Ich hoffe, Du hattest ein frohes Osterfest. In Monat März haben wir uns insbesondere mit Pivot-Tabellen befasst. In diesem Beitrag stelle ich Dir eine Möglichkeit vor, wie Du Deine Ergebnisse etwas anders darstellen und einzelne Zwischensummen deaktivieren kannst. Dies kommt vor allem dann zum Einsatz, wenn Du mehrere Felder in der Zeilenbeschriftung verwendest.

Ich stelle Dir hier eine Übungsdatei zur Verfügung, welche ich basierend auf Daten von www.nhl.com erstellt habe – es handelt sich um die Top 50 der aktuellen Skorerliste in der NHL (National Hockey League). Ein schöner Datensatz, mit dem man tolle Pivot-Tabellen erstellen kann!

Top 50 NHL Scorers.xlsx

Die einzelnen Spieler haben entweder Tore (G) oder Assists (A) erzielt – was zusammenaddiert Punkte (P) ergibt. Mehrere Spieler der Top 50 können vom gleichen Team sein – beispielsweise Corey Perry und Ryan Getzlaf von den Anaheim Ducks (ANA). Erstellen wir nun eine Pivot-Tabelle, können wir zum Beispiel "Team" als Zeilenbeschriftung und "P" bei Werte verwenden. Dies gibt die Summe aller Punkte des jeweiligen Teams in den Top50. Ziehen wir nun "Player" unterhalb von "Team" in die Zeilenbeschriftung, erhalten wir zusätzlich noch die Namen der jeweiligen Spieler. Ein Teilausschnitt des Resultats sieht wie folgt aus:

ZwischensummePivot1

Kommen wir nun zu einer Anordnungsmöglichkeit, um den Bericht eventuell besser darzustellen: Wenn Du bei der Zeilenbeschriftung auf "Team" und dann auf "Feldeinstellungen" klickst, kannst Du im Register "Layout & Drucken" die Elementnamen in Tabellenformat anzeigen lassen sowie die selben Elementnamen wiederholen:

PivotZwischensumme2

Nun steht der Teamname in jeder Zeile - eine neue Spalte wurde eingefügt, was unter Umständen die Leserlichkeit und auch die Weiterverarbeitung der Informationen vereinfachen kann.

Als zweites Beispiel können wir folgende Kriterien als Zeilenbeschriftungen verwenden: Position (Pos), Team, Player. Als Werte verwenden wir nach wie vor die Skorerpunkte (P).

PivotZwischensumme3

In den Feldeinstellungen für "Position" wählen wir wiederum wie oben die tabellarische Anzeige sowie die Wiederholung von Elementnamen aus. Bei "Team" gehen wir in den Feldeinstellungen im direkt ausgewählten Register (Teilergebnisse & Filter) bei Teilergebnisse auf "Keine". Nun ist die Pivot-Tabelle deutlich übersichtlicher geworden, da innerhalb der Position die einzelnen Teams nicht länger summiert werden, was den Bericht nur unnötig aufgebläht hat.

Mit diesen beiden Optionen kann die Leserlichkeit deutlich erhöht werden. Insbesondere durch die Wiederholung von Elementen fällt ein Weiterverarbeiten der aggregierten Daten deutlich einfacher aus.

Mittels Formel auf Zellen in einer Pivot-Tabelle zugreifen

Einerseits kann man berechnete Felder wie im kürzlich veröffentlichten Beitrag erstellen, man kann jedoch auch mittels Formeln auf Zellen in einer Pivot-Tabelle zugreifen. Wenn man eine Formel eingeben will und mittels Mausklick (oder mit den Pfeiltasten Deiner Tastatur) auf Werte der Pivot verweist, kommt jedoch automatisch die Formel "Pivotdatenzuordnen":

PivotDatenZuordnen

Um diesen möglicherweise verwirrenden (und eventuell zu präzisen) Bezug zu umgehen, kannst Du direkt "=D4" oder "=D4+D5" als Formel in Deiner Wunschzelle eingeben. Als Alternative kannst Du auch auf die Pivot-Tabelle klicken, im nun verfügbaren Menüpunkt "PivotTable-Tools" → "Optionen" und auf der linken Seite erneut "Optionen" wählen, um dort "GetPivotData generieren" zu deselektieren - nun kannst Du Deine herkömmlichen Bezüge auch via Mausklick in Formeln erfassen.

 

Fehlermeldungen und leere Zellen in einer Pivot-Tabelle steuern

In Pivot-Tabellen kann es zu Fehlermeldungen kommen - insbesondere, wenn Berechnungen vorgenommen werden (siehe auch: berechnetes Feld in einer Pivot-Tabelle). Zudem sind nicht immer zu allen Datengruppen vollständige Informationen vorhanden, was zu leeren Zellen führt. Ich zeige Dir hier, wie Du für diese beiden Fälle bestimmte Werte in der Pivottabelle anzeigen lassen kannst.

Nachdem Du eine Pivot erstellt hast, klickst Du darauf und gehst im nun verfügbaren Menüpunkt "PivotTable-Tools" auf das Register "Optionen" und wählst ganz auf der linken Seite "Optionen" → "Optionen" (so viele Optionen?!):

 

Unmittelbar im angezeigten Fenster kannst Du daraufhin bestimmen, was bei Fehlerwerten und was im Falle von leeren Zellen angezeigt werden soll:

Pivot_Fehlerwerte

Dasselbe Ergebnis (eine Null) für leere Zellen erhältst Du ebenfalls, wenn Du den Haken für diese Option weglässt.

Vielleicht hast Du auch bemerkt, dass es weiter unten die Option "Spaltenbreiten bei Aktualisierung automatisch anpassen" gibt - teilweise zerschmettert genau diese aktive Steuerung die ganzen manuell vorgenommenen Spaltenformatierungen, wenn man nach einem Update der Daten die Pivottabelle aktualisiert.