Обща формула
=SUMPRODUCT(weights,values)/SUM(weights)
Обобщение
За да изчислите претеглена средна стойност, можете да използвате функцията SUMPRODUCT заедно с функцията SUM. В показания пример формулата в G5, копирана надолу, е:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
където теглото е посоченият диапазон I5: K5.
Обяснение
Претеглена средна стойност, наричана още средно претеглена, е средна стойност, при която някои стойности се броят повече от други. С други думи, някои стойности имат по-голяма „тежест“. Можем да изчислим претеглена средна стойност, като умножим стойностите по средни по съответните тегла, след което сумата от резултатите се раздели на сумата от теглата. В Excel това може да бъде представено с общата формула по-долу, където теглата и стойностите са диапазони от клетки:
=SUMPRODUCT(weights,values)/SUM(weights)
В показания работен лист резултатите за 3 теста се появяват в колони C до E, а теглата са в посочените тегла на диапазона (I5: K5). Формулата в клетка G5 е:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Работейки отвътре навън, първо използваме функцията SUMPRODUCT, за да умножим тежестите по съответните резултати и да сумираме резултата:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT първо умножава съответните елементи на двата масива заедно, след което връща сумата на произведението:
=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
Тъй като формулата се копира надолу в колона G, посочените тегла на диапазона I5: K5 не се променят, тъй като се държат като абсолютна референция. Резултатите в C5: E5, въведени като относителна препратка, се актуализират във всеки нов ред. Резултатът е средно претеглена стойност за всяко име в списъка, както е показано. Средната стойност в колона F се изчислява само за справка с функцията AVERAGE:
=AVERAGE(C5:E5)
Тегла, които не се равняват на 1
В този пример теглата са конфигурирани да добавят до 1, така че делителят винаги е 1, а резултатът е стойността, върната от SUMPRODUCT. Хубава характеристика на формулата обаче е, че тежестите не трябва да се събират до 1.
Например, бихме могли да използваме тежест 1 за първите два теста и тежест 2 за финала (тъй като финалът е два пъти по-важен) и среднопретеглената средна стойност ще бъде еднаква:
В клетка G5 формулата се решава по следния начин:
=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 изисква размерите на масива да бъдат съвместими. Ако размерите не са съвместими, SUMPRODUCT ще върне грешка #VALUE. В примера по-долу теглата са същите като оригиналния пример, но са изброени във вертикален диапазон:
За да изчислим среднопретеглена средна стойност със същата формула, трябва да „обърнем“ тежестите в хоризонтален масив с функцията TRANSPOSE по следния начин:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
След стартиране на TRANSPOSE вертикалният масив:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
става:
=(0.25,0.25,0.5) // horizontal array
И от този момент формулата се държи както преди.
Прочетете повече: вертикални и хоризонтални масиви.
