Bendroji formulė
=SUMPRODUCT(weights,values)/SUM(weights)
Santrauka
Norėdami apskaičiuoti svertinį vidurkį, galite naudoti funkciją SUMPRODUCT kartu su funkcija SUM. Parodytame pavyzdyje G5 formulė, nukopijuota žemiau, yra:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
kur svoriai yra įvardytas diapazonas I5: K5.
Paaiškinimas
Svertinis vidurkis, dar vadinamas svertiniu vidurkiu, yra vidurkis, kai vienos vertės skaičiuojamos daugiau nei kitos. Kitaip tariant, kai kurios vertybės turi daugiau „svorio“. Mes galime apskaičiuoti svertinį vidurkį, padauginę vertes iš vidutinių iš atitinkamų svorių, tada rezultatų sumą padaliję iš svorių sumos. „Excel“ programoje tai galima pateikti naudojant toliau pateiktą bendrą formulę, kur svoriai ir reikšmės yra langelių diapazonai:
=SUMPRODUCT(weights,values)/SUM(weights)
Parodytame darbalapyje 3 testų rezultatai rodomi stulpeliuose C – E, o svoriai yra nurodytame diapazono svoryje (I5: K5). G5 langelio formulė yra:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Dirbdami iš vidaus į išorę, mes pirmiausia naudojame funkciją SUMPRODUCT, kad svoriai padaugintume iš atitinkamų rezultatų ir susumuotume rezultatą:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
„SUMPRODUCT“ pirmiausia padaugina atitinkamus dviejų masyvų elementus kartu, tada pateikia produkto sumą:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Tada rezultatas padalijamas iš svorių sumos:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Kadangi formulė nukopijuojama žemiau G stulpelio, įvardyti diapazono svoriai I5: K5 nesikeičia, nes jie elgiasi kaip absoliuti nuoroda. Tačiau C5: E5 balai, įvesti kaip santykinė nuoroda, atnaujinami kiekvienoje naujoje eilutėje. Rezultatas yra kiekvieno sąrašo vardo svertinis vidurkis, kaip parodyta. F stulpelio vidurkis apskaičiuojamas tik kaip nuoroda naudojant AVERAGE funkciją:
=AVERAGE(C5:E5)
Svoriai, kurių suma nesiekia 1
Šiame pavyzdyje svoriai sukonfigūruoti taip, kad būtų sudaryti iki 1, todėl daliklis visada yra 1, o rezultatas yra SUMPRODUCT grąžinta vertė. Tačiau maloni formulės ypatybė yra ta, kad svorių nereikia pridėti iki 1.
Pavyzdžiui, pirmiesiems dviem bandymams galėtume naudoti 1 svorį, o finalui - 2 svorį (nes finalas yra dvigubai svarbesnis), o svertinis vidurkis bus toks pats:
G5 langelyje formulė išspręsta taip:
=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
Pastaba: aukščiau garbanotų petnešėlių () vertės yra diapazonai, išreikšti masyvais.
Svorių perkėlimas
SUMPRODUCT funkcija reikalauja, kad masyvo matmenys būtų suderinami. Jei matmenys nesuderinami, SUMPRODUCT pateiks #VALUE klaidą. Žemiau pateiktame pavyzdyje svoriai yra tokie patys kaip pradinio pavyzdžio, tačiau jie išvardyti vertikaliu diapazonu:
Norėdami apskaičiuoti svertinį vidurkį pagal tą pačią formulę, turime „apversti“ svorius į horizontalią masyvą, naudodami funkciją TRANSPOSE:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Paleidus TRANSPOSE, vertikalus masyvas:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
tampa:
=(0.25,0.25,0.5) // horizontal array
Nuo šio taško formulė elgiasi taip, kaip anksčiau.
Skaitykite daugiau: vertikalios ir horizontalios masyvai.
