Eines der beliebtesten Werkzeuge in Microsoft Excel ist die Pivot-Tabelle. Nur wenige kennen dagegen ihre entfernten Verwandten, die Cube-Funktionen. Dabei sind sie oft die elegantere Alternative: Im Gegensatz zum starren Aufbau der Pivot-Tabelle haben Sie mit Cube-Funktionen die volle Flexibilität, wie Sie es von Excel-Funktionen gewohnt sind. Sie können gezielt Werte aus dem Datenmodell abfragen, diese nach Belieben anordnen und bei Bedarf Zwischenüberschriften oder Zusatzinformationen einfügen. In diesem Beitrag erfahren Sie, wie Sie mit Hilfe von Cube-Funktionen in wenigen Schritten ein interaktives Dashboard erstellen.

Da eine Pivot-Tabelle eine in sich geschlossene Einheit darstellt, bietet sie hinsichtlich des Layouts kaum Spielraum für Variationen. Sobald Sie die Felder für Zeilen, Spalten, Filter und die entsprechenden Berechnungen festgelegt haben, können Sie an der Tabellenstruktur keine Änderungen mehr vornehmen. Sie haben also viel weniger Freiheiten als bei normalen Excel-Zellen. Es gibt jedoch einen einfachen Trick, um sich diese Freiheiten zurückzuholen. Dieser erfordert allerdings einen "Umweg" über das Datenmodell in Excel.

Was ist das "Datenmodell" in Excel?

Das Konzept des Datenmodells gibt es seit Excel 2013. Es handelt sich dabei um eine multidimensionale relationale Datenbank, auch "Cube" genannt (engl.: "Würfel"). Tabellen aus der Arbeitsmappe oder aus externen Datenquellen (SQL-Server, Webseite, etc.) lassen sich in den Speicher dieser Datenbank laden und die Daten anschließend miteinander in Verbindung bringen.

Da ein Datenmodell oft sehr komplex ist und weitaus mehr Daten enthalten kann, als auf ein Excel-Arbeitsblatt passen, ist eine direkte Darstellung des Inhalts nicht vorgesehen. Um auf die Daten zugreifen und bestimmte Werte ermitteln zu können, benötigen Sie entweder eine Pivot-Tabelle oder Cube-Funktionen. Nur letztere bieten die Freiheit, gezielt einzelne Werte aus dem Datenmodell abzufragen und darzustellen.

Daten in das Datenmodell laden

Um Werte aus dem Datenmodell abzufragen, müssen die Daten zunächst einmal in das Datenmodell gelangen. Dazu gibt es mehrere Möglichkeiten:

• Beim Erstellen einer Pivot-Tabelle: Aktivieren Sie im Dialog PivotTable erstellen die Option Dem Datenmodell diese Daten hinzufügen.
• Bei Abfragen unter der Rubrik Daten abrufen und Transformieren bzw. mit dem Add-In "Power Query": Klicken Sie nicht auf die Schaltfläche "Laden", sondern wählen Sie stattdessen die Alternative "Laden in…". Daraufhin erscheint ein Dialogfenster mit der Option "Dem Datenmodell diese Daten hinzufügen" (Bild 1).

Bild 1: Befüllung des Datenmodells durch eine Abfrage

• Mit dem Add-In PowerPivot: Für die detaillierte Verwaltung und Bearbeitung des Datenmodells gibt es das Add-In PowerPivot (siehe " Mit Power Pivot die Grenzen herkömmlicher Pivot-Tabellen überwinden", Ausgabe 2/2019). Damit lesen Sie Daten aus verschiedenen Quellen in das Datenmodell ein, erstellen neue Spalten oder sog. Measures und verknüpfen mehrere Tabellen mit Hilfe von Beziehungen.

Die passenden Cube-Funktionen aus einer Pivot-Tabelle "holen"

Um aus dem Datenmodell einzelne Werte auszulesen, verwenden Sie Cube-Funktionen. Deren Syntax ist etwas komplizierter, als bei den üblichen Excel-Formeln, da sie auf sogenannten MDX-Ausdrücken basieren (Kurzform für "Multidimensional Expressions"). Sie müssen diese Datenbanksprache jedoch nicht unbedingt lernen, um Cube-Funktionen verwenden zu können. Mit einem einfachen Trick holen Sie sich die benötigten Formeln aus einer Pivot-Tabelle.

Bild 2: Beim Erstellen einer Pivot-Tabelle können Sie die Daten zusätzlich dem Datenmodell zufügen

