Zum Glück gibt es SVERWEIS – ist aber nicht immer das Beste!
Klassischerweise gehen Sie hin und reichern Ihre Umsatztabelle mit allen Informationen an, die Sie nachher in der Pivot-Tabelle auswerten möchten. Zum Glück gibt es dafür die SVERWEIS-Funktion, mit der Sie die benötigten Beschreibungen aus der Kunden-, Lieferanten- und Produktetabelle herauslesen können.
Für jede Information müssen Sie physisch eine Spalte hinzufügen. Diese Spalten bestehen aus lauter SVERWEIS-Funktionen, die berechnet werden müssen. Das funktioniert technisch einwandfrei, manchmal langsam, aber es geht!
Doch trotzdem stecken ein paar Unschönheiten in diesem Verfahren:
- Sie müssen sich zwingend vor der Datenauswertung entscheiden, welche Informationen Sie in die Pivot-Tabelle ziehen wollen.
- Sie blähen Ihre Tabelle physisch auf und gleichzeitig wächst der Speicherbedarf Ihrer Excel-Datei.
- Dieselbe Information ist mehrmals in der Arbeitsmappe gespeichert: in der Ursprungstabelle (z. B. Kundentabelle) und gleichzeitig in der auszuwertenden Tabelle (Umsatztabelle).
- Stellen sie zu einem späteren Zeitpunkt fest, dass sie weitere Informationen benötigen, müssen Sie Ihre Umsatztabelle wieder physisch ergänzen.
Wenn das jetzt alles einfach nur normal klingt, läuft alles nach Plan. Worauf ich eigentlich hinaus will, kommt erst noch.
Breite Tabellen braucht es nicht mehr!
Wir haben also die Umsatztabelle, die uns Kunden-, Produkte- und Lieferantenschlüssel liefert. Das brauchen Sie auch für die SVERWEIS-Funktion. Nun “erklären” Sie der Umsatztabelle, dass die Kundeninformationen aus der Kundentabelle stammen, die Produktinformationen aus der Produktetabelle und die Lieferanteninformationen aus der Lieferantentabelle. Die Erklärung erfolgt durch eine Verbindung von Schlüsselfeldern unterschiedlicher Tabellen. Genauso, wie Sie mit der SVERWEIS-Funktion die Kundennummer aus der Umsatztabelle referenzieren und in der Kundentabelle nachschlagen würden.
Aber natürlich erfolgt die Erklärung, die ich hier meine, nicht mit der SVERWEIS-Funktion, sondern über die Definition einer Beziehung, d.h. einer Relation. Von der Umsatztabelle zu den anderen Tabellen werden logische Beziehungen eingerichtet und so entsteht das relationale Datenmodell.
Wie Sie weiter unten sehen werden, ersetzt das relationale Datenmodell den Einsatz breiter Tabellen komplett. Sie können auf alle Felder aller Tabellen direkt zugreifen.
Die effizientere Alternative zum SVERWEIS: das relationale Datenmodell in Power Pivot
Power Pivot ist ab der Version 2016 in Excel integriert. Ich hoffe sehr, dass Ihre Unternehmung dies nicht deaktiviert hat – das wäre sehr, sehr bedauernswert (wehren Sie sich dagegen!). Leider sorgt Microsoft mit dem Namen «Power Pivot» auch für Verwirrung. Power Pivot ist keine Pivot-Tabelle. Mit Power Pivot erstellen Sie ein relationales Datenmodell. Aber ja, bleiben wir beim eigentlichen Thema.
Beim relationalen Datenmodell stellen Sie Beziehungen her zwischen Tabellen. Die Verbindung erfolgt über Schlüsselfelder. Im folgenden Bild sehen Sie vier Tabellen, die mit durchgezogenen Linien verbunden sind. Das sind die Beziehungen. Auf der einen Seite steht eine «1», auf der anderen ein Stern («*»). Damit werden die Kardinalitäten angezeigt. Das ist aber ein separates Thema, über das ich später mal einen Blogpost schreiben werde.
Und so erstellen Sie das relationale Datenmodell in Power Pivot
Gehen Sie wie folgt vor:
- Stellen Sie sicher, dass alle Tabellen über ein Schlüsselfeld verfügen (KundenNr, ProduktNr, LieferantNr)
- Laden Sie die Tabellen in das Datenmodell in Power Pivot (meine Empfehlung: immer via Power Query)
- Wechseln Sie in Power Pivot in die Diagrammsicht
- Verbinden Sie die Schlüsselfelder der Umsatztabelle (KundenNr, ProduktNr) mit den übereinstimmenden Schlüsselfeldern der Stammdatentabellen. Hierfür klicken Sie auf das Feld KundenNr der Umsatztabelle und ziehen die Maus über das korrespondierende Feld in der Kundentabelle. Wiederholen Sie den Vorgang für alle anderen Schlüsselfelder.
- Hier liegt ein sog. Snowflake-Schema vor. Die Dimensionstabelle Produkte hat eine Beziehung zur Dimensionstabelle Lieferanten. Die Verbindung erfolgt über den Schlüssel LieferantNr.
Alle Felder aller Tabellen des Datenmodells können Sie in der Pivot-Tabelle situativ einsetzen – ganz ohne physische Tabellenanreicherung
Da Sie jetzt das Datenmodell erstellt und auch schon Measures geschrieben haben, können Sie eine Pivot-Tabelle einfügen. Als Datenquelle ziehen Sie das Datenmodell heran.
Wie Sie im oberen Bild sehen, präsentiert sich die Feldliste etwas anders. Wir sehen vier Tabellen und darunter die Felder der jeweiligen Tabelle.
Die Pivot-Tabelle enthält den Namen des Lieferanten sowie beide Measures “Total Menge” und “Total Umsatz”. Werfen Sie jetzt einen Blick auf das Datenmodell. Die Beziehung von der Umsatztabelle zum Lieferanten erfolgt über die Produktetabelle. Ist das nicht genial?
Hier greifen Mechanismen, die den Titel “Auswertungskontext” und “Filterweitergabe” tragen. Zu diesen zentralen Konzepten werde ich später einen separaten Blogpost schreiben. Falls Sie Lust haben auf dem Laufenden zu bleiben, abonnieren Sie meine Newsletter.