Nachdem ich im letzten Artikel ein paar grundlegende Gedanken und Überlegungen über das Datenmodells und Power Pivot vorgestellt habe, wollen wir uns heute etwas weiter in die Praxis vorwagen. Eine Schwäche normaler Pivot-Tabellen ist es, wenn die auszuwertenden Daten auf mehrere Quelltabellen verteilt sind. Denn von wenigen eher exotischen Ausnahmen abgesehen kann eine normale Pivot-Tabelle eben nicht mit mehr als einer Datenquelle arbeiten.
Daher ist es üblich, die Daten vorher zusammenzuführen: Entweder auf die klassische und mühsame Tour per SVERWEIS und Co. Oder deutlich komfortabler und sicherer mit Power Query.
Das Datenmodell und Power Pivot machen solche Vorarbeiten aber überflüssig. Stattdessen bleiben alle Daten in ihren unterschiedlichen Quelltabellen und werden lediglich über sogenannte Beziehungen miteinander verbunden.
Wie das genau funktioniert und was man damit anstellen kann, davon handelt der heute Artikel.
Und los geht’s.
Die Ausgangslage
Als Beispiel dient wieder die Rechnungstabelle, die ich bereits im letzten Beitrag verwendet habe. Die Tabelle enthält die grundsätzlichen Transaktionsdaten, wie Rechnungsnummer und -datum, Kundennummer, Artikelnummer und Verkaufsmengen. Allerdings werden wir diesmal auch auf andere Daten zurückgreifen. Denn Details zu den Kunden und Artikeln sind in separaten Tabellen abgespeichert. Die Beispieldatei zum heutigen Artikel kannst du dir hier herunterladen.
Ein solche Trennung zwischen sogenannten Bewegungsdaten (Rechnungen) und Stammdaten (Kunden, Artikel) ist in Warenwirtschaftssystem und anderen Datenbanken üblich und dient vor allem einer effizienteren Datenhaltung.
Wie eingangs erwähnt, müssen Verbindungen zwischen diesen Tabellen hergestellt werden, um sie gemeinsam auswertbar zu machen. Diese Verbindungen oder Beziehungen erfordern gemeinsame Schlüsselfelder, die in den Tabellen enthalten sind. Das wären beispielsweise die Artikelnummer oder die Kundennummer. In der Rechnungstabelle kommt eine bestimmte Artikelnummer erfahrungsgemäß mehrfach vor – die Produkte werden ja hoffentlich häufig verkauft. In der Artikeltabelle hingegen darf jede Artikelnummer nur ein einziges Mal enthalten sein. Genauso verhält es sich mit der Kundennummer. Damit können sogenannte 1:n-Beziehungen zwischen den Tabellen hergestellt werden.
Und genau das werden wir jetzt machen. Dazu gibt es zwei Möglichkeiten.
Gute Beziehungen: Variante 1
Bei sämtlichen Tabellen (Rechnungen, Kunden, Artikel) handelt es sich um formatierte Tabellen. Dies ist eine Grundvoraussetzung für alle folgenden Schritte.
Rufen wir also das Menü „Daten | Beziehungen“ auf:
Wichtig:
Diese Schaltfläche ist nur dann aktiv, wenn die aktuelle Arbeitsmappe mindestens zwei formatierte Tabellen enthält. Ansonsten wären Beziehungen ja auch nicht möglich. Die Tabellen dürfen dabei auch in unterschiedlichen Arbeitsblättern liegen.
Es erscheint ein leeres Dialogfenster, in dem wir über die Schaltfläche „Neu“ die erste Beziehung einrichten werden:
Danach öffnet sich das eigentliche Beziehungsfenster, in dem die ersten beiden Tabellen miteinander verbunden werden sollen.
Im ersten Auswahlfeld wählen wir die Rechnungstabelle („Arbeitsblatttabelle: tblRechnungen“) aus, im Feld daneben werden danach alle Felder aus dieser Tabelle aufgelistet. Da die erste Beziehung zur Artikeltabelle erfolgen soll, markieren wir daher das Feld „Artikel“.
Als verwandte Tabelle tragen wir jetzt die Artikeltabelle („Arbeitsblatttabelle: tblArtikel“) aus und geben im Feld „Verwandte Spalte“ ebenfalls „Artikel“ ein:
Mit einem Klick auf „OK“ schließen wir die Erstellung ab und sehen damit die neue Beziehung im ersten Fenster. Nun kommt die nächste Beziehung dran: Zwischen Rechnungs- und Kundentabelle. Hier ist die Kundennummer die gemeinsame Spalte:
An dieser Stelle möchte ich auf zwei Dinge hinweisen:
Erstens hat sich die Bezeichnung der Rechnungstabelle geändert. Im Auswahlfeld steht jetzt nicht mehr „Arbeitsblatttabelle“, sondern „Datenmodelltabelle“. Denn durch das Erstellen der Beziehungen wurden die beiden Tabellen automatisch im Hintergrund in das Datenmodell geladen.
Außerdem sieht man, dass die Spaltennamen nicht zwingend identisch sein müssen: Kundennr vs. Kdnr. Der Name spielt also keine Rolle, wichtig ist nur, dass die Feldinhalte zueinander passen.
Damit sind wir zunächst einmal fertig mit unserer Beziehungsarbeit, alle Voraussetzungen für eine gemeinsame Pivot-Tabelle sind geschaffen.
Die Power-Pivot-Tabelle
Über das Menü „Einfügen | PivotTable | Aus dem Datenmodell“ wird jetzt eine neue Pivot-Tabelle erstellt:
Als Ziel bestätigen wir den Vorgabewert „Neues Arbeitsblatt“.
Nun sieht eigentlich alles fast so aus, wie man es von einer normalen Pivot-Tabelle kennt. Ein noch leerer Pivot-Bereich im Arbeitsblatt und am rechten Rand die Liste mit den Pivot-Feldern. Allerdings werden jetzt gleich alle vier Quelltabellen angezeigt (drei davon hatten wir ja gerade miteinander verknüpft, auf die vierte Tabelle tblVerkäufer kommen wir später noch zu sprechen). Über das Pfeilsymbol können die Tabellen „aufgeklappt“ werden, so dass man die Felder sieht:
Für die erste Auswertung hole ich mir die Felder „Artikel“ und „Menge“ aus der Tabelle tblRechnungen und das Feld „Bezeichnung“ aus tblArtikel. Da die Tabellen über die zuvor eingerichteten Beziehungen miteinander verbunden sind, können wir jetzt auch die Felder aus allen Tabellen in unserer Pivot-Tabelle verwenden – ganz so, als ob sie alle aus einer einzigen Quelle stammen würden!
Und genauso funktioniert es natürlich auch bei einer Auswertung nach Kunden. Denn auch wenn in der Rechnungstabelle nur die Kundennummern vorhanden sind, kann ich über die erstellten Beziehungen doch die passenden Namen aus der Kundentabelle anzeigen lassen. Das Datenmodell macht’s möglich.
Alternativ: Beziehungen im Datenmodell einrichten
Ich möchte noch einen zweiten, noch bequemeren Weg aufzeigen, Beziehungen zwischen Tabellen zu erstellen. In der Rechnungstabelle sind keinerlei Informationen über die Verkäufer enthalten. Dafür gibt es in der Kundentabelle eine Verkäufernummer, denn jedem Kunden ist ein fester Vertriebsmitarbeiter zugeordnet. Die dazu passenden Namen und Vertriebsregionen befinden sich – du ahnst es bereits – in einer separaten Tabelle:
Diese Verkäufertabelle laden wir jetzt direkt ins Datenmodell. Dazu stellen wir die aktive Zelle irgendwo innerhalb der Verkäufertabelle und rufen das Menü „Power Pivot | Zu Datenmodell hinzufügen“ auf:
(Wie man das Power-Pivot Add-In aktiviert, hatte ich im ersten Artikel beschrieben).
Damit wird das Power Pivot-Fenster geöffnet und du siehst neben der Verkäufertabelle am unteren Bildschirmrand auch alle anderen Tabellen, die durch das Erstellen der Beziehungen automatisch geladen wurden.
Jetzt brauchen wir nur noch die Beziehung. Dazu aktivierst du oben im Menüband die Diagrammansicht durch einen Klick auf die entsprechende Schaltfläche. Das Fenster sollte dann ungefähr so aussehen:
Man erkennt die vorhandenen Verbindungen (= Beziehungen) zwischen Kunden, Rechnungen und Artikel. Und etwas abseits liegt die Verkäufertabelle. Du kannst die Anordnung der Tabelle ganz einfach ändern, indem du oben auf den Namen der Tabelle klickst, die Maus gedrückt hältst und dann das Fenster einfach an die gewünschte Position verschiebst:
Um nun die Kundentabelle mit der Verkäufertabelle zu verbinden, klickst du in das Feld „Verkäufer“ in der Kundentabelle und ziehst dann dieses Feld nach unten auf das Feld „VKID“ in der Verkäufertabelle. Sobald beide Felder grün hervorgehoben sind, lässt du die Maus wieder los und die Beziehung wurde erstellt.
Anschließend kannst du das Power Pivot-Fenster wieder schließen und zu Excel zurückkehren.
Jetzt lässt sich die Pivot-Tabelle auch nach Regionen auswerten, und das, obwohl es innerhalb der Rechnungstabelle eigentlich keinerlei Informationen zu den Verkäufern und Regionen gibt. Aber durch die Beziehungen zwischen allen vier Tabellen sind diese Einschränkungen aufgehoben:
Wie du jetzt sicherlich langsam erkennst, sind das Datenmodell und Power Pivot ziemlich mächtige Werkzeuge. In einem der nächsten Artikel werden wir uns ansehen, welche Auswertungen mit Power Pivot möglich sind, die eine normale Pivot-Tabelle nicht bietet.
Das könnte dich auch interessieren:
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich versuche, mit Hilfe deiner Anleitung einen ganz ähnlichen Berg an Daten zu durchdringen. Es geht um sehr viele Teile in sehr vielen Stücklisten…
Das Konzept der miteinander verbundenen Tabellen ist klar und die Beziehungen sind hergestellt.
Aber irgendwie schaffe ich es nicht, selbst bei Verwendung Deiner Beispieldatei, die Daten korrekt zu verdichten: die gemeinsame Verwendung der Felder tblArtikel_Bezeichnung und tblRechnungen_Artikel im Bereich Zeilen führt in der Anzeige zu einer ausgeklappten Liste von jeweils ALLEN Bezeichnungen unter jedem Artikel.
Was mache ich hier falsch?
Schöne Grüße, Anton.
Hallo Anton,
das beschriebene Problem sollte in dem Moment verschwinden, wenn du ein Feld in den Wertebereich ziehst (also z.B. Menge oder Umsatz). Dann werden nur noch die relevanten Artikel mit den entsprechenden Bezeichnungen angezeigt.
Schöne Grüße,
Martin
Ciao Martin
Ich hatte das gleiche Problem und zur Lösung ein Summenaggregat im Berechnungsfeld gebildet, funktioniert perfekt!
LG
Alexis
Hi Alexis,
was genau meinst du?
Schöne Grüße,
Martin