Du hast Excel 2013 oder Excel 2016 und willst Power Pivot lernen und suchst nach einem Einstieg? In diesem Artikel machen wir zusammen einen Schnelldurchlauf durch Excel Power Pivot und streifen Excel Power Query.

Was ist eigentlich Excel Power Pivot und Power Query?

Datenauswertungen in Excel sind eigentlich seit vielen Jahren weit verbreitet. Excel Pivot-Tabellen und Diagramme sind beliebt. Excel stößt aber immer mehr an seine Grenzen. Die Beschränkung auf eine Million Zeilen ist die eine harte Grenze. Allerdings sind Performance und Wartbarkeit (von mit vielen SVERWEISEN verknüpften Tabellen) schon lange vorher absolute Effizienz-Killer.

Power Pivot gibt es seit Excel 2010. Dort musste man es noch extra installieren. Mit Excel 2013 war es dann automatisch dabei. Dafür kam in Excel 2013 Power Query hinzu. Eigentlich ist Power Query der Nachfolger von MS Query. MS Query ist die wirklich ganz alte Variante, um Daten in Excel zu laden. Seit Excel 2016 sind beide Werkzeug integriert.

Die Aufgabenteilung ist kurz gesagt: Mit Power Query werden Daten geladen, mit Power Pivot werden sie ausgewertet.

Ziel dieses Power Pivot Tutorials

Stelle dir vor du bist der Besitzer einer Firma, die Fahrräder herstellt und entsprechendes Zubehör verkauft. Deine Firma nennt sich Adventure Works Cycles. Daher nutzt du ein System mit dem du unter anderem Kundenaufträge und Produkte verwaltest, Fertigungsaufträge anlegst und deine Kunden hinterlegst.

Alle Daten werden in einer Datenbank gespeichert. Die Datenbank zum Herunterladen und nachvollziehen dieses Beispiels findest du in unserem Blog Beitrag.

In unserem ersten Dashboard möchten wir sehen:

  • Wie hat sich der Umsatz pro Quartal entwickelt?
  • Wie hat sich die Anzahl Kundenaufträge pro Quartal entwickelt?
  • Wieviel Umsatz haben wir pro Produktkategorie gemacht – getrennt nach Online und Retail Aufträgen?
  • Was sind unsere Top 10 Artikel (gemessen am Umsatz)?

Das scheint für einen Schnelleinstieg viel. Ist aber mit Excel Power Pivot tatsächlich ganz einfach.

Voraussetzungen

Wenn du Excel 2013 oder Excel 2016 hast, solltest du im Menü Power Pivot sehen:

Excel Power Pivot Ribbon

Power Pivot musst du eventuell noch aktivieren, wenn es bei dir im Menü nicht sichtbar ist. Daher haben wir hier Installationshinweise bereitgestellt.

Für Excel 2013 musst du Power Query extra installieren. Das AddIn findest du direkt bei Microsoft. Power Query heißt bei Microsoft mittlerweile auch Get&Transform. Oder deutsch: Daten abrufen und transformieren. Je nachdem, welche Excel-Version du hast, sieht das Menü bei dir etwas anders aus. Im Grunde musst du folgende Funktionen sehen:

Excel Power Query Ribbon

Daten laden mit Power Query

Man kann Daten auch ohne Power Query direkt via Power Pivot laden. Das ist historisch bedingt, da es in der ersten Version von Excel Power Pivot noch kein Power Query gab. Und in unserer ersten Version dieses Artikels haben wir die Daten auch noch direkt geladen. Das machen wir nicht mehr und empfehlen es daher auch nicht.

Um die Daten aus der Access-Datenbank zu laden, wählst du in Excel im Menü Daten unter Daten abrufen den Eintrag Datenbank-Aus Microsoft Access-Datenbank.

Laden von Daten aus Access via Power Query

Du siehst hier schon, dass es noch reichlich andere Datenquellen gibt, von denen du potentiell Daten laden kannst. Im Dialog wählst du deine lokale gespeicherte Datenbank Adventure Works aus.

Es öffnet sich eine Tabellen-Auswahl. Hier siehst du links alle in Access verfügbaren Tabellen und Abfragen. Und auf der rechten Seite wird eine Vorschau der jeweils selektierten Tabelle angezeigt.

Power Query Navigator zur Tabellenauswahl

Da wir nicht jede Tabelle einzeln importieren wollen, können wir oben links Mehrere Elemente auswählen anklicken. Dann kannst du die Tabellen über die Checkbox vor der Tabelle in der Liste auswählen.

Die Textbox oben ist eine Volltextsuche und zwar eine Volltextsuche über die Tabellennamen. Du kannst also einfach einen Teil des Tabellennamens eintragen, um die richtige Tabelle zu suchen. Folgende Tabellen sollen ausgewählt werden:

  • Sales_SalesOrderHeader: alle Kundenaufträge der Firma.
  • Sales_SalesOrderDetail: die Positionen eines Kundenauftrags.
  • Production_Product: der Materialstamm, also Artikelnummer, Name, etc.
  • Production_ProductCategory: Produkte werden in Kategorien eingeteilt, wie Fahrräder, Zubehör, Kleidung.
  • Production_ProductSubcategory: Kategorien werden nochmals in Subkategorien unterteilt.

Wie kommen die Daten ins Datenmodell?

Wenn du alle Tabellen ausgewählt hast wählst du unten die Option Laden In.

!["Laden In" - Laden der Daten von Power Query ins Datenmodell](622imagethumb-38.png ""Laden In" - Laden der Daten von Power Query ins Datenmodell") Es erscheint ein Dialog in dem wir dafür sorgen, dass die Daten nicht in Excel Arbeitsmappen geladen werden, sondern in das Excel Power Pivot Datenmodell. Im oberen Teil wählst du Nur Verbindung erstellen und im unteren Teil Dem Datenmodell diese Daten hinzufügen.

Excel Optionen zum Datenimport: Wohin sollen die Daten geladen werden?

Wenn du diesen Dialog mit OK bestätigst, werden die Daten aus der Access-Datenbank in dein Excel Power Pivot Datenmodell geladen.

Hinweis: Wenn du die Excel-Datei jetzt weitergibst, sind die Daten weiterhin vorhanden. Das heißt dein Kollege benötigt die Access-Datenbank nicht.

Damit hast du die Daten via Power Query geladen. Natürlich ohne weitere Funktionen von Power Query zu nutzen, wie beispielsweise Filter, Auswahl von Spalten, Formatierung von Daten usw. Wir wollen ja nur einen Schnelleinstieg.

Das Power Pivot Datenmodell

Jetzt öffnest du das Datenmodell im Menü unter Power Pivot und dann Verwalten.

Excel Power Pivot Datenmodell öffnen

Es öffnet sich ein eigenes Fenster, in dem du deine Daten wiederfindest: das Datenmodell. Hier im Menü kannst du zwischen zwei Ansichten wechseln: der Datenansicht und der Diagrammansicht.

Diagrammansicht und Datenansicht im Excel Power Pivot Datenmodell

In der Datenansicht siehst du deine importierten Daten. Hier ist jede Tabelle in einem eigenen Reiter organisiert. Also ein bisschen so, wie du es von Excel Arbeitsmappen gewohnt bist.

Über Schnellfilter in jeder Spalte kannst du deine Daten effizient durchsuchen. So kannst du beispielsweise prüfen, wie viele Aufträge online und wie viele über die Ladentheke (Retail) abgewickelt worden sind (das Feld OnlineOrderFlag in der Tabelle Sales\SalesOrderHeader_). Die Schnellfilter haben aber für die Auswertungen später keine Auswirkungen.

Datenansicht und Schnellfilter im Excel Power Pivot Datenmodell

Beziehungen anlegen

Sicher weißt du, dass in relationalen Datenbanken Tabellen über Schlüssel verknüpft sind. Jede Zeile in SalesOrderDetail (also einer Auftragsposition) hat nicht alle Daten des verkauften Artikels (Product), sondern nur einen Schlüssel auf die entsprechende Zeile in der Tabelle Product. In Excel hast du diese Tabellen vermutlich mit SVERWEIS (oder INDEX) zusammengeführt.

Im Excel Power Pivot Datenmodell arbeiten wir modellbasiert. Das bedeutet, wir zeigen Power Pivot, wie die Tabellen zusammenhängen. Daher verknüpfen wir die Schlüssel der Tabellen in der Diagrammansicht mit der Maus, in dem wir die jeweiligen Schlüsselfelder zweier Tabellen aufeinander ziehen.

Beziehungen anlegen im Excel Power Pivot Datenmodell

Excel Power Pivot erkennt eigenständig, dass in einer Tabelle der Schlüssel eindeutig (Production\Product) und in der anderen Tabelle mehrdeutig ist (Sales_SalesOrderDetail_). Die Verknüpfungen führen wir für alle Tabellen durch, so dass wir am Ende folgende Modell haben.

Komplettes Datenmodell für das Beispiel

Die Schlüsselpaare sind:

  • Sales_SalesOrderHeader[SalesOrderID] – Sales_SalesOrderDetail[SalesOrderID]
  • Sales_SalesOrderDetail[ProductID] – Production_Product[ProductID]
  • Production_Product[ProductSubcategoryID] – Production_ProductSubcategory[ProductSubcategoryID]
  • Production_ProductSubcategory[ProductCategoryID] – Production_ProductCategory[ProductCategoryID]

Pivot Tabelle anlegen

Auf Basis dieses Modells kannst du jetzt deine erste Pivot-Tabelle anlegen. Dazu wählst du Datenmodell-Fenster im Menü unter Start-PivotTable die entsprechende Funktion. Anschließend kannst du wählen, ob du die neue Pivot-Tabelle im aktuellen oder in einem neuen Arbeitsblatt anlegen möchtest.

Wie bei herkömmlichen Pivot-Tabellen legt Excel einen Pivot-Tabellenbereich in einer Arbeitsmappe an. Ist der Bereich gewählt (eine Zelle in dem Bereich genügt) findest du auf der rechten Seite die Feldliste. Diese sieht fast so aus, wie die Feldliste herkömmlicher Pivot-Tabellen. Nur sind die Felder gruppiert unter den Tabellen dargestellt und nicht in einer einfachen Liste.

Als erstes werten wir aus, wie viele Umsatz wir pro Produktkategorie generiert haben. Dazu benötigen wir das Feld Name aus der Tabelle Production\ProductCategory_ und das Feld LineTotal aus SalesOrderDetail. Das Feld Name ziehst du mit der Maus in den Pivot-Bereich Zeilen, das Feld in den Bereich Werte.

Erste Pivot-Tabelle mit Excel Power Pivot

Durch das Modell und die angelegten Beziehungen zwischen den Tabellen, hat Power Pivot für jede Produktkategorie (Production\ProductCategory) alle zugehörigen Subkategorien (Production_ProductSubcategory) und alle zugehörigen Produkte (Production_Product) ermittelt. Und dann hat Power Pivot alle zugehörigen Zeilen in _Sales\SalesOrderDetail_ gefiltert und das Feld LineTotal summiert. Ganz ohne SVERWEIS. Und das ganze ziemlich performant!

Top 10 Produkte

Aus dem Datenmodell kannst du eine zweite Pivot-Tabelle anlegen. Als Ziel verwendest du das selbe Arbeitsblatt (die Pivot-Tabellen dürfen sich nicht überschneiden). In die Zeilen fügst du den Namen des Produkts ein (Production\Product[Name]) und in die Werte wieder das Feld _LineTotal aus Sales\SalesOrderDetail_. Jetzt bekommst du für jedes Produkt den entsprechenden Umsatz dargestellt. Als nächstes musst du für die Top 10 in der Pivot-Tabelle den Wertefilter bemühen:

Top 10 Produkte mit Excel Power Pivot

Im Dialog gibst du an, dass du die obersten 10 Elemente nach Summe von LineTotal haben möchtest.

Wertefilter für Top 10 Auswertungen

Fertig! Schon hast du die Top 10 Artikel identifiziert.

Diagramm einfügen

Um den Umsatz über die Zeit als Diagramm darzustellen fügst du aus dem Datenmodell-Fenster von Power Pivot ein Diagramm ein. Das findest du im Menü an der gleichen Stelle, wo du eine Pivot Tabelle einfügst. Das neue Diagramm platzierst du wieder auf der gleichen Arbeitsmappe.

Standardmäßig legt Excel ein Säulendiagramm an. In dieses neue Säulendiagramm legst du in die Achse das Feld OrderDate aus der Tabelle Sales\SalesOrderHeader._ Jetzt erkennt Excel, dass es sich um ein Datum handelt und erzeugt automatisch weitere Felder, wie Jahr, Monat und Quartal. Und für diesen Schnelleinstieg ist das auch in Ordnung. Darüber hinaus gilt: Arbeite immer mit einer Datumstabelle. Natürlich haben wir darüber auch einen Artikel geschrieben.

Lösche jetzt aus der Achse des Diagramms außer den Felder Jahr und Quartal alle anderen Felder wieder raus. Und dann sollte Diagramm sollte in etwa so aussehen:

Erstes Diagramm mit Excel Power Pivot

Einfache berechnete Spalten

In der Tabelle Sales\SalesOrderHeader_ findest du ein Feld OnlineOrderFlag. Dieses Feld ist ein Boole’sches Feld, hat also nur die Werte Wahr (True) und Falsch (False). Wahr bedeutet hier, dass es sich um einen Online-Auftrag handelt. Wenn du dieses Feld in die Pivot-Tabelle mit den Kategorien in die Spalten einfügst, verändert sich die Pivot-Tabelle:

Erweiterung der Kategorie Auswertung um Online/Retail Information

So richtig schön sind die Überschriften FALSCH und WAHR allerdings nicht. Aber das können wir schnell ändern. Öffne dazu erneut das Datenmodell, wechsle in die Datenansicht und den Reiter Sales\SalesOrderHeader. Hier findest du ganz rechts eine Spalte mit dem Namen _Spalte hinzufügen. Wenn du diese markierst kannst du, wie aus Excel bekannt, in der Bearbeitungsleiste oben eine Formel eingeben. Die Formel lautet:

Berechnete Spalte in Excel Power Pivot anlegen

Die Formel muss ich dir vermutlich nicht erklären. Sie fühlt sich an wie eine Excel-Formel. Diese neue Spalte heißt aktuell noch Calculated Column 1. Via Doppelklick kannst du die Spalte einfach umbenennen in Vertriebsweg. Dieses neue Feld erscheint in der Feldliste deiner Pivot-Tabelle im Arbeitsblatt. Wenn du jetzt das Feld OnlineOrderFlag gegen dieses neue Feld Vertriebsweg austauscht, sieht die Pivot-Tabelle so aus:

Pivot Tabelle mit Berechneter Spalte

Anzahl Kundenaufträge pro Quartal

Füge erneut ein Diagramm aus dem Datenmodell hinzu. Füge es erneut in die gleiche Arbeitsmappe. Anschließend fügst du in die Achse die berechneten Felder Jahr und Quartal von OrderDate in der Tabelle Sales\SalesOrderHeader_ ein. Damit ist die x-Achse identisch wie beim oberen Diagramm.

In die Werte fügst du das Feld SalesOrderID aus der gleichen Tabelle ein. Da der Datentyp von SalesOrderID eine Zahl ist, versucht Excel die Werte zu summieren. Daher kannst du über das Kontextmenü des Felds in die Wertfeldeinstellungen wechseln und dort die Aggregation von Summe auf Anzahl ändern.

Wertfeldeinstellungen

Layout

Sex sells oder Akzeptanz durch Firlefanz hört sich lustig an, ist aber tatsächlich wichtig. Hier ein paar Ideen für unser aktuelles Dashboard:

  • Hintergrund markieren und eine Farbe setzen (weiß oder ein leichtes grau – halten Sie sich immer an Unternehmensfarben).
  • Logo: Füge dein Unternehmenslogo ein – es kostet nicht viel und macht sofort was her.
  • Beschrifte die Diagramme.
  • Blende die Feldschaltflächen in den Diagrammen aus (Kontextmenü auf eine Feldschaltfläche und dann der Eintrag Alle Feldschaltflächen im Diagramm ausblenden).
  • Schiebe die Diagramme logisch zusammen und mache sie gleich hoch/breit.
  • Legenden können im Diagramm weg, wenn ohnehin nur eine Kennzahl verwendet wird.
  • Markiere das Diagramm und wähle im Kontextmenü Diagrammbereich formatieren. Wähle im Punkt Rahmenfarbe den Eintrag Keine Linie.

Für die Präsentation:

  • Im Excel-Menü Ansicht alle Haken entfernen bei Gitternetzlinien, Bearbeitungsleiste und Überschriften.
  • Darstellen, z.B. auf einem Beamer, immer im Vollbildmodus.
  • Und ganz zum Schluss präsentiere das Dashboard immer im Vollbildmodus – zu finden unter Ansicht/Ganzer Bildschirm.

Und so könnte es dann aussehen:

Erstes Excel Power Pivot Dashboard

Noch eine Erweiterung gefällig?

Wir können beim Umsatz und bei den Aufträgen pro Quartal jeweils zwei Balken anzeigen: Online und einmal Retail Aufträge getrennt. Dazu führen wir zwei Measures ein.

Measures werden in der Datensicht im Power Pivot Datenmodell-Fenster unterhalb im Berechnungsbereich definiert. Auch wenn es scheint, dass die Spalten sich dort fortsetzen, ist es völlig unerheblich in welche Zelle du die Measure definierst. Klicke einfach auf eine gewünschte Zelle im Berechnungsbereich und füge in der Bearbeitungsleiste (ganz Excel-like) die entsprechende Formel ein.

Wir definieren die Measures in der Tabelle Sales_SalesOrderDetail.

Measures in Excel Power Pivot

Die vier Formeln lauten:

Wenn du wieder dein Excel-Diagramm öffnest und eines der beiden Diagramme markierst findest du die neuen Measures in der Feldliste unter Sales\SalesOrderDetail. Wenn du jetzt das _LineTotal aus dem Diagramm entfernst (bzw. das Feld SalesOrderID im anderen Diagramm) und gegen die beiden Measures Umsatz Online und Umsatz Retail tauscht (im anderen Diagramm die beiden anderen Measures), müsste dein Dashboard so aussehen (die Legenden habe ich wieder eingebaut – jetzt ergeben sie wieder Sinn):

Erweitertes Power Pivot Dashboard mit Measures

Slicer

Markiere das erste Diagramm und suche in der Feldliste das Feld Jahr von OrderDate (das wurde ja automatisch angelegt). Öffne das Kontextmenü des Feldes und wähle Als Datenschnitt hinzufügen.

Dashboards mit Slicer

Es erscheinen Schaltflächen mit den Jahren. Wenn du das Element mit den Schaltflächen (Datenschnitt oder Slicer genannt) findest du in Excel oben ein neues Menü Datenschnittools. Dort findest du weitere Einstellungen.

image

Erhöhe die Spaltenzahl (rechts) auf 4. Wähle ein Design (ich verwende gerne grau). Öffne die Berichtsverbindungen und mache bei allen Elemente einen Haken.

Excel Slicer übergreifend verwenden

Positioniere den Slicer über dem Diagramm und schaue, was passiert, wenn du Elemente aus dem Slicer auswählst:

Fertiges Dashboard mit Excel Power Pivot

Fertig!

Das Ergebnis kannst du hier herunterladen.

Hand aufs Herz: Willst du wirklich noch mit herkömmlichem Excel Auswertungen machen? Mit vielen Arbeitsmappen, in denen deine Felder in Tabellen und vielen SVERWEISEN zusammengeführt werden? Die du dann am besten noch ausblenden musst? Und dein Rechner geht immer mehr in die Knie?

Starthilfe und Training

Wenn du einsteigen willst, findest du hier weitere Artikel:

Du und deine Kollegen möchten schneller einsteigen? Dann kommen wir gerne vorbei für ein Inhouse Training oder einen HandsOn-Workshop.

Und dann gibt es noch unseren Online-Kurs, der dir eine Einführung in Power Pivot gibt.