Nach meinem letzten Beitrag über die Verwendung von Office Skripten und Cube-Funktionen zur Generierung von Excel-Berichten aus Power BI-Daten, erhielt ich eine interessante Frage von Meagan Longoria auf Twitter:
Screenshot eines Tweets von Meagan Longoria zu Excel Cube Funktionen und Office Skripten.
Darauf kann ich nur antworten: Schuldig im Sinne der Anklage! Ich war schon immer ein großer Fan der excel cube funktionen, auch wenn sie vielleicht zu den am wenigsten geschätzten, bekanntesten und genutzten Features im gesamten Microsoft BI-Stack gehören. Sie haben ihre Schwächen (einschließlich manchmal der Performance), aber sie eignen sich hervorragend für den Bau bestimmter Berichtstypen in Excel, die auf keine andere Weise erstellt werden können. Für weitere nützliche Funktionen in der Tabellenkalkulation, besonders im Bereich Analyse, können Sie sich auch unsere Artikel zu excel statistik formeln ansehen.
Wie dem auch sei, die kürzliche Einführung neuer Lambda-Hilfsfunktionen in Excel hat mich besonders glücklich gemacht, da sie zusammen mit den Cube-Funktionen einige seit ihrer Einführung in Excel 2007 bestehende Einschränkungen überwinden und andere coole Dinge ermöglichen können. In dieser Artikelreihe werde ich einige der neuen Möglichkeiten erkunden.
Das Problem der statischen Cube-Sets in Excel
Beginnen wir mit etwas Einfachem. In Excel kann die CUBESET-Funktion verwendet werden, um ein (MDX-)Set von Elementen zurückzugeben. Dieses Set wird jedoch in einer einzelnen Zelle gespeichert. Um jedes Element in eine eigene Zelle auf Ihrem Arbeitsblatt zu extrahieren, müssen Sie die CUBERANKEDMEMBER-Funktion verwenden. Nehmen wir zum Beispiel an, ich habe eine Tabelle namens “Sales” (Verkäufe) auf meinem Arbeitsblatt:
Excel-Tabelle mit Verkaufsdaten (Produkt, Menge, Umsatz).
Diese Tabelle wird dann in das Excel Datenmodell (auch bekannt als Power Pivot – dies funktioniert jedoch genau gleich, wenn ich ein Power BI-Dataset, Azure Analysis Services oder SQL Server Analysis Services als Quelle verwende) geladen. Für Nutzer, die sich fragen, wie power bi office 2013 integriert werden kann, gibt es spezifische Ansätze.
Was Sie dann tun können, ist, die CUBESET-Funktion zu verwenden, um ein Set aller Produkte wie folgt zu erstellen:
=CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set")…und dann die CUBERANKEDMEMBER-Funktion zu verwenden, um jedes einzelne Element des Sets in eine Zelle zu schreiben. Hier ist ein einfaches Beispielarbeitsblatt, zuerst mit den Formeln und dann den Ergebnissen:
Excel-Arbeitsblatt mit CUBESET- und CUBERANKEDMEMBER-Formeln zur Anzeige von Produktdaten.
Dieses Beispiel zeigt jedoch das grundlegende Problem, das schon immer bei CUBERANKEDMEMBER bestand: Um alle Elemente in einem Set anzuzeigen, müssen Sie im Voraus wissen, wie viele Elemente es gibt, und so viele Zellen mit CUBERANKEDMEMBER-Formeln füllen, wie es Elemente gibt. In diesem Fall 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 im Set abzurufen.
Wenn jedoch ein viertes Produkt zur Tabelle hinzugefügt würde, würde es nicht automatisch erscheinen – Sie müssten manuell eine weitere Zelle mit einer weiteren CUBERANKEDMEMBER-Formel hinzufügen. Ich habe einige Workarounds gesehen, aber diese sind etwas “hacky” und erfordern, dass Sie die maximal mögliche Anzahl von Elementen in einem Set kennen. Dies war in der Tat 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.
Die Lösung: Dynamische Arrays und Lambda-Funktionen
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 im Set zurückgibt. Angenommen, dass das gleiche CUBESET wie oben in B2 existiert, können Sie Folgendes tun:
=MAKEARRAY(CUBESETCOUNT($B$2), 1, LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",Sheet3!$B$2,r)))
Ergebnis der MAKEARRAY-Formel in Excel, die eine dynamische Liste von Produkten liefert.
Hier ist die Ausgabe: Beachten Sie, wie die Formel in Zelle B4 ein Array zurückgibt, das alle drei Elemente im Set in den Bereich B4:B6 enthält. Wenn Sie mehr über die Grundlagen der cube funktion excel wissen möchten, finden Sie hier weiterführende Informationen.
So funktioniert diese dynamische Lösung
Wie funktioniert das im Detail?
- Die CUBESETCOUNT-Funktion wird verwendet, um die Anzahl der Elemente im CUBESET in B2 zu ermitteln.
- Die MAKEARRAY-Funktion wird dann verwendet, um ein Array mit der von CUBESETCOUNT zurückgegebenen Zeilenanzahl und einer Spalte zu erstellen. Dies ist ein Schlüssel zum Erstellen von tabellenkalkulation formeln für dynamische Berichte.
- Im dritten Parameter von MAKEARRAY wird die Lambda-Funktion verwendet, um eine Funktion zurückzugeben, die CUBERANKEDMEMBER umschließt, welche dann mit der aktuellen Zeilennummer des Arrays aufgerufen wird.
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, wie hier gezeigt:
Hier ist die neue Ausgabe der Formel, die die beiden neuen Produkte automatisch im Array anzeigt:
Automatisches Update der Produktdaten durch die MAKEARRAY-Formel nach Hinzufügen neuer Elemente.
Fazit
Diese Neuerung in Excel ist ein echter Durchbruch für Anwender von Cube-Funktionen. Durch die Kombination von excel cube funktionen mit Dynamic Arrays und Lambda-Funktionen können wir die seit langem bestehende Einschränkung der statischen Berichterstellung überwinden. Berichte, die zuvor manuelle Anpassungen erforderten, können nun vollständig dynamisch gestaltet werden und sich automatisch an Änderungen in den zugrunde liegenden Daten anpassen. Dies spart nicht nur Zeit, sondern reduziert auch Fehlerquellen und erhöht die Effizienz bei der Datenanalyse und Berichterstattung.
Obwohl dies allein noch nicht sehr nützlich ist, ebnet es den Weg für deutlich komplexere und dynamischere Berichte. Bleiben Sie dran für meinen nächsten Beitrag, in dem ich Ihnen zeigen werde, wie dies verwendet werden kann, um einen einfachen Bericht zu erstellen.