1. Öffnen Sie die Beispieldatei "Projektstunden". Sie enthält eine Tabelle, in der die Arbeitszeiten mehrerer Mitarbeiter an verschiedenen Projekten während eines Quartals aufgeführt sind.
2. Erstellen Sie eine Pivot-Tabelle aus der Stundenliste, indem Sie eine Zelle in der Tabelle auf dem ersten Arbeitsblatt auswählen und im Register Einfügen auf PivotTable klicken.
3. Aktivieren Sie auf jeden Fall die Option "Dem Datenmodell diese Daten hinzufügen". Andernfalls ist die Verwendung der Cube-Formeln nicht möglich (Bild 2).
4. Ziehen Sie in der Feldliste das Feld "Mitarbeiter" in den Bereich "Zeilen" und das Feld "Dauer in Min" in den Bereich "Werte".
5. Fügen Sie einen Datenschnitt zur Auswahl von Projekten ein. Klicken Sie hierfür im Register PivotTableTools / Analysieren im Bereich Filtern auf Datenschnitt einfügen. Wählen Sie im Dialog Datenschnitt auswählen das Feld Projekt.
6. Markieren Sie wieder die Pivot-Tabelle und wechseln Sie auf das Register PivotTable-Tools / Analysieren.
7. Öffnen Sie unter Berechnungen das Menü OLAP-Tools und wählen Sie den Befehl In Formeln konvertieren (Bild 3).

Bild 3: Sofern die Pivot-Tabelle auf einem Datenmodell basiert, kann sie in Formeln konvertiert werden

Durch das Umwandeln in Formeln verliert die Pivot-Tabelle ihre Formatierung und es werden nur noch Text- und Zahlenwerte angezeigt. Wenn Sie eine Zelle der ehemaligen Pivot-Tabelle auswählen, sehen Sie, dass die Bearbeitungsleiste nun eine CUBE-Formel anzeigt (Bild 4).

Bild 4: Die Zahlenwerte werden über die Funktion CUBEWERT aus dem Datenmodell gelesen

Beispiel – Verwenden umgewandelter Pivot-Tabellen

Der Vorteil beim Umwandeln einer Pivot-Tabelle in Cube-Formeln ist, dass Sie die aufwändigen Formeln nicht selbst schreiben müssen, aber auch nicht an die Beschränkungen der Pivot-Tabelle gebunden sind. Sie können nun z.B. nach Lust und Laune Zeilen und Spalten verschieben oder neu einfügen. Bereiche, die Sie nicht mehr benötigen, können Sie einfach löschen – sofern sich keine anderen Formeln darauf beziehen. Auch bei der Formatierung stehen Ihnen alle Möglichkeiten offen.

Probieren Sie es aus:

1. Falls in der ersten Zeile die Überschrift "Zeilenbeschriftungen" vorhanden ist, löschen Sie sie einfach.
2. Geben Sie stattdessen als Überschrift für die erste Spalte "Personalnummer" ein.
3. Fügen Sie neben der Spalte für die Personalnummern eine neue Spalte ein und geben Sie ihr die Überschrift "Name". Fügen Sie in die Zelle unter der Überschrift diese Formel ein:
   =SVERWEIS(A4; tbl_Mitarbeiter; 2; 0)
   Falls die erste Personalnummer bei Ihnen nicht in der Zelle A4 steht, müssen Sie den Bezug in der Formel entsprechend anpassen. Kopieren Sie die Formel nun in alle Zellen in der Namens-Spalte.
4. In der ersten Zeile befindet sich eine Zelle mit dem Text "Summe von Dauer in Min". Diese Zelle hat eine Formel hinterlegt, auf die sich andere Zellen beziehen. Sie darf daher nicht gelöscht werden. Da sie optisch aber eher stört, verschieben Sie sie einfach an einen anderen Ort. Wählen Sie dazu die Zelle aus, klicken Sie den Rand an und ziehen sie nun mit gedrückter Maustaste aus dem sichtbaren Bereich, zum Beispiel in die Spalte M.
   Alternativ hätten Sie auch die CUBEELEMENT-Formel so anpassen können, dass ein anderer Text angezeigt wird. Hier sollte lediglich gezeigt werden, wie einfach es ist, Zellen zu verschieben, nachdem eine Pivot-Tabelle in Formeln umgewandelt wurde.
