Dirbdami su „Excel“ duomenimis, dažnai susiduriate su duomenų rinkinio pašalinių rodiklių problemomis.
Nuokrypiai yra gana dažni visų rūšių duomenyse, todėl svarbu nustatyti ir apdoroti šiuos nukrypimus, kad įsitikintumėte, jog analizė yra teisinga ir prasmingesnė.
Šioje pamokoje aš jums parodysiu kaip rasti nukrypimus „Excel“ir kai kuriuos metodus, kuriuos naudojau savo darbe, kad galėčiau tvarkyti šiuos nukrypimus.
Kas yra nukrypimai ir kodėl svarbu juos rasti?
Nuokrypis yra duomenų taškas, kuris yra toli nuo kitų duomenų rinkinio duomenų taškų. Kai jūsų duomenys yra prastesni, tai gali iškreipti jūsų duomenis, o tai gali sukelti neteisingų išvadų.
Pateiksiu paprastą pavyzdį.
Tarkime, 30 žmonių keliauja autobusu iš kelionės tikslo A į paskirties vietą B. Visi žmonės yra panašios svorio ir pajamų grupės. Šioje pamokoje vidutinis svoris yra 220 svarų, o vidutinės metinės pajamos - 70 000 USD.
Dabar kažkur mūsų maršruto viduryje sustoja autobusas ir įlipa Billas Gatesas.
Dabar, kaip manote, ar tai paveiktų vidutinį autobuso žmonių svorį ir vidutines pajamas?
Nors vidutinis svoris greičiausiai nesikeis, vidutinės autobuso žmonių pajamos labai pakils.
Taip yra todėl, kad Billo Gateso pajamos yra mūsų grupėje neįprastos, todėl mes neteisingai interpretuojame duomenis. Vidutinės kiekvieno autobuso keleivio pajamos būtų keli milijardai dolerių, o tai gerokai viršija tikrąją vertę.
Dirbdami su faktiniais duomenų rinkiniais „Excel“, galite turėti nukrypimų bet kuria kryptimi (t. Y. Teigiamą ar neigiamą nukrypimą).
Ir norėdami įsitikinti, kad jūsų analizė yra teisinga, turite kažkaip nustatyti šiuos nukrypimus ir tada nuspręsti, kaip geriausiai juos gydyti.
Dabar pažiūrėkime kelis būdus, kaip sužinoti „Excel“ nukrypimus.
Raskite nukrypimus rūšiuodami duomenis
Naudojant nedidelius duomenų rinkinius, greitas būdas nustatyti nukrypimus yra tiesiog surūšiuoti duomenis ir rankiniu būdu pereiti kai kurias vertes, esančias šių surūšiuotų duomenų viršuje.
Ir kadangi abiem kryptimis gali būti nukrypimų, įsitikinkite, kad pirmiausia surūšiavote duomenis didėjančia tvarka, o vėliau - mažėjančia tvarka, o tada peržiūrėkite aukščiausias vertes.
Leiskite parodyti jums pavyzdį.
Žemiau turiu duomenų rinkinį, kuriame turiu 15 klientų aptarnavimo skambučių trukmę (sekundėmis).
Toliau pateikiami šių duomenų rūšiavimo veiksmai, kad galėtume nustatyti duomenų rinkinio nukrypimus:
- Pasirinkite stulpelio, kurį norite rūšiuoti, stulpelio antraštę (šiame pavyzdyje B1 langelis)
- Spustelėkite skirtuką Pagrindinis
- Redagavimo grupėje spustelėkite piktogramą Rūšiuoti ir filtruoti.
- Spustelėkite Pasirinktinis rūšiavimas
- Dialogo lango Rūšiuoti išskleidžiamajame sąraše Rūšiuoti pagal pasirinkite „Trukmė“ ir išskleidžiamajame sąraše „Nuo didžiausių iki mažiausių“
- Spustelėkite Gerai
Pirmiau minėti veiksmai surūšiuos skambučio trukmės stulpelį, kurio viršuje bus didžiausios vertės. Dabar galite rankiniu būdu nuskaityti duomenis ir pamatyti, ar nėra nukrypimų.
Mūsų pavyzdyje matau, kad pirmosios dvi vertės yra daug didesnės už likusias vertes (o dvi apatinės yra daug mažesnės).
Pastaba: Šis metodas veikia su mažais duomenų rinkiniais, kuriuose galite rankiniu būdu nuskaityti duomenis. Tai nėra mokslinis metodas, bet veikia gerai
Nuokrypių radimas naudojant kvartilines funkcijas
Dabar pakalbėkime apie labiau mokslinį sprendimą, kuris gali padėti nustatyti, ar yra kokių nors nukrypimų.
Statistikoje kvartilis yra ketvirtadalis duomenų rinkinio. Pavyzdžiui, jei turite 12 duomenų taškų, tada pirmasis kvartilis bus trys apatiniai duomenų taškai, antrasis kvartilis - kiti trys duomenų taškai ir pan.
Žemiau yra duomenų rinkinys, kuriame noriu rasti nukrypimus. Norėdami tai padaryti, turėsiu apskaičiuoti 1 -ąjį ir 3 -ąjį kvartilius, o paskui jį apskaičiuoti viršutinę ir apatinę ribas.
Žemiau pateikiama formulė, skirta apskaičiuoti pirmąjį kvartilį E2 langelyje:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
ir čia yra tas, kuris apskaičiuoja trečiąjį kvartilį E3 ląstelėje:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Dabar galiu naudoti du aukščiau pateiktus skaičiavimus, kad gaučiau interkvartilinį diapazoną (kuris yra 50% mūsų duomenų 1 ir 3 kvartile)
= F3-F2
Dabar mes naudosime interkvartilinį diapazoną, kad surastume apatinę ir viršutinę ribas, kuriose būtų dauguma mūsų duomenų.
Visa tai, kas nepatenka į šias apatines ir viršutines ribas, tuomet būtų laikoma pašaline.
Žemiau yra formulė, skirta apskaičiuoti apatinę ribą:
= Kvartilis1 - 1,5*(tarpkvartilinis diapazonas)
kuris mūsų pavyzdyje tampa:
= F2-1,5*F4
Ir viršutinės ribos apskaičiavimo formulė yra tokia:
= Kvartilis3 + 1,5*(tarpkvartilinis diapazonas)
kuris mūsų pavyzdyje tampa:
= F3+1,5*F4
Dabar, kai duomenų rinkinyje yra viršutinė ir apatinė ribos, galime grįžti prie pradinių duomenų ir greitai nustatyti tas vertes, kurios nėra šiame diapazone.
Greitas būdas tai padaryti būtų patikrinti kiekvieną vertę ir naujame stulpelyje pateikti TIESA arba NETIESA.
Aš naudoju toliau pateiktą ARBA formulę, kad gaučiau TIKRĄ tų verčių, kurios yra nukrypusios.
= ARBA (B2 $ F $ 6)
Dabar galite filtruoti stulpelį Outlier ir rodyti tik tuos įrašus, kurių vertė yra TRUE.
Arba taip pat galite naudoti sąlyginį formatavimą, kad paryškintumėte visas ląsteles, kuriose vertė yra TIKRA
Pastaba: Nors tai yra labiau priimtas metodas statistikos nuokrypiams rasti. Manau, kad šis metodas yra šiek tiek nenaudojamas realaus gyvenimo scenarijuose. Ankstesniame pavyzdyje apatinė riba, apskaičiuota pagal formulę, yra -103, o mūsų turimas duomenų rinkinys gali būti tik teigiamas. Taigi šis metodas gali padėti mums rasti nuokrypius viena kryptimi (didelės vertės), nenaudinga nustatyti nukrypimus kita kryptimi.Nuokrypių paieška naudojant LARGE/SMALL funkcijas
Jei dirbate su daugybe duomenų (vertės keliuose stulpeliuose), galite išgauti didžiausią ir mažiausią 5 ar 7 reikšmes ir pamatyti, ar jame nėra nukrypimų.
Jei yra kokių nors nuokrypių, galėsite juos identifikuoti, nesinaudodami visais duomenimis abiem kryptimis.
Tarkime, kad turime žemiau esantį duomenų rinkinį ir norime sužinoti, ar yra kokių nors nukrypimų.
Žemiau pateikiama formulė, kuri suteiks didžiausią duomenų rinkinio vertę:
= DIDELIS ($ B $ 2: $ B $ 16,1)
Panašiai antrą didžiausią vertę suteiks
= DIDELIS ($ B $ 2: $ B $ 16,1)
Jei nenaudojate „Microsoft 365“, kurioje yra dinaminiai masyvai, galite naudoti toliau pateiktą formulę ir ji suteiks jums penkias didžiausias duomenų rinkinio vertes, naudojant vieną formulę:
= DIDELIS ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Panašiai, jei norite mažiausių 5 verčių, naudokite šią formulę:
= MAŽAS ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
arba, jei neturite dinaminių masyvų:
= MAŽAS ($ B $ 2: $ B $ 16,1)
Kai turėsite šias vertes, tikrai nesunku sužinoti visus duomenų rinkinio nukrypimus.
Nors aš nusprendžiau išgauti didžiausią ir mažiausią 5 reikšmes, galite pasirinkti gauti 7 arba 10, atsižvelgdami į tai, koks yra jūsų duomenų rinkinys.
Nesu tikras, ar tai priimtinas metodas, norint rasti „Excel“ nukrypimus, ar ne, tačiau būtent šį metodą naudojau, kai prieš keletą metų savo darbe turėjau dirbti su daugybe finansinių duomenų. Palyginus su visais kitais šioje pamokoje aprašytais metodais, man pasirodė, kad šis metodas yra efektyviausias.
Kaip teisingai elgtis su pašaliniais
Iki šiol matėme metodus, kurie padės rasti duomenų rinkinio nukrypimus. Bet ką daryti, kai žinai, kad yra nukrypimų.
Štai keli metodai, kuriuos galite naudoti, kad pašalintumėte reikšmes, kad jūsų duomenų analizė būtų teisinga.
Ištrinkite „Outliers“
Lengviausias būdas pašalinti nukrypimus nuo duomenų rinkinio yra tiesiog juos ištrinti. Tokiu būdu tai neiškraipys jūsų analizės.
Tai yra perspektyvesnis sprendimas, kai turite didelius duomenų rinkinius ir ištrynus porą pašalinių rodiklių neturės įtakos bendrai analizei. Ir, žinoma, prieš ištrindami duomenis būtinai sukurkite kopiją ir pasidomėkite, kas sukelia šiuos nukrypimus.
Normalizuokite nukrypimus (koreguokite vertę)
Normalizuodamas nuokrypius, aš dariau, kai dirbau visą darbo dieną. Visoms išimtinėms vertėms aš tiesiog pakeisčiau jas į vertę, kuri yra šiek tiek didesnė už maksimalią duomenų rinkinio vertę.
Tai užtikrino, kad aš neištrinu duomenų, bet tuo pačiu neleidžiu jiems iškreipti mano duomenų.
Jei norite pateikti realaus gyvenimo pavyzdį, jei analizuojate įmonių grynąjį pelną, kai dauguma įmonių yra nuo -10%iki 30%, ir yra keletas verčių, kurios yra didesnės nei 100%, tiesiog pakeistų šias neįprastas vertes į 30% arba 35%.
Taigi tai yra keletas metodų, kuriuos galite naudoti „Excel“, kad sužinotumėte nukrypimus.
Nustačius nukrypimus, galite įsigilinti į duomenis ir išsiaiškinti, kas juos sukelia, ir tuo pačiu pasirinkti vieną iš metodų, kaip elgtis su šiomis nuokrypiais (tai gali būti pašalinta arba normalizuojama, pakoregavus vertę)
Tikiuosi, kad ši pamoka jums buvo naudinga.
