In diesem Artikel geht es weiter mit Teil 2 meiner kleinen Einführung in die Cube-Funktionen. Hier werde ich zeigen, wie man die CUBEWERT-Funktion flexibler gestalten kann, als dies mit der eher statischen Variante im ersten Teil der Fall war.
Außerdem erfährst du, wie man die Funktion mit Datenschnitten steuern und damit seine Auswertungen auf ein völlig neues Level heben kann.
Und so geht’s:
Beispieldatei herunterladen
Falls du den ersten Teil dieser Einführungsserie verpasst haben solltest, kannst du ihn hier nachlesen.
Mehr Flexibilität
Zur Erinnerung: Beim Erstellen der CUBEWERT-Funktion im letzten Artikel haben wir bei der Eingabe regen Gebrauch von den Anführungszeichen und dem Punkt gemacht. Denn damit wird die Intellisense-Hilfe von Excel aktiviert. So kann man sich relativ leicht durch die Funktion hangeln und bequem aus der Liste der jeweils angebotenen Optionen auswählen.
Das nimmt schon mal ziemlich viel Druck aus diesen doch recht komplexen Formeln. Der Nachteil dabei ist, dass die einzelnen Elementausdrücke damit in der Formel hart codiert werden. Beispiel:
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].[Black Mambo]";"[tbl_Vertrieb].[Region].[Nord]")
Möchte man nun die Umsätze für ein anderes Produkt berechnen, dann muss man wieder an der Formel herumbasteln. Aber selbstverständlich geht das auch besser. Wir können stattdessen, wie auch in allen „normalen“ Excel-Funktionen, Bezüge auf einzelne Zellen verwenden. Und in diesen Zellen dann das gewünschte Element eintragen.
Beispielsweise soll in Zelle B31 der Produktname gepflegt werden, auf den wir dann in unserer CUBEWERT-Funktion zugreifen. Der hart codierte Teil in der Formel wird durch den Zellbezug ersetzt, wobei man darauf achten muss, dass die grundsätzliche Syntax mit den eckigen Klammern erhalten bleiben muss. Das sieht dann so aus:
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].["&B31&"]";"[tbl_Vertrieb].[Region].[Nord]")
CUBEWERT mit Bezug auf eine Zelle
Da es sich bei allen Bestandteilen dieser Elementausdrucke um Texte handelt, die in Anführungszeichen gesetzt sind, kann Über den Textverkettungsoperator & der Zellbezug einfach eingebunden werden. Bei der anschließenden eckigen Klammer handelt es sich dann wieder um ein Textelement, das somit wieder in Anführungszeichen zu setzen ist.
Auf diese Weise lässt sich auch die Region flexible einbinden. Tipp: Erstelle für die beiden Felder eine Dropdown-Liste mit den zulässigen Einträgen. Damit verhinderst du, dass ein Tippfehler die Berechnung zunichtemacht.
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";"[tbl_Artikel].[Bezeichnung].["&B31&"]";"[tbl_Vertrieb].[Region].["&B32&"]")
CUBEWERT mit Bezug auf zwei Zellen
Schon deutlich besser, oder? Aber es geht noch VIEL besser!
Datenschnitte verwenden
Ein großer Vorteil von Cube-Funktionen ist, dass man sie auch direkt über Datenschnitte steuern kann. Falls du bisher noch nie mit Datenschnitte gearbeitet hast, solltest du das unbedingt ändern! Eine kleine Einführung findest du in diesem Artikel.
Zuerst fügen wir zwei Datenschnitte für die Produktbezeichnung und die Region ein, Menü „Einfügen | Datenschnitt“:
Datenschnitte einfügen
Wenn, wie in unserem Fall, im Hintergrund das Datenmodell zum Einsatz kommt, sieht der Eingangsdialog etwas anders aus, als bisher gewohnt. Anstatt der Liste mit den Feldern aus der Pivot-Tabelle (oder der intelligenten Tabelle) wird mir nur ein Liste an Verbindungen angeboten:
Fenster mit vorhandenen Bedingungen
Hier muss man nur auf das Register „Datenmodell“ wechseln. Hier sieht man auch, dass es in unserem Datenmodell 4 Tabellen gibt:
Datenmodell-Tabellen auswählen
Nach einem Klick auf die Öffnen-Schaltfläche erscheint jetzt auch die Liste aller Datenmodell-Tabellen mit den jeweiligen Feldern. In der Artikel-Tabelle markieren wir das Feld „Bezeichnung“ und in der Vertrieb-Tabelle das Feld „Region“ und übernehmen beides mit „OK“:
Gewünschte Datenfelder auswählen
Datenschnitte sind noch ohne Funktion
Nun haben wir also zwei Datenschnitte ins Arbeitsblatt eingefügt. Allerdings lassen sie im Moment noch keinerlei Funktion erkennen, ein paar Klicks auf die Schaltflächen ändert nichts an den gezeigten Umsätzen. Wie auch, die Datenschnitte verweisen ja nur auf unser Datenmodell, sind aber ansonsten noch mit keinen anderen Objekten verknüpft. Das werden wir aber gleich ändern.
Ich habe die CUBEWERT-Funktion aus der Zelle B33 nochmal nach B35 kopiert und die Elementausdrucke für Produkt und Region herausgelöscht. Somit wird mir der Umsatz über alle Produkte und alle Regionen berechnet:
Die verkürzte CUBEWERT-Funktion
Diese Formel erweitere ich nun wieder um zwei Elementausdrücke, die auf die beiden Datenschnitte verweisen. Dazu lösche ich die schließende Klammer und füge ein Semikolon ein, um den nächsten Ausdruck eingeben zu können. Anders als in der bisherigen Vorgehensweise beginne ich aber nicht mit einem Anführungszeichen, sondern tippe einfach „Daten“. Bereits während des Tippens bietet mir Excel die Namen aller vorhandenen Datenschnitte an, so dass ich dann mit der Tabulatortaste den ersten übernehmen kann:
CUBEWERT mit Bezug zu den beiden Datenschnitten
Nach einem weiteren Semikolon wiederhole ich das für den zweiten Datenschnitt: einfach „daten“ tippen (ohne Anführungszeichen), mit den Pfeiltasten den zweiten Datenschnitt markieren und mit der Tabulatortaste in die Formel übernehmen. Klammer zu, fertig.
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";Datenschnitt_Bezeichnung;Datenschnitt_Region)
Wenn jetzt ein anderes Produkt und/oder eine andere Region in den Datenschnitten ausgewählt werden, berechnet unsere CUBEWERT-Funktion sofort den entsprechenden Umsatz:
Der Name des Datenschnitts
Zur Erklärung:
In den Datenschnitteinstellungen kann man für jeden Datenschnitt nachschauen, unter welchem Namen der angesprochen werden kann. Dieser findet sich hinter dem unscheinbaren Text „In Formeln zu verwendender Name“. Mit diesem Namen lässt sich der Datenschnitt dann in den Cube-Funktionen ansprechen – und auch nur dort. Wer also jetzt auf die Idee kommt, den Datenschnitt in einer „normalen“ Excel-Formel zu verwenden, wird leider enttäuscht werden (wie es über einen Trick und einen kleinen Umweg im Einzelfall doch funktionieren könnte, kannst du in diesem Artikel nachlesen)
Wie man an dem Beispiel von CUBEWERT gesehen hat, bieten sich mit diesen Funktionen allerhand coole Möglichkeiten. Im Teil 3 dieser Einführungsserie werde ich noch ein paar andere Cube-Funktionen vorstellen.
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.