So fügen Sie berechnete Excel-PivotTable-Felder hinzu und verwenden sie

Nachdem Sie eine Pivot-Tabelle erstellt haben, müssen Sie normalerweise Ihre Analyse erweitern und weitere Daten/Berechnungen darin aufnehmen.

Wenn Sie einen neuen Datenpunkt benötigen, der mithilfe eines vorhandenen Datenpunkts in der Pivot-Tabelle abgerufen werden kann, müssen Sie nicht zurückgehen und ihn den Quelldaten hinzufügen.Stattdessen können Sie verwendenBerechnete PivotTable-Felderum dies zu tun.

Was ist ein berechnetes PivotTable-Feld?

Beginnen wir mit einem einfachen Beispiel einer Pivot-Tabelle.

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

Die obige Pivot-Tabelle fasst die Verkaufs- und Gewinnwerte des Einzelhändlers zusammen.

Nun, was wäre, wenn Sie auch wissen wollten, wie hoch die Gewinnspannen dieser Einzelhändler waren (die Gewinnspanne ist „Gewinn“ geteilt durch „Umsatz“)?

Dazu gibt es mehrere Möglichkeiten:

1. Gehen Sie zurück zum ursprünglichen Datensatz und fügen Sie diesen neuen Datenpunkt hinzu.Sie können also eine neue Spalte in die Quelldaten einfügen und darin die Gewinnmarge berechnen.Sobald dies erledigt ist, müssen Sie die Quelldaten der Pivot-Tabelle aktualisieren, um diese neue Spalte als Teil davon zu erhalten.
   • Während dieser Ansatz funktioniert, müssen Sie manuell zum Datensatz zurückkehren und die Berechnungen durchführen.Beispielsweise möchten Sie möglicherweise eine weitere Spalte hinzufügen, um den durchschnittlichen Umsatz pro Einheit (Umsatz/Menge) zu berechnen.Auch hier müssen Sie diese Spalte zu den Quelldaten hinzufügen und dann die Pivot-Tabelle aktualisieren.
   • Diese Methode bläht auch Ihre Pivot-Tabelle auf, wenn Sie ihr neue Daten hinzufügen.
2. Fügen Sie Berechnungen außerhalb von Pivot-Tabellen hinzu.Wählen Sie diese Option, wenn sich Ihre Pivot-Tabellenstruktur voraussichtlich nicht ändern wird.Wenn Sie jedoch die Pivot-Tabelle ändern, werden die Berechnungen möglicherweise nicht entsprechend aktualisiert und Sie erhalten möglicherweise falsche Ergebnisse oder Fehler.Wie unten gezeigt, habe ich die Gewinnmarge berechnet, wenn sich Einzelhändler in der Reihe befinden.Aber wenn ich es von Kunde zu Bereich ändere, geht die Formel schief.
3. Berechnen Sie Felder mithilfe von Pivot-Tabellen.das istVerwenden Sie vorhandene PivotTable-Daten und berechnen Sie dieeffizienteste Weg.Stellen Sie sich berechnete Felder als virtuelle Spalten vor, die Sie mithilfe vorhandener Spalten in Ihrer Pivot-Tabelle hinzufügen.Die Verwendung von berechneten Pivot-Tabellenfeldern hat viele Vorteile (wie wir später sehen werden):
   • Sie müssen nicht mit Formeln arbeiten oder Quelldaten aktualisieren.
   • Es ist erweiterbar, da es automatisch alle neuen Daten berücksichtigt, die Sie möglicherweise zur Pivot-Tabelle hinzufügen.Nachdem Sie ein berechnetes Feld hinzugefügt haben, können Sie es wie jedes andere Feld in einer Pivot-Tabelle verwenden.
   • Es ist einfach zu aktualisieren und zu verwalten.Wenn sich beispielsweise eine Metrik ändert oder Sie eine Berechnung ändern müssen, können Sie dies ganz einfach über die Pivot-Tabelle selbst tun.

Berechnete Felder zur Pivot-Tabelle hinzufügen

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

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

Hier sind die Schritte zum Hinzufügen eines berechneten PivotTable-Felds:

Nachdem Sie das berechnete Feld hinzugefügt haben, wird es als eines der Felder in der Liste der PivotTable-Felder angezeigt.

Sie können dieses berechnete Feld jetzt wie jedes andere Pivot-Tabellenfeld verwenden (beachten Sie, dass Sie ein berechnetes Pivot-Tabellenfeld nicht als Berichtsfilter oder Datenschnitt verwenden können).

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