5. Geben Sie über der Zahlenspalte den Titel "Arbeitszeit in Min." ein.
6. Fügen Sie neben "Arbeitszeit in Min." eine neue Spaltenüberschrift "Arbeitszeit in Std." ein. Lassen Sie hierfür die Minutenwerte durch 60 teilen. Für die Zeile 4 würde die Formel folgendermaßen lauten:
   =C4/60
7. Formatieren Sie die Zellen in der Überschriften-Zeile fett.
8. Fügen Sie Rahmen ein, um die Liste optisch zu strukturieren (Bild 5).

Bild 5: Eine Excel-Liste aus Cube-Formeln

Das Ergebnis ist eine schlichte aber funktionale Excel-Liste, der man optisch nicht ansieht, dass sie einmal eine Pivot-Tabelle war. Sie kann nach Belieben weiter durch Einfügungen, beispielsweise mit weiteren Auswertungsspalten, ergänzt werden, ohne ihren Bezug zur Datenbasis zu verlieren. Der Datenschnitt, den Sie vor der Umwandlung eingefügt haben, funktioniert immer noch. Wenn Sie die Projektauswahl im Datenschnitt ändern, werden in der Zeitliste umgehend die Werte für die gewählten Projekte angezeigt.

Aktualisieren von Cube-Funktionen

Die meisten Excel-Funktionen werden automatisch aktualisiert, sobald sich die zugrundeliegenden Zellen ändern. Bei Cube-Funktionen ist das nicht der Fall, unter anderem weil die Aktualisierung je nach Datenquelle einige Zeit in Anspruch nimmt.

Um eine Aktualisierung zu starten, klicken Sie – wie bei Pivot-Tabellen – im Register Daten auf Alle aktualisieren. Noch schneller geht es mit der Tastenkombination Strg+Alt+F5.

Während die Formeln aktualisiert werden, zeigt Excel kurzzeitig den Text "#GETTING DATA" an (Bild 6). Je nach Datenumfang kann es etwas dauern, bis die Ergebnisse erscheinen, meist sind es aber nur wenige Sekunden. Sie können in dieser Zeit ganz normal weiterarbeiten, da der Aktualisierungs-Prozess Excel nicht blockiert.

Bild 6: Während die Werte aus dem Datenmodell abgerufen werden, erscheint in den Formel-Zellen der Text "#GETTING DATA"

Eine automatische Aktualisierung können Sie bei den Eigenschaften der Datenmodell-Verbindung einrichten. Öffnen Sie hierzu die Verbindungsliste über Daten / Abfragen und Verbindungen, klicken Sie mit der rechten Maustaste auf die Verbindung und wählen Sie Eigenschaften. Im Dialogfenster "Verbindungseigenschaften" können Sie wählen, ob die Verbindung in bestimmten Zeitintervallen oder beim Öffnen der Datei aktualisiert werden soll.

Die wichtigsten Cube-Funktionen

Die Funktion CUBEELEMENT

Im obigen Beispiel ist in der Zelle mit dem Text "Summe von Dauer in Min" diese Formel hinterlegt:

=CUBEELEMENT("ThisWorkbookDataModel";"[Measures].[Summe von Dauer in Min]")

Als erste Angabe erfolgt hier – wie bei fast allen Cube-Funktionen – ein Verweis auf das Datenmodell. Standardmäßig hat es den Namen "ThisWorkbookDataModel" und wird in Anführungszeichen geschrieben.

Das Datenmodell ist in Felder unterteilt. Hierzu zählen die Spalten der Quelltabellen, aber auch berechnete Felder wie Summen, Durchschnitte, Maximal- und Minimalwerte. Ein berechnetes Feld wird Measure genannt. Die obige Funktion liefert einen Bezug auf das Measure "Summe von Dauer in Min".

Auch für Zeilen- und Spaltentitel wird die Funktion CUBEELEMENT verwendet. In diesen Fällen erzeugt die Funktion einen Verweis auf die entsprechende Teilmenge des Datenmodells.

Die Funktion CUBEWERT

Eine weitere wichtige Cube-Funktion ist CUBEWERT. Sie liefert einen einzelnen Zahlenwert aus dem Datenmodell (Bild 4). Hierfür benötigt sie folgende Angaben:

• Den Namen des Datenmodells in Anführungszeichen, z.B. "ThisWorkbookDataModel".
• Ein oder mehrere Cube-Elemente. Diese können z.B. als verschachtelte Funktionen angegeben werden oder durch Bezug auf Zellen mit CUBEELEMENT-Formeln.
  Eines der angegebenen Cube-Elemente ist im Normalfall ein Measure, wie in unserem Beispiel "Summe von Dauer in Min". Wenn nur dieses Element angegeben wird, ist der zurückgegebene Wert die Gesamtsumme, also die Arbeitszeiten aller Mitarbeiter über alle Zeiträume und an allen Projekten. Durch die Angabe weiterer Cube-Elemente schränken Sie das Ergebnis weiter ein, z.B. auf einen bestimmten Mitarbeiter und ein bestimmtes Projekt.

Die folgende Formel ermittelt beispielsweise die Arbeitszeit des Mitarbeiters mit der Nummer "C2/22222" am Projekt "Netzwerk Ausbau":

=CUBEWERT("ThisWorkbookDataModel";
CUBEELEMENT("ThisWorkbookDataModel";"[Measures].[Summe von Dauer in Min]");
CUBEELEMENT("ThisWorkbookDataModel";"[tbl_Stundenliste].[Mitarbeiter].&[C2/22222]");
CUBEELEMENT("ThisWorkbookDataModel";"[tbl_Stundenliste].[Projekt].&[Netzwerk Ausbau]"))

Durch die langen Feldnamen kann eine CUBEWERT-Formel schon bei wenigen Angaben ziemlich lang werden.

Erstellen eines interaktiven Dashboards

Während die Pivot-Tabelle eine geschlossene Einheit bildet, können Cube-Formeln völlig unabhängig voneinander bestehen. Das können Sie sich zu Nutze machen, um ein Dashboard aufzubauen, das z.B. nur bestimmte Werte anzeigt. Das Erstellen der Pivot-Tabelle war nötig, um die Quelldaten in das Datenmodell zu befördern. Die Umwandlung in Cube-Formeln ist hilfreich, wenn Sie sich nicht sicher sind, wie Sie diese verwenden. Für das nachfolgende Beispiel benötigen Sie die bisher verwendete Tabelle jedoch nicht mehr. Stattdessen steuern Sie die Datenabfrage durch geschickten Einsatz der Formeln CUBEELEMENT und CUBEWERT.

In unserem Beispiel soll statt der bisherigen Pivot-Tabelle nur ein einziger Zahlenwert angezeigt werden und zwar die Zahl der geleisteten Projektstunden. Über eine flexible Datenauswahl können Sie gezielt Abfragen erstellen und z.B. alle geleisteten Projektstunden eines bestimmten Projekts oder alle Projektstunden eines speziellen Mitarbeiters anzeigen lassen.

Für die flexible Datenauswahl bieten sich zwei Varianten an.

Datenauswahl per Datenschnitt

Datenschnitte sind eine komfortable Lösung zur Einfach- und Mehrfachauswahl, vor allem, weil sie die zur Auswahl stehenden Elemente automatisch erkennen. Um einen Datenschnitt einzufügen, gehen Sie wie folgt vor:

1. Erstellen Sie in der Beispiel-Arbeitsmappe ein neues Arbeitsblatt.
2. Geben Sie die folgende Formel in die Zelle A1 ein:
   =CUBEELEMENT("ThisWorkbookDataModel";"[Measures].[Summe von Dauer in Min]";" ")
   Wundern Sie sich nicht, dass die Zelle leer bleibt. Durch die Angabe am Ende wird als Beschriftung ein Leerzeichen festgelegt. So ist das Element für Berechnungen verfügbar, aber im Tabellenblatt unsichtbar.
3. Klicken Sie im Register Einfügen auf Datenschnitt.
4. Wechseln Sie im Dialogfenster auf die Registerkarte Datenmodell und setzen Sie die Markierung auf Tabellen im Datenmodell dieser Arbeitsmappe.
5. Klicken Sie Öffnen.
6. Sie können nun entscheiden, welches Feld für den Datenschnitt verwendet wird. Markieren Sie das Feld "Projekt" aus der Tabelle "tbl_Stundenliste" und bestätigen Sie mit OK.
7. Wenn der Datenschnitt ausgewählt ist, können Sie diesen im Register Datenschnitttools formatieren: Erhöhen Sie die Spaltenzahl auf zwei und wählen Sie eine ansprechende Farbe.
8. Wählen Sie die Zelle C13 aus und geben Sie folgende Formel ein:
   =CUBEWERT("ThisWorkbookDataModel"; $A$1; Datenschnitt_Projekt1) / 60
   Hinweis: Fügen Sie anstelle von "Datenschnitt_Projekt1" den Namen Ihres soeben erstellten Datenschnitts in die Formel ein.
9. Fügen Sie in der Zelle davor den Text "Arbeitszeit in Std.:" ein.

