Nachdem wir die faszinierende Welt der Excel-Berichterstellung mithilfe von Office Scripts und den leistungsstarken Cube-Funktionen erkundet haben, tauchen wir heute tiefer in die Materie ein. Die Excel Cube Funktionen sind vielleicht die am wenigsten bekannten und genutzten Features im Microsoft BI-Stack, aber sie sind unglaublich mächtig, um spezifische Berichtstypen in Excel zu erstellen, die auf keine andere Weise realisiert werden könnten. Mit der Einführung der neue Funktionen in Excel wie LAMBDA-Hilfsfunktionen hat sich das Spiel jedoch grundlegend geändert. Diese Neuerungen ermöglichen es uns, langjährige Einschränkungen der Cube-Funktionen zu überwinden, die seit ihrer Einführung in Excel 2007 bestehen, und ganz neue Möglichkeiten zu erschließen. In diesem Artikel konzentrieren wir uns darauf, wie Sie dynamische Arrays mit den CUBE-Funktionen in Kombination mit LAMBDA und MAKEARRAY effektiv nutzen können.
Der Charme der CUBE-Funktionen in Excel
Die CUBE-Funktionen in Excel sind ein unverzichtbares Werkzeug für die Datenanalyse und Berichterstellung, insbesondere wenn Sie mit multidimensionalen Datenquellen wie dem Excel-Datenmodell (Power Pivot), Power BI-Datasets, Azure Analysis Services oder SQL Server Analysis Services arbeiten. Im Gegensatz zu den grundlegenden Excel-Funktionen bieten sie eine direkte Schnittstelle zu diesen komplexen Datenstrukturen und ermöglichen es, präzise Werte oder Mengen von Elementen abzurufen. Ihre Stärke liegt in der Fähigkeit, direkt auf die hierarchischen Strukturen und Metadaten des Datenmodells zuzugreifen, was sie ideal für maßgeschneiderte und flexible Berichte macht, die über die Standardfunktionen von PivotTables hinausgehen. Trotz ihrer Komplexität und der oft geringen Bekanntheit eröffnen sie erfahrenen Benutzern immense Möglichkeiten, maßgeschneiderte und dynamische Business Intelligence-Lösungen in Excel zu entwickeln.
CUBESET und CUBERANKEDMEMBER: Eine grundlegende Einführung
Beginnen wir mit etwas Einfachem: Die CUBESET-Funktion in Excel wird verwendet, um eine (MDX-)Menge von Elementen zurückzugeben. Diese Menge wird jedoch in einer einzigen Zelle gespeichert. Um jedes Element in eine separate Zelle Ihres Arbeitsblatts zu extrahieren, müssen Sie die CUBERANKEDMEMBER-Funktion verwenden.
Angenommen, wir haben eine Tabelle namens “Sales” auf unserem Arbeitsblatt:
Excel-Tabelle "Sales" mit Produkt-, Mengen- und Verkaufsdaten
Diese Tabelle wird dann in das Excel-Datenmodell (auch bekannt als Power Pivot) geladen. Das gleiche Prinzip gilt, wenn Sie ein Power BI-Dataset, Azure Analysis Services oder SQL Server Analysis Services als Quelle verwenden.
Nun können Sie die CUBESET-Funktion verwenden, um eine Menge aller Produkte wie folgt zu erstellen:
=CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set")Anschließend nutzen Sie die CUBERANKEDMEMBER-Funktion, um jedes einzelne Element der Menge in eine Zelle zu platzieren. Hier ist ein einfaches Beispielarbeitsblatt, zuerst mit den angezeigten Formeln und dann mit den Ergebnissen:
Excel-Arbeitsblatt zeigt CUBESET und CUBERANKEDMEMBER Formeln und deren Ergebnisse, die drei Produkte auflisten
Dieses Beispiel zeigt jedoch das grundlegende Problem, das schon immer mit CUBERANKEDMEMBER existierte: Um alle Elemente in einer Menge anzuzeigen, müssen Sie im Voraus wissen, wie viele Elemente es gibt, und ebenso viele Zellen mit CUBERANKEDMEMBER-Formeln befüllen, wie Elemente vorhanden sind. Im Beispiel sehen Sie, wie der Bereich B4:B6 die Zahlen 1, 2 und 3 enthält; diese Zahlen werden in den Formeln im Bereich C4:C6 verwendet, um das erste, zweite und dritte Element der Menge abzurufen.
Würde jedoch ein viertes Produkt zur Tabelle hinzugefügt, würde es nicht automatisch erscheinen – Sie müssten manuell eine weitere Zelle mit einer weiteren CUBERANKEDMEMBER-Formel hinzufügen. Es gibt zwar einige Workarounds, aber diese sind oft umständlich und erfordern, dass Sie die maximal mögliche Anzahl von Elementen in einer Menge kennen. Genau das war schon immer einer der Hauptunterschiede zwischen Cube-Funktionen und PivotTables: Cube-Funktionen sind statisch, während PivotTables dynamisch wachsen und schrumpfen können, wenn sich die Daten ändern. Dies kann auch die Kompatibilität mit Excel auf dem Mac betreffen, wo dynamische Funktionalitäten oft eine Herausforderung darstellen.
Die Revolution durch MAKEARRAY und LAMBDA
Die neue MAKEARRAY-Funktion in Excel bietet eine wirklich elegante Lösung für dieses Problem: Sie können jetzt eine einzelne Formel schreiben, die ein dynamisches Array mit allen Elementen der Menge zurückgibt. Angenommen, das gleiche CUBESET existiert in Zelle B2 wie oben gezeigt, können Sie Folgendes tun:
CUBESETCOUNT: Der erste Schritt zur Dynamik
Bevor wir MAKEARRAY einsetzen, benötigen wir die genaue Anzahl der Elemente in unserem CUBESET. Hier kommt die CUBESETCOUNT-Funktion ins Spiel. Sie liefert präzise die Anzahl der Elemente in einem zuvor definierten CUBESET, was für die dynamische Erstellung von Arrays unerlässlich ist. Ohne diese Funktion müsste die Größe des Arrays manuell bestimmt werden, was den Vorteil der Automatisierung zunichtemachen würde.
MAKEARRAY und LAMBDA in Aktion
=MAKEARRAY(CUBESETCOUNT($B$2), 1, LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",B2,r)))
Excel-Arbeitsblatt zeigt die MAKEARRAY-Formel in Zelle B4, die dynamisch alle Produkte aus dem CUBESET in Zelle B2 auflistet
Hier ist die Ausgabe:
Beachten Sie, wie die Formel in Zelle B4 ein Array zurückgibt, das alle drei Elemente der Menge in den Bereich B4:B6 enthält.
Wie funktioniert das?
- Die CUBESETCOUNT-Funktion wird verwendet, um die Anzahl der Elemente im CUBESET in B2 abzurufen.
- Die MAKEARRAY-Funktion wird dann verwendet, um ein Array mit der von CUBESETCOUNT zurückgegebenen Zeilenanzahl und einer Spalte zu erstellen.
- Im dritten Parameter von MAKEARRAY wird die LAMBDA-Funktion verwendet, um eine Funktion zurückzugeben, die CUBERANKEDMEMBER umschließt und dann mit der aktuellen Zeilennummer des Arrays aufgerufen wird.
Automatische Aktualisierung: Das Ende statischer Berichte
Das Schöne daran ist, dass, wenn weitere Produkte zur Sales-Tabelle hinzugefügt werden, diese automatisch in der Ausgabe der MAKEARRAY-Formel in B4 erscheinen. Wenn zum Beispiel zwei weitere Produkte zur Sales-Tabelle hinzugefügt werden, sieht die Tabelle so aus:
Hier ist die neue Ausgabe der Formel, die die zwei neuen Produkte automatisch im Array zurückgibt:
Excel-Arbeitsblatt mit der aktualisierten MAKEARRAY-Formel, die nun fünf Produkte dynamisch auflistet, inklusive der neu hinzugefügten
Diese Kombination aus MAKEARRAY und LAMBDA mit den CUBE-Funktionen stellt einen großen Schritt in Richtung dynamischer und wartungsarmer Berichterstellung in Excel dar. Es überwindet eine der größten Einschränkungen der traditionellen Cube-Funktionen und ermöglicht es Benutzern, Berichte zu erstellen, die sich automatisch an Datenänderungen anpassen. Dies bietet einen klaren Vorteil gegenüber ähnliche Programme, die möglicherweise nicht diese Flexibilität bieten. Die nahtlose Integration und automatische Skalierung macht die Arbeit mit Daten in einer Excel-Box deutlich effizienter.
Fazit
Die Kombination der Excel Cube Funktionen mit den neuen dynamischen Array-Funktionen wie MAKEARRAY und LAMBDA revolutioniert die Berichterstellung in Excel. Die Zeiten, in denen man die genaue Größe eines Sets im Voraus wissen musste oder Workarounds nutzen musste, um dynamische Listen zu erstellen, sind vorbei. Mit CUBESETCOUNT, MAKEARRAY und LAMBDA können Sie nun vollständig dynamische Berichte erstellen, die sich automatisch an Änderungen in Ihrem Datenmodell anpassen. Dies erhöht die Effizienz und Genauigkeit Ihrer Analysen erheblich.
Bleiben Sie dran für unseren nächsten Beitrag, in dem wir zeigen werden, wie diese Technik genutzt werden kann, um einen einfachen, aber leistungsstarken Bericht zu erstellen. Die Möglichkeiten, die sich hieraus ergeben, sind enorm und werden Ihre Art der Arbeit mit Excel grundlegend verändern.