So berechnen Sie gewichtete Durchschnitte in Excel mit SUMPRODUCT

Normalerweise, wenn Sie einen berechnen Durchschnitt oder arithmetisches Mittel, jede Zahl hat den gleichen Wert oder das gleiche Gewicht. Der Durchschnitt wird berechnet, indem eine Reihe von Zahlen addiert und dann diese Summe durch die Anzahl der Werte in der geteilt wird Bereich. Ein gewichteter Durchschnitt hingegen betrachtet eine oder mehrere Zahlen im Bereich als wertvoller oder gewichtiger als die anderen Zahlen.

So funktioniert die SUMPRODUCT-Funktion

SUMPRODUCT multipliziert die Elemente von zwei oder mehr Arrays und fügt dann die Produkte hinzu, um gewichtete Durchschnitte zu berechnen. Zum Beispiel sind bestimmte Noten in der Schule, wie Zwischen- und Abschlussprüfungen, in der Regel mehr wert als reguläre Tests oder Hausaufgaben. Bei einer Mittelwertbildung zur Berechnung der Abschlussnote eines Studierenden würden die Zwischen- und Abschlussprüfungen stärker gewichtet.

In einer Situation, in der zwei Arrays mit je vier Elementen eingegeben werden als Argumente für die SUMMENPRODUKT-Funktion:

• 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

Eine Funktion Syntax bezieht sich auf das Layout der Funktion und enthält den Namen, die Klammern und die Argumente der Funktion. Die Syntax für die Funktion SUMMENPRODUKT 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 sein: Zellreferenzen an die Position der Daten im Arbeitsblatt oder durch arithmetische Operatoren getrennte Zahlen, z. B. Plus (+) oder Minus (-). Wenn Sie Zahlen eingeben, die nicht durch Operatoren getrennt sind, behandelt Excel diese als Textdaten.

Geben Sie die Gewichtungsformel ein

Das in diesem Artikel gezeigte Beispiel berechnet den gewichteten Durchschnitt für die Endnote eines Schülers mithilfe der SUMMENPRODUKT-Funktion.

Die Funktion erreicht dies durch:

• Multiplizieren der verschiedenen Markierungen mit ihrem individuellen Gewichtungsfaktor.
• Addieren der Produkte dieser Multiplikationsoperationen zusammen.
• Dividieren der obigen Summe durch die Summe des Gewichtungsfaktors 7 (1+1+2+3) für die vier Bewertungen.

Wie die meisten anderen Funktionen in Excel kann SUMPRODUCT mit Hilfe der Funktionsbibliothek gefunden auf dem Formeln Tab. Da die Gewichtungsformel in diesem Beispiel SUMPRODUCT nicht standardmäßig verwendet (das Ergebnis der Funktion wird durch den Gewichtungsfaktor geteilt), muss die Gewichtungsformel in a. eingegeben werden Arbeitsblatt Zelle.

Um die SUMMENPRODUKT-Formel einzugeben, um einen gewichteten Durchschnitt zu berechnen, öffnen Sie ein leeres Arbeitsblatt und geben Sie die Daten in Zeilen ein 1 durch 6 aus dem obigen Bild und gehen Sie wie folgt vor:

1. Zelle auswählen C7 um es zu machen aktive Zelle (Dies ist der Ort, an dem die Endnote des Schülers angezeigt wird).

2. Geben Sie die Formel ein =SUMMENPRODUKT(B3:B6,C3:C6)/(1+1+2+3) in die Zelle. Die Formel wird in der Formelleiste angezeigt.

3. Drücken Sie die EintretenTaste auf der Tastatur.

4. Die Antwort 78.6 erscheint in Zelle C7 (Ihre Antwort hat möglicherweise mehr Nachkommastellen).

Der ungewichtete Durchschnitt für die gleichen vier Noten wäre 76.5. Da der Student bei seinen Zwischen- und Abschlussprüfungen bessere Ergebnisse erzielte, half das Abwägen des Durchschnitts, die Gesamtnote zu verbessern.

SUMPRODUCT Formelvariationen

Um zu betonen, dass die Ergebnisse der SUMMENPRODUKT-Funktion durch die Summe der Gewichte für jede Bewertungsgruppe geteilt werden, wird der Divisor (der Teil, der die Division durchführt) wie folgt eingegeben:

(1+1+2+3)

Die Gesamtgewichtungsformel könnte durch Eingabe der Zahl vereinfacht werden 7 (die Summe der Gewichte) als Divisor. Die Formel wäre dann:

=SUMMENPRODUKT(B3:B6,C3:C6)/7. 

Diese Wahl ist in Ordnung, wenn die Anzahl der Elemente im Gewichtungsarray klein ist und sie leicht addiert werden können, aber es wird weniger effektiv, wenn die Anzahl der Elemente im Gewichtungs-Array zunimmt, wodurch ihre Addition größer wird schwierig.

Eine andere Option und wahrscheinlich die beste Wahl, da bei der Summierung des Divisors Zellreferenzen anstelle von Zahlen verwendet werden, wäre die Verwendung des Summenfunktion um den Teiler zu summieren. Die Formel lautet dann:

=SUMMENPRODUKT(B3:B6,C3:C6)/SUMME(B3:B6)

Normalerweise ist es am besten, Zellbezüge anstelle von tatsächlichen Zahlen in Formeln einzugeben. Dies vereinfacht die Aktualisierung, wenn sich die Daten der Formel ändern.

Wenn beispielsweise der Gewichtungsfaktor für Zuweisungen im Beispiel auf 0,5 geändert wird und Tests auf 1.5 geändert, müssen die ersten beiden Formen der Formel manuell bearbeitet werden, um die Divisor.

In der dritten Variante müssen nur die Daten in den Zellen B3 und B4 aktualisiert werden und die Formel berechnet das Ergebnis neu.