Kako (i zašto) koristiti Outliers funkciju u Excelu

Outlier je vrijednost koja je značajno viša ili niža od većine vrijednosti u vašim podacima. Kada koristite Excel za analizu podataka, outlieri mogu iskriviti rezultate. Na primjer, srednja vrijednost skupa podataka može uistinu odražavati vaše vrijednosti. Excel pruža nekoliko korisnih funkcija za pomoć u upravljanju vašim outlierima, pa pogledajmo.

Brzi primjer

Na slici ispod, outlieri su razumljivo lako uočiti - vrijednost dva dodijeljena Ericu i vrijednost 173 dodijeljena Ryanu. U skupu podataka kao što je ovaj, lako je uočiti i riješiti te outliere ručno.

U većem skupu podataka to neće biti slučaj. Bitno je biti u stanju identificirati ekstreme i ukloniti ih iz statističkih izračuna - i to je ono što ćemo gledati kako to učiniti u ovom članku.

Kako pronaći otkaze u svojim podacima

Da bismo pronašli ekstreme u skupu podataka, koristimo sljedeće korake:

Izračunajte prvi i treći kvartil (govorimo o tome što su to samo malo).
Procijenite raspon međukartila (to ćemo također objasniti malo niže).
Vratite gornju i donju granicu našeg raspona podataka.
Koristite ove granice da biste identificirali udaljene podatkovne točke.

Raspon ćelija desno od skupa podataka koji se vidi na donjoj slici koristit će se za pohranjivanje tih vrijednosti.

Započnimo.

Prvi korak: Izračunajte kvartile

Ako podijelite podatke na četvrtine, svaki od tih skupova naziva se kvartil. Najniže 25% brojeva u rasponu čine prvi kvartil, sljedećih 25% drugi kvartil, i tako dalje. Ovaj korak najprije koristimo jer je najčešće korištena definicija outlier-a podatkovna točka koja je više od 1,5 interkvartilnih raspona (IQR) ispod prvog kvartila, a 1,5 interkvartilnih raspona iznad 3. kvartila. Da bismo odredili te vrijednosti, prvo moramo shvatiti što su kvartili.

Excel pruža QUARTILE funkciju za izračun kvartila. Za to su potrebne dvije informacije: polje i kvart.

= QUARTILE (niz, kvart)

red je raspon vrijednosti koje ocjenjujete. I kvart je broj koji predstavlja kvartil koji želite vratiti (npr. 1 za 1^st kvartil, 2 za drugi kvartil, i tako dalje).

Bilješka: U programu Excel 2010 Microsoft je izdao funkcije QUARTILE.INC i QUARTILE.EXC kao poboljšanja funkcije QUARTILE. QUARTILE je kompatibilniji unatrag kada radi u više verzija programa Excel.

Vratimo se našem primjeru tablice.

Za izračun 1^st Kvartil možemo koristiti sljedeću formulu u ćeliji F2.

= QUARTILE (B2: B14,1)

Dok unosite formulu, Excel nudi popis opcija za argument quart.

Za izračunavanje 3^rd kvartil, možemo unijeti formulu poput prethodne u ćeliji F3, ali koristeći tri umjesto jedne.

= QUARTILE (B2: B14,3)

Sada imamo kvartilne podatkovne točke prikazane u stanicama.

Drugi korak: procijenite interkvartilni raspon

Interkvartilni raspon (ili IQR) je srednji 50% vrijednosti u vašim podacima. Izračunava se kao razlika između vrijednosti 1. kvartila i vrijednosti trećeg kvartila.

Koristit ćemo jednostavnu formulu u ćeliji F4 koja oduzima 1^st kvartil od 3. \ t^rd kvartil:

-F3-F2

Sada možemo vidjeti naš interkvartilni raspon.

Treći korak: Vratite donju i gornju granicu

Donja i gornja granica su najmanje i najveće vrijednosti raspona podataka koje želimo koristiti. Sve vrijednosti koje su manje ili veće od tih vezanih vrijednosti su ekstremne vrijednosti.

Izračunat ćemo donju graničnu granicu u ćeliji F5 množenjem IQR vrijednosti s 1,5, a zatim je oduzeti od Q1 podatkovne točke:

= F2- (1.5 * F4)

Bilješka: Zagrade u ovoj formuli nisu potrebne jer će se dio množenja izračunati prije dijela za oduzimanje, ali oni olakšavaju čitanje formule.

Da bismo izračunali gornju granicu u ćeliji F6, umnožit ćemo IQR za 1,5, ali ovaj put dodati do podatkovne točke Q3:

= + F3 (1,5 * F4)

Četvrti korak: Identificirajte ekstreme

Sada kada smo postavili sve temeljne podatke, vrijeme je da identificiramo naše vanjske podatkovne točke - one koje su niže od donje granične vrijednosti ili više od gornje granice.

Koristit ćemo funkciju OR za izvođenje ovog logičkog testa i prikaz vrijednosti koje zadovoljavaju te kriterije unosom sljedeće formule u ćeliju C2:

= OR (B2 $ F $ 6)

Tada ćemo kopirati tu vrijednost u naše C3-C14 stanice. TRUE vrijednost označava outlier, i kao što možete vidjeti, imamo dvije u našim podacima.

Ignoriranje outliersa pri izračunavanju srednjeg prosjeka

Korištenjem funkcije QUARTILE izračunali smo IQR i radili s najčešće korištenom definicijom outliera. Međutim, pri izračunavanju prosječnog prosjeka za raspon vrijednosti i ignoriranje outliera, postoji brža i lakša funkcija. Ova tehnika neće identificirati outlier kao prije, ali će nam omogućiti da budemo fleksibilni s onim što bismo mogli uzeti u obzir naš outlier dio.

Funkcija koja nam je potrebna zove se TRIMMEAN, a ispod nje možete vidjeti sintaksu:

= TRIMMEAN (niz, postotak)

red je raspon vrijednosti koje želite prosjeciti. posto je postotak podatkovnih točaka koje treba isključiti s vrha i dna skupa podataka (možete ga unijeti kao postotak ili decimalnu vrijednost).

Donju formulu smo unijeli u ćeliju D3 u našem primjeru kako bismo izračunali prosjek i isključili 20% outliera.

= TRIMMEAN (B2: B14, 20%)

Tu imate dvije različite funkcije za rukovanje ekstremima. Bez obzira želite li ih identificirati za neke potrebe izvješćivanja ili ih izuzeti iz izračuna kao što su prosjeci, Excel ima funkciju koja odgovara vašim potrebama.