Ako (a prečo) používať funkciu Outliers v programe Excel

Odchýlka je hodnota, ktorá je výrazne vyššia alebo nižšia ako väčšina hodnôt vo vašich údajoch. Pri používaní programu Excel na analýzu údajov môžu výsledky odľahčenia skresľovať výsledky. Napríklad priemerný priemer množiny údajov môže skutočne odrážať vaše hodnoty. Program Excel poskytuje niekoľko užitočných funkcií, ktoré vám pomôžu zvládnuť vaše mimoriadne udalosti. Pozrime sa teda.

Rýchly príklad

Na obrázku nižšie sú odľahlé hodnoty dostatočne ľahko odhalené - hodnota dvoch priradených Ericovi a hodnota 173 určená pre Ryana. V súbore údajov, ako je táto, je to dosť ľahké na to, aby sa tieto odchýlky objavili ručne.

V rozsiahlejšom súbore údajov to tak nie je. Byť schopný identifikovať odchýlky a odstrániť ich zo štatistických výpočtov je dôležitý - a to je to, čo budeme sledovať, ako robiť v tomto článku.

Ako nájsť odľahlé údaje vo svojich údajoch

Ak chcete nájsť odľahlé hodnoty v množine údajov, používame nasledujúce kroky:

Vypočítajte prvý a tretí kvartilu (budeme hovoriť o tom, čo sú len trochu).
Vyhodnoťte medzikvartilný rozsah (vysvetlíme to aj trochu ďalej).
Vráťme hornú a dolnú hranicu nášho rozsahu údajov.
Použite tieto hranice na identifikáciu vzdialených dátových bodov.

Rozsah buniek vpravo od množiny údajov zobrazený na obrázku nižšie sa použije na uloženie týchto hodnôt.

Začnime.

Krok 1: Vypočítajte štvrtiny

Ak rozdeľujete svoje údaje na štvrtiny, každá z týchto množín sa nazýva kvartil. Najnižšie 25% čísel v rozsahu tvorí prvý kvartil, ďalších 25% 2. štvrťroku atď. Tento krok sme urobili ako prvý, pretože najpoužívanejšou definíciou odchýlky je dátový bod, ktorý je viac ako 1,5 interkvartilných rozsahov (IQR) pod prvým kvartilom a 1,5 interkvartílnych rozsahov nad tretím štvrťrokom. Na určenie týchto hodnôt musíme najprv zistiť, aké sú kvartily.

Program Excel poskytuje funkciu QUARTILE na výpočet kvartilov. Vyžaduje dve informácie: pole a kvartér.

= QUARTILE (pole, quart)

rad je rozsah hodnôt, ktoré hodnotíte. A Kvart je číslo, ktoré predstavuje kvartil, ktorý chcete vrátiť (napr. 1 pre 1^st kvartil, 2 pre 2. kvartil a tak ďalej).

Poznámka: V programe Excel 2010 spoločnosť Microsoft uvoľnila funkcie QUARTILE.INC a QUARTILE.EXC ako vylepšenia funkcie QUARTILE. QUARTILE je viac spätne kompatibilný pri práci vo viacerých verziách programu Excel.

Vráťme sa k našej príkladovej tabuľke.

Vypočítať 1^st Kvartil môžeme použiť nasledujúci vzorec v bunke F2.

= Kvartil (B2: B14,1)

Ako zadáte vzorec, program Excel poskytuje zoznam možností pre argument quart.

Vypočítať 3^rd kvartil, môžeme zadať vzorec ako predchádzajúci v bunke F3, ale pomocou troch namiesto jedného.

= Kvartil (B2: B14,3)

Teraz máme kvantové dátové body zobrazené v bunkách.

Druhý krok: Vyhodnoťte medzikvartilný rozsah

Medzikvartilový rozsah (alebo IQR) je stredných 50% hodnôt vo vašich údajoch. Vypočíta sa ako rozdiel medzi prvou kvartilovou hodnotou a treťou kvartilovou hodnotou.

Použijeme jednoduchý vzorec do bunky F4, ktorá odčíta 1^st kvartil z 3^rd kvartil:

= F3-F2

Teraz môžeme vidieť náš interkvartilný rozsah.

Tretí krok: vráťte spodnú a hornú hranicu

Dolná a horná hranica sú najmenšie a najväčšie hodnoty dátového rozsahu, ktorý chceme použiť. Akékoľvek hodnoty menšie alebo väčšie ako tieto viazané hodnoty sú odľahlé hodnoty.

Spočítame limit spodnej hranice v bunke F5 vynásobením hodnoty IQR o 1,5 a potom odčítaním z dátového bodu Q1:

= F2- (1,5 * F4)

Poznámka: Zápaly v tomto vzorci nie sú potrebné, pretože časť násobenia bude počítať pred odčítaním časti, ale robia vzorec ľahšie na čítanie.

Ak chcete vypočítať hornú hranicu v bunke F6, vynásobíme IQR opäť 1,5, ale tentoraz pridať do dátového bodu Q3:

= F3 + (1,5 * F4)

Krok štyri: Identifikujte nadbytočné hodnoty

Teraz, keď máme všetky základné údaje nastavené, je čas určiť naše vzdialené dátové body - tie, ktoré sú nižšie ako hodnota dolnej hranice alebo vyššia ako horná hraničná hodnota.

Na vykonanie tohto logického testu použijeme funkciu OR a ukážeme hodnoty, ktoré spĺňajú tieto kritériá, zadaním nasledujúceho vzorca do bunky C2:

= Alebo (B2 $ F $ 6)

Potom túto hodnotu skopírujeme do buniek C3-C14. Hodnota TRUE označuje odstup a ako vidíte, v našich údajoch máme dve.

Ignorovanie odľahlých stránok pri výpočte priemerného priemeru

Pomocou funkcie QUARTILE vypočítajte IQR a pracujeme s najrozšírenejšou definíciou odchýlky. Avšak pri výpočte priemerného priemeru pre rozsah hodnôt a ignorovanie mimoriadnych hodnôt existuje rýchlejšia a jednoduchšia funkcia. Táto technika neidentifikuje odľahlosť ako predtým, ale umožní nám byť flexibilná s tým, čo by sme mohli považovať za našu odľahčenú časť.

Funkcia, ktorú potrebujeme, sa nazýva TRIMMEAN a môžete vidieť jeho syntax nižšie:

= TRIMMEAN (pole, percento)

rad je rozsah hodnôt, ktoré chcete priemerovať. percento je percento dátových bodov, ktoré sa majú vylúčiť z hornej a dolnej časti súboru údajov (môžete ich zadať ako percento alebo desatinnú hodnotu).

V našom príklade sme vložili do vzorca D3 do bunky D3, aby sme vypočítali priemernú hodnotu a vylúčili sme 20% odľahlých hodnôt.

= TRIMMEAN (B2: B14, 20%)

Tam máte dve rôzne funkcie na zvládnutie mimoriadnych udalostí. Bez ohľadu na to, či ich chcete identifikovať v prípade niektorých požiadaviek na vykazovanie alebo ich vylúčiť z výpočtov, ako sú napríklad priemery, program Excel má funkciu, ktorá zodpovedá vašim potrebám.