Jak (a proč) používat funkci odlehlých hodnot v aplikaci Excel

Odlehlá hodnota je hodnota, která je výrazně vyšší nebo nižší než většina hodnot ve vašich datech. Při použití aplikace Excel k analýze dat mohou odlehlé hodnoty zkreslit výsledky. Například průměrný průměr souboru dat může skutečně odrážet vaše hodnoty. Excel poskytuje několik užitečných funkcí, které vám pomohou spravovat odlehlé hodnoty, takže se na to podívejme.

Rychlý příklad

Na obrázku níže jsou odlehlé hodnoty poměrně snadno rozpoznatelné - hodnota dvou přiřazená Ericovi a hodnota 173 přiřazená Ryanovi. V datové sadě, jako je tato, je dost snadné tyto odlehlé hodnoty zjistit a vypořádat se s nimi ručně.

U větší sady dat tomu tak nebude. Schopnost identifikovat odlehlé hodnoty a odstranit je ze statistických výpočtů je důležitá - a to je to, o čem se budeme zabývat v tomto článku.

Jak najít odlehlé hodnoty ve vašich datech

K vyhledání odlehlých hodnot v datové sadě použijeme následující kroky:

  1. Vypočítejte 1. a 3. kvartil (o tom, co jsou, si povíme jen trochu).
  2. Vyhodnoťte mezikvartilní rozsah (vysvětlíme je také trochu dále).
  3. Vraťte horní a dolní mez našeho rozsahu dat.
  4. Tyto hranice použijte k identifikaci odlehlých datových bodů.

K uložení těchto hodnot se použije rozsah buněk napravo od datové sady zobrazené na obrázku níže.

Začněme.

Krok první: Vypočítejte kvartily

Pokud své údaje rozdělíte na čtvrtiny, každá z těchto sad se nazývá kvartil. Nejnižší 25% čísel v rozsahu tvoří 1. kvartil, dalších 25% 2. kvartil atd. Nejprve provedeme tento krok, protože nejpoužívanější definicí odlehlé hodnoty je datový bod, který je více než 1,5 mezikvartilového rozsahu (IQR) pod 1. kvartilem a 1,5 mezikvartilového rozsahu nad 3. kvartilem. Abychom tyto hodnoty určili, musíme nejprve zjistit, jaké jsou kvartily.

Excel poskytuje funkci QUARTILE pro výpočet kvartilů. Vyžaduje dvě informace: pole a kvart.

= QUARTILE (pole, kvart)

Pole je rozsah hodnot, které se hodnotí. A kvart je číslo, které představuje kvartil, který chcete vrátit (např. 1 pro 1. kvartil, 2 pro 2. kvartil atd.).

Poznámka: V aplikaci Excel 2010 společnost Microsoft vydala funkce QUARTILE.INC a QUARTILE.EXC jako vylepšení funkce QUARTILE. QUARTILE je zpětněji kompatibilní při práci ve více verzích aplikace Excel.

Vraťme se k naší ukázkové tabulce.

Pro výpočet 1. kvartilu můžeme v buňce F2 použít následující vzorec.

= QUARTILE (B2: B14,1)

Při zadávání vzorce poskytuje Excel seznam možností pro argument kvart.

Pro výpočet 3. kvartilu můžeme do buňky F3 zadat vzorec jako předchozí, ale místo jednoho použijeme tři.

= QUARTILE (B2: B14,3)

Nyní máme kvartilové datové body zobrazené v buňkách.

Krok dva: Vyhodnoťte mezikvartilní rozsah

Interkvartilový rozsah (nebo IQR) je prostředních 50% hodnot ve vašich datech. Vypočítává se jako rozdíl mezi hodnotou 1. kvartilu a hodnotou 3. kvartilu.

Do buňky F4 použijeme jednoduchý vzorec, který odečte 1. kvartil od 3. kvartilu:

= F3-F2

Nyní vidíme zobrazený náš mezikvartilní rozsah.

Krok třetí: Vraťte dolní a horní hranici

Dolní a horní hranice jsou nejmenší a největší hodnoty rozsahu dat, které chceme použít. Jakékoli hodnoty menší nebo větší než tyto vázané hodnoty jsou odlehlé hodnoty.

Spodní mez v buňce F5 vypočítáme vynásobením hodnoty IQR 1,5 a následným odečtením od datového bodu Q1:

= F2- (1,5 * F4)

Poznámka: Závorky v tomto vzorci nejsou nutné, protože multiplikační část se vypočítá před odčítací částí, ale usnadňují čtení vzorce.

Pro výpočet horní meze v buňce F6 vynásobíme IQR opět 1,5, ale tentokrát ji přidáme do datového bodu Q3:

= F3 + (1,5 * F4)

Krok čtyři: Určete odlehlé hodnoty

Nyní, když máme nastavena všechna základní data, je čas identifikovat naše odlehlé datové body - ty, které jsou nižší než dolní mezní hodnota nebo vyšší než horní mezní hodnota.

K provedení tohoto logického testu použijeme funkci OR a zadáním následujícího vzorce do buňky C2 ukážeme hodnoty, které splňují tato kritéria:

= NEBO (B2 $ F $ 6)

Tuto hodnotu potom zkopírujeme do našich buněk C3-C14. Hodnota TRUE označuje odlehlou hodnotu a jak vidíte, v našich datech máme dvě.

Ignorování odlehlých hodnot při výpočtu průměrného průměru

Pomocí funkce QUARTILE vypočítáme IQR a pracujeme s nejpoužívanější definicí odlehlé hodnoty. Při výpočtu průměrného průměru pro rozsah hodnot a ignorování odlehlých hodnot však existuje rychlejší a snadnější funkce. Tato technika nebude identifikovat odlehlou hodnotu jako dříve, ale umožní nám být flexibilní s tím, co bychom mohli považovat za naši odlehlou část.

Funkce, kterou potřebujeme, se nazývá TRIMMEAN a níže vidíte její syntaxi:

= TRIMMEAN (pole, procenta)

Pole je rozsah hodnot Chcete-li průměr. Procent je procento datových bodů vyloučit z horní a dolní části souboru dat (můžete zadat jako procento nebo hodnota desítkové).

V našem příkladu jsme zadali vzorec níže do buňky D3, abychom vypočítali průměr a vyloučili 20% odlehlých hodnot.

= TRIMMEAN (B2: B14, 20%)

Zde máte dvě různé funkce pro zpracování odlehlých hodnot. Ať už je chcete identifikovat pro některé potřeby vykazování nebo je chcete vyloučit z výpočtů, jako jsou průměry, Excel má funkci, která vyhovuje vašim potřebám.