Lieben Sie Pivot-Tabellen? Dann kennen Sie wahrscheinlich die Beschränkungen bei der Berichterstattung: Es gelingt oft nicht, dasjenige aussagekräftige Dashboard zu erstellen, das Sie sich gewünscht hätten. Soweit Ihre Pivot-Tabelle auf einem Datenmodell basiert, können in diesen Fällen Cube-Funktionen gute Dienste leisten. Mit Cube-Funktionen kann man – wie mit einer Pivot-Tabelle – Daten direkt aus dem Datenmodell abfragen, aber auch das Dashboard frei gestalten.
Das Wunsch-Dashboard
Stellen Sie sich bitte vor, Sie arbeiten für einen Großhandel für Obst und Gemüse und halten es für eine gute Idee, auf Basis einer Pivot-Tabelle folgendes Dashboard aufzubauen:
Nicht nur die Frage, ob die Tomate ein Gemüse ist oder Obst, macht uns hier zu schaffen. Auch sehen Sie, als erfahrener Nutzer oder erfahrene Nutzerin von Pivot-Tabellen, hier wahrscheinlich mehrere Haken. Innerhalb einer Pivot-Tabelle ist es zum Beispiel nicht möglich, per Rechtsklick Leerzeilen einzufügen, wie hier zwischen den Kategorien „Obst“ und „Gemüse“ gemacht wurde. Um die Zeilen nach Wunsch zu formatieren, müsste man eventuell ein neues PivotTable-Format erstellen. Für die Errechnung der Prozentsätze wären PIVOTDATENZUORDNEN-Formeln nötig. Diese Formeln wären bei der Weiterentwicklung des Dashboards fehleranfällig.
Eine Pivot-Tabelle in Cube-Funktionen konvertieren
Um eine freie und stabile Gestaltung des Dashboards zu ermöglichen, konvertieren wir die Pivot-Tabelle in Cube-Funktionen. In diesem Beispiel geht es um die folgende Pivot-Tabelle:
Das Datenmodell („ThisWorkbookDataModel“) enthält – als vereinfachtes Beispiel – nur eine Tabelle („Tabelle1“), die folgendermaßen aussieht:
Markieren Sie in der Pivot-Tabelle eine oder mehrere Zellen und wählen Sie unter „PivotTable-Tools“, unter „OLAP-Tools”, den Befehl „In Formeln konvertieren“ aus. Die „OLAP-Tools“ befinden sich, je nach Excel-Version, entweder im Register „Analysieren“, in der Gruppe „Berechnungen“, oder im Register „Optionen“, in der Gruppe „Tools“. Nach der Ausführung dieses Befehls sieht das Arbeitsblatt so aus:
Cube-Funktionen lesen und nutzen
In der Bearbeitungsleiste sehen Sie jetzt, bei Markierung der entsprechenden Zelle, dass die Zellen mit den Zeilen- und Spaltenbeschriftungen jeweils CUBELEMENT-Formeln enthalten. Die Formel für „Tomaten“ (in Zelle A6) ist beispielsweise:
=CUBEELEMENT(„ThisWorkbookDataModel“;{„[Tabelle1].[Kategorie].&[Gemüse]“.“[Tabelle1].[Produkt].&[Tomaten]“})
Die Formel für „Summe von Umsatz“ (in Zelle B3) lautet:
=CUBEELEMENT(„ThisWorkbookDataModel“;“[Measures].[Summe von Umsatz]“)
Diese Formeln sehen auf den ersten Blick kompliziert aus, sind aber eigentlich gut lesbar. In der ersten Formel werden die Namen des Datenmodells („ThisWorkbookDataModel“), der Tabelle („Tabelle1“), der Kategorie („Gemüse“) und des relevanten Produkts („Tomaten“) aufgelistet, somit wird ein Filter definiert. In der zweiten Formel wird die auszuführende Berechnung („Summe von Umsatz“), benannt. Das Wort „Measure“ können Sie sich dabei als Befehl für „berechnen“ vorstellen.
Die Werte der Pivot-Tabelle wurden in CUBEWERT-Formeln umgewandelt. Beim Tomaten-Umsatz (in Zelle B6) sieht die Formel zum Beispiel so aus:
=CUBEWERT(„ThisWorkbookDataModel“;$A6;B$3)
Die Zellverweise informieren Excel darüber, welches Filter anzuwenden, und welche Berechnung auszuführen ist. Die Dollarzeichen in den Zellverweisen ermöglichen es Ihnen, problemlos Leerzeilen oder Spalten einzufügen. Möchten Sie eine CUBEWERT-Formel aber in einer völlig anderen Zelle oder auf einem anderen Arbeitsblatt einsetzen, dann stimmen die Zellverweise möglicherweise nicht mehr. Sie müssen dann entweder die Zellverweise anpassen, oder die Formel neutralisieren. Letzteres geht wie folgt:
=CUBEWERT(„ThisWorkbookDataModel“;{„[Tabelle1].[Kategorie].&[Gemüse]“.“[Tabelle1].[Produkt].&[Tomaten]“};“[Measures].[Summe von Umsatz]“)
Die Syntax können Sie sich bei den CUBEELEMENT-Formeln abgucken.
Die neutrale Formel, d. h. ohne Zellverweise, lässt sich problemlos überall verwenden. Zum Berechnen der Prozentsätze (in den Zellen D11 und D13) geben wir einfach die Formeln =B5/B13 und =B5/B6 ein. Diese Zahlen formatieren wir als Prozentsätze. Anschließend lässt sich jede einzelne Zelle beliebig formatieren.
Datenschnitte und Berichtfilter
Sie fragen sich vielleicht, was mit dem Datenschnitt (nach Bundesländern) ist, der sich rechts der Pivot-Tabelle befand. Der Datenschnitt ist bei der Konversion der Pivot-Tabelle in Cube-Funktionen intakt und funktionsfähig geblieben! Klicken wir beispielsweise auf den Schalter „Bayern“, dann ändern sich alle Beträge im Dashboard dementsprechend:
Auf gleiche Weise lassen sich Berichtfilter in einem Dashboard mit Cube-Funktionen einsetzen.
Alles in allem können Cube-Funktionen sehr hilfreich sein beim Bauen von Dashboards in Excel. In diesem Beitrag haben Sie einen ersten Einblick in die Möglichkeiten erhalten. Weitere Tipps zum Arbeiten mit Excel erhalten Sie in unseren Excel-Seminaren zu denen wir Sie herzlich einladen.
Hinterlasse jetzt einen Kommentar