✅ So fügen Sie ein berechnetes Feld für eine Excel-Pivot-Tabelle hinzu und verwenden es

Inhaltsverzeichnis

Was ist ein berechnetes Pivot-Tabellen-Feld?
Hinzufügen eines berechneten Felds zur Pivot-Tabelle
Ein Problem mit berechneten Pivot-Tabellenfeldern
So ändern oder löschen Sie ein berechnetes Pivot-Tabellenfeld
Wie erhalte ich eine Liste aller berechneten Feldformeln?

Nachdem Sie eine Pivot-Tabelle erstellt haben, müssen Sie häufig Ihre Analyse erweitern und mehr Daten/Berechnungen als Teil davon einbeziehen.

Wenn Sie einen neuen Datenpunkt benötigen, der mithilfe vorhandener Datenpunkte in der Pivot-Tabelle abgerufen werden kann, müssen Sie nicht zurückgehen und ihn den Quelldaten hinzufügen. Stattdessen können Sie a . verwenden Berechnetes Feld der Pivot-Tabelle um dies zu tun.

Laden Sie den Datensatz herunter und folgen Sie ihm.

Was ist ein berechnetes Pivot-Tabellen-Feld?

Beginnen wir mit einem einfachen Beispiel einer Pivot-Tabelle.

Angenommen, Sie haben einen Datensatz mit Einzelhändlern und erstellen eine Pivot-Tabelle wie unten gezeigt:

Die obige Pivot-Tabelle fasst die Umsatz- und Gewinnwerte für die Einzelhändler zusammen.

Was ist nun, wenn Sie auch wissen möchten, wie hoch die Gewinnspanne dieser Einzelhändler war (wobei die Gewinnspanne „Gewinn“ geteilt durch „Umsatz“ ist).

Dazu gibt es mehrere Möglichkeiten:

Gehen Sie zurück zum ursprünglichen Datensatz und fügen Sie diesen neuen Datenpunkt hinzu. So können Sie eine neue Spalte in die Quelldaten einfügen und darin die Gewinnspanne berechnen. Sobald Sie dies tun, müssen Sie die Quelldaten der Pivot-Tabelle aktualisieren, um diese neue Spalte als Teil davon zu erhalten.
- Obwohl diese Methode möglich ist, müssen Sie manuell zum Datensatz zurückkehren und die Berechnungen durchführen. Beispielsweise müssen Sie möglicherweise eine weitere Spalte hinzufügen, um den durchschnittlichen Verkauf pro Einheit (Umsatz/Menge) zu berechnen. Auch hier müssen Sie diese Spalte zu Ihren Quelldaten hinzufügen und dann die Pivot-Tabelle aktualisieren.
- Diese Methode bläst auch Ihre Pivot-Tabelle auf, wenn Sie neue Daten hinzufügen.
Fügen Sie Berechnungen außerhalb der Pivot-Tabelle hinzu. Dies kann eine Option sein, wenn sich Ihre Pivot-Tabellenstruktur wahrscheinlich nicht ändern wird. Wenn Sie jedoch die Pivot-Tabelle ändern, wird die Berechnung möglicherweise nicht entsprechend aktualisiert und führt möglicherweise zu falschen Ergebnissen oder Fehlern. Wie unten gezeigt, habe ich die Gewinnspanne berechnet, wenn Einzelhändler in der Reihe waren. Aber als ich es von Kunden zu Regionen änderte, gab die Formel einen Fehler aus.
Verwenden eines berechneten Pivot-Tabellenfelds. Dies ist das der effizienteste Weg um vorhandene Pivot-Tabellendaten zu verwenden und die gewünschte Metrik zu berechnen. Betrachten Sie das berechnete Feld als virtuelle Spalte, die Sie mithilfe der vorhandenen Spalten aus der Pivot-Tabelle hinzugefügt haben. Die Verwendung eines berechneten Pivot-Tabellenfelds bietet viele Vorteile (wie wir gleich sehen werden):
- Sie müssen keine Formeln verarbeiten oder Quelldaten aktualisieren.
- Es ist skalierbar, da es automatisch alle neuen Daten berücksichtigt, die Sie Ihrer Pivot-Tabelle hinzufügen. Nachdem Sie ein Berechnungsfeld hinzugefügt haben, können Sie es wie jedes andere Feld in Ihrer Pivot-Tabelle verwenden.
- Es ist einfach zu aktualisieren und zu verwalten. Wenn sich beispielsweise die Metriken ändern oder Sie die Berechnung ändern müssen, können Sie dies ganz einfach über die Pivot-Tabelle selbst tun.

