V datové sadě se mohou objevit hodnoty, které se výrazně odlišují od ostatních. Tyto hodnoty se nazývají odlehlé hodnoty. Při použití Excelu pro analýzu dat mohou tyto extrémní hodnoty zkreslit celkové výsledky, například průměr. Naštěstí Excel nabízí několik užitečných funkcí, které vám pomohou s těmito odlehlými hodnotami efektivně pracovat. Podívejme se na to podrobněji.
Rychlý příklad
V následujícím příkladu jsou odlehlé hodnoty zjevné na první pohled – hodnota 2 u Erika a hodnota 173 u Ryana. V takto malém souboru dat je relativně snadné tyto hodnoty najít a případně s nimi dále pracovat.
Ovšem ve větším souboru dat to již tak jednoduché nebude. Proto je důležité umět automatizovaně identifikovat odlehlé hodnoty a v případě potřeby je eliminovat ze statistických výpočtů. A právě na to se zaměříme v tomto článku.
Jak vyhledat odlehlé hodnoty v datech
Pro nalezení odlehlých hodnot v datové sadě postupujeme následujícími kroky:
- Nejprve vypočítáme 1. a 3. kvartil (vysvětlíme si, co to znamená).
- Poté zjistíme mezikvartilové rozpětí (také si jej definujeme).
- Následně vypočítáme horní a dolní hranici našeho datového rozsahu.
- Pomocí těchto hranic pak určíme, které hodnoty jsou považovány za odlehlé.
Pro uložení těchto pomocných hodnot budeme používat buňky vpravo od naší datové sady, jak je vidět na obrázku níže.
Pojďme se do toho pustit!
Krok 1: Výpočet kvartilů
Pokud rozdělíme data na čtvrtiny, každá z těchto částí se nazývá kvartil. Prvních 25 % nejnižších čísel tvoří 1. kvartil, dalších 25 % je 2. kvartil, a tak dále. Tento krok je klíčový, protože nejčastější definice odlehlé hodnoty je taková, která leží více než 1,5 násobku mezikvartilového rozpětí (IQR) pod 1. kvartilem nebo více než 1,5 násobku IQR nad 3. kvartilem. Pro určení těchto hraničních hodnot tedy musíme nejprve zjistit, jaké jsou kvartily.
Excel nabízí funkci QUARTIL, která nám s výpočtem kvartilů pomůže. Tato funkce vyžaduje dva argumenty: pole a kvartil.
=QUARTILE(pole, kvartil)
Argument „pole“ představuje rozsah hodnot, ze kterého chceme kvartily vypočítat. Argument „kvartil“ je číslo, které specifikuje, který kvartil chceme získat (např. 1 pro 1. kvartil, 2 pro 2. kvartil atd.).
Poznámka: V Excelu 2010 a novějších verzích existují funkce QUARTILE.INC a QUARTILE.EXC jako vylepšení funkce QUARTILE. Funkce QUARTILE je zpětně kompatibilní se staršími verzemi Excelu.
Vraťme se k naší vzorové tabulce.
Pro výpočet 1. kvartilu zadáme do buňky F2 následující vzorec:
=QUARTILE(B2:B14,1)
Při zadávání vzorce Excel zobrazí nabídku možných hodnot pro argument „kvartil“.
Pro výpočet 3. kvartilu zadáme do buňky F3 vzorec podobný předchozímu, ale místo jedničky použijeme trojku:
=QUARTILE(B2:B14,3)
Nyní se v buňkách F2 a F3 zobrazí vypočítané hodnoty kvartilů.
Krok 2: Výpočet mezikvartilového rozpětí
Mezikvartilové rozpětí (IQR) představuje středních 50 % hodnot v našich datech. Vypočítá se jako rozdíl mezi hodnotou 3. kvartilu a hodnotou 1. kvartilu.
Do buňky F4 zadáme jednoduchý vzorec, který odečte hodnotu 1. kvartilu od 3. kvartilu:
=F3-F2
Nyní se v buňce F4 zobrazí naše mezikvartilové rozpětí.
Krok 3: Výpočet dolní a horní hranice
Dolní a horní hranice definují rozsah hodnot, které považujeme za „normální“. Jakékoli hodnoty, které leží mimo tento rozsah, jsou považovány za odlehlé.
Dolní hranici v buňce F5 vypočítáme tak, že hodnotu IQR vynásobíme 1,5 a poté ji odečteme od hodnoty 1. kvartilu:
=F2-(1.5*F4)
Poznámka: Závorky v tomto vzorci nejsou nezbytné, protože operace násobení se provede před odečítáním, ale pomáhají zlepšit čitelnost vzorce.
Pro výpočet horní hranice v buňce F6 opět vynásobíme hodnotu IQR číslem 1,5, ale tentokrát ji přičteme k hodnotě 3. kvartilu:
=F3+(1.5*F4)
Krok 4: Identifikace odlehlých hodnot
Nyní, když máme všechny potřebné pomocné hodnoty, je čas identifikovat odlehlé hodnoty – ty, které jsou menší než dolní hranice nebo větší než horní hranice.
K provedení tohoto logického testu použijeme funkci NEBO a zobrazíme hodnoty, které splňují naše kritéria. Do buňky C2 zadáme následující vzorec:
=OR(B2$F$6)
Poté tento vzorec zkopírujeme do buněk C3 až C14. Hodnota TRUE označuje, že se jedná o odlehlou hodnotu. Jak vidíte, v našem příkladu máme dvě odlehlé hodnoty.
Ignorování odlehlých hodnot při výpočtu průměru
Pomocí funkce QUARTIL a výpočtu IQR jsme si ukázali postup, jak identifikovat odlehlé hodnoty podle nejčastější definice. Pokud chceme ale pouze vypočítat průměr z dat a chceme při výpočtu ignorovat odlehlé hodnoty, existuje rychlejší a jednodušší funkce. Tato metoda neidentifikuje odlehlé hodnoty, ale umožňuje nám flexibilně nastavit, které části dat chceme z výpočtu vyloučit.
Potřebná funkce se jmenuje PRŮMĚR.SEŘÍZNUTÍ a její syntaxe je následující:
=PRŮMĚR.SEŘÍZNUTÍ(pole, procenta)
Argument „pole“ představuje rozsah hodnot, ze kterého chceme vypočítat průměr. Argument „procenta“ udává procento datových bodů, které se mají vyloučit z horní i dolní části souboru dat (může být zadáno jako procento nebo desetinné číslo).
V našem příkladu jsme do buňky D3 zadali vzorec, který vypočítá průměr a vyloučí 20 % odlehlých hodnot.
=PRŮMĚR.SEŘÍZNUTÍ(B2:B14, 20%)
Ukázali jsme si dvě různé funkce pro práci s odlehlými hodnotami. Ať už potřebujete odlehlé hodnoty identifikovat pro účely reportingu, nebo je chcete vyloučit z výpočtů, Excel nabízí funkce, které vám pomohou dosáhnout vašich cílů.