Sonntag, 23. Februar 2014
17:30 Uhr
17:30 Uhr
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
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
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:
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.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
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))
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
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
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.
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 13.11.2024)
2 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn