Erweiterte Datenanalyse - Datenmodell

Das Datenmodell ist in Excel 2013 und späteren Versionen verfügbar. In Excel können Sie das Datenmodell verwenden, um Daten aus mehreren Tabellen in die aktuelle Arbeitsmappe und / oder aus den importierten Daten und / oder aus den Datenquellen zu integrieren, die über Datenverbindungen mit der Arbeitsmappe verbunden sind.

Mit einem Datenmodell können Sie Beziehungen zwischen den Tabellen erstellen. Das Datenmodell wird transparent in PivotTable-, PivotChart-, PowerPivot- und Power View-Berichten verwendet.

Erstellen eines Datenmodells beim Importieren von Daten

Wenn Sie Daten aus relationalen Datenbanken wie der Microsoft Access-Datenbank importieren, die mehrere verwandte Tabellen enthalten, wird das Datenmodell automatisch erstellt, wenn Sie mehrere Tabellen gleichzeitig importieren.

Sie können dem Datenmodell optional Tabellen hinzufügen, wenn Sie Daten aus den folgenden Datenquellen importieren:

Relationale Datenbanken, jeweils eine Tabelle
Textdateien
Excel-Arbeitsmappen

Während Sie beispielsweise Daten aus einer Excel-Arbeitsmappe importieren, können Sie die Option beobachten Add this data to the Data Modelmit aktiviertem Kontrollkästchen.

Wenn Sie die Daten, die Sie importieren, zum Datenmodell hinzufügen möchten, aktivieren Sie das Kontrollkästchen.

Datenmodell aus Excel-Tabellen erstellen

Sie können das Datenmodell mithilfe von PowerPivot-Befehlen aus Excel-Tabellen erstellen. Sie werden PowerPivot in späteren Kapiteln ausführlich kennenlernen.

Alle Datenmodellbefehle sind auf der Multifunktionsleiste auf der Registerkarte PowerPivot verfügbar. Mit diesen Befehlen können Sie dem Datenmodell Excel-Tabellen hinzufügen.

Betrachten Sie die folgende Arbeitsmappe mit Verkaufsdaten, in der Sie über ein Arbeitsblatt für den Produktkatalog verfügen, das Produkt, Produkt-ID und Preis enthält. Sie haben vier Arbeitsblätter für den Verkauf in 4 Regionen - Ost, Nord, Süd und West.

Jedes dieser vier Arbeitsblätter enthält die Anzahl der verkauften Einheiten und den Gesamtbetrag für jedes Produkt in jedem Monat. Sie müssen den Gesamtbetrag für jedes Produkt in jeder Region und den Gesamtumsatz in jeder Region berechnen.

Mit den folgenden Schritten gelangen Sie zu den gewünschten Ergebnissen:

Beginnen Sie mit der Erstellung des Datenmodells.
Klicken Sie in das Arbeitsblatt Produktkatalog.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte POWERPIVOT.
Klicken Sie auf Zum Datenmodell hinzufügen. Das Dialogfeld Tabelle erstellen wird angezeigt.
Wählen Sie den Tabellenbereich aus.
Aktivieren Sie das Kontrollkästchen Meine Tabelle enthält Überschriften. OK klicken.

Ein neues Fenster - PowerPivot für Excel - <Ihr Excel-Dateiname> wird angezeigt.

Die folgende Meldung wird in der Mitte des leeren Fensters angezeigt:

Die Tabelle Product Backlog, die Sie dem Datenmodell hinzugefügt haben, wird im PowerPivot-Fenster als Blatt angezeigt. Jede Zeile in der Tabelle ist ein Datensatz, und Sie können die Datensätze mit den Pfeiltasten links und rechts am unteren Rand des Fensters hin und her bewegen.

Klicken Sie im PowerPivot-Fenster auf die Registerkarte Verknüpfte Tabelle.
Klicken Sie auf Zur Excel-Tabelle wechseln.

Das Excel-Datenfenster wird angezeigt.

Klicken Sie auf die Registerkarte Arbeitsblatt - Ost.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte POWERPIVOT.
Klicken Sie auf Zum Datenmodell hinzufügen.

Im PowerPivot-Fenster wird ein weiteres Blatt mit der Osttabelle angezeigt.

Wiederholen Sie diesen Vorgang für die Arbeitsblätter - Nord, Süd und West. Insgesamt haben Sie dem Datenmodell fünf Tabellen hinzugefügt. Ihr PowerPivot-Fenster sieht wie folgt aus:

