Excel–Pivot mit automatischem Vergleich

In einer langen Liste sind alle Aufträge der Jahre 2014 bis 2016 aufgelistet. Daraus wird eine Pivot-Tabelle gemacht, die die Umsätze pro Jahr und Ort ausgibt:

03.03.2017_0013

 

Zusätzlich zu den absoluten Werten sollen die Veränderungen zwischen den beiden Jahren erfasst werden – wie viel Prozent Umsatz mehr oder weniger haben die Städte im Vergleich zum Vorjahr gemacht? Das geht automatisch in der Pivot-Tabelle.

03.03.2017_0014

Begonnen wird im Jahr 2014 – da es keinen Vergleich zum Vorjahr gibt, werden hier für alle Städte 100 % eingetragen. Im Jahr 2015 erreicht Berlin 105 %, Düsseldorf nur 86 % verglichen zum Vorjahr – das ist das Jahr 2014. Im Jahr 2016 hat Berlin nur 42 % im Vergleich zum Vorjahr – das ist das Jahr 2015. Verglichen werden also nicht die Werte zum immer gleichen Jahr 2014, sondern immer zum Jahr davor.

 

Erstellen Sie zuerst die Pivot-Tabelle:

  • Orte sind die Zeilenbeschriftung
  • Datum ist die Spaltenbeschriftung
  • Umsatz kommt in die Werte

Die Gruppierung in Jahre wird in Excel 2013/2016 automatisch erzeugt. Wenn das nicht passiert, klicken Sie mit rechter Maustaste in die Spaltenüberschrift mit dem Datum und wählen “Gruppierung” (Erklärung im Lexikon).

Aus dieser ersten Pivot erstellen Sie anschließend den Vergleich zum Vorjahr. Und das geht so:

  • Rechtsklick in eine der Umsatzzahlen
  • Wählen Sie “Werte anzeigen als: % von …”
  • Basisfeld: Jahre
    Basiselement: (Vorherige)03.03.2017_0009
  • Mit OK bestätigen

Ich habe anschließend noch mit “PivotTable-Tools Entwurf” die Gesamtergebnisse nur für Spalten aktiviert.

Eine andere (und in meinen Augen bessere) Darstellung ist die Anzeige als Veränderung in der Form “- 7%” oder “+15 %”.

03.03.2017_0015

 

Dafür verwenden Sie “Werte anzeigen als: % Differenz von …”

  • Wieder Rechtsklick in eine der Umsatzzahlen
  • Wählen Sie jetzt “Werte anzeigen als: % Differenz von …”
  • Basisfeld: Jahre
    Basiselement: (Vorherige)03.03.2017_0011
  • Mit OK bestätigen

Die Werte des Jahres 2014 werden nicht mehr angezeigt, die Werte für 2015 und 2016 werden als Prozentveränderung zum jeweiligen Vorjahr dargestellt. Berlin hat im Jahr 2015 um 5,5 % mehr als im Jahr 2014 und im Jahr 2016 um 57,9 % weniger als im Jahr 2015.

Die Formatierung in rot und blau wie in meinem Beispiel wird über eine benutzerdefinierte Zahlenformatierung erreicht. Klicken Sie eine Zahl im Wertfeldbereich mit rechter Maustaste an und wählen Sie “Wertfeldeinstellung | Zahlenformat | Benutzerdefiniert”. Tragen Sie ein:

 

[Blau]+0,00 %;[Rot]-0,00 %

03.03.2017_0012

 

Sie können die Beispieltabelle (ohne die Pivots) hier herunterladen: Beispiel für Differenz in Prozent

 

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*