tomolpi
Goto Top

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
screenshot 2020-10-05 224932

Content-Key: 610364

Url: https://administrator.de/contentid/610364

Printed on: June 25, 2024 at 11:06 o'clock

Member: cykes
cykes Oct 06, 2020 updated at 03:22:11 (UTC)
Goto Top
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. face-wink

Gruß

cykes
Member: tomolpi
tomolpi Oct 06, 2020 updated at 07:22:57 (UTC)
Goto Top
Zitat von @cykes:

Moin,
Hi,
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. face-wink
Also, ich möchte die Spalte "Wert 1" erst durch 2 teilen, und dann den Mittelwert bilden.
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ück
cykes
tomolpi
Member: tomolpi
tomolpi Oct 06, 2020 at 20:34:40 (UTC)
Goto Top
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.
Member: falscher-sperrstatus
falscher-sperrstatus Oct 06, 2020 updated at 20:47:11 (UTC)
Goto Top
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...
Member: cykes
cykes Oct 07, 2020 updated at 04:39:03 (UTC)
Goto Top
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
Member: tomolpi
tomolpi Oct 07, 2020 updated at 08:22:50 (UTC)
Goto Top
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:
  1. 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.
  2. jetzt erstelle ich das erste PivotTable: auswählen einer neuen Zelle, dann "Einfügen" -> "PivotTable".
  3. 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.
  4. mit "Ok" wird das erste PivotTable erstellt.
  5. wählt irgendein Element in diesem neuen PivotTable aus, und dann über "PivotTable-Tools" -> "Analysieren" -> "Feldliste" in die Optionen
  6. in der Seitenleiste sind jetzt unten rechts unter "Werte" die Elemente "Summe von Wert 1" und "Summe von Wert 2" vorhanden.
  7. z.B. bei "Summe von Wert 1" das kleine Dreieck nach unten anklicken, die "Wertfeldeinstellungen..." öffnen.
  8. im aufgehenden Fenster "Werte zusammenfassen nach" den "Mittelwert" auswählen. Die Beschriftung dieses Felder ändert sich in "Mittelwert von Wert 1".
bis hier hin ist alles in Ordnung!

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
Member: cykes
cykes Oct 10, 2020 at 11:45:20 (UTC)
Goto Top
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 face-wink

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
Member: khierhol
khierhol Sep 10, 2022 updated at 23:48:58 (UTC)
Goto Top
Ich habe dasselbe Problem bei vollständig unterschiedlichen Daten, das ganze sieht nach einem echten Excel Bug aus. Hier kurz das Testbeispiel zum Nachweis:

  • 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 ...
Member: khierhol
khierhol Sep 10, 2022 at 20:48:22 (UTC)
Goto Top
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