Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Gewichteten Mittelwert dynamisch berechnen ohne Summenproduktformel
#1
Hallo zusammen,

ich bin relativer Excel-Anfänger und das Forum hat mir bereits heute morgen helfen können. Dort wurde mir eine Formel für den normalen dynamischen Mittelwert gesagt, der sich an den Merkmalen der Produktgruppe orientiert. Und  nun geht es darum, dass ich den gewichteten Mittelwert dynamisch berechnen möchte. Das bedeutet ich möchte die Formel Summenprodukt nicht verwenden, da man ja stets den Bereich neu definieren muss, oder? Wie ihr in der Spalte A sehen könnt, habe ich für jede Produktgruppe ein fest definiertes Merkmal. Ich hoffe, dass ich eine Formel erstellen kann, die ich einfach für die 16000 Artikel runter ziehen kann. Hättet ihr eventuell eine Idee? Anbei ein Screenshot und die Datei.

Vielen Dank schonmal :)

Oben seht ihr jeweils die Formel:
[
Bild bitte so als Datei hochladen: Klick mich!
]


Angehängte Dateien
.xlsx   Gewichteter Mittelwert.xlsx (Größe: 10,48 KB / Downloads: 6)
Antworten Top
#2
G4: =--(A3=A4) als Hilfsspalte

H4: 
=WENN(G4;H3;WENN(A5<>A4;D4;SUMMENPRODUKT(
E4:INDEX(E5:E$17000;VERGLEICH(;G5:G$17000;)-1);
D4:INDEX(D5:D$17000;VERGLEICH(;G5:G$17000;)-1))/SUMME(
E4:INDEX(E5:E$17000;VERGLEICH(;G5:G$17000;)-1))))
  • Die Lösung ist länger und geschwindigkeitsoptimiert, damit nicht mehrfach 16.000^2-mal gerechnet werden muss. 
  • Die Sortierung von A hast Du vorgegeben und sie ist nötig.
  • Hinter den letzten Eintrag in G gehört die Formel noch eine Zeile weiter gezogen.
Antworten Top
#3
Hier noch etwas schneller, da nur einmal VERGLEICH pro Sektion gerechnet wird und der VERGLEICH in die Hilfsspalte ausgegliedert ist
(Anm.: der sehr schnelle Binär-VERGLEICH ist nicht möglich):

G4: =WENN(A3=A4;;VERGLEICH(WAHR;INDEX(A5:A$17000<>A6:A$17001;);))

H4: =WENN(G4;WENN(A5<>A4;D4;SUMMENPRODUKT(
E4:INDEX(E5:E$17000;G4);
D4:INDEX(D5:D$17000;G4)/SUMME(
E4:INDEX(E5:E$17000;G4))));H3)
  • xl365/xlOnline kommen seit 3 Monaten ohne die größeren Formel-Zeichen aus, da sie die meisten Matrizen implizit erkennen
  • Die zusätzliche Formel hinter dem Ende in G:G ist nicht mehr nötig
  • es ist nun jegliche Redundanz beim Rechnen aufgehoben
[-] Folgende(r) 1 Nutzer sagt Danke an LCohen für diesen Beitrag:
  • Alexa1995
Antworten Top
#4
Hallo,

soweit ich das sehe , willst du pro Merkmal deinen gew. Mittelwert -->  da macht es keinen Sinn das gleiche Merkmal  sooft zu rechnen,  wie das Merkmal vorkommt -->

weil es kommt immer das gleiche raus. Siehe in deinen Bspl  H4 / H5 / H6.

Also  trenne Einzelsätze von  Auswertungen


Mach eine gesonderte Spalte / Blatt  mit eindeutigen Merkmalen

dann musst du nur einmal Summenprodukt über die ganze Spalte machen und kannst due Fomel runterziehen

=(SUMMENPRODUKT($E$4:$E$16000;$D$4:$D$16000;--($A$4:$A$16000=Neu!$A2)))  / (SUMMENPRODUKT($E$4:$E$16000;--($A$4:$A$16000=Neu!A2)))


Verstehst du was ich meine
[-] Folgende(r) 1 Nutzer sagt Danke an Dieter63 für diesen Beitrag:
  • Alexa1995
Antworten Top
#5
Hallo Dieter 63,

nein ich würde gerne alle Merkmale behalten, da er mir nicht nur für die einzelne Produktgruppe den gewichteten Mittelwert wiedergeben sollte, sondern hinter jedem Artikel der gewichtete Mittelwert stehen soll. Dieser gewichtete Mittelwert soll sozusagen den neuen Preis darstellen, da es mir wichtig ist, dass alle Produkte derselben Produktgruppe einen identischen Preis aufweisen.

Aber danke dir vielmals für deine Antwort, deine Formel sieht definitiv verständlicher aus :D

Mit der vorhergehenden Formel =WENN(G4;WENN(A5<>A4;D4;SUMMENPRODUKT(E4:INDEX(E5:E$17000;G4);
D4:INDEX(D5:D$17000;G4)/SUMME(E4:INDEX(E5:E$17000;G4))));H3) hat es auch funktioniert und so kriege ich auch jeweils den gewichteten Mittelwert hinter jeden Artikel :)
Antworten Top
#6
Hallo LCohen,

danke auch dir für deine Mühe. Die Formel funktioniert einwandfrei und hat mir sehr weitergeholfen! :28:
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste