Was bedeuten die Funktionen “Berechnetes Feld“ und „Berechnetes Element“ in Pivot Tabellen?
veröffentlicht am 10.10.2017
überprüft / aktualisiert am 02.02.2022
Die Pivot-Tabelle ist ein großartiges Werkzeug, um große vorhandene Datenmengen auszuwerten und zu analysieren.
Aber nicht nur vorhandene Daten – man kann auch innerhalb der Pivot-Tabelle Werte errechnen. Diese stehen dann zwar “nur” innerhalb dieser Pivot-Tabelle zur Verfügung, aber alleine das kann schon sehr hilfreich sein. Nicht immer hat man schließlich die Möglichkeit, bereits die Ausgangsdaten zu verändern. Klassische Anwendungen dafür sind Berechnungen mit fixen Faktoren wie z.B. die Mehrwertsteuer.
Für diese Berechnungen gibt es eben die Funktionen “Berechnetes Feld” und “Berechnetes Element”. Was diese Funktionen bedeuten und wie man sie zielführend anwendet erklären wir in diesem Blogbeitrag an einem praxisnahen Beispiel.
Ein paar grundlegende Gedanken
Pivot-Tabellen sind Kreuztabellen, die Auswertungen finden also kombiniert auf Spalten- und Zeilen-Ebene statt.
In den Spalten, auch Felder genannt, befinden sich jeweils inhaltlich unterschiedliche Informationen.
In den Zeilen befinden sich die Zusammenfassungen dieser Informationen, auch Aggregationen genannt. Das sind dann z.B. Summen, Anzahl, Anteile etc.
In der gleichen Weise unterscheiden sich somit auch die Berechnungsmöglichkeiten in Pivot-Tabellen:
- Berechnete Felder fügen weitere Spalten bzw. Felder mit Informationen zur Pivot-Tabelle hinzu, die bisher nicht enthalten waren.
- Berechnete Elemente fügen weitere Zeilen zur Pivot-Tabelle hinzu. Es werden also weitere Aggregation zu bereits bestehenden Informationen errechnet.
Das klingt jetzt alles ein bisschen theoretisch, deswegen sehen wir uns das in Folge an einem praktischen Beispiel an:
Ein berechnetes Feld einfügen
Sehen wir uns die nebenstehende Pivot-Tabelle an. Sie enthält nur Netto-Umsätze, da in den Ausgangsdaten nicht mehr Informationen vorhanden sind..
Pivot-Tabelle nur mit Netto-Umsätzen
Um jetzt 20% Mehrwertsteuer hinzuzufügen, müssen Sie diese Zahlen mit 1,2 multiplizieren. Das ergibt dann eine weitere Spalte in unserer Pivot-Tabelle – sie fügen also ein errechnetes Feld hinzu.
Um diese Funktion aufzurufen klicken Sie in die Pivot-Tabelle und gehen Sie im Register “PivotTable-Analyse” in der Gruppe “Berechnungen” im Punkt “Felder, Elemente und Gruppen” auf “Berechnetes Feld”.
Dialog “Berechnetes Feld einfügen”
Dort vergeben Sie dann einen Namen für das neue Feld, in unserem Beispiel “Umsätze brutto”, und legen die Formel zur Berechnung fest. Nachdem Sie auf OK geklickt haben, finden Sie in der Feldliste der Pivot-Tabelle das neue Feld “Umsätze netto” und können es – wie Sie nachstehend abgebildet – in die Pivot-Tabelle einfügen und gegebenenfalls die Netto-Spalte wieder entfernen.
Pivot-Tabelle nur mit Brutto-Umsätzen
Sie sehen, die errechneten Werte stehen Ihnen nun als neue Spalte zur Verfügung. Die Daten können wie alle anderen Daten verwendet werden. Einzig ein “zurückschreiben” der Werte in die Ausgangsdaten ist nicht möglich.
Ein berechnetes Element einfügen
Während berechnete Felder der Pivot-Tabelle weitere, “virtuelle” Spalten hinzufügt, ergänzen berechnete Elemente einen Wert in einer bestehenden Spalte. Im Grunde verlängern berechnete Elemente die Pivot-Tabelle also.
Schauen wir uns das an einem praktischen Beispiel an. Wir bleiben dazu bei der oben schon verwendeten Pivot-Tabelle.
Im errechneten Feld “Umsätze brutto” hat sich ein Fehler eingeschlichen: In München beträgt die Mehrwertsteuer nur 19%. Wir müssen daher eine Auswahl “München 19%” erstellen, um korrekte Werte zu erhalten.
Wir markieren also München in der Pivot-Tabelle und rufen – wie oben beschrieben – den Dialog “Berechnetes Element” auf.
Dialog “Berechnetes Element einfügen”
Hier fügen wir die korrekte Berechnung ein, indem wir die Werte für München durch 1,2 dividieren und dann wieder mit 1,19 multiplizieren.
In der Pivot-Tabelle entsteht dadurch eine neue Zeile “München 19%”, die den korrekten Bruttobetrag anzeigt. Die falsche Zeile kann nun mit dem Filter ausgeblendet werden und die Pivot-Tabelle zeigt uns ein korrektes Endergebnis:
Pivot-Tabelle mit korrigierten Brutto-Umsätzen
Wie Sie sehen können Sie auch innerhalb der Pivot-Tabelle vielfältige Daten errechnen und entsprechend Ihren Bedürfnissen auswerten.
Noch ein Praxistipp vom Profi:
Eine sauber formatierte Pivot-Tabelle ist auf jeden Fall noch verständlicher und übersichtlicher.
Vergeben Sie im Register “PivotTable-Entwurf” ein farbiges Format und wählen Sie z.B. beim Berichtslayout einmal testweise das Tabellenformat aus.
Erforschen Sie die Möglichkeiten dieses Registers – es lohnt sich!
Wählen Sie Ihren Kurs …
