Pivot-Tabellen sind ein erstaunliches integriertes Berichtstool in Excel. Während sie normalerweise zum Zusammenfassen von Daten mit Summen verwendet werden, können Sie sie auch verwenden, um den Prozentsatz der Änderung zwischen Werten zu berechnen. Noch besser: Es ist ganz einfach.
Sie können diese Technik für alle möglichen Dinge verwenden – so ziemlich überall, wo Sie sehen möchten, wie ein Wert im Vergleich zu einem anderen abschneidet. In diesem Artikel verwenden wir das einfache Beispiel zur Berechnung und Anzeige des Prozentsatzes, um den sich der Gesamtverkaufswert Monat für 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 anzeigt.
Um all dies zu tun, formatieren wir zuerst unseren Wertebereich als Tabelle in Excel und erstellen dann eine Pivot-Tabelle, um unsere prozentualen Änderungsberechnungen durchzuführen und anzuzeigen.
Inhaltsverzeichnis
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 Zellbereich aus und klicken Sie auf Einfügen > Tabelle.
Überprüfen Sie, ob der Bereich korrekt ist, dass Sie in der ersten Zeile dieses Bereichs Überschriften haben, und klicken Sie dann auf „OK“.
Der Bereich ist jetzt als Tabelle formatiert. Die Benennung der Tabelle erleichtert die zukünftige Bezugnahme beim Erstellen von PivotTables, Diagrammen und Formeln.
Klicken Sie unter Tabellentools auf die Registerkarte „Design“ und geben Sie einen Namen in das Feld am Anfang der Multifunktionsleiste ein. Diese Tabelle trägt den Namen „Sales“.
Sie können hier auch den Stil der Tabelle ändern, wenn Sie möchten.
Erstellen Sie eine PivotTable, um die prozentuale Änderung anzuzeigen
Kommen wir nun zum Erstellen der PivotTable. Klicken Sie in der neuen Tabelle auf Einfügen > PivotTable.
Das Fenster PivotTable erstellen wird angezeigt. Es wird Ihren Tisch automatisch erkannt haben. Sie können jedoch an dieser Stelle die Tabelle oder den Bereich auswählen, die Sie für die PivotTable verwenden möchten.
Gruppieren Sie die Daten in Monate
Wir ziehen dann 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 nach Jahren, Quartalen und Monaten gruppiert.
Wenn Ihre Excel-Version dies nicht tut oder Sie einfach nur die Gruppierung ändern möchten, klicken Sie mit der rechten Maustaste auf eine Zelle, die einen Datumswert enthält, und wählen Sie dann den Befehl „Gruppieren“.
Wählen Sie die Gruppen aus, die Sie verwenden möchten. In diesem Beispiel sind nur Jahre und Monate ausgewählt.
Jahr und Monat sind nun Felder, die wir zur Analyse verwenden können. Die Monate werden weiterhin als Bestelldatum bezeichnet.
Fügen Sie der PivotTable die Wertfelder hinzu
Verschieben Sie das Feld Jahr aus Zeilen in den Bereich Filter. Dadurch 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 und ändern möchten, zweimal in den Bereich Werte.
Es sieht vielleicht noch nicht nach viel aus. Aber das wird sich sehr bald ändern.
Beide Wertfelder haben standardmäßig die Summe und haben derzeit keine Formatierung.
Die Werte in der ersten Spalte möchten wir als Summen behalten. Sie erfordern jedoch eine Formatierung.
Klicken Sie mit der rechten Maustaste auf eine Zahl in der ersten Spalte und wählen Sie im Kontextmenü „Zahlenformatierung“.
Wählen Sie im Dialogfeld Zellen formatieren das Format „Buchhaltung“ mit 0 Dezimalstellen.
Die PivotTable sieht nun so 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 aus. Dies bedeutet, dass der Wert des aktuellen Monats 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 Zeilenbeschriftungen und geben Sie „Monat“ als Überschrift für diese Spalte ein. Klicken Sie dann in die Kopfzelle der zweiten Wertespalte und geben Sie „Variance“ ein.
Fügen Sie einige Abweichungspfeile hinzu
Um diese PivotTable wirklich abzurunden, möchten wir die prozentuale Änderung besser visualisieren, indem wir einige grüne und rote Pfeile hinzufügen.
Diese geben uns eine schöne Möglichkeit zu sehen, ob eine Verä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 sich öffnenden Fenster Formatierungsregel bearbeiten die folgenden Schritte aus:
Wählen Sie die Option „Alle Zellen mit „Varianz“-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 in „Zahl“ anstelle von Prozentsatz. Dadurch wird die Spalte Wert auf 0 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 Änderung von Werten im Zeitverlauf anzuzeigen.