Díky statistice lze v Excelu vytvářet různé reporty, analýzy, zprávy, apod. Není na škodu si běžné statistické funkce, které Microsoft Excel nabízí, osvěžit, abyste nástroje statistiky používali naplno a efektivně.
Bez přemíry matematické teorie se podíváme rovnou, jak statistické funkce v Excelu využít. Souhrnně často tyto funkce řadíme mezi popisnou statistiku, neboť popisují vlastnosti statistického znaku. Paleta statistických funkcí v Excelu je velice široká.
Podíváme se zejména na ty často používané, abyste je vždy uměli efektivně použít. Pro matematickou teorii můžeme odkázat například na PDF soubor z Ústavu matematiky na VUT Brno a rovněž poslouží i nápověda k Excelu. V příkladu máme fiktivní tabulku se dvěma sloupci obsahující data z nějakého měření.
Charakteristiky polohy v Excelu
Mezi základní statistické charakteristiky polohy patří především průměr (aritmetický a geometrický), modus, medián a obecně k-tý percentil (50. percentil je medián). V Excelu využijeme tyto funkce:
- PRŮMĚR pro aritmetický průměr (v angl. AVERAGE)
- GEOMEAN pro geometrický průměr
- variační rozpětí nemá vlastní funkci, spočteme jako rozdíl =MAX(D9:D21)-MIN(D9:D21)
- MIN pro minimum, MAX pro maximum
Poznámka k percentilu. Medián je 50. percentil, tudíž pokud vypočteme =PERCENTIL(D9:D21;0,5), dostaneme stejný výsledek, kde 0,5 je hodnota k, tedy počítáme k-tý percentil. Ve vzorci je z intervalu 0 až 1, tedy např. 0,25 odpovídá 1. kvartilu (25. percentilu).
Charakteristiky variability v Excelu
K charakteristikám variability (proměnlivosti) statistického znaku patří variační rozpětí, rozptyl a směrodatná odchylka, dále pak variační koeficient. Použijeme k těmto výpočtům tyto funkce:
- MEDIAN pro medián (stejný výsledek bychom dostali pro 50. percentil)
- MODE pro modus (novější funkce je MODE.SNGL)
- SM.ODCH pro směrodatnou odchylku (druhá odmocnina z rozptylu)
- VAR pro rozptyl
Koeficient korelace (korelační koeficient)
V případě sledování více znaků nás často zajímá jejich vzájemná závislost, tedy jak jsou na sobě znaky závislé. Tuto míru závislosti určuje korelační koeficient, který lze vyjádřit korelačním koeficientem r z intervalu od -1 do 1. Čím je r blíže k 1, závislost znaků je větší. V Excelu použijeme funkci CORREL, v níž argumenty jsou 2 pole. V tomto příkladu zapíšeme =CORREL(D9:D21;E9:E21).
Funkce COUNTIF a AVERAGEIF
Užitečnou funkcí, která se statistikou souvisí, je COUNTIF. V Excelu najdeme funkci POČET (angl. COUNT), která vrátí počet buněk v oblasti, bez ohledu na jejich obsah. Funkce COUNTIF spojuje tuto funkci s podmínkou. Princip této funkce je ukázán na obrázku výše, kde zjišťujeme počet buněk, v nichž je číslo větší než 20. Obecná syntaxe zápisu je =COUNTIF(pole;”podmínka”). V tomto případě jde o =COUNTIF(D9:D21;”>20″). Podmínku zapisujeme do uvozovek.
Podobně funguje funkce AVERAGEIF, jejíž syntaxe je =AVERAGEIF(pole;”podmínka”;oblast). Výstupem je průměr hodnot z oblasti buněk, které splňují určité kritérium. Může být v praxi výhodné pro eliminaci extrémních hodnot, tedy zjistíme průměr pouze z určitých hodnot. Představme si tyto hodnoty: 2, 4, 5, 10, 12, 13, 7, 5, 8, 8, 10, 4000, 6, 3, 13, 9. Je zřejmé, že hodnota 4000 zde patrně nepatří a mohla vzniknout vlivem chyby. Pokud bychom vzali průměr všech hodnot, dostaneme 257,188. Vyloučíme-li pomocí AVERAGEIF podmínkou “<4000”, extrémní hodnota se nezapočítá a výsledek bude 7,667. V syntaxi lze vynechat třetí parametr oblast a průměr bude spočten v označeném poli (první parametr).
Další poznámky ke statistickým funkcím
V tomto článku jsme se soustředili na jednodušší statistické funkce, jejich používání je základem pro každého, kdo chce z dat mít užitečné přehledy, souhrny, apod. Jejich využití najdeme všude, kde pracujeme se statistickými daty. Zvládněte ovládat tyto statistické funkce, které Vám pomohou udělat z dat přehledné analýzy. Nezapomeňte vždy na správné nastavení datových typů, v tomto případě čísla, procenta, měna apod.
Přesnost výpočtů bude podle nastavení Excelu či přímo dle nastavení desetinných míst. Oddělovač desetinných míst je v české verzi Excelu ve výchozím nastavení čárka, ale může být i tečka, případně dle formátování zdrojových hodnot.
Popisná statistika na tři kliknutí
Velmi pohodlnou možností, jak získat základní popisnou statistiku dat, je využití doplňku Analytické nástroje (Analýza dat). Je třeba tento doplněk nejprve do Excelu načíst, návod najdete zde pro všechny verze Excelu. Poté uvidíme na kartě Data položku Analýza. Zde zvolíme Analýza dat a objeví se dialogové okno, v němž vybereme Popisná statistika. Poté zadáme výběr dat a zaškrtneme políčko Celkový přehled. Lze zadat výstupní oblast dat na nový list nebo kdekoli potřebujeme. Výsledkem bude přehledná popisná statistika dat.
Tímto způsobem lze dostat statistiku bez přímého použití vzorců.
