PowerPivot-Daten in Excel mit Cube-Funktionen flexibel abfragen

Wenn Sie in Excel mit PowerPivot ein Datenmodell mit Measures bzw. berechneten Feldern aufgebaut haben, können Sie die Daten nicht nur in PivotTables und PivotCharts interaktiv analysieren, sondern Sie können auch die Cube-Funktionen nutzen, um gezielt einzelne Werte aus dem Datenmodell abzurufen und in einer beliebigen Zelle anzuzeigen.

Cube-Funktionen können Sie in Excel einsetzen, um Daten aus einem sog. Cube abzurufen. Ein Cube stellt einen Datenwürfel dar, in dem die zu analysierenden Daten als Elemente eines mehrdimensionalen Würfels angeordnet werden. Der Zugriff auf die Daten erfolgt über die Dimensionen des Cubes. Bei der Analyse von Verkaufsdaten können z .B. Kunde, Verkaufsperiode und Verkaufsregion Dimensionen darstellen. Die berechneten Werte für die einzelnen Elemente des Cubes werden Measures genannt, wie z. B. Umsatz und Mengen.

Wenn Sie in Excel 2010, Excel 2013 oder Excel 2016 eine Arbeitsmappe mit einem PowerPivot-Datenmodell anlegen oder öffnen, wird im Speicher ein Cube erzeugt, wenn Sie auf die Daten in diesem Datenmodell zugreifen. Um einzelne Elemente oder Werte aus diesem Cube in einer Zelle des Tabellenblatts darzustellen, setzen Sie Cube-Funktionen ein.

Zwei Cube-Funktionen sollen am folgenden Beispiel erläutert werden:

Das Datenmodell besteht aus drei 3 Tabellen mit Kunden- und Verkaufsdaten sowie einer Datumstabelle für die zeitliche Analyse. Die Diagrammsicht im PowerPivot-Fenster sieht folgendermaßen aus:

PowerPivot-Diagrammsicht

Sie können im Tabellenblatt Cube-Funktionen automatisch erzeugen, indem Sie für eine PivotTable, die auf einem Datenmodell basiert, die Zeilen- und Spaltenbeschriftungen sowie die berechneten Werte in Formeln konvertieren. In Excel 2016 sieht die PivotTable folgendermaßen aus:

Excel-PivotTable

Um die Cube-Funktionen zu erzeugen, gehen Sie bitte folgendermaßen vor:

  1. Markieren Sie eine beliebige Zelle in der PivotTable.
  2. Klicken Sie im Menüband in den PivotTable-Tools auf die Registerkarte Analysieren, dann in der Gruppe Berechnungen auf die Schaltfläche OLAP-Tools und abschließend auf die Schaltfläche In Formeln konvertieren.
    Ein Excel 2010 klicken Sie in den PivotTable-Tools auf die Registerkarte Optionen und dann in der Gruppe Tools auf die Schaltfläche OLAP-Tools. Abschließend klicken Sie auf die Schaltfläche In Formeln konvertieren.
  3. Die PivotTable wird danach aufgelöst. Im Tabellenblatt werden nur noch die Elemente und Werte aus der ehemaligen PivotTable angezeigt:

    Excel-PivotTable Formeln konvertiert

  4. Um sich die Formeln anzeigen zu lassen, klicken Sie im Menüband auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Formeln anzeigen. Das Tabellenblatt sieht dann folgendermaßen aus (Ausschnitt):

    Excel-PivotTable Cube-Formeln

Für die Darstellung der ehemaligen Zeilen- und Spaltenbeschriftungen wird die Funktion CUBEELEMENT verwendet. Die Werte werden mit der Funktion CUBEWERT abgerufen.

Mit der Funktion CUBELEMENT können Sie ein Element aus dem Cube abrufen. In Zelle A3 steht die z.B. die folgende Formel:

=CUBEELEMENT(„ThisWorkbookDataModel „;“[Kunde].[KdKurzname].&[Hoffmann]“)

Der erste Parameter Verbindung stellt die Verbindung zu dem PowerPivot-Datenmodell her. Er lautet in Excel 2016 und Excel 2013 immer ThisWorkbookDataModel,
in Excel 2010 lautet die Verbindung PowerPivot Data.
Der zweite Parameter bezeichnet den Elementausdruck. In diesem Fall wird in der Tabelle Kunde im Feld KdKurzname der Eintrag Hoffmann gesucht. Falls der Eintrag im Cube existiert, wird in der Zelle der Kurzname angezeigt. Sollte der Eintrag im Cube nicht vorhanden sein, erscheint in der Zelle der Ausdruck #NV.

Der Umsatz mit den Kunden Hoffmann im Jahr 2013 wird in Zelle B3 angezeigt und mit der folgenden Formel berechnet:

=CUBEWERT(„ThisWorkbookDataModel „;$A$1;$A3;B$2)

Der erste Parameter Verbindung mit identisch mit dem der Funktion CUBEELEMENT. Die anderen drei Parameter sind Elementausdrücke, die aus den Zellen A1, A3 und B2 stammen. Die Formel berechnet den Gesamtumsatz (siehe Elementausdruck in Zelle A1) mit dem Kunden Hoffmann (siehe Elementausdruck in Zelle A3) im Jahr 2013 (siehe Elementausdruck in Zelle B2).

