Jak (a proč) použít funkci Outliers v aplikaci Excel

Odchylka je hodnota, která je výrazně vyšší nebo nižší než většina hodnot v datech. Pokud používáte aplikaci Excel k analýze dat, mohou mít odlehčení výsledky. Například průměrný průměr datového souboru může skutečně odrážet vaše hodnoty. Aplikace 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 následujícím obrázku jsou odlehlé hodnoty relativně snadné - hodnota dvou přiřazených Ericovi a hodnota 173 přiřazená Ryanovi. V datovém souboru, jako je tento, je to dost snadné k tomu, aby se tyto odlehlé hodnoty vyřešily ručně.

Ve větším souboru dat to nebude. Být schopen identifikovat odlehlé hodnoty a odstranit je ze statistických výpočtů je důležitý - a to je to, co budeme zkoumat, jak dělat v tomto článku.

Jak zjistit odlehlé hodnoty ve vašich datech

Chcete-li zjistit mimořádné hodnoty v sadě dat, použijeme následující kroky:

Vypočítejte 1. a 3. čtvrtletí (budeme mluvit o tom, co jsou jen trochu).
Vyhodnoťte interkvartilní rozmezí (budeme je také vysvětlovat o něco dále).
Vraťte horní a spodní hranice našeho datového rozsahu.
Použijte tyto limity k identifikaci vzdálených datových bodů.

Rozsah buněk na pravé straně datového souboru, který je vidět na následujícím obrázku, se použije k uložení těchto hodnot.

Začněme.

Krok první: Vypočítejte čtvrtiny

Pokud rozdělíte data na čtvrtiny, každá z těchto sad se nazývá kvartil. Nejnižší 25% čísel v rozsahu tvoří první kvartil, dalších 25% druhého kvartilu a tak dále. Tento krok jsme nejprve učinili, protože nejpoužívanější definice odlehlosti je datový bod, který je více než 1,5 interkvartilních rozsahů (IQR) pod 1. kvartilem a 1,5 mezikvartilních rozsahů nad třetím kvartilem. K určení těchto hodnot musíme nejdříve zjistit, jaké jsou kvartily.

Aplikace Excel poskytuje kvartilu funkci QUARTILE. Vyžaduje dvě informace: pole a čtverec.

= QUARTILE (pole, quart)

The pole je rozsah hodnot, které hodnotíte. A kvart je číslo, které představuje kvartil, který chcete vrátit (např. 1 pro 1^Svatý kvartil, 2 pro 2. kvartil a tak dále).

Poznámka: V aplikaci Excel 2010 společnost Microsoft uvolnila funkce QUARTILE.INC a QUARTILE.EXC 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 do tabulky příkladů.

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

= QUARTILE (B2: B14,1)

Jakmile zadáte vzorec, Excel poskytuje seznam možností pro argument quart.

Pro výpočet hodnoty 3^rd kvartil, můžeme zadat vzorec jako předchozí v buňce F3, ale pomocí tří namísto jednoho.

= QUARTILE (B2: B14,3)

Teď máme kvantové datové body zobrazené v buňkách.

Druhý krok: Vyhodnoťte mezikvartilní rozsah

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

Použijeme jednoduchý vzorec do buňky F4, který odečte hodnotu 1^Svatý kvartil od 3^rd kvartil:

= F3-F2

Nyní můžeme vidět náš interkvartilní rozsah zobrazený.

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

Dolní a horní hranice jsou nejmenší a největší hodnoty datového rozsahu, který chceme použít. Všechny hodnoty menší nebo větší než tyto vázané hodnoty jsou odlehčující hodnoty.

Vypočíteme hranici dolní hranice v buňce F5 vynásobením hodnoty IQR hodnotou 1,5 a odečtením od datového bodu Q1:

= F2- (1,5 * F4)

Poznámka: Závorky v tomto vzorci nejsou nutné, protože část násobení bude vypočtena před odčítáním, ale učiní to pro snadnější čtení vzorec.

Chcete-li vypočítat horní hranici v buňce F6, vynásobíme IQR znovu 1,5, tentokrát však přidat do datového bodu Q3:

= F3 + (1,5 * F4)

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

Nyní, když máme všechny základní údaje nastavené, je čas zjistit naše vzdálené datové body - ty, které jsou nižší než hodnota dolní hranice nebo vyšší než hodnota horní hranice.

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

= OR (B2 $ F $ 6)

Potom tuto hodnotu zkopírujeme do buněk C3-C14. Hodnota TRUE označuje oddělovač 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čteme IQR a pracujeme s nejčastěji používanou definicí odlehlosti. Při výpočtu průměrného průměru pro rozsah hodnot a ignorování odchylky je však k dispozici rychlejší a snadnější funkce. Tato technika nebude identifikovat odlehlost jako předtím, ale umožní nám být flexibilní s tím, co bychom mohli považovat za svou odlehlou část.

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

= TRIMMEAN (pole, procento)

The pole je rozsah hodnot, které chcete průměrně měnit. The procent je procento datových bodů, které chcete vyloučit z horní a spodní části sady dat (můžete je zadat jako procento nebo desetinnou hodnotu).

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

= TRIMMEAN (B2: B14, 20%)

Tam máte dvě různé funkce pro manipulaci s odlehlými hodnotami. Ať už je chcete identifikovat pro potřeby přehledů nebo je vyloučit z výpočtů, jako jsou průměry, aplikace Excel má funkci, která odpovídá vašim potřebám.