Excel Power Pivot - Verwalten des Datenmodells
Die Hauptanwendung von Power Pivot ist die Fähigkeit, die Datentabellen und die Beziehungen zwischen ihnen zu verwalten, um die Analyse der Daten aus mehreren Tabellen zu erleichtern. Sie können dem Datenmodell eine Excel-Tabelle hinzufügen, während Sie eine PivotTable erstellen, oder direkt über das PowerPivot-Menüband.
Sie können Daten aus mehreren Tabellen nur analysieren, wenn Beziehungen zwischen ihnen bestehen. Mit Power Pivot können Sie Beziehungen in der Datenansicht oder Diagrammansicht erstellen. Wenn Sie dem Power Pivot eine Tabelle hinzugefügt haben, müssen Sie außerdem eine Beziehung hinzufügen.
Hinzufügen von Excel-Tabellen zum Datenmodell mit PivotTable
Wenn Sie eine PivotTable in Excel erstellen, basiert diese nur auf einer einzelnen Tabelle / einem einzelnen Bereich. Wenn Sie der PivotTable weitere Tabellen hinzufügen möchten, können Sie dies mit dem Datenmodell tun.
Angenommen, Sie haben zwei Arbeitsblätter in Ihrer Arbeitsmappe -
Eine, die die Daten von Verkäufern und die Regionen, die sie repräsentieren, in einer Tabelle enthält - Verkäufer.
Eine andere enthält die Daten zu Verkäufen nach Regionen und Monaten in einer Tabelle - Verkäufe.
Sie können die Verkäufe - verkäuferspezifisch - wie unten angegeben zusammenfassen.
Klicken Sie auf die Tabelle - Vertrieb.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte EINFÜGEN.
Wählen Sie PivotTable in der Gruppe Tabellen aus.
Eine leere PivotTable mit den Feldern aus der Verkaufstabelle - Region, Monat und Bestellbetrag wird erstellt. Wie Sie sehen können, gibt es eineMORE TABLES Befehl unter der Liste PivotTable-Felder.
Klicken Sie auf MEHR TABELLEN.
Das Create a New PivotTableMeldungsfeld wird angezeigt. Die angezeigte Meldung lautet: Um mehrere Tabellen in Ihrer Analyse zu verwenden, muss eine neue PivotTable mithilfe des Datenmodells erstellt werden. Klicken Sie auf Ja
Eine neue PivotTable wird wie unten gezeigt erstellt -
Unter PivotTable-Felder können Sie feststellen, dass es zwei Registerkarten gibt - ACTIVE und ALL.
Klicken Sie auf die Registerkarte ALL.
Zwei Tabellen - Sales und Salesperson - mit den entsprechenden Feldern werden in der Liste PivotTable Fields angezeigt.
Klicken Sie in der Tabelle Verkäufer auf das Feld Verkäufer und ziehen Sie es in den Bereich REIHEN.
Klicken Sie in der Verkaufstabelle auf das Feld Monat und ziehen Sie es in den Bereich REIHEN.
Klicken Sie in der Verkaufstabelle auf das Feld Bestellbetrag und ziehen Sie es in den Bereich ∑ WERTE.
Die PivotTable wird erstellt. In den PivotTable-Feldern wird eine Meldung angezeigt -Relationships between tables may be needed.
Klicken Sie neben der Nachricht auf die Schaltfläche ERSTELLEN. DasCreate Relationship Das Dialogfeld wird angezeigt.
Unter TableWählen Sie Vertrieb.
Unter Column (Foreign) Wählen Sie im Feld Region aus.
Unter Related TableWählen Sie Verkäufer.
Unter Related Column (Primary) Wählen Sie im Feld Region aus.
OK klicken.
Ihre PivotTable aus den beiden Tabellen auf zwei Arbeitsblättern ist fertig.
Wie Excel beim Hinzufügen der zweiten Tabelle zur PivotTable angegeben hat, wurde die PivotTable mit dem Datenmodell erstellt. Gehen Sie zur Überprüfung wie folgt vor:
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte POWERPIVOT.
Klicken Managein der Gruppe Datenmodell. Die Datenansicht des Power Pivot wird angezeigt.
Sie können beobachten, dass die beiden Excel-Tabellen, die Sie beim Erstellen der PivotTable verwendet haben, in Datentabellen im Datenmodell konvertiert werden.
Hinzufügen von Excel-Tabellen aus einer anderen Arbeitsmappe zum Datenmodell
Angenommen, die beiden Tabellen - Verkäufer und Vertrieb - befinden sich in zwei verschiedenen Arbeitsmappen.
Sie können die Excel-Tabelle aus einer anderen Arbeitsmappe wie folgt zum Datenmodell hinzufügen:
Klicken Sie auf die Verkaufstabelle.
Klicken Sie auf die Registerkarte EINFÜGEN.
Klicken Sie in der Gruppe Tabellen auf PivotTable. DasCreate PivotTable Das Dialogfeld wird angezeigt.
Geben Sie im Feld Tabelle / Bereich den Wert Vertrieb ein.
Klicken Sie auf Neues Arbeitsblatt.
Aktivieren Sie das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen.
OK klicken.
Sie erhalten eine leere PivotTable in einem neuen Arbeitsblatt mit nur den Feldern, die der Verkaufstabelle entsprechen.
Sie haben die Daten der Verkaufstabelle zum Datenmodell hinzugefügt. Als Nächstes müssen Sie die Salesperson-Tabellendaten auch wie folgt in das Datenmodell übernehmen:
Klicken Sie auf das Arbeitsblatt mit der Verkaufstabelle.
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.
Unter This Workbook Data Model, 1 tablewird angezeigt (Dies ist die Verkaufstabelle, die Sie zuvor hinzugefügt haben). Sie finden auch die beiden Arbeitsmappen, in denen die Tabellen angezeigt werden.
Klicken Sie unter Salesperson.xlsx auf Salesperson.
Klicken Sie auf Öffnen. DasImport Data Das Dialogfeld wird angezeigt.
Klicken Sie auf PivotTable-Bericht.
Klicken Sie auf Neues Arbeitsblatt.
Sie können sehen, dass die Box - Add this data to the Data Modelist geprüft und inaktiv. OK klicken.
Die PivotTable wird erstellt.
Wie Sie sehen können, befinden sich die beiden Tabellen im Datenmodell. Möglicherweise müssen Sie wie im vorherigen Abschnitt eine Beziehung zwischen den beiden Tabellen erstellen.
Hinzufügen von Excel-Tabellen zum Datenmodell über das PowerPivot-Menüband
Eine andere Möglichkeit, Excel-Tabellen zum Datenmodell hinzuzufügen, besteht darin, dies zu tun so from the PowerPivot Ribbon.
Angenommen, Sie haben zwei Arbeitsblätter in Ihrer Arbeitsmappe -
Eine, die die Daten von Verkäufern und die Regionen, die sie repräsentieren, in einer Tabelle enthält - Verkäufer.
Eine andere enthält die Daten zu Verkäufen nach Regionen und Monaten in einer Tabelle - Verkäufe.
Sie können diese Excel-Tabellen zuerst zum Datenmodell hinzufügen, bevor Sie eine Analyse durchführen.
Klicken Sie auf die Excel-Tabelle - Vertrieb.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte POWERPIVOT.
Klicken Sie in der Gruppe Tabellen auf Zum Datenmodell hinzufügen.
Das Power Pivot-Fenster wird mit der Datentabelle Salesperson hinzugefügt. Außerdem wird auf der Multifunktionsleiste im Power Pivot-Fenster eine Registerkarte - Verknüpfte Tabelle angezeigt.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte Verknüpfte Tabelle.
Klicken Sie auf Excel-Tabelle: Verkäufer.
Sie können feststellen, dass die Namen der beiden in Ihrer Arbeitsmappe vorhandenen Tabellen angezeigt werden und der Name Verkäufer aktiviert ist. Dies bedeutet, dass die Datentabelle Verkäufer mit der Excel-Tabelle Verkäufer verknüpft ist.
Klicken Go to Excel Table.
Das Excel-Fenster mit dem Arbeitsblatt mit der Verkäufertabelle wird angezeigt.
Klicken Sie auf die Registerkarte Sales-Arbeitsblatt.
Klicken Sie auf die Verkaufstabelle.
Klicken Sie in der Gruppe Tabellen auf der Multifunktionsleiste auf Zum Datenmodell hinzufügen.
Die Excel-Tabelle Sales wird ebenfalls zum Datenmodell hinzugefügt.
Wenn Sie, wie Sie wissen, eine Analyse basierend auf diesen beiden Tabellen durchführen möchten, müssen Sie eine Beziehung zwischen den beiden Datentabellen erstellen. In Power Pivot können Sie dies auf zwei Arten tun:
Aus der Datenansicht
Aus der Diagrammansicht
Erstellen von Beziehungen aus der Datenansicht
Wie Sie wissen, können Sie in der Datenansicht die Datentabellen mit Datensätzen als Zeilen und Felder als Spalten anzeigen.
Klicken Sie im Power Pivot-Fenster auf die Registerkarte Design.
Klicken Sie in der Gruppe Beziehungen auf Beziehung erstellen. DasCreate Relationship Das Dialogfeld wird angezeigt.
Klicken Sie im Feld Tabelle auf Verkauf. Dies ist die Tabelle, von der aus die Beziehung beginnt. Wie Sie wissen, sollte Spalte das Feld sein, das in der zugehörigen Tabelle Verkäufer vorhanden ist und eindeutige Werte enthält.
Klicken Sie im Feld Spalte auf Region.
Klicken Sie im Feld "Verwandte verknüpfte Tabelle" auf "Verkäufer".
Die zugehörige verknüpfte Spalte wird automatisch mit Region gefüllt.
Klicken Sie auf die Schaltfläche Erstellen. Die Beziehung wird erstellt.
Erstellen von Beziehungen aus der Diagrammansicht
Das Erstellen von Beziehungen aus der Diagrammansicht ist relativ einfach. Befolgen Sie die angegebenen Schritte.
Klicken Sie im Power Pivot-Fenster auf die Registerkarte Home.
Klicken Sie in der Gruppe Ansicht auf Diagrammansicht.
Die Diagrammansicht des Datenmodells wird im Power Pivot-Fenster angezeigt.
Klicken Sie in der Verkaufstabelle auf Region. Region in der Verkaufstabelle ist hervorgehoben.
Ziehen Sie in der Tabelle "Verkäufer" in die Region. Region in der Verkäufertabelle wird ebenfalls hervorgehoben. In der Richtung, in die Sie gezogen haben, wird eine Linie angezeigt.
Von der Tabelle Sales wird eine Zeile zur Tabelle Salesperson angezeigt, in der die Beziehung angegeben ist.
Wie Sie sehen können, wird eine Zeile von der Verkaufstabelle zur Verkäufertabelle angezeigt, in der die Beziehung und die Richtung angegeben sind.
Wenn Sie das Feld kennen möchten, das Teil einer Beziehung ist, klicken Sie auf die Beziehungszeile. Die Linie und das Feld in beiden Tabellen sind hervorgehoben.
Beziehungen verwalten
Sie können eine vorhandene Beziehung im Datenmodell bearbeiten oder löschen.
Klicken Sie im Power Pivot-Fenster auf die Registerkarte Design.
Klicken Sie in der Gruppe Beziehungen auf Beziehungen verwalten. Das Dialogfeld Beziehungen verwalten wird angezeigt.
Alle im Datenmodell vorhandenen Beziehungen werden angezeigt.
Eine Beziehung bearbeiten
Klicken Sie auf eine Beziehung.
Drücke den EditTaste. DasEdit Relationship Das Dialogfeld wird angezeigt.
Nehmen Sie die erforderlichen Änderungen in der Beziehung vor.
OK klicken. Die Änderungen spiegeln sich in der Beziehung wider.
Eine Beziehung löschen
Klicken Sie auf eine Beziehung.
Klicken Sie auf die Schaltfläche Löschen. Eine Warnmeldung zeigt an, wie sich die Tabellen, die vom Löschen der Beziehung betroffen sind, auf die Berichte auswirken würden.
Klicken Sie auf OK, wenn Sie sicher sind, dass Sie löschen möchten. Die ausgewählte Beziehung wird gelöscht.
Aktualisieren der Power Pivot-Daten
Angenommen, Sie ändern die Daten in der Excel-Tabelle. Sie können die Daten in der Excel-Tabelle hinzufügen / ändern / löschen.
Gehen Sie wie folgt vor, um die PowerPivot-Daten zu aktualisieren:
Klicken Sie im Power Pivot-Fenster auf die Registerkarte Verknüpfte Tabelle.
Klicken Sie auf Alle aktualisieren.
Die Datentabelle wird mit den in der Excel-Tabelle vorgenommenen Änderungen aktualisiert.
Wie Sie sehen können, können Sie Daten in den Datentabellen nicht direkt ändern. Daher ist es besser, Ihre Daten in Excel-Tabellen zu verwalten, die mit den Datentabellen verknüpft sind, wenn Sie sie dem Datenmodell hinzufügen. Dies erleichtert das Aktualisieren der Daten in Datentabellen, wenn Sie die Daten in Excel-Tabellen aktualisieren.