Erstellen von Beziehungen zwischen Tabellen

Wenn Sie Berechnungen über die Tabellen hinweg durchführen möchten, müssen Sie zuerst Beziehungen zwischen ihnen definieren.

Klicken Sie im Menüband im PowerPivot-Fenster auf die Registerkarte Startseite. Wie Sie sehen können, werden die Tabellen in der Datenansicht angezeigt.
Klicken Sie auf Diagrammansicht.

Die Tabellen werden in der Diagrammansicht angezeigt. Wie Sie sehen, befinden sich einige Tabellen möglicherweise außerhalb des Anzeigebereichs und alle Felder in den Tabellen sind möglicherweise nicht sichtbar.

Ändern Sie die Größe jeder Tabelle, um alle Felder in dieser Tabelle anzuzeigen.
Ziehen Sie die Tabellen und ordnen Sie sie so an, dass alle angezeigt werden.

Klicken Sie in der Tabelle Ost auf Produkt-ID.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte Design.
Klicken Sie auf Beziehung erstellen. Das Dialogfeld Beziehung erstellen wird angezeigt.

Im Feld unter Tabelle wird Ost angezeigt. Im Feld unter Spalte wird die Produkt-ID angezeigt.

Wählen Sie im Feld unter Verwandte Nachschlagetabelle die Option Produktkatalog aus.
Die Produkt-ID wird im Feld unter "Verwandte Suchspalte" angezeigt.
Klicken Sie auf die Schaltfläche Erstellen.

Die Linie, die die Beziehung zwischen den Tabellen Ost und Product Backlog darstellt, wird angezeigt.

Wiederholen Sie die gleichen Schritte für die Tabellen - Nord, Süd und West. Beziehungslinien werden angezeigt.

Zusammenfassen der Daten in den Tabellen im Datenmodell

Jetzt können Sie die Verkaufsdaten für jedes Produkt in jeder Region in nur wenigen Schritten zusammenfassen.

Klicken Sie auf die Registerkarte Home.
Klicken Sie auf PivotTable.
Wählen Sie PivotTable aus der Dropdown-Liste.

Das Dialogfeld "PivotTable erstellen" wird im Fenster "Excel-Tabellen" angezeigt. Wählen Sie Neues Arbeitsblatt.

In einem neuen Arbeitsblatt wird eine leere PivotTable angezeigt. Wie Sie sehen können, enthält die Feldliste alle Tabellen im Datenmodell, wobei alle Felder angezeigt werden.

Wählen Sie die Produkt-ID aus Tabelle 1 (Produktkatalog).
Wählen Sie Gesamtbetrag aus den anderen vier Tabellen.
Ändern Sie für jedes Feld in ∑ Werte den benutzerdefinierten Namen in den Wertefeldeinstellungen, um die Regionsnamen als Spaltenbezeichnungen anzuzeigen.

Die Summe des Gesamtbetrags wird durch das von Ihnen angegebene Etikett ersetzt. PivotTable mit zusammengefassten Werten aus allen Datentabellen zeigt Ihnen die erforderlichen Ergebnisse.

Hinzufügen von Daten zum Datenmodell

Sie können dem Datenmodell eine neue Datentabelle oder den vorhandenen Tabellen im Datenmodell neue Datenzeilen hinzufügen.

Fügen Sie dem Datenmodell mit den folgenden Schritten eine neue Datentabelle hinzu.

Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.
Klicken Sie in der Gruppe Externe Daten abrufen auf Vorhandene Verbindungen. Das Dialogfeld Bestehende Verbindungen wird angezeigt.
Klicken Sie auf die Registerkarte Tabellen. Die Namen aller Tabellen in der Arbeitsmappe werden angezeigt.
Klicken Sie auf den Namen der Tabelle, die Sie dem Datenmodell hinzufügen möchten.

Klicken Sie auf die Schaltfläche Öffnen. Das Dialogfeld Daten importieren wird angezeigt.

Wie Sie wissen, wird die Datentabelle beim Importieren automatisch zum Datenmodell hinzugefügt. Die neu hinzugefügte Tabelle wird im PowerPivot-Fenster angezeigt.

Fügen Sie den vorhandenen Tabellen im Datenmodell neue Datenzeilen hinzu.

Aktualisieren Sie die Datenverbindung. Neue Datenzeilen aus der Datenquelle werden dem Datenmodell hinzugefügt.