Excel hat das Berechnen des Durchschnitts mehrerer Zellen zu einer sehr einfachen Aufgabe gemacht – verwenden Sie einfach die Funktion DURCHSCHNITTLICH (DURCHSCHNITT). Was aber, wenn einige Werte mehr Gewicht haben als andere? Beispielsweise haben in vielen Kursen Tests mehr Gewicht als Aufgaben. Für solche Fälle muss gerechnet werden gewichteter Durchschnitt.
Excel hat keine Funktion zur Berechnung des gewichteten Durchschnitts, aber es gibt eine Funktion, die Ihnen die meiste Arbeit abnimmt: SUMMENPRODUKT (SUMMENPRODUKT). Und selbst wenn Sie diese Funktion noch nie zuvor verwendet haben, werden Sie sie am Ende dieses Artikels wie ein Profi verwenden. Die von uns verwendete Methode funktioniert in jeder Version von Excel sowie anderen Tabellenkalkulationen wie Google Sheets.
Wir bereiten den Tisch vor
Wenn Sie einen gewichteten Durchschnitt berechnen möchten, benötigen Sie mindestens zwei Spalten. Die erste Spalte (in unserem Beispiel Spalte B) enthält die Punktzahlen für jede Aufgabe oder jeden Test. Die zweite Spalte (Spalte C) enthält die Gewichte. Mehr Gewicht bedeutet mehr Einfluss der Aufgabe oder des Tests auf die Endnote.
Um zu verstehen, was Gewicht ist, können Sie es sich als Prozentsatz Ihrer Abschlussnote vorstellen. Tatsächlich ist dies nicht der Fall, da sich in diesem Fall die Gewichte zu 100 % addieren müssten. Die Formel, die wir in dieser Lektion analysieren werden, berechnet alles richtig und hängt nicht von der Summe der Gewichte ab.
Wir geben die Formel ein
Nachdem unsere Tabelle nun fertig ist, fügen wir die Formel der Zelle hinzu B10 (jede leere Zelle reicht). Wie bei jeder anderen Formel in Excel beginnen wir mit einem Gleichheitszeichen (=).
Der erste Teil unserer Formel ist die Funktion SUMMENPRODUKT (SUMMENPRODUKT). Argumente müssen in Klammern eingeschlossen werden, also öffnen wir sie:
=СУММПРОИЗВ(
=SUMPRODUCT(
Fügen Sie als Nächstes die Funktionsargumente hinzu. SUMMENPRODUKT (SUMPRODUCT) kann mehrere Argumente haben, aber normalerweise werden zwei verwendet. In unserem Beispiel ist das erste Argument ein Zellbereich. B2:B9A, das die Partituren enthält.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Das zweite Argument ist ein Zellbereich C2:C9, die die Gewichte enthält. Diese Argumente müssen durch ein Semikolon (Komma) getrennt werden. Wenn alles fertig ist, schließen Sie die Klammern:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Fügen wir nun den zweiten Teil unserer Formel hinzu, der das von der Funktion berechnete Ergebnis dividiert SUMMENPRODUKT (SUMMENPRODUKT) durch die Summe der Gewichte. Wir werden später diskutieren, warum dies wichtig ist.
Um die Divisionsoperation durchzuführen, setzen wir die bereits eingegebene Formel mit dem Symbol fort / (gerader Schrägstrich) und schreiben Sie dann die Funktion SUM (SUMME):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Für Funktion SUM (SUMME) geben wir nur ein Argument an – eine Reihe von Zellen C2:C9. Vergessen Sie nicht, die Klammern nach der Eingabe des Arguments zu schließen:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Bereit! Nach Drücken der Taste Enter, berechnet Excel den gewichteten Durchschnitt. In unserem Beispiel lautet das Endergebnis 83,6.
So funktioniert's
Lassen Sie uns jeden Teil der Formel aufschlüsseln, beginnend mit der Funktion SUMMENPRODUKT (SUMMENPRODUKT), um zu verstehen, wie es funktioniert. Funktion SUMMENPRODUKT (SUMPRODUCT) berechnet das Produkt aus der Punktzahl jedes Elements und seinem Gewicht und summiert dann alle resultierenden Produkte. Mit anderen Worten, die Funktion findet die Summe der Produkte, daher der Name. So für Aufgaben 1 multipliziere 85 mit 5, und für Der Test multipliziere 83 mit 25.
Wenn Sie sich fragen, warum wir die Werte im ersten Teil multiplizieren müssen, stellen Sie sich vor, je größer das Gewicht der Aufgabe ist, desto öfter müssen wir die Note dafür berücksichtigen. Zum Beispiel, Aufgabe 2 5 mal gezählt und Abschlussprüfung – 45 mal. Deshalb Abschlussprüfung hat einen größeren Einfluss auf die Abschlussnote.
Zum Vergleich wird bei der Berechnung des üblichen arithmetischen Mittels jeder Wert nur einmal berücksichtigt, d.h. alle Werte haben das gleiche Gewicht.
Wenn Sie unter die Haube einer Funktion schauen könnten SUMMENPRODUKT (SUMPRODUKT), wir haben gesehen, dass sie tatsächlich Folgendes glaubt:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Zum Glück müssen wir keine so lange Formel schreiben, weil SUMMENPRODUKT (SUMPRODUCT) macht das alles automatisch.
Eine Funktion für sich SUMMENPRODUKT (SUMMENPRODUKT) gibt uns eine riesige Zahl zurück − 10450. An dieser Stelle kommt der zweite Teil der Formel ins Spiel: /SUMME(C2:C9) or /SUMME(C2:C9), die das Ergebnis auf den normalen Bereich der Punktzahlen zurückführt und die Antwort gibt 83,6.
Der zweite Teil der Formel ist sehr wichtig, da Sie Berechnungen automatisch korrigieren können. Denken Sie daran, dass sich die Gewichte nicht zu 100 % addieren müssen? All dies dank des zweiten Teils der Formel. Wenn wir beispielsweise einen oder mehrere Gewichtswerte erhöhen, wird der zweite Teil der Formel einfach durch den größeren Wert dividiert, was wiederum zur richtigen Antwort führt. Oder wir können die Gewichte viel kleiner machen, indem wir zum Beispiel Werte wie angeben 0,5, 2,5, 3 or 4,5, und die Formel wird weiterhin korrekt funktionieren. Es ist großartig, oder?