Pivot-Datenquelle automatisch erweitern

Pivot-Tabellen aggregieren innert kürzester Zeit immense Datenmengen. Sobald man der Datenquelle weitere Informationen anfügt, wird dies standardmässig nicht direkt erkannt - der neue Bereich ist jeweils manuell anzupassen:

pivotdatenquelleaendern

Es besteht jedoch die Möglichkeit, die Datenquelle automatisch erweitern zu lassen. Dies funktioniert im Grunde genommen, wie wir das bereits in diesem Beitrag hier mittels Namensgebung für Bereiche behandelt haben. Unserer Pivot-Datenquelle teilen wir auch hier zuerst einen Namen zu: Markiere die gesamte Datenquelle und schreibe ins Namensfeld (links neben der Bearbeitungsleiste) Deinen gewünschten Namen, bspw. "Datenbasis":

pivotdatenquellenamensfeld

Gehst Du nun via "Einfügen" → "PivotTable" kannst Du direkt bei "Tabelle/Bereich:" den definierten Namen ("Datenbasis") eingeben:

(Beachte: Unsere Pivot wird in einem neuen Tabellenblatt sein, nicht im selben wie die Datenbasis.)

Nun ist aber noch nicht konfiguriert, dass sich der Bereich automatisch erweitern soll, sobald weitere Daten angefügt werden (weitere Zeilen, weitere Spalten). Hier kommt die Formel "BEREICH.VERSCHIEBEN" zum Einsatz. Gehe via "Formeln" auf "Namensmanager" und bearbeite Deinen bereits angelegten Namen. In der Maske gibst Du bei "Bezieht sich auf:" die nachfolgende Formel ein:

=BEREICH.VERSCHIEBEN(Datenbasis!$A$1;;;ANZAHL2(Datenbasis!$A:$A);ANZAHL2(Datenbasis!$1:$1))

Auf dem Tabellenblatt mit ebenfalls dem Namen "Datenbasis", beginnend bei Zelle "A1" wird einerseits nach unten (Spalte A:A) gezählt, wie viele Einträge vorhanden sind. Dasselbe passiert auf der Zeile 1.

pivotdatenquellenamensmanager

Der Bereich wird entsprechend daraufhin erweitert, sobald neue Einträge angefügt werden. (Beachte, dass die Pivot-Tabelle noch via Rechtsklick → "Aktualisieren" aufzufrischen ist.)

Automatische Datumsaggregation in Pivots (Excel 2016)

Der Fokus in diesem Beitrag liegt auf einer Änderung für Pivot-Tabellen in Excel 2016 gegenüber älteren Versionen - und zwar werden Datumsformate neu automatisch aggregiert. Schauen wir uns zuerst einen Ausschnitt der Datenbasis an, bevor wir zum Vergleich kommen:

umsatzliste

Es werden also pro Tag die Umsätze pro Produkt aufgeführt. In älteren Excel-Versionen wurden diese Daten als einzelne Tagen in den Spalten ausgewiesen:

pivotdatenalt

Excel 2016 geht nun einen Schritt weiter und führt die Daten automatisch zu Jahren zusammen (in den Grafiken oben wurden der Übersicht halber weniger Einträge gezeigt):

pivotdatumsaggregationneu

Zudem bietet sich die Möglichkeit, die Pivot-Tabelle weiter aufzuklappen - es werden Quartale oder darin auch die jeweiligen Monate angezeigt, wenn man auf das "+" klickt.

Dies ist sicherlich ein tolles Feature. Allerdings muss beachtet werden, dass die zusätzlichen Dimensionen die Zeilen der effektiven Werte nach unten verschieben. Falls Du also in früheren Versionen mittels Formeln Bezug auf die Pivot-Tabelle genommen hattest, musst Du die Verschiebung entsprechend berücksichtigen. Falls Du nach wie vor das alte Format beibehalten möchtest, kannst Du in der Feldliste unter "SPALTEN" die automatisch generierten Felder "Years" und "Quarters" via Drag-And-Drop entfernen (oder in der Auswahlliste oben den Haken entfernen):

pivottablefelderaggregation

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.