Den Umsatz mit dem Kunden Hoffmann im Jahr 2013 können Sie auch folgendermaßen ermitteln, ohne vorher eine PivotTable erzeugen zu müssen, um danach deren Werte in Formeln zu konvertieren.

Geben Sie in einer beliebigen Zelle folgende Formel ein:

=CUBEWERT(„ThisWorkbookDataModel“; „[Measures].[Gesamtumsatz]“;“[Kunde].[KdKurzname].[All].[Hoffmann]“;“[Kalender].[Jahr].[All].[2013]“)

Die Intellisense-Funktion hilft Ihnen bei der Erfassung. Nach der Eingabe des Anführungszeichens bzw. der sich öffnenden eckigen Klammer werden Ihnen die möglichen Eingaben angezeigt.

Sie können die Formel flexibel gestalten, in dem Sie den gewünschten Kunden und das gewünschte Jahr in zwei Zellen eintragen und in der Formel an passender Stelle den Zellbezug eintragen:

Cubewert-Formel in Excel

Wenn der Kunde in Zelle A2 und das Jahr in Zelle B2 eingegeben werden, lautet die Formel folgendermaßen:

=CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Gesamtumsatz]“;“[Kunde].[KdKurzname].[All].[“ &A2 &“]“;“[Kalender].[Jahr].[All].[“ &B2&“]“)

Hinweis

Wenn Sie die Daten im PowerPivot-Datenmodell aktualisieren, werden die Zellwerte, die mit CUBE-Funktionen errechnet werden, ebenfalls aktualisiert.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

9 Kommentare

  1. Anonymous

    Vorbereitung auf ein Seminar

    Antworten
  2. Anonymous

    Tolles Beispiel! Wenn man die Beispielsdatei noch zum ausprobieren herunterladen könnte, wäre das genial.

    Antworten
  3. Anonymous

    Meiner Meinung nach ein sehr guter Beitrag. Danke hierfür! Um die Komplexität zu erhöhen: Wie wird die Syntax einer Cubeelement-Formel als Matrixformel mit geschwungenen Klammern gestaltet wenn ein Betandteil auf eine Zelle verweist? Beispiel: =CUBEELEMENT(„Cube_Monat Results“;{„[Positions].[Origination Date].[2017-08-08]“.“[Positions].[General Ledger].&[73]“}) In dieser Formel soll das Datum (2017-08-08) auf eine Zelle verweisen. Und zwar so, dass ein Attribut auf eine Zelle verweis? => (Insbesondere Das Datum!)

    Antworten
  4. Benjamin Bloch

    Super toll, hat mir wirklich weitergeholfen. Auf die „&“ wäre ich nie gekommen

    Antworten
    • Benjamin Bloch

      fast das wichtigste vergessen: DANKE !!!!

      Antworten
  5. Aaron

    Toller Beitrag! Ich habe ein ähnliches Problem wie „Anonymous“, hat hierfür jemand mittlerweile eine Lösung? Auch ich würde gerne in einer Matrixformel ein Zellbezug einbauen, um das Jahr variabel zu gestalten. Folgende Formel funktioniert dabei aber nicht. Wenn ich nur eine Dimension abrufe, würde es gehen, aber das bringt mir natürlich nichts.

    =CUBEELEMENT(„ThisWorkbookDataModel“;{„[Kunde].[Segment].[Region].&[100]“.“[Zeit].[Jahr nach Monaten].[Monat].&[“ &A1&“]&[3]“}

    Kann mir hier jemand helfen? DANKE!

    Antworten
    • Michael Richter

      Hallo Aaron,

      probieren Sie es mit der folgenden Formel:
      =CUBEELEMENT(„ThisWorkbookDataModel“;“([Kunde].[Segment].[Region].&[100],[Zeit].[Jahr nach Monaten].[Monat].&[“ &A1&“]&[3])“)

      Antworten
  6. Eli

    Vielen Dank für diesen Wertvollen Beitrag.

    Kann mir jemand sagen, wie ich den Berichtsfilter in die Formel integrieren kann?

    Beispiel: Im Berichtsfilter is das Land (DE, IT, CH, FR). Ich möchte mir die Kennzahl „Umsatz“ für die alle Länder mit Ausnahme von CH via Formel anzeigen lassen.

    Herzlichen Dank im Voraus!

    Antworten
    • Michael Richter

      Hallo Elisa,

      wenn der Berichtsfilter beispielsweise auf Zelle B1 liegt, können Sie in der CUBEWERT-Formel einen Bezug auf diese Zelle herstellen: =CUBEWERT(„ThisWorkbookDataModel“;“[Measures].[Gesamtumsatz]“;$B$1).
      Falls in dem Excel-Tabellenblatt der Berichtsfilter fehlt, können Sie ihn erstellen, indem Sie eine neue PivotTable anlegen und nur das Filterfeld füllen.

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Pin It on Pinterest

Share This