9
Mittelwert von berechneten Feldern eines PivotTable - rechnet nur Summe
Guten Abend zusammen,
ich habe folgendes Problem in Excel:
Eine Tabelle mit Werten ist vorhanden. Ich erstelle ein neues PivotTable mit diesen Daten.
Über die Registerkarte "PivotTable Tools" -> "Felder, Elemente und Gruppen" erstelle ich ein neues berechnetes Feld, welches alle Werte einer Spalte durch 2 teilt.
Anschließend sage ich dem PivotTable, dass es mir die Werte nicht summieren, sondern den Mittelwert bilden soll.
Das macht Excel aber nicht! Es rechnet mir trotzdem die Summe aus!
Anbei ein Bild zum Verständnis.
"Summe von Wert 1" bedeutet, dass ich in der Feldliste des PivotTables unter "Werte" auch wirklich die Summe ausgewählt habe. Ich habe das Fenster im Screenshot offen gelassen, damit ihr mir das auch glaubt.
Analog dazu der Mittelwert.
Ich frage euch: was mache ich falsch? Geht es schlichtweg nicht, aus berechneten Feldern eines PivotTables den Mittelwert zu bilden?
Bei Feldern ohne Berechnung klappt es ja - siehe Bild.
Verzweifelte Grüße
tomolpi
ich habe folgendes Problem in Excel:
Eine Tabelle mit Werten ist vorhanden. Ich erstelle ein neues PivotTable mit diesen Daten.
Über die Registerkarte "PivotTable Tools" -> "Felder, Elemente und Gruppen" erstelle ich ein neues berechnetes Feld, welches alle Werte einer Spalte durch 2 teilt.
Anschließend sage ich dem PivotTable, dass es mir die Werte nicht summieren, sondern den Mittelwert bilden soll.
Das macht Excel aber nicht! Es rechnet mir trotzdem die Summe aus!
Anbei ein Bild zum Verständnis.
"Summe von Wert 1" bedeutet, dass ich in der Feldliste des PivotTables unter "Werte" auch wirklich die Summe ausgewählt habe. Ich habe das Fenster im Screenshot offen gelassen, damit ihr mir das auch glaubt.
Analog dazu der Mittelwert.
Ich frage euch: was mache ich falsch? Geht es schlichtweg nicht, aus berechneten Feldern eines PivotTables den Mittelwert zu bilden?
Bei Feldern ohne Berechnung klappt es ja - siehe Bild.
Verzweifelte Grüße
tomolpi
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 610364
Url: https://administrator.de/contentid/610364
Printed on: April 19, 2024 at 21:04 o'clock
9 Comments
Latest comment
Moin,
also entweder ergibt Dein Beispiel keinen Sinn oder ich verstehe den Sinn nicht (beides ist durchaus möglich).
Der (arithmetische) Mittelwert wird aus der Summe mehrerer (>1) Zellen(werte) geteilt durch die Anzahl der summierten Zellen gebildet. Für einen Zellenwert ergibt das m.E. keinen Sinn.
Deine "Mittelwert von wert1_geteilt_durch_2" von 1 Zelle führt dann konsequent zum falschen Ergebnis.
Vielleicht habe ich das aber auch nicht richtig erfasst.
Gruß
cykes
also entweder ergibt Dein Beispiel keinen Sinn oder ich verstehe den Sinn nicht (beides ist durchaus möglich).
Der (arithmetische) Mittelwert wird aus der Summe mehrerer (>1) Zellen(werte) geteilt durch die Anzahl der summierten Zellen gebildet. Für einen Zellenwert ergibt das m.E. keinen Sinn.
Deine "Mittelwert von wert1_geteilt_durch_2" von 1 Zelle führt dann konsequent zum falschen Ergebnis.
Vielleicht habe ich das aber auch nicht richtig erfasst.
Gruß
cykes
Hi,
Du siehst ja, dass das klappt, wenn ich diese Berechnung weglasse. Das Problem liegt hier am Berechneten Feld, sobald ich das einsetze, wird zwar durch 2 geteilt, aber jegliche Einstellungen (egal ob Mittelwert o.ä.) wird ignoriert. Es wird nur aufsummiert, das will ich aber nicht.
Ich erwarte in Spalte L unter "Gesamtergebnis" nicht 2003, sondern irgendwas um 500 - den Mittelwert der Spalte eben...
Bloß mit klappt es nicht.
also entweder ergibt Dein Beispiel keinen Sinn oder ich verstehe den Sinn nicht (beides ist durchaus möglich).
Der (arithmetische) Mittelwert wird aus der Summe mehrerer (>1) Zellen(werte) geteilt durch die Anzahl der summierten Zellen gebildet. Für einen Zellenwert ergibt das m.E. keinen Sinn.
Deine "Mittelwert von wert1_geteilt_durch_2" von 1 Zelle führt dann konsequent zum falschen Ergebnis.
Vielleicht habe ich das aber auch nicht richtig erfasst.
Also, ich möchte die Spalte "Wert 1" erst durch 2 teilen, und dann den Mittelwert bilden.Der (arithmetische) Mittelwert wird aus der Summe mehrerer (>1) Zellen(werte) geteilt durch die Anzahl der summierten Zellen gebildet. Für einen Zellenwert ergibt das m.E. keinen Sinn.
Deine "Mittelwert von wert1_geteilt_durch_2" von 1 Zelle führt dann konsequent zum falschen Ergebnis.
Vielleicht habe ich das aber auch nicht richtig erfasst.
Du siehst ja, dass das klappt, wenn ich diese Berechnung weglasse. Das Problem liegt hier am Berechneten Feld, sobald ich das einsetze, wird zwar durch 2 geteilt, aber jegliche Einstellungen (egal ob Mittelwert o.ä.) wird ignoriert. Es wird nur aufsummiert, das will ich aber nicht.
Ich erwarte in Spalte L unter "Gesamtergebnis" nicht 2003, sondern irgendwas um 500 - den Mittelwert der Spalte eben...
Bloß mit klappt es nicht.
Gruß
Grüße und danke zurückcykes
tomolpi
Zur Info: ich habe heute ein Ticket bei Microsoft deswegen eröffnet - den Support zahle ich ja eh mit (Microsoft 365 Business).
Mal sehen, was da rauskommt.
Mal sehen, was da rauskommt.
Pack mal die Formel rein, die du da genommen hast? Ich mein, so ist das bissl witzlos
Summe von Wert 1 ist zwar per "=SUMME(A1)" definierbar, aber de facto ja nur A1, A1 /2 "=SUMME(A2/2)" und Mittelwert ist =Mittelwert(F1-F5) (bspw).
Womit der MW bei 500.75 liegt...
klappt Problemlos, entweder du hast da einen wahnsinnigen Denkfehler oder du erklärst nicht richtig, was du willst...
Summe von Wert 1 ist zwar per "=SUMME(A1)" definierbar, aber de facto ja nur A1, A1 /2 "=SUMME(A2/2)" und Mittelwert ist =Mittelwert(F1-F5) (bspw).
Womit der MW bei 500.75 liegt...
klappt Problemlos, entweder du hast da einen wahnsinnigen Denkfehler oder du erklärst nicht richtig, was du willst...
Ich vermute auch einen Denkfehler. Ich habe den Verdacht, dass der Mittelwert doppelt berechnet wird, zum einen händisch durch Summe()/"Anzahl" und dann nochmal mit der Mittelwert()-Funktion. Aber ohne die konkreten Formeln in den Zellen zu sehen, wird das schwierig zu analysieren.
Was ich außerdem nicht verstehe: Warum wird fest durch 2 geteilt?
Also wird beim Gesamtergebnis wohl doppelt geteilt, das ist "zufällig" genau das Ergebnis der Summe (Mittelwert / 2 => "Summe / Anzahl" / 2 = 1001,5 * 2 = 2003) [leider nur ansatzweise hier darstellbar]
Gruß
cykes
Was ich außerdem nicht verstehe: Warum wird fest durch 2 geteilt?
Also wird beim Gesamtergebnis wohl doppelt geteilt, das ist "zufällig" genau das Ergebnis der Summe (Mittelwert / 2 => "Summe / Anzahl" / 2 = 1001,5 * 2 = 2003) [leider nur ansatzweise hier darstellbar]
Gruß
cykes
Hallo zusammen,
danke für eure Antworten!
Ich verwende keinerlei "Formel" für den Mittelwert und die Summe - das lasse ich alleine übers PivotTable machen.
Der Mittelwert müsste natürlich bei 500,75 liegen, aber das soll Excel ja selbst ausrechnen...
Damit es hoffentlich keine Missverständnisse mehr gibt, hier eine Schritt-für-Schritt Anleitung, was ich gemacht habe. Ziel ist es, in einem PivotTable Daten darzustellen, die vorher umgerechnet werden müssen (andere Einheit).
Also:
um den Fehler zu zeigen:
wiederholt Schritte 2 bis 4 und erstellt eine zweite PivotTable.
9. wählt wieder irgendein Element in diesem neuen PivotTable aus, und dann über "PivotTable-Tools" -> "Analysieren" -> "Felder, Elemente und Gruppen" auf "Berechnetes Feld".
10. Unter "Name" trage ich ein "wert1_geteilt_durch_2", unter "Formel" ='Wert 1'/2
dies ist das berechnete Feld, mit dem ich alle Werte von Wert 1 durch 2 teile! Hier ließe sich natürlich auch irgendeine andere Berechnung durchführen!
11. Klick auf "Hinzufügen" und "Ok".
12. falls nicht mehr offen, die Pivot Optionen mit 5. öffnen
13. das neue Feld "wert1_geteilt_durch_2" anhaken, es ist jetzt ebenfalls Bestandteil des neuen PivotCharts und bekommt automatisch die Überschrift "Summe von wert1_geteilt_durch_2"
14. bei "Summe von wert1_geteilt_durch_2" das kleine Dreieck nach unten anklicken, die "Wertfeldeinstellungen..." öffnen.
15. Schritt 8. ausführen.
Fehler tritt auf, es wird kein Mittelwert gebildet, sondern wieder die Summe!.
Ich hoffe, ihr versteht jetzt, was ich meine.
Danke für eure Zeit!
Grüße
tomolpi
danke für eure Antworten!
Ich verwende keinerlei "Formel" für den Mittelwert und die Summe - das lasse ich alleine übers PivotTable machen.
Der Mittelwert müsste natürlich bei 500,75 liegen, aber das soll Excel ja selbst ausrechnen...
Damit es hoffentlich keine Missverständnisse mehr gibt, hier eine Schritt-für-Schritt Anleitung, was ich gemacht habe. Ziel ist es, in einem PivotTable Daten darzustellen, die vorher umgerechnet werden müssen (andere Einheit).
Also:
- starten mit der Tabelle im Screenshot oben links. Es sind keine Formeln hinterlegt, nur die Werte, wie ihr sie von A1 bis C5 seht. Tragt die auch so in euer Blatt ein.
- jetzt erstelle ich das erste PivotTable: auswählen einer neuen Zelle, dann "Einfügen" -> "PivotTable".
- im Fenster "PivotTable erstellen", welches dann aufgeht, unter "Tabelle/Bereich" folgendes eintragen: Tabelle1!$A$1:$C$5 oder einfach die kleine Tabelle aus dem ersten Schritt markieren.
- mit "Ok" wird das erste PivotTable erstellt.
- wählt irgendein Element in diesem neuen PivotTable aus, und dann über "PivotTable-Tools" -> "Analysieren" -> "Feldliste" in die Optionen
- in der Seitenleiste sind jetzt unten rechts unter "Werte" die Elemente "Summe von Wert 1" und "Summe von Wert 2" vorhanden.
- z.B. bei "Summe von Wert 1" das kleine Dreieck nach unten anklicken, die "Wertfeldeinstellungen..." öffnen.
- im aufgehenden Fenster "Werte zusammenfassen nach" den "Mittelwert" auswählen. Die Beschriftung dieses Felder ändert sich in "Mittelwert von Wert 1".
um den Fehler zu zeigen:
wiederholt Schritte 2 bis 4 und erstellt eine zweite PivotTable.
9. wählt wieder irgendein Element in diesem neuen PivotTable aus, und dann über "PivotTable-Tools" -> "Analysieren" -> "Felder, Elemente und Gruppen" auf "Berechnetes Feld".
10. Unter "Name" trage ich ein "wert1_geteilt_durch_2", unter "Formel" ='Wert 1'/2
dies ist das berechnete Feld, mit dem ich alle Werte von Wert 1 durch 2 teile! Hier ließe sich natürlich auch irgendeine andere Berechnung durchführen!
11. Klick auf "Hinzufügen" und "Ok".
12. falls nicht mehr offen, die Pivot Optionen mit 5. öffnen
13. das neue Feld "wert1_geteilt_durch_2" anhaken, es ist jetzt ebenfalls Bestandteil des neuen PivotCharts und bekommt automatisch die Überschrift "Summe von wert1_geteilt_durch_2"
14. bei "Summe von wert1_geteilt_durch_2" das kleine Dreieck nach unten anklicken, die "Wertfeldeinstellungen..." öffnen.
15. Schritt 8. ausführen.
Fehler tritt auf, es wird kein Mittelwert gebildet, sondern wieder die Summe!.
Ich hoffe, ihr versteht jetzt, was ich meine.
Danke für eure Zeit!
Grüße
tomolpi
Moin,
bin erst jetzt dazu gekommen, das mal nachzuvollziehen - vorab: mein Excel kommt zum selben Ergebnis.
Ich glaube aber auch, dass Du eine falsche Erwartungshaltung hast bzw. einen gedanklichen Fehler.
Wie oben bereits erwähnt, ist es eigentlich Blödsinn, den Mittelwert von einer Zelle (Wert 1) zu bilden, das ergibt erwartungsgemäß den Zellenwert. Diesen Zellenwert dann noch durch 2 zu teilen und nochmal den Mittelwert davon zu bilden ist redundanter Blödsinn
Um Dein erwartetes Ergebnis zu erzielen, ist ggf. eine deutlich komplexere Konfiguration der Pivot-Tabelle nötig oder man abut sich die Berechnungen gleich selbst, dann hat man auch mehr Kontrolle über die Berechnungen.
Ich habe oben im Datenbereich auch noch eine Spalte Wert1_durch_2 hinzugefügt und dann unten manuell den Mittelwert der Spalte Wert1 (B2:B5) und Wert1_durch_2 (D2:D5) berehcnet mit dem richtigen Ergebnis.
Gruß
cykes
bin erst jetzt dazu gekommen, das mal nachzuvollziehen - vorab: mein Excel kommt zum selben Ergebnis.
Ich glaube aber auch, dass Du eine falsche Erwartungshaltung hast bzw. einen gedanklichen Fehler.
Wie oben bereits erwähnt, ist es eigentlich Blödsinn, den Mittelwert von einer Zelle (Wert 1) zu bilden, das ergibt erwartungsgemäß den Zellenwert. Diesen Zellenwert dann noch durch 2 zu teilen und nochmal den Mittelwert davon zu bilden ist redundanter Blödsinn
Um Dein erwartetes Ergebnis zu erzielen, ist ggf. eine deutlich komplexere Konfiguration der Pivot-Tabelle nötig oder man abut sich die Berechnungen gleich selbst, dann hat man auch mehr Kontrolle über die Berechnungen.
Ich habe oben im Datenbereich auch noch eine Spalte Wert1_durch_2 hinzugefügt und dann unten manuell den Mittelwert der Spalte Wert1 (B2:B5) und Wert1_durch_2 (D2:D5) berehcnet mit dem richtigen Ergebnis.
Gruß
cykes
Ich habe dasselbe Problem bei vollständig unterschiedlichen Daten, das ganze sieht nach einem echten Excel Bug aus. Hier kurz das Testbeispiel zum Nachweis:
Bei einer Pivottabelle zeigen nun
1. Summe, Mittelwert, Abs, ... auf echte Spalten die erwarteten unterschiedliche Werte
2. Summe, Mittelwert, Abs, ... auf berechnete Felder immer den Wert für Aggregationsmethode=Summe, die anderen Aggregationsmethoden sind wählbar, aber die Wahl ändert eben nichts.
OK, in https://superuser.com/questions/1233961/working-with-averages-in-pivot-t ... , answer from Jonathan July 25th 2017. https://superuser.com/a/1234352 ist eine super Erklärung:
- Tabelle mit Spalte B: uploadduration_s (Zeit in s), C uploadduration_h (Spalte B/3600)
- Pivottabelle-Analyse: berechnetes Feld _uploadduration_h> (=uploadduration_s/3600)
- Hinweis: Im echten Anwendungsfall hätte ich natürlich keine Spalte C mit dem Stundenwert in der Tabelle, die dient hier nur zum Vergleich mit dem berechneten Feld
Bei einer Pivottabelle zeigen nun
1. Summe, Mittelwert, Abs, ... auf echte Spalten die erwarteten unterschiedliche Werte
2. Summe, Mittelwert, Abs, ... auf berechnete Felder immer den Wert für Aggregationsmethode=Summe, die anderen Aggregationsmethoden sind wählbar, aber die Wahl ändert eben nichts.
OK, in https://superuser.com/questions/1233961/working-with-averages-in-pivot-t ... , answer from Jonathan July 25th 2017. https://superuser.com/a/1234352 ist eine super Erklärung:
- Bei Verwendung von berechneten Feldern wird zuerst für jede Spalte die Summe berechnet, quasi als Wert je Pi###ile
- das berechnete Feld arbeitet dann auf diesen bereits summierten Werten, im wesentlichen können damit Operationen zwischen den Summen zu verschiedenen Spalten gemacht werdne
- das ist nicht selbsterklärend, aber ok, auch kein Bug ...
a good explanation why excel does what it does seems to be in: https://superuser.com/questions/1233961/working-with-averages-in-pivot-t ... , answer from Jonathan July 25th 2017. https://superuser.com/a/1234352
