Berechnen der prozentualen Änderung mit Pivot-Tabellen in Excel

Pivot-Tabellen sind ein erstaunliches integriertes Berichterstellungstool in Excel. Während normalerweise Daten mit Summen zusammengefasst werden, können Sie sie auch verwenden, um den Prozentsatz der Änderung zwischen Werten zu berechnen. Noch besser: Es ist einfach zu machen.

Sie können diese Technik verwenden, um alle möglichen Dinge zu tun – so ziemlich überall, wo Sie sehen möchten, wie ein Wert mit einem anderen verglichen wird. In diesem Artikel verwenden wir das einfache Beispiel für die Berechnung und Anzeige des Prozentsatzes, um den sich der Gesamtverkaufswert von Monat zu Monat ändert.

Hier ist das Blatt, das wir verwenden werden.

Es ist ein ziemlich typisches Beispiel für ein Verkaufsblatt, das das Bestelldatum, den Kundennamen, den Vertriebsmitarbeiter, den Gesamtverkaufswert und einige andere Dinge enthält.

Zu diesem Zweck formatieren wir zuerst unseren Wertebereich als Tabelle in Excel und erstellen dann eine Pivot-Tabelle, um unsere prozentualen Änderungsberechnungen durchzuführen und anzuzeigen.

Formatieren des Bereichs als Tabelle

Wenn Ihr Datenbereich noch nicht als Tabelle formatiert ist, empfehlen wir Ihnen, dies zu tun. In Tabellen gespeicherte Daten haben gegenüber Daten in Zellbereichen eines Arbeitsblatts mehrere Vorteile, insbesondere bei Verwendung von PivotTables (Lesen Sie mehr über die Vorteile der Verwendung von Tabellen).

Um einen Bereich als Tabelle zu formatieren, wählen Sie den Zellenbereich aus und klicken Sie auf Einfügen> Tabelle.

Überprüfen Sie, ob der Bereich korrekt ist und ob sich in der ersten Zeile dieses Bereichs Überschriften befinden, und klicken Sie dann auf «OK».

Der Bereich ist jetzt als Tabelle formatiert. Durch das Benennen der Tabelle wird es in Zukunft einfacher, beim Erstellen von PivotTables, Diagrammen und Formeln darauf zuzugreifen.

Klicken Sie unter Tabellentools auf die Registerkarte „Design“ und geben Sie einen Namen in das Feld am Anfang der Multifunktionsleiste ein. Diese Tabelle wurde als «Vertrieb» bezeichnet.

Sie können hier auch den Stil der Tabelle ändern, wenn Sie möchten.

Erstellen Sie eine PivotTable, um die prozentuale Änderung anzuzeigen

Lassen Sie uns nun mit dem Erstellen der PivotTable fortfahren. Klicken Sie in der neuen Tabelle auf Einfügen> PivotTable.

Das Fenster PivotTable erstellen wird angezeigt. Ihre Tabelle wird automatisch erkannt. Sie können jedoch die Tabelle oder den Bereich auswählen, die / der Sie an dieser Stelle für die PivotTable verwenden möchten.

Gruppieren Sie die Daten in Monate

Anschließend ziehen wir das Datumsfeld, nach dem wir gruppieren möchten, in den Zeilenbereich der PivotTable. In diesem Beispiel heißt das Feld Bestelldatum.

Ab Excel 2016 werden Datumswerte automatisch in Jahre, Quartale und Monate gruppiert.

Wenn Ihre Excel-Version dies nicht tut oder Sie einfach die Gruppierung ändern möchten, klicken Sie mit der rechten Maustaste auf eine Zelle mit einem Datumswert und wählen Sie dann den Befehl «Gruppieren».

Wählen Sie die Gruppen aus, die Sie verwenden möchten. In diesem Beispiel werden nur Jahre und Monate ausgewählt.

Das Jahr und der Monat sind jetzt Felder, die wir für die Analyse verwenden können. Die Monate werden weiterhin als Bestelldatum bezeichnet.

Fügen Sie die Wertfelder zur PivotTable hinzu

Verschieben Sie das Feld Jahr aus den Zeilen in den Bereich Filter. Auf diese Weise kann der Benutzer die PivotTable ein Jahr lang filtern, anstatt die PivotTable mit zu vielen Informationen zu überladen.

Ziehen Sie das Feld mit den Werten (in diesem Beispiel Gesamtverkaufswert), die Sie berechnen möchten, und präsentieren Sie die Änderung in den Bereich Werte zweimal.

Es könnte noch nicht viel aussehen. Das wird sich aber sehr bald ändern.

Beide Wertefelder sind standardmäßig summiert und haben derzeit keine Formatierung.

Die Werte in der ersten Spalte möchten wir als Summen behalten. Sie müssen jedoch formatiert werden.

Klicken Sie mit der rechten Maustaste auf eine Zahl in der ersten Spalte und wählen Sie «Zahlenformatierung» aus dem Kontextmenü.

Wählen Sie im Dialogfeld «Zellen formatieren» das Format «Buchhaltung» mit 0 Dezimalstellen.

Die PivotTable sieht nun folgendermaßen aus:

Erstellen Sie die Spalte für die prozentuale Änderung

Klicken Sie mit der rechten Maustaste auf einen Wert in der zweiten Spalte, zeigen Sie auf «Werte anzeigen» und klicken Sie dann auf die Option «% Differenz von».

Wählen Sie «(Zurück)» als Basiselement. Dies bedeutet, dass der aktuelle Monatswert immer mit dem Wert der Vormonate (Feld Bestelldatum) verglichen wird.

Die PivotTable zeigt jetzt sowohl die Werte als auch die prozentuale Ä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 für die zweite Wertespalte und geben Sie «Varianz» ein.

Fügen Sie einige Abweichungspfeile hinzu

Um diese PivotTable wirklich zu verbessern, möchten wir die prozentuale Änderung durch Hinzufügen einiger grüner und roter Pfeile besser visualisieren.

Diese bieten uns eine schöne Möglichkeit zu sehen, ob eine Änderung positiv oder negativ war.

Klicken Sie auf einen der Werte in der zweiten Spalte und dann auf Start> Bedingte Formatierung> Neue Regel. Führen Sie im folgenden Fenster «Formatierungsregel bearbeiten» die folgenden Schritte aus:

1. Wählen Sie die Option «Alle Zellen mit» Abweichungswerten «für das Bestelldatum».
2. Wählen Sie «Icon Sets» aus der Liste «Format Style».
3. Wählen Sie die roten, gelben und grünen Dreiecke aus der Liste der Symbolstile aus.
4. Ändern Sie in der Spalte Typ die Listenoption in «Zahl» anstelle von «Prozentsatz». Dadurch wird die Spalte Wert in Nullen geändert. Genau das, was wir wollen.

Klicken Sie auf «OK» und die bedingte Formatierung wird auf die PivotTable angewendet.

PivotTables sind ein unglaubliches Werkzeug und eine der einfachsten Möglichkeiten, die prozentuale zeitliche Änderung von Werten anzuzeigen.