Excel Berechnete Felder in Pivottabellen

Ausgangslage
In einer Exceltabelle sind für verschiedene Kostenstellen entweder das Budget oder der gebuchte Aufwand je Geschäftsjahr in einer Tabelle angegeben. Nun soll eine entsprechende Zusammenfassung des Ergebnis pro Geschäftsjahr ausgegeben werden.

Lösung
Das ideale Werkzeug hierfür ist die Anwendung von Pivottabellen.

Die Ausgangsdaten sehen zum Beispiel wie folgt aus:

Innerhalb des Tabellenblattes sind in der Spalte A das Jahr, B die Kostenstelle, C das Budget und in D der im jeweiligen Jahr gebuchte Aufwand zu finden.

Da diese Tabelle fortgeschrieben wird, ist es nicht sicher, bis wohin die Daten gefüllt werden.

1. Grunddaten festlegen
Wie im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche unter "Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen" beschrieben kann es, sofern weitere Daten in der Liste erwartet werden, sinnvoll sein, die Datengrundlage der Pivottabelle entsprechend anzupassen.


Im Beispiel kann innerhalb des Ribbon "Formeln" in der Befehlsgruppe "Definierte Namen" ein entsprechender Name definiert werden (etwas ausführlicher ist dieses auch im Artikel Formulare gestalten in Excel erläutert).

Hierbei wird der "dynamische Datenbereich" wie folgt festgelegt:


Durch die Formel

• =Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))

wird den Namen Grunddaten automatisch der Inhalt der Tabelle im Blatt Grundtabelle zugewiesen. Dank der Formel wird der Bereich von der Zelle A1 bis zur Zelle D8 festgelegt (entsprechend ist dieser Bereich auch mit einer gestrichelten Linie von Excel hinterlegt).

Hierbei werden die Anzahl der Zeilen aus der Spalte A und die Anzahl der Spalten aus der Zeile 1 ermittelt.

Sofern nun weitere Daten hinzukommen wird der Bereich entsprechend angepasst.


2. Pivottabelle anlegen

Innerhalb des Ribbon "Einfügen" kann nun eine Pivottabelle eingefügt werden.



Als Bereich der zu analysierenden Daten wird nun der vorher angelegte Name durch

• =Grunddaten

eingetragen.

Auf diese Weise wird die Datengrundlage der Pivottabelle autmatisch erneut berechnet, sollten weitere Datenzeilen eingefügt werden.



Innerhalb des Spaltenvorrates können nun die einzelnen Datenfelder aus der Feldliste Als Spalten, Zeilen oder Werte einer Pivottabelle übernommen werden.



Innerhalb der unter Werte angegebenen Felder können über die Wertfeldeinstellungen festgelegt werden, wie diese Daten ausgegeben werden (die häufigst genutzten Methoden dürften wohl Summe, Anzahl oder auch Mittelwert sein).



Im Ergebnis sind nun nach Jahren Budget und Aufwand je Kostenstelle ausgegeben. Um nun das noch verfügbare Budget zu berechnen kann ein berechnetes Feld im Ribbon "Optionen" in der Befehlsgruppe "Berechnung" über "Felder, Elemente und Gruppen" ein "Berechnetes Feld" eingefügt werden.



Bei der Definition dieses Feldes kann dann auf die anderen Felder zugegriffen werden.



Im Beispiel hat das berechnete Feld den Namen "Verfügbar" und berechnet sich aus der Formel:

• =Budget - Aufwand

Sofern diese Formel definiert ist, kann das Feld durch "Hinzufügen" aktiviert werden und befindet sich nun ebenfalls in der Feldliste.



Im Beispiel kann nun in der Pivotabelle auch die Summe über Verfügbar ermittelt werden, so dass hier für 2013 ersichtlich ist, dass die Kostenstelle A noch 350 zur Verfügung hat (Budget in Höhe von 1000 abzüglich Aufwand in Höhe von 650).

Diese Berechnungen können natürlich auch mit anderen Formeln genutzt werden. So könnten bei Preisen die Bruttopreise aus den Nettopreisen ermittelt werden, oder eine prozentuale Lohnsteigerung berücksichtigt werden.

Sofern sich ein Überblick über die berechneten Felder geschafft werden soll ist dieses ebenfalls im Ribbon Optionen der Pivot-Tabelle unter "Berechnung" bei "Felder, Elemente und Gruppen" durch die Option "Formeln auflisten" möglich.



Hierdurch wird ein neues Tabellenblatt angelegt in dem alle berechnete Formeln aufgeführt sind.



Dieses Tabellenblatt kann dann auch direkt als Dokumentation verwendet werden.

Ein weiteres (umfangreicheres) Beispiel für berechnete Felder innerhalb Pivot-Tabellen ist im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlich beschrieben. Hier werden auch innerhalb der berechneten Felder passende Formeln verwendet.

2x

SAP Weiterbildung
ein Angebot von Espresso Tutorials


unkelbach.link/et.books/

unkelbach.link/et.reportpainter/

unkelbach.link/et.migrationscockpit/

Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel Berechnete Felder in Pivottabellen« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 23.2.2014, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=508 (Abgerufen am 23.11.2024)