Office-Blog

Früher konnte ich beim Anlegen einer Pivot-Auswertung nur auf die Daten aus einer Tabelle zugreifen. Brauchte ich Informationen aus einer weiteren Tabelle, musste ich beide Tabellen mittels SVERWEIS kombinieren. Seit Excel 2013 geht das viel einfacher – und ganz ohne SVERWEIS!

Die Lösung bringt das Datenmodell. Mit seiner Hilfe kann ich beliebig viele Tabellen als Datenbasis für meine Pivot-Auswertungen heranziehen. Das ist einfacher und weniger fehleranfällig. Zudem eröffnet es mir neue Möglichkeiten zur Datenanalyse und -visualisierung.

Die Feldliste einer »klassischen« Pivot-Tabelle (links) erlaubt den Zugriff auf nur eine Tabelle; mit Hilfe des Datenmodells hingegen lassen sich die Inhalte mehrerer Tabellen in einer Pivot-Tabelle auswerten (rechts)

Warum »intelligente« Tabellen für mein Datenmodell unverzichtbar sind

In der PivotTable-Feldliste stehen nur die Tabellen zur Verfügung, die ich zu einer »intelligenten« Tabelle gemacht habe:

• Nach einem Klick auf eine beliebige Zelle in der Datenliste drücke ich die Tastenkombination Strg+T.
• Wird die Datenliste weder durch leere Zeilen oder Spalten unterbrochen, ist die Bereichsauswahl korrekt und kann mit OK bestätigt werden.

»Intelligente« Tabellen bieten einen weiteren Vorteil: Meine Auswertung greift stets zuverlässig auf die aktuellen Daten zu. Ändere, lösche oder ergänze ich Datensätze, muss ich meine Pivot-Tabelle nur noch per Rechtsklick aktualisieren. Ich muss mir keine Gedanken mehr darüber machen, ob wirklich alle Datensätze berücksichtigt sind.

Wie das Datenmodell seit Excel 2013 Pivot-Auswertungen revolutioniert

Ich möchte wissen, wie viele Artikel pro Warengruppe verkauft wurden. Meine Daten liegen in zwei verschiedenen Tabellen einer Arbeitsmappe vor:

• Die Tabelle auf dem Blatt Bestellungen enthält die ProduktID und die jeweilige Menge.
• Die Warengruppe des jeweiligen Produkts befindet sich in einer zweiten Tabelle auf dem Blatt Produkte.

Diese beiden Tabellen sollen gemeinsam in einer Pivot-Tabelle ausgewertet werden

Anstatt die Warengruppe mittels SVERWEIS in die Tabelle mit den Bestellungen zu holen, nutze ich die Vorteile des Datenmodells:

In 2 Schritten einen Pivot-Bericht auf Basis mehrerer Tabellen erstellen

1. Im Blatt Bestellungen klicke ich in die Liste und wähle Einfügen > Tabellen > PivotTable.
2. Ich aktivieren das Kontrollkästchen Dem Datenmodell diese Daten hinzufügen und klicke anschließend auf OK.

Das Anklicken des Kontrollkästchens erspart mir das umständliche Hantieren mit dem SVERWEIS

Die Felder mehrerer Tabellen in der Pivot-Tabelle verwenden

In der PivotTable-Feldliste am rechten Seitenrand aktiviere ich das Kontrollkästchen vor Menge, um dieses Feld dem Wertebereich hinzuzufügen.

Die Pivot-Tabelle zeigt nun bereits die Gesamtmenge aller bestellten Produkte. Für eine Aufschlüsselung nach Warengruppen fehlt noch das gleichnamige Feld aus dem Blatt Produkte:

1. Um alle Tabellen des Datenmodells in der PivotTable-Feldliste anzuzeigen, klicke ich oberhalb der Feldliste auf Alle [1]. Mit einem Klick auf das Dreieck [2] vor den Tabellennamen tbl_Produkte machen ich alle Felder dieser Tabelle sichtbar.
2. Per Drag & Drop ziehe ich die Warengruppe aus tbl_Produkte in den Bereich Zeilenbeschriftung.

Dank Datenmodell werden die Daten aus beiden Tabellen in nur einer Pivot-Tabelle angezeigt

Wie im richtigen Leben: Manchmal gibt’s Beziehungsprobleme

Das Aussehen der Pivot-Tabelle ist zwar korrekt, aber die Ergebnisse sind fehlerhaft.

Das Layout ist korrekt, aber aufgrund fehlender Beziehungen ist das Ergebnis noch falsch

Damit Excel (wie beim SVERWEIS) zu jedem Artikel in tbl_Bestellungen über die ProduktID die entsprechende Warengruppe in tbl_Produkte nachschlagen kann, erstelle ich mit folgenden Schritten zwischen beiden Tabellen eine Beziehung:

1. Im gelben Hinweiskasten der PivotTable-Feldliste klicke ich auf die Schaltfläche ERSTELLEN.
2. Anschließend lege ich im Dialogfeld die Beziehung zwischen den beiden Tabellen fest. Nach einem Klick auf OK sind die Ergebnisse in der Pivot-Tabelle korrekt!

Tabellenbeziehungen im Datenmodell machen den SVERWEIS überflüssig

Das Datenmodell kann noch viel mehr

Sie möchten mehr darüber erfahren, wie Sie mit dem Datenmodell mehr aus Ihren Pivot-Auswertungen herausholen können? Dann sichern Sie sich gleich einen Platz bei den Excel-Kompetenztagen 2018!