Andreas Unkelbach
Werbung


Sonntag, 25. Oktober 2015
15:15 Uhr

Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle

Die Einnahmen eines Jahres werden auf Innenaufträgen erfasst, die über die verantwortliche Kostenstellen einzelnen Abteilungen zugeordnet sind.  In jeder Abteilung beziehungsweise auf jeder Kostenstelle sind entsprechende Personen beschäftigt, die für diese Einnahmen verantwortlich sind. Diese sind auf den einzelnen Kostenstellen über die Kennzahl VZÄ oder FTE erfasst so dass im Rahmen einer Auswertung sowohl eine Liste der Personen auf Kostenstellen, als auch der Einnahmen auf einzelne Innenaufträge für den entsprechenden Zeitraum vor liegen.

Für das Jahr 2014 sehen die Werte in unseren Beispiel wie folgt aus:

Übersicht Jahr-Auftrag-Kostenstelle-Einnahmen-VZÄ

Anhand der Zahlen soll über einen Grenzwert in Höhe von 20.000 € bestimmt werden, welche Anzahl an Personen oberhalb dieses Wertes Einnahmen erzielt haben, welche unterhalb dieses Wertes Einnahmen erzielten und welche gar keine Einnahmen erzielt haben.

Durch Ausscheiden von Personen kann es auch passieren, dass (bedingt durch eine Stichtagsbetrachtung) dieser Schwellenwert für eine Kostenstelle tatsächlich überschritten wurde, aber im fraglichen Zeitraum gar keine Person auf dieser Kostenstelle beschäftigt war. Auch dieses soll entsprechend festgehalten werden.

Im ersten Moment bietet sich hier eine Pivottabelle an über die je Kostenstelle sowohl die Summe der Einnahmen als auch die besetzten Personen ausgewiesen werden.

Diese könnte dann wie folgt aussehen:

Pivottabelle Kostenstelle-Einnahmen-VZÄ

Hier sind nun je Kostenstelle die Summe der Einnahmen und auch die besetzten Stellen in Form der VZÄ zu sehen.

Nun könnte direkt Einnahmen / VZÄ manuell errechnet werden und die entsprechende Fragestellung beantwortet werden. Eine schönere Alternative ist es natürlich, wenn Excel selbst hier eine passende Antwort geben würde.

Die Schwierigkeit dabei ist jedoch, dass an manchen Kostenstellen tatsächlich Einnahmen erzielt werden, aber 0 Personen beschäftigt sind. Dieses würde bei einer einfachen Berechnung die Fehlermeldung #DIV/0! herovrrufen.

Um dieses zu vermeiden besteht in Excel allerdings auch die Möglichkeit über entsprechende Formeln diese Fehler abzufangen.

Hierzu verwenden wir, wie im Artikel "Excel Berechnete Felder in Pivottabellen" beschrieben die Möglichkeit innerhalb einer Pivottabelle eigene Felder ergänzend zu den Grunddaten anzulegen.

Hierzu legen wir insgesamt fünf berechnete Felder an, wobei das erste ein Hilfsfeld ist, welches wir in der späteren Pivottabelle ausblenden bzw. nicht in der Feldliste aufführen werden.

In der erstellten Pivottabelle wird über Ribbon "Optionen" in der Befehlsgruppe "Berechnung" über "Felder, Elemente und Gruppen" ein "Berechnetes Feld" eingefügt.

Pivottabelle berechnetes Feld einfügen

Im folgenden Fenster geben wir die Formel für das Feld Einnahmen_VZÄ ein.
Berechnung Einnahmen je Person bzw. VZÄ

Durch die Formel WENNFEHLER werden entweder die Einnahmen je VZÄ durch Division ermittelt, oder wenn die Kostenstelle keine VZÄ (und somit keine Person vorhanden ist) direkt die Einnahmen ausgegeben. Dieses ist nachher noch wichtig  um ermitteln zu können, welche Kostenstelle die Wertgrenze überschritten hat, obgleich keine Person beschäftigt war.

Im nächsten Schritt definieren wir ein Feld, dass die Anzahl der Personen der Kostenstelle ausgeben soll, sofern der Wert Einnahmen_VZÄ (also Einnahmen pro Person) oberhalb oder gleich des definierten Grenzwertes in Höhe von 20.000 liegt.

= WENN(UND( Einnahmen_VZÄ>20000-0,01; VZÄ>0,1); VZÄ;0)

Die hinter diesen Wert liegende Formel lautet:

= WENN(UND( Einnahmen_VZÄ>20000-0,01; VZÄ>0,1); VZÄ;0)

Innerhalb der WENN Funktion wird entweder der Wert VZÄ ausgegeben, wenn sowohl die Einnahmen größer als 20.000 - 0,01 sind und die VZÄ größer als 0,1 ist.

Auf ebensolche Weise geben wir noch folgende Felder ein die ich als Formelliste aufgeführt habe.
Liste berechneter Formeln

Dieser  Überblick über die berechneten Felder kann ebenfalls im Ribbon Optionen der Pivot-Tabelle unter "Berechnung" bei "Felder, Elemente und Gruppen" durch die Option "Formeln auflisten" erstellt werden.

Formeln auflisten

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


Der Übersicht halber sind die einzelnen Formeln folgender Tabelle zu entnehmen:
 

FELD FORMEL
Liste berechneter Felder
Einnahmen_VZÄ = WENNFEHLER(Einnahmen /VZÄ;Einnahmen )
VZÄ_>Grenzwert
= WENN(UND(Einnahmen_VZÄ >20000-0,01;VZÄ >0,1);VZÄ;0)
ohneVZÄ_>Grenzwert = WENN(UND(Einnahmen_VZÄ>20000-0,01;VZÄ<0,1);VZÄ;0)
VZÄ_<Grenzwert = WENN(UND(Einnahmen_VZÄ >0,1;Einnahmen_VZÄ<20000-0,01;VZÄ>0,1);VZÄ;0)
VZÄ_ohne-Einnahmen =WENN(UND(Einnahmen_VZÄ <0,1;VZÄ >0,1);VZÄ;0)

Im Ergebnis haben wir nun eine Pivottabelle in der auch die einzelnen Ergebnisse auf Ebene der Person gewertet werden. Durch eine bedingte Formatierung, können die einzelnen Spalten, sofern diese > 0,1 sind auch farblich hervorgehoben werden. Ferner besteht die Möglichkeit auch eine passende Summenformel in der Tabelle aufzuführen.

Dieses ist am Einfachsten über Ribbon Start innerhalb der Gruppe Formatierung, durch Bedingte Formatierung und hier die "Regel zum Hervorheben von Zellen" über die Bedingung "Größer als" möglich, wie in der folgenden Abbildung zu sehen.

Bedingte Formatierung zum Hervorheben Einnahmen oberhalb Grenzwert je Person

Ebenso kann auch mit den anderen Feldern verfahren werden, so dass im Ergebnis auf einen Blick die entsprechenden Personen ersichtlich sind. Die Beschriftung der einzelnen Felder kann in der Wertfeldeinstellung noch angepasst werden.

Darstellung Einnahmen nach VZÄ und Berücksichtigung Grenzwert

Die auf diese Weise erhobenen Daten können dann, zum Beispiel in Form eines Mehrjahresvergleich, als ein 3D-Säulendiagramm dargestellt werden, so dass die einzelnen Daten hintereinander dargestellt werden.

3D-Säulen Diagramm

Dieses 3D-Säulendiagramm ist vergleichbar erstellt worden, wie im Artikel "Gestapelte Säulendiagramme in Excel mit Summenausweis"  beschrieben. Die hinterste Spalte (Gesamtzahl) wurde ausgeblendet, so dass in der unter den Diagramm befindlichen Tabelle die Gesamtzahl der VZÄ ausgegeben werden kann, ohne dass diese als separate "sichtbare" Säule im entsprechenden Diagramm dargestellt wird. Durch die 3D Darstellung ist auch eine entsprechende Entwicklung in den Einnahmen / Person ersichtlich. Allerdings kann nur in der oberen (Pivot)Tabelle abgelesen werden um welche Kostenstelle es sich handelt, da das Diagramm lediglich eine Gesamtsicht über alle relevanten Kostenstellen liefert.
 

Nachtrag:

