Obecný vzorec
=SUMPRODUCT(weights,values)/SUM(weights)
souhrn
Pro výpočet váženého průměru můžete použít funkci SUMPRODUCT společně s funkcí SUM. V zobrazeném příkladu je vzorec v G5, zkopírovaný dolů, následující:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
kde váhy je pojmenovaný rozsah I5: K5.
Vysvětlení
Vážený průměr, nazývaný také vážený průměr, je průměr, kde některé hodnoty počítají více než jiné. Jinými slovy, některé hodnoty mají větší „váhu“. Můžeme vypočítat vážený průměr vynásobením hodnot k průměru odpovídajícími váhami a poté vydělením součtu výsledků součtem váh. V aplikaci Excel to lze vyjádřit pomocí obecného vzorce níže, kde váhy a hodnoty jsou rozsahy buněk:
=SUMPRODUCT(weights,values)/SUM(weights)
V zobrazeném listu se skóre pro 3 testy objeví ve sloupcích C až E a váhy jsou v pojmenovaných váhách rozsahu (I5: K5). Vzorec v buňce G5 je:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Při práci zevnitř ven nejdříve pomocí funkce SUMPRODUCT vynásobíme váhy odpovídajícími skóre a sečteme výsledek:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT nejprve znásobí odpovídající prvky dvou polí dohromady, poté vrátí součet produktu:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Výsledek se poté vydělí součtem vah:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Protože vzorec je zkopírován dolů do sloupce G, nemění se pojmenované váhy rozsahu I5: K5, protože se chová jako absolutní reference. Skóre v C5: E5, zadaná jako relativní reference, se však aktualizují v každém novém řádku. Výsledkem je vážený průměr pro každé jméno v seznamu, jak je znázorněno. Průměr ve sloupci F se počítá pro referenci pouze s funkcí PRŮMĚR:
=AVERAGE(C5:E5)
Váhy, které nesčítají 1
V tomto příkladu jsou váhy nakonfigurovány tak, aby přidávaly až 1, takže dělitel je vždy 1 a výsledkem je hodnota vrácená programem SUMPRODUCT. Pěknou vlastností vzorce je, že váhy nemusí přidávat až 1.
Například bychom mohli použít váhu 1 pro první dva testy a váhu 2 pro finále (protože finále je dvakrát tak důležité) a vážený průměr bude stejný:
V buňce G5 je vzorec vyřešen takto:
=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
Poznámka: hodnoty ve složených závorkách () výše jsou rozsahy vyjádřené jako pole.
Transponování závaží
Funkce SUMPRODUCT vyžaduje, aby rozměry pole byly kompatibilní. Pokud rozměry nejsou kompatibilní, vrátí SUMPRODUCT chybu #HODNOTA. V níže uvedeném příkladu jsou váhy stejné jako původní příklad, ale jsou uvedeny ve vertikálním rozsahu:
Chcete-li vypočítat vážený průměr se stejným vzorcem, musíme „převrátit“ váhy do vodorovného pole pomocí funkce TRANSPOSE takto:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Po spuštění TRANSPOSE vertikální pole:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
se stává:
=(0.25,0.25,0.5) // horizontal array
A od tohoto bodu se vzorec chová jako předtím.
Číst dále: vertikální a horizontální pole.
