Excel udělal z výpočtu průměru více buněk velmi snadný úkol – stačí použít funkci PRŮMĚRNÝ (PRŮMĚRNÝ). Ale co když některé hodnoty mají větší váhu než jiné? Například v mnoha kurzech mají testy větší váhu než úkoly. Pro takové případy je nutné počítat vážený průměr.
Excel nemá funkci pro výpočet váženého průměru, ale existuje funkce, která udělá většinu práce za vás: SUMPRODUCT (SOUČETOVÝ PRODUKT). A i když jste tuto funkci nikdy předtím nepoužívali, na konci tohoto článku ji budete používat jako profík. Metoda, kterou používáme, funguje v jakékoli verzi Excelu i v jiných tabulkách, jako jsou Tabulky Google.
Připravíme stůl
Pokud budete počítat vážený průměr, budete potřebovat alespoň dva sloupce. První sloupec (v našem příkladu sloupec B) obsahuje skóre pro každý úkol nebo test. Druhý sloupec (sloupec C) obsahuje váhy. Větší váha znamená větší vliv úlohy nebo testu na výslednou známku.
Abyste pochopili, co je to váha, můžete si to představit jako procento vaší konečné známky. Ve skutečnosti tomu tak není, protože v tomto případě by součet vah měl činit 100 %. Vzorec, který budeme v této lekci analyzovat, vše spočítá správně a nezávisí na množství, které váhy sčítají.
Zadáme vzorec
Nyní, když je naše tabulka připravena, přidáme vzorec do buňky B10 (postačí jakákoli prázdná buňka). Stejně jako u každého jiného vzorce v Excelu začínáme rovnítkem (=).
První částí našeho vzorce je funkce SUMPRODUCT (SOUČETOVÝ PRODUKT). Argumenty musí být uzavřeny v závorkách, takže je otevřeme:
=СУММПРОИЗВ(
=SUMPRODUCT(
Dále přidejte argumenty funkce. SUMPRODUCT (SUMPRODUCT) může mít více argumentů, ale obvykle se používají dva. V našem příkladu bude prvním argumentem rozsah buněk. B2:B9A, která obsahuje skóre.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Druhý argument bude rozsah buněk C2:C9, který obsahuje závaží. Tyto argumenty musí být odděleny středníkem (čárkou). Když je vše připraveno, zavřete závorky:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Nyní přidáme druhou část našeho vzorce, která rozdělí výsledek vypočítaný funkcí SUMPRODUCT (SUMPRODUCT) součtem vah. Proč je to důležité, probereme později.
Pro provedení operace dělení pokračujeme v již zadaném vzorci se symbolem / (přímé lomítko) a poté napište funkci SOUČET (SOUČET):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Pro funkci SOUČET (SUM) uvedeme pouze jeden argument – rozsah buněk C2:C9. Po zadání argumentu nezapomeňte uzavřít závorky:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Připraveno! Po stisknutí klávesy vstoupit, Excel vypočítá vážený průměr. V našem příkladu bude konečný výsledek 83,6.
Jak to funguje
Pojďme si rozebrat jednotlivé části vzorce, počínaje funkcí SUMPRODUCT (SUMPRODUCT), abyste pochopili, jak to funguje. Funkce SUMPRODUCT (SUMPRODUCT) vypočítá součin skóre každé položky a její váhu a poté sečte všechny výsledné produkty. Jinými slovy, funkce najde součet součinů, odtud název. Tak pro Úkoly 1 vynásobte 85 5 a pro Zkouška vynásobte 83 25.
Pokud vás zajímá, proč potřebujeme násobit hodnoty v první části, představte si, že čím větší je váha úkolu, tím vícekrát za něj musíme zvažovat známku. Například, Úkol 2 počítáno 5x a Závěrečná zkouška – 45krát. Proto Závěrečná zkouška má větší vliv na výslednou známku.
Pro srovnání, při výpočtu obvyklého aritmetického průměru se každá hodnota bere v úvahu pouze jednou, to znamená, že všechny hodnoty mají stejnou váhu.
Kdybyste se mohli podívat pod pokličku funkce SUMPRODUCT (SUMPRODUCT), viděli jsme, že ve skutečnosti věří tomuto:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Naštěstí nemusíme psát tak dlouhý vzorec, protože SUMPRODUCT (SUMPRODUCT) to vše dělá automaticky.
Funkce sama o sobě SUMPRODUCT (SUMPRODUCT) nám vrátí obrovské číslo − 10450. V tomto okamžiku přichází na řadu druhá část vzorce: /SUM(C2:C9) or /SUM(C2:C9), který vrátí výsledek do normálního rozsahu skóre a poskytne odpověď 83,6.
Druhá část vzorce je velmi důležitá, protože umožňuje automaticky opravovat výpočty. Pamatujete si, že součet váhy nemusí být 100 %? To vše díky druhé části vzorce. Pokud například zvýšíme jednu nebo více hodnot hmotnosti, druhá část vzorce se jednoduše vydělí větší hodnotou, což opět povede ke správné odpovědi. Nebo můžeme váhy výrazně zmenšit, například zadáním hodnot jako 0,5, 2,5, 3 or 4,5a vzorec bude stále fungovat správně. Je to skvělé, že?