Monatliche Abweichungen mit PivotTable in Excel ermitteln

Bei der zeitraumbezogenen Analyse von Umsätzen sind oft die Abweichungen der Monatsumsätze von den Umsätzen des jeweiligen Vorjahresmonats interessant. In diesem Tipp zeigen wir Ihnen, wie Sie aus einer Tabelle von Umsatzdaten in Excel 2010 mit Hilfe einer PivotTable die absoluten und prozentualen Abweichungen ermitteln können.

Beispiel

In einem Tabellenblatt wurde für die Rohdaten mit den Verkaufszahlen der Jahre 2012 und 2013 eine Tabelle mit dem Namen Verkäufe erstellt. Das untere Bild zeigt einen Ausschnitt aus dieser Tabelle.

Excel Tabelle

Um aus diesen Verkaufsdaten die monatlichen Umsätze für die einzelnen Jahre ermitteln zu können, muss die Basistabelle eine Spalte mit gültigen Datumsangaben enthalten.

Die absoluten und prozentualen Abweichungen bezogen auf die Monatsumsätze des Vorjahres können Sie mit Hilfe einer PivotTable folgendermaßen ermitteln:

 

    1. Aktivieren Sie eine beliebige Zelle in der Tabelle Verkäufe.
    2. Klicken Sie im Menüband auf der Registerkarte Einfügen in der Gruppe Tabellen auf die Schaltfläche PivotTable. Das folgende Dialogbild erscheint:

      Excel Tabelle

    3. Prüfen Sie, ob der Eintrag im Eingabefeld Tabelle/Bereich richtig ist. Klicken Sie auf die Schaltfläche OK. Danach wird ein neues Tabellenblatt angelegt mit einer leeren PivotTable.
    4. Ziehen Sie aus der PivotTable-Feldliste das Feld Umsatz in das Listenfeld Werte und das Feld Datum in das Listenfeld Zeilenbeschriftungen. Die PivotTable sieht folgendermaßen aus (Ausschnitt):

      Excel Tabelle

       

    5. Im nächsten Schritt sollen die Umsatzwerte im Euro-Format erscheinen. Markieren Sie alle Umsatzwerte einschließlich des Gesamtumsatzes und klicken Sie auf die rechte Maustaste, um das Kontextmenü zu öffnen.
    6. Klicken Sie auf den Menüpunkt Zellen formatieren. Es erscheint das folgende Dialogbild:

      Excel Tabelle

       

    7. Klicken Sie im Listenfeld Kategorie auf Währung und anschließend auf den Eintrag -1.234,10 €. Klicken Sie auf die Schaltfläche OK.
    8. Jetzt sollen die Monatswerte ermittelt werden. Dazu müssen die Datumswerte gruppiert werden. Aktivieren Sie eine beliebige Zelle mit einer Datumsangabe. Öffnen Sie mit der rechten Maustaste das Kontextmenü und klicken Sie auf Gruppieren. Das folgende Dialogbild wird angezeigt:

      Excel Tabelle

       

    9. Markieren Sie in dem Listenfeld Nach zusätzlich den Eintrag Jahre und klicken Sie auf die Schaltfläche OK. In der PivotTable werden die Monatswerte nach Jahr und Monat angezeigt. In der PivotTable-Feldliste befindet sich ein neuer Eintrag mit dem Namen Jahre.
    10. Im nächsten Schritt sollen rechts neben den Monatswerten die absoluten Abweichungen gegenüber den Monatswerten des jeweiligen Vorjahres angezeigt werden. Ziehen Sie dazu aus der PivotTable-Feldliste den Eintrag Umsatz ein zweites Mal in das Listenfeld Werte. In der PivotTable erscheint eine neue Spalte mit der Überschrift Summe von Umsatz2.
    11. Aktivieren Sie eine beliebige Zelle mit einem Umsatzwert in der Spalte mit der Überschrift Summe von Umsatz2.
    12. Klicken Sie auf die rechte Maustaste und in dem sich öffnenden Kontextmenü auf den Menüpunkt Werte anzeigen als. Es erscheint ein zweites Kontextmenü. Klicken Sie auf den Menüpunkt Differenz von. Das folgende Dialogbild erscheint:

      Excel Tabelle

       

    13. Prüfen Sie, ob im Kombinationsfeld Basisfeld der Eintrag Datum erscheint. Als Basiselement wird das aktuelle Element angezeigt.
    14. Öffnen Sie das Kombinationsfeld Basisfeld und wählen Sie den Eintrag Jahre.
    15. Öffnen Sie das Kombinationsfeld Basiselement und wählen Sie den Eintrag (Vorheriger).
    16. Klicken Sie auf OK.
    17. Im letzten Schritt sollen rechts neben den absoluten Abweichungen die prozentualen Abweichungen gegenüber den Monatswerten des jeweiligen Vorjahres angezeigt werden. Ziehen Sie dazu aus der PivotTable-Feldliste den Eintrag Umsatz ein drittes Mal in das Listenfeld Werte. In der PivotTable erscheint eine neue Spalte mit der Überschrift Summe von Umsatz3.
    18. Aktivieren Sie eine beliebige Zelle mit einem Umsatzwert in der Spalte mit der Überschrift Summe von Umsatz3.
    19. Klicken Sie auf die rechte Maustaste und in dem sich öffnenden Kontextmenü auf den Menüpunkt Werte anzeigen als. Es erscheint ein zweites Kontextmenü. Klicken Sie auf den Menüpunkt % Differenz von. Das folgende Dialogbild erscheint:

      Excel Tabelle

       

    20. Prüfen Sie, ob im Kombinationsfeld Basisfeld der Eintrag Datum erscheint. Als Basiselement wird das aktuelle Element angezeigt.
    21. Öffnen Sie das Kombinationsfeld Basisfeld und wählen Sie wieder den Eintrag Jahre.
    22. Öffnen Sie das Kombinationsfeld Basiselement und wählen Sie erneut den Eintrag (Vorheriger).
    23. Klicken Sie auf OK.

Die folgende PivotTable zeigt Ihnen die absoluten und prozentualen Abweichungen der Monatsumsätze des Jahres 2013 gegenüber dem Jahr 2012 an.

Excel Tabelle

Hinweise

In der oben angezeigten PivotTable wurden noch folgende Ergänzungen vorgenommen:

 

    • Die Überschrift Summe von Umsatz2 wurde in Abw. Vorjahr geändert. Aktivieren Sie die entsprechende Zelle und tragen Sie in der Bearbeitungsleiste die neue Überschrift ein.
    • Die Überschrift Summe von Umsatz3 wurde in Abw. Vorjahr % geändert. Aktivieren Sie die entsprechende Zelle und tragen Sie in der Bearbeitungsleiste die neue Überschrift ein.
    • Die Summe der absoluten Abweichungen sowie die Abweichungen auf Jahresbasis werden in der PivotTable zunächst nicht angezeigt Aktivieren Sie eine Zelle mit dem Eintrag 2012 oder 2013, öffnen Sie das Kontextmenü mit der rechten Maustaste und aktivieren Sie den Menüpunkt Teilergebnis Jahre.
    • Den prozentualen Abweichungen wurde das benutzerdefinierte Format 0,00%;[Rot]-0,00% zugewiesen.
Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

3 Kommentare

  1. Wenn ein Umsatz 0 ist ergibt sich #NULL, also bei einer Abweichung von -100%…Wie kann man das darstellen?

    Antworten
  2. ich hatte eine excel voralge erwartet wo man diese sehen und bearbeiten kann

    Antworten
  3. Die Daten werden mit Monaten in den ZEILEN dargestellt. Wenn der Benutzer jedoch in den Zeilen zB die Kunden und in den SPALTEN die Monate hat, geht es nicht mehr

    Antworten

Einen Kommentar abschicken

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

Pin It on Pinterest

Share This