Datenmodell in Excel

Was ist das Datenmodell in Excel?

Das Datenmodell in Excel ist eine Art Datentabelle, bei der zwei oder mehr als zwei Tabellen über eine gemeinsame oder mehrere Datenreihen miteinander in Beziehung stehen. In Datenmodelltabellen werden Daten aus verschiedenen anderen Blättern oder Quellen zu einer eindeutigen Tabelle zusammengefasst Tabelle, die Zugriff auf die Daten aller Tabellen haben kann.

Erläuterung

  • Es ermöglicht die Integration von Daten aus mehreren Tabellen, indem Beziehungen basierend auf einer gemeinsamen Spalte erstellt werden.
  • Datenmodelle werden transparent verwendet und stellen tabellarische Daten bereit, die in Pivot Table in Excel und Pivot Charts in Excel verwendet werden können. Es integriert die Tabellen und ermöglicht eine umfassende Analyse mithilfe von Pivot-Tabellen, Power Pivot und Power View in Excel.
  • Das Datenmodell ermöglicht das Laden von Daten in den Speicher von Excel.
  • Es wird im Speicher gespeichert, wo wir es nicht direkt sehen können. Anschließend kann Excel angewiesen werden, Daten mithilfe einer gemeinsamen Spalte miteinander in Beziehung zu setzen. Der Teil 'Modell' des Datenmodells bezieht sich darauf, wie alle Tabellen zueinander in Beziehung stehen.
  • Das Datenmodell kann auf alle benötigten Informationen zugreifen, auch wenn sich die Informationen in mehreren Tabellen befinden. Nach der Erstellung des Datenmodells verfügt Excel über die verfügbaren Daten in seinem Speicher. Mit den Daten in seinem Speicher kann auf viele Arten auf die Daten zugegriffen werden.

Beispiele

Sie können diese Datenmodell-Excel-Vorlage hier herunterladen - Datenmodell-Excel-Vorlage

Beispiel 1

Wenn wir drei Datensätze haben, die sich auf den Verkäufer beziehen: Erstens mit Umsatzinformationen, zweitens mit Einnahmen des Verkäufers und drittens mit Ausgaben des Verkäufers.

Um diese drei Datensätze zu verbinden und eine Beziehung zu diesen herzustellen, erstellen wir ein Datenmodell mit den folgenden Schritten:

  • Konvertieren Sie die Datasets in Tabellenobjekte:

Wir können keine Beziehung zu normalen Datensätzen herstellen. Das Datenmodell funktioniert nur mit Excel-Tabellenobjekten. Um dies zu tun:

  • Schritt 1 - Klicken Sie auf eine beliebige Stelle im Dataset, dann auf die Registerkarte "Einfügen" und dann in der Gruppe "Tabellen" auf "Tabelle".

  • Schritt 2 - Aktivieren oder deaktivieren Sie die Option "Meine Tabelle enthält Überschriften" und klicken Sie auf "OK".

  • Schritt 3 - Geben Sie bei ausgewählter neuer Tabelle den Namen der Tabelle unter "Tabellenname" in der Gruppe "Extras" ein.

  • Schritt 4 - Jetzt können wir sehen, dass der erste Datensatz in das Objekt 'Tabelle' konvertiert wird. Wenn Sie diese Schritte für die beiden anderen Datensätze wiederholen, sehen Sie, dass sie auch wie folgt in 'Tabellen'-Objekte konvertiert werden:

Hinzufügen der 'Table'-Objekte zum Datenmodell: Über Verbindungen oder Beziehungen.

Über Verbindungen

  • Wählen Sie eine Tabelle aus, klicken Sie auf die Registerkarte "Daten" und dann auf "Verbindungen".

  • Im daraufhin angezeigten Dialogfeld wird das Symbol "Hinzufügen" angezeigt. Erweitern Sie die Dropdown-Liste "Hinzufügen" und klicken Sie auf "Zum Datenmodell hinzufügen".

  • Klicken Sie im daraufhin angezeigten Dialogfeld auf "Tabellen", wählen Sie eine der Tabellen aus und klicken Sie auf "Öffnen".

Auf diese Weise wird ein Arbeitsmappen-Datenmodell mit einer Tabelle erstellt und ein Dialogfeld wird wie folgt angezeigt:

Wenn wir diese Schritte also auch für die beiden anderen Tabellen wiederholen, enthält das Datenmodell jetzt alle drei Tabellen.

Wir können jetzt sehen, dass alle drei Tabellen in den Arbeitsmappenverbindungen angezeigt werden.

Über Beziehungen

