Lernprogramm: PivotTable-Datenanalyse mithilfe eines Datenmodells in Excel 2013

In weniger als einer Stunde können Sie einen PivotTable-Bericht in Excel erstellen, in dem Daten aus mehreren Tabellen zusammengeführt sind. Der erste Teil dieses Lernprogramms führt Sie durch den Datenimport und die Datensuche. In der zweiten Hälfte verfeinern Sie das Datenmodell, auf dem der Bericht basiert, mithilfe des Power Pivot-Add-Ins. Dabei lernen Sie, wie Sie Berechnungen, Hierarchien und Optimierungen für die Power View-Berichterstellung hinzufügen.

Importieren von Daten

Bevor Sie eine PivotTable erstellen können, benötigen Sie Daten. Rufen Sie dazu einige Umsatzdaten aus der Beispieldatenbank ab, die Sie heruntergeladen haben.

  1. Laden Sie Beispieldaten (ContosoV2) für dieses Lernprogramm herunter. Weitere Informationen finden Sie unter Abrufen von Beispieldaten für Datenmodell- und DAX-Lernprogramme. Extrahieren und speichern Sie die Datendateien an einem leicht zugänglichen Ort, z. B. unter "Downloads" oder "Eigene Dokumente".

  2. Öffnen Sie in Excel eine leere Arbeitsmappe.

  3. Klicken Sie auf Daten > Externe Daten abrufen > Aus Access.

  4. Wechseln Sie zum Ordner mit den Beispieldatendateien, und wählen Sie ContosoSales aus.

  5. Klicken Sie auf Öffnen. Da Sie eine Verbindung mit einer Datenbankdatei herstellen, die mehrere Tabellen enthält, wird das Dialogfeld Tabelle auswählen angezeigt, damit Sie die zu importierenden Tabellen auswählen können.

    Dialogfeld 'Tabelle auswählen'

  6. Aktivieren Sie in "Tabelle auswählen" die Option Auswahl mehrerer Tabellen aktivieren.

  7. Wählen Sie alle Tabellen aus, und klicken Sie auf OK.

  8. Klicken Sie auf "Daten importieren", auf PivotTable-Bericht und auf OK.

Was ist geschehen?

Vielleicht haben Sie es noch nicht bemerkt, aber Sie haben gerade ein Datenmodell erstellt. Es wird automatisch erstellt, wenn Sie mehrere Tabellen gleichzeitig in demselben PivotTable-Bericht importieren oder damit arbeiten. Das Modell ist in Excel meist nicht zu sehen, Sie können es aber mit dem Power Pivot-Add-In direkt anzeigen und bearbeiten. In Excel ist das Vorhandensein eines Datenmodells daran zu erkennen, dass eine Sammlung von Tabellen in der PivotTable-Feldliste angezeigt wird. Es gibt mehrere Möglichkeiten zum Erstellen eines Modells. Ausführliche Informationen finden Sie unter Erstellen eines Datenmodells in Excel.

Durchsuchen von Daten mithilfe einer PivotTable

Sie können die Daten auf einfache Weise durchsuchen, indem Sie Felder in die Bereiche Werte, Spalten und Zeilen in der PivotTable-Feldliste ziehen.

  1. Scrollen Sie in der Feldliste nach unten bis zur Tabelle "FactSales".

  2. Klicken Sie auf "SalesAmount". Da diese Daten numerisch sind, fügt Excel "SalesAmount" automatisch in den Bereich "Werte" ein.

  3. Ziehen Sie "CalendarYear" in "DimDate" auf "Spalten".

  4. Ziehen Sie "ProductSubcategoryName" in "DimProductSubcategory" auf "Zeilen".

  5. Ziehen Sie "BrandName" in "DimProduct" auf "Zeilen", und legen Sie das Element unterhalb der Unterkategorie ab.

Die PivotTable sollte der folgenden Abbildung ähneln.

PivotTable mit Beispieldaten

Sie haben nun mit minimalem Aufwand eine einfache PivotTable erstellt, die Felder aus vier verschiedenen Tabellen umfasst. Diese Aufgabe war aufgrund der bereits vorhandenen Beziehungen zwischen den Tabellen so einfach. Da Tabellenbeziehungen in der Quelle vorhanden waren und Sie alle Tabellen in einem einzigen Schritt importiert haben, konnten diese Beziehungen von Excel im Modell wiederhergestellt werden.

Doch was ist, wenn die Daten aus verschiedenen Quellen stammen oder zu einem späteren Zeitpunkt importiert werden? Normalerweise beziehen Sie neue Daten ein, indem Sie Beziehungen auf Grundlage übereinstimmender Spalten erstellen. Im nächsten Schritt importieren Sie weitere Tabellen und lernen dabei die Anforderungen und Schritte zum Erstellen neuer Beziehungen kennen.

Hinzufügen weiterer Tabellen

Um das Einrichten von Tabellenbeziehungen zu erlernen, benötigen Sie weitere, nicht miteinander verbundene Tabellen, mit denen Sie arbeiten können. In diesem Schritt werden die restlichen Daten für dieses Lernprogramm bereitgestellt, indem Sie eine zusätzliche Datenbankdatei importieren und Daten aus zwei weiteren Arbeitsmappen einfügen.

Hinzufügen von Produktkategorien

  1. Öffnen Sie in der Arbeitsmappe ein neues Arbeitsblatt. In diesem Blatt werden die zusätzlichen Daten gespeichert.

  2. Klicken Sie auf Daten > Externe Daten abrufen > Aus Access.

  3. Wechseln Sie zum Ordner mit den Beispieldatendateien, und wählen Sie "ProductCategories" aus. Klicken Sie auf Öffnen.

  4. Wählen Sie in "Daten importieren" die Option Tabelle aus, und klicken Sie auf OK.

Hinzufügen geografischer Daten

  1. Fügen Sie ein weiteres Arbeitsblatt ein.

  2. Öffnen Sie in den Beispieldatendateien "Geography.xlsx", setzen Sie den Cursor in A1, und drücken Sie dann STRG-UMSCHALT-ENDE, um alle Daten auszuwählen.

  3. Kopieren Sie die Daten in die Zwischenablage.

  4. Fügen Sie die Daten in das gerade hinzugefügte, leere Arbeitsblatt ein.

  5. Klicken Sie auf Als Tabelle formatieren, und wählen Sie ein beliebiges Format aus. Beim Formatieren der Daten als Tabelle können Sie ihr auch einen Namen zuweisen. Das wird sich als nützlich erweisen, wenn Sie in einem späteren Schritt Beziehungen definieren.

  6. Überprüfen Sie unter "Als Tabelle formatieren", ob Meine Tabelle hat Überschriften ausgewählt ist. Klicken Sie auf OK.

  7. Geben Sie der Tabelle den Namen "Geography". Geben Sie unter Tabellentools > Entwurf im Feld "Tabellenname" den Namen "Geography" ein.

  8. Schließen Sie "Geography.xlsx", um die Datei aus dem Arbeitsbereich zu entfernen.

Hinzufügen von Filialdaten

  • Wiederholen Sie die vorherigen Schritte für die Datei "Stores.xlsx", indem Sie den Inhalt in ein leeres Arbeitsblatt einfügen. Geben Sie der Tabelle den Namen "Stores".

Sie sollten jetzt über vier Arbeitsblätter verfügen. Tabelle1 enthält die PivotTable, Tabelle2 enthält ProductCategories, Tabelle3 enthält "Geography" und Tabelle4 enthält "Stores". Da Sie sich die Zeit genommen haben, jede Tabelle zu benennen, ist das Erstellen von Beziehungen im nächsten Schritt wesentlich einfacher.

Verwenden von Feldern aus den neu importierten Tabellen

Sie können sofort mit der Verwendung von Feldern aus den soeben importierten Tabellen beginnen. Wenn Excel nicht bestimmen kann, wie ein Feld in einen PivotTable-Bericht einzubeziehen ist, werden Sie gebeten, eine Tabellenbeziehung zu erstellen, durch die die neue Tabelle einer Tabelle zugeordnet wird, die bereits Teil des Modells ist.

  1. Klicken Sie oben in "PivotTable-Felder" auf Alle, um die vollständige Liste der verfügbaren Tabellen anzuzeigen.

  2. Scrollen Sie zum Ende der Liste. Dort finden Sie die neuen Tabellen, die Sie gerade hinzugefügt haben.

  3. Erweitern Sie "Stores".

  4. Ziehen Sie "StoreName" in den Bereich "Filter".

  5. Sie werden von Excel aufgefordert, eine Beziehung zu erstellen. Diese Benachrichtigung erfolgt, da Sie Felder aus einer Tabelle verwendet haben, die nicht mit dem Modell verknüpft ist.

  6. Klicken Sie auf Erstellen, um das Dialogfeld "Beziehung erstellen" zu öffnen.

  7. Wählen Sie in "Tabelle" den Namen "FactSales" aus. In den verwendeten Beispieldaten enthält "FactSales" detaillierte Umsatz- und Kostendaten des Unternehmens Contoso sowie Schlüssel zu weiteren Tabellen, darunter Filialcodes, die ebenfalls in der im vorherigen Schritt importierten Datei "Stores.xlsx" gespeichert sind.

  8. Wählen Sie in "Spalte (fremd)" den Eintrag "StoreKey" aus.

  9. Wählen Sie in "Verwandte Tabelle" den Eintrag "Stores" aus.

  10. Wählen Sie in "Verwandte Spalte (primär)" den Eintrag "StoreKey" aus.

  11. Klicken Sie auf OK.

Im Hintergrund erstellt Excel ein Datenmodell, das überall in der Arbeitsmappe für eine beliebige Anzahl von PivotTables, PivotCharts oder Power View-Berichten verwendet werden kann. Dieses Modell basiert auf Tabellenbeziehungen, die die Navigations- und Berechnungspfade bestimmen, die in einem PivotTable-Bericht verwendet werden. In der nächsten Aufgabe erstellen Sie Beziehungen manuell, um die gerade importierten Daten zu verbinden.

Hinzufügen von Beziehungen

Sie können Tabellenbeziehungen systematisch für alle neuen Tabellen erstellen, die Sie importieren. Wenn Sie die Arbeitsmappe gemeinsam mit Kollegen nutzen, empfiehlt sich die Verwendung vordefinierter Beziehungen, falls Ihre Kollegen die Daten nicht so gut kennen wie Sie.

