Üldine valem
=SUMPRODUCT(weights,values)/SUM(weights)
Kokkuvõte
Kaalutud keskmise arvutamiseks võite kasutada funktsiooni SUMPRODUCT koos funktsiooniga SUM. Näidatud näites on G5 valem, mis on kopeeritud, järgmine:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
kus kaal on nimega vahemik I5: K5.
Selgitus
Kaalutud keskmine, mida nimetatakse ka kaalutud keskmiseks, on keskmine, kus mõned väärtused loevad rohkem kui teised. Teisisõnu, mõnel väärtusel on rohkem "kaalu". Kaalutud keskmise saame arvutada, korrutades väärtused keskmistele vastavate kaaludega, jagades seejärel tulemuste summa kaalude summaga. Excelis saab seda esitada allpool toodud üldise valemiga, kus kaalud ja väärtused on lahtrivahemikud:
=SUMPRODUCT(weights,values)/SUM(weights)
Töölehe näidanud, hinded 3 testid ilmuvad veerud C-E ja kaalud on nimega vahemik kaalu (I5: K5). Lahtris G5 on järgmine valem:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Seestpoolt välja töötades kasutame kõigepealt funktsiooni SUMPRODUCT, et korrutada kaalud vastavate punktidega ja tulemus kokku:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT korrutab kõigepealt kahe massiivi vastavad elemendid kokku ja tagastab seejärel toote summa:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Seejärel jagatakse tulemus kaalude summaga:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Kui valem kopeeritakse veergu G, ei muutu nimetatud vahemiku kaal I5: K5, kuna see käitub absoluutse võrdlusena. Suhtelise võrdlusalusena sisestatud C5: E5 skoore värskendatakse siiski igas uues reas. Tulemuseks on iga loendis oleva nime kaalutud keskmine, nagu näidatud. F-veeru keskmine arvutatakse ainult funktsiooniga AVERAGE:
=AVERAGE(C5:E5)
Kaalud, mis ei lähe kokku 1-ga
Selles näites on kaalud konfigureeritud lisama 1, nii et jagaja on alati 1 ja tulemuseks on väärtus, mille tagastab SUMPRODUCT. Valemi kena omadus on aga see, et kaalu pole vaja kokku liita 1.
Näiteks võiksime esimese kahe testi jaoks kasutada kaalu 1 ja finaalis kaalu 2 (kuna finaal on kaks korda olulisem) ja kaalutud keskmine on sama:
Lahtris G5 on valem lahendatud järgmiselt:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Märkus: ülaltoodud lokkisulgudes () olevad väärtused on vahemikud, mis on väljendatud massiividena.
Kaalude ülekandmine
Funktsioon SUMPRODUCT nõuab, et massiivi mõõtmed oleksid ühilduvad. Kui mõõtmed ei ühildu, tagastab SUMPRODUCT vea #VALUE. Allpool toodud näites on kaalud samad kui algsel näidisel, kuid need on loetletud vertikaalses vahemikus:
Kaalutud keskmise arvutamiseks sama valemiga peame kaalud horisontaalsesse massiivi keerama funktsiooniga TRANSPOSE järgmiselt:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Pärast TRANSPOSE käivitamist vertikaalne massiiv:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
saab:
=(0.25,0.25,0.5) // horizontal array
Ja sellest hetkest alates käitub valem nagu varem.
Loe lisaks: vertikaalsed ja horisontaalsed massiivid.
