Excel-Tipps SUMPRODUCT berechnen gewichteten Durchschnitt - Full Seite
Verwenden Sie SUMPRODUCT in Excel und anderen Tabellenkalkulationsprogrammen gewichtete Mittelwerte zu berechnen. Erfahren Sie alles über die Excel SUMPRODUCT Funktion hier.
Verwenden Sie SUMPRODUCT zu gewichtete Mittelwerte zu berechnen
Excel macht es extrem einfach, den Durchschnitt mehrerer Zellen zu berechnen: Verwenden Sie einfach die Funktion MITTELWERT. Aber was, wenn einige Werte haben mehr „Gewicht“ als andere? Zum Beispiel, in vielen Klassen sind die Tests mehr wert als die Zuweisungen. Für diese Situationen müssen Sie den gewichteten Mittelwert berechnen.
Obwohl Excel keine gewichtete durchschnittliche Funktion hat, hat es eine Funktion hat, die für Sie die meiste Arbeit tut: SUMPRODUCT. Selbst wenn Sie noch nie SUMPRODUCT verwendet haben, werden Sie in der Lage sein, es wie ein Profi am Ende dieses Artikels zu verwenden. Die Methode, die wir verwenden funktioniert mit allen Versionen von Excel, und es funktioniert auch mit anderen Tabellenkalkulationsprogrammen wie Google Sheets.
Wenn Sie folgen mögen, können Sie unser Beispiel herunterladen.
Einrichten der Tabelle
Um den gewichteten Mittelwert zu berechnen, müssen Sie mindestens zwei Spalten. Die erste Spalte (Spalte B in unserem Beispiel) enthält, die Noten für jeden Auftrag oder Test. Die zweite Spalte (Spalte C) enthält, die Gewichte. Ein höheres Gewicht wird die Zuordnung oder Test verursachen eine größere Wirkung auf die Endnote haben.
Sie können als der Prozentsatz der Gesamtnote des Gewichts denken. Aber in diesem Fall addieren sich die Gewichte tatsächlich bis zu mehr als 100%. Das ist in Ordnung, weil unsere Formel noch egal arbeiten, was die Gewichte summieren sich zu.
Eingabe der Formel
Nun, da haben wir unsere Tabelle einrichten, werden wir die Formel Zelle B10 (jede leere Zelle funktioniert) hinzuzufügen. Wie bei jeder Formel, starten Sie ein Gleichheitszeichen durch Eingabe (=).
Der erste Teil unserer Formel wird die SUMPRODUCT Funktion sein. Da die Argumente in Klammern sein wird, gehen Sie vor und geben Sie eine öffnende Klammer:
Als nächstes werden wir die Argumente der Funktion hinzuzufügen. SUMPRODUCT kann eine beliebige Anzahl von Argumenten haben, aber es wird in der Regel zwei. In unserem Beispiel wird das erste Argument den Zellbereich B2 sein: B9 -die Zellen, die unsere Qualitäten enthalten:
Das zweite Argument wird die Zelle C2 Bereich sein: C9 -Der Zellen, die die Gewichte enthalten. Sie werden ein Komma zu trennen diese beiden Argumente verwenden müssen. Wenn Sie fertig sind, geben Sie eine geschlossene Klammer:
Jetzt werden wir den zweiten Teil unserer Formel hinzuzufügen. Dieser Teil wird SUMPRODUCT durch die Summe der Gewichte unterteilt. Später werden wir darüber reden, warum dies wichtig ist.
Beginnen Sie mit einem / (Schrägstrich) für die Teilung eingeben und dann die Funktion SUMME eingeben:
Wir brauchen nur ein Argument für die Funktion SUMME: der Zellbereich C2: C9. Erinnern Sie sich die Klammern nach dem Argument zu schließen:
Das ist es! Wenn Sie die Eingabetaste auf der Tastatur drücken, wird Excel den gewichteten Mittelwert berechnen. In unserem Beispiel ist die Endnote 83.6.
Wie es funktioniert
Lassen Sie sich an jedem Teil dieser Formel, um zu sehen, wie es funktioniert, mit der SUMPRODUCT Funktion zu starten. SUMPRODUCT wird multipliziert (Suche nach dem Produkt aus) jedes Gewicht seiner Klasse mal die Zuordnung, dann zusammen alle Produkte hinzufügen. Mit anderen Worten, findet es die Summe der Produkte. das ist, wo es seinen Namen hat. Also für Aufgabe 1 es vervielfacht 85 von 5 und für das Test multipliziert es 83 von 25.
Wenn Sie sich fragen, warum benötigt die Werte in erster Linie multipliziert werden, denken Sie daran: Die Zuordnungen mit einem höheren Gewicht werden mehrmals gezählt. Zum Beispiel Aufgabe 2 wird 5 mal gezählt, aber die Abschlussprüfung 45 mal gezählt. Aus diesem Grund ist die Abschlussprüfung einen größeren Einfluss auf die Gesamtnote hat. Im Vergleich dazu wäre ein „normaler“ Durchschnitt jede Zuordnung eine Zeit zählen. so hat jeder das gleiche Gewicht.
Wenn Sie „unter der Haube“ der SUMPRODUCT Funktion aussehen könnten, würden Sie sehen, dass das ist, was es eigentlich ist die Berechnung:
Zum Glück, wir haben noch eine lange Formel wie folgt zu schreiben, weil SUMPRODUCT wird alles davon automatisch tun.
Von selbst, uns SUMPRODUCT wird eine große Anzahl geben: 10.450. Dies ist, wo der zweite Teil der Formel ist in: / SUM (C2: C9). Dieser Teil bringt den Wert wieder auf einen normalen Grade-Bereich, so dass die Antwort 83.6.
Der zweite Teil der Formel ist eigentlich wirklich nützlich, weil es die Formel ermöglicht automatisch seine Berechnungen zu korrigieren. Denken Sie daran, dass die Gewichte müssen zu 100% addieren sich nicht? Dies liegt daran, dieser Teil der Formel, dass kümmert sich um uns. Zum Beispiel, wenn man ein oder mehrere der Gewichte erhöhen, teilt sich der zweite Teil der Formel einfach durch eine höhere Anzahl, sie wieder auf die richtige Antwort zu bringen. Wir könnten sogar die Gewichte machen viel kleiner, so dass sie wie 0,5-Werte, 2.5, 3.0 und 4.5 und die Formel würde immer noch perfekt funktionieren. Recht ordentlich, nicht wahr?