Beim manuellen Erstellen von Beziehungen arbeiten Sie immer jeweils mit zwei Tabellen. Für jede Tabelle wählen Sie Spalten aus, die Excel Aufschluss darüber geben, wie nach zugehörigen Zeilen in einer anderen Tabelle gesucht werden soll.

Ihr Browser unterstützt kein Video. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.

Erstellen einer Beziehung zwischen "ProductSubcategory" und "ProductCategory"

  1. Klicken Sie in Excel auf Daten > Beziehungen > Neu.

  2. Wählen Sie in "Tabelle" den Eintrag "DimProductSubcategory" aus.

  3. Wählen Sie in "Spalte (fremd)" den Eintrag "ProductCategoryKey" aus.

  4. Wählen Sie in "Verwandte Tabelle" den Eintrag "Table_ProductCategory.accdb" aus.

  5. Wählen Sie in "Verwandte Spalte (primär)" den Eintrag "ProductCategoryKey" aus.

  6. Klicken Sie auf OK.

  7. Schließen Sie das Dialogfeld Beziehungen verwalten.

Hinzufügen von Kategorien zur PivotTable

Obwohl das Datenmodell aktualisiert wurde, um weitere Tabellen und Beziehungen aufzunehmen, werden diese noch nicht von der PivotTable verwendet. In dieser Aufgabe fügen Sie der PivotTable-Feldliste das Feld "ProductCategory" hinzu.

  1. Klicken Sie in "PivotTable-Felder" auf Alle, um die im Datenmodell vorhandenen Tabellen anzuzeigen.

  2. Scrollen Sie zum Ende der Liste.

  3. Entfernen Sie "BrandName" aus dem Bereich "Zeilen".

  4. Erweitern Sie "Table_DimProductCategories.accdb".

  5. Ziehen Sie "ProductCategoryName" in den Bereich "Zeilen", und legen Sie das Element oberhalb von "ProductSubcategory" ab.

  6. Klicken Sie in "PivotTable-Felder" auf Aktiv, um zu überprüfen, ob die PivotTable die gerade verwendeten Tabellen aktiv verwendet.

Prüfpunkt: Überprüfen der erworbenen Kenntnisse

Sie haben jetzt eine PivotTable erstellt, die Daten aus mehreren Tabellen enthält, von denen einige in einem nachfolgenden Schritt importiert wurden. Damit diese Daten zusammengeführt werden, müssen Sie Tabellenbeziehungen erstellen, die von Excel zum Korrelieren der Zeilen verwendet werden. Sie haben gelernt, dass die Suche nach verwandten Zeilen wesentlich davon abhängt, dass Spalten mit passenden Daten verfügbar sind. In den Beispieldatendateien enthalten alle Tabellen eine Spalte, die zu diesem Zweck verwendet werden kann.

Obwohl die PivotTable nun funktioniert, haben Sie möglicherweise einige Verbesserungsvorschläge. Die Liste "PivotTable-Felder" scheint zusätzliche Tabellen (DimEntity) und Spalten (ETLLoadID) zu enthalten, die keinen Bezug zu Contoso haben. Außerdem wurden noch keine geografischen Daten eingebunden.

Nächster Schritt: Anzeigen und Erweitern des Modells mit Power Pivot

In den nächsten Aufgaben verwenden Sie das Microsoft Office Power Pivot in Microsoft Excel 2013-Add-In, um das Modell zu erweitern. Sie werden feststellen, dass sich Beziehungen leichter mithilfe der Diagrammsicht erstellen lassen, die vom Add-In bereitgestellt wird. Sie verwenden das Add-In außerdem zum Erstellen von Berechnungen und Hierarchien, Ausblenden von Elementen, die nicht in der Feldliste angezeigt werden sollen, und Optimieren von Daten für zusätzliche Berichtszwecke.

Hinweis :  Das Power Pivot in Microsoft Excel 2013-Add-In ist in Office Professional Plus verfügbar. Weitere Informationen finden Sie unter Power Pivot in Microsoft Excel 2013-Add-In.

Fügen Sie Power Pivot zum Excel-Menüband hinzu, indem Sie das Power Pivot-Add-In aktivieren.

  1. Wechseln Sie zu Datei > Optionen > Add-Ins.

  2. Klicken Sie im Feld Verwalten auf COM-Add-Ins > Gehe zu.

  3. Aktivieren Sie das Kontrollkästchen Microsoft Office Power Pivot in Microsoft Excel 2013, und klicken Sie dann auf OK.

Das Menüband enthält nun eine Registerkarte für Power Pivot.

