Berechnete Felder in der Excel Pivot Tabelle: Deckungsbeiträge, Verkaufsprovisionen & mehr

Posted by sebastian.zang on May 13th, 2013

Mit der Excel Pivot Tabelle können Sie Daten nicht nur analysieren. Sie können auf Basis von Rohdaten auch neue Kennzahlen berechnen: Deckungsbeiträge, Umsatzabhängige Verkaufsprovisionen und Vieles mehr. Nutzen Sie dazu „Berechnete Felder“ oder aber „Berechnete Elemente“. Wir stellen Ihnen die Technik der „Berechneten Felder“ für Ihre Excel Pivot Tabelle vor.

Expertentipps von Categis: Exzellente Excel Tools für effiziente Geschäftsprozesse

Berechnete Felder in der Excel Pivot Tabelle – So geht’s!

Berechnete Felder schaffen neue „Datendimensionen“ [im Gegensatz zu „Berechneten Elementen“, die neue „Elemente“ zu einer bestehenden Datendimension hinzufügen]. Gehen wir für Demonstrationszwecke von einer einfachen Excel Pivot Tabelle aus, die für verschiedene Verkäufer Verkaufsumsätze ausweist – differenziert nach Produktkategorie, Jahr sowie Budget-/Ist-Werten.

Excel Programmierer Pivot Tabelle Berechnete Felder_118_01

Nehmen wir an, Sie möchten als Controller statt der Umsätze die Deckungsbeiträge in dieser Excel Pivot Tabelle sehen. Die Deckungsbeiträge liegen einheitlich bei 25% (falls diese je Produktkategorie differieren, müssen Sie „Berechnete Elemente“ nutzen. Vergleiche hierzu unseren Blogbeitrag Berechnete Elemente in der Excel Pivot Tabelle).

Platzieren Sie Ihren Cursor innerhalb der Excel Pivot Tabelle, so dass das Register „PivotTable Optionen“ eingeblendet wird. Wählen Sie hier die Schaltfläche „Felder, Elemente und Gruppen“ aus. Im Drop-Down-Menü selektieren Sie „Berechnetes Feld“:

Excel Programmierer Pivot Tabelle Berechnete Felder_118_02

Ein Dialog wird eingeblendet, der wie folgt aussieht. Hier vergeben Sie zunächst einen Namen für das neue „Berechnete Feld“ Ihrer Excel Pivot Tabelle. Erfassen Sie dann im „Formel“-Feld die Formel, mittels derer Sie den gewünschten Wert ermitteln. [Per Doppelklick auf eines der existierenden Felder wird Ihnen dieser „Feld“-Name in das „Formel“-Feld eingetragen.]

Excel Programmierer Pivot Tabelle Berechnete Felder_118_03

Sobald Sie die obige Eingabe abgeschlossen haben, bestätigen Sie die Eingabe mit „OK“, dann erhalten Sie folgende Excel Pivot Tabelle als Ergebnis [Zwecks Übersichtlichkeit wurden kleinere Re-Formatierungen vorgenommen].

Excel Programmierer Pivot Tabelle Berechnete Felder_118_04

Berechnete Felder in der Excel Pivot Tabelle – Vorsicht!

Es zählt zu den bekannten Schwächen der „Berechneten Felder“ der Excel Pivot Tabellen, dass Teil- bzw. Gesamtergebnisse nicht immer korrekt wiedergegeben werden KÖNNEN. Dies ist NICHT ZWANGSLÄUFIG zwangsläufig der Fall, sondern tritt als „Bug“ meist dann auf, wenn komplexere Formeln genutzt werden. Dazu zählen beispielsweise schon WENN-Verformelungen.

Betrachten wir unseren Beispielfall: Die Teil- bzw. Gesamtergebnisse für das obige Beispiel werden in der Excel Pivot Tabelle richtig ausgewiesen. Denn das „Berechnete Feld“ ergibt sich mit einer ausgesprochen simplen Berechnung: „=Umsatz*0,25“.

Wenn Sie stattdessen Verkaufsprovisionen berechnen, die von der Umsatzhöhe abhängen [z.B. „=WENN(Umsatz=>100000;Umsatz*0,25;Umsatz*0,2) ist damit zu rechnen, dass die Teil- bzw. Gesamtergebnisse nicht korrekt ausgegeben werden. Dies MUSS NICHT DER FALL sein – wir empfehlen Ihnen aber: Prüfen Sie die Teil- und Gesamtergebnisse auf Richtigkeit, wenn Sie komplexere Formeln für „Berechnete Felder“ verwenden.

Wann ein Excel Programmierer Sinn macht: Gerade angesichts dieser dargestellten Problematik kann es Sinn machen, bei komplexeren Berechnungen auf ein „Berechnetes Feld“ zu verzichten. Die Ermittlung erforderlicher Kennzahlen sollte für diesen Fall direkt in den Rohdaten erfolgen. Hier kann ein Excel Programmierer eine einfaches Makro für Sie bereitstellen, das die erforderliche Anpassung der Rohdaten mit einem einzigen Klick bereitstellt. Hierzu beraten wir Sie gerne ganz unverbindlich.

Berechnete Felder in der Excel Pivot Tabelle – Mehr…

Auf diesem Best Practice Forum finden Sie noch weitere Expertentipps für smarte Excel Tools mit oder ohne Excel Pivot Tabelle. Schauen Sie sich beispielsweise auch folgende Beiträge an:

Auf unserer Hauptwebseite erfahren Sie, wie ein Expertenteam passionierter Excel Programmierer Sie ON-DEMAND bei der Optimierung/Automatisierung von bestehenden Excel Tools unterstützen kann: Beschleunigte Geschäftsprozesse mithilfe erfahrener Excel Programmierer.



Diesen Artikel kommentieren

Kommentar

Kommentieren Sie diesen Artikel

*