Power Pivot: Datenmodelle in Excel professionell aufbauen

Excel-Tabellen mit hunderttausenden Zeilen, Daten aus mehreren Quellen, komplexe Berechnungen über verknüpfte Tabellen hinweg: Hier kommt Power Pivot ins Spiel. Das in Excel integrierte Add-In ermöglicht relationale Datenmodelle und die Formelsprache DAX (Data Analysis Expressions), die weit über SVERWEIS und Pivot-Tabellen hinausgeht.

1. Power Pivot aktivieren und verstehen

Power Pivot ist in Excel Professional Plus und Microsoft 365 enthalten. Aktivieren Sie es unter Datei → Optionen → Add-Ins → COM-Add-Ins → Microsoft Power Pivot for Excel. Nach der Aktivierung erscheint die Registerkarte Power Pivot im Menüband.

Der zentrale Unterschied zu normalen Pivot-Tabellen:

2. Ein Datenmodell aufbauen

Ein typisches Datenmodell im Geschäftsumfeld besteht aus Faktentabellen (Transaktionen, Bestellungen) und Dimensionstabellen (Kunden, Produkte, Zeit). So laden Sie Tabellen ins Datenmodell:

' VBA: Tabellen programmatisch ins Datenmodell laden
Sub TabellenInDatenmodellLaden()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lo As ListObject

    Set wb = ThisWorkbook

    ' Alle benannten Tabellen dem Datenmodell hinzufuegen
    Dim tabellenNamen As Variant
    tabellenNamen = Array("tblBestellungen", "tblKunden", _
        "tblProdukte", "tblKalender")

    Dim tblName As Variant
    For Each tblName In tabellenNamen
        On Error Resume Next
        Set lo = Nothing
        For Each ws In wb.Worksheets
            Set lo = ws.ListObjects(CStr(tblName))
            If Not lo Is Nothing Then Exit For
        Next ws
        On Error GoTo 0

        If Not lo Is Nothing Then
            ' Verbindung zum Datenmodell erstellen
            wb.Connections.Add2 _
                Name:="Datenmodell_" & tblName, _
                Description:="", _
                ConnectionString:="WORKSHEET;" & wb.FullName, _
                CommandText:=wb.Name & "!" & lo.Name, _
                lCmdtype:=6, _
                CreateModelConnection:=True, _
                ImportRelationships:=False
            Debug.Print "Geladen: " & tblName
        Else
            Debug.Print "Nicht gefunden: " & tblName
        End If
    Next tblName

    MsgBox "Datenmodell wurde aktualisiert."
End Sub

3. Beziehungen definieren

Im Power-Pivot-Fenster können Sie Beziehungen über die Diagrammansicht per Drag-and-Drop erstellen. Oder Sie verwenden VBA für die automatische Einrichtung:

Sub BeziehungenErstellen()
    Dim mdl As Model
    Set mdl = ThisWorkbook.Model

    ' Beziehung: Bestellungen -> Kunden
    mdl.ModelRelationships.Add _
        mdl.ModelTables("tblBestellungen").ModelTableColumns("KundenID"), _
        mdl.ModelTables("tblKunden").ModelTableColumns("KundenID")

    ' Beziehung: Bestellungen -> Produkte
    mdl.ModelRelationships.Add _
        mdl.ModelTables("tblBestellungen").ModelTableColumns("ProduktID"), _
        mdl.ModelTables("tblProdukte").ModelTableColumns("ProduktID")

    ' Beziehung: Bestellungen -> Kalender
    mdl.ModelRelationships.Add _
        mdl.ModelTables("tblBestellungen").ModelTableColumns("BestellDatum"), _
        mdl.ModelTables("tblKalender").ModelTableColumns("Datum")

    MsgBox "Beziehungen wurden erfolgreich erstellt."
End Sub

4. DAX-Measures: Die Kernkompetenz von Power Pivot

Measures sind dynamische Berechnungen, die im Kontext der Pivot-Tabelle ausgewertet werden. Hier die wichtigsten DAX-Patterns für den Geschäftsalltag:

Grundlegende Measures