Hinzufügen eines berechneten Felds zur Pivot-Tabelle

Sehen wir uns an, wie Sie ein berechnetes Pivot-Tabellenfeld zu einer vorhandenen Pivot-Tabelle hinzufügen.

Angenommen, Sie haben eine Pivot-Tabelle wie unten gezeigt und möchten die Gewinnspanne für jeden Einzelhändler berechnen:

Hier sind die Schritte zum Hinzufügen eines berechneten Pivot-Tabellenfelds:

Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
Gehen Sie zu Pivot-Tabellentools -> Analysieren -> Berechnungen -> Felder, Elemente und Sets.
Wählen Sie in der Dropdown-Liste Berechnetes Feld aus.
Im Dialogfeld Berechnetes Feld einfügen:
- Geben Sie ihm einen Namen, indem Sie ihn in das Feld Name eingeben.
- Erstellen Sie im Feld Formel die gewünschte Formel für das berechnete Feld. Beachten Sie, dass Sie aus den darunter aufgeführten Feldnamen auswählen können. In diesem Fall lautet die Formel „= Gewinn/Umsatz“. Sie können die Feldnamen entweder manuell eingeben oder auf den im Feld Felder aufgeführten Feldnamen doppelklicken.
Klicken Sie auf Hinzufügen und schließen Sie das Dialogfeld.

Sobald Sie das berechnete Feld hinzufügen, wird es als eines der Felder in der Liste der PivotTable-Felder angezeigt.

Jetzt können Sie dieses berechnete Feld wie jedes andere Pivot-Tabellenfeld verwenden (beachten Sie, dass Sie das berechnete Pivot-Tabellenfeld nicht als Berichtsfilter oder Slicer verwenden können).

Wie bereits erwähnt, besteht der Vorteil der Verwendung eines berechneten Pivot-Tabellenfelds darin, dass Sie die Struktur der Pivot-Tabelle ändern können und diese automatisch angepasst wird.

Wenn ich beispielsweise eine Region per Drag & Drop in den Zeilenbereich ziehe, erhalten Sie das unten gezeigte Ergebnis, in dem der Gewinnspannenwert sowohl für Einzelhändler als auch für die Region gemeldet wird.

Im obigen Beispiel habe ich eine einfache Formel (=Gewinn/Umsatz) verwendet, um ein berechnetes Feld einzufügen. Sie können jedoch auch einige erweiterte Formeln verwenden.

Bevor ich Ihnen ein Beispiel für die Verwendung einer erweiterten Formel zum Erstellen eines Pivot-Tabellen-Berechnungsfelds zeige, sind hier einige Dinge, die Sie wissen müssen:

Sie können beim Erstellen eines berechneten Pivot-Tabellenfelds KEINE Referenzen oder benannten Bereiche verwenden. Das würde viele Formeln wie SVERWEIS, INDEX, OFFSET usw. ausschließen. Sie können jedoch Formeln verwenden, die ohne Referenzen funktionieren (wie SUM, IF, COUNT usw.).
Sie können eine Konstante in der Formel verwenden. Wenn Sie beispielsweise den prognostizierten Umsatz mit einem prognostizierten Wachstum von 10 % ermitteln möchten, können Sie die Formel =Umsatz*1,1 verwenden (wobei 1,1 konstant ist).
Die Rangfolge wird in der Formel befolgt, die das berechnete Feld erstellt. Verwenden Sie als Best Practice Klammern, um sicherzustellen, dass Sie sich die Rangfolge nicht merken müssen.

Sehen wir uns nun ein Beispiel für die Verwendung einer erweiterten Formel zum Erstellen eines berechneten Felds an.

Angenommen, Sie verfügen über das unten gezeigte Dataset und müssen den prognostizierten Verkaufswert in der Pivot-Tabelle anzeigen.

Für den prognostizierten Wert müssen Sie eine Umsatzsteigerung von 5 % für große Einzelhändler (Umsatz über 3 Millionen) und eine Umsatzsteigerung von 10 % für kleine und mittlere Einzelhändler (Umsatz unter 3 Millionen) verwenden.

