Kui töötate Excelis andmetega, on teil sageli probleeme andmekogumi kõrvalekallete käsitlemisega.
Kõrvalekalded on igasuguste andmete puhul üsna tavalised ning nende kõrvalekallete kindlakstegemine ja käsitlemine on oluline, et veenduda, et teie analüüs on õige ja sisukam.
Selles õpetuses näitan teile kuidas Excelist kõrvalekaldeid leidaja mõned tehnikad, mida olen oma töös nende kõrvalekallete käsitlemiseks kasutanud.
Mis on kõrvalekalded ja miks on oluline need leida?
Kõrvalekalle on andmepunkt, mis on teistest andmekogumi andmepunktidest kaugemal. Kui teie andmetes on kõrvalekalle, võib see teie andmeid moonutada, mis võib viia valede järeldusteni.
Toon lihtsa näite.
Oletame, et 30 inimest reisivad bussiga sihtkohast A sihtkohta B. Kõik inimesed on sarnases kaalurühmas ja sissetulekugrupis. Selle õpetuse jaoks kaalume, et keskmine kaal on 220 naela ja keskmine aastane sissetulek 70 000 dollarit.
Nüüd kusagil meie marsruudi keskel buss peatub ja Bill Gates hüppab sisse.
Mis te arvate, mida see teeks bussis viibivate inimeste keskmise kaalu ja keskmise sissetulekuga.
Kuigi keskmine kaal tõenäoliselt palju ei muutu, tõuseb bussis viibijate keskmine sissetulek tugevasti.
Selle põhjuseks on asjaolu, et Bill Gatesi sissetulekud on meie rühmas kõrvalekalded ja see annab meile andmetest vale tõlgenduse. Iga bussisolija keskmine sissetulek oleks paar miljardit dollarit, mis ületab tegelikku väärtust.
Excelis tegelike andmekogumitega töötades võivad teil olla kõrvalekaldeid mis tahes suunas (st positiivne kõrvalekalle või negatiivne kõrvalekalle).
Ja veendumaks, et teie analüüs on õige, peate need kõrvalekalded kuidagi tuvastama ja seejärel otsustama, kuidas neid kõige paremini käsitleda.
Vaatame nüüd paari võimalust Exceli väljanägemiste leidmiseks.
Leidke kõrvalekaldeid andmete sorteerimise teel
Väikeste andmekogumite korral on kiire viis kõrvalekallete tuvastamiseks lihtsalt sortida andmed ja käsitsi läbi vaadata mõned selle sorteeritud andmete ülaosas olevad väärtused.
Ja kuna mõlemas suunas võib esineda kõrvalekaldeid, sorteerige andmed esmalt kasvavas järjekorras ja seejärel kahanevas järjekorras ning seejärel vaadake üle tippväärtused.
Näitan teile näidet.
Allpool on mul andmestik, kus mul on kõne kestus (sekundites) 15 klienditeeninduskõne jaoks.
Allpool on toodud sammud nende andmete sortimiseks, et saaksime andmestiku kõrvalekalded tuvastada.
- Valige sortitava veeru veerupäis (selles näites lahter B1)
- Klõpsake vahekaarti Avaleht
- Klõpsake rühmas Redigeerimine ikooni Sorteeri ja filtreeri.
- Klõpsake Kohandatud sortimine
- Valige dialoogiboksis Sortimine rippmenüüst Sortimisalus ja valige Kestus ja rippmenüüst Tellimuse valik Suurim kuni väikseim
- Klõpsake nuppu OK
Ülaltoodud sammud sorteerivad kõne kestuse veeru, mille ülaosas on kõrgeimad väärtused. Nüüd saate andmeid käsitsi skannida ja vaadata, kas on kõrvalekaldeid.
Meie näites näen, et kaks esimest väärtust on ülejäänud väärtustest palju kõrgemad (ja kaks alumist on palju madalamad).
Märkus. See meetod töötab väikeste andmekogumitega, kus saate andmeid käsitsi skannida. See ei ole teaduslik meetod, kuid töötab hästi
Kvartiilifunktsioonide abil kõrvalekallete leidmine
Räägime nüüd teaduslikumast lahendusest, mis aitab teil tuvastada, kas on kõrvalekaldeid või mitte.
Statistikas on kvartiil üks neljandik andmekogumist. Näiteks kui teil on 12 andmepunkti, siis esimene kvartiil oleks kolm alumist andmepunkti, teine kvartiil kolm järgmist andmepunkti jne.
Allpool on andmekogum, kust soovin leida kõrvalekaldeid. Selleks pean arvutama esimese ja kolmanda kvartiili ning seejärel selle abil arvutama ülemise ja alumise piiri.
Allpool on valem lahtri E2 esimese kvartiili arvutamiseks:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
ja siin on üks, mis arvutab lahtris E3 kolmanda kvartiili:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Nüüd saan kasutada ülaltoodud kahte arvutust, et saada interkvartilisvahemik (mis on 50% meie andmetest 1. ja 3. kvartiilis)
= F3-F2
Nüüd kasutame kvartalitevahelist vahemikku, et leida alumine ja ülemine piir, mis sisaldaks enamikku meie andmetest.
Kõik, mis jääb nendest alumistest ja ülemistest piiridest välja, loetakse siis kõrvalekalleteks.
Allpool on valem alumise piiri arvutamiseks:
= Kvartiil1 - 1,5*(kvartiilidevaheline vahemik)
mis meie näites saab:
= F2-1,5*F4
Ja ülemise piiri arvutamise valem on järgmine:
= Kvartiil3 + 1,5*(kvartiilidevaheline vahemik)
mis meie näites saab:
= F3+1,5*F4
Nüüd, kui meil on andmekogumis ülemine ja alumine piir, saame naasta algandmete juurde ja kiiresti tuvastada need väärtused, mis selles vahemikus ei ole.
Kiire viis seda teha oleks kontrollida iga väärtust ja esitada uues veerus tõde või vale.
Olen kasutanud allpool olevat VÕI valemit, et saada tõde nende väärtuste kohta, mis on kõrvalekalded.
= VÕI (B2 $ F $ 6)
Nüüd saate veergu Outlier filtreerida ja näidata ainult kirjeid, kus väärtus on TRUE.
Teise võimalusena võite kasutada ka tingimuslikku vormindamist, et tõsta esile kõik lahtrid, mille väärtus on TRUE
Märge: Kuigi see on rohkem aktsepteeritud meetod statistika kõrvalekallete leidmiseks. Leian, et see meetod on reaalsetes stsenaariumides natuke kasutamiskõlbmatu. Ülaltoodud näites on valemiga arvutatud alumine piir -103, samas kui meie olemasolev andmestik võib olla ainult positiivne. Nii et see meetod aitab meil leida kõrvalekaldeid ühes suunas (kõrged väärtused), see on kasutu teises suunas kõrvalekallete tuvastamisel.Väljundite leidmine funktsioonide LARGE/SMALL abil
Kui töötate paljude andmetega (mitme veeru väärtused), saate välja võtta suurima ja väikseima 5 või 7 väärtuse ja vaadata, kas selles on kõrvalekaldeid.
Kui on kõrvalekaldeid, saate need tuvastada, ilma et peaksite kõiki andmeid mõlemas suunas läbi vaatama.
Oletame, et meil on allpool olev andmekogum ja me tahame teada, kas on mingeid kõrvalekaldeid.
Allpool on valem, mis annab teile andmestiku suurima väärtuse:
= SUUR ($ B $ 2: $ B $ 16,1)
Samamoodi annab suuruselt teine väärtus
= SUUR ($ B $ 2: $ B $ 16,1)
Kui te ei kasuta Microsoft 365, millel on dünaamilised massiivid, saate kasutada järgmist valemit ja see annab teile ühe suurima valemiga andmestiku viis suurimat väärtust:
= SUUR ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Samamoodi, kui soovite väikseimat 5 väärtust, kasutage järgmist valemit:
= VÄIKE ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
või kui teil pole dünaamilisi massiive, toimige järgmiselt.
= VÄIKE ($ B $ 2: $ B $ 16,1)
Kui need väärtused on olemas, on andmestiku kõrvalekaldeid väga lihtne välja selgitada.
Kuigi ma olen valinud suurima ja väikseima 5 väärtuse eraldamise, saate valida, kas saada 7 või 10 selle põhjal, kui suur on teie andmekogum.
Ma ei ole kindel, kas see on vastuvõetav meetod Excelis kõrvalekallete leidmiseks või mitte, kuid seda meetodit kasutasin, kui pidin paar aastat tagasi oma töökohal töötama paljude finantsandmetega. Võrreldes kõigi teiste selles õpetuses käsitletud meetoditega leidsin, et see on kõige tõhusam.
Kuidas võõrväärtustega õigesti toime tulla
Siiani oleme näinud meetodeid, mis aitavad meil leida andmekogumist kõrvalekaldeid. Aga mida teha, kui teate, et on kõrvalekaldeid.
Siin on paar meetodit, mida saate kasutada kõrvalekallete käsitlemiseks, et teie andmete analüüs oleks õige.
Kustutage Outliers
Lihtsaim viis kõrvaliste väärtuste eemaldamiseks andmekogumist on need lihtsalt kustutada. Nii ei moonuta see teie analüüsi.
See on elujõulisem lahendus, kui teil on suured andmekogumid ja paari kõrvalekalde kustutamine ei mõjuta üldist analüüsi. Ja muidugi, enne andmete kustutamist looge kindlasti koopia ja uurige, mis neid kõrvalekaldeid põhjustab.
Normaliseerige kõrvalekalded (reguleerige väärtust)
Väärtuste normaliseerimine on see, mida ma täiskohaga töötades tegin. Kõigi kõrvaliste väärtuste puhul muudaksin need lihtsalt väärtuseks, mis on andmekogumi maksimaalsest väärtusest veidi kõrgem.
See kindlustas, et ma ei kustuta andmeid, kuid samal ajal ei lase see mul oma andmeid moonutada.
Kui tuua teile näide tegelikust elust, kui analüüsite ettevõtete puhaskasumimarginaali, kus enamik ettevõtteid jääb vahemikku -10%kuni 30%ja on paar väärtust, mis ületavad 100%, muudaks need kõrvalekalded lihtsalt 30% või 35%.
Nii et need on mõned meetodid, mida saate kasutada Excel, et leida kõrvalekaldeid.
Kui olete kõrvalekalded tuvastanud, saate andmetesse süveneda ja otsida, mis neid põhjustab, valides samal ajal ühe nende kõrvalekallete käsitlemise meetoditest (milleks võib olla nende kõrvaldamine või nende normaliseerimine väärtuse kohandamisega).
Loodan, et leidsite selle õpetuse kasulikuks.
