Kuinka löytää poikkeamia Excelistä (ja miten käsitellä niitä)

Kun käsittelet tietoja Excelissä, sinulla on usein ongelmia tietojoukon poikkeamien käsittelyssä.

Poikkeamat ovat yleisiä kaikenlaisissa tiedoissa, ja on tärkeää tunnistaa ja käsitellä nämä poikkeamat varmistaaksesi, että analyysi on oikea ja mielekkäämpi.

Tässä opetusohjelmassa näytän sinulle miten löytää outliers Excelistäja joitakin tekniikoita, joita olen käyttänyt työssäni näiden poikkeamien käsittelyyn.

Mitä ovat poikkeamat ja miksi on tärkeää löytää nämä?

Poikkeama on datapiste, joka on kaukana muista tietojoukon datapisteistä. Kun tiedoissa on poikkeama, se voi vääristää tietojasi, mikä voi johtaa virheellisiin johtopäätöksiin.

Annan teille yksinkertaisen esimerkin.

Oletetaan, että 30 ihmistä matkustaa bussilla kohteesta A kohteeseen B. Kaikki ihmiset ovat samanlaisessa painoryhmässä ja tuloryhmässä. Tässä opetusohjelmassa keskimääräinen paino on 220 kiloa ja keskimääräinen vuositulo 70 000 dollaria.

Nyt jonnekin reittimme keskellä bussi pysähtyy ja Bill Gates hyppää sisään.

Mitä luulet tämän tekevän linja -autossa olevien ihmisten keskipainolle ja keskituloille?

Vaikka keskipaino ei todennäköisesti muutu paljon, linja -autossa olevien ihmisten keskitulot nousevat voimakkaasti.

Tämä johtuu siitä, että Bill Gatesin tulot ovat ryhmämme ulkopuolella, ja se antaa meille väärän tulkinnan tiedoista. Keskimääräiset tulot jokaiselle bussissa olevalle henkilölle olisivat muutama miljardi dollaria, mikä on paljon todellista arvoa enemmän.

Kun työskentelet todellisten tietojoukkojen kanssa Excelissä, sinulla voi olla poikkeamia mihin tahansa suuntaan (eli positiivinen tai negatiivinen poikkeama).

Ja varmistaaksesi, että analyysi on oikea, sinun on jotenkin tunnistettava nämä poikkeamat ja päätettävä sitten, miten niitä parhaiten käsitellä.

Katsotaanpa nyt muutamia tapoja löytää poikkeavuuksia Excelissä.

Löydä poikkeavuudet lajittelemalla tiedot

Pienillä tietojoukoilla nopea tapa tunnistaa poikkeamat on yksinkertaisesti lajitella tiedot ja käydä läpi joitakin tämän lajiteltujen tietojen yläosassa olevia arvoja.

Ja koska molempiin suuntiin voi tulla poikkeavuuksia, varmista, että lajittelet tiedot ensin nousevaan järjestykseen ja sitten laskevaan järjestykseen ja käydään sitten läpi arvot.

Annan sinulle esimerkin.

Alla on tietojoukko, jossa minulla on puhelujen kesto (sekunneissa) 15 asiakaspalvelupuhelulle.

Alla on ohjeet tietojen lajitteluun, jotta voimme tunnistaa tietojoukon poikkeamat:

1. Valitse lajiteltavan sarakkeen sarakeotsikko (solu B1 tässä esimerkissä)
2. Napsauta Etusivu -välilehteä
3. Napsauta Muokkaus -ryhmässä Lajittele ja suodata -kuvaketta.
4. Napsauta Mukautettu lajittelu
5. Valitse Lajittele-valintaikkunassa avattavasta Lajitteluperuste-valikosta "Kesto" ja avattavasta "Tilaa" -kohdasta "Suurimmasta pienimpään".
6. Napsauta OK

Yllä olevat vaiheet lajittelivat puhelun kesto -sarakkeen, jossa on korkeimmat arvot yläreunassa. Nyt voit skannata tiedot manuaalisesti ja nähdä, onko poikkeamia.

Esimerkissämme näen, että kaksi ensimmäistä arvoa ovat paljon korkeammat kuin muut arvot (ja kaksi alinta ovat paljon pienemmät).

Huomautus: Tämä menetelmä toimii pienillä tietojoukoilla, joilla voit skannata tiedot manuaalisesti. Se ei ole tieteellinen menetelmä, mutta toimii hyvin

Poikkeamien löytäminen kvartiilitoimintojen avulla

Puhutaan nyt tieteellisemmästä ratkaisusta, joka voi auttaa sinua tunnistamaan, onko poikkeamia vai ei.

Tilastossa kvartiili on neljäsosa tietojoukosta. Jos sinulla on esimerkiksi 12 datapistettä, ensimmäinen kvartiili on alin kolme datapistettä, toinen kvartiili on seuraavat kolme datapistettä ja niin edelleen.

Alla on tietojoukko, josta haluan löytää poikkeamat. Tätä varten minun on laskettava ensimmäinen ja kolmas kvartiili ja laskettava sitten sen avulla ylä- ja alaraja.

Alla on kaava solun E2 ensimmäisen kvartiilin laskemiseksi:

= QUARTILE.INC ($ B $ 2: $ B $ 15,1)

ja tässä lasketaan solun E3 kolmas kvartiili:

= QUARTILE.INC ($ B $ 2: $ B $ 15,3)

Nyt voin käyttää yllä olevia kahta laskentaa saadakseni interkvartilialueen (joka on 50% datastamme ensimmäisen ja kolmannen kvartiilin sisällä)

= F3-F2

Nyt käytämme kvartiiliväliä löytääksemme ala- ja ylärajan, joka sisältäisi suurimman osan tiedoistamme.

Kaikki, mikä on näiden ala- ja ylärajojen ulkopuolella, katsotaan silloin poikkeaviksi.

Alla on kaava alarajan laskemiseksi:

= Kvartiili1 - 1,5*(neljännesvälinen alue)

josta esimerkissämme tulee:

= F2-1,5*F4

Ja kaava ylärajan laskemiseksi on:

= Kvartiili3 + 1,5*(neljännesvälinen alue)

josta esimerkissämme tulee:

= F3+1,5*F4

Nyt kun tietojoukossamme on ylä- ja alaraja, voimme palata alkuperäisiin tietoihin ja tunnistaa nopeasti ne arvot, jotka eivät ole tällä alueella.

Nopea tapa tehdä tämä olisi tarkistaa jokainen arvo ja palauttaa TOSI tai EPÄTOSI uuteen sarakkeeseen.

Olen käyttänyt alla olevaa TAI -kaavaa saadaksesi TOSI arvoille, jotka ovat poikkeavia.

= TAI (B2 $ F $ 6)

Nyt voit suodattaa Outlier -sarakkeen ja näyttää vain tietueet, joissa arvo on TRUE.

Vaihtoehtoisesti voit myös käyttää ehdollista muotoilua korostaaksesi kaikki solut, joissa arvo on TOSI

merkintä: Vaikka tämä on enemmän hyväksytty tapa löytää outliers tilastoista. Minusta tämä menetelmä on hieman käyttökelvoton tosielämän skenaarioissa. Yllä olevassa esimerkissä kaavan laskema alaraja on -103, kun taas meillä oleva tietojoukko voi olla vain positiivinen. Joten tämä menetelmä voi auttaa meitä löytämään poikkeavuuksia yhteen suuntaan (korkeat arvot), se on hyödytön tunnistettaessa poikkeamia toiseen suuntaan.

Poikkeamien löytäminen LARGE/SMALL -toimintojen avulla

Jos käytät paljon tietoja (arvot useissa sarakkeissa), voit poimia suurimmat ja pienimmät 5 tai 7 arvoa ja tarkistaa, onko siinä poikkeamia.

