Microsoft Excel (často označovaný ako Excel) je výkonný tabuľkový softvér určený na ukladanie veľkých súborov údajov. Používateľom tiež umožňuje organizovať a analyzovať údaje a dokonca vykonávať základné až zložité výpočty so zaznamenanými údajmi. Medzi základné úlohy analýzy údajov programu Excel patrí najmä výpočet alebo nájdenie priemeru (alebo aritmetického priemeru), režimu a mediánu.
V tomto návode diskutujeme o rôznych postupných metódach alebo riešeniach, ako vypočítať priemer v Exceli. Pred diskusiou o metódach najprv stručne pochopme pojem priemer a jeho výhody.
Čo je to priemer v Exceli?
V Exceli nie je aritmetický priemer nič iné ako priemerné číslo, ktoré sa zvyčajne vypočíta sčítaním všetkých čísel daného súboru údajov a následným vydelením súčtu celkovým počtom hodnôt (alebo počtom) daných položiek.
Hoci je ľahké vypočítať aritmetický priemer pre malé súbory údajov v reálnom živote, je ťažké pracovať s veľkými súbormi údajov. Tu sa hodí výkonný tabuľkový procesor ako Excel. Dokáže ľahko vypočítať priemer stoviek až tisícok čísel. Okrem toho je menej náchylný na chyby.
Pri analýze údajov existuje mnoho základných použití priemeru, ako napríklad:
- Na porovnanie historických údajov
- Na zvýraznenie KPI (Key Performance Indicators) a úvodzoviek
- Ak chcete nájsť stred pre dané dátové body
- Stanoviť projektový manažment a obchodné plány
Základný scenár, kedy možno potrebujeme vypočítať priemer v Exceli, je, keď potrebujeme zistiť priemerný predaj položky za celý mesiac, štvrťrok, rok atď.
Ako vypočítať priemer v Exceli?
Bohužiaľ neexistuje žiadna jednoznačná funkcia MEAN na výpočet alebo určenie aritmetického priemeru údajov. Existuje však niekoľko účinných spôsobov. Vo všeobecnosti môžeme zvyčajne použiť matematický koncept sčítania hodnôt a následného delenia ich súčtu počtom hodnôt v bunkách Excelu. Okrem toho existujú funkcie ako AVERAGE, AVERAGEA, AVERAGEIF a AVERAGEIFS, ktoré nám tiež pomáhajú vypočítať aritmetický priemer v rôznych prípadoch použitia.
Ak chcete získať lepšiu predstavu o tom, ako tieto koncepty fungujú pri výpočte aritmetického priemeru, poďme diskutovať o každom z nich jednotlivo na príklade:
Výpočet priemeru v Exceli: Všeobecná metóda
Môžeme použiť funkcie SUM a COUNT v hárku programu Excel, aby sme použili prístup sčítania hodnôt a ich delenia počtom zahrnutých hodnôt.
Predpokladajme napríklad, že sme zadali čísla od 1 do 5 a potrebujeme vypočítať aritmetický priemer. Matematicky najprv všetky tieto čísla spočítame, t.j. (1+2+3+4+5). Následne ich SUM vydelíme 5, pretože je to celkový počet daných čísel.
Celý vzorec na výpočet priemeru sa teda stáva týmto:
(1+2+3+4+5)/ 5
Tým sa vráti aritmetický priemer 3.
V Exceli môžeme použiť vyššie uvedený matematický vzorec tak, že ho skombinujeme s funkciami SUM a COUNT nasledujúcim spôsobom:
=SUM(1;2;3;4,5)/COUNT(1;2;3;4;5)
Tu funkcia SUM sčítava dané hodnoty, zatiaľ čo funkcia COUNT určí počet daných hodnôt.
Ak teraz zaznamenáme týchto päť čísel v rozsahu A1:A5, nasledujúci vzorec v bunke A6 vypočíta aritmetický priemer:
=SUM(A1:A5)/COUNT(A1:A5)
Výsledok (alebo priemer) získaný na obrázku vyššie je rovnaký ako ten, ktorý sme vypočítali z matematického vzorca, t.j. 3.
Výpočet priemeru v Exceli: Funkcia AVERAGE
Keďže priemer je priemer, funkcia AVERAGE v Exceli je najefektívnejším spôsobom výpočtu aritmetického priemeru. Je to jedna z najčastejšie používaných štatistických funkcií na výpočet priemeru pre určitý rozsah, ak neexistujú žiadne špeciálne podmienky alebo obmedzenia. Je to vstavaná funkcia Excel.
Syntax
Syntax funkcie AVERAGE v Exceli je nasledovná:
=AVERAGE(číslo1, [číslo2], ...)
Číslo1 je povinný argument, čo znamená, že jeden argument je povinný vo funkcii AVERAGE. Môžeme však dodať viacero argumentov v rôznych formách, ako sú čísla, bunky, rozsahy, polia, konštanty atď. Pri výpočte aritmetického priemeru by sme mali zadať aspoň dva argumenty vo funkcii AVERAGE.
Kroky na výpočet priemeru pomocou funkcie AVERAGE
Poďme teraz pochopiť fungovanie funkcie AVERAGE a vypočítajme aritmetický priemer pre skupinu čísel v hárku programu Excel pomocou vzorových údajov.
Predpokladajme, že uvádzame niektoré druhy ovocia v hárku programu Excel s príslušnými cenami. Názvy ovocia sú zaznamenané v stĺpci A (A2:A9) a ich ceny sú v nasledujúcom stĺpci B (B2:B9). Potrebujeme nájsť alebo vypočítať priemernú cenu (alebo aritmetický priemer cien) v bunke B10.
Na výpočet aritmetického priemeru v našom príklade musíme vykonať nasledujúce kroky:
- Najprv musíme vybrať alebo zvýrazniť bunku, aby sme zaznamenali výsledný aritmetický priemer, t. j. bunku B10.
- Ďalej musíme vložiť názov funkcie začínajúci znakom rovnosti a poskytnúť potrebné argumenty vo funkcii. V našom prípade napíšeme znamienko rovnosti (=) a funkciu AVERAGE. Funkciu si môžeme vybrať aj zo zoznamu funkcií navrhnutých Excelom.
- Po zadaní názvu funkcie musíme zadať počiatočnú zátvorku a dodať náš efektívny rozsah súborov údajov. Keďže máme údaje v susedných bunkách alebo rozsahu (od B2 do B9), môžeme celý rozsah vo funkcii odovzdať nasledujúcim spôsobom:
- Nakoniec musíme zadať zátvorku a stlačiť tlačidlo Zadajte na klávesnici získate efektívny výsledok alebo aritmetický priemer pre daný súbor údajov. Obrázok nižšie zobrazuje aritmetický priemer cien v bunkách od B2 do B9:
Na obrázku vyššie sme použili susedné bunky a dodali celý rozsah súborov údajov naraz. Môžeme však vypočítať aj priemer pre nesusediace bunky v našom hárku Excel tak, že bunky dodáme jednotlivo.
Predpokladajme, že potrebujeme vypočítať iba aritmetický priemer cien iba troch druhov ovocia: Jablko, mango a hrozno. V takom prípade môžeme prejsť každú bunku samostatne vo funkcii nasledujúcim spôsobom:
=AVERAGE(B2;B3;B6)
Tu sú B2, B3 a B6 jednotlivé bunky s cenami jablka, manga a hrozna.
Hoci funkciu AVERAGE možno použiť s prázdnymi bunkami, textami, nulami, logickými hodnotami a dokonca aj zmiešanými argumentmi, počas výpočtu ignoruje prázdne bunky, text a logické hodnoty. Bunky s nulovou hodnotou sú však zahrnuté vo funkcii AVERAGE.
Výpočet priemeru v Exceli: Funkcia AVERAGEA
Funkcia AVERAGEA je mierne pokročilá verzia funkcie AVERAGE. Funkcia AVERAGEA nám tiež pomáha vypočítať aritmetický priemer pre dané súbory údajov a funguje vo všetkých verziách Excelu. Jednou z výhod funkcie AVERAGEA je, že môže zahŕňať textové hodnoty a booleovské dátové typy pri výpočte priemeru v Exceli. Textové hodnoty sú vyhodnotené ako nuly, zatiaľ čo booleovské hodnoty TRUE a FALSE sú vyhodnotené ako 1 a 0, v tomto poradí.
Syntax
Syntax funkcie AVERAGEA Excelu je nasledovná:
=AVERAGEA(hodnota1; [hodnota2]; ...)
Rovnako ako funkcia AVERAGE, aj vo funkcii AVERAGEA sa vyžaduje prvý argument. Podľa potreby však môžeme použiť viacero argumentov.
Syntax funkcií AVERAGE a AVERAGEA je takmer podobná; ich pracovný koncept je však mierne odlišný. Funkcia AVERAGE dokáže vypočítať iba aritmetický priemer pre číselné hodnoty, zatiaľ čo funkcia AVERAGEA dokáže vypočítať to isté pre akýkoľvek typ údajov vrátane textu a boolovských hodnôt.
Kroky na výpočet priemeru pomocou funkcie AVERAGEA
Zopakujme si predchádzajúce príklady množín údajov, kde máme názvy ovocia v stĺpci A (A2:A9) a ich ceny v stĺpci B (B2:B9). Tentoraz však nemáme cenu hrozna (v zodpovedajúcej bunke B6 je uvedená ako „Nie je k dispozícii“), ako je uvedené nižšie:
Môžeme vykonať nasledujúce kroky a vypočítať strednú hodnotu pomocou funkcie AVERAGEA:
- Podobne ako pri predchádzajúcej metóde najprv vyberieme výslednú bunku B10 na zaznamenanie priemeru. Ďalej napíšeme znamienko rovnosti (=), zadáme alebo vyberieme našu funkciu AVERAGEA a celý rozsah údajov prenesieme do funkcie uzavretej v zátvorkách.
- Nakoniec musíme stlačiť Zadajte kľúčom k získaniu efektívneho výstupu (alebo priemeru).
Vyššie uvedený obrázok vracia priemerný výstup 57,125 s funkciou AVERAGEA.
Ak v tomto príklade použijeme funkciu AVERAGE a vypočítame aritmetický priemer, vidíme obrovský rozdiel medzi výsledkami získanými oboma funkciami. Priemerný výstup 65,285 sa zobrazí s funkciou AVERAGE, ako je uvedené nižšie:
Rozdiely v aritmetickom priemere v tomto príklade dávajú zmysel, pretože číslo delenia je pre obe tieto funkcie odlišné. Počet čísel pre funkciu AVERAGEA je 8, pretože tiež vyhodnocuje TEXT ako platný údaj. Ale pre funkciu AVERAGE je počet delení 7, pretože do výpočtu nezahŕňa údaje TEXT.
Ak množina údajov obsahuje namiesto údajov TEXT nulu (0), rovnaký výsledok (alebo aritmetický priemer) sa zobrazí pre obe funkcie (AVERAGE a AVERAGEA), pretože obe obsahujú nulu ako platné údaje vo výpočte.
Výpočet priemeru v Exceli: Funkcia AVERAGEIF
Funkcia AVERAGEIF je relevantnou funkciou funkcie AVERAGE a pomáha nám vypočítať aritmetický priemer s danou podmienkou alebo kritériom. Pomocou funkcie AVERAGEIF môžeme vypočítať priemer len pre určité čísla, ktoré spĺňajú zadané kritériá. Excel najskôr vyhodnotí špecifické kritériá, nájde efektívne bunky podľa týchto kritérií a potom podľa toho vypočíta priemer iba pre umiestnené bunky.
Syntax
Syntax funkcie AVERAGEIF programu Excel je nasledovná:
=AVERAGEAIF(rozsah; kritériá; [priemerný_rozsah])
Rozsah a kritériá sú povinné argumenty, zatiaľ čo priemerný_rozsah je voliteľný.
- Rozsah obsahuje bunky, ktoré sa majú testovať podľa daných kritérií.
- Kritériom sa rozumie argument, v ktorom môžeme špecifikovať požadovanú podmienku, ktorá sa má dodržiavať. Môže obsahovať text, číselné hodnoty, odkaz na bunky, rozsah a logický výraz.
- Priemerný_rozsah je argument, v ktorom uvádzame bunky (alebo rozsah), pre ktoré chceme vypočítať priemer. Ak tento argument nie je zadaný, Excel použije rozsah automaticky.
Kroky na výpočet priemeru pomocou funkcie AVERAGEIF
Predpokladajme, že v tom istom príklade uvádzame niektoré druhy ovocia viackrát s rôznymi cenami. Tentoraz musíme vypočítať iba priemer pre akékoľvek konkrétne opakované ovocie (t. j. jablko) v bunke B11. Takže použijeme funkciu AVERAGEIF a použijeme podmienku pri výpočte priemeru. Na výpočet priemernej ceny pre Apple musíme vykonať nasledujúce kroky:
- Najprv vyberieme výslednú bunku (B11) a vložíme vzorec AVERAGEIF nasledujúcim spôsobom:
=AVERAGEIF(A2:B9; 'Apple'; B2:B9)
Tu A2:B9 predstavuje efektívny rozsah, Apple predstavuje naše požadované kritériá/podmienku a B2:B9 predstavuje priemerný_rozsah.
Tu sme manuálne dodali voliteľný average_range, aby sme zahrnuli iba bunky s cenami. Ak v našom príklade neprekročíme priemerný_rozsah, funkcia použije primárny rozsah (A2:B9) s textovými údajmi a spôsobí #DIV! chyba.
Poznámka: Je dôležité poznamenať, že funkcia AVERAGEIF ignoruje prázdne bunky vo svojom argumente priemerný_rozsah. Okrem toho, ak je niektorá bunka špecifikovaná v kritériách prázdna, funkcia ju považuje za bunku s nulovou hodnotou.
Výpočet priemeru v Exceli: Funkcia AVERAGEIFS
Funkcia AVERAGEIFS je rozšírená alebo pokročilá verzia funkcie AVERAGEIF. Funkcia AVERAGEIF nám umožňuje aplikovať naraz iba jednu podmienku. S funkciou AVERAGEIFS však môžeme použiť viacero kritérií, ktoré sa majú testovať súčasne. Okrem toho funkcia AVERAGEIFS nám umožňuje používať bunky s boolovskými hodnotami TRUE a FALSE, čo nám pomáha jednoducho testovať viacero kritérií. To tiež nie je možné s funkciou AVERAGEIF.
Syntax
Syntax funkcie AVERAGEIFS Excelu je nasledovná:
=AVERAGEAIFS(priemerný_rozsah, kritériá1_rozsah1, kritériá1, [rozsah_kritérií2, kritériá2], ...)
Tu sú povinné argumenty priemerný_rozsah, kritériá1_rozsah1 a kritériá1. Môžeme však poskytnúť voliteľné argumenty založené na našich efektívnych súboroch údajov. Okrem toho, kritérium1 sa vzťahuje na stav, ktorý sa má testovať v rozsahu1, kritérium2 sa vzťahuje na stav, ktorý sa má testovať na rozsahu2 a ďalšie.
Kroky na výpočet priemeru pomocou funkcie AVERAGEIFS
Uvažujme tie isté príklady údajov, aké sme použili v predchádzajúcej metóde. V predchádzajúcej metóde sme použili funkciu AVERAGEIF a vypočítali sme Priemer pre ceny opakovaného ovocia (Jablko). V tom prípade sme mali jedinú podmienku.
Teraz predpokladajme, že musíme vypočítať priemer cien Apple, ale zahrnúť iba tie bunky, kde sú ceny vyššie ako 40.
V tomto prípade máme dve rôzne kritériá, takže funkciu AVERAGEIF nemožno použiť.
Kritériá 1: V rozsahu A2:A9 je počiatočným kritériom kontrola „Apple“, ktorá je splnená tromi bunkami (alebo riadkami)
r v c programovaní
Kritériá 2: V rozsahu B2:B9 je ďalším kritériom kontrola cien nad 40 (>40), ktoré spĺňajú dve z troch buniek (alebo riadkov) s ovocným jablkom.
Ak teda matematicky vypočítame priemer, použijeme nasledujúci vzorec:
(95+68)/2
Toto vráti strednú hodnotu 81,5. Skontrolujeme, či dostaneme rovnaké výsledky pomocou funkcie AVERAGEIFS v Exceli.
Keď potrebujeme otestovať podmienky v Exceli, zvyčajne si na pomoc vezmeme logický operátor OR. Operátor OR vráti hodnotu TRUE a FALSE podľa toho, či je daná podmienka splnená. Predpokladajme napríklad, že musíme skontrolovať, či bunka A2 obsahuje Apple. V tomto prípade môžeme použiť operátor OR takto:
=ALEBO(A2='Apple')
Vyššie uvedenú podmienku vložíme do nášho vzorového listu, aby sme otestovali alebo lokalizovali bunky s ovocím Apple. Takže vytvoríme nový stĺpec (C) a napíšeme konkrétnu podmienku do bunky C2. Potom skopírujeme rovnakú podmienku do zostávajúcich buniek stĺpca C. Vyzerá to takto:
Vyššie uvedený obrázok ukazuje, že operátor OR vráti hodnotu TRUE pre bunky zodpovedajúce ovociu Apple, zatiaľ čo pre ostatné vráti hodnotu FALSE. Na základe tohto výsledku teraz aplikujeme funkciu AVERAGEIFS a vložíme viacero podmienok pre bunky s hodnotou TRUE nasledujúcim spôsobom:
- Rovnako ako iné metódy, opäť najprv vyberieme výslednú bunku (t. j. B12).
- Ďalej zadáme vzorec AVERAGEIFS nasledujúcim spôsobom:
=AVERAGEIFS(B2:B9;C2:C9;'TRUE',B2:B9;'>40')
S vyššie uvedeným AVERAGEIFS vzorcom sme zahrnuli iba ovocie „Jablko“ a ceny nad 40. - Nakoniec musíme stlačiť Zadajte kľúč na získanie výsledku vzorca. Pre náš súbor údajov vzorec vrátil aritmetický priemer 81,5, rovnaký ako náš matematicky vypočítaný výsledok.
Špeciálne prípady
Aj keď pri výpočte aritmetického priemeru v programe Excel môžeme mať rôzne scenáre, tu sú uvedené dva najbežnejšie scenáre. V týchto prípadoch musíme byť pri výpočte aritmetického priemeru opatrní.
Výpočet priemeru vrátane núl
Keď potrebujeme vypočítať priemer s nulami, môžeme normálne použiť funkciu AVERAGE a dodať efektívne bunky vrátane buniek s nulovou hodnotou. Funkcia AVERAGE zahŕňa pri výpočte nuly ako platné čísla.
V nasledujúcom vzorovom súbore údajov použijeme funkciu AVERAGE na výpočet priemeru pre dve rôzne známky študentov:
Vyššie uvedený obrázok ukazuje rôzne stredné hodnoty pre oboch študentov. Ak sa pozrieme na známky, súčet známok je u oboch žiakov rovnaký. Druhý študent má z jedného predmetu nulu (bunka s nulovou hodnotou), pričom prvý študent v jednom predmete chýba (bunka s hodnotou textu).
Keďže funkcia AVERAGE ignoruje textové hodnoty, ale obsahuje nulové hodnoty, počet delení sa u oboch študentov líši. Pre prvého študenta je počet delení 4, zatiaľ čo pre iného študenta je to 5. Existuje teda rozdiel v priemerných výsledkoch. V prípade potreby však môžeme explicitne vylúčiť aj nulové hodnoty.
Výpočet priemeru bez núl
Keď potrebujeme vypočítať priemer bez núl, musíme využiť funkciu AVERAGEIF v Exceli. V tejto funkcii môžeme použiť špecifickú podmienku a prikázať Excelu, aby počas výpočtu nezahŕňal bunky s nulovou hodnotou.
Nižšie uvedený vzorec AVERAGEIF možno použiť na vylúčenie nulových hodnôt pri výpočte priemeru v Exceli:
=AVERAGEIF(bunka alebo rozsah,'>0')
Ak pre naše vzorové údaje vložíme vyššie uvedený vzorec, pre oboch študentov sa objavia rovnaké výsledky strednej hodnoty.
Vyššie uvedený vzorec AVERAGEIF ignoruje text a bunky s nulovou hodnotou. Počty delení sú teda rovnaké (t. j. 4) a rovnaký je aj aritmetický priemer známok oboch študentov.
Dôležité body na zapamätanie
- Je dôležité poznamenať, že každá funkcia v Exceli vrátane AVERAGE, AVERAGEA, AVERAGEIF a AVERAGEIFS môže akceptovať maximálne 255 argumentov.
- Ručné zadávanie názvu funkcie do bunky Excelu sa neodporúča. Výber požadovanej funkcie zo zoznamu návrhov je lepšie, aby ste sa vyhli nesprávne napísaným názvom funkcií a odstránili chyby vo výsledkoch.