Generische Formel
=SUMPRODUCT(weights,values)/SUM(weights)
Zusammenfassung
Um einen gewichteten Durchschnitt zu berechnen, können Sie die SUMPRODUCT-Funktion zusammen mit der SUM-Funktion verwenden. Im gezeigten Beispiel lautet die kopierte Formel in G5:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
wobei Gewichte der benannte Bereich I5: K5 ist.
Erläuterung
Ein gewichteter Durchschnitt, auch gewichteter Mittelwert genannt, ist ein Durchschnitt, bei dem einige Werte mehr zählen als andere. Mit anderen Worten, einige Werte haben mehr "Gewicht". Wir können einen gewichteten Durchschnitt berechnen, indem wir die Werte mit den entsprechenden Gewichten multiplizieren und dann die Summe der Ergebnisse durch die Summe der Gewichte dividieren. In Excel kann dies mit der folgenden allgemeinen Formel dargestellt werden, wobei Gewichte und Werte Zellbereiche sind:
=SUMPRODUCT(weights,values)/SUM(weights)
In dem Arbeitsblatt gezeigt, Noten für 3 Tests erscheinen in den Spalten C bis E und Gewichte sind in den genannten Bereich Gewichte (I5: K5). Die Formel in Zelle G5 lautet:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Wir arbeiten von innen nach außen und verwenden zunächst die Funktion SUMPRODUCT, um Gewichte mit entsprechenden Bewertungen zu multiplizieren und das Ergebnis zu summieren:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT multipliziert zuerst die entsprechenden Elemente der beiden Arrays miteinander und gibt dann die Summe des Produkts zurück:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Das Ergebnis wird dann durch die Summe der Gewichte geteilt:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Da die Formel nach unten Spalte G kopiert wird, werden die benannten Bereich Gewichte I5: nicht K5 nicht ändern, da es wie eine absolute Referenz verhält. Die als relative Referenz eingegebenen Ergebnisse in C5: E5 werden jedoch in jeder neuen Zeile aktualisiert. Das Ergebnis ist ein gewichteter Durchschnitt für jeden Namen in der Liste, wie gezeigt. Der Durchschnitt in Spalte F wird nur als Referenz mit der Funktion DURCHSCHNITTLICH berechnet:
=AVERAGE(C5:E5)
Gewichte, die nicht 1 ergeben
In diesem Beispiel sind die Gewichte so konfiguriert, dass sie sich zu 1 addieren, sodass der Divisor immer 1 ist und das Ergebnis der von SUMPRODUCT zurückgegebene Wert ist. Ein schönes Merkmal der Formel ist jedoch, dass sich die Gewichte nicht zu 1 addieren müssen.
Zum Beispiel könnten wir für die ersten beiden Tests ein Gewicht von 1 und für das Finale ein Gewicht von 2 verwenden (da das Finale doppelt so wichtig ist), und der gewichtete Durchschnitt ist der gleiche:
In Zelle G5 wird die Formel folgendermaßen gelöst:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Hinweis: Die Werte in geschweiften Klammern () oben sind Bereiche, die als Arrays ausgedrückt werden.
Gewichte transponieren
Die SUMPRODUCT-Funktion erfordert, dass die Array-Dimensionen kompatibel sind. Wenn die Abmessungen nicht kompatibel sind, gibt SUMPRODUCT einen # VALUE-Fehler zurück. Im folgenden Beispiel sind die Gewichte dieselben wie im Originalbeispiel, sie sind jedoch in einem vertikalen Bereich aufgeführt:
Um einen gewichteten Durchschnitt mit derselben Formel zu berechnen, müssen wir die Gewichte mit der TRANSPOSE-Funktion wie folgt in ein horizontales Array "spiegeln":
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Nachdem TRANSPOSE ausgeführt wurde, wird das vertikale Array:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
wird:
=(0.25,0.25,0.5) // horizontal array
Und ab diesem Punkt verhält sich die Formel wie zuvor.
Lesen Sie mehr: vertikale und horizontale Arrays.
