Heute gibt es den dritten und vorerst letzten Teil meiner kleinen Einführung in die Welt der Cube-Funktionen. Nachdem wir in Teil 1 und 2 bereits ausgiebig Gebrauch von der CUBEWERT-Funktion gemacht haben, steigen wir im heutigen Beitrag noch ein wenig tiefer ein und lernen zwei weitere Funktionen aus dieser Familie kennen.
Aber keine Angst, nichts wird so heiß gegessen, wie es gekocht wird.
Los geht’s!
Beispieldatei herunterladen
Wer die ersten beiden Teile verpasst hat, kann sie hier nochmal nachlesen:
Flexibler Zugriff auf das Datenmodell mit Cube-Funktionen
Einführung in die Cube-Funktionen, Teil 2
Die Aufgabenstellung
Wir wollen eine kleine Rangliste der umsatzstärksten Artikel und Verkäufer in unserer Beispieldatei erstellen. Dazu benötigen wir neben der bereits bekannten CUBEWERT-Funktion folgende Funktionen:
- CUBEMENGE
- CUBERANGELEMENT
Bevor so eine Rangliste erstellt werden kann, muss zunächst die Menge aller dazu benötigten Elemente bereitgestellt werden. Für die Artikelliste also die Menge alle überhaupt vorhandenen Artikel. Genau dafür wird die Funktion CUBEMENGE benötigt:
=CUBEMENGE(Verbindung;Menge_Ausdruck;[Beschriftung];[Sortierreihenfolge];[Sortieren_nach])
Den Verbindungsnamen kennst du bereits aus den ersten beiden Teilen dieser Einführung: "ThisWorkbookDataModel"
Danach wird noch das Argument Menge_Ausdruck benötigt, was nichts anderes ist als die genaue Angabe von Tabelle und Feld, welche die gewünschten Daten enthält. Da wir die Bezeichnungen der Artikel wollen, wäre das die Tabelle tbl_Artikel und das Feld Bezeichnung.
Die Tabelle tblArtikel im Datenmodell
In der Formel würde das so aussehen:
=CUBEMENGE("ThisWorkbookDataModel";"[tbl_Artikel].[Bezeichnung]")
Da diese Menge sämtliche einzelnen Bezeichnungen enthalten soll, reicht das noch nicht, das Argument muss noch erweitert werden. Wie du mittlerweile weißt, bietet die Intellisense-Hilfe nach der Eingabe eines Punkts weitere Optionen an. Wie man im folgenden Bild sieht, wäre das nur das Element "All":
CUBEMENGE: Der Ausdruck All reicht leider nicht
Wir übernehmen also diese Option und auch wenn das auf den ersten Blick schon die Lösung zu sein scheint, reicht das leider immer noch nicht. Nach einem weiteren Punkt werden nun sämtliche Artikelbezeichnungen angeboten:
CUBEMENGE: Benötigt werden sämtliche Artikelbezeichnungen
Aber wir können und wollen uns ja nicht auf eine bestimmte Bezeichnung festlegen, denn wir wissen ja nicht, welches Produkt das umsatzstärkste ist. Daher müssen wir jetzt auf eine kleine Besonderheit zurückgreifen. Nach dem "[All]." tippen wir jetzt das Wort "children" ein und schließen dieses Argument noch mit dem doppelte Anführungszeichen ab. Leider wird diese Option children nicht in der Auswahlliste der Bezeichnungen angeboten, das muss man sich einfach merken:
CUBELEMENT: Ihr Kinderlein kommet…
Theoretisch könnten wir die Funktion jetzt schon mit der Klammer komplett abschließen, denn alle weiteren Argumente sind optional. Wenn wir das machen, kommt schon die nächste Enttäuschung. Man sieht nämlich… nichts:
Die Zelle mit der Cubemenge scheint leer zu sein
Aber keine Sorge, hinter der Zelle B3 erfolgt wirklich der Zugriff in das Datenmodell und es wird auch die Liste sämtlicher Artikelbezeichnungen bereitgestellt, auch wenn man im Augenblick noch nichts davon sieht. Da eine leere Formelzelle aber gefährlich ist, da man sie leicht übersehen und überschreiben kann, vergeben wir über das optionale dritte Argument noch eine frei wählbare Beschriftung. Ich nehme dafür "Artikel", damit klar ist, wofür die Zelle steht:
Das optionale Argument zeigt wenigstens einen Text an
Immerhin sieht man jetzt, dass die Zelle nicht leer ist. Auch wenn das Ergebnis immer noch enttäuschend ist. Aber nur Geduld, es wird gleich besser!
Die Rangliste wird erstellt
Ziel der Aufgabe ist es ja, eine Top-3-Liste der umsatzstärksten Artikel zu erzeugen. Daher tippe ich jetzt einfach mal die Werte 1 bis 3 in Spalte A und gebe auch noch gleich die Überschrift "Umsatz" in Zelle C3 ein:
Die noch leere Rangliste
Und jetzt kommt die nächste Cube-Funktion zum Einsatz, CUBERANGELEMENT. Diese macht endlich die Artikelbezeichnungen sichtbar:
=CUBERANGELEMENT(Verbindung;Menge_Ausdruck;Rang;[Beschriftung])
Die Verbindung ist wieder klar. Für das zweite Argument muss eine Menge angegeben werden. Und diese Menge haben wir vorhin mit der CUBEMENGE-Funktion in Zelle B3 ermittelt, daher können wir einfach auf diese Zelle verweisen. Da wir anschließend die Formel nach unten kopieren möchten, der Bezug zu B3 aber statisch bleiben soll, wird dieser Bezug mit den Dollarzeichen noch absolut gesetzt.
Als drittes Argument wird noch eine Rangnummer benötigt. Dazu verweisen wir auf die erste Rangnummer, die wir zuvor in Spalte A eingetippt hatten. Die fertige Formel sieht danach so aus:
=CUBERANGELEMENT("ThisWorkbookDataModel";$B$3;A4)
Das erste Rangelement wird angezeigt
Endlich, die erste Produktbezeichnung wird angezeigt! Jetzt noch in der Zelle daneben den Umsatz mit Hilfe der CUBEWERT-Funktion berechnen. Die ersten beiden Argumente sind wieder klar, es wird auf das Measure Umsatz zugegriffen. Der muss jetzt nur noch eingeschränkt werden auf den Artikel daneben, auf dessen Zelle wir einfach wieder verweisen können:
=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Umsatz]";B4)
Der Umsatz für das erste Rangelement
Kopieren wir nun die beiden Formeln in die nächsten beiden Zeilen, werden auch für Rang 2 und 3 die Artikel und Umsätze angezeigt. Allerdings ist irgendwo ziemlich der Wurm drin:
Warum hat das Produkt auf Rang 2 einen höheren Umsatz als das auf Rang 1?
Was stimmt da nicht?
Ernüchterung: Die Rangliste stimmt nicht!
Die Antwort liegt in der Menge
Wir erinnern uns kurz: Im ersten Schritt haben wir in Zelle B3 die Menge sämtlicher Artikelbezeichnungen ermittelt und dabei auf die Datenmodell-Tabelle tblArtikel zugegriffen. Aber woher soll diese Menge wissen, dass an erster Stelle nicht irgendein Artikel, sondern der umsatzstärkste Artikel gewünscht wird? Die Funktion CUBERANGELEMENT greift ja nur auf das erste Element in dieser Menge zu, was immer auch dieses Element im Augenblick ist.
Die Cubemenge muss also nach Umsatz sortiert werden. Und genau dafür kommen jetzt die beiden optionalen Argumente zum Einsatz. Passen wir die Formel in B3 also nochmal an.
Das 4 Argument legt eine Sortierreihenfolge fest:
Die CUBEMENGE muss erst sortiert werden
Wir wollen den umsatzstärksten Artikel am Anfang der Menge haben, daher soll sie absteigend sortiert werden, Parameter 2 also. Und das letzte Argument gibt an, wonach denn eigentlich sortiert werden soll. In unserem Fall wäre das das Umsatz-Measure. Danach sieht die Formel wie folgt aus und auch die Top-3-Liste hat sich komplett geändert:
=CUBEMENGE("ThisWorkbookDataModel";"[tbl_Artikel].[Bezeichnung].[All].children";"Artikel";2;"[Measures].[Umsatz]")
Die angepasste CUBEMENGE-Funktion
Puh, ich gebe zu, das war keine ganz leichte Kost. Und natürlich hätte man das gleiche Ergebnis mit einer einfachen Pivot-Tabelle aus dem Datenmodell mit einem entsprechenden Wertefilter ebenfalls erreichen können. Aber mein Ziel war es, dir mit diesem Beitrag eine weitere, viel flexiblere Möglichkeit näherzubringen. Wären beispielsweise die Ränge 4 bis 6 von Interesse, ließe sich das durch eine einfache Änderung der Rangnummern in Spalte A sofort ermitteln. Das wiederum wäre mit einer Pivot-Tabelle nicht so elegant zu lösen.
Wie dem auch sei:
Mit diesem letzten Teil geht mein kleiner Ausflug in die Welt der Cube-Funktionen zu Ende. Vielleicht bist du ja auf den Geschmack gekommen und hast schon ein paar Anwendungsfälle für dich entdeckt. 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.