Hinweis: Die Verkaufszahlen hier sind gefälscht und wurden verwendet, um die Beispiele in diesem Tutorial zu veranschaulichen.

So geht's:

Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
Gehen Sie zu Pivot-Tabellentools -> Analysieren -> Berechnungen -> Felder, Elemente und Sätze.
Wählen Sie in der Dropdown-Liste Berechnetes Feld aus.
Im Dialogfeld Berechnetes Feld einfügen:
- Geben Sie ihm einen Namen, indem Sie ihn in das Feld Name eingeben.
- Verwenden Sie im Feld Formel die folgende Formel: =IF(Region =”South”,Sales *1.05,Sales *1.1)
Klicken Sie auf Hinzufügen und schließen Sie das Dialogfeld.

Dadurch wird der Pivot-Tabelle eine neue Spalte mit dem Verkaufsprognosewert hinzugefügt.

Klicken Sie hier, um den Datensatz herunterzuladen.

Ein Problem mit berechneten Pivot-Tabellenfeldern

Berechnetes Feld ist eine erstaunliche Funktion, die den Wert Ihrer Pivot-Tabelle mit Feldberechnungen wirklich steigert, während alles skalierbar und überschaubar bleibt.

Es gibt jedoch ein Problem mit berechneten Pivot-Tabellenfeldern, das Sie kennen müssen, bevor Sie es verwenden.

Angenommen, ich habe eine Pivot-Tabelle wie unten gezeigt, in der ich das berechnete Feld verwendet habe, um die prognostizierten Verkaufszahlen zu erhalten.

Beachten Sie, dass die Zwischensumme und die Gesamtsumme nicht korrekt sind.

Während diese den individuellen Verkaufsprognosewert für jeden Einzelhändler hinzufügen sollten, folgt er in Wirklichkeit der gleichen berechneten Feldformel, die wir erstellt haben.

Während also für South Total der Wert 22.824.000 betragen sollte, gibt South Total ihn fälschlicherweise mit 22.287.000 an. Dies geschieht, da die Formel 21.225.800 * 1,05 verwendet wird, um den Wert zu erhalten.

Leider gibt es keine Möglichkeit, dies zu korrigieren.

Der beste Weg, dies zu handhaben, besteht darin, Zwischensummen und Gesamtsummen aus Ihrer Pivot-Tabelle zu entfernen.

Sie können auch einige innovative Problemumgehungen durchgehen, die Debra gezeigt hat, um dieses Problem zu lösen.

So ändern oder löschen Sie ein berechnetes Pivot-Tabellenfeld

Nachdem Sie ein berechnetes Pivot-Tabellenfeld erstellt haben, können Sie die Formel mit den folgenden Schritten ändern oder löschen:

Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
Gehen Sie zu Pivot-Tabellentools -> Analysieren -> Berechnungen -> Felder, Elemente und Sets.
Wählen Sie aus der Dropdown-Liste Berechnetes Feld aus.
Klicken Sie im Feld Name auf den Dropdown-Pfeil (kleiner Pfeil nach unten am Ende des Felds).
Wählen Sie aus der Liste das berechnete Feld aus, das Sie löschen oder ändern möchten.
Ändern Sie die Formel, falls Sie sie ändern möchten, oder klicken Sie auf Löschen, falls Sie sie löschen möchten.

Wie erhalte ich eine Liste aller berechneten Feldformeln?

Wenn Sie viele berechnete Pivot-Tabellen-Felder erstellen, müssen Sie sich nicht darum kümmern, die in jedem Feld verwendete Formel zu verfolgen.

Mit Excel können Sie schnell eine Liste aller Formeln erstellen, die beim Erstellen von berechneten Feldern verwendet werden.

Hier sind die Schritte, um schnell die Liste der Formeln für alle berechneten Felder zu erhalten:

Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
Gehen Sie zu Pivot-Tabellentools -> Analysieren -> Felder, Elemente und Sets -> Listenformeln.

Sobald Sie auf Formeln auflisten klicken, fügt Excel automatisch ein neues Arbeitsblatt ein, das die Details aller berechneten Felder/Elemente enthält, die Sie in der Pivot-Tabelle verwendet haben.