Vážený priemer Excel

Tento článok napísal lektor z Easy Excel, autor obľúbených video kurzov Excelu. Ak aj vy máte záujem učiť sa z pohodlia domova, otvorte si www.easyexcel.sk.

Málokto vie vážený priemer v Exceli používať napriek tomu, že je veľmi dôležitý pre matematické výpočty.

Vážený priemer je jedným z druhov matematických priemerov. Využíva sa pri vyhodnocovaní údajov, ktoré obyčajne nesú rozličnú váhu.

Vážený priemer možno počítať manuálne. Takýto výpočet však zaberie niekoľko minút.

Tu nám na pomoc prichádza tabuľkový editor Excel, ktorý dokáže vypočítať vážený priemer jedným vzorcom. Funkciu ocenia hlavne užívatelia pracujúci s veľkým množstvom hodnôt i prirodzený nepriatelia numerickej matematiky.

Aritmetický a vážený priemer

Aritmetický priemer pozná určite každý z nás. Ide o klasický výpočet, kde súčet hodnôt delíme ich počtom. V Exceli predstavuje aritmetický priemer preddefinovaná funkcia AVERAGE.

Tento jednoduchý druh priemeru však má svoje medzery. Ide hlavne o prípady, kedy potrebujeme zohľadniť viacero atribútov daných hodnôt. Aritmetický priemer totiž pracuje s dvoma hodnotami. S celkovým súčtom a počtom činiteľov.

Vážený priemer zohľadňuje aj iné, pre nás podstatné čísla. Zjednodušene ak sú naše čísla ovplyvnené ďalšou hodnotou. Potrebujeme teda spriemerovať viacero hodnôt. Napríklad majme jednotlivé ceny tovaru a počet predaných kusov. Zaujíma nás priemerný zisk za jeden kus. Z problému je zreteľné, že jednoduchý aritmetický priemer nedokáže pobrať množstvo kusov. Preto tu využijeme priemer vážený.

Manuálny výpočet

Princíp výpočtu váženého priemeru je pomerne priamočiary. Vyžaduje si len elementárnu znalosť matematiky. Možno ho vyrátať aj bez pomoci Excelu.

Manuálny výpočet si vyžaduje niekoľko krokov. V prvom kroku potrebujeme samozrejme zistiť čiastkové hodnoty. V prípade popísanom vyššie ide o celkový zisk z predaja jedného produktu. To pochopiteľne dosiahneme násobením množstva a ceny za kus. Z toho následne vyrátame celkový zisk za dané obdobie. Vypočítaný celkový zisk predelíme počtom všetkých predaných kusov a máme výsledok. Sami určite uznáte, že tento postup je pomerne zdĺhavý, pri vyššom počte dát až nemožný. Excel ho dokáže previesť jedným vzorcom.

Vážený priemer v Exceli

Program Excel neobsahuje preddefinovanú funkciu pre výpočet váženého priemeru. Aj napriek tomu je možné jednoducho vytvoriť vyššie popísanú závislosť. Vytvárame takzvaný vlastný vzorec. Na pomoc si zavoláme dve preddefinované funkcie Excelu, ktoré následne delíme. Prvou z nich je funkcia SUMPRODUCT, druhou jednoduchá SUM.

SUMPRODUCT patri do kategórie matematických funkcií Excelu. Po vyplnení vráti číslo, ktoré predstavuje súčet súčinov označených hodnôt. SUMPRODUCT v podstate zhrnie prvé dva kroky vyššie popísaného logického postupu. V našom prípade ide o prenásobenie ceny produktu počtom kusov a vyrátanie celkového zisku. Funkcia vráti hodnotu výpočtu jedným klikom. Do riadka zadáme oblasti, ktoré chceme násobiť, a oddelíme bodkočiarkou. Násobenie je z matematického hľadiska komutatívnou operáciou. To znamená, že zmena poradia činiteľov neovplyvní výsledok. Preto aj v našom prípade nezáleží na poradí oblastní zadaných do vzorca.

SUM je ďalšou z dvoch funkcií, ktoré budeme k výpočtu váženého priemeru potrebovať. SUMA je absolútne triviálnou funkciou. Vracia súčet hodnôt z vyznačených buniek. Vyššie popísanú funkciu SUMPRODUCT vo vzorci delíme funkciou SUMA. Z logického hľadiska ide vlastne o posledný krok výpočtu. V známom príklade ide o predelenie celkového zisku (výsledok funkcie SUMPRODUCT) počtom predaných kusov (výsledok funkcie SUMA).

Vážený priemer v praxi

Vážený priemer v praxi možno zrozumiteľne ilustrovať príkladmi nižšie.

Príklad 1.

Príklad 1 sme slovne riešili už v predošlom texte. V stĺpci A máme druhy tovaru, v stĺpci B ceny za 1 kus daného tovaru a v stĺpci C počet kusov predaných za dané obdobie. Pod tabuľkou vidíme zvýraznenú prázdnu bunku, do ktorej chceme dostať priemerný zisk za jeden predaný kus.

Výpočet priemernej ceny za kus je veľmi jednoduchý. Vyvoláme funkciu AVERAGE, vyznačíme bunky, ktoré chceme spriemerovať, a po potvrdení nám systém vráti priemernú cenu za kus.

Priemerná cena je v našom prípade 176,8 €. To však, pochopiteľne, nie je hodnotou, ktorú hľadáme. Ide len o priemernú cenu za kus, ktorý hodláme predať. My potrebujeme zistiť, koľko sme za obdobie skutočne zarobili. Nakoľko tu bude potrebné rátať i s počtom predaných kusov, nebude už aritmetický priemer zodpovedajúci.

Vážený priemer nám v tomto prípade vezme v úvahu aj počet predaných kusov. Z obrázka je viditeľné, že do príkazového riadka zadávame vlastnú funkciu SUMPRODUCT (pole1;pole2), ktorá v podstate počíta (360*2)+(123*6)+(98*8)…., a delíme ju funkciou SUMA, čo predstavuje súčet predaných kusov.

Po potvrdení sa v bunke zobrazí výsledok váženého priemeru, čiže priemerný zisk za jeden kus.

Druhý príklad na počítanie váženého priemeru

Druhý príklad ukazuje jedno z najčastejších využití váženého priemeru v praxi. Chceme pomocou Excelu vypočítať priemernú známku za celú triedu. V tabuľke máme jednotlivé známky a počty žiakov, ktorí danú známku získali. Keďže potrebujeme dostať výsledok za celú triedu, budeme musieť vziať v úvahu oba stĺpce. Tu si na pomoc opäť zavoláme vážený priemer.

Oblasti do vzorca vypĺňame rovnako ako v predchádzajúcom príklade. SUMPRODUCT vynásobí pole 1, čiže známky, poľom 2, čiže počtom študentov. Vydelíme funkciou SUM, ktorá v tomto prípade predstavuje celkový počet študentov (stĺpec B).

Priemerná známka za celú triedu je teda 2,1. výsledok sme dostali bez zdĺhavého počítania behom pár sekúnd.