TechCodeview

Microsoft Excel (často označovaný jako Excel) je výkonný tabulkový procesor určený k ukládání velkých souborů dat. Také umožňuje uživatelům organizovat a analyzovat data a dokonce provádět základní až složité výpočty se zaznamenanými daty. Mezi základní úkoly analýzy dat aplikace Excel patří především výpočet nebo nalezení střední hodnoty (nebo aritmetického průměru), režimu a mediánu.

V tomto tutoriálu diskutujeme o různých postupných metodách nebo řešeních, jak vypočítat průměr v Excelu. Než budeme diskutovat o metodách, nejprve stručně pochopme koncept střední hodnoty a její výhody.

Jaký je průměr v Excelu?

V Excelu není aritmetický průměr nic jiného než průměrné číslo, které se obvykle vypočítá sečtením všech čísel daného souboru dat a následným vydělením součtu celkovým počtem hodnot (nebo počtem) daných položek.

Ačkoli je snadné vypočítat aritmetický průměr pro malé soubory dat v reálném životě, je obtížné pracovat s velkými soubory dat. Zde se hodí výkonný tabulkový procesor, jako je Excel. Dokáže snadno vypočítat průměr stovek až tisíců čísel. Navíc je méně náchylný k chybám.

Při analýze dat existuje mnoho zásadních použití střední hodnoty, jako například:

java skener

• Pro srovnání historických dat
• Pro zvýraznění KPI (Key Performance Indicators) a uvozovek
• Chcete-li najít střed pro dané datové body
• Stanovit projektové řízení a obchodní plány

Základní scénář, kdy můžeme potřebovat vypočítat průměr v Excelu, je, když potřebujeme zjistit průměrné prodeje položky za celý měsíc, čtvrtletí, rok atd.

Jak vypočítat průměr v Excelu?

Bohužel neexistuje žádná jednoznačná funkce MEAN pro výpočet nebo určení aritmetického průměru pro data. Existuje však několik účinných způsobů. Obecně lze obvykle použít matematický koncept sčítání hodnot a následného dělení jejich součtu počtem hodnot v buňkách Excelu. Kromě toho existují funkce jako AVERAGE, AVERAGEA, AVERAGEIF a AVERAGEIFS, které nám také pomáhají vypočítat aritmetický průměr v různých případech použití.

Chcete-li získat lepší představu o tom, jak tyto koncepty fungují pro výpočet aritmetického průměru, proberme každý jednotlivě s příkladem:

Výpočet průměru v Excelu: Obecná metoda

Můžeme použít funkce SUM a POČET v listu Excelu, abychom použili přístup sčítání hodnot a jejich dělení počtem zahrnutých hodnot.

Předpokládejme například, že jsme zadali čísla od 1 do 5 a potřebujeme vypočítat aritmetický průměr. Matematicky nejprve všechna tato čísla sečteme, tedy (1+2+3+4+5). Poté jejich SOUČET vydělíme 5, protože je to celkový počet daných čísel.

Celý vzorec pro výpočet průměru tedy bude tento:

(1+2+3+4+5)/ 5

Tím se vrátí aritmetický průměr 3.

V Excelu můžeme použít výše uvedený matematický vzorec tak, že jej zkombinujeme s funkcemi SUM a COUNT následujícím způsobem:

=SUM(1;2;3;4,5)/POČET(1;2;3;4;5)

Zde funkce SUMA dané hodnoty sečte, zatímco funkce COUNT určí počet daných hodnot.

Nyní, když zaznamenáme těchto pět čísel v rozsahu A1:A5, níže uvedený vzorec v buňce A6 vypočítá aritmetický průměr:

=SUM(A1:A5)/POČET(A1:A5)

Výsledek (nebo průměr) získaný na obrázku výše je stejný jako ten, který jsme vypočítali z matematického vzorce, tj. 3.

Výpočet průměru v Excelu: Funkce AVERAGE

