Генеричка формула
=SUMPRODUCT(weights,values)/SUM(weights)
Резиме
Да бисте израчунали пондерисани просек, можете да користите функцију СУМПРОДУЦТ заједно са функцијом СУМ. У приказаном примеру, формула у Г5, копирана доле, је:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
где је тежина именовани опсег И5: К5.
Објашњење
Пондерисани просек, који се назива и пондерисана средња вредност, просек је у коме се неке вредности рачунају више од других. Другим речима, неке вредности имају већу „тежину“. Пондерисани просек можемо израчунати множењем вредности са просеком одговарајућим пондерима, а затим поделити збир резултата са збиром пондера. У Екцелу се ово може представити доњом генеричком формулом, где су пондери и вредности опсези ћелија:
=SUMPRODUCT(weights,values)/SUM(weights)
На приказаном радном листу, резултати за 3 теста појављују се у колонама Ц до Е, а пондери су у именованим пондерима опсега (И5: К5). Формула у ћелији Г5 је:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Радећи изнутра, прво користимо функцију СУМПРОДУЦТ да помножимо пондере са одговарајућим резултатима и збројимо резултат:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
СУМПРОДУЦТ прво множи одговарајуће елементе два низа, а затим враћа збир производа:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Резултат се затим дели збиром пондера:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Како се формула копира доле у колону Г, именовани пондери опсега И5: К5 се не мењају, јер се понаша као апсолутна референца. Међутим, оцене у Ц5: Е5, унете као релативна референца, ажурирају се у сваком новом реду. Резултат је пондерисани просек за свако име на листи као што је приказано. Просек у колони Ф израчунава се само за референцу са АВЕРАГЕ функцијом:
=AVERAGE(C5:E5)
Пондери који се не збрајају на 1
У овом примеру су тежине конфигурисане да додају до 1, тако да је делилац увек 1, а резултат је вредност коју враћа СУМПРОДУЦТ. Међутим, лепа карактеристика формуле је да тежине не морају да се збрајају до 1.
На пример, могли бисмо да користимо тег од 1 за прва два теста и тег од 2 за финале (пошто је финале двоструко важније), а пондерисани просек ће бити исти:
У ћелији Г5, формула је решена овако:
=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
Напомена: Вредности у завојним заградама () горе су опсези изражени као низови.
Транспоновани тегови
Функција СУМПРОДУЦТ захтева да димензије низа буду компатибилне. Ако димензије нису компатибилне, СУМПРОДУЦТ ће вратити грешку #ВАЛУЕ. У доњем примеру, пондери су исти као оригинални пример, али наведени су у вертикалном опсегу:
Да бисмо израчунали пондерисани просек са истом формулом, треба да „преокренемо“ пондере у водоравни низ помоћу функције ТРАНСПОСЕ:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Након рада ТРАНСПОСЕ, вертикални низ:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
постаје:
=(0.25,0.25,0.5) // horizontal array
И од овог тренутка, формула се понаша као и раније.
Прочитајте још: вертикални и хоризонтални низови.