Wenn ich beispielsweise die Region per Drag & Drop in den Zeilenbereich ziehe, erhalten Sie Ergebnisse wie die folgenden, in denen die Gewinnspannenwerte für den Einzelhändler und die Region gemeldet werden.

 

Im obigen Beispiel habe ich eine einfache Formel (=Gewinn/Umsatz) verwendet, um das berechnete 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 berechneten PivotTable-Felds zeige, sind hier einige Dinge, die Sie wissen müssen:

• Beim Erstellen von berechneten PivotTable-Feldern können Sie keine Verweise oder benannten Bereiche verwenden.Dadurch werden viele Formeln wie SVERWEIS, INDEX, OFFSET usw. ausgeschlossen.Sie können jedoch Formeln verwenden, die ohne Referenzen funktionieren (zB SUMME, WENN, ANZAHL, etc...).
• Sie können Konstanten in Formeln verwenden.Wenn Sie beispielsweise das prognostizierte Umsatzwachstum wissen möchten, das voraussichtlich um 10 % steigen wird, würden Sie die Formel =Umsatz*1.1 verwenden (wobei 1.1 eine Konstante ist).
• Befolgen Sie die Prioritätsreihenfolge in Formeln, die berechnete Felder generieren.Verwenden Sie als Best Practice Klammern, um sicherzustellen, dass Sie sich nicht an die Priorität erinnern müssen.

Sehen wir uns nun ein Beispiel für die Erstellung eines berechneten Felds mithilfe einer erweiterten Formel an.

Angenommen, Sie haben den unten gezeigten Datensatz und müssen die prognostizierten Verkäufe in einer Pivot-Tabelle anzeigen.

Als Prognosewerte würden Sie eine Umsatzsteigerung von 300 % für große Einzelhändler (über 5 Millionen Verkäufe) und eine Umsatzsteigerung von 300 % für kleine und mittlere Einzelhändler (weniger als 10 Millionen Verkäufe) verwenden.

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

So geht's:

Dadurch wird der PivotTable eine neue Spalte mit Verkaufsprognosewerten hinzugefügt.

Problem mit berechneten Feldern in der Pivot-Tabelle

Berechnete Felder sind eine erstaunliche Funktion, die den Wert von Pivot-Tabellen mit Feldberechnungen wirklich erhöht und gleichzeitig alles skalierbar und überschaubar hält.

Es gibt jedoch ein Problem mit berechneten PivotTable-Feldern, das Sie verstehen müssen, bevor Sie sie verwenden.

Angenommen, ich habe eine Pivot-Tabelle wie unten und verwende ein berechnetes Feld, um prognostizierte Verkaufszahlen zu erhalten.

Beachten Sie, dass die Zwischensummen und Summen falsch sind.

Während diese separate Verkaufsprognosewerte für jeden Einzelhändler hinzufügen sollten, folgt sie in Wirklichkeit der gleichen berechneten Feldformel, die wir erstellt haben.

Während der Wert für South Total also 22,824,000 lauten sollte, gibt South Total ihn fälschlicherweise als 22,287,000 aus.Dies geschieht, weil es die Formel 21,225,800 * 1.05 verwendet, um den Wert zu erhalten.

Leider können Sie dieses Problem nicht beheben.

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

Du kannst auch passieren Präsentiert von DebraEinige innovative Problemumgehungen, um dieses Problem zu lösen.

Wie ändere oder lösche ich ein berechnetes Feld einer Pivot-Tabelle?

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

Wie erhalte ich eine Liste aller berechneten Feldformeln?

Wenn Sie viele berechnete PivotTable-Felder erstellen, müssen Sie sich keine Gedanken darüber machen, welche Formeln in den einzelnen Feldern verwendet werden.

Mit Excel können Sie schnell eine Liste aller Formeln erstellen, die zum Erstellen berechneter Felder verwendet werden.

Hier sind die Schritte, um schnell eine Liste aller berechneten Feldformeln zu erhalten:

• Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
• Gehen Sie zu PivotTable-Tools -> Analysieren -> Felder, Elemente und Sätze -> Formeln auflisten.

Sobald Sie auf die Listenformel klicken, fügt Excel automatisch ein neues Arbeitsblatt mit Details aller berechneten Felder/Elemente ein, die Sie in der Pivot-Tabelle verwendet haben.

Dies kann ein sehr nützliches Tool sein, wenn Sie Arbeit an einen Kunden senden oder mit Ihrem Team teilen müssen.