Hinzufügen einer Beziehung mithilfe der Diagrammsicht in Power Pivot

  1. Klicken Sie in Excel auf "Tabelle3", um das Blatt zum aktiven Arbeitsblatt zu machen. Tabelle3 enthält die zuvor importierte Tabelle "Geography".

  2. Klicken Sie im Menüband auf Power Pivot > Zu Datenmodell hinzufügen. Mit diesem Schritt wird die Tabelle "Geography" zum Modell hinzugefügt. Außerdem wird das Power Pivot-Add-In geöffnet, mit dem Sie die übrigen Schritte dieser Ausgabe ausführen.

  3. Das Power Pivot-Fenster zeigt alle Tabellen im Modell, einschließlich "Geography". Klicken Sie sich durch ein paar Tabellen. Im Add-In können Sie alle im Modell enthaltenen Daten anzeigen.

  4. Klicken Sie im Power Pivot-Fenster im Abschnitt "Ansicht" auf Diagrammsicht.

  5. Ändern Sie die Diagrammgröße mithilfe der Schiebeleiste, damit alle im Diagramm enthaltenen Objekte angezeigt werden. Beachten Sie, dass zwei Tabellen nicht mit dem Diagramm verbunden sind: "DimEntity" und "Geography".

  6. Klicken Sie mit der rechten Maustaste auf "DimEntity", und klicken Sie dann auf Löschen. Diese Tabelle ist ein Artefakt aus der ursprünglichen Datenbank und wird im Modell nicht gebraucht.

  7. Vergrößern Sie "Geography", damit alle Felder angezeigt werden. Sie können das Tabellendiagramm mithilfe des Schiebereglers vergrößern.

  8. Die Tabelle "Geography" umfasst die Spalte "GeographyKey". Diese Spalte enthält Werte, die jede Zeile in der Tabelle "Geography" eindeutig kennzeichnen. Finden Sie heraus, ob dieser Schlüssel auch bei anderen Tabellen im Modell verwendet wird. Wenn das der Fall ist, können Sie eine Beziehung erstellen, durch die die Tabelle "Geography" mit dem restlichen Modell verbunden wird.

  9. Klicken Sie auf Suchen.

  10. Geben Sie in "Metadaten suchen" den Begriff "GeographyKey" ein.

  11. Klicken Sie mehrere Male auf Weitersuchen. Sie werden feststellen, dass "GeographyKey" sowohl in der Tabelle "Geography" als auch in der Tabelle "Stores" angezeigt wird.

  12. Ändern Sie die Position der Tabelle "Geography", sodass sie sich neben "Stores" befindet.

  13. Ziehen Sie die Spalte "GeographyKey" in "Stores" auf die Spalte "GeographyKey" in "Geography". Power Pivot zeichnet eine Linie zwischen den beiden Spalten und zeigt so eine bestehende Beziehung an.

In dieser Aufgabe haben Sie eine neue Technik zum Hinzufügen von Tabellen und Erstellen von Beziehungen kennengelernt. Sie verfügen jetzt über ein vollständig integriertes Modell, in dem alle Tabellen verbunden und für die PivotTable in Tabelle1 verfügbar sind.

Tipp :  In der Diagrammsicht sind einige Tabellendiagramme vollständig erweitert und zeigen Spalten wie "ETLLoadID", "LoadDate" und "UpdateDate". Bei diesen Feldern handelt es sich um Artefakte aus dem ursprünglichen Contoso Data Warehouse, die zur Unterstützung von Datenextraktions- und Ladevorgängen hinzugefügt wurden. Sie werden im Modell nicht benötigt. Um sie zu entfernen, markieren Sie das Feld, klicken Sie mit der rechten Maustaste darauf, und klicken Sie dann auf Löschen.

Erstellen einer berechneten Spalte

In Power Pivot können Sie Data Analysis Expressions (DAX) zum Hinzufügen von Berechnungen verwenden. In dieser Aufgabe berechnen Sie den Gesamtgewinn und fügen eine berechnete Spalte hinzu, die auf Datenwerte aus anderen Tabellen verweist. Später erfahren Sie, wie Sie das Modell mithilfe von Spalten, auf die verwiesen wird, einfacher gestalten.

  1. Wechseln Sie im Power Pivot-Fenster zurück zur Datensicht.

  2. Geben Sie der Tabelle "Table_ProductCategories accdb" einen leichter verständlichen Namen. In den folgenden Schritten verweisen Sie auf diese Tabelle, und bei Verwendung eines kürzeren Namens sind die Berechnungen leichter zu lesen. Klicken Sie mit der rechten Maustaste auf den Tabellennamen, klicken Sie auf Umbenennen, geben Sie "ProductCategories" ein, und drücken Sie dann die EINGABETASTE.

  3. Wählen Sie die Tabelle "FactSales" aus.

  4. Klicken Sie auf Entwurf > Spalten > Hinzufügen.

  5. Geben Sie in der Bearbeitungsleiste über der Tabelle die folgende Formel ein. AutoVervollständigen hilft Ihnen bei der Eingabe der vollqualifizierten Namen von Spalten und Tabellen und listet die verfügbaren Funktionen auf. Sie können auch einfach auf die Spalte klicken, und Power Pivot fügt den Spaltennamen zur Formel hinzu.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Nachdem Sie die Formel erstellt haben, drücken Sie die EINGABETASTE, um die Formel zu akzeptieren.

    Werte werden für alle Zeilen in der berechneten Spalte eingetragen. Wenn Sie in der Tabelle nach unten scrollen, sehen Sie, dass Zeilen für diese Spalte je nach den Daten in jeder Zeile unterschiedliche Werte haben können.

  7. Benennen Sie die Spalte um, indem Sie mit der rechten Maustaste auf "BerechneteSpalte1" klicken und dann Spalte umbenennen auswählen. Geben Sie "Profit" ein, und drücken Sie dann die EINGABETASTE.

  8. Wählen Sie jetzt die Tabelle "DimProduct" aus.

  9. Klicken Sie auf Entwurf > Spalten > Hinzufügen.

  10. Geben Sie in der Bearbeitungsleiste über der Tabelle die folgende Formel ein.

    = RELATED(ProductCategories[ProductCategoryName])

    Mit der RELATED-Funktion wird ein Wert aus einer verknüpften Tabelle zurückgegeben. In diesem Fall enthält die Tabelle "ProductCategories" die Namen von Produktkategorien, die in der Tabelle "DimProduct" von Nutzen sind, wenn Sie eine Hierarchie erstellen, die Kategorieinformationen umfasst. Weitere Informationen zu dieser Funktion finden Sie unter RELATED-Funktion (DAX).

  11. Nachdem Sie die Formel erstellt haben, drücken Sie die EINGABETASTE, um die Formel zu akzeptieren.

    Werte werden für alle Zeilen in der berechneten Spalte eingetragen. Wenn Sie in der Tabelle nach unten scrollen, sehen Sie, dass jetzt jede Zeile einen Produktkategorienamen aufweist.

  12. Benennen Sie die Spalte um, indem Sie mit der rechten Maustaste auf "BerechneteSpalte1" klicken und dann Spalte umbenennen auswählen. Geben Sie "ProductCategory" ein, und drücken Sie dann die EINGABETASTE.

  13. Klicken Sie auf Entwurf > Spalten > Hinzufügen.

  14. Geben Sie in der Bearbeitungsleiste über der Tabelle die folgende Formel ein, und drücken Sie dann die EINGABETASTE, um die Formel zu akzeptieren.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Benennen Sie die Spalte um, indem Sie mit der rechten Maustaste auf "BerechneteSpalte1" klicken und dann Spalte umbenennen auswählen. Geben Sie "ProductSubcategory" ein, und drücken Sie dann die EINGABETASTE.

Erstellen einer Hierarchie

Die meisten Modelle enthalten Daten, die von sich aus hierarchisch sind. Häufige Beispiele sind Kalenderdaten, geografische Daten und Produktkategorien. Das Erstellen von Hierarchien ist nützlich, weil Sie ein Element (die Hierarchie) auf einen Bericht ziehen können, anstatt dieselben Felder immer wieder zusammenzustellen und anzuordnen.

  1. Wechseln Sie in Power Pivot zur Diagrammsicht. Erweitern Sie die Tabelle "DimDate", um alle Felder übersichtlich anzuzeigen.

  2. Halten Sie STRG gedrückt, und klicken Sie auf die Spalten "CalendarYear", "CalendarQuarter" und "CalendarMonth" (Sie zum Tabellenende scrollen).

  3. Wenn die drei Spalten markiert sind, klicken Sie mit der rechten Maustaste auf eine davon, und klicken Sie dann auf Hierarchie erstellen. Am Ende der Tabelle wird der übergeordnete Hierarchieknoten "Hierarchy 1" erstellt, und die ausgewählten Spalten werden als untergeordnete Knoten unter die Hierarchie kopiert.

  4. Geben Sie "Dates" als Namen für die neue Hierarchie ein.

  5. Fügen Sie die Spalte "FullDateLabel" zur Hierarchie hinzu. Klicken Sie mit der rechten Maustaste auf "FullDateLabel", und wählen Sie Zur Hierarchie hinzufügen aus. Wählen Sie "Datum" aus. "FullDateLabel" enthält ein vollständiges Datum, einschließlich Jahr, Monat und Tag. Stellen Sie sicher, dass "FullDateLabel" in der Hierarchie zuletzt angezeigt wird. Sie verfügen nun über eine Hierarchie mit mehreren Ebenen, die das Jahr, das Quartal, den Monat und einzelne Kalendertage umfasst.

  6. Zeigen Sie in der Diagrammsicht auf die Tabelle "DimProduct", und klicken Sie auf die Schaltfläche Hierarchie erstellen im Tabellenkopf. Ein leerer übergeordneter Hierarchieknoten wird am Ende der Tabelle angezeigt.

  7. Geben Sie "Product Categories" als Namen für die neue Hierarchie ein.

  8. Um untergeordnete Hierarchieknoten zu erstellen, ziehen Sie die Spalten "ProductCategory" und "ProductSubcategory" auf die Hierarchie.

  9. Klicken Sie mit der rechten Maustaste auf "ProductName", und wählen Sie Zur Hierarchie hinzufügen aus. Wählen Sie "Product Categories" aus.

Sie haben nun verschiedene Möglichkeiten kennengelernt, wie Sie eine Hierarchie erstellen. Als Nächstes wenden Sie dieses Wissen auf die PivotTable an.

  1. Kehren Sie zu Excel zurück.

  2. Entfernen Sie in "Tabelle1" (das Arbeitsblatt mit der PivotTable) die Felder aus dem Bereich "Zeilen".

  3. Ersetzen Sie sie in "DimProduct" durch die neue Hierarchie "Product Categories".

  4. Ersetzen Sie "CalendarYear" auf ähnliche Weise im Bereich "Spalten" durch die Hierarchie "Dates" in "DimDate".

