Excel Cube-Funktionen: Dynamische Arrays und LAMBDA – Problemlösung für Berichte

Die Excel Cube-Funktionen sind ein mächtiges, aber oft unterschätztes Werkzeug im Microsoft BI-Stack. Obwohl sie seit ihrer Einführung in Excel 2007 existieren, bleiben sie für viele Anwender ein Geheimtipp. Sie ermöglichen die Erstellung einzigartiger Berichtsarten in Excel, die auf andere Weise kaum umsetzbar sind. Ein zentrales Problem, das diese Funktionen lange Zeit begleitete, war die statische Natur ihrer Ergebnisse, insbesondere bei der Extraktion von Elementen aus einem CUBESET. Doch mit den neuen LAMBDA-Hilfsfunktionen hat sich dies grundlegend geändert. Dieser Artikel beleuchtet, wie diese Erweiterungen die Arbeitsweise mit den Cube-Funktionen in Excel revolutionieren.

Die Herausforderung mit CUBESET und CUBERANKEDMEMBER

Betrachten wir zunächst die traditionelle Verwendung von Cube-Funktionen. Die Funktion CUBESET wird verwendet, um eine MDX-Menge von Elementen zurückzugeben. Diese Menge wird jedoch in einer einzelnen Zelle gespeichert. Um jedes einzelne Element dieser Menge auf Ihrem Arbeitsblatt zu extrahieren, war bisher die Funktion CUBERANKEDMEMBER erforderlich.

Stellen Sie sich vor, Sie haben eine Tabelle namens “Sales” in Ihrem Arbeitsblatt, die in das Excel-Datenmodell (oder Power Pivot) geladen ist. Dies funktioniert identisch, ob Sie ein Power BI-Dataset, Azure Analysis Services oder SQL Server Analysis Services als Datenquelle verwenden.

Mit CUBESET können Sie eine Menge aller Produkte erstellen:

=CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set")

Anschließend nutzten Sie CUBERANKEDMEMBER, um jedes einzelne Element der Menge in eine Zelle zu platzieren.

Beispiel: CUBESET und CUBERANKEDMEMBER

Das grundlegende Problem hierbei: Um alle Elemente einer Menge anzuzeigen, mussten Sie deren Anzahl im Voraus kennen. Sie mussten so viele Zellen manuell mit CUBERANKEDMEMBER-Formeln befüllen, wie Elemente vorhanden waren. In obigem Beispiel verwenden die Formeln in C4:C6 die Zahlen 1, 2 und 3, um das erste, zweite und dritte Element der Menge abzurufen.

Wenn nun ein viertes Produkt zur Tabelle hinzugefügt wurde, erschien es nicht automatisch. Sie mussten eine weitere Zelle mit einer neuen CUBERANKEDMEMBER-Formel manuell hinzufügen. Dies war immer ein entscheidender Unterschied zu Pivot-Tabellen, die sich dynamisch an Datenänderungen anpassen können, während Cube-Funktionen in Excel statisch blieben.

Eine elegante Lösung: MAKEARRAY und LAMBDA

Die neue MAKEARRAY-Funktion in Excel bietet eine elegante Lösung für dieses Problem. Sie können nun eine einzige Formel schreiben, die ein dynamisches Array mit allen Elementen der Menge zurückgibt. Angenommen, das zuvor erstellte CUBESET befindet sich in Zelle B2, können Sie folgende Formel verwenden:

=MAKEARRAY(CUBESETCOUNT($B$2), 1, LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",Sheet3!$B$2,r)))

Ausgabe der MAKEARRAY-Funktion

Die Ausgabe dieser Formel ist ein dynamisches Array, das alle drei Elemente der Menge im Bereich B4:B6 enthält.

Funktionsweise im Detail

1. CUBESETCOUNT: Zuerst wird die CUBESETCOUNT-Funktion verwendet, um die Anzahl der Elemente in der CUBESET-Menge in Zelle B2 zu ermitteln. Dies liefert die benötigte Zeilenanzahl für das dynamische Array.
2. MAKEARRAY: Die MAKEARRAY-Funktion erstellt dann ein Array mit der von CUBESETCOUNT zurückgegebenen Zeilenanzahl und einer Spalte. Diese Funktion ist Teil der erweiterten Tabellenkalkulation Formeln in Excel, die eine flexiblere Datenverarbeitung ermöglichen.
3. LAMBDA: Im dritten Parameter von MAKEARRAY wird die LAMBDA-Funktion eingesetzt. Sie gibt eine Funktion zurück, die CUBERANKEDMEMBER umschließt und diese mit der aktuellen Zeilennummer des Arrays aufruft. Dies ist der Schlüssel zur Dynamik.

Das Besondere daran ist, dass, wenn weitere Produkte zur “Sales”-Tabelle hinzugefügt werden, diese automatisch in der Ausgabe der MAKEARRAY-Formel in Zelle B4 erscheinen.

Die Formel aktualisiert sich selbst und zeigt die neuen Produkte im Array automatisch an:

Neue Ausgabe der Formel mit zusätzlichen Produkten

Diese Kombination aus dynamischen Arrays und Cube-Funktionen in Excel bietet eine beispiellose Flexibilität bei der Berichtserstellung. Es transformiert statische Cube-Funktionen in dynamische Werkzeuge, die sich nahtlos an Änderungen in Ihren Datenquellen anpassen. Für Nutzer, die bereits Power BI Office 2013 oder neuere Versionen für ihre Datenanalyse nutzen, ist dies eine signifikante Verbesserung in der Art und Weise, wie Berichte in Excel erstellt und aktualisiert werden können.

Fazit: Eine neue Ära für Excel-Berichte

Die Integration von MAKEARRAY und LAMBDA mit den Cube-Funktionen in Excel stellt einen bedeutenden Fortschritt für die Berichtserstellung dar. Endlich können die einst statischen Cube-Funktionen dynamisch auf Datenänderungen reagieren und somit einen entscheidenden Nachteil gegenüber Pivot-Tabellen überwinden. Diese Neuerungen eröffnen spannende Möglichkeiten, komplexere und reaktionsfähigere Berichte direkt in Excel zu erstellen. Für jeden, der das volle Potenzial seiner Datenanalyse in Excel ausschöpfen möchte, ist das Verständnis dieser Funktionen unerlässlich. Bleiben Sie dran für weitere Einblicke in die praktischen Anwendungen dieser leistungsstarken Kombination.