Sie haben haben Excel 2013 oder Excel 2016, wollen Excel Power Pivot lernen und suchen 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?

Wenn Sie direkt auf diesem Artikel gelandet sind und nicht so genau wissen, was Excel Power Pivot und Power Query ist, dann lesen Sie einfach zuerst die verlinkten Artikel.

Die Kurzfassung:

Excel 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.

Das Szenario für diesen Power Pivot Quickstart

Stellen Sie sich vor Sie sind der Besitzer einer Firma, die Fahrräder herstellt und Zubehör dafür verkauft. Ihre Firma nennt sich Adventure Works Cycles. Natürlich nutzen Sie ein System mit dem Sie unter anderem Kundenaufträge und Produkte verwalten, Fertigungsaufträge anlegen und Kunden verwalten.

Alle Daten werden in einer Datenbank gespeichert. Die Datenbank zum Herunterladen und nachvollziehen dieses Beispiels findest Sie 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 Sie Excel 2013 oder Excel 2016 haben, sollten Sie im Menü Power Pivot sehen:

Excel Power Pivot Ribbon

Power Pivot müssen Sie eventuell noch aktivieren, wenn es bei Ihnen im Menü nicht sichtbar ist. Daher haben wir hier Installationshinweise bereitgestellt.

Für Excel 2013 müssen Sie Power Query extra installieren. Das AddIn finden Sie direkt bei Microsoft. Power Query heißt bei Microsoft im Excel-Umfeld Get&Transform. Oder deutsch: Daten abrufen und transformieren. Je nachdem, welche Excel-Version Sie haben, sieht das Menü bei Ihnen etwas anders aus. Im Grunde müssen Sie folgende Funktionen sehen:

Excel Power Query Ribbon

Daten laden mit Power Query

Man kann Daten auch ohne Power Query direkt via Excel 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ählen Sie in Excel im Menü Daten unter Daten abrufen den Eintrag Datenbank-Aus Microsoft Access-Datenbank.

Laden von Daten aus Access via Power Query

Sie sehen hier schon, dass es noch reichlich andere Datenquellen gibt, von denen man potentiell Daten laden kann. Im Dialog wählen Sie Ihre lokal gespeicherte Datenbank Adventure Works aus.

Es öffnet sich eine Tabellen-Auswahl. Hier sehen Sie 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 können Sie 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. Sie können 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 Sie alle Tabellen ausgewählt haben, wählen Sie 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ählen Sie Nur Verbindung erstellen und im unteren Teil Dem Datenmodell diese Daten hinzufügen.

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

Wenn Sie diesen Dialog mit OK bestätigen, werden die Daten aus der Access-Datenbank in Ihr Excel Power Pivot Datenmodell geladen.

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

Damit haben Sie 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 erstmal einen Schnelleinstieg.

Das Power Pivot Datenmodell

Jetzt öffnen Sie das Datenmodell im Menü unter Power Pivot und dann Verwalten.

Excel Power Pivot Datenmodell öffnen

Es öffnet sich ein eigenes Fenster, in dem Sie Ihre Daten wiederfinden: das Datenmodell. Hier im Menü können Sie zwischen zwei Ansichten wechseln: der Datenansicht und der Diagrammansicht.

Diagrammansicht und Datenansicht im Excel Power Pivot Datenmodell

In der Datenansicht sehen Sie Ihre importierten Daten. Hier ist jede Tabelle in einem eigenen Reiter organisiert. Also ein bisschen so, wie Sie es von Excel Arbeitsmappen gewohnt sind.

Über Schnellfilter in jeder Spalte können Sie Ihre Daten effizient durchsuchen. So können Sie 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 wissen Sie, 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 haben Sie diese Tabellen 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]

Neue Pivot Tabelle anlegen

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

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) finden Sie 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 ziehen wir 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 können Sie eine zweite Pivot-Tabelle anlegen. Als Ziel verwenden Sie das selbe Arbeitsblatt (die Pivot-Tabellen dürfen sich nicht überschneiden). In die Zeilen fügen Sie den Namen des Produkts ein (Production\Product[Name]) und in die Werte wieder das Feld _LineTotal aus Sales\SalesOrderDetail_. Jetzt bekommen Sie für jedes Produkt den entsprechenden Umsatz dargestellt. Als nächstes müssen Sie für die Top 10 in der Pivot-Tabelle den Wertefilter bemühen:

Top 10 Produkte mit Excel Power Pivot

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

Wertefilter für Top 10 Auswertungen

Fertig! Schon haben Sie die Top 10 Artikel identifiziert.

Diagramm einfügen

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

Standardmäßig legt Excel ein Säulendiagramm an. In dieses neue Säulendiagramm legen Sie 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: Arbeiten Sie immer mit einer Datumstabelle. Natürlich haben wir darüber auch einen Artikel geschrieben.

Löschen Sie 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_ finden Sie 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 Sie dieses Feld in die Pivot-Tabelle mit den Kategorien in die Spalten einfügen, 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. Öffnen Sie dazu erneut das Datenmodell, wechseln Sie in die Datenansicht und den Reiter Sales\SalesOrderHeader. Hier finden Sie ganz rechts eine Spalte mit dem Namen _Spalte hinzufügen. Wenn Sie diese markieren, können Sie, 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 Ihnen 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 können Sie die Spalte einfach umbenennen in Vertriebsweg. Dieses neue Feld erscheint in der Feldliste Ihrer Pivot-Tabelle im Arbeitsblatt. Wenn Sie jetzt das Feld OnlineOrderFlag gegen dieses neue Feld Vertriebsweg austauschen, sieht die Pivot-Tabelle so aus:

Pivot Tabelle mit Berechneter Spalte

Anzahl Kundenaufträge pro Quartal

Fügen Sie erneut ein Diagramm aus dem Datenmodell hinzu. Fügen Sie es in die gleiche Arbeitsmappe ein. Anschließend fügen Sie 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ügen Sie das Feld SalesOrderID aus der gleichen Tabelle ein. Da der Datentyp von SalesOrderID eine Zahl ist, versucht Excel die Werte zu summieren. Daher können Sie ü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ügen Sie Ihr Unternehmenslogo ein – es kostet nicht viel und macht sofort was her.
  • Beschriften Sie Ihre Diagramme mit Überschriften.
  • Blenden Sie die Feldschaltflächen in den Diagrammen aus (Kontextmenü auf eine Feldschaltfläche und dann der Eintrag Alle Feldschaltflächen im Diagramm ausblenden).
  • Schieben Sie die Diagramme logisch zusammen und machen Sie sie gleich hoch/breit.
  • Legenden können im Diagramm weg, wenn ohnehin nur eine Kennzahl verwendet wird.
  • Markieren Sie das Diagramm und wählen 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äsentieren Sie 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 Sie das Measure definieren. Klicken Sie einfach auf eine gewünschte Zelle im Berechnungsbereich und fügen 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 Sie wieder das Excel-Diagramm öffnen und eines der beiden Diagramme markieren finden Sie die neuen Measures in der Feldliste unter Sales\SalesOrderDetail. Wenn Sie jetzt das _LineTotal aus dem Diagramm entfernen (bzw. das Feld SalesOrderID im anderen Diagramm) und gegen die beiden Measures Umsatz Online und Umsatz Retail tauschen (im anderen Diagramm die beiden anderen Measures), müsste Ihr Dashboard so aussehen (die Legenden habe ich wieder eingebaut – jetzt ergeben sie ja wieder Sinn):

Erweitertes Power Pivot Dashboard mit Measures

Slicer

Markieren Sie das erste Diagramm und suchen in der Feldliste das Feld Jahr von OrderDate (das wurde automatisch angelegt). Öffnen Sie das Kontextmenü des Feldes und wählen Als Datenschnitt hinzufügen.

Dashboards mit Slicer

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

image

Erhöhen Sie die Spaltenzahl (rechts) auf 4. Wählen Sie ein Design (ich verwende gerne grau). Öffnen Sie die Berichtsverbindungen und machen bei allen Elemente einen Haken.

Excel Slicer übergreifend verwenden

Positionieren Sie den Slicer über dem Diagramm und schauen, was passiert, wenn Sie Elemente aus dem Slicer auswählen:

Fertiges Dashboard mit Excel Power Pivot

Fertig!

Unten können Sie sich das Ergebnis herunterladen.

Hand aufs Herz: Wollen Sie wirklich noch mit herkömmlichem Excel größere Auswertungen machen? Mit vielen Arbeitsmappen, in denen Ihre Felder in Tabellen und vielen SVERWEISEN zusammengeführt werden? Die Sie dann am besten noch ausblenden müssen? Und Ihr Rechner geht immer mehr in die Knie?