Excel-Datenmodell (Inhaltsverzeichnis)

  • Einführung in das Datenmodell in Excel
  • Wie erstelle ich ein Datenmodell in Excel?

Einführung in das Datenmodell in Excel

Die Datenmodellfunktion von Excel ermöglicht den einfachen Aufbau von Beziehungen zwischen einfachen Berichten und ihren Hintergrunddatensätzen. Dies erleichtert die Datenanalyse erheblich. Es ermöglicht die Integration von Daten aus einer Vielzahl von Tabellen, die auf mehrere Arbeitsblätter verteilt sind, indem einfach Beziehungen zwischen übereinstimmenden Spalten aufgebaut werden. Es funktioniert vollständig hinter den Kulissen und vereinfacht Berichtsfunktionen wie PivotTable usw. erheblich.

In unserem Artikel versuchen wir zu zeigen, wie eine Pivot-Tabelle aus zwei Tabellen erstellt wird, indem das Datenmodell-Feature verwendet wird. Auf diese Weise wird eine Beziehung zwischen zwei Tabellenobjekten hergestellt und somit eine Pivot-Tabelle erstellt.

Wie erstelle ich ein Datenmodell in Excel?

Lassen Sie uns anhand einiger Beispiele verstehen, wie das Datenmodell in Excel erstellt wird.

Sie können diese Excel-Vorlage für Datenmodelle hier herunterladen - Excel-Vorlage für Datenmodelle

Beispiel 1

  • Wir haben eine Liste von Produkten und wir haben einen Regalcode für jedes Produkt. Wir brauchen eine Tabelle, in der wir die Regalbeschreibung zusammen mit den Regalcodes haben. Wie integrieren wir die Regalbeschreibungen in die einzelnen Regalcodes? Vielleicht würden viele von uns hier auf VLOOKUP zurückgreifen, aber wir werden die Notwendigkeit, VLOOKUP hier mit Excel Data Model zu verwenden, insgesamt beseitigen.

  • Die Tabelle links ist die Datentabelle und die Tabelle rechts ist die Nachschlagetabelle. Wie wir aus den Daten ersehen können, ist es möglich, eine Beziehung basierend auf gemeinsamen Spalten zu erstellen.

  • Das Datenmodell ist jetzt nur mit Tabellenobjekten kompatibel. Daher kann es manchmal erforderlich sein, Datensätze in Tabellenobjekte zu konvertieren. Führen Sie dazu die folgenden Schritte aus.
  1. Klicken Sie mit der linken Maustaste auf eine beliebige Stelle im Datensatz.
  2. Klicken Sie auf die Registerkarte Einfügen und navigieren Sie zu Tabelle in der Gruppe Tabellen, oder drücken Sie einfach Strg + T.
  3. Deaktivieren oder aktivieren Sie die Option "Meine Tabelle hat die Kopfzeile". In unserem Beispiel hat es tatsächlich einen Header. OK klicken.
  4. Während wir uns immer noch auf die neue Tabelle konzentrieren, müssen wir im Feld Name (links neben der Bearbeitungsleiste) einen aussagekräftigen Namen eingeben.

In unserem Beispiel haben wir die Tabelle Personal benannt.

  • Jetzt müssen wir den gleichen Vorgang auch für die Nachschlagetabelle ausführen und sie als Shelf Code bezeichnen.

Beziehung erstellen

Gehen Sie zunächst zur Registerkarte Daten und wählen Sie dann Beziehungen in der Untergruppe Datentools aus. Nachdem wir auf die Option Beziehungen geklickt haben, haben wir am Anfang, da keine Beziehung besteht, nichts.