Wenn Sie die Daten jetzt durchsuchen, werden Sie auf Anhieb die Vorteile von Hierarchien erkennen. Sie können die verschiedenen Bereiche der PivotTable unabhängig voneinander erweitern und schließen und so die Ausnutzung des verfügbaren Raums präziser steuern. Indem Sie sowohl dem Bereich "Zeilen" als auch dem Bereich "Spalten" eine einzelne Hierarchie hinzufügen, erhalten Sie außerdem sofort wertvolle Drilldowninformationen, ohne mehrere Felder stapeln zu müssen.

Ausblenden von Spalten

Nachdem Sie jetzt die Hierarchie "Product Categories" erstellt und in "DimProduct" abgelegt haben, wird "DimProductCategory" bzw. "DimProductSubcategory" nicht mehr in der PivotTable-Feldliste benötigt. In dieser Aufgabe erfahren Sie, wie Sie fremde Tabellen und Spalten ausblenden, die zu viel Platz in der PivotTable-Feldliste belegen. Indem Sie die Tabellen und Spalten ausblenden, vereinfachen Sie die Arbeit im Bericht, ohne dass sich dies auf das zugrunde liegende Modell mit Datenbeziehungen und Berechnungen auswirkt.

Ihr Browser unterstützt kein Video. Installieren Sie Microsoft Silverlight, Adobe Flash Player oder Internet Explorer 9.

Sie können einzelne Spalten, einen Spaltenbereich oder eine gesamte Tabelle ausblenden. Tabellen- und Spaltennamen sind abgeblendet, um anzuzeigen, dass sie für Berichtserstellungsclients, die das Modell nutzen, ausgeblendet sind. Ausgeblendete Spalten sind im Modell abgeblendet, um ihren Status anzuzeigen, bleiben aber in der Datensicht sichtbar, sodass Sie weiterhin damit arbeiten können.

  1. Stellen Sie in Power Pivot sicher, dass die Datensicht ausgewählt ist.

  2. Klicken Sie auf den Registerkarten unten mit der rechten Maustaste auf "DimProductSubcategory", und wählen Sie Aus Clienttools ausblenden aus.

  3. Wiederholen Sie diesen Schritt für "ProductCategories".

  4. Öffnen Sie "DimProduct".

  5. Klicken Sie mit der rechten Maustaste auf die folgenden Spalten, und klicken Sie dann auf Aus Clienttools ausblenden:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Wählen Sie mehrere angrenzende Spalten aus. Beginnen Sie mit "ClassID", und schließen Sie die Auswahl mit "ProductSubcategory" am Ende ab. Klicken Sie mit der rechten Maustaste, um die Spalten auszublenden.

  7. Wiederholen Sie diesen Schritt für andere Tabellen, um IDs, Schlüssel oder andere Details auszublenden, die Sie in diesem Bericht nicht verwenden.

Wechseln Sie in Excel wieder zu Tabelle1 mit der PivotTable-Feldliste, um den Unterschied zu sehen. Die Anzahl der Tabellen ist reduziert, und "DimProduct" enthält nur diejenigen Elemente, die bei der Analyse von Umsatzzahlen wahrscheinlicher zum Einsatz kommen.

Erstellen eines Power View-Berichts

PivotTable-Berichte sind nicht die einzigen Berichtstypen, die von einem Datenmodell profitiert. Sie können das soeben erstellte Modell auch verwenden, um ein Power View-Blatt hinzufügen und einige der darin enthaltenen Layouts auszuprobieren.

  1. Klicken Sie in Excel auf Einfügen > Power View.

    Hinweis :  Wenn Sie Power View zum ersten Mal auf diesem Computer verwenden, werden Sie aufgefordert, zuerst das Add-In zu aktivieren und Silverlight zu installieren.

  2. Klicken Sie im Bereich "Power View-Felder" auf den Pfeil neben der Tabelle "FactSales", und klicken Sie auf "SalesAmount".

  3. Erweitern Sie die Tabelle "Geography", und klicken Sie auf "RegionCountryName".

  4. Klicken Sie im Menüband auf Karte.

  5. Ein Kartenbericht wird angezeigt. Ziehen Sie eine Ecke, um die Berichtsgröße zu ändern. Blaue Kreise unterschiedlicher Größe zeigen auf der Karte die Umsatzleistung für unterschiedliche Länder oder Regionen an.

Optimieren der Power View-Berichterstellung

Durch einige kleine Änderungen am Modell, kann das Entwerfen eines Power View-Berichts intuitiver gestaltet werden. In dieser Aufgabe fügen Sie Website-URLs für mehrere Hersteller hinzu, und dann kategorisieren Sie diese Daten als Web-URL, damit die URL-Adresse in Form eines Links angezeigt wird.

Zuerst fügen Sie der Arbeitsmappe URLs hinzu.

  1. Öffnen Sie in Excel ein neues Arbeitsblatt, und kopieren Sie die folgenden Werte:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Formatieren Sie die Zellen als Tabelle, und geben Sie der Tabelle dann den Namen "URL".

  2. Erstellen Sie eine Beziehung zwischen "URL" und der Tabelle, die Herstellernamen enthält ("DimProduct").

    1. Klicken Sie auf Daten > Beziehungen. Das Dialogfeld "Beziehung erstellen" wird angezeigt.

    2. Klicken Sie auf Neu.

    3. Wählen Sie unter "Tabelle" den Eintrag "DimProduct" aus.

    4. Wählen Sie unter "Spalte" den Eintrag "Manufacturer" aus.

    5. Wählen Sie unter "Verwandte Tabelle" den Eintrag "URL" aus.

    6. Wählen Sie unter "Verwandte Spalte (primär)" den Eintrag "ManufacturerID" aus.

