Pri práci s údajmi v Exceli budete mať často problémy so spracovaním odľahlých hodnôt vo svojej množine údajov.
Mať odľahlé hodnoty je úplne bežné vo všetkých druhoch údajov a je dôležité tieto odľahlé hodnoty identifikovať a zaobchádzať s nimi, aby ste sa uistili, že vaša analýza je správna a zmysluplnejšia.
V tomto návode vám to ukážem ako nájsť odľahlé hodnoty v programe Excel, a niektoré z techník, ktoré som vo svojej práci použil na zvládnutie týchto extrémnych hodnôt.
Čo sú odľahlé hodnoty a prečo je dôležité ich nájsť?
Odľahlá hodnota je údajový bod, ktorý výrazne presahuje ostatné dátové body v súbore údajov. Ak máte v údajoch odľahlú hodnotu, môže skresliť vaše údaje, čo môže viesť k nesprávnym záverom.
Uvediem jednoduchý príklad.
Povedzme, že 30 ľudí cestuje autobusom z destinácie A do destinácie B. Všetci ľudia sú v podobnej hmotnostnej skupine a príjmovej skupine. Na účely tohto tutoriálu uvažujme priemernú hmotnosť 220 libier a priemerný ročný príjem 70 000 dolárov.
Teraz niekde v strede našej trasy autobus zastaví a Bill Gates naskočí dovnútra.
Čo si myslíte, že by to urobilo s priemernou hmotnosťou a priemerným príjmom ľudí v autobuse.
Hoci sa priemerná hmotnosť pravdepodobne príliš nezmení, priemerný príjem ľudí v autobuse bude prudko stúpať.
Je to preto, že príjem Billa Gatesa je v našej skupine odľahlý, a to nám dáva nesprávnu interpretáciu údajov. Priemerný príjem pre každú osobu v autobuse by bol niekoľko miliárd dolárov, čo je oveľa viac, ako je skutočná hodnota.
Pri práci so skutočnými množinami údajov v Exceli môžete mať odľahlé hodnoty v ľubovoľnom smere (t. J. Kladnú alebo zápornú hodnotu).
A aby ste sa uistili, že je vaša analýza správna, musíte tieto mimoriadne hodnoty identifikovať a potom sa rozhodnúť, ako s nimi najlepšie zaobchádzať.
Teraz sa pozrime na niekoľko spôsobov, ako nájsť odľahlé hodnoty v programe Excel.
Nájdite odľahlé hodnoty zoradením údajov
V prípade malých množín údajov je rýchlym spôsobom, ako identifikovať odľahlé hodnoty, jednoducho zoradiť údaje a ručne prejsť niektorými hodnotami v hornej časti týchto zoradených údajov.
A pretože v oboch smeroch môžu existovať odľahlé hodnoty, uistite sa, že ste údaje najskôr zoradili vzostupne a potom zostupne a potom prechádzali najvyššími hodnotami.
Ukážem vám príklad.
Nasleduje súbor údajov, kde mám dĺžku hovoru (v sekundách) pre 15 telefonátov na zákaznícky servis.
Nasledujú kroky na zoradenie týchto údajov, aby sme v množine údajov mohli identifikovať odľahlé hodnoty:
- Vyberte hlavičku stĺpca v stĺpci, ktorý chcete zoradiť (v tomto prípade bunka B1)
- Kliknite na kartu Domov
- V skupine Úpravy kliknite na ikonu Zoradiť a filtrovať.
- Kliknite na položku Vlastné zoradenie
- V dialógovom okne Zoradiť vyberte v rozbaľovacom zozname Zoradiť podľa „Trvanie“ a v rozbaľovacej ponuke Poradie „Najväčšia na najmenšiu“.
- Kliknite na Ok
Vyššie uvedené kroky by zoradili stĺpec trvania hovoru s najvyššími hodnotami v hornej časti. Teraz môžete údaje manuálne naskenovať a zistiť, či existujú nejaké výnimky.
V našom prípade vidím, že prvé dve hodnoty sú oveľa vyššie ako ostatné hodnoty (a spodné dve hodnoty sú oveľa nižšie).
Poznámka: Táto metóda funguje s malými množinami údajov, kde môžete údaje naskenovať ručne. Nie je to vedecká metóda, ale funguje dobre
Hľadanie odľahlých hodnôt pomocou kvartilných funkcií
Teraz sa porozprávajme o vedeckejšom riešení, ktoré vám môže pomôcť identifikovať, či existujú nejaké extrémne hodnoty alebo nie.
V štatistikách je kvartil jednou štvrtinou súboru údajov. Ak máte napríklad 12 dátových bodov, potom prvý kvartil budú posledné tri údajové body, druhý kvartil budú ďalšie tri údajové body atď.
Nasleduje súbor údajov, kde chcem nájsť odľahlé hodnoty. Aby som to urobil, budem musieť vypočítať 1. a 3. kvartil a potom pomocou neho vypočítať hornú a dolnú hranicu.
Nasleduje vzorec na výpočet prvého kvartilu v bunke E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
a tu je ten, ktorý vypočíta tretí kvartil v bunke E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Teraz môžem vyššie uvedené dva výpočty použiť na získanie medzikvartilového rozsahu (čo je 50% našich údajov v rámci prvého a tretieho kvartilu)
= F3-F2
Teraz použijeme medzikvartilný rozsah na nájdenie spodnej a hornej hranice, ktoré by obsahovali väčšinu našich údajov.
Čokoľvek, čo je mimo týchto dolných a horných hraníc, by sa potom považovalo za mimoriadne hodnoty.
Nasleduje vzorec na výpočet spodnej hranice:
= Quartile1 - 1,5*(Inter Quartile Range)
ktorý sa v našom prípade stáva:
= F2-1,5*F4
A vzorec na výpočet hornej hranice je:
= Quartile3 + 1,5*(Inter Quartile Range)
ktorý sa v našom prípade stáva:
= F3+1,5*F4
Teraz, keď máme v súbore údajov hornú a dolnú hranicu, môžeme sa vrátiť k pôvodným údajom a rýchlo identifikovať tie hodnoty, ktoré nespadajú do tohto rozsahu.
Rýchly spôsob, ako to urobiť, je skontrolovať každú hodnotu a vrátiť PRAVDU alebo NEPRAVDU do nového stĺpca.
Na získanie hodnoty TRUE pre hodnoty, ktoré sú odľahlé, som použil nižšie uvedený vzorec ALEBO.
= ALEBO (B2 $ F $ 6)
Teraz môžete filtrovať stĺpec Odľahlé a zobrazovať iba záznamy, kde je hodnota PRAVDA.
Alternatívne môžete tiež použiť podmienené formátovanie na zvýraznenie všetkých buniek, kde je hodnota PRAVDA
Poznámka: Aj keď je to prijateľnejšia metóda na nájdenie extrémnych hodnôt v štatistikách. Považujem túto metódu za trochu nepoužiteľnú v skutočných scenároch. Vo vyššie uvedenom príklade je dolná hranica vypočítaná podľa vzorca -103, zatiaľ čo súbor údajov, ktorý máme, môže byť iba kladný. Táto metóda nám teda môže pomôcť nájsť odľahlé hodnoty v jednom smere (vysoké hodnoty), je zbytočná pri identifikácii odľahlých hodnôt v druhom smere.Hľadanie odľahlých hodnôt pomocou funkcií VEĽKÉ/MALÉ
Ak pracujete s veľkým počtom údajov (hodnoty vo viacerých stĺpcoch), môžete extrahovať najväčšiu a najmenšiu hodnotu 5 alebo 7 hodnôt a zistiť, či v nich nie sú žiadne mimoriadne hodnoty.
Ak existujú nejaké odľahlé hodnoty, budete ich môcť identifikovať bez toho, aby ste museli prechádzať všetkými údajmi v oboch smeroch.
Predpokladajme, že máme nižšie uvedený súbor údajov a chceme vedieť, či existujú nejaké odľahlé hodnoty.
Nasleduje vzorec, ktorý vám poskytne najväčšiu hodnotu v množine údajov:
= VEĽKÉ ($ B $ 2: $ B $ 16,1)
Podobne bude druhá najväčšia hodnota daná hodnotou
= VEĽKÉ ($ B $ 2: $ B $ 16,1)
Ak nepoužívate Microsoft 365, ktorý má dynamické polia, môžete použiť nasledujúci vzorec a poskytne vám päť najväčších hodnôt z množiny údajov s jediným vzorcom:
= VEĽKÉ ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Podobne, ak chcete najmenších 5 hodnôt, použite nasledujúci vzorec:
= MALÉ ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
alebo nasledujúce v prípade, že nemáte dynamické polia:
= MALÉ ($ B $ 2: $ B $ 16,1)
Akonáhle budete mať tieto hodnoty, je skutočne ľahké zistiť akékoľvek odľahlé hodnoty v množine údajov.
Aj keď som sa rozhodol extrahovať 5 najväčších a najmenších hodnôt, môžete sa rozhodnúť získať 7 alebo 10 podľa toho, aký veľký je váš súbor údajov.
Nie som si istý, či je to prijateľná metóda na hľadanie odľahlých hodnôt v programe Excel alebo nie, ale je to metóda, ktorú som použil, keď som pred niekoľkými rokmi musel vo svojej práci pracovať s veľkým počtom finančných údajov. V porovnaní so všetkými ostatnými metódami uvedenými v tomto návode som zistil, že táto je najúčinnejšia.
Ako zaobchádzať s odľahlými hodnotami správnym spôsobom
Doteraz sme videli metódy, ktoré nám pomôžu nájsť v našom súbore údajov odľahlé hodnoty. Čo však robiť, keď viete, že existujú extrémne hodnoty.
Tu je niekoľko metód, ktoré môžete použiť na spracovanie odľahlých hodnôt, aby bola vaša analýza údajov správna.
Odstráňte odľahlé hodnoty
Najľahší spôsob, ako odstrániť odľahlé hodnoty z množiny údajov, je jednoducho ich odstrániť. Týmto spôsobom to neskreslí vašu analýzu.
Je to životaschopnejšie riešenie, keď máte veľké množiny údajov a odstránenie niekoľkých odľahlých hodnôt neovplyvní celkovú analýzu. A samozrejme, pred odstránením údajov si vytvorte kópiu a ponorte sa do toho, čo tieto extrémne hodnoty spôsobuje.
Normalizujte odľahlé hodnoty (upravte hodnotu)
Normalizácia odľahlých hodnôt je to, čo som robil, keď som bol na plný úväzok. Pre všetky krajné hodnoty by som ich jednoducho zmenil na hodnotu, ktorá je o niečo vyššia ako maximálna hodnota v súbore údajov.
To zaistilo, že údaje nevymažem, ale zároveň ich nenechám skresľovať.
Aby som vám dal príklad z reálneho života, ak analyzujete maržu čistého zisku spoločností, kde väčšina spoločností leží v rozmedzí od -10%do 30%a existuje niekoľko hodnôt, ktoré sú vyššie ako 100%, by jednoducho zmenil tieto odľahlé hodnoty na 30% alebo 35%.
Toto sú teda niektoré z metód, ktoré môžete použiť Excel na vyhľadanie odľahlých hodnôt.
Akonáhle identifikujete odľahlé hodnoty, môžete sa ponoriť do údajov a pátrať po tom, čo ich spôsobuje, a zároveň si vybrať jednu z techník na zvládnutie týchto odľahlých hodnôt (ktorými by mohli byť ich odstránenie alebo normalizácia úpravou hodnoty)
Dúfam, že ste našli tento návod užitočný.
