Izolovanje i Upravljanje Odstupanjima u Excel-u
Odstupanje, ili autlajer, predstavlja vrednost koja se značajno razlikuje, bilo da je znatno viša ili niža, od većine ostalih vrednosti unutar skupa podataka. Kada se koristi Excel za analizu podataka, odstupanja mogu izobličiti rezultate i dati nerealnu sliku. Na primer, aritmetička sredina skupa podataka može biti iskrivljena prisustvom autlajera i ne mora realno da odražava opšte vrednosti. Excel, srećom, nudi nekoliko funkcija koje nam pomažu da efikasno upravljamo takvim odstupanjima, i u nastavku ćemo detaljnije istražiti te mogućnosti.
Jednostavan Primer Odstupanja
Na priloženoj slici, odstupanja su lako uočljiva: vrednost 2 dodeljena Eriku i vrednost 173 dodeljena Rajanu. U ovako malom skupu podataka, prilično je jednostavno uočiti i ručno obraditi ova odstupanja.
Međutim, u većim skupovima podataka, takva identifikacija odstupanja postaje znatno teža. Sposobnost prepoznavanja i uklanjanja odstupanja iz statističkih izračuna je ključna za dobijanje tačnih i pouzdanih rezultata, i upravo ćemo se fokusirati na to kako se to može postići.
Kako Pronaći Odstupanja u Podacima
Za pronalaženje odstupajućih vrednosti unutar skupa podataka, sledićemo korake:
Izračunati prvi (Q1) i treći kvartil (Q3).
Izračunati interkvartilni raspon (IQR).
Odrediti gornju i donju granicu opsega podataka.
Koristiti te granice za identifikaciju odstupanja.
Ćelije sa desne strane skupa podataka na sledećoj slici poslužiće nam za smeštanje ovih vrednosti.
Krenimo sa procesom.
Prvi Korak: Izračunavanje Kvartila
Ako podelimo podatke na četiri jednaka dela, svaki od tih delova se naziva kvartil. Najnižih 25% vrednosti u opsegu čini prvi kvartil (Q1), sledećih 25% drugi kvartil (Q2) i tako dalje. Ovaj korak je prvi jer se najčešće odstupanje definiše kao podatak koji je više od 1,5 interkvartilnih raspona (IQR) ispod prvog kvartila ili 1,5 interkvartilnih raspona iznad trećeg kvartila. Dakle, da bismo odredili te vrednosti, prvo moramo razumeti kvartile.
Excel nudi funkciju QUARTILE za izračunavanje kvartila. Funkcija zahteva dva argumenta: niz vrednosti i kvartil koji želimo da vratimo.
=QUARTILE(niz, kvart)
Niz je opseg vrednosti koje analiziramo, a kvartil je broj koji predstavlja koji kvartil želimo da dobijemo (npr. 1 za prvi kvartil, 2 za drugi, itd.).
Napomena: U verziji Excel 2010, Microsoft je uveo funkcije QUARTILE.INC i QUARTILE.EXC kao poboljšanja funkcije QUARTILE. Funkcija QUARTILE je kompatibilnija sa starijim verzijama Excel-a.
Vratimo se našem primeru.
Za izračunavanje prvog kvartila u ćeliji F2, možemo koristiti sledeću formulu:
=QUARTILE(B2:B14,1)
Prilikom unosa formule, Excel nudi listu opcija za argument „kvartil“.
Za izračunavanje trećeg kvartila, unosimo sličnu formulu u ćeliju F3, ali umesto broja 1 koristimo broj 3.
=QUARTILE(B2:B14,3)
Sada u ćelijama F2 i F3 imamo vrednosti za prvi i treći kvartil.
Drugi Korak: Izračunavanje Interkvartilnog Raspona
Interkvartilni raspon (IQR) predstavlja srednjih 50% vrednosti u skupu podataka. Izračunava se kao razlika između vrednosti trećeg i prvog kvartila.
U ćeliji F4 unećemo jednostavnu formulu koja oduzima prvi kvartil od trećeg:
=F3-F2
Sada je prikazan interkvartilni raspon.
Treći Korak: Određivanje Donje i Gornje Granice
Donja i gornja granica su minimalna i maksimalna vrednost opsega podataka koji želimo da analiziramo. Sve vrednosti koje su ispod donje ili iznad gornje granice smatramo odstupanjima.
U ćeliji F5 ćemo izračunati donju granicu množenjem IQR vrednosti sa 1,5 i oduzimanjem dobijenog od prvog kvartila (Q1):
=F2-(1.5*F4)
Napomena: Zagrade u formuli nisu neophodne jer se množenje obavlja pre oduzimanja, ali doprinose čitljivosti formule.
Za izračunavanje gornje granice u ćeliji F6, ponovo množimo IQR sa 1.5, ali ovaj put dodajemo dobijeno na vrednost trećeg kvartila (Q3):
=F3+(1.5*F4)
Četvrti Korak: Identifikacija Odstupanja
Sada kada smo postavili sve osnove podatke, možemo da identifikujemo odstupanja – one vrednosti koje su ispod donje ili iznad gornje granice.
Koristićemo OR funkciju da izvršimo logički test i prikažemo vrednosti koje zadovoljavaju kriterijum odstupanja, unosom sledeće formule u ćeliju C2:
=OR(B2$F$6)
Zatim ćemo kopirati ovu formulu u sve ćelije od C3 do C14. TRUE vrednost označava odstupanje, i kao što možemo videti, imamo dva takva u našem skupu podataka.
Ignorisanje Odstupanja prilikom Izračunavanja Srednje Vrednosti
Iako smo koristili QUARTILE funkciju da izračunamo IQR i identifikujemo odstupanja, postoji brži i lakši način za izračunavanje srednje vrednosti (proseka) uz ignorisanje odstupanja. Ova metoda ne identifikuje odstupanja eksplicitno kao prethodna, ali daje nam fleksibilnost da odredimo šta smatramo odstupanjem.
Funkcija koju ćemo koristiti zove se TRIMMEAN, i njena sintaksa je:
=TRIMMEAN(niz, procenat)
Niz je opseg vrednosti za koji izračunavamo srednju vrednost. Procenat je procenat podataka koje treba isključiti sa gornjeg i donjeg kraja skupa podataka (može se uneti kao procentna ili decimalna vrednost).
U našem primeru, u ćeliju D3 unosimo formulu za izračunavanje proseka i isključivanje 20% odstupanja.
=TRIMMEAN(B2:B14, 20%)
Dakle, sada smo upoznali dve različite funkcije za rad sa odstupanjima. Bilo da želite da identifikujete odstupanja za potrebe izveštavanja ili da ih izuzmete iz proračuna kao što su proseci, Excel nudi funkcije koje odgovaraju vašim potrebama.