Berechnete Felder in Pivot Tabellen sind äußerst hilfreich wenn man größere Datenmengen analysieren möchte.
Die Benutzerdefinierten berechneten Felder waren schon seit Excel 97 möglich (nebst den Standard Berechnungsfelder wie z.B. Prozent der Gesamtsumme, Durchschnittswerte, etc.), aber wirklich benutzerfreundlich ist das ganze erst seit Excel 2007!
Ein berechnetes Feld in einer Pivot Tabelle einfügen
Wir nehmen uns also eine Liste und erstellen damit eine Pivot Tabelle. Zum selber anschauen könnt Ihr die Beispiele in der hier runter laden:
Hier ein Auszug der Daten aus denen eine Pivot Tabelle erstellt wird:
Wie Ihr seht handelt es sich lediglich um eine Liste mit Personen deren Gewicht und Größe erfasst wurde. Unser Ziel ist es nun ein Berechnetes Feld in eine Pivot einzufügen welches den BMI (Body-Mass-Index) auswertet gemäss der Formel:
BMI = Masse in Kg / (Länge in m)^2
Man erstellt aus diesen Daten eine Pivot Tabelle. Wenn man nun irgendwo in die erstellte Pivot Tabelle klickt, erscheint im Menüband oben das Pivot-Tabellen Menü. Hier wählen wir folgendes aus:
Pivot-Tabellen Menü>Optionen>Berechnungen>Felder, Element und Gruppen>Berechnetes Feld…
wonach das “Berechnetes Feld einfügen” Dialogfenster erscheint:
Wir können im ersten Feld den Namen des Berechneten Feldes definieren (natürlich darf dieser in der Pivot nicht bereits vorkommen, muss also einzigartig sein).
Im Formel Feld geben wir die gewünschte Gleichung eingeben. Dies können wir tun in dem wir die gewünschten Daten markieren und per Doppelklick oder mit dem “Feld einfügen” Button in die Formel einfügen. Leider können wir nicht Tabellenbezüge, Namen (aus Namensmanager), und Arrays einfügen da diese in den berechneten Pivot Felder nicht unterstützt werden.
Für unser berechnetes BMI Feld sieht die Formel folgendermaßen aus:
= ‘Gewicht in KG’/(‘Grösse in CM’/100)^2
‘Gewicht in KG’ und ‘Grösse in CM’ sind beides Felder aus der bestehenden Pivot Tabelle welche per Doppelklick aus der Auswahlliste eingesetzt wurden. Da die BMI Formel die Grösse in Meter verlangt habe ich die Daten durch 100 geteilt. Der Rest ist getreu der BMI Formel wie oben erwähnt.
Nach Eingabe OK drücken und wir sind fertig. Das neu berechnete BMI Feld erscheint automatisch in der Pivot Tabelle!
Fehler in Gesamtsumme und Zwischensummen
Ein Schönheitsfehler hat das ganze jedoch: Wenn man Zwischensummen und Gesamtsummen ansieht, so merkt man schnell dass diese nicht richtig kalkulieren.
Leider ist dies ein Fehler in Excel!
Gemäss Knowledgebase von Microsoft werden alle Berechneten Felder welche Berechnungen “durchführen einer höheren Reihenfolge arithmetischer Operation, z. B. Potenzierung, Multiplikation oder Division auf anderen Feldern in der PivotTable” davon betroffen. Der Artikel in der KB sagt dieses Problem sei nur in Excel 2003 und Excel 2007 vorhanden, tatsächlich ist es aber auch bei Excel 2010 ein Problem!
Das Problem umgehen kann man lediglich in dem man die Kalkulation in der Quelltabelle bereits berechnet. Dies hat aber natürlich den Nachteil dass die Datenmenge grösser wird und jeder Nutzen der durch das einfügen eines berechneten Feldes verloren geht!
Wenn Ihr mehr über Pivot Tabellen lernen wollt, schreibt Euch in unserem Newsletter ein, oder klickt in der Schlagwörter Wolke in der rechten Spalte auf “Pivot Tabellen”!