Wer auch nur Ansatz weise mit grösseren Tabellen in Excel arbeitet kennt die Situation.

Management möchte eine Analyse haben. “Fast” alles Notwendige ist bereits in der Tabelle vorhanden. Damit jedoch die paar wenigen fehlenden Informationen auch in der Liste erscheinen, müssen diese zuerst per SVERWEIS hinzugefügt werden. Die Datenanreicherung ist 90% der Arbeit des gesamten Auftrages. Die Analyse an und für sich wird dann in 5 Minuten mit einer PivotTabelle erledigt!

Bis jetzt war der SVERWEIS die Königsfunktion für solche Aufgaben. Aber jetzt nicht mehr! Neu ist es das Beziehungen Datentool!

Ab Office 2013 ist Datentool “Beziehungen” standardmässig in Excel integriert. In Excel 2010 geht es auch, aber nur mit dem PowerPivot Add-In.

Beziehungen findet man im Daten Menü unter den Datentools:

Beziehungen Datentool erklärt

Wir möchten eine Transaktionen Liste mit Informationen anreichern damit wir aussagekräftige Analysen erstellen können. Aus der Kunden Liste benötigen wir z.B. die Kunden Namen, etc.

Die Tabellen sehen so aus:

Wichtig ist das die Tabellen die wir analysieren als Tabellen erstellt sind (also mit dem Tabellen Werkzeug oder per STRG + L).

Das “Beziehungen” Symbol ist ausgegraut wenn nur eine Tabelle in einer Arbeitsmappe existiert!

Wenn wir mehrere Tabellen in der Arbeitsmappe haben, können wir das Symbol klicken, und es öffnet sich das Beziehungen verwalten Fenster.

Beziehungen erstellen

Hier klicken wir Neu.

Wir erstellen zuerst die Beziehung zwischen der ProductID Spalte in der Transaktionen Liste und der ProductID Spalte in der Produkt Liste. Das macht man in dem man auf die Dropdown Listen klickt und jeweils die Tabellen und Spalten auswählt. Die Spalten müssen
nicht zwingend gleich heissen!

Eine der beiden ausgewählten Spalten muss jedoch eine eindeutige Liste enthalten (also keine Duplikaten Werte) damit Excel nachher eine 1:n Beziehung erstellen kann!

Achtet man nicht darauf, so kommt diese Fehlermeldung:

Die zweite Beziehung erstellt man analog der ersten…

Wir verbinden die KundenID Spalten in den beiden relevanten Listen. Hier ist es interessant zu wissen das die Spalten in irgendeiner Reihenfolge sein können, was ja beim SVERWEIS nicht der Fall war! Natürlich gab es dafür in der Vergangenheit immer Tricks und Workarounds wie z.B. die INDEX(VERGLEICH) Formel Kombination, aber das hier ist eindeutig Angenehmer!

Am Schluss hat man im Beziehungen verwalten Fenster folgende Übersicht:

An dieser Stelle möchte ich hervorheben dass man Beziehungen auch löschen oder deaktivieren kann. Ich gehe auf die beiden Punkte aber nicht weiter ein, da sie selbsterklärend sind.

Analyse erstellen mit dem Datenmodell der Beziehungen

Hat man alle Beziehungen erstellt, kann es mit der Analyse losgehen!

Wir erstellen wie gewohnt eine PivotTabelle in dem wir den Cursor in eine der zu analysierenden Tabellen platzieren. Excel erkennt wie gewohnt den Datenbereich und…

…gibt uns die im PivotTable erstellen Fenster die Möglichkeit “Dem Datenmodell diese Daten hinzufügen” auszuwählen mit einem Haken.

Wäre die ausgewählte Tabelle nicht bereits ein Teil des Datenmodelles, könnten wir diese dem Datenmodell neu hinzufügen.

Da unsere Tabelle jedoch bereits dabei ist, überspringt Excel diesen Schritt und gibt uns dieses PivotTable-Fenster:

In dem wir auf ALLE klicken sehen wir alle Tabellen im Datenmodell. Wenn es eine Tabelle in der Arbeitsmappe gibt die noch nicht verbunden ist, so wird sie unten aufgeführt und durch ein Strich abgegrenzt, wie hier z.B. Tabelle6

So können wir nun sämtliche Informationen verbinden, Daten aus verschiedenen Tabellen ziehen, und unsere Analyse im Handumdrehen erstellen!

Nachteile

So vorteilhaft wie dieses Feature auch ist, nicht alles ist ganz so gut daran wie man es sich wünschen würde.

Hier ein paar Nachteile welche mir immer wieder auffallen wenn ich es einsetze:

• Keine berechneten Felder mehr möglich in Pivot Tabellen
• Daten können nicht mehr gruppiert werden in Pivot Tabellen
• Drilldown Funktion in Pivot ergänzt Daten aus anderen Tabellen nicht (wäre dies möglich könnte man es auch Konsolidierungswerkzeug benutzen)
• Je mehr Beziehungen bestehen, desto intensiver wird die benötigte Rechenleistung. In einem kleinen Test bemerkte ich bereits ab 100’000 Zeilen mit zwei Beziehungen auf meinem Rechner erhebliche Geschwindigkeitsverzögerungen von ein paar Sekunden. Eine Datenmenge die Excel in einer normalen PivotTabelle im Handumdrehen auswertet.

Ist “Beziehungen” für Euch auch die neue Königsfunktion beim Daten anreichern?