So erstellen Sie Beziehungen zwischen mehreren Tabellen mithilfe des Datenmodells in Excel
Excel ist ein leistungsstarkes Werkzeug zur Datenanalyse und anschließenden Automatisierung beim Umgang mit großen Datenmengen. Sie könnten viel Zeit damit verbringen, Tonnen von Daten mit SVERWEIS, INDEX-MATCH, SUMIF usw. zu analysieren.
Dank des Excel-Datenmodells können Sie durch automatische Datenberichte wertvolle Zeit sparen. Im folgenden Abschnitt erfahren Sie, wie einfach Sie mithilfe des Datenmodells eine Beziehung zwischen zwei Tabellen zuweisen können und eine Illustration einer solchen Beziehung in einer Pivot-Tabelle.
Die Grundvoraussetzungen
Sie benötigen Power Pivot und Power Query (Abrufen und Transformieren), um beim Erstellen eines Excel- Datenmodells mehrere Aufgaben auszuführen. So erhalten Sie diese Funktionen in Ihrer Excel-Arbeitsmappe:
So erhalten Sie Power Pivot
1. Excel 2010: Sie müssen das Power Pivot-Add-In von Microsoft herunterladen und dann für Ihr Excel-Programm auf Ihrem Computer installieren.
2. Excel 2013: Office Professional Plus Edition von Excel 2013 enthält Power Pivot. Sie müssen es jedoch vor der ersten Verwendung aktivieren. Hier ist, wie:
- Klicken Sie auf Datei auf dem Band einer Excel – Arbeitsmappe.
- Klicken Sie dann auf Optionen , um die Excel-Optionen zu öffnen.
- Klicken Sie nun auf Add-Ins .
- Wählen Sie COM-Add-Ins aus, indem Sie auf das Dropdown-Menü des Felds Verwalten klicken.
- Klicken Sie auf Los und aktivieren Sie dann das Kontrollkästchen für Microsoft Power Pivot für Excel .
3. Excel 2016 und höher: Sie finden das Power Pivot-Menü auf der Multifunktionsleiste .
So erhalten Sie Power Query (Abrufen und Transformieren)
1. Excel 2010: Sie können das Power Query-Add-In von Microsoft herunterladen. Nach der Installation wird Power Query im Menüband angezeigt .
2. Excel 2013: Sie müssen Power Query aktivieren, indem Sie die gleichen Schritte ausführen, die Sie gerade ausgeführt haben, um Power Pivot in Excel 2013 funktionsfähig zu machen.
3. Excel 2016 und höher: Sie finden Power Query (Get & Transform) über die Registerkarte Daten in der Excel- Multifunktionsleiste .
Erstellen eines Datenmodells durch Importieren von Daten in die Excel-Arbeitsmappe
Für dieses Tutorial können Sie vorformatierte Beispieldaten von Microsoft abrufen:
Download: Beispielstudentendaten (nur Daten) | Beispielstudentendaten (vollständiges Modell)
Sie können eine Datenbank mit mehreren verknüpften Tabellen aus vielen Quellen wie Excel-Arbeitsmappen, Microsoft Access, Websites, SQL Server usw. importieren. Dann müssen Sie den Datensatz formatieren, damit Excel ihn verwenden kann. Hier sind die Schritte, die Sie ausprobieren können:
1. Klicken Sie in Excel 2016 und späteren Editionen auf die Registerkarte Daten und wählen Sie Neue Abfrage aus .
2. Sie haben mehrere Möglichkeiten, Daten aus externen oder internen Quellen zu importieren. Wählen Sie diejenige, die zu Ihnen passt.
3. Wenn Excel – Ausgabe 2013 verwenden, klicken Sie auf der Power – Abfrage auf dem Band und dann Externe Daten wählen Get Daten für den Import auszuwählen.
4. Sie sehen das Navigator- Feld, in dem Sie auswählen müssen, welche Tabellen Sie importieren möchten. Klicken Sie auf das Kontrollkästchen für Mehrere Elemente auswählen, um mehrere Tabellen für den Import auszuwählen.
5. Klicken Sie auf Laden , um den Importvorgang abzuschließen.
6. Excel erstellt anhand dieser Tabellen ein Datenmodell für Sie. Sie können die Tabellenspaltenüberschriften in den PivotTable-Felderlisten sehen .
Sie können auch Power Pivot-Funktionen wie berechnete Spalten, KPIs, Hierarchien, berechnete Felder und gefilterte Datensätze aus dem Excel-Datenmodell verwenden. Zu diesem Zweck müssen Sie das Datenmodell aus einer einzelnen Tabelle generieren. Sie können diese Schritte ausprobieren:
1. Formatieren Sie Ihre Daten in einem tabellarischen Modell, indem Sie alle Zellen mit Daten auswählen und dann auf Strg+T klicken.
2. Wählen Sie nun die gesamte Tabelle und klicken Sie dann auf dem Power Pivot Registerkarte auf der Band.
3. Klicken Sie im Abschnitt Tabellen auf Zum Datenmodell hinzufügen .
Excel erstellt Tabellenbeziehungen zwischen verwandten Daten aus dem Datenmodell. Dazu sollten innerhalb der importierten Tabellen Primär- und Fremdschlüsselbeziehungen bestehen.
Excel verwendet die Beziehungsinformationen aus der importierten Tabelle als Grundlage, um Verbindungen zwischen den Tabellen in einem Datenmodell zu generieren.
Erstellen Sie Beziehungen zwischen den Tabellen im Datenmodell
Da Sie nun über ein Datenmodell in Ihrer Excel-Arbeitsmappe verfügen, müssen Sie Beziehungen zwischen den Tabellen definieren, um aussagekräftige Berichte zu erstellen. Sie müssen jeder Tabelle eine eindeutige Feldkennung oder einen Primärschlüssel zuweisen, z. B. Semester-ID, Klassennummer, Studenten-ID usw.
Mit der Diagrammansichtsfunktion von Power Pivot können Sie diese Felder ziehen und ablegen, um eine Beziehung aufzubauen. Führen Sie diese Schritte aus, um Tabellenlinks im Excel-Datenmodell zu erstellen:
1. Auf dem Band der Excel – Arbeitsmappe, klicken Sie auf Menü Power Pivot.
2. Klicken Sie nun im Bereich Datenmodell auf Verwalten . Der Power Pivot- Editor wird wie unten gezeigt angezeigt:
3. Klicken Sie auf die Schaltfläche Diagrammansicht im Abschnitt Ansicht der Registerkarte Power Pivot- Startseite . Sie sehen Tabellenspaltenüberschriften, die nach dem Tabellennamen gruppiert sind.
4. Sie können nun die eindeutige Feldkennung per Drag & Drop von einer Tabelle in eine andere ziehen. Im Folgenden sehen Sie das Beziehungsschema zwischen den vier Tabellen des Excel-Datenmodells:
Im Folgenden wird die Verknüpfung zwischen Tabellen beschrieben:
- Tischschüler | Schüler-ID zur Tabelle Noten | Studenten ID
- Tabelle Semester | Semester-ID zur Tabelle Noten | Semester
- Tabellenklassen | Klassennummer zur Tabelle Noten | Klassen-ID
5. Sie können Beziehungen erstellen, indem Sie ein Paar eindeutiger Wertspalten auswählen. Wenn Duplikate vorhanden sind, wird der folgende Fehler angezeigt:
6. In der Diagrammansicht der Beziehungen werden Sie auf der einen Seite Stern (*) und auf der anderen Seite Eins (1) bemerken. Es definiert, dass zwischen den Tabellen eine Eins-zu-Viele-Beziehung besteht.
7. Klicken Sie im Power Pivot-Editor auf die Registerkarte Entwurf und wählen Sie dann Beziehungen verwalten aus, um zu erfahren, welche Felder die Verbindungen herstellen.
Generieren Sie eine PivotTable mit dem Excel-Datenmodell
Sie können jetzt eine PivotTable oder ein PivotChart erstellen, um Ihre Daten aus dem Excel-Datenmodell zu visualisieren. Eine Excel-Arbeitsmappe kann nur ein Datenmodell enthalten, aber Sie können die Tabellen weiterhin aktualisieren.
Da sich Daten im Laufe der Zeit ändern, können Sie weiterhin dasselbe Modell verwenden und beim Arbeiten mit demselben Datensatz Zeit sparen. Sie werden mehr Zeit sparen, wenn Sie an Daten in Tausenden von Zeilen und Spalten arbeiten. Gehen Sie folgendermaßen vor, um einen PivotTable-basierten Bericht zu erstellen:
1. Wählen Sie im Editor Power – Pivot, klicken Sie auf die Registerkarte Start.
2. Geben Sie auf dem Menüband auf Pivot – Tabelle klicken.
3. Wählen Sie eines zwischen Neues Arbeitsblatt oder Vorhandenes Arbeitsblatt.
4. Wählen Sie OK . Excel fügt eine PivotTable hinzu , die den Bereich Feldliste auf der rechten Seite anzeigt .
Im Folgenden finden Sie eine ganzheitliche Ansicht einer Pivot-Tabelle, die mithilfe des Excel-Datenmodells für die in diesem Tutorial verwendeten Beispiel-Studentendaten erstellt wurde. Sie können mit dem Excel-Datenmodell-Tool auch professionelle Pivot-Tabellen oder -Diagramme aus Big Data erstellen.
Verwandeln Sie komplexe Datensätze in einfache Berichte mit dem Datenmodell von Excel
Das Excel-Datenmodell nutzt die Vorteile der Erstellung von Beziehungen zwischen Tabellen, um aussagekräftige Pivot-Tabellen oder -Diagramme für Datenberichtszwecke zu erstellen.
Sie können die vorhandene Arbeitsmappe kontinuierlich aktualisieren und Berichte zu aktualisierten Daten veröffentlichen. Sie müssen keine Formeln bearbeiten oder Zeit investieren, um bei jeder Aktualisierung der Quelldaten durch Tausende von Spalten und Zeilen zu scrollen.