Haben Sie schon einmal SVERWEIS verwendet, um eine Spalte aus einer Tabelle in einer anderen Tabelle zu verwenden? Da Excel nun ein integriertes Datenmodell aufweist, ist SVERWEIS veraltet. Sie können eine Beziehung zwischen zwei Datentabellen erstellen, die auf sich entsprechenden Daten in jeder der Tabellen basiert. Dann können Sie Power View-Blätter erstellen und PivotTables sowie andere Berichte anhand von Feldern der einzelnen Tabellen erzeugen, selbst wenn die Tabellen aus unterschiedlichen Quellen stammen. Wenn Sie z. B. über Kundenumsatzdaten verfügen, können Sie Zeitintelligenzdaten zum Analysieren von Umsatzmustern nach Jahr und Monat importieren und in Beziehung setzen.
Alle Tabellen in einer Arbeitsmappe sind in den PivotTable- und Power View-Feldlisten aufgeführt.
Wenn Sie verknüpfte Tabellen aus einer relationalen Datenbank importieren, können diese Beziehungen häufig in dem von Excel im Hintergrund erstellten Datenmodell erstellt werden. In allen anderen Fällen müssen Beziehungen manuell erstellt werden.
-
Stellen Sie sicher, dass die Arbeitsmappe mindestens zwei Tabellen enthält, und dass jede Tabelle eine Spalte aufweist, die einer Spalte in einer anderen Tabelle zugeordnet werden kann.
-
Führen Sie eine der folgenden Aktionen aus: Formatieren der Daten als Tabelle oder Importieren externer Daten als Tabelle in einem neuen Arbeitsblatt.
-
Geben Sie jeder Tabelle einen aussagekräftigen Namen: Klicken Sie unter Tabellentools auf Entwurf > Tabellenname, und geben Sie einen Namen ein.
-
Stellen Sie sicher, dass die Spalte in einer der Tabellen eindeutige Datenwerte ohne Duplikate aufweist. Excel kann die Beziehung nur erstellen, wenn eine Spalte eindeutige Werte enthält.
Um beispielsweise Kundenumsätze mit Zeitintelligenz in Beziehung zu setzen, müssen beide Tabellen Datumsangaben im gleichen Format (z. B. 1.1.2012) aufweisen, und in mindestens einer Tabelle (Zeitintelligenz) sind die Datumsangaben jeweils nur einmal innerhalb der Spalte aufgelistet.
-
Klicken Sie auf Daten > Beziehungen.
Wenn Beziehungen abgeblendet ist, enthält die Arbeitsmappe nur eine Tabelle.
-
Klicken Sie im Dialogfeld Beziehungen verwalten auf Neu.
-
Klicken Sie im Dialogfeld Beziehung erstellen auf den Pfeil für Tabelle, und wählen Sie eine Tabelle aus der Liste aus. Diese Tabelle sollte sich auf der n-Seite einer 1:n-Beziehung befinden. Bei dem Beispiel mit Kunden und Zeitintelligenz würden Sie zuerst die Kundenumsatztabelle auswählen, da wahrscheinlich mehrere Umsätze an einem bestimmten Tag vorkommen.
-
Wählen Sie für Spalte (fremd) die Spalte mit den Daten aus, die sich auf Verwandte Spalte (primär) beziehen. Wenn z. B. beide Tabellen über eine Datumsspalte verfügen, würden Sie jetzt diese Spalte auswählen.
-
Wählen Sie unter Verwandte Tabelle eine Tabelle mit mindestens einer Spalte mit Daten aus, die sich auf die gerade für Tabelle ausgewählte Tabelle beziehen.
-
Wählen Sie für Verwandte Spalte (primär) eine Spalte mit eindeutigen Werten aus, die den Werten in der für Spalte ausgewählten Spalte entsprechen.
-
Klicken Sie auf OK.
Weitere Informationen zu Beziehungen zwischen Tabellen in Excel
Hinweise zu Beziehungen
-
Um festzustellen, ob eine Beziehung vorhanden ist, können Sie Felder aus unterschiedlichen Tabellen in die PivotTable-Feldliste ziehen. Wenn Sie nicht aufgefordert werden, eine Beziehung zu erstellen, sind die Beziehungsinformationen, die Excel zum Zuordnen der Daten benötigt, bereits vorhanden.
-
Das Erstellen von Beziehungen ist mit der Verwendung von SVERWEISEN vergleichbar: Sie benötigen Spalten, die übereinstimmende Daten enthalten, damit Excel zwischen Zeilen in einer Tabelle und denen in einer anderen Tabelle Querverweise erstellen kann. Im Beispiel mit Zeitintelligenz müsste die Kundentabelle Datumswerte aufweisen, die auch in einer Zeitintelligenztabelle enthalten sind.
-
In einem Datenmodell können Tabellenbeziehungen vom Typ 1:1 (jeder Passagier hat eine Bordkarte) oder vom Typ 1:n (jeder Flug hat viele Passagiere), aber keine n:n-Beziehungen vorhanden sein. Beziehungen des Typs n:n führen zu Zirkelbezugsfehlern wie "Ein Zirkelbezug wurde erkannt". Dieser Fehler tritt auf, wenn Sie eine direkte Verbindung zwischen zwei Tabellen des Typs n:n oder indirekte Verbindungen (eine Kette von Tabellenbeziehungen vom Typ 1:n innerhalb der einzelnen Beziehungen, aber n:n insgesamt gesehen) erstellen. Erfahren Sie mehr über Beziehungen zwischen Tabellen in einem Datenmodell.
-
Die Datentypen in den beiden Spalten müssen kompatibel sein. Ausführliche Informationen finden Sie unter Datentypen in Excel-Datenmodellen.
-
Andere Möglichkeiten zum Erstellen von Beziehungen sind möglicherweise intuitiver, besonders dann, wenn Sie nicht sicher sind, welche Spalten verwendet werden sollen. Informationen finden Sie unter Erstellen einer Beziehung in der Diagrammsicht in Power Pivot.
Beispiel: Erstellen einer Beziehung zwischen Zeitintelligenzdaten und Flugdaten
Sie können die Verwendung von Tabellenbeziehungen und Zeitintelligenz anhand kostenloser Daten aus Microsoft Azure Marketplace erlernen. Einige dieser Datasets sind sehr umfangreich und erfordern eine schnelle Internetverbindung, damit die Daten in angemessener Zeit heruntergeladen werden.
-
Starten Sie das Power Pivot in Microsoft Excel-Add-In, und öffnen Sie das Power Pivot-Fenster.
-
Klicken Sie auf Externe Daten abrufen > Aus Datendienst > Aus Microsoft Azure Marketplace. Die Homepage von Microsoft Azure Marketplace wird im Tabellenimport-Assistenten geöffnet.
-
Klicken Sie unter Preis auf Kostenlos.
-
Klicken Sie unter Kategorie auf Wissenschaft und Statistik.
-
Suchen Sie DateStream, und klicken Sie auf Abonnieren.
-
Geben Sie Ihr Microsoft-Konto ein, und klicken Sie auf Abonnieren. Es sollte eine Vorschau der Daten im Fenster angezeigt werden.
-
Scrollen Sie nach unten, und klicken Sie auf Abfrage auswählen.
-
Klicken Sie auf Weiter.
-
Wählen Sie BasicCalendarUS aus, und klicken Sie dann auf Fertig stellen, um die Daten zu importieren. Bei einer schnellen Internetverbindung sollte der Import ca. eine Minute dauern. Nach Abschluss sollte ein Statusbericht über 73.414 übertragene Zeilen angezeigt werden. Klicken Sie auf Schließen.
-
Klicken Sie auf Externe Daten abrufen > Aus Datendienst > Aus Microsoft Azure Marketplace, um ein zweites Dataset zu importieren.
-
Klicken Sie unter Typ auf Daten.
-
Klicken Sie unter Preis auf Kostenlos.
-
Suchen Sie nach US Air Carrier Flight Delays, und klicken Sie auf Auswählen.
-
Scrollen Sie nach unten, und klicken Sie auf Abfrage auswählen.
-
Klicken Sie auf Weiter.
-
Klicken Sie auf Fertig stellen, um die Daten zu importieren. Bei einer schnellen Internetverbindung kann dies 15 Minuten dauern. Nach Abschluss sollte ein Statusbericht über 2.427.284 übertragene Zeilen angezeigt werden. Klicken Sie auf Schließen. Es sollten nun zwei Tabellen im Datenmodell vorhanden sein. Um eine Beziehung zu erstellen, sind kompatible Spalten in jeder Tabelle erforderlich.
-
Beachten Sie, dass dateKey in BasicCalendarUS das Format 1/1/2012 12:00:00 AM hat. Die On_Time_Performance Tabelle enthält auch die datetime-Spalte FlightDate, deren Werte im gleichen Format angegeben werden: 1/1/2012 12:00:00 AM. Die beiden Spalten enthalten übereinstimmende Daten desselben Datentyps, und mindestens eine der Spalten (DateKey) enthält nur eindeutige Werte. In den nächsten Schritten werden Sie eine Beziehung zwischen den Tabellen auf Grundlage dieser Spalten erstellen.
-
Klicken Sie im Power Pivot-Fenster auf PivotTable, um eine PivotTable in einem neuen oder vorhandenen Arbeitsblatt zu erstellen.
-
Erweitern Sie in der Feldliste den Eintrag On_Time_Performance, und klicken Sie auf ArrDelayMinutes, um das Element zum Bereich "Werte" hinzuzufügen. In der PivotTable sollte die Gesamtsumme der Flugverspätungen in Minuten angezeigt werden.
-
Erweitern Sie BasicCalendarUS, und klicken Sie auf MonthInCalendar, um das Element zum Bereich "Zeilen" hinzuzufügen.
-
In der PivotTable werden nun Monate aufgelistet, doch die Gesamtminutenzahl ist für jeden Monat gleich. Sich wiederholende, identische Werte zeigen an, dass eine Beziehung erstellt werden muss.
-
Klicken Sie in der Feldliste im Bereich "Möglicherweise sind Beziehungen zwischen Tabellen" erforderlich auf Erstellen.
-
Wählen Sie unter "Verwandte Tabelle" die Tabelle On_Time_Performance und unter "Verwandte Spalte (primär)" den Eintrag FlightDate aus.
-
Wählen Sie unter "Tabelle" die Tabelle BasicCalendarUS und unter "Spalte (fremd)" den Eintrag DateKey aus. Klicken Sie auf OK, um die Beziehung zu erstellen.
-
Die Summe der Verspätungen in Minuten ist nun für jeden Monat verschieden.
-
Ziehen Sie in BasicCalendarUS das Feld YearKey in den Bereich "Zeilen" oberhalb von MonthInCalendar.
Sie können nun die verspäteten Ankünfte nach Jahr und Monat oder anderen Werten im Kalender unterteilen.
Tipps: Monate werden standardmäßig in alphabetischer Reihenfolge aufgelistet. Mit dem Power Pivot-Add-In können Sie die Sortierung so ändern, dass Monate in chronologischer Reihenfolge angezeigt werden.
-
Vergewissern Sie sich, dass die Tabelle BasicCalendarUS im Power Pivot-Fenster geöffnet ist.
-
Klicken Sie auf der Registerkarte "Start" auf Nach Spalte sortieren.
-
Wählen Sie unter "Sortieren" den Eintrag MonthInCalendar aus.
-
Wählen Sie unter "Nach den Eintrag" MonthOfYear aus.
In der PivotTable werden die einzelnen Kombinationen aus Monat und Jahr (Oktober 2011, November 2011) nach der Zahl des Monats im Jahr (10, 11) sortiert. Das Ändern der Sortierfolge ist ganz leicht, da mit dem DateStream-Feed alle erforderlichen Spalten für dieses Szenario bereitgestellt werden. Wenn Sie eine andere Zeitintelligenztabelle verwenden, ist ein anderer Schritt erforderlich.
"Beziehungen zwischen Tabellen können erforderlich sein"
Wenn Sie Felder zu einer PivotTable hinzufügen, werden Sie informiert, ob eine Tabellenbeziehung erforderlich ist, damit die von Ihnen ausgewählten Felder in der PivotTable sinnvoll genutzt werden können.
Zwar kann Excel Ihnen mitteilen, wann eine Beziehung erforderlich ist, jedoch nicht, welche Tabellen und Spalten zu verwenden sind oder ob eine Tabellenbeziehung überhaupt möglich ist. Mithilfe der folgenden Schritte finden Sie Antworten auf diese Fragen.
Schritt 1: Feststellen, welche Tabellen in der Beziehung anzugeben sind
Wenn Ihr Modell nur wenige Tabellen umfasst, kann es sofort offensichtlich sein, welche Sie verwenden müssen. Bei umfassenderen Modellen können Sie aber bestimmt Hilfe gebrauchen. Eine Möglichkeit ist die Verwendung der Diagrammsicht im Power Pivot-Add-In. Die Diagrammsicht bietet eine visuelle Darstellung aller Tabellen im Datenmodell. Hier können Sie schnell feststellen, welche Tabellen vom restlichen Modell getrennt sind.
Hinweis: Möglicherweise werden nicht eindeutige Beziehungen erstellt, die bei Verwendung in einem PivotTable- oder Power View-Bericht ungültig sind. Stellen Sie sich vor, alle Tabellen stehen auf irgendeine Weise in Bezug zu anderen Tabellen im Modell, doch wenn Sie versuchen, Felder aus verschiedenen Tabellen miteinander zu kombinieren, wird die Meldung "Möglicherweise sind Beziehungen zwischen Tabellen erforderlich" angezeigt. Die wahrscheinlichste Ursache dafür ist, dass eine n:n-Beziehung entstanden ist. Wenn Sie der Kette von Tabellenbeziehungen folgen, durch die die zu verwendenden Tabellen verbunden sind, werden Sie wahrscheinlich feststellen, dass zwei oder mehr 1:n-Tabellenbeziehungen vorhanden sind. Es gibt keine allgemeine Problemumgehung, die in jeder Situation zutrifft, doch Sie können versuchen, berechnete Spalten zu erstellen, um die zu verwendenden Spalten in einer Tabelle zu konsolidieren.
Schritt 2: Ermitteln der Spalten, die zum Erstellen eines Pfads von einer Tabelle zur nächsten verwendet werden können
Nachdem Sie festgestellt haben, welche Tabelle vom restlichen Modell getrennt ist, prüfen Sie deren Spalten, um herauszufinden, ob eine andere Spalte an einer anderen Stelle im Modell passende Werte aufweist.
Beispiel: Sie haben ein Modell, das Artikelumsätze nach Vertriebsgebiet enthält, und Sie importieren zu einem späteren Zeitpunkt demografische Daten, um zu ermitteln, ob eine Korrelation zwischen Umsätzen und demografischen Trends in den einzelnen Vertriebsgebieten besteht. Da die demografischen Daten aus einer anderen Datenquelle stammen, sind die entsprechenden Tabellen anfänglich vom restlichen Modell getrennt. Um die demografischen Daten in das Modell einzubeziehen, müssen Sie nach einer Spalte in einer der demografischen Tabellen suchen, die einer bereits verwendeten Spalte entspricht. Wenn die demografischen Daten z. B. nach Region angeordnet sind und in den Umsatzdaten angegeben ist, welche Region den Umsatz aufweist, können Sie eine Beziehung zwischen den beiden Datasets erstellen, indem Sie nach einer gemeinsamen Spalte wie Bundesland, Postleitzahl oder Region als Nachschlagespalte suchen.
Neben passenden Werten gibt es noch einige weitere Voraussetzungen für das Erstellen einer Beziehung:
-
Datenwerte in der Nachschlagespalte müssen eindeutig sein. Das heißt, dass die Spalte keine doppelten Werte enthalten darf. In einem Datenmodell entsprechen Nullen und leere Zeichenfolgen einem leeren Wert, bei dem es sich um einen eindeutigen Datenwert handelt. Das bedeutet, dass in der Nachschlagespalte nicht mehrere Nullen vorhanden sein dürfen.
-
Datentypen sowohl in der Quellspalte als auch in der Nachschlagespalte müssen kompatibel sein. Weitere Informationen zu Datentypen finden Sie unter Datentypen in Datenmodellen.
Weitere Informationen zu Tabellenbeziehungen finden Sie unter Beziehungen zwischen Tabellen in einem Datenmodell.
