Quickstart – eine erste Auswertung mit Power Pivot

    Power Pivot Anleitung

    Sie sind Anfänger in Excel Power Pivot? Keine Sorge, in diesem Artikel erklären wir Ihnen anhand eines Beispiels Schritt für Schritt, wie Sie eine erste Auswertung erstellen.

    Das Ziel

    Aus den Daten eines ERP-Systems haben wir alle Kundenaufträge zur Verfügung. Wir wissen, wann ein Kundenauftrag versandt wurde, wer den Kundenauftrag akquiriert hat, ob dieser online bestellt wurde und wie hoch die Nettosumme ist. Wir möchten darstellen:

    • 1. Wie viel Umsatz haben wir pro Jahr in welchem Monat gemacht?
    • 2. Wie viel Umsatz haben wir pro Jahr pro Verkäufer gemacht?
    • 3. Wie hat sich die Anzahl online und herkömmlich bestellter Aufträge entwickelt?

    Voraussetzung

    Voraussetzung ist natürlich, dass Sie Power Pivot installiert haben – das erkennen Sie leicht daran, dass Sie einen neuen Reiter in Ihrem Menü finden:

    image

    Wenn nicht, finden Sie eine kurze Installationsanleitung hier.

    Daten

    Damit wir bei dem Beispiel auf einer gemeinsamen Datenbasis arbeiten, finden Sie eine CSV-Datei (Textdatei mit Daten) hier. Es handelt sich um die Auftragsdaten (Auftragsköpfe) der Microsoft Beispieldatenbank AdventureWorks. Laden Sie die Datei herunter und legen diese auf Ihrer Festplatte ab – so, dass Sie sie wiederfinden.

    Öffnen Sie Excel und wechseln in das Power Pivot Fenster:

    image

    Wählen Sie im Power Pivot Fenster den Menüeintrag Aus Text:

    image

    Ändern Sie den Verbindungsname auf SalesOrderHeader und wählen Sie im Feld Dateipfad die csv-Datei aus, die Sie heruntergeladen haben. Als Spaltentrennzeichen wählen Sie Semikolon. Wählen Sie außerdem die Option Erste Zeile als Spaltenüberschriften verwenden. Ihr Dialog sollte anschließend so aussehen:

    image

    Sie können den Import verkleinern (etwas, was Sie immer tun sollten), indem Sie nur benötigte Spalten auswählen. In der Vorschau wählen Sie daher ganz oben links den Haken, der dafür sorgt, dass keine Spalte ausgewählt wird. Wählen Sie anschließend nur die Felder:

    • ShipDate
    • OnlineOrderFlag
    • SalesPersonID
    • SubTotal

    Bestätigen Sie die Auswahl mit Fertig stellen. Sie sollten die Daten anschließend in der Datensicht sehen.

    Daten ergänzen

    In Power Pivot kann man leicht Spalten mit berechneten Informationen ergänzen. Lesen Sie dazu auch einen entsprechenden Artikel. Wir berechnen aus dem ShipDate das Jahr, den Monat sowie eine Verbindung aus Jahr und Monat (bei diesem Wert wird noch eine Formatierung durchgeführt, damit der Monat immer zweistellig erscheint):

    image

    image

    image

     

    Diagramme einfügen

    Wählen Sie anschließend im Menü unter Home/PivotTable/PivotChart aus. Wählen Sie im Dialog Neues Arbeitsblatt. Dadurch springen Sie zurück in eine Excel, eine neue Arbeitsmappe wird angelegt und dort ein Diagramm ohne Daten eingefügt:

    image

    Umsatz pro Jahr

    Wählen Sie auf der rechten Seite in der PowerPivot Feldliste das Feld Jahr aus und ziehen es via Drag&Drop in den unteren Bereich Achsenfelder (Rubriken). Ziehen Sie anschließend das Feld SubTotal in den Bereich Werte. Sie erhalten ein Balkendiagramm mit den kumulierten Umsatzwerten pro Jahr:

    image

    Umsatz pro SalesPersonID

    Fügen Sie entweder im Excel-Menü unter PowerPivot oder im PowerPivot-Fenster erneut den Menüeintrag PivotTable aus und wählen dort erneut die Schaltfläche PivotChart. Legen Sie das Diagramm in der vorhandenen Arbeitsmappe an.

    Wählen Sie auf der rechten Seite in der PowerPivot-Feldliste das Feld SubTotal aus und ziehen Sie es via Drag&Drop in den Pivot-Bereich Werte. Ziehen Sie außerdem das Feld SalesPersonID in den Bereich Achsenfelder (Rubriken) und das Feld Jahr in den Bereich Slicer vertikal. Sie erhalten automatisch ein Balkendiagramm mit den kumulierten Umsätzen pro SalesPersonID:

    image

    Auf der linken Seite erhalten Sie einen Slicer. Hier können Sie die Jahre wählen, die Sie auswerten möchten. mit gedrückter STRG-Taste können Sie mehrere Jahre auswählen. Wenn Sie den Filter löschen wollen, wählen Sie das kleine Filter-löschen-Icon im oberen rechten Eck des Slicers. Lesen Sie auch den Artikel über Slicer hier.

    Online und andere Aufträge

    Um die Anzahl Online-/Offline-Aufträge zählen zu können, benötigen wir ein sogenanntes Measure (theoretisch lässt sich das auch über zwei berechnete Spalten lösen). Wechseln Sie dazu in das Power Pivot Fenster zurück und klicken auf eine Zelle im unteren Berechnungsbereich. Beginnen Sie das Measure wie ein Formel mit einem Gleichheitszeichen. Markieren Sie im Berechnungsbereich eine Zelle und schreiben direkt los, ist die Bearbeitungszeile im oberen Bereich aktiv. Die Formel für die Berechnung lautet:

    =SUMX(FILTER(SalesOrderHeader; SalesOrderHeader[OnlineOrderFlag]=”WAHR”);1)

    Zerbrechen Sie sich an dieser Stelle noch nicht den Kopf, was SUMX bedeutet. Es gibt hierzu einen Artikel. Power Pivot gibt Ihrem Measure sofort einen Namen Measure 1. Ändern Sie diesen auf Online, indem Sie in der Bearbeitungszeile vor dem Doppelpunkt den Namen austauschen. So steht am Ende für das Measure Online in der Bearbeitungsleiste folgender Text:

    Online:=SUMX(FILTER(SalesOrderHeader; SalesOrderHeader[OnlineOrderFlag]=”WAHR”);1)

    Legen Sie ein zweites Measure an mit der Formel:

    Offline:=SUMX(FILTER(SalesOrderHeader; SalesOrderHeader[OnlineOrderFlag]=”FALSCH”);1)

    Die Ergebnisse der Measures werden direkt angezeigt:

    image

    Fügen Sie in Ihre aktuelles Arbeitsblatt ein weiteres Diagramm ein. Sie finden in der Feldliste auch unser beiden neuen Measures wieder. Wählen Sie folgende Felder in folgende Bereiche:

    • JahrUndMonat in Achsenfelder
    • Offline in Werte
    • Online in Werte

    Markieren Sie das Diagramm und wechseln Sie im Menü auf PivotChart-Tools auf den Menüpunkt Entwurf:

    image

    Wählen Sie ganz links den Eintrag Diagrammtyp ändern und wählen im Dialog den ersten Linientyp aus, der angezeigt wird:

    image

    Bestätigen Sie die Auswahl mit OK. Das Diagramm zeigt jetzt den zeitlichen Verlauf von Offline- und Online-Aufträgen an:

    image

    Layout

    Sex sells oder Akzeptanz durch Firlefanz hört sich lustig an, ist aber für die Akzeptanz tatsächlich wichtig. Hier ein paar Stichpunkt für unser aktuelles Dashboard:

    • Hintergrund markieren und eine Farbe setzen (weiß oder ein leichtes grau – halten Sie sich immer an Unternehmensfarben).
    • Logo: Fügen Sie Ihr Unternehmenslogo ein – es kostet nicht viel und macht sofort was her.
    • Beschriften Sie die Diagramme.
    • Löschen Sie die Felder in den Diagrammen (Kontextmenü auf ein Feld und dann der Eintrag Alle Feldschaltflächen im Diagramm ausblenden).
    • Schieben Sie die Diagramme logisch zusammen. Machen Sie die Diagramme gleich hoch/gleich breit.
    • Brauchen Sie die Legendenfelder? Wenn dort nur Ergebnis steht, markieren Sie sie und löschen sie einfach.
    • Markieren Sie das Diagramm und wählen im Kontextmenü Diagrammbereich formatieren. Wählen Sie im Punkt Rahmenfarbe den Eintrag Keine Linie.
    • Formatieren Sie den Slicer (siehe Artikel über Slicer-Layout).
    • Für die Präsentation:
      • Im Menü Ansicht alle Haken entfernen bei Gitternetzlinien, Bearbeitungsleiste und Überschriften
      • Darstellen, z.B. auf einem Beamer, immer im Vollbildmodus.
      • Und ganz zum Schluss präsentieren Sie das Dashboard immer im Vollbildmodus – zu finden unter Ansicht/Ganzer Bildschirm.

    Und so könnte es dann aussehen:

    image

     

    Das Ergebnis können Sie sich auch hier herunterladen.

    Kategorien: Grundlagen, Killer Entries, Power Pivot
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    3 Comments

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.