So erstellen Sie Beziehungen zwischen mehreren Tabellen mithilfe des Datenmodells in Excel

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 (Get & Transform), um beim Erstellen mehrere Aufgaben auszuführen Excel Datenmodell. 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 herunterladen von Microsoft und installieren Sie es dann für Ihr Excel-Programm auf Ihrem Computer.

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:



  1. Klicke auf Datei auf der Schleife einer Excel-Arbeitsmappe.
  2. Dann klick auf Optionen öffnen Excel-Optionen .
  3. Klicken Sie nun auf Add-Ins .
  4. Auswählen COM-Add-Ins indem Sie auf das Dropdown-Menü des Verwalten Kasten.
  5. Klicke auf gehen und aktivieren Sie dann das Kontrollkästchen für Microsoft PowerPivot für Excel .

3. Excel 2016 und höher: Sie finden das Power Pivot-Menü auf der Schleife .

Verwandt: So fügen Sie die Registerkarte 'Entwickler' zur Multifunktionsleiste in Microsoft Word und Excel hinzu



So erhalten Sie Power Query (Abrufen und Transformieren)

1. Excel 2010: Sie können das Power Query-Add-In von herunterladen Microsoft . Nach der Installation, Power-Abfrage wird auf der Schleife .

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) unter dem Daten Registerkarte in Excel Schleife .

Erstellen eines Datenmodells durch Importieren von Daten in die Excel-Arbeitsmappe

Für dieses Tutorial können Sie vorformatierte Beispieldaten von Microsoft abrufen:

Herunterladen : Beispielschüler Daten (nur Daten) | Beispielschüler Daten (komplettes 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 das Daten Tab und wählen Sie Neue Abfrage .

2. Sie haben mehrere Möglichkeiten, Daten aus externen oder internen Quellen zu importieren. Wählen der zu dir passt.

3. Wenn Sie Excel 2013 Edition verwenden, klicken Sie auf Power-Abfrage auf der Schleife und dann auswählen Externe Daten abrufen um Daten für den Import auszuwählen.

4. Sie sehen die Navigator Feld, in dem Sie auswählen müssen, welche Tabellen Sie importieren möchten. Klicken Sie auf das Kontrollkästchen für Wählen Sie mehrere Elemente aus um mehrere Tabellen für den Import auszuwählen.

5. Klicken Sie auf Belastung um den Importvorgang abzuschließen.

6. Excel erstellt anhand dieser Tabellen ein Datenmodell für Sie. Sie können die Überschriften der Tabellenspalten im PivotTable-Felder Auflistungen.

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 ein 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 klicken Strg+T .

2. Wählen Sie nun die gesamte Tabelle aus und klicken Sie dann auf das Power Pivot Registerkarte auf der Schleife .

3. Von der Tabellen Abschnitt, klicken Sie 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.

Verwandt: So erstellen Sie eine Was-wäre-wenn-Analyse in Microsoft Excel

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 Schleife der Excel-Arbeitsmappe klicken Sie auf das Power Pivot Speisekarte.

2. Klicken Sie nun auf Verwalten in dem Datenmodell Sektion. Sie werden sehen, Power Pivot Editor wie unten gezeigt:

3. Klicken Sie auf das Diagrammansicht Taste befindet sich im Sicht Abschnitt des Power Pivot Heim Tab. 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. Du wirst es bemerken Stern (*) auf einer Seite und Eins 1) andererseits in der Diagrammansicht von Beziehungen. Es definiert, dass zwischen den Tabellen eine Eins-zu-Viele-Beziehung besteht.

7. Klicken Sie im Power Pivot-Editor auf das Entwurf Registerkarte und wählen Sie dann Beziehungen verwalten um zu wissen, 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.

Verwandte: Was ist Data Mining und ist es illegal?

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. Klicken Sie im Power Pivot-Editor auf das Heim Tab.

2. Auf dem Schleife , klicke auf PivotTable .

3. Wählen eines zwischen New Worksheet oder Existing Worksheet.

So steuern Sie das Telefon vom PC aus

4. Wählen Sie OK . Excel fügt a . hinzu PivotTable das wird das zeigen Feldliste scheibe rechts.

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 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 bei jeder Aktualisierung der Quelldaten durch Tausende von Spalten und Zeilen zu scrollen.

Teilen Teilen Tweet Email So erstellen Sie eine Pivot-Tabelle in Excel

Erfahren Sie, wie Sie Pivot-Tabellen in Excel erstellen und diese verwenden, um die gewünschten Informationen zu zeichnen.

Weiter lesen Verwandte Themen
  • Produktivität
  • Tabellenkalkulationstipps
  • Microsoft Excel
  • Tipps zu Microsoft Office
  • Datenanalyse
Über den Autor Tamal Das(100 veröffentlichte Artikel)

Tamal ist freiberufliche Autorin bei MakeUseOf. Nachdem er in seiner vorherigen Tätigkeit in einem IT-Beratungsunternehmen umfangreiche Erfahrungen in den Bereichen Technologie, Finanzen und Geschäftsprozesse gesammelt hatte, begann er vor 3 Jahren das Schreiben als Vollzeitberuf. Während er nicht über Produktivität und die neuesten technischen Nachrichten schreibt, liebt er es, Splinter Cell zu spielen und Netflix / Prime Video zu sehen.

Mehr von Tamal Das

Abonniere unseren Newsletter

Abonnieren Sie unseren Newsletter für technische Tipps, Rezensionen, kostenlose E-Books und exklusive Angebote!

Klicken Sie hier, um zu abonnieren