Erklärung
Bisher gehen die meisten Anwender davon aus, dass sie Beziehungen zwischen Tabellen mit unterschiedlichen, aber inhaltlich verbundenen, Daten nur in Datenbanken wie Access erstellen können. Dadurch würden die Tabellen miteinander verknüpft. Ich möchte das an einem Beispiel erläutern: Angenommen, wir haben je eine Tabelle für Branchen, Kunden und Umsätze. Die Brancheninformationen sind auch in der Kundendatei enthalten und die Kundendaten selbstverständlich auch in den Umsätzen. Wenn wir diese Daten sinnvoll in Excel, beispielsweise in Pivot-Tabellen, auswerten wollten, so mussten wir erst alle Daten in einer Excel-Tabelle zusammenfassen. Inzwischen kann man auch in Excel verschiedene Tabellen miteinander verknüpfen. Die von Mircrosoft angebotene Lösung für dies Problem heißt Power Query oder Power Pivot. Zuerst stelle ich die etwas einfachere Variante von Power Pivot vor.
Aktivieren von Power Pivot
Power Pivot ist in den Excel-Versionen der Office Pakete 2019, Office 2013 -2016 Professional Plus sowie den eigenständigen Excel-Versionen von 2013 und 2016 enthalten. Allerdings ist es noch nicht aktiviert. Das muss man in Excel über Datei – Optionen – Add-Ins – COM-Add-Ins machen.
In der dort erscheinenden Liste kann man diverse Leistungen freischalten.
Einlesen der Tabellen
Die nötigen Tabellen liest man in Excel über Daten – Daten abrufen und transformieren – Daten abrufen ein. In der Liste der einlesbaren Formate gibt es kaum eines, welches nicht einlesbar ist. Nur bei Power BI ist die Liste noch umfangreicher. Ich möchte hier nicht auf die vielfältigen Transformationsmöglichkeiten dieser Daten eingehen, nur so viel: Im Dialogfeld „Navigator“ sollte man auf das kleine Pfeilchen neben „Laden“ gehen und dort dann festlegen, wohin die Daten geladen werden sollen.
In unserem Fall sollte die Option „Dem Datenmodell diese Daten hinzufügen“ (letzte Option im Dialogfeld) ausgewählt werden.
Erstellen der Verknüpfungen
Die Grundvoraussetzung dafür, dass wir zwei Tabellen in Power Pivot miteinander verknüpfen können, ist, dass in einer davon das ausgewählte Attribut bzw. Schlüsselfeld nur je einmal vorkommt, sodass wir eindeutige Datensätze haben. Nur so können wir die geforderte 1:n Beziehung erstellen.
Nachdem wir alle benötigten Daten auf diese Art eingeladen haben, wählen wir Power Pivot – Datenmodell – Verwalten. Dort wählen wir unter „Ansicht“ die Diagrammansicht. Wir bekommen nun die eingelesenen Datentabellen mit den Feldnamen nebeneinander angezeigt. Um eine Verknüpfung zu erstellen, klicken wir auf das Feld in der Tabelle, in dem die Definition des Feldes steht, also wo nur eine Instanz jedes Datensatzes vorkommt. Dies Feld „ziehen“ wir dann auf das Pendant in der Tabelle mit den Details. Beispiel: Die Kundennummer in der Kundenliste ziehen wir auf die Kundennummer in der Umsatztabelle und lassen dort los. Jetzt ist zwischen den Tabellen ein Verbindungsstrich mit einer 1 am einen und einem * am anderen Ende entstanden. Wenn wir mit der Maus darüberfahren, können wir auch sehen, welche Felder verknüpft sind. Die Verknüpfung können wir nach einem Rechtsklick auch bearbeiten. Dort können bei Bedarf auch nochmal die Felder korrigieren, welche in Beziehung stehen. Es ist wichtig, dass die Tabelle mit der Definition des Beziehungsfeldes unten erscheint.
Es ist nur ein klassischer Inner oder Natural Join möglich, also eine Verbindung, bei der als Ergebnis der Abfrage nur die Datensätze angezeigt werde, welche im gewählten Attribut oder Schlüsselfeld miteinander übereinstimmen. Outer oder Cross Joins sind nicht möglich. Mehr Beziehungsarten gibt es unter Power Query (siehe unten) oder Power BI (siehe Portfolio).
Pivot-Tabelle aufbauen
Jetzt können wir entweder in der gewohnten Art eine Pivot Tabelle aufbauen oder das direkt aus Power Pivot erledigen. Wenn wir die Standardmethode wählen, sollte die Option „Das Datenmodell dieser Arbeitsmappe verwenden“ aktiviert sein. In unserem Beispiel haben wir drei Tabellen als Grundlage genommen: Branchen, Kunden und Umsätze pro Kunde. Für eine klassische Pivot-Tabelle hätten wir eine einzige Tabelle mit allen relevanten Informationen erstellen müssen (eine „Excel-Tapete“), hier brauchen wir nur die Einzeltabellen zu laden und zu verknüpfen. Das Ergebnis könnte so aussehen:
Das Feld „Branche“ in den Spalten kommt aus der Tabelle „Branchen“, die „Firma“ in den Zeilen aus der Tabelle „Kunden“ und die Summe über die Umsätze
wird aus den Umsätzen in der Tabelle „UmsatzProKunde“ gebildet.
Alternative mit Power Query
Das Abfragewerkzeug von Excel bietet auch die Möglichkeit, Tabellen zu verknüpfen, allerdings werden hier exceltypische große Tabellen erstellt („Excel-Tapeten“). Also aus mehreren Tabellen wird eine gemacht.
Die Tabellen wurden bereits eingelesen (siehe oben). Diese Anweisungen, wie bestimmte Daten für die aktuelle Datei zur Verfügung gestellt werden sollen, kann man wieder aufrufen unter Daten – Abfragen und Verbindungen. Danach werden die für diese Datei genutzten Abfragen in einer Seitenspalte rechts dargestellt. Auf eine dieser Abfragen klicken wir mit rechts und wählen „Bearbeiten“ aus. Dadurch wird der Power Query-Editor gestartet. Hier wählen wir Start – Kombinieren – Abfragen als neue Abfrage zusammenführen (kleines nach unten zeigendes Dreieck neben "Abfragen zusammenführen" anklicken). Das Dialogfeld ist selbsterklärend. Unten kann man die Join-Art einstellen, wobei man unter secs Arten auswählen kann. Dort werden nicht nur die Joins gut beschrieben, sondern man bekommt auch nach Auswahl des Joins ein Ergebnis angezeigt: „Die Auswahl stimmt mit 13 von 13 Zeilen der ersten Tabelle und mit 10 ….“.
Fazit
So haben wir auch unter Excel die Möglichkeit, wie unter „richtigen“ Datenbanken, unterschiedliche Tabellen miteinander in Beziehung zu setzen, indem wir entweder zwischen ihnen einfache Verknüpfungen erstellen oder mithilfe von sechs verschiedenen Join-Arten eine neue Gesamtsicht auf die Tabellen generieren.
Kommentar schreiben