Heute geht meine kleine Artikel-Serie zur Einführung in Power-Pivot in die nächste Runde. Auch ohne tiefer in das Datenmodell und die dort möglichen Berechnungen einzudringen, zeige ich dir ein paar kleine aber feine Annehmlichkeiten, die Power-Pivot bietet und die mit normalen Pivot-Tabellen nicht möglich sind.
Außerdem gehe ich auf eine Einschränkung bei Power Pivot ein, die sich bei näherer Betrachtung sogar als Vorteil erweist.
Und los geht’s:
Solltest du die beiden ersten Artikel zur Einführung ins Datenmodell und Power Pivot verpasst haben, kannst du sie hier nachlesen:
- Teil 1: Einführung in das Datenmodell und Power Pivot
- Teil 2: Power Pivot. Oder: Gute Beziehungen sind alles
Und noch eine wichtige Information für alle Mac-Anwender:
Power Pivot – und auch Power Query – ist im Augenblick dort nicht bzw. nur sehr eingeschränkt verfügbar. Microsoft arbeitet zwar daran, das zu ändern und erste Funktionen scheinen im Beta-Kanal von Microsoft 365 für Mac bereits verfügbar zu sein. Genaueres kann ich mangels Mac-Computer dazu jedoch auch nicht sagen.
Das Filter-Dilemma
Die Beispieldatei kannst du dir hier herunterladen. Ich habe dort eine kleine reguläre Pivot-Tabelle vorbereitet (kein Power Pivot!). Darin werden Verkaufszahlen nach Region und Verkäufer dargestellt, und zwar einmal in absoluten Zahlen und einmal als prozentualer Anteil am Gesamtergebnis:
Normale Pivot-Tabelle mit %-Anteilen
Der Anteil des Verkäufer Müller aus der Region Nord am Gesamtergebnis liegt beispielsweise bei 6,35%. Soweit, so gut.
Nun lege ich einen Filter an und lasse mir nur noch die Region Nord anzeigen. Und jetzt sieht das Ergebnis völlig anders aus. Zum besseren Vergleich habe ich die Pivot-Tabelle kopiert und neben die erste, ungefilterte Version gelegt:
Bei Filterung ändern sich Gesamtergebnis und Anteile
Die Anteile haben sich verändert, denn sie beziehen sich jetzt nur noch auf das gefilterte Gesamtergebnis. Diese Darstellung kann im Einzelfall natürlich durchaus gewünscht sein. Aber es kann eben auch für einen anderen Betrachter verwirrend sein, denn das Ergebnis Nord wird hier mit dem Gesamtergebnis gleichgestellt.
Schön wäre es, wenn man in solch einer gefilterten Pivot-Tabelle wahlweise auch das echte Gesamtergebnis sehen könnte – und damit verbunden auch die entsprechenden %-Anteile. Mit einer normalen Pivot-Tabelle ist das leider nicht möglich. Aber du ahnst es sicherlich schon: Mit Power Pivot geht das!
Ich habe die gleiche Pivot-Tabelle nachgebaut und zwar auf Basis des Datenmodells. Also eine Power-Pivot-Tabelle. Zur besseren Unterscheidung habe ich lediglich eine andere Formatvorlage gewählt:
Die gleiche Auswertung mit Power Pivot
Auch hier kopiere ich die Pivot und filtere die Kopie nach der Region Nord. Zunächst ist auch hier kein Unterschied zu erkennen, es werden die gleichen Werte geliefert wie in der normalen Pivot-Tabelle:
Identische Ergebnisse in der Voreinstellung
Aber jetzt kommt’s: Um den gewünschten Effekt zu erzielen, muss nur eine kleine Einstellung in den PivotTable-Optionen geändert werden. Im Registerblatt „Summen & Filter“ setzen wir das Häkchen bei „Gefilterte Elemente in Summen einschließen“. Die darunterliegende Option wird automatisch mit angekreuzt:
PivotTable-Optionen aufrufen
Gefilterte Elemente in Summen einschließen
Und nun sieht die Sache gleich ganz anders aus. Auch die gefilterten Anteile beziehen sich jetzt wieder auf des Gesamtergebnis. Zusätzlich sind die beiden Ergebniszeilen auch noch mit einem Sternchen gekennzeichnet, so dass man erkennt, dass es sich hierbei um gefilterte (beim Teilergebnis) bzw. ungefilterte Werte (beim Gesamtergebnis) handelt.
Gesamtergebnis und Anteile ändern sich
Das ist doch ziemlich praktisch, oder?
In der „normalen“ Pivot-Tabelle ist diese Einstellung nicht vorhanden:
Einstellung fehlt in normalen Pivot-Tabellen
Pivot-Tabelle in Formeln umwandeln
Eine weitere interessante Möglichkeit, die Power Pivot bietet und die in normalen Pivot-Tabellen nicht vorhanden ist: Man kann die gesamte Pivot-Tabelle mit ein paar Klicks in eine Tabelle mit Formeln umwandeln.
Steht die aktive Zelle innerhalb einer Power Pivot-Tabelle, dann gibt es im Menü „PivotTable-Tools“ die Schaltfläche „OLAP-Tools“, hinter der sich der Eintrag „In Formeln konvertieren“ verbirgt:
Power Pivot in Formeln konvertieren
Dabei gehen zwar die Formatierungen aus der Pivot-Tabelle verloren, aber wie man in der Bearbeitungszeile sieht, sind die Inhalte nicht statisch, sondern werden über spezielle Formeln erzeugt:
Erstellt automatisch Cube-Funktionen
Dabei handelt es sich um die sogenannten CUBE-Funktionen, über die es möglich ist, auch ohne Pivot-Tabellen direkt auf das Datenmodell zuzugreifen. Die Einzelheiten dazu sollen uns heute nicht interessieren, auf die CUBE-Funktionen werde ich noch in einem gesonderten Artikel eingehen.
Durch diese Formeln eröffnen sich dem Anwender ganz praktische Möglichkeiten. Denn man wird noch flexibler in der Darstellung seiner Auswertungen, kann zum Beispiel zusätzliche Spalten oder Zeilen einfügen (was in einer Pivot-Tabelle ja nicht möglich ist). Oder man hat ein Dashboard erstellt, in dem man nur Auszüge oder gar nur einen ganz bestimmten Wert aus dem Datenmodell benötigt und wo eine Pivot-Tabelle aus Platzgründen oder optischen Aspekten nicht in Frage kommt.
Mehr Flexibilität im Umgang mit Datumsangaben
Aus normalen Pivot-Tabellen kennst du das vielleicht schon: Zieht man ein Datumsfeld in den Zeilenbereich, dann nimmt Excel in der Regel eine automatische Gruppierung vor, so dass man beispielsweise seine Ergebnisse auch gleich nach Jahren oder Monaten auswerten kann (siehe auch Verdichten und Gruppieren in Pivot-Tabellen)
Nutzt man hingegen Power Pivot und das Datenmodell, dann war diese praktische Gruppierungsfunktion in älteren Excel-Versionen leider nicht möglich. Mittlerweile klappt das aber auch wieder (zumindest in Microsoft 365). Aber ganz egal, ob das in deiner Excel-Version funktioniert oder nicht, bietet Power Pivot ohnehin ein viel besseres Instrument für den Umgang mit Datumsangaben:
Die sogenannten Datumstabellen.
Dazu müssen wir einen kleinen Ausflug in den Power Pivot-Editor machen: Menü „Power Pivot | Verwalten“
Das Datenmodell aufrufen
Mein Datenmodell enthält mehrere Tabellen, unter anderem das Blatt mit den Rechnungen (tblRechnungen), in dem sich auch eine Spalte mit dem Rechnungsdatum befindet:
Rechnungstabelle mit Datumsfeld
Öffnet man jetzt im Menü „Entwurf“ die Schaltfläche „Datumstabelle“ und wählt die Option „Neu“, dann geschieht etwas fast Magisches:
Eine neue Datumstabelle erstellen
Ohne weiteres Zutun analysiert Excel die vorhandenen Datumswerte in der Rechnungstabelle und erstellt eine neue Tabelle namens „Calendar“, welche jeden einzelnen Tag enthält, beginnend beim ersten Januar im Jahr des frühesten Rechnungsdatums und endend mit dem 31. Dezember im Jahr des letzten Rechnungsdatums. Und es werden auch gleich noch ein paar zusätzliche Spalten mit weiteren Datumsinformationen generiert:
Automatisch erzeugte Datumstabelle
Wenn das nicht genial ist!
Um nun mit dieser Datumstabelle arbeiten zu können, müssen wir sie nur noch mit der Rechnungstabelle verbinden. Dazu schalten wir über die entsprechende Schaltfläche von der Datensicht in die Diagrammsicht:
Zur Diagrammsicht wechseln
In meiner Beispieldatei sind bereits einige Tabellen mit der Rechnungstabelle verbunden. Die neu erstelle Datumstabelle „Calendar“ liegt noch etwas abseits ohne Verbindung. Um diese zu erstellen, klicken wir dort das Feld „Date“ an und ziehen es mit der Maus auf das Feld „ReDatum“ in der Rechnungstabelle:
Beziehung zwischen den Tabellen erstellen
Dann lassen wir die Maus los und sehen die neue Verbindung zwischen den beiden Tabellen:
Die neue Beziehung
Jetzt können wir den Power Pivot-Editor wieder schließen und zu Excel zurückkehren. Im Arbeitsbereich PivotTable-Felder finden wir neben allen anderen Tabellen jetzt die neue Datumstabelle mit ihren Feldern und der Datumshierarchie:
Datumstabelle in den PivotTable-Feldern
Jetzt brauchst du nur noch das gewünschte Datumsfeld auswählen und beispielsweise in den Zeilenbereich der Pivot-Tabelle ziehen, um die Daten entsprechend zu gruppieren:
Pivot-Tabelle nach Datum gruppiert
Das war doch wirklich einfach, oder? So eine Datumstabelle bringt noch eine Reihe weiterer Vorteile mit sich und kann im Datenmodell noch jederzeit um zusätzliche Zeiteinheiten wie z.B. Quartale erweitert werden. Darauf werde ich aber in einem zukünftigen Beitrage noch eingehen.
Ich hoffe, du bekommst langsam ein Gefühl für die Vorteile, die Power Pivot gegenüber normalen Pivot-Tabellen bietet. Und wir haben dabei immer noch erst an der Oberfläche gekratzt!
Wie sieht es aus: Hast du bereits Erfahrungen mit Power Pivot gemacht? Lass es uns unten in den Kommentaren wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ja, ich habe mit Power Pivot auch schon gearbeitet. Den Sinn von einer Datumstabelle und deren „Erzeugungautomatik“ aber erst jetzt durch Deine Ausführungen erkannt.
Vielen Dank.
Ich werde meine Anwendung mit diesem Wissen überarbeiten.
Hallo Gerhard,
danke für dein Feedback. Das Konzept mit den Datumstabellen ist anfangs sicherlich gewöhnungsbedürftig, aber es bietet immer mehr Möglichkeiten, je tiefer man vordringt.
Schöne Grüße,
Martin