Prilikom rada s podacima u Excelu često ćete imati problema s postupanjem s izvanrednim vrijednostima u svom skupu podataka.
Ispuštanje je uobičajeno u svim vrstama podataka, pa je važno identificirati i tretirati te izdvojenosti kako biste bili sigurni da je vaša analiza točna i smislena.
U ovom vodiču ću vam pokazati kako pronaći eksterijere u Excelu, i neke od tehnika koje sam koristio u svom radu za rješavanje ovih izvanrednih vrijednosti.
Što su isticanja i zašto ih je važno pronaći?
Outlier je podatkovna točka koja je daleko iznad ostalih podatkovnih točaka u skupu podataka. Kad u podacima imate izdvojenost, to može iskriviti vaše podatke što može dovesti do netočnih zaključaka.
Navest ću vam jednostavan primjer.
Recimo da 30 ljudi putuje autobusom od odredišta A do odredišta B. Svi su ljudi u sličnoj težinskoj skupini i skupini prihoda. U svrhu ovog vodiča, uzmimo u obzir da je prosječna težina 220 kilograma, a prosječni godišnji prihod 70.000 dolara.
Sada negdje na sredini naše rute autobus staje i uskoči Bill Gates.
Što mislite da bi ovo učinilo prosječnoj težini i prosječnim primanjima ljudi u autobusu.
Iako se vjerojatno neće promijeniti prosječna težina, prosječni prihod ljudi u autobusu će se jako povećati.
To je zato što je prihod Bill Gatesa izvanredan u našoj grupi, a to nam daje pogrešno tumačenje podataka. Prosječan prihod za svaku osobu u autobusu iznosio bi nekoliko milijardi dolara, što je daleko iznad stvarne vrijednosti.
Kada radite sa stvarnim skupovima podataka u Excelu, možete imati isticanja u bilo kojem smjeru (tj. Pozitivan ili negativan izlaz).
A kako biste bili sigurni da je vaša analiza točna, morate nekako identificirati ove izdvojene vrijednosti, a zatim odlučiti kako ih najbolje tretirati.
Pogledajmo sada nekoliko načina kako pronaći Excel u Excelu.
Pronađite izdvojenosti razvrstavanjem podataka
Uz male skupove podataka, brz način za identificiranje izvanrednih vrijednosti je jednostavno sortiranje podataka i ručno pregledavanje nekih vrijednosti na vrhu ovih sortiranih podataka.
A budući da bi moglo doći do izdvajanja u oba smjera, svakako prvo sortirajte podatke uzlaznim, a zatim silaznim redoslijedom, a zatim prođite kroz najveće vrijednosti.
Dopustite mi da vam pokažem primjer.
Ispod imam skup podataka u kojem imam trajanje poziva (u sekundama) za 15 poziva korisničke službe.
U nastavku su navedeni koraci za sortiranje ovih podataka kako bismo mogli identificirati izdvojenosti u skupu podataka:
- Odaberite Zaglavlje stupca stupca koji želite sortirati (ćelija B1 u ovom primjeru)
- Kliknite karticu Početna
- U grupi Uređivanje kliknite ikonu Sortiraj i filtriraj.
- Pritisnite Custom Sort
- U dijaloškom okviru Sortiranje odaberite "Trajanje" na padajućem izborniku Poredaj po i "Od najvećeg do najmanjeg" u padajućem izborniku Narudžba
- Pritisnite U redu
Gore navedeni koraci razvrstali bi stupac trajanja poziva s najvišim vrijednostima na vrhu. Sada možete ručno skenirati podatke i provjeriti postoje li odstupanja.
U našem primjeru mogu vidjeti da su prve dvije vrijednosti daleko veće od ostalih vrijednosti (a dvije donje su znatno niže).
Napomena: Ova metoda radi s malim skupovima podataka gdje možete ručno skenirati podatke. To nije znanstvena metoda, ali dobro funkcionira
Pronalaženje outliera pomoću funkcija kvartila
Razgovarajmo sada o znanstvenijem rješenju koje vam može pomoći da utvrdite postoje li odstupanja ili ne.
U statistikama, kvartil je jedna četvrtina skupa podataka. Na primjer, ako imate 12 podatkovnih točaka, tada bi prvi kvartil bile tri donje podatkovne točke, drugi kvartil bile bi sljedeće tri podatkovne točke itd.
Ispod je skup podataka u kojem želim pronaći odstupanja. Da bih to učinio, morat ću izračunati prvi i treći kvartil, a zatim pomoću njega izračunati gornju i donju granicu.
Ispod je formula za izračun prvog kvartila u ćeliji E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
a evo jedan za izračunavanje trećeg kvartila u ćeliji E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Sada mogu upotrijebiti gornja dva izračuna za dobivanje interkvartilnog raspona (što je 50% naših podataka unutar prvog i trećeg kvartila)
= F3-F2
Sada ćemo koristiti međukvartilni raspon za pronalaženje donje i gornje granice koja bi sadržavala većinu naših podataka.
Sve što je izvan ovih donjih i gornjih granica tada bi se smatralo izvanrednim.
Ispod je formula za izračun donje granice:
= Kvartil1 - 1,5*(međukvartilni raspon)
što u našem primjeru postaje:
= F2-1,5*F4
Formula za izračunavanje gornje granice je:
= Kvartil 3 + 1,5*(međukvartilni raspon)
što u našem primjeru postaje:
= F3+1,5*F4
Sada kada imamo gornju i donju granicu u skupu podataka, možemo se vratiti na izvorne podatke i brzo identificirati one vrijednosti koje ne leže u ovom rasponu.
Brz način da to učinite bio bi provjeriti svaku vrijednost i vratiti TRUE ili FALSE u novi stupac.
Koristio sam donju formulu ILI da dobijem TRUE za one vrijednosti koje su izvanredne.
= ILI (B2 $ F $ 6)
Sada možete filtrirati stupac Outlier i prikazati samo zapise u kojima je vrijednost TRUE.
Alternativno, također možete koristiti uvjetno oblikovanje za isticanje svih ćelija u kojima je vrijednost TRUE
Bilješka: Iako je ovo prihvaćenija metoda za pronalaženje izostanka u statistici. Smatram da je ova metoda pomalo neupotrebljiva u scenarijima iz stvarnog života. U gornjem primjeru donja granica izračunata formulom je -103, dok skup podataka koji imamo može biti samo pozitivan. Dakle, ova metoda može nam pomoći u pronalaženju izoštrenih vrijednosti u jednom smjeru (visoke vrijednosti), beskorisna je u identificiranju ispadanja u drugom smjeru.Pronalaženje izvanrednih vrijednosti pomoću funkcija VELIKO/MALO
Ako radite s mnogo podataka (vrijednosti u više stupaca), možete izdvojiti najveću i najmanju 5 ili 7 vrijednosti i provjeriti ima li u njoj ikakvih izdvajanja.
Ako postoje odstupanja, moći ćete ih identificirati bez pregledavanja svih podataka u oba smjera.
Pretpostavimo da imamo niz podataka u nastavku i želimo znati postoje li neki odstupci.
Ispod je formula koja će vam dati najveću vrijednost u skupu podataka:
= VELIKO ($ B $ 2: $ B $ 16,1)
Slično, drugu najveću vrijednost će dati
= VELIKO ($ B $ 2: $ B $ 16,1)
Ako ne koristite Microsoft 365 koji ima dinamičke nizove, možete upotrijebiti donju formulu i ona će vam dati pet najvećih vrijednosti iz skupa podataka s jednom formulom:
= VELIKO ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Slično, ako želite najmanjih 5 vrijednosti, upotrijebite donju formulu:
= MALO ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
ili sljedeće ako nemate dinamičke nizove:
= MALO ($ B $ 2: $ B $ 16,1)
Kad dobijete ove vrijednosti, doista je lako saznati bilo kakve odstupanja u skupu podataka.
Iako sam odabrao izdvajanje najvećih i najmanjih 5 vrijednosti, možete odabrati da dobijete 7 ili 10 na temelju veličine vašeg skupa podataka.
Nisam siguran je li to prihvatljiva metoda za pronalaženje outliera u Excelu ili ne, ali ovo je metoda koju sam koristio kad sam morao raditi s puno financijskih podataka na svom poslu prije nekoliko godina. U usporedbi sa svim ostalim metodama opisanim u ovom vodiču, smatrao sam da je ova najučinkovitija.
Kako na pravi način postupati s outlierima
Do sada smo vidjeli metode koje će nam pomoći u pronalaženju odstupanja u našem skupu podataka. Ali što učiniti kada znate da postoje izdvojene vrijednosti.
Evo nekoliko metoda koje možete koristiti za rješavanje izvanrednih vrijednosti kako bi vaša analiza podataka bila točna.
Izbrišite isticanja
Najjednostavniji način uklanjanja izvanrednih vrijednosti iz vašeg skupa podataka je jednostavno njihovo brisanje. Na ovaj način neće iskriviti vašu analizu.
To je održivije rješenje ako imate velike skupove podataka, a brisanje nekoliko izdvajanja neće utjecati na cjelokupnu analizu. I naravno, prije brisanja podataka svakako napravite kopiju i uđite u uzroke tih izdvajanja.
Normalizirajte izvanredne vrijednosti (prilagodite vrijednost)
Normalizacija izdvajanja je ono što sam radila dok sam radila s punim radnim vremenom. Za sve vanjske vrijednosti jednostavno bih ih promijenio u vrijednost koja je nešto veća od maksimalne vrijednosti u skupu podataka.
Time sam bio siguran da ne brišem podatke, ali istovremeno ne dopuštam da mi iskrivi podatke.
Da vam dam primjer iz stvarnog života, ako analizirate maržu neto dobiti poduzeća, gdje većina tvrtki leži unutar -10%do 30%, a postoji nekoliko vrijednosti koje su veće od 100%, jednostavno bi promijenili ove vrijednosti izlaza na 30% ili 35%.
Dakle, ovo su neke od metoda koje možete koristiti Excel za pronalaženje odstupanja.
Nakon što ste identificirali izdvojene vrijednosti, možete zaroniti u podatke i potražiti što ih uzrokuje, u isto vrijeme odabrati jednu od tehnika za rukovanje tim izdvojenostima (što bi moglo ukloniti ove ili normalizirati ih prilagođavanjem vrijednosti)
Nadam se da vam je ovaj vodič bio koristan.