Um die Vorher/Nachher-Ergebnisse zu vergleichen, starten Sie einen neuen Power View-Bericht, und fügen Sie "FactSales | SalesAmount", "dimProduct | Manufacturer" und "URL | ManufacturerURL" zu einem Bericht hinzu. Die URLs werden als statischer Text angezeigt.

Zur Darstellung einer URL als aktiver Link ist eine Kategorisierung erforderlich. Zum Kategorisieren einer Spalte verwenden Sie Power Pivot.

  1. Öffnen Sie "URL" in Power Pivot.

  2. Wählen Sie "ManufacturerURL" aus.

  3. Klicken Sie auf Erweitert > Berichterstellungseigenschaften >Datenkategorie: Nicht kategorisiert.

  4. Klicken Sie auf den nach unten weisenden Pfeil.

  5. Wählen Sie "Web-URL" aus.

  6. Klicken Sie in Excel auf Einfügen > Power View.

  7. Wählen Sie im Bereich "Power View-Felder" die Einträge "FactSales | SalesAmount", "dimProduct | Manufacturer" und "URL | ManufacturerURL" aus. Jetzt werden die URLs als wirkliche Links angezeigt.

Bei anderen Power View-Optimierungen können Sie u. a. einen Standardfeldsatz für die einzelnen Tabellen erstellen und Eigenschaften festlegen, durch die bestimmt wird, ob Zeilen mit sich wiederholenden Daten aggregiert oder unabhängig voneinander aufgelistet werden. Weitere Informationen finden Sie unter Konfigurieren eines Standardfeldsatzes für Power View-Berichte und Konfigurieren von Tabellenverhaltenseigenschaften für Power View-Berichte.

Erstellen berechneter Felder

In der zweiten Aufgabe zum Durchsuchen von Daten mithilfe einer PivotTable haben Sie in der Liste "PivotTable-Felder" auf das Feld "SalesAmount" geklickt. Da es sich bei "SalesAmount" um eine numerische Spalte handelt, wurde sie automatisch im Bereich "Werte" der PivotTable platziert. Mit "Summe von SalesAmount" konnten dann die Umsatzbeträge für die jeweils anzuwendenden Filter berechnet werden. In diesem Fall wurden zunächst keine Filter, doch dann "CalendarYear", "ProductSubcategoryName" und "BrandName" angewendet.

Tatsächlich haben Sie ein implizit berechnetes Feld erstellt, mit dem auf einfache Weise Umsatzbeträge aus der Tabelle "FactSales" anhand anderer Felder wie der Produktkategorie, Region oder Datumsangaben analysiert werden können. Implizit berechnete Felder werden von Excel erstellt, wenn Sie ein Feld in den Bereich "Werte" ziehen oder auf ein numerisches Feld klicken (wie es bei "SalesAmount" der Fall war). Implizit berechnete Felder sind Formeln, die standardmäßige Aggregationsfunktionen wie SUMME, ANZAHL und MITTELWERT verwenden, die automatisch für Sie erstellt werden.

Es gibt auch noch andere Arten berechneter Felder. Sie können explizit berechnete Felder in Power Pivot erstellen. Im Gegensatz zu implizit berechneten Feldern, die nur in der PivotTable verwendet werden können, in der sie erstellt wurden, können explizit berechnete Felder in jeder beliebigen PivotTable innerhalb der Arbeitsmappe oder von jedem Bericht verwendet werden, der das Datenmodell als Datenquelle verwendet. Bei explizit berechneten Feldern, die in Power Pivot erstellt wurden, können Sie "AutoSumme" verwenden, um berechnete Felder automatisch anhand von Standardaggregationen oder selber mithilfe einer Formel zu erstellen, die mit Data Analysis Expressions (DAX) erstellt wurde.

Das Erstellen berechneter Felder kann beim Analysieren der Daten auf umfassende und vielfältige Art nützlich sein, also werden Sie jetzt erfahren, wie Sie sie erstellen.

Berechnete Felder können in Power Pivot auf einfache Weise mit AutoSumme erstellt werden.

  1. Klicken Sie in der Tabelle FactSales auf die Spalte Profit.

  2. Klicken Sie auf Berechnungen > AutoSumme. Wie Sie sehen, wurde automatisch ein neues berechnetes Feld mit dem Namen Summe von Profit in der Zelle im Berechnungsbereich direkt unterhalb der Spalte Profit erstellt.

  3. Klicken Sie in Excel in "Tabelle1" in der Feldliste in FactSales auf Summe von Profit.

Das war's auch schon! Mehr ist nicht nötig, um ein berechnetes Feld anhand einer Standardaggregation in Power Pivot zu erstellen. Sie haben in nur wenigen Minuten ein berechnetes Feld "Summe von Profit" erstellt und zur PivotTable hinzugefügt. Dadurch können Sie auf einfache Weise die Gewinne je nach angewendeten Filtern analysieren. In diesem Fall wird "Summe von Profit" nach den Hierarchien "Product Category" und "Dates" gefiltert.

