Általános képlet
=SUMPRODUCT(weights,values)/SUM(weights)
Összegzés
A súlyozott átlag kiszámításához használhatja a SUMPRODUCT függvényt a SUM függvénnyel együtt. A bemutatott példában a G5 képlete, lemásolva:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
ahol a súlyok az I5: K5 megnevezett tartomány.
Magyarázat
A súlyozott átlag, más néven súlyozott átlag, olyan átlag, ahol egyes értékek többet számítanak, mint mások. Más szavakkal, egyes értékeknek nagyobb a „súlya”. Kiszámíthatunk súlyozott átlagot úgy, hogy az értékeket megszorozzuk az átlaggal a megfelelő súlyokkal, majd elosztjuk az eredmények összegét a súlyok összegével. Az Excelben ez az alábbi általános képlettel ábrázolható, ahol a súlyok és az értékek cellatartományok:
=SUMPRODUCT(weights,values)/SUM(weights)
A bemutatott munkalapon 3 teszt pontszáma jelenik meg a C – E oszlopokban, a súlyok pedig a megnevezett tartományi súlyokban vannak (I5: K5). A G5 cellában szereplő képlet a következő:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Belülről kifelé haladva először a SUMPRODUCT függvényt használjuk a súlyok és a megfelelő pontszámok szorzására és az eredmény összegzésére:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
A SUMPRODUCT először megszorozza a két tömb megfelelő elemeit, majd visszaadja a szorzat összegét:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Az eredményt elosztjuk a súlyok összegével:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Amint a képletet lefelé másoljuk a G oszlopba, a megnevezett I5: K5 súlyok nem változnak, mivel abszolút referenciaként viselkedik. A relatív referenciaként megadott C5: E5 pontszámok azonban frissülnek minden új sorban. Az eredmény a lista minden egyes nevének súlyozott átlaga, az ábra szerint. Az F oszlop átlagát csak az ÁTLAG függvény referenciaként számoljuk:
=AVERAGE(C5:E5)
Súlyok, amelyek nem 1-nek felelnek meg
Ebben a példában a súlyok úgy vannak konfigurálva, hogy összeadják az 1-et, így az osztó mindig 1, és az eredmény a SUMPRODUCT által visszaadott érték. A képlet szép tulajdonsága, hogy a súlyoknak nem kell 1-ig összeadódniuk.
Például használhatunk 1-et az első két teszthez, és 2-t a döntőhöz (mivel a döntő kétszer olyan fontos), és a súlyozott átlag megegyezik:
A G5 cellában a képlet így oldódik meg:
=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
Megjegyzés: a fenti göndör zárójelben () szereplő értékek tömbben kifejezett tartományok.
A súlyok átültetése
A SUMPRODUCT funkció megköveteli, hogy a tömb méretei kompatibilisek legyenek. Ha a dimenziók nem kompatibilisek, a SUMPRODUCT #VALUE hibát ad vissza. Az alábbi példában a súlyok megegyeznek az eredeti példával, de függőleges tartományban vannak felsorolva:
A súlyozott átlag kiszámításához ugyanazzal a képlettel a súlyokat vízszintes tömbbe kell "fordítanunk" a TRANSPOSE függvény segítségével:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
A TRANSPOSE futtatása után a függőleges tömb:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
válik:
=(0.25,0.25,0.5) // horizontal array
És ettől kezdve a képlet úgy viselkedik, mint korábban.
Bővebben: függőleges és vízszintes tömbök.