Erstellen der Beziehung: Sobald beide Datasets Tabellenobjekte sind, können wir eine Beziehung zwischen ihnen erstellen. Um dies zu tun:

  • Klicken Sie auf die Registerkarte "Daten" und dann auf "Beziehungen".

  • Wir sehen ein leeres Dialogfeld, da keine aktuellen Verbindungen bestehen.

  • Klicken Sie auf "Neu" und ein weiteres Dialogfeld wird angezeigt.

  • Erweitern Sie die Dropdown-Listen "Tabelle" und "Verwandte Tabelle": In einem Dialogfeld "Beziehung erstellen" werden die Tabellen und Spalten ausgewählt, die für eine Beziehung verwendet werden sollen. Wählen Sie in der Erweiterung "Tabellen" das Dataset aus, das wir auf irgendeine Weise analysieren möchten, und wählen Sie in "Verwandte Tabelle" das Dataset mit Nachschlagewerten aus.
  • Die Nachschlagetabelle in Excel ist die kleinere Tabelle bei einer bis mehreren Beziehungen und enthält keine wiederholten Werte in der gemeinsamen Spalte. Wählen Sie in der Erweiterung 'Spalte (Fremd)' die gemeinsame Spalte in der Haupttabelle aus. Wählen Sie unter 'Verwandte Spalte (Primär)' die gemeinsame Spalte in der zugehörigen Tabelle aus.

  • Wenn alle vier Einstellungen ausgewählt sind, klicken Sie auf "OK". Ein Dialogfeld wird wie folgt angezeigt, wenn Sie auf "OK" klicken.

Wenn wir diese Schritte wiederholen, um zwei weitere Tabellen in Beziehung zu setzen: Ertragstabelle mit Aufwandsentabelle, werden sie auch im Datenmodell wie folgt verknüpft:

Excel erstellt jetzt die Beziehung hinter den Kulissen, indem Daten im Datenmodell basierend auf einer gemeinsamen Spalte kombiniert werden: Verkäufer-ID (in diesem Fall).

Beispiel 2

Angenommen, im obigen Beispiel möchten wir eine Pivot-Tabelle erstellen, die die Tabellenobjekte auswertet oder analysiert:

  • Klicken Sie auf "Einfügen" -> "Pivot-Tabelle".

  • Klicken Sie im daraufhin angezeigten Dialogfeld auf die Option "Externe Datenquelle verwenden" und dann auf "Verbindung auswählen".

  • Klicken Sie im daraufhin angezeigten Dialogfeld auf "Tabellen", wählen Sie das Arbeitsmappendatenmodell mit drei Tabellen aus und klicken Sie auf "Öffnen".

  • Wählen Sie am Speicherort die Option "Neues Arbeitsblatt" und klicken Sie auf "OK".

  • Im Bereich Pivot-Tabellenfelder werden Tabellenobjekte angezeigt.

  • Jetzt können Änderungen in der Pivot-Tabelle entsprechend vorgenommen werden, um die Tabellenobjekte nach Bedarf zu analysieren.

Wenn wir in diesem Fall beispielsweise den Gesamtumsatz oder den Umsatz für einen bestimmten Verkäufer ermitteln möchten, wird eine Pivot-Tabelle wie folgt erstellt:

Dies ist bei einem Modell / einer Tabelle mit einer großen Anzahl von Beobachtungen von immenser Hilfe.

Wir können also sehen, dass Pivot Table das Datenmodell (Auswahl durch Auswahl der Verbindung) im Excel-Speicher sofort verwendet, um Beziehungen zwischen Tabellen anzuzeigen.

Dinge, an die man sich erinnern sollte

  • Mit dem Datenmodell können wir Daten aus mehreren Tabellen gleichzeitig analysieren.
  • Durch das Erstellen von Beziehungen mit dem Datenmodell müssen wir nicht mehr die Formeln VLOOKUP, SUMIF, INDEX und MATCH verwenden, da nicht alle Spalten in einer einzigen Tabelle abgerufen werden müssen.
  • Wenn Datasets aus externen Quellen in Excel importiert werden, werden Modelle implizit erstellt.
  • Tabellenbeziehungen können automatisch erstellt werden, wenn verwandte Tabellen mit Primär- und Fremdschlüsselbeziehungen importiert werden.
  • Beim Erstellen von Beziehungen sollten die Spalten, die wir in Tabellen verbinden, denselben Datentyp haben.
  • Mit den mit dem Datenmodell erstellten Pivot-Tabellen können wir auch Slicer hinzufügen und die Pivot-Tabellen in jedes gewünschte Feld aufteilen.
  • Der Vorteil des Datenmodells gegenüber den Funktionen von LOOKUP () besteht darin, dass es wesentlich weniger Speicher benötigt.
  • Excel 2013 unterstützt nur eine zu eins oder eine zu viele Beziehungen, dh eine der Tabellen darf keine doppelten Werte in der Spalte enthalten, mit der wir verknüpfen.