Doch was ist, wenn Sie eine detailliertere Analyse vornehmen müssen, wie die Anzahl der Umsätze für einen bestimmten Kanal, ein Produkt oder eine Kategorie? Dazu müssen Sie ein weiteres berechnetes Feld erstellen, mit dem die Anzahl der Zeilen – eine für jeden Umsatzwert in der Tabelle "FactSales" – je nach angewendeten Filtern gezählt wird.

  1. Klicken Sie in der Tabelle "FactSales" auf die Spalte SalesKey.

  2. Klicken Sie in der Gruppe Berechnungen auf den Pfeil nach unten für AutoSumme > Anzahl.

  3. Benennen Sie das neue berechnete Feld um, indem Sie auf Anzahl von SalesKey im Berechnungsbereich klicken, und ändern Sie dann in der Bearbeitungsleiste den Eintrag Anzahl von SalesKey in Count. Drücken Sie dann die EINGABETASTE. Im Gegensatz zu berechneten Spalten sind Namen für berechnete Felder in der DAX-Formel enthalten.

  4. Klicken Sie in Excel in "Tabelle1" in der Feldliste in FactSalesauf Count.

Wie Sie sehen, wird eine neue Spalte mit dem Namen Count zur PivotTable hinzugefügt, in der die Anzahl der Umsätze je nach angewendeten Filtern angezeigt wird. Wie beim berechneten Feld "Summe von Profit" wird auch "Count" nach den Hierarchien "Product Category" und "Dates" gefiltert.

Jetzt erstellen Sie noch ein weiteres berechnetes Feld, mit dem der Prozentsatz des Gesamtumsatzes für einen bestimmten Kontext oder Filter berechnet wird. Im Gegensatz zu den berechneten Feldern, die Sie zuvor mithilfe von "AutoSumme" erstellt haben, geben Sie diesmal manuell eine Formel ein.

  1. Klicken Sie in der Tabelle "FactSales" im Berechnungsbereich auf eine leere Zelle. Tipp: Die Zelle ganz oben links eignet sich hervorragend als Ausgangspunkt zum Einfügen berechneter Felder. Sie sind dann leichter zu finden. Sie können jedes berechnete Feld im Berechnungsbereich beliebig verschieben.

  2. Beginnen Sie mit der Eingabe in der Bearbeitungsleiste, und verwenden Sie IntelliSense zum Erstellen der folgenden Formel: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Drücken Sie die EINGABETASTE, um die Formel zu akzeptieren.

  4. Klicken Sie in Excel in "Tabelle1" in der Feldliste in FactSales auf Percentage of All Products.

  5. Treffen Sie in der PivotTable eine Mehrfachauswahl der Spalten Percentage of All Products.

  6. Klicken Sie auf der Registerkarte Start auf Zahl > Prozent. Verwenden Sie zwei Dezimalstellen als Formatierung für jede neue Spalte.

Mit diesem neuen berechneten Feld wird der Prozentsatz des Gesamtumsatzes für einen bestimmten Filterkontext berechnet. In diesem Fall handelt es sich beim Filterkontext noch immer um die Hierarchien "Product Category" und "Dates". Sie können beispielsweise sehen, dass Computer im Lauf der Jahre einen immer größeren prozentualen Anteil des Gesamtproduktumsatzes darstellen.

Das Erstellen von Formeln sowohl für berechnete Spalten als auch berechnete Felder ist recht einfach, wenn Sie bereits mit dem Erstellen von Excel-Formeln vertraut sind. Doch unabhängig davon können die Grundlagen von DAX-Formeln anhand der Lektionen in Schnellstart: Erlernen der DAX-Grundlagen in 30 Minuten auf einfache Weise erlernt werden.

Speichern Ihrer Arbeit

Speichern Sie die Arbeitsmappe, damit Sie sie für andere Lernprogramme verwenden oder noch weiter damit arbeiten können.

Nächste Schritte

Obwohl Sie Daten aus Excel leicht importieren können, ist es häufig schneller und effizienter, wenn Sie sie mithilfe des Power Pivot-Add-Ins importieren. Sie können die importierten Daten filtern, nicht benötigte Spalten ausschließen und auswählen, ob Sie die Daten mit dem Abfrage-Generator oder einem Abfragebefehl abrufen möchten. Im nächsten Schritt lernen Sie die folgenden alternativen Herangehensweisen kennen: Abrufen von Daten aus einem Datenfeed in Power Pivot und Importieren von Daten aus Analysis Services oder Power Pivot.

Die Power View-Berichterstellung ist für Datenmodelle konzipiert, die dem gerade erstellten Modell ähneln. Lesen Sie weiter, um mehr über die leistungsstarken Datenvisualisierungen zu erfahren, die Power View für Excel bereithält: Starten von Power View in Excel 2013 und Power View: Durchsuchen, Visualisieren und Präsentieren von Daten.

Um noch bessere Power View-Berichte erstellen zu können, optimieren Sie Ihr Datenmodell anhand der Schritte unter Lernprogramm: Optimieren des Datenmodells für die Power View-Berichterstellung

Teilen Facebook Facebook Twitter Twitter E-Mail E-Mail

War diese Information hilfreich?

Sehr gut. Noch anderes Feedback?

Was können wir verbessern?

Vielen Dank für Ihr Feedback!

×