Erstellen von Beziehungen zwischen mehreren Tabellen mithilfe des Datenmodells in Excel
Excel ist ein leistungsstarkes Tool für die Datenanalyse und anschließende Automatisierung beim Umgang mit großen Datenmengen. Sie könnten viel Zeit damit verbringen, Tonnen von Daten mit VLOOKUP, INDEX-MATCH, SUMIF usw. zu analysieren.
Dank des Excel-Datenmodells können Sie durch automatische Datenberichte wertvolle Zeit sparen. Finden Sie im folgenden Abschnitt heraus, wie einfach Sie mithilfe des Datenmodells eine Beziehung zwischen zwei Tabellen zuweisen können, und veranschaulichen Sie eine solche Beziehung in einer Pivot-Tabelle.
Die Grundvoraussetzungen
Sie benötigen Power Pivot und Power Query (Get & Transform), um beim Erstellen eines Excel- Datenmodells mehrere Aufgaben auszuführen. So können Sie diese Funktionen in Ihre Excel-Arbeitsmappe aufnehmen:
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: Die Office Professional Plus-Edition von Excel 2013 enthält Power Pivot. Sie müssen es jedoch vor dem ersten Gebrauch aktivieren. Hier ist, wie:
- Klicken Sie in der Multifunktionsleiste einer Excel-Arbeitsmappe auf Datei .
- 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 das Kontrollkästchen für Microsoft Power Pivot für Excel .
3. Excel 2016 und höher: Das Power Pivot-Menü finden Sie auf der Multifunktionsleiste .
So erhalten Sie eine Power-Abfrage (Get & Transform)
1. Excel 2010: Sie können das Power Query-Add-In von Microsoft herunterladen. Nach der Installation wird Power Query auf der Multifunktionsleiste angezeigt .
2. Excel 2013: Sie müssen Power Query aktivieren, indem Sie dieselben 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) auf der Registerkarte Daten in Excel Ribbon .
Erstellen Sie ein Datenmodell, indem Sie Daten in die Excel-Arbeitsmappe importieren
Für dieses Tutorial können Sie vorformatierte Beispieldaten von Microsoft erhalten:
Download: Beispielstudentendaten (nur Daten) | Beispielstudentendaten (vollständiges Modell)
Sie können eine Datenbank mit mehreren verwandten Tabellen aus vielen Quellen wie Excel-Arbeitsmappen, Microsoft Access, Websites, SQL Server usw. importieren. Anschließend 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 .
2. Sie finden verschiedene Möglichkeiten, um Daten aus externen oder internen Quellen zu importieren. Wählen Sie diejenige, die zu Ihnen passt.
3. Wenn Sie Excel 2013 Edition verwenden, klicken Sie in der Multifunktionsleiste auf Power Query und wählen Sie dann Externe Daten abrufen aus , um 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. Aktivieren Sie das Kontrollkästchen 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 Listen der PivotTable-Felder sehen .
Sie können auch Power Pivot-Funktionen wie berechnete Spalten, KPIs, Hierarchien, berechnete Felder und gefilterte Datasets aus dem Excel-Datenmodell verwenden. Zu diesem Zweck müssen Sie ein Datenmodell aus einer einzelnen Tabelle generieren. Sie können diese Schritte ausprobieren:
1. Formatieren Sie Ihre Daten in einem Tabellenmodell, indem Sie alle Zellen auswählen, die Daten enthalten, und klicken Sie dann auf Strg + T.
2. Wählen Sie nun die gesamte Tabelle aus und klicken Sie auf der Multifunktionsleiste auf die Registerkarte Power Pivot .
3. Klicken Sie im Abschnitt Tabellen auf Zum Datenmodell hinzufügen .
Excel erstellt Tabellenbeziehungen zwischen verwandten Daten aus dem Datenmodell. Zu diesem Zweck sollten in den importierten Tabellen Primär- und Fremdschlüsselbeziehungen vorhanden sein.
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
Nachdem Sie ein Datenmodell in Ihrer Excel-Arbeitsmappe haben, 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 Diagrammansicht von Power Pivot können Sie diese Felder ziehen und ablegen, um eine Beziehung aufzubauen. Führen Sie die folgenden Schritte aus, um Tabellenverknüpfungen im Excel-Datenmodell zu erstellen:
1. Klicken Sie in der Multifunktionsleiste der Excel-Arbeitsmappe auf das Power Pivot- Menü.
2. Klicken Sie nun im Abschnitt Datenmodell auf Verwalten . Sie sehen den Power Pivot- Editor wie folgt:
3. Klicken Sie auf die Schaltfläche Diagrammansicht im Abschnitt Ansicht der Registerkarte Power Pivot Home . Sie sehen Tabellenspaltenüberschriften, die nach dem Tabellennamen gruppiert sind.
4. Sie können jetzt die eindeutige Feldkennung per Drag & Drop von einer Tabelle in eine andere ziehen. Es folgt das Beziehungsschema zwischen den vier Tabellen des Excel-Datenmodells:
Im Folgenden wird die Verknüpfung zwischen Tabellen beschrieben:
- Tischschüler | Studentenausweis zur Tabelle Noten | Studenten ID
- Tafelsemester | Semester-ID zur Tabelle Noten | Semester
- Tabellenklassen | Klassennummer zur Tabelle Noten | Klassen-ID
5. Sie können Beziehungen erstellen, indem Sie ein Paar eindeutiger Wertespalten auswählen. Wenn Duplikate vorhanden sind, wird der folgende Fehler angezeigt:
6. Sie werden Stern (*) auf der einen Seite und Eins (1) auf der anderen Seite in der Diagrammansicht von Beziehungen bemerken. Es definiert, dass eine Eins-zu-Viele-Beziehung zwischen den Tabellen besteht.
7. Klicken Sie im Power Pivot-Editor auf die Registerkarte Design und wählen Sie dann Beziehungen verwalten aus, um zu erfahren, in welchen Feldern die Verbindungen hergestellt werden.
Generieren Sie eine PivotTable mithilfe des Excel-Datenmodells
Sie können jetzt eine PivotTable oder ein PivotChart erstellen, um Ihre Daten aus dem Excel-Datenmodell zu visualisieren. Eine Excel-Arbeitsmappe enthält möglicherweise nur ein Datenmodell, Sie können die Tabellen jedoch weiterhin aktualisieren.
Da sich die Daten im Laufe der Zeit ändern, können Sie weiterhin dasselbe Modell verwenden und Zeit sparen, wenn Sie mit demselben Datensatz arbeiten. Sie werden mehr Zeit sparen, wenn Sie Daten in Tausenden von Zeilen und Spalten bearbeiten. Gehen Sie folgendermaßen vor, um einen PivotTable-basierten Bericht zu erstellen:
1. Klicken Sie im Power Pivot-Editor auf die Registerkarte Home .
2. Klicken Sie in der Multifunktionsleiste auf PivotTable .
3. Wählen Sie eines zwischen Neuem Arbeitsblatt oder Vorhandenem 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 Lernprogramm verwendeten Beispieldaten von Schülern erstellt wurde. Mit dem Excel-Datenmodell-Tool können Sie auch professionelle Pivot-Tabellen oder -Diagramme aus Big Data erstellen.
Transformieren Sie komplexe Datensätze mithilfe des Excel-Datenmodells in einfache Berichte
Das Excel-Datenmodell nutzt die Vorteile des Erstellens 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 jedes Mal, wenn die Quelldaten aktualisiert werden, durch Tausende von Spalten und Zeilen zu scrollen.