Bild 7: Die Ausgabe kann über den Datenschnitt gesteuert werden

Die Cube-Funktion in Zelle A1 liefert einen Bezug auf das berechnete Feld "Summe von Dauer in Min". Dieses wird in Zelle C13 zusammen mit der Auswahl des Datenschnitts verwendet, um einen Wert aus dem Datenmodell zu ermitteln. Das Ergebnis wird durch 60 geteilt, um die Minuten in Stunden umzuwandeln. Sie können ein beliebiges Projekt oder mit gedrückter Strg-Taste sogar mehrere auswählen. In Zelle C13 erscheint sofort der zugehörige Wert.

Auflistung der Elemente im Auswahlfeld

Wer lieber auf klassische Excel-Methoden zurückgreift, kann stattdessen auch ein Auswahlfeld mit Hilfe der Datenüberprüfung verwenden. Dies hat den Vorteil, dass Nutzer auch Freitext-Eingaben machen können, was bei größeren Listen oft schneller geht, als den entsprechenden Wert zu suchen. In unserem Beispiel soll zusätzlich die Personalnummer in einem Auswahlfeld gewählt oder eingegeben werden können.

Bild 8: Ein benannter Bereich verweist auf die Spalte mit den Personalnummern

Bild 9: Mit der Datenüberprüfung definieren Sie die möglichen Werte zur Auswahl

1. Wählen Sie die Zelle B3 aus und geben Sie den Text "Personalnummer:" ein.
2. Für die Auswahl der Personalnummer wird ein benannter Bereich benötigt, der sich ggf. auf die Größe der Tabelle anpasst. Klicken Sie im Register Formeln auf Namen definieren.
3. Geben Sie als Bereichsname "Personalnummern" ein und als Bezug die folgende Formel (Bild 8):
   =tbl_Mitarbeiter[Personalnummer]
4. Bestätigen Sie mit OK.
5. Wählen Sie die Zelle C3 aus und klicken Sie im Register Daten auf die Schaltfläche Datenüberprüfung. Hiermit können Sie einstellen, dass nur bestimmte Werte eingegeben werden können. Diese Werte werden auch per Auswahlfeld vorgeschlagen.
6. Wechseln Sie im Feld Zulassen auf den Wert Liste.
7. In das Textfeld "Quelle" wird nun der eben erstellte Bereich "Personalnummern" mit einem vorangestellten Ist-Gleich-Zeichen eingetragen (Bild 9).
8. Bestätigen Sie mit OK. Die Zelle enthält nun ein Dropdown-Auswahlfeld.
9. Aus der gewählten Personalnummer soll nun ein Cubeelement mittels der gleichnamigen Formel ermittelt werden. Hierbei bedienen wir uns wieder des Tricks mit dem Leerzeichen als Beschriftung, sodass die Zelle leer erscheint. Geben Sie diese Formel in die Zelle A2 ein:
   =CUBEELEMENT("ThisWorkbookDataModel";"[tbl_Stundenliste].[Mitarbeiter].&["&C3&"]";"")
10. Passen Sie die Formel in der Ergebniszelle C13 folgendermaßen an:
    =WENNFEHLER(CUBEWERT("ThisWorkbookDataModel"; $A$1;$A$2; Datenschnitt_Projekt1) / 60;0)
    Die CUBEWERT-Formel hat neben dem Cubeelement-Feld aus der Zelle A1 und dem Datenschnitt nun eine weitere Einschränkung erhalten, nämlich das Cubeelement aus der Zelle A2. Zusätzlich wurde mit der Formel WENNFEHLER sichergestellt, dass keine Fehlerwerte erscheinen, wenn der ausgewählte Mitarbeiter keine Arbeitsstunden für das gewählte Projekt hat. In diesem Fall wird einfach eine 0 angezeigt.
11. Wenn Sie möchten, verschönern Sie das Dashboard noch durch Zellformatierungen und Rahmen.
12. Wechseln Sie auf das Register Ansicht und entfernen Sie den Haken neben "Gitternetzlinien" um das Tabellenblatt noch aufgeräumter erscheinen zu lassen.

Bild 10: Das fertige Dashboard ist interaktiv und übersichtlich

Auf diese Weise erhalten Sie ein minimalistisches und elegantes Dashboard, das nur einen einzelnen Zahlenwert liefert. Über das Dropdown-Feld und den Datenschnitt können Sie gezielt steuern, welche Information Sie anzeigen möchten, sodass Sie auch bei großen Datenmengen stets den Überblick behalten.