Pri delu s podatki v Excelu boste v svojem naboru podatkov pogosto imeli težave z ravnanjem z odstopanji.
Odstopanja so precej pogosta v vseh vrstah podatkov, zato je pomembno, da jih prepoznamo in obravnavamo, da se prepričamo, da je vaša analiza pravilna in bolj smiselna.
V tej vadnici vam bom pokazal kako najti izvirnike v Excelu, in nekaj tehnik, ki sem jih uporabil pri svojem delu za obvladovanje teh odstopanj.
Kaj so izstopajoče in zakaj jih je pomembno najti?
Odstotek je podatkovna točka, ki presega druge podatkovne točke v naboru podatkov. Ko imate v podatkih odstopanje, lahko to izkrivi vaše podatke, kar lahko vodi do napačnih sklepov.
Naj vam dam preprost primer.
Recimo, da 30 ljudi potuje z avtobusom od cilja A do cilja B. Vsi ljudje so v podobni skupini teže in dohodkovni skupini. Za namene te vadnice pomislimo, da je povprečna teža 220 funtov, povprečni letni dohodek pa 70.000 USD.
Zdaj nekje na sredini poti se avtobus ustavi in vstopi Bill Gates.
Kaj mislite, da bi to naredilo s povprečno težo in povprečnim dohodkom ljudi na avtobusu.
Čeprav se povprečna teža verjetno ne bo bistveno spremenila, se bo povprečni dohodek ljudi v avtobusu močno povečal.
To je zato, ker je dohodek Billa Gatesa v naši skupini izstopajoč, kar nam daje napačno razlago podatkov. Povprečni dohodek za vsako osebo v avtobusu bi bil nekaj milijard dolarjev, kar presega dejansko vrednost.
Pri delu z dejanskimi nabori podatkov v Excelu imate lahko odstopanja v kateri koli smeri (to je pozitiven ali negativen odstopanje).
In da se prepričate, da je vaša analiza pravilna, morate nekako prepoznati te odstopanja in se nato odločiti, kako jih najbolje obravnavati.
Zdaj pa poglejmo nekaj načinov, kako najti Excel v Excelu.
Poiščite odstopnike z razvrščanjem podatkov
Z majhnimi nabori podatkov je hiter način za prepoznavanje odstopanj preprosto razvrščanje podatkov in ročni pregled nekaterih vrednosti na vrhu teh razvrščenih podatkov.
Ker lahko pride do odstopanj v obeh smereh, najprej najprej razvrstite podatke po naraščajočem in nato po padajočem vrstnem redu, nato pa pojdite skozi najvišje vrednosti.
Naj vam pokažem primer.
Spodaj imam nabor podatkov, kjer imam trajanje klicev (v sekundah) za 15 klicev službe za stranke.
Spodaj so navedeni koraki za razvrščanje teh podatkov, da lahko v naboru podatkov prepoznamo odstopanja:
- Izberite glavo stolpca stolpca, ki ga želite razvrstiti (celica B1 v tem primeru)
- Kliknite zavihek Domov
- V skupini Urejanje kliknite ikono Razvrsti in filtriraj.
- Kliknite Razvrščanje po meri
- V pogovornem oknu Razvrsti izberite »Trajanje« v spustnem meniju Razvrsti po in »Od največjega do najmanjšega« v spustnem meniju Naročilo
- Kliknite V redu
Zgornji koraki bi razvrstili stolpec trajanja klica z najvišjimi vrednostmi na vrhu. Zdaj lahko ročno skenirate podatke in preverite, ali obstajajo odstopanja.
V našem primeru vidim, da sta prvi dve vrednosti precej višji od preostalih vrednosti (spodnji dve pa sta precej nižji).
Opomba: Ta metoda deluje z majhnimi nabori podatkov, kjer lahko ročno skenirate podatke. To ni znanstvena metoda, vendar dobro deluje
Iskanje odstopanj z uporabo kvartilnih funkcij
Zdaj pa se pogovorimo o bolj znanstveni rešitvi, ki vam lahko pomaga ugotoviti, ali obstajajo odstopanja ali ne.
V statistiki je kvartil ena četrtina nabora podatkov. Če imate na primer 12 podatkovnih točk, bi bil prvi kvartil spodnje tri podatkovne točke, drugi kvartil bi bile naslednje tri podatkovne točke itd.
Spodaj je niz podatkov, kjer želim najti odstopanja. Če želite to narediti, bom moral izračunati 1. in 3. kvartil, nato pa z njegovo pomočjo izračunati zgornjo in spodnjo mejo.
Spodaj je formula za izračun prvega kvartila v celici E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
in tukaj je tisti, ki izračuna tretji kvartil v celici E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Sedaj lahko uporabim zgornja dva izračuna, da dobim Interkvartilno območje (kar je 50% naših podatkov v prvem in tretjem kvartilu)
= F3-F2
Zdaj bomo uporabili interkvartilno območje za iskanje spodnje in zgornje meje, ki bi vsebovala večino naših podatkov.
Vse, kar je zunaj teh spodnjih in zgornjih meja, bi se potem štelo za odstopanje.
Spodaj je formula za izračun spodnje meje:
= Kvartil1 - 1,5*(razpon med četrtinami)
kar v našem primeru postane:
= F2-1,5*F4
Formula za izračun zgornje meje je:
= Kvartil 3 + 1,5*(razpon med četrtinami)
kar v našem primeru postane:
= F3+1,5*F4
Zdaj, ko imamo v naboru podatkov zgornjo in spodnjo mejo, se lahko vrnemo k prvotnim podatkom in hitro ugotovimo tiste vrednosti, ki ne spadajo v to območje.
Hiter način za to bi bil preveriti vsako vrednost in vrniti TRUE ali FALSE v nov stolpec.
Uporabil sem spodnjo formulo ALI, da dobim TRUE za tiste vrednosti, ki so zunanje vrednosti.
= ALI (B2 $ F $ 6)
Zdaj lahko filtrirate stolpec Outlier in prikažete samo zapise, kjer je vrednost TRUE.
Lahko pa uporabite tudi pogojno oblikovanje, da označite vse celice, kjer je vrednost TRUE
Opomba: Čeprav je to bolj sprejemljiva metoda za odkrivanje odstopanj v statistiki. Ta metoda se mi zdi nekoliko neuporabna v resničnih scenarijih. V zgornjem primeru je spodnja meja, izračunana po formuli, -103, medtem ko je nabor podatkov, ki ga imamo, lahko le pozitiven. Tako nam lahko ta metoda pomaga pri odkrivanju odstopanj v eni smeri (visoke vrednosti), neuporabna je pri prepoznavanju odstopanj v drugi smeri.Iskanje odstopanj s funkcijami LARGE/SMALL
Če delate z veliko podatkov (vrednosti v več stolpcih), lahko izvlečete največjo in najmanjšo vrednost 5 ali 7 in preverite, ali so v njej kakšne odstopanja.
Če obstajajo odstopanja, jih boste lahko prepoznali, ne da bi morali pregledati vse podatke v obe smeri.
Recimo, da imamo spodnji nabor podatkov in želimo vedeti, ali obstajajo odstopanja.
Spodaj je formula, ki vam bo dala največjo vrednost v naboru podatkov:
= VELIKO ($ B $ 2: $ B $ 16,1)
Podobno bo drugo največjo vrednost dal
= VELIKO ($ B $ 2: $ B $ 16,1)
Če ne uporabljate Microsoft 365, ki ima dinamične matrike, lahko uporabite spodnjo formulo, ki vam bo dala eno največjo vrednost iz nabora podatkov z eno samo formulo:
= VELIKO ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Podobno, če želite najmanjših 5 vrednosti, uporabite spodnjo formulo:
= MALO ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
ali naslednje, če nimate dinamičnih nizov:
= MALO ($ B $ 2: $ B $ 16,1)
Ko imate te vrednosti, je zelo enostavno odkriti morebitne odstopanja v naboru podatkov.
Medtem ko sem se odločil izvleči največje in najmanjše 5 vrednosti, se lahko odločite, da dobite 7 ali 10 glede na to, kako velik je vaš nabor podatkov.
Nisem prepričan, ali je to sprejemljiva metoda za iskanje odstopanj v Excelu ali ne, vendar je to metoda, ki sem jo uporabil, ko sem moral pred nekaj leti pri svojem delu delati z veliko finančnimi podatki. V primerjavi z vsemi drugimi metodami, ki jih obravnava ta vadnica, se mi je zdela ta najučinkovitejša.
Kako ravnati z zunanjimi vrednostmi na pravi način
Doslej smo videli metode, ki nam bodo pomagale najti odstopanja v našem naboru podatkov. Kaj pa storiti, ko veste, da obstajajo odstopanja.
Tukaj je nekaj načinov, s katerimi lahko ravnate z odstopanji, da bo analiza podatkov pravilna.
Izbrišite odstopanja
Najlažji način, da odstranite odstopanja iz nabora podatkov, je, da jih preprosto izbrišete. Tako ne bo popačil vaše analize.
To je bolj izvedljiva rešitev, če imate velike nabore podatkov in brisanje nekaj odstopanj ne bo vplivalo na splošno analizo. In seveda, preden izbrišete podatke, se prepričajte, da ste ustvarili kopijo in se poglobili, kaj povzroča te odstopanja.
Normalizirajte odstopanja (prilagodite vrednost)
Normalizacija odstopanj je tisto, kar sem včasih počel, ko sem bil zaposlen za polni delovni čas. Za vse zunanje vrednosti bi jih preprosto spremenil v vrednost, ki je nekoliko višja od največje vrednosti v nizu podatkov.
To je zagotovilo, da podatkov ne izbrišem, hkrati pa ne dovolim, da mi popači podatke.
Na primer, če analizirate stopnjo čistega dobička podjetij, kjer je večina podjetij v razponu od -10%do 30%, obstaja pa nekaj vrednosti, ki so navzgor 100%, bi preprosto spremenili te vrednosti odstopanja na 30% ali 35%.
To so torej nekatere metode, ki jih lahko uporabite Excel za iskanje izstopajočih vrednosti.
Ko ugotovite odstopanja, se lahko poglobite v podatke in poiščete, kaj jih povzroča, hkrati pa izberite eno od tehnik za ravnanje s temi odstopanji (ki bi jih lahko odstranili ali normalizirali s prilagajanjem vrednosti)
Upam, da vam je bila ta vadnica koristna.
