Pivot-Tabellen sind ein bemerkenswertes, in Excel integriertes Werkzeug für die Berichterstattung. Sie werden meistens verwendet, um Daten durch Summenbildung zusammenzufassen, können aber auch dazu dienen, die prozentuale Veränderung zwischen Werten zu ermitteln. Das Beste daran: Es ist kinderleicht.
Diese Technik ist vielseitig einsetzbar – überall dort, wo Sie die Entwicklung eines Wertes im Vergleich zu einem anderen beobachten möchten. In diesem Artikel demonstrieren wir dies am einfachen Beispiel der Berechnung und Anzeige der monatlichen Veränderung des Gesamtumsatzes in Prozent.
Hier ist das zugrunde liegende Datenblatt:
Es handelt sich um ein typisches Verkaufsdatenblatt, das Bestelldatum, Kundennamen, Verkäufer, Gesamtumsatz und weitere Details enthält.
Um dies umzusetzen, formatieren wir zuerst unseren Wertebereich als Tabelle in Excel und erstellen anschließend eine Pivot-Tabelle, um die prozentualen Änderungen zu berechnen und anzuzeigen.
Bereich als Tabelle formatieren
Falls Ihr Datenbereich noch keine Tabelle ist, empfehlen wir Ihnen dies zu ändern. Tabellendaten bieten mehrere Vorteile gegenüber Daten in Zellbereichen, besonders bei der Verwendung von Pivot-Tabellen (Erfahren Sie mehr über die Vorteile von Tabellen).
Markieren Sie den Bereich und klicken Sie auf „Einfügen“ > „Tabelle“, um einen Bereich als Tabelle zu formatieren.
Überprüfen Sie, ob der Bereich richtig ausgewählt wurde und dass die erste Zeile des Bereichs als Überschrift dient, und bestätigen Sie mit „OK“.
Der Bereich ist nun als Tabelle formatiert. Durch die Vergabe eines Namens wird es leichter, in Zukunft beim Erstellen von Pivot-Tabellen, Diagrammen und Formeln auf diese Tabelle zu verweisen.
Klicken Sie unter „Tabellentools“ auf die Registerkarte „Design“ und geben Sie einen Namen in das Feld am Anfang des Menübands ein. Wir nennen diese Tabelle „Sales“.
Hier können Sie auch das Design der Tabelle ändern, wenn Sie möchten.
Erstellung einer Pivot-Tabelle für prozentuale Veränderungen
Jetzt widmen wir uns der Erstellung der Pivot-Tabelle. Wählen Sie in der neu erstellten Tabelle „Einfügen“ > „PivotTable“.
Das Fenster „PivotTable erstellen“ wird eingeblendet. Ihre Tabelle wurde automatisch erkannt. Sie können jedoch hier die Tabelle oder den Bereich auswählen, den Sie für die PivotTable verwenden möchten.
Daten nach Monaten gruppieren
Als Nächstes ziehen wir das Datumsfeld, nach dem wir gruppieren möchten, in den Zeilenbereich der Pivot-Tabelle. In diesem Beispiel heißt das Feld „Bestelldatum“.
Ab Excel 2016 werden Datumsangaben automatisch nach Jahren, Quartalen und Monaten gruppiert.
Wenn Ihre Excel-Version dies nicht macht oder Sie die Gruppierung ändern möchten, klicken Sie mit der rechten Maustaste auf eine Zelle mit einem Datum und wählen Sie „Gruppieren“.
Wählen Sie die gewünschten Gruppierungen aus. In diesem Beispiel sind nur Jahre und Monate ausgewählt.
Jahr und Monat sind nun Felder, die wir für die Analyse nutzen können. Die Monate behalten den Namen „Bestelldatum“.
Wertfelder zur Pivot-Tabelle hinzufügen
Verschieben Sie das Feld „Jahr“ aus den Zeilen in den Filterbereich. Das ermöglicht es dem Nutzer, die Pivot-Tabelle für ein bestimmtes Jahr zu filtern, ohne die Tabelle mit zu vielen Informationen zu überladen.
Ziehen Sie das Feld mit den zu berechnenden Werten (hier „Gesamtverkaufswert“) zweimal in den Wertebereich.
Es mag noch nicht viel erscheinen, aber das ändert sich bald.
Beide Wertfelder haben standardmäßig die Summenfunktion und sind noch nicht formatiert.
Die Werte der ersten Spalte sollen als Summen beibehalten werden, aber eine Formatierung ist erforderlich.
Klicken Sie mit der rechten Maustaste auf eine Zahl in der ersten Spalte und wählen Sie „Zahlenformatierung“.
Wählen Sie im Dialogfenster „Zellen formatieren“ das Format „Buchhaltung“ mit 0 Dezimalstellen.
Die Pivot-Tabelle sieht nun so aus:
Spalte für prozentuale Veränderung erstellen
Klicken Sie mit der rechten Maustaste auf einen Wert in der zweiten Spalte, zeigen Sie auf „Werte anzeigen als“ und wählen Sie dann die Option „% Differenz von“.
Wählen Sie „(Vorherige)“ als Basiselement. Dies bedeutet, dass der Wert des aktuellen Monats immer mit dem des Vormonats verglichen wird (Feld „Bestelldatum“).
Die Pivot-Tabelle zeigt nun sowohl die Werte als auch die prozentuale Veränderung an.
Klicken Sie in die Zelle mit den Zeilenbeschriftungen und geben Sie „Monat“ als Überschrift für diese Spalte ein. Klicken Sie dann in die Kopfzelle der zweiten Wertespalte und geben Sie „Abweichung“ ein.
Abweichungspfeile hinzufügen
Um diese Pivot-Tabelle abzurunden, visualisieren wir die prozentuale Veränderung mit roten und grünen Pfeilen.
Diese geben uns eine gute Übersicht, ob eine Veränderung positiv oder negativ war.
Klicken Sie auf einen Wert in der zweiten Spalte und wählen Sie „Start“ > „Bedingte Formatierung“ > „Neue Regel“. Im sich öffnenden Fenster „Formatierungsregel bearbeiten“ führen Sie folgende Schritte aus:
Wählen Sie „Alle Zellen mit ‚Abweichung‘-Werten für Bestelldatum“ aus. Wählen Sie „Symbolsätze“ aus der Liste „Formatstil“. Wählen Sie die roten, gelben und grünen Dreiecke aus der Liste „Symbolstil“ aus. Ändern Sie in der Spalte „Typ“ die Listenoption auf „Zahl“ anstelle von „Prozent“. Dadurch wird der Wert in der Spalte auf 0 geändert, genau das, was wir möchten.
Bestätigen Sie mit „OK“ und die bedingte Formatierung wird auf die Pivot-Tabelle angewendet.
Pivot-Tabellen sind ein unschätzbares Werkzeug und eine der einfachsten Methoden, um die prozentuale Veränderung von Werten im Zeitverlauf anzuzeigen.