Wesentlich eleganter kann natürlich auch der GRENZWERT als berechnetes Feld gepflegt werden, so dass die Formeln wie folgt anzupassen sind. Künftig brauchen dann nicht mehr die einzelnen Formeln angepasst werden sondern lediglich das berechnete Feld "GRENZWERT".
bersicht halber sind die angepassten Formeln folgender Tabelle zu entnehmen:
 
FELD FORMEL
Liste berechneter Felder
Einnahmen_VZÄ = WENNFEHLER(Einnahmen /VZÄ;Einnahmen )
GRENZWERT = 200000
VZÄ_>Grenzwert
= WENN(UND(Einnahmen_VZÄ >GRENZWERT-0,01;VZÄ >0,1);VZÄ;0)
ohneVZÄ_>Grenzwert = WENN(UND(Einnahmen_VZÄ>GRENZWERT-0,01;VZÄ<0,1);VZÄ;0)
VZÄ_<Grenzwert = WENN(UND(Einnahmen_VZÄ >0,1;Einnahmen_VZÄ<GRENZWERT-0,01;VZÄ>0,1);VZÄ;0)
VZÄ_ohne-Einnahmen =WENN(UND(Einnahmen_VZÄ <0,1;VZÄ >0,1);VZÄ;0)

Das Ergebnis wäre identisch, jedoch müsste für andere Bereiche die Formel nur im berechneten Feld Grenzwert angepasst werden.
Dieses kann besonders dann interessant sein, wenn der Grenzwert entsprechend häufig angepasst werden muss. Leider ist es jedoch nicht möglich den Grenzwert durch einen Feldbezug festzulegen, da keine Tabellenbezüge, Namen und Arrays innerhalb Pivottabellenformeln unterstützt werden.

Datenanalyse mit Excel

Weitere Artikel rund um die Datenanalyse mit Excel sind auch in den Artikeln "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen", "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" und "Gestapelte Säulendiagramme in Excel mit Summenausweis" zu finden.
 

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 Berichtswesen

2 Kommentare - - Office

Artikel datenschutzfreundlich teilen

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


Kommentare

E. Rodich am 25.4.2016 um 18:32 Uhr
Ich bin ein ganz normaler Excel-Anwender und verbuche meine
Ausgaben-Einnahmen über eine Tabelle.
Nun habe ich mich mit der Pivot-Tabelle befasst und es klappt
wunderbar mit den Ausgaben.
Ich gebe ein Datum- Kathegorie - Betrag.
Leider gelingt mir es nicht Einnahmen einzufügen.
Für eine Hilfe wäre ich Ihnen sehr dankbar.
Mfg
E. Rodich


Andreas Unkelbach Homepage am 25.4.2016 um 19:08 Uhr
Hallo E.Rodich,

problematisch dürfte sein, dass du in deiner Grundtabelle nicht Einnahmen und Ausgaben unterscheidest.

Hier würde ich zwei Wege sehen, wie du das von dir gewünschte umsetzen kannst.

Sofern anhand der Kategorie zwischen Einnahmen und Ausgaben unterschieden werden kann, würde ich hier deine Einnahmen mit einen anderen Vorzeichen als deine Ausgaben verbuchen, so kann dann die Pivottabelle problemlos einen Saldo bilden.

Die Frage wäre halt, wie die Pivot Tabelle aufgebaut sein soll. Welche Information soll in die Spaltenüberschrift, was in die Zeilen und welche Werte sollen mit ausgegeben werden.

Sofern einfach Einnahmen und Ausgaben ausgewertet werden sollen, ist es vermutlich die einfachste Möglichkeit das Feld Betrag in zwei weitere Felder Betrag_Einnahmen und Betrag_Ausgaben aufzuteilen und darüber eine Pivottabelle zu bilden.

Ein sehr guter Grundlagenartikel zu Pivottabellen ist beim tabellenexperten unter http://www.tabellenexperte.de/pivot-tabellen/ zu finden. :-) Möglicherweise hilft dieser ja schon ein wenig weiter um das Grundprinzip von Pivottabellen zu verstehen. Ansonsten schiebe ich das Thema "Einnahmen-Ausgaben-Rechnung mit Pivottabelle" ebenfalls einmal in die Entwurfsbox für einen zukünftigen Artikel :-)

Viele Grüße
Andreas Unkelbach


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert (siehe auch XII. Fremde Nutzung / Kommentarfunktion 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):


Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht):

Homepage:

Kommentar:


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