Wenn wir einen einfachen Durchschnitt einer gegebenen Menge von Werten berechnen, gehen wir davon aus, dass alle Werte das gleiche Gewicht oder die gleiche Bedeutung haben.
Wenn Sie beispielsweise zu Prüfungen erscheinen und alle Prüfungen ein ähnliches Gewicht haben, ist der Durchschnitt Ihrer Gesamtpunktzahl auch der gewichtete Durchschnitt Ihrer Punktzahlen.
Im wirklichen Leben ist dies jedoch kaum der Fall.
Manche Aufgaben sind immer wichtiger als die anderen. Einige Prüfungen sind wichtiger als die anderen.
Und das ist wo Gewichteter Durchschnitt kommt ins Bild.
Hier ist die Lehrbuchdefinition des gewichteten Durchschnitts:
Sehen wir uns nun an, wie Sie den gewichteten Durchschnitt in Excel berechnen.
Berechnen des gewichteten Durchschnitts in Excel
In diesem Tutorial erfahren Sie, wie Sie den gewichteten Durchschnitt in Excel berechnen:
- Verwenden der SUMMENPRODUKT-Funktion.
- Verwenden der SUM-Funktion.
Also lasst uns anfangen.
Berechnen des gewichteten Durchschnitts in Excel - SUMPRODUCT-Funktion
Es kann verschiedene Szenarien geben, in denen Sie den gewichteten Durchschnitt berechnen müssen. Im Folgenden sind drei verschiedene Situationen aufgeführt, in denen Sie die SUMMENPRODUKT-Funktion verwenden können, um den gewichteten Durchschnitt in Excel zu berechnen
Im Folgenden sind drei verschiedene Situationen aufgeführt, in denen Sie die SUMMENPRODUKT-Funktion verwenden können, um den gewichteten Durchschnitt in Excel zu berechnen
Beispiel 1 – Wenn sich die Gewichte auf 100 % summieren
Angenommen, Sie haben einen Datensatz mit Noten, die von einem Schüler in verschiedenen Prüfungen erzielt wurden, zusammen mit den Gewichtungen in Prozent (wie unten gezeigt):
In den oben genannten Daten erhält ein Schüler in verschiedenen Bewertungen Noten, muss aber am Ende eine Endnote oder Note erhalten. Ein einfacher Durchschnitt kann hier nicht berechnet werden, da die Bedeutung der verschiedenen Bewertungen unterschiedlich ist.
Zum Beispiel hat ein Quiz mit einer Gewichtung von 10 % das doppelte Gewicht im Vergleich zu einer Aufgabe, aber ein Viertel des Gewichtes im Vergleich zur Prüfung.
In einem solchen Fall können Sie die SUMMENPRODUKT-Funktion verwenden, um den gewichteten Durchschnitt der Punktzahl zu erhalten.
Hier ist die Formel, die Ihnen den gewichteten Durchschnitt in Excel liefert:
=SUMMENPRODUKT(B2:B8,C2:C8)
So funktioniert diese Formel: Die Excel-Funktion SUMMENPRODUKT multipliziert das erste Element des ersten Arrays mit dem ersten Element des zweiten Arrays. Dann multipliziert es das zweite Element des ersten Arrays mit dem zweiten Element des zweiten Arrays. Usw…
Und schließlich fügt es all diese Werte hinzu.
Hier ist eine Illustration, um es zu verdeutlichen.
Beispiel 2 – Wenn Gewichte nicht 100 % ergeben
Im obigen Fall wurden die Gewichte so vergeben, dass die Summe 100 % ergab. In realen Szenarien ist dies jedoch möglicherweise nicht immer der Fall.
Schauen wir uns das gleiche Beispiel mit unterschiedlichen Gewichten an.
Im obigen Fall addieren sich die Gewichte zu 200%.
Wenn ich dieselbe SUMPRODUCT-Formel verwende, erhalte ich das falsche Ergebnis.
Im obigen Ergebnis habe ich alle Gewichtungen verdoppelt und es gibt den gewichteten Durchschnittswert als 153,2 zurück. Jetzt wissen wir, dass ein Schüler nicht mehr als 100 von 100 erreichen kann, egal wie brillant er/sie ist.
Der Grund dafür ist, dass sich die Gewichte nicht zu 100 % addieren.
Hier ist die Formel, die das sortiert:
=SUMMENPRODUKT(B2:B8,C2:C8)/SUMME(C2:C8)
In der obigen Formel wird das SUMPRODUCT-Ergebnis durch die Summe aller Gewichtungen dividiert. Daher würden sich die Gewichte auf jeden Fall immer auf 100 % summieren.
Beispiel 3 – Wenn die Gewichte berechnet werden müssen
Im bisher behandelten Beispiel wurden die Gewichte angegeben. Es kann jedoch Fälle geben, in denen die Gewichtungen nicht direkt verfügbar sind und Sie zuerst die Gewichtungen und dann den gewichteten Durchschnitt berechnen müssen.
Angenommen, Sie verkaufen drei verschiedene Arten von Produkten, wie unten erwähnt:
Sie können den gewichteten Durchschnittspreis pro Produkt mit der Funktion SUMMENPRODUKT berechnen. Hier ist die Formel, die Sie verwenden können:
=SUMMENPRODUKT(B2:B4,C2:C4)/SUMME(B2:B4)
Die Division des SUMMENPRODUKT-Ergebnisses durch die SUMME der Mengen stellt sicher, dass sich die Gewichte (in diesem Fall Mengen) zu 100 % addieren.
Berechnen des gewichteten Durchschnitts in Excel - SUM-Funktion
Während die SUMMENPRODUKT-Funktion der beste Weg ist, um den gewichteten Durchschnitt in Excel zu berechnen, können Sie auch die SUMMEN-Funktion verwenden.
Um den gewichteten Durchschnitt mit der SUM-Funktion zu berechnen, müssen Sie jedes Element mit seiner prozentualen Bedeutung multiplizieren.
Verwenden des gleichen Datensatzes:
Hier die Formel, die Ihnen das richtige Ergebnis liefert:
=SUMME(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)
Diese Methode ist in Ordnung, wenn Sie ein paar Elemente haben. Wenn Sie jedoch viele Artikel und Gewichte haben, kann diese Methode umständlich und fehleranfällig sein. Es gibt einen kürzeren und besseren Weg, dies mit der SUM-Funktion zu tun.
Fortfahrend mit dem gleichen Datensatz ist hier die kurze Formel, die Ihnen den gewichteten Durchschnitt mit der SUM-Funktion liefert:
=SUMME(B2:B8*C2:C8)
Der Trick bei der Verwendung dieser Formel besteht darin, Strg + Umschalt + Eingabetaste zu verwenden, anstatt nur die Eingabetaste zu verwenden. Da die SUM-Funktion keine Arrays verarbeiten kann, müssen Sie Strg + Umschalt + Eingabetaste verwenden.
Wenn Sie Strg + Umschalt + Eingabetaste drücken, werden am Anfang und am Ende der Formel automatisch geschweifte Klammern angezeigt (siehe Formelleiste im obigen Bild).
Stellen Sie auch hier sicher, dass die Gewichte 100 % ergeben. Ist dies nicht der Fall, müssen Sie das Ergebnis durch die Summe der Gewichte dividieren (wie unten am Produktbeispiel gezeigt):
