Die Excel Cube Funktionen sind vielleicht eines der am wenigsten bekannten, aber gleichzeitig mächtigsten Features im gesamten Microsoft BI-Stack. Obwohl sie seit Excel 2007 existieren, fristen sie oft ein Nischendasein und werden in ihrer Leistungsfähigkeit unterschätzt. Sie ermöglichen den Aufbau spezifischer Berichtstypen in Excel, die auf keine andere Weise realisierbar wären, und bieten eine direkte Verbindung zu Datenmodellen in Power Pivot, Power BI, Azure Analysis Services oder SQL Server Analysis Services. Trotz gelegentlicher Performance-Herausforderungen sind sie für die Erstellung komplexer Analysen und Berichte unerlässlich.
Kürzlich hat die Einführung neuer Lambda-Helferfunktionen in Excel die Möglichkeiten der Cube Funktionen Excel erheblich erweitert. Diese neuen Funktionen adressieren einige Einschränkungen, die seit der ersten Veröffentlichung der Cube-Funktionen bestanden, und eröffnen spannende neue Anwendungsfelder. In diesem Artikel werden wir die Synergie zwischen diesen Technologien beleuchten und zeigen, wie Sie damit Ihre Excel-Berichterstattung auf ein neues Niveau heben können.
Die Grundlagen: CUBESET und CUBERANKEDMEMBER verstehen
Beginnen wir mit einem einfachen Beispiel. In Excel wird die Funktion CUBESET verwendet, um eine (MDX-)Menge von Elementen zurückzugeben. Diese Menge wird jedoch in einer einzelnen Zelle gespeichert. Um jedes Element dieser Menge einzeln in eine Zelle Ihres Arbeitsblatts zu extrahieren, benötigen Sie die Funktion CUBERANKEDMEMBER.
Stellen Sie sich vor, Sie haben eine Tabelle namens “Sales” (Verkäufe) auf Ihrem Arbeitsblatt:
Beispieltabelle "Sales" in Excel mit Produktdaten für Cube-Funktionen
Diese Tabelle wird anschließend in das Excel-Datenmodell (auch bekannt als Power Pivot) geladen. Das Prinzip funktioniert übrigens identisch, wenn Sie ein Power BI-Dataset, Azure Analysis Services oder SQL Server Analysis Services als Datenquelle verwenden.
Sie können dann die Funktion CUBESET nutzen, um eine Menge aller Produkte zu erstellen, zum Beispiel so:
=CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set")Diese Formel speichert die gesamte Menge der Produkte in einer einzigen Zelle. Um nun jedes einzelne Element dieser Menge in eine eigene Zelle zu bringen, verwenden Sie CUBERANKEDMEMBER.
Anwendung von CUBESET und CUBERANKEDMEMBER zur Auflistung von Produkten
Das obige Beispiel zeigt ein einfaches Arbeitsblatt, zuerst mit den angezeigten Formeln und dann mit den Ergebnissen. Hier 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 in der Menge abzurufen.
Die Herausforderung mit CUBERANKEDMEMBER: Statische Listen
Dieses Beispiel verdeutlicht das grundlegende Problem, das schon immer bei CUBERANKEDMEMBER bestand: Um alle Elemente in einer Menge anzuzeigen, müssen Sie im Voraus wissen, wie viele Elemente es gibt, und entsprechend viele Zellen mit CUBERANKEDMEMBER-Formeln füllen.
Würde beispielsweise 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 gab zwar einige Workarounds, doch diese waren oft umständlich und setzten voraus, dass Sie die maximal mögliche Anzahl von Elementen in einer Menge kannten. Dies war schon immer einer der Hauptunterschiede zwischen Cube-Funktionen und PivotTables: Cube-Funktionen waren statisch, während PivotTables dynamisch wachsen und schrumpfen können, wenn sich die Daten ändern.
Wer nach flexiblen und leistungsstarken Wegen sucht, Excel und ähnliche Tools effektiv zu nutzen, wird oft auch an die Notwendigkeit von unterstützenden Software-Lösungen denken. Für viele Aufgaben gibt es dabei sogar software kostenlos, die den Arbeitsalltag erleichtern kann.
Die elegante Lösung: MAKEARRAY und LAMBDA in Aktion
Die neue Funktion MAKEARRAY 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 wie oben in Zelle B2 existiert, können Sie Folgendes tun:
=MAKEARRAY(CUBESETCOUNT($B$2), 1, LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",Sheet3!$B$2,r)))
Excel-Arbeitsblatt zeigt MAKEARRAY-Formel für dynamische Produktlisten mit Cube-Funktionen
Das ist das Ergebnis:
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 die dynamische Cube-Formel?
Lassen Sie uns die einzelnen Bestandteile dieser innovativen Formel aufschlüsseln, um ihr Funktionsprinzip bei den cube funktionen excel zu verstehen:
CUBESETCOUNT($B$2): Die FunktionCUBESETCOUNTwird verwendet, um die Anzahl der Elemente in derCUBESET-Menge, die in Zelle B2 definiert ist, abzurufen. Dies liefert die benötigte Zeilenanzahl für unser dynamisches Array.MAKEARRAY(CUBESETCOUNT($B$2), 1, ...): Die FunktionMAKEARRAYwird dann verwendet, um ein Array mit der vonCUBESETCOUNTzurückgegebenen Anzahl von Zeilen und einer Spalte zu erstellen.MAKEARRAYist dafür konzipiert, dynamisch ein Array basierend auf Zeilen- und Spaltenindizes zu befüllen.LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",Sheet3!$B$2,r)): Im dritten Parameter vonMAKEARRAYwird dieLAMBDA-Funktion eingesetzt.LAMBDAdefiniert eine wiederverwendbare Funktion, die hierCUBERANKEDMEMBERumschließt. Sie wird mit der aktuellen Zeilennummer (r) des zu erstellenden Arrays aufgerufen. Somit ruftCUBERANKEDMEMBERfür jede Zeile desMAKEARRAY-Outputs das entsprechende Element aus demCUBESETab, basierend auf seinem Rang.
Automatisches Anpassen bei Datenänderungen
Das Schöne daran ist, dass, wenn weitere Produkte zur “Sales”-Tabelle hinzugefügt werden, diese automatisch in der Ausgabe der MAKEARRAY-Formel in Zelle B4 erscheinen. Wenn beispielsweise zwei weitere Produkte zur “Sales”-Tabelle hinzugefügt werden:
Hier ist die neue Ausgabe der Formel, die automatisch die beiden neuen Produkte im Array anzeigt:
Dynamisch aktualisierte Produktliste in Excel durch MAKEARRAY nach Datenänderung
Dies transformiert die bisher statische Natur der CUBERANKEDMEMBER-Funktion in eine vollkommen dynamische Lösung, die sich automatisch an Datenänderungen anpasst. Dies ist ein entscheidender Schritt zur Automatisierung und Flexibilisierung Ihrer Berichterstellung mit cube funktionen excel.
Fazit: Die Zukunft dynamischer Excel-Berichte
Die Kombination aus den mächtigen cube funktionen excel und den neuen Lambda-Helferfunktionen wie MAKEARRAY und LAMBDA eröffnet völlig neue Dimensionen für die Erstellung dynamischer und interaktiver Berichte. Die Fähigkeit, statische Einschränkungen von Funktionen wie CUBERANKEDMEMBER zu überwinden und automatisch aktualisierte Listen und Tabellen zu generieren, ist ein Game-Changer für jeden, der regelmäßig mit komplexen Datenmodellen in Excel arbeitet.
Diese Innovation ermöglicht es Ihnen, Berichte zu erstellen, die nicht nur präzise, sondern auch zukunftssicher sind, da sie sich automatisch an veränderte Datengrundlagen anpassen. Dies spart nicht nur Zeit und reduziert manuelle Fehler, sondern steigert auch die Effizienz Ihrer Datenanalyse erheblich. Im nächsten Beitrag werden wir darauf aufbauen und zeigen, wie diese Technik genutzt werden kann, um einen einfachen, aber leistungsstarken dynamischen Bericht zu erstellen. Bleiben Sie dran, um das volle Potenzial dieser Funktionen für Ihre Excel-Workflows zu entdecken!