15:15 Uhr
Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle
Für das Jahr 2014 sehen die Werte in unseren Beispiel wie folgt aus:
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:
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.
Im folgenden Fenster geben wir die Formel für das Feld Einnahmen_VZÄ ein.
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.
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.
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.
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 | |
|---|---|---|
| Einnahmen_VZÄ | = WENNFEHLER(Einnahmen /VZÄ;Einnahmen ) | |
|
= 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.
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.
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.
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 | |
|---|---|---|
| Einnahmen_VZÄ | = WENNFEHLER(Einnahmen /VZÄ;Einnahmen ) | |
| GRENZWERT | = 200000 | |
|
= 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.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 25.10.2015, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=653 (Abgerufen am 5.12.2024)
2 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn