Power Pivot und das Datenmodell in Excel: Ein Leitfaden für Einsteiger

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was ist ein Datenmodell und wie unterscheidet es sich von einer normalen Excel-Tabelle?

A: Das Datenmodell ist eine In-Memory-Datenbank, die in deiner Excel-Datei integriert ist. Es kann mehrere miteinander verknüpfte Tabellen enthalten, Hunderte von Millionen Zeilen verarbeiten und Berechnungen mithilfe einer Sprache namens DAX durchführen. In diesem Artikel erklären wir, was das in der Praxis bedeutet.

Q: Muss ich noch irgendwas zusätzlich kaufen, um Power Pivot nutzen zu können?

A: Nein, Power Pivot ist in Excel 365, Excel 2021 und Excel 2019 enthalten. Standardmäßig ist es ausgeblendet, aber du kannst es als COM-Add-In aktivieren. In diesem Artikel erklären wir dir genau, wie das funktioniert.

Q: Ich würde gern wissen, weshalb man sich statt SVERWEIS für dieses Datenmodell entscheiden sollte, wenn es darum geht, zwei Tabellen miteinander zu verknüpfen.

A: SVERWEIS ist eine Formel, die pro Zelle einmal ausgeführt wird. Wenn die Tabelle zum Beispiel 500.000 Zeilen hat, kann es zu 500.000 langsamen Suchvorgängen kommen. Im Gegensatz dazu ist eine Beziehung im Datenmodell eine dauerhafte Verknüpfung. Excel löst sie unabhängig vom Umfang sofort auf, sodass du nie wieder eine Suchformel schreiben musst.

Q: Was ist DAX und muss ich eine Programmiersprache lernen?

A: DAX (Data Analysis Expressions) ist eine für das Datenmodell entwickelte Formelsprache. Die einfachsten Formeln, wie SUM (SUMME), AVERAGE (MITTELWERT) und COUNTROWS (ZEILENZÄHLEN), sehen fast genauso aus wie die in Excel. Dieser Artikel beginnt mit diesen Formeln und erläutert das eine neue Konzept, das DAX auszeichnet: den Filterkontext.

Q: Was ist der Unterschied zwischen einer Kennzahl und einer berechneten Spalte?

A: Jede Filteranwendung rechnet eine Kennzahl neu aus. Das macht sie besonders gut für Summen, Kennzahlen und Prozentsätze. Mit einer berechneten Spalte kannst du der Tabelle eine neue Spalte mit einem festen Wert pro Zeile hinzufügen. Die kannst du dann für Bezeichnungen und Kategorien verwenden. In dem Artikel geht's um beide Themen.

Was ist Power Pivot eigentlich?

Öffne einfach eine normale Excel-Pivot-Tabelle und du wirst sehen, dass es nur eine flache Tabelle auf einem Arbeitsblatt ist. Die Power-Pivot-Engine, auch Datenmodell genannt, ersetzt diese durch einen viel leistungsfähigeren, komprimierten Spaltenspeicher im Arbeitsspeicher. Der kann mehrere miteinander verknüpfte Tabellen enthalten, Millionen von Zeilen ohne Geschwindigkeitsverlust verarbeiten und Berechnungen mithilfe einer speziell für analytische Aufgaben entwickelten Formelsprache durchführen.

Das Datenmodell findest du in deiner .xlsx-Datei. Du brauchst keinen externen Server und auch keine separate Installation oder ein Tool. Es ist Excel, aber mit einer viel leistungsfähigeren Engine dahinter!

Power Pivot aktivieren

Power Pivot ist standardmäßig deaktiviert. Sobald es aktiviert ist, bleibt es aktiviert.

1. Gehe zu Datei → Optionen → Add-Ins
2. Wähle unten im Dropdown-Menü Verwalten die Option COM-Add-Ins aus und klicke auf OK
3. Setze ein Häkchen bei Microsoft Power Pivot für Excel
4. Klicke auf OK

A In deiner Multifunktionsleiste erscheint nun die Registerkarte Power Pivot. Diese Funktion ist in Excel 365, Excel 2021 und Excel 2019 verfügbar. In Excel für Mac ist sie nicht verfügbar; dort können Datenmodelle zwar gelesen, aber nicht erstellt werden.

Es ist entscheidend, zu wissen, wann man es einsetzt und wann nicht.

Power Pivot ersetzt Excel nicht, sondern erweitert die Funktionen, wenn Excel an seine Grenzen stößt. Bevor wir weitermachen, sollten wir uns kurz darüber verständigen, wo die Grenze liegt.

Nimm einfach Excel, wenn deine Daten in eine Tabelle passen, die Zeilenzahl unter 200.000 liegt und eine normale Pivot-Tabelle mit SUMME- oder MITTELWERT-Funktionen die gewünschten Ergebnisse liefert. Das deckt den Großteil der alltäglichen Analysen ab.

Wenn du zu Power Pivot wechselst, solltest du folgende Punkte beachten:

• Du musst zwei oder mehr Tabellen ohne SVERWEIS verknüpfen.
• Deine Daten sind echt umfangreich und die Datei ist ziemlich langsam.
• Du brauchst irgendwelche Berechnungen, zum Beispiel Prozentanteile der Gesamtsumme, Jahreswerte oder gleitende Durchschnitte.
• Du erstellst jeden Monat denselben Bericht mit frisch exportierten Daten.

Das Datenmodell besteht aus mehreren Tabellen und einem Modell.

Das wichtigste Konzept in Power Pivot ist die Beziehung. Du kannst auch Excel sagen, wo die Verbindung zwischen den Tabellen ist, wenn du nicht die Daten mit SVERWEIS von einer Tabelle in die andere kopieren willst. Ab da löst Excel die Verbindung automatisch in jedem Umfang und bei jeder Interaktion mit der Pivot-Tabelle auf.

Faktentabellen und Dimensionstabellen

In jedem gut strukturierten Datenmodell gibt es zwei Arten von Tabellen.

In einer Faktentabelle findest du für jeden Verkauf, jede Bestellung oder jedes Ereignis eine Zeile mit den Transaktionsdaten. Die sind meistens ziemlich lang und haben viele Zeilen. Eine „Verkaufs"-Tabelle mit Spalten wie „Bestell-ID", „Datumsschlüssel", „Kunden-ID", „Produkt-ID", „Betrag" und „Menge" ist eine typische Faktentabelle.

In Dimensionstabellen findest du beschreibende Daten wie zum Beispiel eine Zeile pro Kunde, eine Zeile pro Produkt und eine Zeile pro Datum. Die sind kleiner und zeigen dir die Bezeichnungen und Kategorien, anhand derer du deine Fakten analysieren kannst. Eine „Kunden"-Tabelle mit einer Zeile pro Kunde (Kunden-ID, Name, Region, Segment) ist eine typische Dimensionstabelle.

Die Spalte, die als Verbindung zwischen den beiden Tabellen dient, wird in der Dimensionstabelle als Primärschlüssel (PK) und in der Faktentabelle als Fremdschlüssel (FK) bezeichnet. In einer Dimensionstabelle ist pro Schlüsselwert eine Zeile. In der Faktentabelle gibt's mehrere Zeilen pro Schlüsselwert. Das ist so eine Eins-zu-Viele-Beziehung.

Beziehungen erstellen

1. Lade deine Tabellen in das Datenmodell. Klicke in jede Excel-Tabelle, wechsle zur Registerkarte Power Pivot und klicke auf Zum Datenmodell hinzufügen. Wiederhole das für jede Tabelle.
2. Klicke im Power Pivot-Fenster auf der Registerkarte „Start“ auf Diagrammansicht. Deine Tabellen werden als Kästchen angezeigt.
3. Ziehe die Primärschlüsselspalte aus der Dimensionstabelle auf die entsprechende Fremdschlüsselspalte in der Faktentabelle. Zwischen beiden erscheint eine Linie, das ist die Beziehung.

Ab diesem Zeitpunkt kann jede mit dem Datenmodell erstellte Pivot-Tabelle Felder aus allen verknüpften Tabellen kombinieren, ohne dass SVERWEIS benötigt wird.

DAX ist die Formelsprache des Datenmodells.

DAX steht für „Data Analysis Expressions“. Es ist die Sprache, mit der Berechnungen innerhalb des Datenmodells erstellt werden. Viele DAX-Funktionen haben dieselben Namen wie ihre Excel-Entsprechungen, zum Beispiel SUM (SUMME), AVERAGE (MITTELWERT), MIN, MAX und IF (WENN), und funktionieren ähnlich. Der wesentliche Unterschied besteht darin, dass DAX-Formeln immer auf ganze Spalten oder Tabellen angewendet werden und nicht auf einzelne Zellen.

Deine ersten Kennzahlen

Eine Kennzahl ist eine gespeicherte Berechnung, die im Datenmodell vorhanden ist. Sobald du sie im Power Pivot-Fenster erstellt hast, steht sie in jeder Pivot-Tabelle zur Verfügung, die das Modell verwendet. In der Syntax wird := anstelle von = verwendet:

[Total Sales] := SUM(Sales[Amount])
[Order Count] := COUNTROWS(Sales)
[Average Order] := AVERAGE(Sales[Amount])

Wenn du eine Kennzahl erstellen willst, klick einfach auf eine leere Zelle im grauen Berechnungsbereich unterhalb der Tabelle im Power-Pivot-Fenster. Deine Formel gibst du dann in die Formelleiste darüber ein.

Die eine neue Idee: Filterkontext.

Der DAX ist anders als Excel-Formeln, weil er einen Filterkontext hat. Das ist ein Konzept, bei dem eine Kennzahl neu berechnet wird. Und zwar wird sie auf Basis der Filter berechnet, die gerade auf die Pivot-Tabelle angewendet werden.

Füge [Total Sales] in die Pivot-Tabelle ein und ziehe Region in die Zeilen. Die Kennzahl zeigt automatisch die Summe für jede Region an. Das liegt nicht daran, dass für jede Zeile eine andere Formel geschrieben wurde. Stattdessen wird für jede Region ein Filter in der Pivot-Tabelle angewendet, und die Kennzahl reagiert darauf. Wenn du einen Jahr-Slicer hinzufügst, reagiert die Kennzahl auch darauf. Egal, welche Filter du kombinierst, du bekommst immer das richtige Ergebnis, und zwar aus einer einzigen Kennzahl.

Deshalb solltest du die meisten deiner Berechnungen als Kennzahlen statt als fest codierte Formeln schreiben.

CALCULATE ist die wichtigste DAX-Funktion

CALCULATE führt eine Kennzahl innerhalb eines modifizierten Filterkontexts aus. Mit anderen Worten: Damit kannst du die Daten, auf die die Formel zugreift, vor der Berechnung anpassen.

[North Sales] := CALCULATE([Total Sales], Customers[Region] = "North")

Das zeigt immer die Gesamtsumme für „North“ an, unabhängig davon, welcher Regionsfilter in der Pivot-Tabelle angewendet wird, was besonders für Vergleichsspalten nützlich ist.

[% of Total] := DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))

ALL(Sales) entfernt alle Filter aus der Tabelle Sales und liefert die Gesamtsumme. DIVIDE berechnet den Prozentsatz für den aktuellen Filterkontext. Eine einzige Formel liefert automatisch den richtigen Prozentsatz für jede Zeile in der Pivot-Tabelle.

Erstellen der PivotTable aus dem Datenmodell

Sobald du Tabellen, Beziehungen und Kennzahlen zu deinem Modell hinzugefügt hast, ist das Erstellen des Berichts ganz einfach:

1. Gehe zu Einfügen → PivotTable
2. Wähle Aus Datenmodell (nicht Aus Tabelle/Bereich)
3. Im Bereich PivotTable-Felder siehst du nun alle deine Tabellen aufgelistet, statt nur einer flachen Liste von Spalten.

Ziehe Felder aus einer beliebigen verknüpften Tabelle in die PivotTable. Da die Beziehungen bereits definiert sind, weiß Excel, wie sie kombiniert werden müssen. Ziehe Region aus Kunden in die Zeilen und [TotalSales] aus Umsatz in die Werte. Die PivotTable zeigt sofort den TotalSales pro Region an, der über die Beziehung hinweg korrekt berechnet wurde.

Füge für jede Dimension, zum Beispiel Jahr, Produktkategorie oder Umsatzsegment, einen Slicer (Datenschnitt) hinzu, und alle Kennzahlen werden gleichzeitig aktualisiert, sobald du darauf klickst. Füge mehrere Pivot-Tabellen hinzu, die mit demselben Modell verbunden sind, und sie reagieren alle auf dieselben Slicer (Datenschnitt).

Praktische Tipps für Anfänger

Benenne deine Tabellen, bevor du sie lädst. Wähle deine Daten aus, drücke Strg+T, wechsle dann zum Tabellenentwurf und gib der Tabelle einen aussagekräftigen Namen, zum Beispiel Umsatz oder Kunden. Diese Namen tauchen in DAX-Formeln und im Feldbereich auf – klare Namen machen also alles besser lesbar.

Fang mit drei Kennzahlen an: Bevor du dich mit fortgeschrittenem DAX beschäftigst, mach dich erst einmal mit SUM, COUNTROWS und DIVIDE vertraut. Neunzig Prozent der Fragen im Berichtswesen lassen sich mit diesen drei Kennzahlen beantworten.

Verwende DIVIDE statt direkt zu dividieren, da =A/B einen Fehler verursacht, wenn B gleich Null ist. =DIVIDE(A,B) gibt eine leere Zelle zurück, was in einer Pivot-Tabelle viel übersichtlicher ist.

Halte Dimensionstabellen klein und übersichtlich. Deine Tabelle „Datum“ sollte eine Zeile für jedes Kalenderdatum enthalten und nützliche Spalten wie Monat, Quartal, Jahr und Wochentagsname umfassen. Du kannst diese mit der Funktion SEQUENZ in einer Tabellenkalkulation erstellen und dann in das Modell laden.

Vermische keine Fakten- und Dimensionsdaten in einer Tabelle: Wenn deine Verkaufstabelle in jeder Zeile den Namen und die Region des Kunden enthält, handelt es sich um redundante Daten. Löse diese in eine eigene Customers-Dimension aus; die Beziehung sorgt dann für die Verknüpfung.

Zusammenfassung

Power Pivot ist eine lohnende Investition. Sobald du das Konzept von Beziehungen und Kennzahlen verstanden hast, lassen sich Probleme, für die bisher komplexe SVERWEIS-Formeln, Hilfspalten oder aufwendige Berechnungen nötig waren, mit nur wenigen Zeilen DAX und einer aktualisierbaren Pivot-Tabelle lösen.