Wer die Funktion „Gruppieren“ aus normalen Pivottabellen in Excel kennt, wird sie bei Power Pivot zunächst vermissen. Auch bei korrekter Formatierung als Datum lassen sich Werte nicht nach Monat oder Quartal gruppieren. Abhilfe schafft hier die Einführung von Beziehungen zwischen einem Datum und der dazugehörigen Gruppe.

Kurz zu den Ausgangsdaten. Das Beispiel enthält Verkäufe mit Summen und Datumsangaben. Denkbare Analysen wären nun die Preisentwicklung pro Monat oder die Einnahmen pro Quartal.

Die Beispieldatei kann hier heruntergeladen werden: Verkäufe Gruppieren

Schritt 1: Zuordnungstabelle erstellen

Das kleinste Gemeinsam, welches wir zuordnen wollen, ist der Monat. Dementsprechend müssen wir eine Tabelle erstellen, in welcher jeder Monat einem Monatsnamen, Quartal und Halbjahr zugeordnet wird. In meinem Fall sind das folgende Spalten:

  • Monat (Nummer)
  • MonatKurz
  • MonatLang
  • Quartal
  • Halbjahr

Schritt 2: Spalte Monat hinzufügen

Ich setze voraus, dass die beiden Tabellen spätestens jetzt über „PowerPivot > Zu Datenmodell hinzufügen“ ins Datenmodell aufgenommen wurden. Zunächst brauchen wir also für die Rohdaten eine neue Spalte namens „Monat“, welche sich aus folgender Formel errechnet:

=MONTH([Datum])

Wer es gerne ganz sauber mag, der macht nun einen Rechtsklick auf den Spaltennamen und wählt die Option „Aus Clienttools ausblenden“. Dieser Schritt bewirkt, dass die Spalte nachher nicht als verfügbare Spalte für die Pivottabelle angezeigt wird.

Schritt 3: Zuordnungstabelle vorbereiten

Die Zuordnungstabelle ist an sich schon fertig, aber eine Gemeinheit versteckt sich noch darin. Alphabetisch kommt Februar vor Januar, aber selbstverständlich wollen wir die Monate korrekt sortieren. Passend dazu gibt es die Funktion „Nach Spalte sortieren“, die für jede Spalte ausgewählt werden muss, die nicht automatisch richtig sortiert werden. In unserem Fall sind das MonatKurz und MonatLang.

Nach Spalte sortieren

Schritt 4: Verknüpfung herstellen

Das Erstellen der Verknüpfung lässt sich am einfachsten in der Diagrammsicht bewerkstelligen. Der Button zum Wechseln befindet sich ganz rechts unten im Power Pivot-Fenster. In dieser Ansicht werden die verfügbaren Tabellen mit allen Spalten, berechneten Werten und KPI angezeigt. Zur Verknüpfung genügt es, die Spalte „Verkäufe.Monat“ per Drag & Drop auf die Spalte „Zuordnung_Monate.Monat“ zu ziehen.

Verknüpfung erstellen

An dieser Stelle direkt ein Hinweis zu Verknüpfungen. Power Pivot kennt von sich aus nur 1:n-Verknüpfungen. Das heißt ein Wert in einer Spalte darf auf der einen Seite genau ein Mal vorkommen, in der anderen Spalte aber n Mal.

In unserem Beispiel existiert der Monat 1 in „Zuordnung_Monate.Monat“ genau ein Mal, in der Spalte „Verkäufe.Monat“ hingegen kann er beliebig oft vorkommen.

Nutzung in einer Pivottabelle

Zum Einfügen der Pivottabelle haben wir zwei verschiedene Möglichkeiten. Die direkteste ist über das Power Pivot-Fenster über „Home > PivotTable“. Ich persönlich möchte aber nicht jedes Mal dieses Fenster öffnen, wenn ich eine neue Tabelle in mein Datenblatt einfüge. Meiner Meinung nach wesentlich einfacher geht es direkt im Excel-Fenster über „Einfügen > PivotTable“. Dort je nach Wunsch die Position wählen und „Externe Datenquelle verwenden“ wählen. Als Verbindung wählen wir dann „Tabellen im Datenmodell der Arbeitsmappe“.

Vorhandene Verbindungen

Nun wird wie gewohnt eine Pivottabelle eingefügt, in der man Zeilen und Spalten je nach Wunsch einfügen kann. Als kleines Beispiel habe ich die Produkte in die Spalten und die Quartale und Monate in die Zeilen gepackt. Als Wert habe ich einfach die Summe der Gesamtpreise genommen.

PivotTable

Also alles im Kern ganz einfach und das Ergebnis besticht durch sauber aufteilbare Daten, ohne dass die Originaltabelle um die gesamte Palette an möglichen Datumswerten erweitert werden muss. Wie man sehen kann, taucht die Spalte „Verkäufe.Monat“ nicht auf, da ich sie vorher für Clienttools ausgeblendet habe. Für mich ist das vor allem eine Frage der Nutzbarkeit. Alle Spalten und Werte, die der Endnutzer der Tabelle nicht braucht, sollten zur Vermeidung von Unklarheiten einfach von vornherein ausgeblendet werden.

Ach ja, netter Nebeneffekt: Selbstverständlich wird die Tabelle mit den Zuordnungen von mir regelmäßig wiederverwendet und das spart in der Tat eine Menge Zeit.

Fragen? Anregungen? Einfach eine Nachricht hinterlassen und ich schaue, was ich machen kann.