Outlier je hodnota, která je významněvyšší nebo nižší než většina hodnot v datech. Při použití Excelu k analýze dat mohou výsledky odlehčit. Například průměrný průměr sady dat může skutečně odrážet vaše hodnoty. Excel poskytuje několik užitečných funkcí, které vám pomohou spravovat vaše odlehlé hodnoty, takže se podívejme.
Rychlý příklad
Na obrázku níže jsou odlehlé hodnoty přiměřeněsnadno na místě - hodnota dvou přiřazená Ericovi a hodnota 173 přiřazená Ryanovi. V takovéto datové sadě je snadné snadno zjistit a řešit tyto odlehlé hodnoty ručně.
Ve větší sadě dat to nebudepřípad. Schopnost identifikovat odlehlé hodnoty a odstranit je ze statistických výpočtů je důležitá - a proto se podíváme, jak v tomto článku postupovat.
Jak najít odlehlé údaje
K nalezení odlehlých hodnot v datové sadě používáme následující kroky:
- Vypočítejte 1. a 3. kvartily (budeme mluvit o tom, co v nich je).
- Vyhodnoťte mezikvartilový rozsah (tyto vysvětlíme také o něco dále).
- Vraťte horní a dolní mez našeho rozsahu dat.
- Tyto meze použijte k identifikaci odlehlých datových bodů.
K uložení těchto hodnot se použije oblast buněk napravo od datové sady zobrazené na obrázku níže.
Začněme.
Krok 1: Vypočítejte kvartily
Pokud vaše data rozdělíte na čtvrtiny, každá z nichtyto sady se nazývají kvartily. Nejnižší 25% čísel v rozsahu tvoří 1. kvartil, dalších 25% 2. kvartil atd. Tento krok uděláme jako první, protože nejrozšířenější definicí odlehlé hodnoty je datový bod, který je více než 1,5 mezikvartilního rozsahu (IQR) pod 1. kvartilem a 1,5 mezikvartilního rozsahu nad 3. kvartilem. K určení těchto hodnot musíme nejprve zjistit, co jsou kvartily.
Excel poskytuje funkci QUARTILE pro výpočet kvartilů. Vyžaduje to dvě informace: pole a kvart.
=QUARTILE(array, quart)
The pole je rozsah hodnot, které vyhodnocujete. A kvart je číslo, které představuje kvartil, který chcete vrátit (např. 1 pro 1Svatý kvartil, 2 pro 2. kvartil atd.).
Poznámka: V Excelu 2010 společnost Microsoft vydala QUARTILE.INC a QUARTILE.EXC fungují jako vylepšení funkce QUARTILE. QUARTILE je více zpětně kompatibilní při práci ve více verzích aplikace Excel.
Vraťme se k naší ukázkové tabulce.
Pro výpočet 1Svatý Kvartil můžeme použít následující vzorec v buňce F2.
=QUARTILE(B2:B14,1)
Při zadávání vzorce poskytuje Excel seznam možností argumentu kvart.
Pro výpočet 3rd kvartilu, můžeme do buňky F3 zadat vzorec jako ten předchozí, ale místo jednoho použijeme tři.
=QUARTILE(B2:B14,3)
Nyní máme v buňkách zobrazeny kvartilové datové body.
Krok 2: Vyhodnoťte mezikvartilní rozsah
Mezikvartilní rozsah (nebo IQR) je středními 50% hodnot v datech. Vypočítá se jako rozdíl mezi hodnotou 1. kvartilu a 3. kvartilem.
Do buňky F4 použijeme jednoduchý vzorec, který odečte 1Svatý kvartil ze 3rd kvartil:
=F3-F2
Nyní můžeme vidět náš mezikvartilní rozsah.
Krok tři: Vraťte dolní a horní hranice
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.
Vypočítáme dolní limit v buňce F5 vynásobením hodnoty IQR 1,5 a potom odečtením z 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 odečtením části, ale usnadňuje čtení vzorce.
Pro výpočet horní hranice v buňce F6 vynásobíme IQR znovu 1,5, ale tentokrát přidat do datového bodu Q3:
=F3+(1.5*F4)
Krok 4: Identifikace odlehlých hodnot
Nyní, když máme nastavena všechna základní data, je čas identifikovat naše odlehlé datové body - ty, které jsou nižší než dolní mez nebo vyšší než horní mez.
Použijeme funkci OR k provedení tohoto logického testu a k zobrazení hodnot, které splňují tato kritéria, zadáním následujícího vzorce do buňky C2:
=OR(B2<$F$5,B2>$F$6)
Tuto hodnotu pak zkopírujeme do našich buněk C3-C14. Hodnota PRAVDA 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ámeIQR a práce s nejčastěji používanou definicí odlehlé hodnoty. Při výpočtu průměrného průměru pro rozsah hodnot a ignorování odlehlých hodnot je však k dispozici rychlejší a snadnější funkce. Tato technika nebude identifikovat odlehlé hodnoty 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 její syntaxi můžete vidět níže:
=TRIMMEAN(array, percent)
The pole je rozsah hodnot, které chcete průměrovat. procent je procento datových bodů, které mají být vyloučeny z horní a dolní části sady dat (můžete je zadat jako procentuální nebo desítkovou hodnotu).
V následujícím příkladu jsme do buňky D3 zadali vzorec pro výpočet průměru a vyloučení 20% odlehlých hodnot.
=TRIMMEAN(B2:B14, 20%)
Zde máte dvě různé funkcemanipulace s odlehlými hodnotami. Ať už je chcete identifikovat pro některé potřeby vykazování nebo je vyloučit z výpočtů, jako jsou průměry, Excel má funkci, která odpovídá vašim potřebám.