Wir werden zuerst auf Neu klicken, um eine Beziehung zu erstellen. Wir müssen nun den primären und den Nachschlagetabellennamen aus der Dropdown-Liste bereitstellen und dann auch die Spalte erwähnen, die für die beiden Tabellen gemeinsam ist, damit wir die Beziehung zwischen den beiden Tabellen aus der Dropdown-Liste herstellen können von Spalten.

  • Die Primärtabelle ist nun die Tabelle mit den Daten. Es ist die primäre Datentabelle - Tabelle5. Andererseits ist die Related-Tabelle die Tabelle mit den Suchdaten - es ist unsere Suchtabelle ShelfCodesTable. Die Primärtabelle wird anhand der Nachschlagetabelle analysiert, die Nachschlagedaten enthält, die die gemeldeten Daten am Ende aussagekräftiger machen.

  • Die gemeinsame Spalte zwischen den beiden Tabellen ist also die Spalte Regalcode. Auf diese Weise haben wir die Beziehung zwischen den beiden Tabellen hergestellt. In Bezug auf die Spalten bezieht sich die Spalte (fremd) auf die Datentabelle, in der es doppelte Werte geben kann. Andererseits bezieht sich die verwandte Spalte (primäre Spalte) auf die Spalte in der Nachschlagetabelle, in der wir eindeutige Werte haben. Wir richten das Feld einfach so ein, dass Werte aus der Nachschlagetabelle in der Datentabelle gesucht werden.
  • Sobald wir dies eingerichtet haben, würde Excel eine Beziehung zwischen den beiden hinter den Kulissen erstellen. Es integriert die Daten und erstellt ein Datenmodell basierend auf der gemeinsamen Spalte. Dies schont nicht nur den Speicherbedarf, sondern ist auch viel schneller als die Verwendung von VLOOKUP in großen Arbeitsmappen. Nach dem Definieren des Datenmodells behandelt Excel diese Objekte als Datenmodelltabellen anstelle einer Arbeitsblatttabelle.
  • Um zu sehen, was Excel bisher gemacht hat, klicken Sie unter Daten -> Datentools auf Datenmodelle verwalten.

  • Durch Ändern der Ansicht können wir auch die Diagrammdarstellung des Datenmodells erhalten. Wir werden auf die Option Anzeigen klicken. Dies öffnet die Ansichtsoptionen. Wir werden dann die Diagrammansicht auswählen. Dann sehen wir die schematische Darstellung, die die beiden Tabellen und die Beziehung zwischen ihnen zeigt, dh die gemeinsame Spalte - Shelf Code.

  • Das obige Diagramm zeigt eine Eins-zu-Viele-Beziehung zwischen den eindeutigen Nachschlagetabellenwerten und der Datentabelle mit doppelten Werten.
  • Jetzt müssen wir eine Pivot-Tabelle erstellen. Dazu gehen wir zur Registerkarte Einfügen und klicken dann auf die Option Pivot Table.

Im Dialogfeld "Pivot-Tabelle erstellen" der Pivot-Tabelle wird die Quelle als "Datenmodell dieser Arbeitsmappe verwenden" ausgewählt.

  • Dadurch wird die Pivot-Tabelle erstellt und es wird angezeigt, dass beide Quelltabellen im Quellabschnitt verfügbar sind.

  • Jetzt erstellen wir eine Pivot-Tabelle, in der die Anzahl der Personen aufgeführt ist, die Artikel in den Regalen abgelegt haben.

  • In Tabelle 5 (Datentabelle) wird im Abschnitt Zeilen die Option Personal ausgewählt, gefolgt von Beschreibung (Nachschlagetabelle).

  • Nun ziehen wir den Regalcode aus Tabelle 5 in den Abschnitt Werte.

  • Jetzt werden wir Monate aus Tabelle 5 zum Abschnitt Zeilen hinzufügen.

  • Oder wir können die Monate als Filter hinzufügen und sie dem Abschnitt Filter hinzufügen.