// Gesamtumsatz
Gesamtumsatz:=SUMX(
    tblBestellungen,
    tblBestellungen[Menge] * tblBestellungen[Einzelpreis]
)

// Anzahl eindeutiger Kunden
Kundenanzahl:=DISTINCTCOUNT(tblBestellungen[KundenID])

// Durchschnittlicher Bestellwert
Durchschn_Bestellwert:=DIVIDE(
    [Gesamtumsatz],
    COUNTROWS(tblBestellungen),
    0
)

Zeitintelligenz-Measures

// Umsatz Vorjahr (Year-over-Year)
Umsatz_Vorjahr:=CALCULATE(
    [Gesamtumsatz],
    SAMEPERIODLASTYEAR(tblKalender[Datum])
)

// Veraenderung zum Vorjahr in Prozent
YoY_Prozent:=DIVIDE(
    [Gesamtumsatz] - [Umsatz_Vorjahr],
    [Umsatz_Vorjahr],
    BLANK()
)

// Laufende Summe im Jahr (Year-to-Date)
Umsatz_YTD:=TOTALYTD(
    [Gesamtumsatz],
    tblKalender[Datum]
)

// Gleitender 3-Monats-Durchschnitt
Umsatz_3M_Avg:=AVERAGEX(
    DATESINPERIOD(
        tblKalender[Datum],
        MAX(tblKalender[Datum]),
        -3,
        MONTH
    ),
    [Gesamtumsatz]
)

5. Berechnete Spalten vs. Measures

Ein häufiger Fehler ist die Verwechslung von berechneten Spalten und Measures:

• Berechnete Spalte: Wird zeilenweise berechnet und in der Tabelle gespeichert. Geeignet für Kategorisierungen, die als Filter oder Slicer dienen sollen.
• Measure: Wird dynamisch im Kontext der Pivot-Tabelle berechnet. Geeignet für alle Aggregationen und KPIs.

// Berechnete Spalte: Kundenklassifizierung
=IF(
    RELATED(tblKunden[Jahresumsatz]) > 100000,
    "A-Kunde",
    IF(
        RELATED(tblKunden[Jahresumsatz]) > 25000,
        "B-Kunde",
        "C-Kunde"
    )
)

Faustregel: Verwenden Sie Measures wo immer möglich. Berechnete Spalten nur dann, wenn Sie das Ergebnis als Filter, Slicer oder in Beziehungen benötigen.

6. Kalendertabelle: Das Fundament der Zeitanalyse

DAX-Zeitintelligenzfunktionen benötigen eine lückenlose Datumstabelle. Erstellen Sie diese direkt in DAX:

// Kalendertabelle als berechnete Tabelle im Datenmodell
tblKalender:=ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2026, 12, 31)),
    "Jahr", YEAR([Date]),
    "Monat", MONTH([Date]),
    "Monatsname", FORMAT([Date], "MMMM"),
    "Quartal", "Q" & FORMAT([Date], "Q"),
    "KW", WEEKNUM([Date], 21),
    "Wochentag", FORMAT([Date], "DDDD"),
    "JahrMonat", FORMAT([Date], "YYYY-MM")
)

Fazit

Power Pivot verwandelt Excel von einer Tabellenkalkulation in ein echtes Business-Intelligence-Werkzeug. Mit einem sauberen Datenmodell, durchdachten Beziehungen und präzisen DAX-Measures können Sie Analysen erstellen, die früher teure Spezialsoftware erforderten. Der Einstieg erfordert ein Umdenken (weg von zellbasierten Formeln, hin zu tabellenbasierten Berechnungen), aber die Investition in dieses Wissen zahlt sich bei jeder komplexen Auswertung aus.

---

Das könnte Sie auch interessieren:

• Power Query Fortgeschritten: M-Sprache und Custom Functions
• Excel Pivot-Tabellen: Der umfassende Überblick
• Excel vs. Power BI: Wann lohnt sich der Wechsel?

Unsere Services

• Professionelle Excel-Tabellen & VBA-Makros
• Word-Dokumente & PDF-Formulare erstellen lassen
• Alle Blog-Beiträge