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.
Excel Tipps & Tricks
Pivot-Tabellen in Excel 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:
Das klingt jetzt alles ein bisschen theoretisch, deswegen sehen wir uns das in Folge an einem praktischen Beispiel an:
Sehen wir uns die nebenstehende Pivot-Tabelle an. Sie enthält nur Netto-Umsätze, da in den Ausgangsdaten nicht mehr Informationen vorhanden sind.
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“.
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.
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.
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.
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:
Wie Sie sehen können Sie auch innerhalb der Pivot-Tabelle vielfältige Daten errechnen und entsprechend Ihren Bedürfnissen auswerten.
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!
Auflistung ALLER Kurse in Tabellenform
Excel Kurse ... hier klicken
Power BI Kurse ... hier klicken