Protože průměr je průměr, funkce AVERAGE v Excelu je nejúčinnějším způsobem výpočtu aritmetického průměru. Je to jedna z nejčastěji používaných statistických funkcí pro výpočet průměru pro určitý rozsah, pokud neexistují žádné zvláštní podmínky nebo omezení. Je to vestavěná funkce Excelu.

Syntax

Syntaxe funkce AVERAGE aplikace Excel je následující:

=AVERAGE(číslo1; [číslo2]; ...)

Číslo1 je povinný argument, což znamená, že jeden argument je povinný ve funkci AVERAGE. Můžeme však dodat více argumentů v různých formách, jako jsou čísla, buňky, rozsahy, pole, konstanty atd. Při výpočtu aritmetického průměru bychom měli zadat alespoň dva argumenty ve funkci AVERAGE.

Kroky pro výpočet průměru pomocí funkce AVERAGE

Pojďme nyní pochopit fungování funkce PRŮMĚR a vypočítat aritmetický průměr pro skupinu čísel v excelovém listu pomocí ukázkových dat.

Předpokládejme, že uvedeme některé druhy ovoce v listu aplikace Excel s odpovídajícími cenami. Názvy ovoce jsou uvedeny ve sloupci A (A2:A9) a jejich ceny jsou uvedeny ve sloupci B (B2:B9). Potřebujeme najít nebo vypočítat průměrnou cenu (nebo aritmetický průměr cen) v buňce B10.

K výpočtu aritmetického průměru v našem vzorovém listu musíme provést následující kroky:

• Nejprve musíme vybrat nebo zvýraznit buňku, abychom zaznamenali výsledný aritmetický průměr, tj. buňku B10.
  
• Dále musíme vložit název funkce začínající rovnítkem a poskytnout potřebné argumenty ve funkci. V našem případě zadáme rovnítko (=) a funkci AVERAGE. Funkci můžeme také vybrat ze seznamu funkcí navržených aplikací Excel.
  
• Po zadání názvu funkce musíme zadat počáteční závorku a dodat náš efektivní rozsah datových sad. Protože máme data v sousedních buňkách nebo rozsahu (od B2 do B9), můžeme ve funkci předat celý rozsah následujícím způsobem:
  
• Nakonec musíme zadat uzavírací závorku a stisknout Vstupte klávesou na klávesnici získáte efektivní výsledek nebo aritmetický průměr pro danou sadu dat. Níže uvedený obrázek ukazuje aritmetický průměr cen v buňkách od B2 do B9:
  
  Na obrázku výše jsme použili sousední buňky a dodali celou řadu datových sad najednou. Můžeme však také vypočítat průměr pro nesousedící buňky v našem listu Excel tak, že buňky dodáme jednotlivě.
  Předpokládejme, že potřebujeme vypočítat aritmetický průměr cen pouze pro tři druhy ovoce: Jablko, mango a hrozny. V takovém případě můžeme předat každou buňku samostatně ve funkci následujícím způsobem:
  =AVERAGE(B2;B3;B6)
  
  Zde jsou B2, B3 a B6 jednotlivé buňky s cenami Apple, Mango a Grapes.

Ačkoli lze funkci PRŮMĚR použít s prázdnými buňkami, texty, nulami, logickými hodnotami a dokonce i smíšenými argumenty, během výpočtu ignoruje prázdné buňky, text a logické hodnoty. Buňky s nulovou hodnotou jsou však zahrnuty ve funkci AVERAGE.

Výpočet průměru v Excelu: Funkce AVERAGEA

Funkce AVERAGEA je mírně pokročilá verze funkce AVERAGE. Funkce AVERAGEA nám také pomáhá vypočítat aritmetický průměr pro dané datové sady a funguje ve všech verzích Excelu. Jednou z výhod funkce AVERAGEA je, že může zahrnovat textové hodnoty a booleovské datové typy při výpočtu průměru v Excelu. Textové hodnoty jsou vyhodnoceny jako nuly, zatímco booleovské hodnoty TRUE a FALSE jsou vyhodnoceny jako 1 a 0, v tomto pořadí.

Syntax

prolomit mapu

Syntaxe funkce AVERAGEA aplikace Excel je následující:

=AVERAGEA(hodnota1; [hodnota2]; ...)

Stejně jako funkce AVERAGE je vyžadován první argument funkce AVERAGEA. Podle potřeby však můžeme použít více argumentů.

Syntaxe funkcí AVERAGE a AVERAGEA jsou téměř podobné; jejich pracovní koncept je však mírně odlišný. Funkce AVERAGE dokáže vypočítat aritmetický průměr pouze pro číselné hodnoty, zatímco funkce AVERAGEA může vypočítat totéž pro jakýkoli typ dat, včetně textu a booleovských hodnot.

Kroky pro výpočet průměru pomocí funkce AVERAGEA

Vezměme si znovu předchozí vzorové datové sady, kde máme názvy ovoce ve sloupci A (A2:A9) a jejich ceny ve sloupci B (B2:B9). Tentokrát však nemáme cenu hroznů (v odpovídající buňce B6 je uvedena jako „Nedostupné“, jak je uvedeno níže:

Můžeme provést níže uvedené kroky a vypočítat střední hodnotu pomocí funkce AVERAGEA:

• Stejně jako předchozí metoda nejprve vybereme výslednou buňku B10, abychom zaznamenali průměr. Dále napíšeme rovnítko (=), zadáme nebo vybereme naši funkci AVERAGEA a předáme celý datový rozsah funkci uzavřené v závorce.
  
• Nakonec musíme stisknout tlačítko Vstupte klíč k získání efektivního výstupu (nebo střední hodnoty).
  
  Výše uvedený obrázek vrací střední výstup 57,125 s funkcí AVERAGEA.
  Pokud v tomto příkladu použijeme funkci PRŮMĚR a vypočítáme aritmetický průměr, vidíme obrovský rozdíl mezi výsledky získanými oběma funkcemi. Střední výstup 65,285 se objeví s funkcí AVERAGE, jak je ukázáno níže:
  
  Rozdíly v aritmetickém průměru v tomto příkladu dávají smysl, protože počet dělení je u obou těchto funkcí odlišný. Počet čísel pro funkci AVERAGEA je 8, protože také vyhodnocuje TEXT jako platná data. Ale pro funkci AVERAGE je počet dělení 7, protože do výpočtu nezahrnuje data TEXT.
  Pokud soubor dat obsahuje nulu (0), místo dat TEXT se zobrazí stejný výsledek (nebo aritmetický průměr) pro obě funkce (PRŮMĚR a PRŮMĚR), protože obě zahrnují nulu jako platná data ve výpočtu.

Výpočet průměru v Excelu: Funkce AVERAGEIF

Funkce AVERAGEIF je relevantní funkcí funkce AVERAGE a pomáhá nám vypočítat aritmetický průměr s danou podmínkou nebo kritériem. Pomocí funkce AVERAGEIF můžeme vypočítat průměr pouze pro určitá čísla, která splňují zadaná kritéria. Excel nejprve vyhodnotí konkrétní kritéria, vyhledá efektivní buňky podle těchto kritérií a poté podle toho vypočítá průměr pouze pro nalezené buňky.

Syntax

Syntaxe funkce AVERAGEIF aplikace Excel je následující:

=AVERAGEAIF(rozsah; kritéria; [průměrný_rozsah])

Rozsah a kritéria jsou zde povinné argumenty, zatímco průměrný_rozsah je volitelný.

• Rozsah obsahuje buňky, které mají být testovány podle daných kritérií.
• Kritériem se rozumí argument, kde můžeme specifikovat požadovanou podmínku, která se má dodržovat. Může obsahovat text, číselné hodnoty, odkaz na buňky, rozsah a logický výraz.
• Průměrný_rozsah je argument, kde zmiňujeme buňky (nebo rozsah), pro které chceme vypočítat průměr. Pokud tento argument není zadán, Excel použije rozsah automaticky.

Kroky pro výpočet průměru pomocí funkce AVERAGEIF

Předpokládejme, že ve stejném příkladu uvádíme některé druhy ovoce vícekrát s různými cenami. Tentokrát potřebujeme pouze vypočítat průměr pro jakékoli konkrétní opakované ovoce (tj. jablko) v buňce B11. Použijeme tedy funkci AVERAGEIF a podmínku aplikujeme při výpočtu průměru. Pro výpočet průměrné ceny pro Apple musíme provést následující kroky:

• Nejprve vybereme výslednou buňku (B11) a vložíme vzorec AVERAGEIF následujícím způsobem:
  =AVERAGEIF(A2:B9; 'Apple'; B2:B9)
  Zde A2:B9 představuje efektivní rozsah, Apple představuje naše požadovaná kritéria/podmínku a B2:B9 představuje průměrný_rozsah.
  
  Zde jsme ručně dodali volitelný průměrný_rozsah, abychom zahrnuli pouze buňky s cenami. V našem příkladu, pokud nepředáme průměrný_rozsah, funkce použije primární rozsah (A2:B9) s textovými daty a způsobí, že #DIV! chyba.

Poznámka: Je důležité si uvědomit, že funkce AVERAGEIF ignoruje prázdné buňky ve svém argumentu průměrný_rozsah. Pokud je navíc jakákoli buňka zadaná v kritériích prázdná, funkce ji považuje za buňku s nulovou hodnotou.

Výpočet průměru v Excelu: Funkce AVERAGEIFS

Funkce AVERAGEIFS je rozšířenou nebo pokročilou verzí funkce AVERAGEIF. Funkce AVERAGEIF nám umožňuje aplikovat vždy pouze jednu podmínku. S funkcí AVERAGEIFS však můžeme použít více kritérií, která mají být testována současně. Funkce AVERAGEIFS nám navíc umožňuje používat buňky s logickými hodnotami TRUE a FALSE, což nám pomáhá snadno testovat více kritérií. To také není možné s funkcí AVERAGEIF.

Syntax

Syntaxe funkce AVERAGEIFS aplikace Excel je následující:

=AVERAGEAIFS(průměrný_rozsah, kritérium1_rozsah1, kritérium1, [rozsah_kritérií2, kritéria2], ...)

knn algoritmus

Zde jsou povinné argumenty průměrný_rozsah, kritéria1_rozsah1 a kritéria1. Můžeme však dodat volitelné argumenty založené na našich efektivních souborech dat. Kromě toho kritérium1 odkazuje na stav, který má být testován na rozsahu1, kritérium2 odkazuje na stav, který má být testován na rozsahu2 a další.

Kroky pro výpočet průměru pomocí funkce AVERAGEIFS

Uvažujme stejná příkladová data, která jsme použili v předchozí metodě. V předchozí metodě jsme použili funkci AVERAGEIF a vypočítali střední hodnotu pro ceny opakovaného ovoce (Jablko). V tom případě jsme měli jedinou podmínku.

Nyní předpokládejme, že musíme vypočítat průměr pro ceny Apple, ale zahrnout pouze ty buňky, kde jsou ceny vyšší než 40.

V tomto případě máme dvě různá kritéria, takže funkci AVERAGEIF nelze použít.

Kritéria 1: V rozsahu A2:A9 je počátečním kritériem kontrola „Apple“, která je splněna třemi buňkami (nebo řádky)

Kritéria 2: V rozsahu B2:B9 je dalším kritériem kontrola cen nad 40 (>40), které splňují dvě ze tří buněk (nebo řádek) s ovocným jablkem.

Pokud tedy matematicky vypočítáme průměr, použijeme níže uvedený vzorec:

(95+68)/2

Tím se vrátí střední hodnota 81,5. Zkontrolujeme, zda dostaneme stejné výsledky pomocí funkce AVERAGEIFS v Excelu.

Když potřebujeme otestovat podmínky v Excelu, obvykle si na pomoc vezmeme logický operátor OR. Operátor OR vrací TRUE a FALSE podle toho, zda daná podmínka vyhovuje. Předpokládejme například, že potřebujeme zkontrolovat, zda buňka A2 obsahuje Apple. V tomto případě můžeme použít operátor OR takto:

=OR(A2='Apple')

Výše uvedenou podmínku vložíme do našeho vzorového listu, abychom otestovali nebo lokalizovali buňky s ovocem Apple. Vytvoříme tedy nový sloupec (C) a do buňky C2 napíšeme konkrétní podmínku. Poté stejnou podmínku zkopírujeme do zbývajících buněk sloupce C. Vypadá to takto:

xvideoservicethief ubuntu 14.04 ke stažení

Výše uvedený obrázek ukazuje, že operátor OR vrací TRUE pro buňky odpovídající ovoci Apple, zatímco pro ostatní vrací FALSE. Na základě tohoto výsledku nyní aplikujeme funkci AVERAGEIFS a vložíme několik podmínek pro buňky s hodnotou TRUE následujícím způsobem:

• Stejně jako jiné metody opět nejprve vybereme výslednou buňku (tj. B12).
• Dále zadáme vzorec AVERAGEIFS následujícím způsobem:
  =AVERAGEIFS(B2:B9;C2:C9;'PRAVDA',B2:B9;'>40')
  
  U výše uvedeného vzorce AVERAGEIFS jsme zahrnuli pouze ovoce „Jablko“ a ceny nad 40.
• Nakonec musíme stisknout tlačítko Vstupte klíč k získání výsledku vzorce. Pro naši datovou sadu vzorec vrátil aritmetický průměr 81,5, stejně jako náš matematicky vypočítaný výsledek.
  

Speciální případy

Ačkoli můžeme mít různé scénáře při výpočtu aritmetického průměru v Excelu, zde jsou popsány dva nejběžnější. V těchto případech musíme být při výpočtu aritmetického průměru opatrní.

Výpočet střední hodnoty včetně nul

Když potřebujeme vypočítat průměr s nulami, můžeme normálně použít funkci AVERAGE a dodat efektivní buňky, včetně buněk s nulovou hodnotou. Funkce AVERAGE zahrnuje do výpočtu nuly jako platná čísla.

V následujícím vzorovém souboru dat použijeme funkci PRŮMĚR k výpočtu průměru dvou různých známek studentů:

Výše uvedený obrázek ukazuje různé průměrné hodnoty pro oba studenty. Pokud se podíváme na známky, součet známek je u obou žáků stejný. Druhý student má z jednoho předmětu nulu (buňka s nulovou hodnotou), zatímco první student v jednom předmětu chybí (buňka s textovou hodnotou).

Protože funkce AVERAGE ignoruje textové hodnoty, ale obsahuje nulové hodnoty, počet dělení se u obou studentů liší. Pro prvního studenta je počet dělení 4, zatímco pro jiného studenta je to 5. Existuje tedy rozdíl ve středních výsledcích. V případě potřeby však můžeme také explicitně vyloučit nulové hodnoty.

Výpočet průměru bez nul

Když potřebujeme vypočítat střední hodnotu bez nul, musíme využít funkci AVERAGEIF aplikace Excel. V této funkci můžeme použít konkrétní podmínku a dát Excel pokyn, aby do výpočtu nezahrnoval buňky s nulovou hodnotou.

Níže uvedený vzorec AVERAGEIF lze použít k vyloučení nulových hodnot při výpočtu průměru v Excelu:

=AVERAGEIF(buňka nebo rozsah,'>0')

Pokud vložíme výše uvedený vzorec pro naše vzorová data, objeví se u obou studentů stejné výsledky střední hodnoty.

Výše uvedený vzorec AVERAGEIF ignoruje text a buňky s nulovou hodnotou. Počty dělení jsou tedy stejné (tj. 4) a stejný je i aritmetický průměr známek obou studentů.

Důležité body k zapamatování

• Je důležité si uvědomit, že jakákoli funkce v Excelu, včetně AVERAGE, AVERAGEA, AVERAGEIF a AVERAGEIFS, může přijmout maximálně 255 argumentů.
• Ruční zadávání názvu funkce do buňky aplikace Excel se nedoporučuje. Výběr požadované funkce ze seznamu návrhů je lepší, abyste se vyhnuli chybně napsaným názvům funkcí a eliminovali chyby ve výsledcích.