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.

Hat Ihnen dieser Beitrag weitergeholfen?

ja nein Andere Erwartung

Geben Sie uns Ihr Feedback. Möchten Sie eine Antwort von uns erhalten, tragen Sie zusammen mit Ihrem Feedback auch Ihre E-Mail-Adresse ein. Ihre E-Mail-Adresse wird ausschließlich für die Kommunikation mit Ihnen verwendet. Sie wird nicht veröffentlicht oder weitergegeben.

Benötigen Sie professionelle Unterstützung beim Einsatz von Word, Excel, Outlook, PowerPoint oder Access, so sind wir der richtige Ansprechpartner für Sie. Senden Sie uns eine E-Mail mit einer kurzen Problembeschreibung. Jemand aus unserem Team wird sich bei Ihnen melden, um weitere Details abzuklären und Ihnen ein Angebot für unsere Dienstleistung zu erstellen.