Při práci s daty v Excelu budete mít často problémy se zpracováním odlehlých hodnot ve vaší sadě dat.
Mít odlehlé hodnoty je u všech druhů dat zcela běžné a je důležité tyto odlehlé hodnoty identifikovat a zacházet s nimi, abyste se ujistili, že je vaše analýza správná a smysluplnější.
V tomto tutoriálu vám ukážu jak najít odlehlé hodnoty v Excelu, a některé z technik, které jsem použil ve své práci k řešení těchto odlehlých hodnot.
Co jsou odlehlé hodnoty a proč je důležité je najít?
Odlehlá hodnota je datový bod, který je daleko za ostatními datovými body v datové sadě. Pokud máte v datech odlehlou hodnotu, může vaše data zkosit, což může vést k nesprávným závěrům.
Uvedu jednoduchý příklad.
Řekněme, že 30 lidí cestuje autobusem z destinace A do destinace B. Všichni lidé jsou v podobné váhové skupině a příjmové skupině. Pro účely tohoto tutoriálu uvažujme průměrnou hmotnost 220 liber a průměrný roční příjem 70 000 $.
Nyní někde uprostřed naší trasy autobus zastaví a Bill Gates naskočí dovnitř.
Co si myslíte, že by to udělalo s průměrnou hmotností a průměrným příjmem lidí v autobuse?
Přestože se průměrná hmotnost pravděpodobně příliš nezmění, průměrný příjem lidí v autobuse prudce stoupne.
Je to proto, že příjem Billa Gatese je v naší skupině mimořádný, a to nám dává špatnou interpretaci dat. Průměrný příjem pro každou osobu v autobuse by byl několik miliard dolarů, což je daleko za skutečnou hodnotou.
Při práci se skutečnými datovými sadami v aplikaci Excel můžete mít odlehlé hodnoty v libovolném směru (tj. Pozitivní odchylku nebo zápornou odchylku).
A abyste se ujistili, že je vaše analýza správná, musíte tyto odlehlé hodnoty určit a poté se rozhodnout, jak s nimi nejlépe zacházet.
Nyní se podívejme na několik způsobů, jak v aplikaci Excel najít odlehlé hodnoty.
Najděte odlehlé hodnoty seřazením dat
U malých datových sad je rychlým způsobem identifikace odlehlých hodnot jednoduše seřazení dat a ruční procházení některými hodnotami v horní části těchto seřazených dat.
A protože v obou směrech mohou existovat odlehlé hodnoty, ujistěte se, že nejprve seřadíte data vzestupně a poté sestupně a poté projdete nejvyššími hodnotami.
Ukážu vám příklad.
Níže mám datovou sadu, kde mám délku hovoru (v sekundách) pro 15 volání zákaznického servisu.
Níže jsou uvedeny kroky k seřazení těchto dat, abychom mohli identifikovat odlehlé hodnoty v datové sadě:
- Vyberte záhlaví sloupce sloupce, který chcete řadit (v tomto příkladu buňka B1)
- Klikněte na kartu Domů
- Ve skupině Úpravy klikněte na ikonu Seřadit a filtrovat.
- Klikněte na Vlastní řazení
- V dialogovém okně Seřadit vyberte v rozevíracím seznamu Seřadit podle „Trvání“ a v rozevíracím seznamu Pořadí „Největší od nejmenšího“
- Klikněte na Ok
Výše uvedené kroky by seřadily sloupec délky hovoru s nejvyššími hodnotami nahoře. Nyní můžete data ručně naskenovat a zjistit, zda existují nějaké odlehlé hodnoty.
V našem příkladu vidím, že první dvě hodnoty jsou mnohem vyšší než zbývající hodnoty (a spodní dvě jsou mnohem nižší).
Poznámka: Tato metoda funguje s malými datovými sadami, kde můžete data naskenovat ručně. Není to vědecká metoda, ale funguje dobře
Hledání odlehlých hodnot pomocí kvartilních funkcí
Nyní pojďme mluvit o vědeckějším řešení, které vám pomůže identifikovat, zda existují nějaké odlehlé hodnoty nebo ne.
Ve statistikách je kvartil jedna čtvrtina datové sady. Pokud máte například 12 datových bodů, pak by první kvartil byly spodní tři datové body, druhý kvartil by byly další tři datové body atd.
Níže je soubor dat, kde chci najít odlehlé hodnoty. K tomu budu muset vypočítat 1. a 3. kvartil a poté pomocí něj vypočítat horní a dolní mez.
Níže je vzorec pro výpočet prvního kvartilu v buňce E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
a tady je ten, který vypočítá třetí kvartil v buňce E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Nyní mohu použít výše uvedené dva výpočty k získání mezikvartilového rozsahu (což je 50% našich dat v 1. a 3. kvartilu)
= F3-F2
Nyní použijeme mezikvartilní rozsah k nalezení dolní a horní hranice, která by obsahovala většinu našich dat.
Cokoli, co je mimo tyto dolní a horní hranice, by pak bylo považováno za odlehlé hodnoty.
Níže je vzorec pro výpočet spodní hranice:
= Quartile1 - 1,5*(Inter Quartile Range)
který se v našem příkladu stává:
= F2-1,5*F4
A vzorec pro výpočet horní hranice je:
= Quartile3 + 1,5*(Inter Quartile Range)
který se v našem příkladu stává:
= F3+1,5*F4
Nyní, když máme v naší sadě dat horní a dolní limit, můžeme se vrátit k původním datům a rychle identifikovat ty hodnoty, které nespadají do tohoto rozsahu.
Rychlý způsob, jak to provést, je zkontrolovat každou hodnotu a vrátit hodnotu TRUE nebo FALSE do nového sloupce.
Použil jsem níže uvedený vzorec NEBO, abych získal PRAVDA pro hodnoty, které jsou odlehlé.
= NEBO (B2 $ F $ 6)
Nyní můžete filtrovat sloupec odlehlých hodnot a zobrazovat pouze záznamy, kde je hodnota PRAVDA.
Alternativně můžete také použít podmíněné formátování pro zvýraznění všech buněk, kde je hodnota PRAVDA
Poznámka: I když je to přijatelnější metoda k nalezení odlehlých hodnot ve statistikách. Považuji tuto metodu za trochu nepoužitelnou v reálných scénářích. Ve výše uvedeném příkladu je dolní mez vypočtená podle vzorce -103, zatímco soubor dat, který máme, může být pouze kladný. Tato metoda nám tedy může pomoci najít odlehlé hodnoty v jednom směru (vysoké hodnoty), je zbytečná při identifikaci odlehlých hodnot v opačném směru.Hledání odlehlých hodnot pomocí funkcí LARGE/SMALL
Pokud pracujete s velkým množstvím dat (hodnoty ve více sloupcích), můžete extrahovat největší a nejmenší 5 nebo 7 hodnot a zjistit, zda v nich nejsou nějaké odlehlé hodnoty.
Pokud existují nějaké odlehlé hodnoty, budete je moci identifikovat, aniž byste museli procházet všechna data v obou směrech.
Předpokládejme, že máme níže uvedenou datovou sadu a chceme vědět, zda existují nějaké odlehlé hodnoty.
Níže je vzorec, který vám poskytne největší hodnotu v datové sadě:
= VELKÉ ($ B $ 2: $ B $ 16,1)
Podobně bude druhá největší hodnota dána pomocí
= VELKÉ ($ B $ 2: $ B $ 16,1)
Pokud nepoužíváte Microsoft 365, který má dynamická pole, můžete použít níže uvedený vzorec a poskytne vám pět největších hodnot z datové sady s jediným vzorcem:
= VELKÉ ($ B $ 2: $ B $ 16, ŘADA ($ 1: 5))
Podobně pokud chcete nejmenších 5 hodnot, použijte následující vzorec:
= MALÉ ($ B $ 2: $ B $ 16, ŘADA ($ 1: 5))
nebo následující v případě, že nemáte dynamická pole:
= MALÉ ($ B $ 2: $ B $ 16,1)
Jakmile máte tyto hodnoty, je opravdu snadné zjistit v tabulce dat nějaké odlehlé hodnoty.
I když jsem se rozhodl extrahovat největší a nejmenší 5 hodnot, můžete se rozhodnout získat 7 nebo 10 podle toho, jak velký je váš datový soubor.
Nejsem si jistý, jestli je to přijatelná metoda pro hledání odlehlých hodnot v Excelu, nebo ne, ale toto je metoda, kterou jsem použil, když jsem před pár lety musel ve své práci pracovat s velkým množstvím finančních dat. Ve srovnání se všemi ostatními metodami popsanými v tomto tutoriálu jsem zjistil, že tato metoda je nejefektivnější.
Jak zacházet s odlehlými hodnotami správnou cestou
Doposud jsme viděli metody, které nám pomohou najít odlehlé hodnoty v naší sadě dat. Co ale dělat, když víte, že existují odlehlé hodnoty.
Zde je několik metod, které můžete použít ke zpracování odlehlých hodnot, aby byla vaše analýza dat správná.
Odstraňte odlehlé hodnoty
Nejjednodušší způsob, jak odstranit odlehlé hodnoty ze sady dat, je jednoduše je odstranit. Tímto způsobem nezkreslí vaši analýzu.
Je to schůdnější řešení, když máte velké soubory dat a odstranění několika odlehlých hodnot neovlivní celkovou analýzu. A samozřejmě před smazáním dat vytvořte kopii a ponořte se do toho, co tyto odlehlé hodnoty způsobuje.
Normalizujte odlehlé hodnoty (upravte hodnotu)
Normalizace odlehlých hodnot je to, co jsem dělal, když jsem byl na plný úvazek. U všech odlehlých hodnot bych je jednoduše změnil na hodnotu, která je o něco vyšší než maximální hodnota v datové sadě.
Tím bylo zajištěno, že data nesmažu, ale zároveň je nenechám zkreslit.
Abych vám dal reálný příklad, analyzujete -li čistou ziskovou marži společností, kde většina společností leží v rozmezí -10%až 30%a existuje několik hodnot, které jsou vyšší než 100%, já jednoduše změní tyto odlehlé hodnoty na 30% nebo 35%.
Toto jsou tedy některé z metod, které můžete použít Excel k nalezení odlehlých hodnot.
Jakmile identifikujete odlehlé hodnoty, můžete se ponořit do dat a hledat, co je způsobuje, a současně zvolit jednu z technik, jak s těmito odlehlými hodnotami zacházet (což by mohlo být jejich odstranění nebo jejich normalizace úpravou hodnoty)
Doufám, že jste našli tento návod užitečný.
