Jak používat funkci Outliers v Excelu

Když analyzujete data, odlehlé hodnoty mohou zkreslit výsledky a snížit jejich spolehlivost. Pokud je například jedna nebo více vašich hodnot podstatně vyšší než většina údajů, je průměr vyšší, což nemusí odrážet údaje jako celek. Odstranění těchto odlehlých datových bodů je proto důležitým krokem při provádění spolehlivých statistických výpočtů. Bez vyhrazené funkce odlehlých hodnot je nejjednodušším způsobem provedení testu odlehlých hodnot v aplikaci Excel použití mezikvartilní rozsah k identifikaci odlehlých hodnot, ale můžete také použít funkci Trimmean k jejich aproximaci výsledek.

Mezikvartilový rozsah dat je rozsah pokrytý „krabicí“ na krabicovém grafu nebo více přesněji výsledek odečtení hodnoty pro první kvartil dat od hodnoty pro třetí kvartil. Excel obsahuje vestavěnou funkci pro výpočet libovolného kvartilu pro vaše data. Najděte náhradní buňku a zadejte „=QUARTILE([rozsah dat], [číslo kvartilu])“ a zadejte rozsah buněk pro vaše data, kde je uvedeno „[rozsah dat]“ a požadovaný kvartil tam, kde je uvedeno „[kvartil číslo]."

Pokud máte například data v buňkách A2 až A101 a chcete hodnotu pro první kvartil, zadejte "=QUARTILE(A2:A101, 1)", abyste našli hodnotu. U první části argumentu můžete myší zvýraznit příslušné buňky, ale za čárku musíte napsat číslo požadovaného kvartilu. Pro třetí kvartil se stejnými daty zadáte "=QUARTILE(A2:A101, 3)", abyste získali výsledek.

Pomocí další prázdné buňky odečtěte hodnotu buňky prvního kvartilu od hodnoty buňky třetího kvartilu. Pokud je první kvartil v buňce C2 a třetí kvartil je v buňce D2, zadejte "=D2-C2", abyste získali výsledek. Toto je mezikvartilní rozsah.

Chcete-li najít odlehlé hodnoty, můžete nyní použít mezikvartilové rozmezí ve vzorci pro odlehlé hodnoty, který uvádí, že horní limit dat je hodnota třetí kvartil plus 1,5násobek mezikvartilového rozmezí a spodní hranice je hodnota prvního kvartilu mínus 1,5násobek mezikvartilu rozsah.

Pokud je hodnota prvního kvartilu v C2, hodnota třetího kvartilu je v buňce D2 a mezikvartilový rozsah je do buňky E2 byste zadali „=C2-(1,5 * E2)“, abyste našli spodní limit, a „=D2+(1,5 * E2)“, abyste našli horní omezit. Obecně zadáte "=[první kvartil] – (1,5 * [mezikvartilové rozmezí])" pro nalezení spodního limitu a "=[třetí kvartil] + (1,5 * [interkvartilní rozsah])" pro nalezení horního limitu.

Cokoli pod spodní hranicí nebo nad horní hranicí je odlehlá hodnota.

Chcete-li dokončit test odlehlých hodnot v aplikaci Excel, použijte logickou funkci "OR" k efektivnímu zjištění, které hodnoty ve vaší datové třídě jsou odlehlé hodnoty. Zadejte „=OR([datová buňka]>[horní limit], [datová buňka]$F$2, A2

Pokud je hodnota v A2 nad horním limitem nebo pod spodním limitem, zobrazí se „TRUE“, což znamená, že hodnota je odlehlá. Tento vzorec můžete přetáhnout dolů kliknutím na pravý dolní roh buňky se vzorcem a přetáhněte ji dolů tak, aby skončila vedle poslední datové buňky, aby se u každé provedl stejný výpočet datový bod.

Můžete také zvýraznit data a přejít na "Podmíněné formátování" v sekci "Styly" na kartě "Domů", pokud chcete změnit formátování pro odlehlé hodnoty. Zvolte "Nové pravidlo" a zvýrazněte možnost "Použít vzorec k určení, které buňky se mají formátovat". Zadejte stejný vzorec jako v předchozím odstavci a poté klikněte na možnost "Formátovat", abyste zvolili jedinečné formátování pro odlehlé hodnoty.

Funkce "Trimmean" je jednodušší způsob identifikace odlehlých hodnot. Chcete-li funkci použít, zadejte „=TRIMMEAN([rozsah dat], [proporce k oříznutí])“ s rozsahem buněk obsahujících data namísto „[rozsah dat]“ a desetinným číslem procento, které chcete oříznout, kde je uvedeno "[proporce k oříznutí]." Tím se odstraní extrémní hodnoty nahoře a dole a na základě nich se pak vypočítá průměr zbývající. Pokud byste tedy ořezali 10 procent, odstranilo by se horních 5 procent a spodních 5 procent před výpočtem průměru.

Pokud data běží od A2 do A101 a chcete oříznout extrémních 5 procent hodnot, zadejte "=TRIMMEAN(A2:A101, 0,05)", abyste našli upravený průměr. Můžete zkrátit 15 procent tím, že místo toho napíšete "=TRIMMEAN(A2:A101, 0,15)".