Jos poikkeamia on, voit tunnistaa ne ilman, että sinun on käytävä läpi kaikki tiedot molempiin suuntiin.

Oletetaan, että meillä on alla oleva tietojoukko ja haluamme tietää, onko poikkeamia.

Alla on kaava, joka antaa sinulle suurimman arvon tietojoukossa:

= SUURI ($ B $ 2: $ B $ 16,1)

Samoin toiseksi suurin arvo annetaan

= SUURI ($ B $ 2: $ B $ 16,1)

Jos et käytä Microsoft 365: tä, jossa on dynaamisia taulukkoja, voit käyttää alla olevaa kaavaa ja se antaa sinulle viisi suurinta arvoa tietojoukosta yhdellä kaavalla:

= SUURI ($ B $ 2: $ B $ 16, ROW ($ 1: 5))

Jos haluat myös pienimmät 5 arvoa, käytä alla olevaa kaavaa:

= PIENI ($ B $ 2: $ B $ 16, ROW ($ 1: 5))

tai jos sinulla ei ole dynaamisia taulukkoja, toimi seuraavasti:

= PIENI ($ B $ 2: $ B $ 16,1)

Kun olet saanut nämä arvot, on todella helppoa selvittää kaikki tietojoukon poikkeamat.

Vaikka olen päättänyt poimia suurimmat ja pienimmät 5 arvoa, voit valita 7 tai 10 sen perusteella, kuinka suuri tietojoukko on.

En ole varma, onko tämä hyväksyttävä tapa löytää poikkeavuuksia Excelistä vai ei, mutta tätä menetelmää käytin silloin, kun minun piti työskennellä paljon taloudellisia tietoja työssäni muutama vuosi sitten. Verrattuna kaikkiin muihin tässä opetusohjelmassa käsiteltyihin menetelmiin, tämä oli mielestäni tehokkain.

Kuinka käsitellä poikkeamia oikein

Toistaiseksi olemme nähneet menetelmiä, jotka auttavat meitä löytämään poikkeamat tietojoukostamme. Mutta mitä tehdä, kun tiedät, että on outliers.

Tässä on pari tapaa, joilla voit käsitellä poikkeamia, jotta tietoanalyysi on oikea.

Poista Outliers

Helpoin tapa poistaa poikkeamat tietojoukostasi on yksinkertaisesti poistaa ne. Näin se ei vääristä analyysiäsi.

Se on kannattavampi ratkaisu, kun sinulla on suuria tietojoukkoja ja parin poikkeaman poistaminen ei vaikuta yleiseen analyysiin. Ja tietenkin, ennen kuin poistat tietoja, varmista, että luot kopion ja tarkastelet, mikä aiheuttaa nämä poikkeamat.

Normalisoi poikkeamat (säädä arvoa)

Poikkeamien normalisointi on se, mitä tein ennen kokopäivätyötäni. Kaikille poikkeaville arvoille muutan ne yksinkertaisesti arvoon, joka on hieman suurempi kuin tietojoukon enimmäisarvo.

Tämä varmisti, että en poista tietoja, mutta samalla en anna sen vääristää tietojani.

Tosielämän esimerkkinä, jos analysoit yritysten nettovoittomarginaalia, jossa suurin osa yrityksistä on -10%: n ja 30%: n sisällä ja pari arvoa on 100%: n yläpuolella, muuttaa vain nämä poikkeavat arvot 30 prosenttiin tai 35 prosenttiin.

Joten nämä ovat joitain menetelmiä, joita voit käyttää Excel löytää outliers.

Kun olet tunnistanut poikkeamat, voit perehtyä tietoihin ja selvittää, mistä nämä johtuvat, ja samalla valita yksi tekniikoista näiden poikkeamien käsittelemiseksi (joka voi poistaa ne tai normalisoida ne säätämällä arvoa)

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.