Jak vypočítat vážené průměry v Excelu pomocí SUMPRODUCT

Obvykle, když vypočítáte an průměrný nebo aritmetický průměr, každé číslo má stejnou hodnotu nebo váhu. Průměr se vypočítá sečtením řady čísel a následným vydělením tohoto součtu počtem hodnot v rozsah. Na druhé straně vážený průměr považuje jedno nebo více čísel v rozsahu za vyšší nebo větší váhu než ostatní čísla.

Jak funguje funkce SUMPRODUCT

SUMPRODUCT násobí prvky dvou nebo více pole a poté přidá produkty pro výpočet vážených průměrů. Například určité známky ve škole, jako jsou průběžné a závěrečné zkoušky, jsou obvykle cennější než běžné testy nebo úkoly. Pokud se k výpočtu výsledné známky studenta použije průměrování, bude pololetním a závěrečným zkouškám přikládána větší váha.

V situaci, kdy jsou dvě pole se čtyřmi prvky zadávána jako argumenty pro funkci SUMPRODUCT:

• První prvek pole1 je vynásoben prvním prvkem pole2.
• Druhý prvek pole1 je vynásoben druhým prvkem pole2.
• Třetí prvek pole1 je vynásoben třetím prvkem pole2.
• Čtvrtý prvek pole1 je vynásoben čtvrtým prvkem pole2.

Dále jsou součiny čtyř operací násobení sečteny a vráceny funkcí jako výsledek.

Syntaxe a argumenty SUMPRODUCT

Funkce syntax odkazuje na rozložení funkce a zahrnuje název funkce, závorky a argumenty. Syntaxe funkce SUMPRODUCT je:

=SUMPRODUKT(Pole1,Pole2,Pole3,...Pole255)

Argumenty pro funkci SUMPRODUCT jsou:

• Pole1: První argument pole (povinný).
• Pole2,Pole3,...Pole255: Další (volitelná) pole, až 255. U dvou nebo více polí funkce vynásobí prvky každého pole dohromady a poté sečte výsledky.

Prvky pole mohou být odkazy na buňky k umístění dat v listu nebo čísel oddělených aritmetickými operátory, jako jsou znaménka plus (+) nebo mínus (-). Pokud zadáte čísla, která nejsou oddělena operátory, Excel s nimi bude zacházet jako s textovými daty.

Zadejte váhový vzorec

Příklad uvedený v tomto článku vypočítává vážený průměr pro výslednou známku studenta pomocí funkce SUMPRODUCT.

Funkce toho dosáhne:

• Násobení různých známek jejich individuálním váhovým faktorem.
• Sčítání součinů těchto operací násobení dohromady.
• Vydělení výše uvedeného součtu součtem váhového faktoru 7 (1+1+2+3) pro čtyři hodnocení.

Stejně jako většinu ostatních funkcí v Excelu lze SUMPRODUCT zadat do listu pomocí Knihovna funkcí nalezený na Vzorce tab. Protože váhový vzorec v těchto příkladech používá SUMPRODUCT nestandardním způsobem (výsledek funkce je dělen váhovým faktorem), musí být váhový vzorec zadán do pracovní list buňka.

Chcete-li zadat vzorec SUMPRODUCT pro výpočet váženého průměru, otevřete prázdný list a zadejte data do řádků 1 přes 6 z obrázku výše a postupujte takto:

1. Vyberte buňku C7 aby to bylo aktivní buňka (toto je místo, kde se zobrazí výsledná známka studenta).

2. Zadejte vzorec =SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3) do buňky. Vzorec se zobrazí na řádku vzorců.

3. zmáčkni Vstupteklávesu na klávesnici.

4. Odpověď 78.6 se objeví v buňce C7 (Vaše odpověď může mít více desetinná místa).

Nevážený průměr pro stejné čtyři známky by byl 76.5. Protože student měl lepší výsledky u průběžných a závěrečných zkoušek, vážení průměru pomohlo zlepšit celkovou známku.

Variace receptury SUMPRODUCT

Aby se zdůraznilo, že výsledky funkce SUMPRODUCT jsou děleny součtem vah pro každou skupinu hodnocení, je dělitel (část provádějící dělení) zadán jako:

(1+1+2+3)

Celkový váhový vzorec lze zjednodušit zadáním čísla 7 (součet vah) jako dělitel. Vzorec by pak byl:

=SOUČETNÍ SOUČIN(B3:B6,C3:C6)/7. 

Tato volba je vhodná, pokud je počet prvků v poli vah malý a lze je snadno sečíst, ale stává se méně účinným, když se zvyšuje počet prvků v poli vah, takže jejich přidávání je více obtížný.

Další možností a pravděpodobně nejlepší volbou, vzhledem k tomu, že při sčítání dělitele používá spíše odkazy na buňky než čísla, by bylo použít funkce SUM sečíst dělitel. Vzorec je pak:

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

Obvykle je nejlepší zadávat do vzorců spíše odkazy na buňky než skutečná čísla. To zjednodušuje jejich aktualizaci, pokud se změní data vzorce.

Pokud se například váhové faktory pro Úkoly v příkladu změní na 0,5 a Testy ano změněno na 1.5, je třeba první dva tvary vzorce ručně upravit, aby se opravilo dělitel.

Ve třetí variantě je potřeba aktualizovat pouze data v buňkách B3 a B4 a vzorec přepočítá výsledek.