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.

Seminartipp

Die Erstellung von PivotTables ist ein Thema unseres Excel-Aufbaukurses und steht im Mittelpunkt des Seminars Unternehmensdaten analysieren.

Hat Ihnen dieser Beitrag weitergeholfen?

ja nein Andere Erwartung

Geben Sie uns Ihr Feedback. Möchten Sie eine Antwort von uns erhalten, tragen Sie zusammen mit Ihrem Feedback auch Ihre E-Mail-Adresse ein. Ihre E-Mail-Adresse wird ausschließlich für die Kommunikation mit Ihnen verwendet. Sie wird nicht veröffentlicht oder weitergegeben.

Benötigen Sie professionelle Unterstützung beim Einsatz von Word, Excel, Outlook, PowerPoint oder Access, so sind wir der richtige Ansprechpartner für Sie. Senden Sie uns eine E-Mail mit einer kurzen Problembeschreibung. Jemand aus unserem Team wird sich bei Ihnen melden, um weitere Details abzuklären und Ihnen ein Angebot für unsere Dienstleistung zu erstellen.