Hallo Sie die das an:
So berechnen Sie gewichtete Mittelwerte
Versionen: Excel 2010, 2007, 2003 und 2002/XP
Durchschnittswerte lassen sich mit der Funktion MITTELWERT schnell berechnen. Aber was ist, wenn die Basiswerte der Durchschnittsberechnung unterschiedlich gewichtet werden sollen? Die Funktion MITTELWERT besitzt keine Zusatzparameter, mit denen Sie Gewichtungen zuweisen koennten. In diesem Tipp stellen wir Ihnen deshalb eine Formel vor, die die Luecke schliesst.
Ein klassisches Beispiel fuer die Berechnung gewichteter Mittelwerte sind Schulnoten, die sich aus muendlichen und schriftlichen Bewertungen zusammensetzen. So koennte die muendliche Note zu 60 Prozent in die Gesamtnote einfliessen und die schriftlichen Tests zu insgesamt 40 Prozent. Dabei koennten die schriftlichen Arbeiten wiederum unterschiedlich bewertet werden - etwa 10 Prozent fuer den ersten Test und jeweils 15 Prozent fuer den zweiten und dritten.
In einer Excel-Tabelle koennten Sie dafuer folgende Basiswerte eingeben:
B4: Test1
C4: Test2
D4: Test3
E4: Muendlich
B5: 10%
C5: 15%
D5: 15%
E5: 60%
Sie haben damit die Beschreibungen und die Gewichtungen in zwei Zeilen einer Tabelle erfasst. Darunter geben Sie die Noten ein, die Sie den Schuelern in den Tests und im Muendlichen gegeben haben. Ein Beispiel:
B6: 5
C6: 2
D6: 2
E6: 1
Der normale, ungewichtete Mittelwert ergaebe in diesem Fall „2,5“ - also in der Gesamtnote bestenfalls eine 2-, wenn nicht sogar eine 3+.
Aufgrund der Gewichtung der Einzelnoten kommt die 5 im ersten Test aber nicht so stark zum Tragen und die 1 im Muendlichen fuehrt nochmals zu einer besseren Bewertung. Der gewichtete Mittelwert entspricht tatsaechlich einer Gesamtnote von „1,7“.
Und wie berechnen Sie dieses gewichtete Mittel? Zuerst muessen Sie die Einzelwerte mit den jeweiligen Gewichtungen multiplizieren. Die Summe daraus teilen Sie anschliessend durch die Summe aller Gewichtungsanteile. Bei Gewichtungsanteilen, die wie hier in der Summe 100% ergeben, ist diese Division nicht ganz einleuchtend. Aber in unserem zweiten Beispiel wird dieser zusaetzliche Schritt plausibel.
In einer Excel-Formel sehen die Berechnungsschritte generell wie folgt aus:
=SUMMENPRODUKT(
/
SUMME()
Im Fall der Schulnoten koennen Sie zum Beispiel in Zelle F6 folgende Formel fuer die Gesamtnote eingeben:
=SUMMENPRODUKT(B6:E6;$B$5:blush:E$5)/SUMME($B$5:blush:E$5)
Durch die Verwendung von absoluten und relativen Bezuegen laesst sich diese Formel problemlos nach unten kopieren, um die Gesamtnoten fuer andere Schueler auszurechnen.
Hier noch ein zweites Beispiel, in dem klar wird, warum eine Division durch die Summe aller Gewichtungsanteile erforderlich ist: Angenommen, Sie haetten die Kraftstoffkosten fuer Ihr Auto in einer Tabelle erfasst: In Spalte A steht das Datum, an dem Sie getankt haben, in Spalte B der Kilometerstand, in Spalte C die getankten Liter, in Spalte D der Preis fuer die Tankfuellung und in Spalte E der jeweilige Preis pro Liter Kraftstoff. Nun moechten Sie gerne wissen, wie viel Sie im Laufe der Zeit durchschnittlich fuer einen Liter Kraftstoff bezahlt haben.
Dafuer koennen Sie nicht einfach den Mittelwert der Literpreise in Spalte E nehmen, denn damit wuerde nicht beruecksichtigt, dass Sie immer unterschiedliche Mengen an Kraftstoff getankt haben. Hier ist es wichtig, dass Sie das Summenprodukt aus Literpreis und Kraftstoffmenge noch einmal durch die gesamte Kraftstoffmenge teilen.
Wenn die Literpreise beispielsweise im Bereich E2:E100 stehen und die getankten Liter im Bereich C2:C100, lautet die korrekte Formel fuer den gewichteten Mittelwert des
Literpreises:
=SUMMENPRODUKT(E2:E100;C2:C100)/SUMME(C2:C100)
Gruß - Wolfgang