Beispiel # 2

  • Wir haben jetzt Herrn Basu, der eine Fabrik namens Basu Corporation betreibt. Herr Basu versucht, die Einnahmen für 2019 auf der Grundlage der Daten von 2018 zu schätzen.
  • Wir haben eine Tabelle, in der wir die Einnahmen für 2018 und die nachfolgenden Einnahmen auf verschiedenen inkrementellen Ebenen haben.

  • Wir haben also den Umsatz für 2018 - 1, 5 Mio. USD und das erwartete Mindestwachstum für das folgende Jahr beträgt 12%. Herr Basu möchte eine Tabelle, die die Einnahmen auf verschiedenen inkrementellen Ebenen anzeigt.
  • Wir werden die folgende Tabelle für die Projektionen auf verschiedenen inkrementellen Ebenen für 2019 erstellen.

  • Jetzt geben wir der ersten Einnahmenreihe einen Verweis auf die geschätzten Mindesteinnahmen für 2019, dh 1, 68 Mio. USD.

  • Nach Verwendung der Formel wird die Antwort unten angezeigt.

  • Nun wählen wir die gesamte Tabelle aus, dh D2: E12, und gehen dann zu Daten -> Prognose -> Was-wäre-wenn-Analyse -> Datentabelle.

  • Dadurch wird das Dialogfeld Datentabelle geöffnet. Hier geben wir den Mindestschrittprozentsatz von Zelle B4 in die Zelle Spalteneingabe ein. Der Grund dafür ist, dass unsere projizierten geschätzten Wachstumsprozentsätze in der Tabelle in Spalten angeordnet sind.

  • Sobald Sie auf OK klicken, wird die Tabelle von der Was-wäre-wenn-Analyse automatisch mit den prognostizierten Einnahmen in verschiedenen inkrementellen Prozentsätzen gefüllt.

Beispiel # 3

  • Nehmen wir nun an, wir haben dasselbe Szenario wie oben, außer dass wir jetzt auch eine andere Achse zu betrachten haben. Angenommen, wir zeigen nicht nur die prognostizierten Einnahmen für 2019 auf der Grundlage der Daten für 2018 und der erwarteten Mindestwachstumsrate, sondern verfügen jetzt auch über den geschätzten Abzinsungssatz.

  • Zunächst haben wir eine Tabelle unten gezeigt.

  • Wir verweisen nun auf die voraussichtlichen Mindesteinnahmen für 2019, dh Zelle B5 bis Zelle D8.

  • Nun wählen wir die gesamte Tabelle aus, dh D8: J18, und gehen dann zu Daten -> Prognose -> Was-wäre-wenn-Analyse -> Datentabelle.

  • Dadurch wird das Dialogfeld Datentabelle geöffnet. Hier geben wir den Mindestschrittprozentsatz von Zelle B3 in die Zelle Spalteneingabe ein. Der Grund dafür ist, dass unsere projizierten geschätzten Wachstumsprozentsätze in der Tabelle in Spalten angeordnet sind. In der Zelle Zeileneingabe geben wir nun zusätzlich den Mindestrabatt in Prozent ab Zelle B4 ein. Der Grund dafür ist, dass unsere projizierten Rabattprozentsätze in der Tabelle zeilenweise angeordnet sind.

  • OK klicken. Dadurch wird bei der Was-wäre-wenn-Analyse die Tabelle automatisch mit den prognostizierten Einnahmen zu den verschiedenen inkrementellen Prozentsätzen gemäß den Rabattprozentsätzen gefüllt.

Wichtige Informationen zum Datenmodell in Excel

  • Nach erfolgreicher Berechnung der Werte aus der Datentabelle funktioniert ein einfaches Rückgängigmachen, dh Strg + Z, nicht. Es ist jedoch möglich, die Werte manuell aus der Tabelle zu löschen.
  • Es ist nicht möglich, eine einzelne Zelle aus der Tabelle zu löschen. Es wird intern in Excel als Array beschrieben, daher müssen alle Werte gelöscht werden.
  • Wir müssen die Zeileneingabezelle und die Spalteneingabezelle richtig auswählen.
  • Die Datentabelle muss im Gegensatz zur Pivot-Tabelle nicht jedes Mal aktualisiert werden.
  • Mit dem Datenmodell in Excel können wir nicht nur die Leistung verbessern, sondern auch den Speicherbedarf in großen Arbeitsblättern verringern.
  • Datenmodelle vereinfachen unsere Analyse im Vergleich zur Verwendung einer Reihe komplizierter Formeln in der gesamten Arbeitsmappe.

Empfohlene Artikel

Dies ist eine Anleitung zum Datenmodell in Excel. Hier erläutern wir, wie Sie ein Datenmodell in Excel erstellen, zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage. Sie können auch unsere anderen Artikelvorschläge durchgehen -

  1. Formelleiste in Excel
  2. Drucken Sie Gitternetzlinien in Excel
  3. Beobachtungsfenster in Excel
  4. Excel SUMIFS mit Datumsangaben

Kategorie:

Excel-Tipps