Was man wissen sollte
- Syntax: =SUMMENPRODUKT(Array1, Array2, Array3, …Array255).
- Bei einer Berechnung des gewichteten Durchschnitts sind einige Zahlen im Bereich mehr wert als andere.
- Die SUMPRODUCT-Gewichtungsformel ist eine nicht standardmäßige Formel, die in eine Zelle eingegeben werden muss, damit sie funktioniert.
In diesem Artikel wird erläutert, wie Sie die SUMPRODUCT-Funktion verwenden, um einen gewichteten Durchschnitt in Microsoft Excel-Formeln zu berechnen.
Geben Sie die Gewichtungsformel ein
Wie die meisten anderen Funktionen in Excel kann SUMPRODUCT über die Funktionsbibliothek auf der Registerkarte Formeln in ein Arbeitsblatt eingegeben werden. Da die Gewichtungsformel in diesem Beispiel SUMPRODUCT nicht standardmäßig verwendet (das Ergebnis der Funktion wird durch den Gewichtungsfaktor dividiert), muss die Gewichtungsformel in eine Arbeitsblattzelle eingegeben werden.
Das in diesem Artikel gezeigte Beispiel berechnet den gewichteten Durchschnitt für die Endnote eines Schülers mithilfe der SUMPRODUCT-Funktion.
Die Funktion erreicht dies durch:
- Multiplikation der verschiedenen Noten mit ihrem individuellen Gewichtungsfaktor.
- Addieren der Produkte dieser Multiplikationsoperationen.
-
Dividieren der obigen Summe durch die Summe der Gewichtungsfaktoren 7 (1+1+2+3) für die vier Bewertungen.
Um die Formel SUMPRODUCT zur Berechnung eines gewichteten Durchschnitts einzugeben, öffnen Sie ein leeres Arbeitsblatt und geben Sie die Daten in die Zeilen 1 bis 6 ein das Bild oben und folge diesen Schritten:
- Zelle C7 auswählen, um sie zur aktiven Zelle zu machen (an dieser Stelle wird die Endnote des Schülers angezeigt).
- Geben Sie die Formel =SUMMENPRODUKT(B3:B6, C3:C6)/(1+1+2+3) in die Zelle ein. Die Formel erscheint in der Formelleiste.
- Drücken Sie die Taste Enter auf der Tastatur.
- Die Antwort 78.6 erscheint in Zelle C7 (Ihre Antwort kann mehr Dezimalstellen haben).
Der ungewichtete Durchschnitt für dieselben vier Punkte wäre 76,5. Da der Student bei seinen Zwischen- und Abschlussprüfungen bessere Ergebnisse erzielte, trug das Gewichten des Durchschnitts dazu bei, die Gesamtnote zu verbessern.
So funktioniert die SUMPRODUCT-Funktion
Wenn Sie einen Durchschnitt oder ein arithmetisches Mittel berechnen, hat normalerweise jede Zahl den gleichen Wert oder das gleiche Gewicht. Der Durchschnitt wird berechnet, indem eine Reihe von Zahlen addiert und diese Summe dann durch die Anzahl der Werte in der Reihe dividiert wird. Ein gewichteter Durchschnitt hingegen betrachtet eine oder mehrere Zahlen im Bereich als mehr wert oder haben ein größeres Gewicht als die anderen Zahlen.
SUMPRODUCT multipliziert die Elemente von zwei oder mehr Arrays und addiert dann die Produkte, um gewichtete Durchschnitte zu berechnen. Zum Beispiel sind bestimmte Noten in der Schule, wie Zwischen- und Abschlussprüfungen, normalerweise mehr wert als normale Tests oder Aufgaben. Wenn zur Berechnung der Abschlussnote eines Schülers der Durchschnitt verwendet wird, würden die Zwischen- und Abschlussprüfungen stärker gewichtet.
In einer Situation, in der zwei Arrays mit je vier Elementen als Argumente für die SUMPRODUCT-Funktion eingegeben werden:
- Das erste Element von Array1 wird mit dem ersten Element von Array2 multipliziert.
- Das zweite Element von Array1 wird mit dem zweiten Element von Array2 multipliziert.
- Das dritte Element von Array1 wird mit dem dritten Element von Array2 multipliziert.
- Das vierte Element von Array1 wird mit dem vierten Element von Array2 multipliziert.
Als nächstes werden die Produkte der vier Multiplikationsoperationen summiert und von der Funktion als Ergebnis zurückgegeben.
SUMPRODUCT Syntax und Argumente
Die Syntax einer Funktion bezieht sich auf das Layout der Funktion und beinh altet den Namen der Funktion, Klammern und Argumente. Die Syntax für die SUMPRODUCT-Funktion lautet:
=SUMMENPRODUKT(Array1, Array2, Array3, … Array255)
Die Argumente für die SUMPRODUCT-Funktion sind:
- Array1: Das erste Array-Argument (erforderlich).
- Array2, Array3 , …Array255 : Zusätzliche (optionale) Arrays, bis zu 255. Bei zwei oder mehr Arrays multipliziert die Funktion die Elemente jedes Arrays miteinander und addiert dann die Ergebnisse.
Die Array-Elemente können Zellbezüge auf die Position der Daten im Arbeitsblatt oder Zahlen sein, die durch arithmetische Operatoren wie Pluszeichen (+) oder Minuszeichen (-) getrennt sind. Wenn Sie Zahlen eingeben, die nicht durch Operatoren getrennt sind, behandelt Excel sie als Textdaten.
Array-Argumente müssen in jedem Array die gleiche Anzahl von Elementen haben. Wenn nicht, gibt SUMMENPRODUKT den WERT! Fehlerwert. Wenn Array-Elemente keine Zahlen sind, wie z. B. Textdaten, behandelt SUMPRODUCT sie als Nullen.
SUMPRODUCT Formelvariationen
Um zu betonen, dass die Ergebnisse der SUMPRODUCT-Funktion durch die Summe der Gewichtungen für jede Bewertungsgruppe dividiert werden, wird der Divisor (der dividierende Teil) wie folgt eingegeben:
(1+1+2+3)
Die Gesamtgewichtungsformel könnte man vereinfachen, indem man als Divisor die Zahl 7 (die Summe der Gewichte) einträgt. Die Formel wäre dann:
=SUMPRODUKT(B3:B6, C3:C6)/7
Diese Wahl ist in Ordnung, wenn die Anzahl der Elemente im Gewichtungs-Array klein ist und sie leicht zusammengefügt werden können, aber sie wird weniger effektiv, wenn die Anzahl der Elemente im Gewichtungs-Array zunimmt, wodurch ihre Hinzufügung schwieriger wird.
Eine weitere Option und wahrscheinlich die beste Wahl, da sie Zellreferenzen anstelle von Zahlen verwendet, um den Divisor zu summieren, wäre die SUM-Funktion, um den Divisor zu summieren. Die Formel lautet dann:
=SUMPRODUKT(B3:B6, C3:C6)/SUMME(B3:B6)
In Formeln ist es normalerweise am besten, Zellbezüge anstelle von Zahlen einzugeben. Dies vereinfacht die Aktualisierung, wenn sich die Daten der Formel ändern.
Wenn beispielsweise die Gewichtungsfaktoren für Aufgaben im Beispiel auf 0,5 und Tests auf 1,5 geändert werden, müssen die ersten beiden Formen der Formel manuell bearbeitet werden, um den Divisor zu korrigieren.
Bei der dritten Variante müssen nur die Daten in den Zellen B3 und B4 aktualisiert werden, und die Formel berechnet das Ergebnis neu.
