Vor geraumer Zeit habe ich in einer kleinen Artikelserie das Datenmodell in Excel vorgestellt. Damit lassen sich auch extrem große Datenmengen mit Hilfe von Pivot-Tabellen – auch Power-Pivot-Tabellen genannt – in Excel auswerten.
Aber es gibt noch eine weitere Möglichkeit, aus Excel heraus auf das Datenmodell zuzugreifen, nämlich über die sogenannten Cube-Funktionen. Ich lehne mich vermutlich nicht allzu weit aus dem Fenster, wenn ich behaupte, dass die große Mehrheit der Excel-Anwender wahrscheinlich noch nie etwas von diesen speziellen Funktionen gehört hat.
Und genau das werde ich heute ändern!
Beispieldatei herunterladen
Sollte das Datenmodell für dich noch komplettes Neuland sein, empfehle ich dir zuerst die dreiteilige Artikelserie als Einstieg in das Thema:
- Einführung in das Datenmodell und Power Pivot
- Power Pivot. Oder: Gute Beziehungen sind alles
- Einführung in Power Pivot (Teil 3)
In Teil 3 der dieser Einführung hatte ich schon kurz gezeigt, wie man eine Pivot-Tabelle, die aus dem Datenmodell heraus erstellt wurde (= Power Pivot) automatisch in CUBE-Funktionen umwandeln kann, ohne allerdings auf Details einzugehen. Heute sehen wir uns diese besonderen Funktionen etwas näher an.
Die CUBE-Funktionen im Überblick
Es stehen insgesamt 7 verschiedene Cube-Funktionen in Excel zur Verfügung. Die wichtigsten davon sind diese:
- CUBEELEMENT
- CUBEMENGE
- CUBEMENGENANZAHL
- CUBERANGELEMENT
- CUBEWERT
Darüber hinaus gibt es noch zwei weitere, die allerdings nicht mit dem Excel-Datenmodell funktionieren, sondern nur in Zusammenarbeit mit Microsoft SQL Server Analysis Services:
- CUBEELEMENTEIGENSCHAFT
- CUBEKPIELEMENT
Eine Referenz zu allen Cube-Funktionen ist auf dieser Microsoft-Hilfeseite zu finden.
Die wichtigste Funktion zuerst: CUBEWERT
Innerhalb von Pivot-Tabellen bekommt man in aller Regel immer eine ganze Reihe von Werten angezeigt, wie beispielsweise sämtliche Umsätze für alle Kombinationen aus Produkten und Vertriebsregionen. Je nachdem, welche Felder man im Zeilen- und Spaltenbereich der Pivot-Tabelle verwendet, kann das eine mehr oder weniger umfangreiche Matrix sein, die dann ausgegeben wird.
Mit der CUBEWERT-Funktion hingegen wird es möglich, gezielt nur den Wert für eine einzelne und ganz bestimmte Kombination abzugreifen. Zum Beispiel nur den Umsatz für das Produkt A in der Vertriebsregion Nord. Das ist vor allem dann hilfreich, wenn der zur Verfügung stehende Platz im Arbeitsblatt begrenzt ist – so wie es beispielsweise häufig in einem Dashboard der Fall ist.
Schauen wir uns in meiner Beispieldatei einmal an, wie so etwas funktioniert. Es gibt insgesamt 4 Datentabellen, die allesamt als formatierte („intelligente“) Tabellen angelegt sind:
- Rechnungsdaten (tbl_Rechnungen)
- Vertriebsmitarbeiter (tbl_Vertrieb)
- Artikelinformationen (tbl_Artikel)
- Kundeninformationen (tbl_Kunden)
Die verwendeten Datentabellen
Diese Tabellen habe ich über das Menü „Power Pivot | Zu Datenmodell hinzufügen“ bereits nacheinander ins Datenmodell geladen (falls der Menüpunkt Power Pivot bei dir noch nicht existiert, dann lies hier kurz nach):
Tabellen dem Datenmodell hinzufügen
Die Beziehungen zwischen den Tabellen habe ich ebenfalls bereits erstellt:
Die Beziehungen im Datenmodell
Außerdem habe ich im Datenmodell bereits ein sogenanntes Measure mit dem Namen „Umsatz“ erstellt. Measures sind berechnete Werte, die mit Hilfe einer speziellen Funktionssprache namens DAX komplexe Berechnungen innerhalb des Datenmodells ermöglichen. Diesen Measures gibt man einen frei wählbaren Namen, wie zum Beispiel „Umsatz“.
Das Measure namens Umsatz
In unserem Fall handelt es sich dabei um eine sehr einfache Berechnung, bei der zeilenweise die Menge mit dem Einzelpreis multipliziert und am Ende das Ergebnis einfach aufsummiert wird. Auf diesen berechneten Wert – auf dieses Measure „Umsatz“ – wollen wir nun aus Excel heraus mit der CUBEWERT-Funktion zugreifen.
Grundsätzlich gibt es zwei Möglichkeiten, wie man eine solche CUBEWERT-Funktion erzeugen kann:
Methode 1: Automatisch auf Basis einer (Power-)Pivot-Tabelle.
Methode 2: Manuelle Eingabe
Methode 1: Automatisch erzeugte Cube-Funktionen
Wer sich am Anfang die etwas sperrige Syntax der Cube-Funktionen ersparen möchte, erstellt einfach eine Pivot-Tabelle aus dem Datenmodell heraus:
Einfügen | PivotTable | Aus dem Datenmodell
Eine Pivot-Tabelle aus dem Datenmodell erstellen
Dann ziehe ich mir alle benötigten Felder in die Pivot-Tabelle, die ich für meine Auswertung benötige. Das könnte dann beispielsweise so aussehen:
Die fertige Power-Pivot-Tabelle
Der letzte Schritt wandelt diese Pivot-Tabelle in Cube-Funktionen um. Dazu ruft man das folgende Menü auf: PivotTable-Analyse | OLAP-Tools | In Formeln konvertieren
Pivot-Tabelle in Formeln konvertieren
Wichtig:
Dieser Menüpunkt steht nur in Pivot-Tabellen zur Verfügung, die aus dem Datenmodell heraus erstellt wurden. „Normale“ Pivot-Tabellen bieten diese Funktionalität nicht.
Danach sind die Informationen der Pivot-Tabelle zwar immer noch vorhanden, aber es ist eben keine Pivot-Tabelle mehr. Stattdessen wurde jede einzelne Zelle in eine Cube-Funktion umgewandelt, wie man im folgenden Bild sieht:
Alle Elemente wurden in Cube-Funktionen umgewandelt
Nun könnte ich also einzelne Zellen herausschneiden und an einer anderen Stelle wieder einfügen – muss aber darauf achten, dass auch die Zellenbezüge in der Formel weiterhin gültig bleiben! Denn die CUBEWERT-Funktion verweist in meinem Beispiel im Screenshot ja auf die Zellen A1, A3 und B2, die wiederum eigene Cube-Funktionen enthalten:
Achtung: Es gibt Bezüge zu anderen Zellen
Werden also diese Zellen gelöscht, hat man ein Problem. Daher empfehle ich stattdessen die Methode 2.
Methode 2: Manuell erstellte Cube-Funktionen
Ich gebe zu, dass man sich beim ersten Mal von der Syntax dieser Funktionen ein wenig überfordert fühlen könnte. Es lohnt sich aber, denn damit ist man viel flexibler und im Zweifelsfall auch unabhängig von anderen Zellen.
Wenn wir also beispielsweise den Umsatz für das Produkt Black Mambo in der Region Nord bestimmen wollen, sähe die CUBEWERT-Funktion folgendermaßen aus:
Die manuell erstellte CUBEWERT-Funktion
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].[All].[Black Mambo]";"[tbl_Vertrieb].[Region].[All].[Nord]")
Wer jetzt Schnappatmung bekommen hat, den kann ich aber beruhigen. Denn du wirst gleich merken, dass alles nur halb so wild ist, wie es aussieht.
Alle Cube-Funktionen haben eine ähnliche Syntax. Es beginnt immer (mit einer Ausnahme, aber dazu komme ich in einem anderen Artikel) mit dem Verbindungsnamen „ThisWorkbookDataModel“. Das ist ein feststehender Begriff, der in Anführungszeichen stehen muss und der einfach anzeigt, dass man auf das Datenmodell zugreifen möchte.
Danach kommen ein oder mehrere sogenannte Elementausdrucke. Mit ihrer Hilfe legt man fest, auf welches Element genau man im Datenmodell zugreifen möchte. Jeder dieser Ausdrucke beginnt und endet ebenfalls mit den Anführungszeichen. Und innerhalb dieser Anführungszeichen stehen dann mehr oder weniger viele Unterelemente, die von eckigen Klammern umschlossen und durch einen Punkt voneinander getrennt werden. Also zum Beispiel:
"[Measures].[Umsatz]"
Im obigen Fall sagen wir der CUBEWERT-Funktion also sinngemäß:
„Gib mir aus der Liste aller vorhandenen Measure das Measure mit dem Namen Umsatz“
Man könnte die CUBEWERT-Funktion jetzt schon beenden und erhielte damit den Gesamtumsatz aus dem Datenmodell. In unserem Fall sind wir aber nur am Umsatz für ein bestimmtes Produkt in einer bestimmten Region interessiert. Darum müssen wir noch weitere Elementausdrucke angeben.
"[tbl_Artikel].[Bezeichnung].[All].[Black Mambo]"
oder kürzer
"[tbl_Artikel].[Bezeichnung].[Black Mambo]"
Dieser Ausdruck besagt:
„Greife auf die Datenmodelltabelle tbl_Artikel zu. Greife in dieser Tabelle auf die Spalte „Bezeichnung“ zu und nimm aus dieser Spalte nur den Artikel Black Mambo“
Damit wäre der Umsatz schon mal auf dieses eine Produkt beschränkt. Kommen wir noch zum letzten Elementausdruck.
"[tbl_Vertrieb].[Region].[All].[Nord]"
oder wieder kürzer
"[tbl_Vertrieb].[Region].[Nord]"
„Greife auf die Tabelle tbl_Vertrieb zu und hole aus der Spalte Region nur die Region Nord“
Diese Elementausdrucke kann man sich also einfach wie Filter vorstellen, die auf das Umsatz-Measure einwirken.
„Aber die Syntax ist schon ziemlich schwierig, oder?“
Danke, dass du fragst 🙂
Nein, es ist alles sogar ziemlich einfach. Man muss sich eigentlich nur merken:
- Jedes Argument beginnt und endet mit einem Anführungszeichen.
- Die Unterelemente werden durch einen Punkt voneinander getrennt
- Mit den Pfeiltasten kann ich in der Auswahlliste nach unten und oben navigieren und die Tabulator-Taste übernimmt meine Auswahl
Alles andere macht für mich die sogenannte Intellisense-Hilfe, die mir Excel bei der Eingabe der Formel automatisch anbietet. Und so sieht das aus:
Automatische Unterstützung bei der Dateneingabe
Und somit haben wir direkt und ohne Umweg über eine Pivot-Tabelle in das Datenmodell hineingegriffen und den gewünschten Wert ermittelt. Du siehst also, alles halb so schlimm, oder?
Das soll es für den heutigen Ausflug in die Welt der Cube-Funktionen gewesen sein. Im nächsten Artikel schauen wir uns ein paar weitere dieser spannenden Funktionen an.
Hast du vielleicht auch schon Kontakt zu den Cube-Funktionen gehabt? Dann lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo zusammen,
`
in den Excel-Optionen gibt es unter „Daten“ die Option
„Excel-Datenmodell beim Erstellen von Pivot-Tables, Abfragetabellen und Datenverbindungen bevorzugen“
Mit dieser Option können auch Pivot-Tabellen auf Basis „normaler“ Tabellen (ohne PowerQuery)
über die OLAP-Tools in Formeln konvertiert werden.
Gruß
FM
Hallo Frank,
vielen Dank für den Hinweis auf diese Option. Sie bewirkt im Ergebnis allerdings, dass die Datentabelle beim Erstellen einer Pivot-Tabelle im Hintergrund automatisch in das Datenmodell geladen wird. Und damit handelt es sich dann letztendlich um keine normale Pivot-Tabelle mehr, sondern um eine Power Pivot-Tabelle, die auf dem Datenmodell basiert. Auch wenn das für den Anwender so erst mal nicht direkt ersichtlich ist.
Schöne Grüße,
Martin
Hallo Martin.
„die große Mehrheit der Excel-Anwender wahrscheinlich noch nie etwas von diesen speziellen Funktionen gehört hat.“
Trifft bei mir auf jeden Fall zu. Die Funktionen kannte ich zwar bereits, habe diese aber noch niemals – mangels Verständnis – verwendet.
Daher ist dein Artikel zum Thema eine echte Hilfe, damit ich das Thema auch mal verstehe. Danke!
Ich habe auch einmal ein bisschen rumprobiert in meinen Tabellen, da nicht umgehend verwendetes Wissen bei mir als gleich wieder verschwindet 😉
Dabei ist mir aufgefallen, dass der flexible Zugriff auf das Datenmodell tatsächlich echt potential hat für mich.
Leider habe ich es nicht geschafft, die Formel ohne Datenschnitte dynamisch zu gestalten.
Z.B. funktioniert einwandfrei:
CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Anzahl]“;“[DIM_KALENDER].[Datum].[All].[12.03.2024]“)
oder auch dynamisch mit einem Datenschnitt:
CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Anzahl]“;Zeitachse_Datum)
Ich habe es aber nicht geschafft den Teil mit dem Datum zu ersetzen, durch ein Datum, dass in einer Zelle steht, welches mittels Datepicker befüllt wird. Also z.B.:
CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Anzahl]“;$A$1)
Auch mit z.B. INDIREKT klappt dies nicht. Der Verweis auf eine Zelle funktioniert scheinbar immer nur dann, wenn in der Bezugszelle auch eine Cube-Funktion oder eine Datenschnittformel enthalten ist, also z.B. wenn in A1 steht =Zeitachse_Datum oder =CUBEELEMENT(„ThisWorkbookDataModel“;“[DIM_KALENDER].[Datum].[All].[12.03.2024]“)
Gibt es da noch einen Kniff, an den ich nicht gedacht habe oder hat das alles so seine Richtigkeit?
Danke+Gruß
Sebastian
Du kannst dir per Formel eine Zeichenkette in dem benötigten Format bauen und darauf verweisen. Etwa so:
=“[DIM_KALENDER].[Datum].[All].[„&TEXT(A1;“TT.MM.JJJJ“)&“]“
für Zelle A2. Dannfuntioniert der Zugrif auch über
=CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Anzahl]“;$A$2)
Danke für den Hinweis!
Tatsächlich hatte ich auch bereits eine Zeichenkette versucht, aber – beim erneuten darüber nachdenken – ziemlich umständlich:
=“CUBEELEMENT(„&ZEICHEN(34)&“ThisWorkbookDataModel“&ZEICHEN(34)&“;“&ZEICHEN(34)&“[DIM_KALENDER].[Datum].[All].[„&A1&“]“&ZEICHEN(34)&“)“
Benötigt wurde ja nur noch der letzte Ausdruck…
Danke
Hallo Sebastian,
Jochen hat ja schon eine Antwort auf deine Frage geliefert. Sofern es sich nicht um ein Datum handelt, sondern den Namen eines Measures, dann kann man diesen auch in eine Zelle schreiben und dann in der CUBEWERT-Formel darauf Bezug nehmen. Also statt
CUBEWERT("ThisWorkbookDataModel";"[Measures].[Anzahl]";….)
funktioniert auch
CUBEWERT("ThisWorkbookDataModel";$A$2;….)
sofern in A2 der Name des Measures, also Anzahl steht.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den spannenden Artikel. Ich habe tatsächlich zum ersten Mal mit den Cube-Funktionen Kontakt. Das ganze macht definitiv Sinn!
Ich möchte es für eine flexible Ergebnisermittlung nutzen.
Wie gestalte ich die Formel, wenn ich z.B. nicht den Umsatz für „Black Mambo“ ermitteln möchte, sondern die unterschiedlichen Produkte immer flexibel sind. In einer anderen Tabelle steht z.B. „Metro“ und dann soll der Umsatz für „Metro“ gezogen werden. Ich kann aber nicht alle Produkte in der Funktion auflisten, die Produkte setzen sich quasi ständig flexibel, dynamisch zusammen. Wie kann ich das dann machen? Final möchte ich den Umsatz über mehrere Jahre (die ich mit einem Datenschnitt/Zeitachse) eingrenze ermitteln. In deinem Black Mambo Beispiel wäre es dann das Gesamtergebnis der Zeile „Black Mambo“ am rechten Rand.
Gibt es hier noch einen Trick?
Vielen Dank vorab für die Hilfe,
Paula
Hallo Paula,
zunächst einmal freut mich das große Interesse an diesem Artikel, es wird auch noch eine Fortsetzung geben.
Die Formeln lassen sich natürlich flexibler gestalten. Angenommen, die Produktbezeichnung steht in der Zelle A8. Dann könnte man statt
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].[Black Mambo]";"[tbl_Vertrieb].[Region].[All].[Nord]")
folgendes schreiben:
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].["&A8&"]";"[tbl_Vertrieb].[Region].[All].[Nord]")
Man kann also letztendlich über Textverkettungen die einzelnen Elementausdrucke flexibel zusammensetzen.
Auch Datenschnitte lassen sich berücksichtigen, da werde ich in der nächsten Folge noch darauf eingehen.
Schöne Grüße,
Martin
Hi,
die Formeln sind zwar mächtig, aber man sollte unbedingt auch erwähnen, dass sie schon seit langer Zeit von Microsoft nicht mehr weiterentwickelt werden. Daneben sind diese Formeln recht rechenintensiv, v.a. wenn man CUBEVALUE mit CUBEMEMBER kombiniert. Wenn man dann viele dieser Formeln nutzt, wird die Neuberechnung eines Tabellenblatts sehr träge.
Imho lohnt es sich nicht sich in diese Funktionen einzulesen. Stattdessen sollte man eher versuchen DAX zu schreiben. Und wenn man dann unbedingt eine Formel will, lieber eine (Power-) Pivot Table mit GETPIVOTDATA() nutzen.
Aber vielleicht gehst du auf diese Aspekte in einem deiner folgenden Artikeln ein 🙂
Hallo KR,
es stimmt, wenn man sehr viele Cubefuktionen nutzt, kann man das in der Performance spüren. Daher sind sie sicherlich nicht für jeden Zweck geeignet. Wer sich intensiver mit dem Datenmodell beschäftigt, wird früher oder später um das Erlernen von DAX-Funktionen ohnehin nicht herumkommen, unabhängig davon, ob man Cubefunktionen einsetzt oder Power-Pivot-Tabellen.
Wie so oft in Excel gilt auch hier:
Viele Wege führen nach Rom. Und man sollte sich je nach konkretem Anwendungsfall den passenden aussuchen 😉
Schöne Grüße,
Martin