Andreas Unkelbach
Werbung
Steuern, Selbstständigkeit und VGWORT als Blogger und Autor


Sonntag, 23. Februar 2014
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:
Grundtabelle für Pivotauswertung
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:
Datenbereich dynamisch festlegen durch BEREICH.VERSCHIEBEN

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.

Pivottabelle einfügen

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.

PivotTabelle Feldliste

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

Zugewiesene Pivotfelder

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).

Wertfeldeinstellungen

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.

Berechnetes Feld

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

Formel für berechnetes Feld

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.

Neues Feld

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.

Formeln auflisten

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

Liste berechneter Formeln im Tabellenblatt

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 Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

2 Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Jyoti am 4.4.2014 um 07:42 Uhr
Sehr schf6n, toller Tipp. The Junction = Coole Ideen + klsase Bilder : kleine Artikel, ffcr die man nicht den ganzen Nachmittag Zeit braucht von am 10.03.09


Anonym am 28.8.2015 um 15:35 Uhr
Sehr hilfreich!


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.

Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste