Kuinka (ja miksi) käyttää poikkeavia toimintoja Excelissä

Poikkeama on arvo, joka on merkittävästi suurempi tai pienempi kuin suurin osa tiedoissasi olevista arvoista. Kun Excelin avulla analysoidaan tietoja, poikkeamat voivat vääristää tuloksia. Esimerkiksi tietojoukon keskiarvo saattaa todella heijastaa arvojasi. Excel tarjoaa muutamia hyödyllisiä toimintoja poikkeavien hallintaan, joten katsotaanpa.

Nopea esimerkki

Alla olevassa kuvassa poikkeamat on kohtuullisen helppo havaita - kahden arvo on osoitettu Ericille ja arvo 173 on annettu Ryanille. Tällaisessa tietojoukossa on riittävän helppo havaita ja käsitellä näitä poikkeamia manuaalisesti.

Suuremmassa tietojoukossa näin ei ole. Pystyminen tunnistamaan poikkeamat ja poistamaan ne tilastolaskelmista on tärkeää - ja sitä me tarkastelemme, miten tässä artikkelissa tehdään.

Kuinka löytää poikkeamat tiedoistasi

Löydämme poikkeamat tietojoukosta seuraavasti:

  1. Laske 1. ja 3. kvartiili (puhumme vain vähän niistä).
  2. Arvioi kvartiilien välinen alue (selitämme myös näitä hieman alaspäin).
  3. Palauta data-alueemme ylä- ja alarajat.
  4. Käytä näitä rajoja tunnistaaksesi syrjäiset datapisteet.

Näiden arvojen tallentamiseen käytetään alla olevassa kuvassa näkyvän tietojoukon oikealla puolella olevaa solualuetta.

Aloitetaan.

Vaihe yksi: Laske kvartilit

Jos jaat tietosi neljänneksi, kutakin näistä ryhmistä kutsutaan kvartileiksi. Alin 25% alueen numeroista muodostaa ensimmäisen kvartiilin, seuraava 25% toisen kvartiilin ja niin edelleen. Otamme tämän vaiheen ensin, koska yleisimmin käytetty poikkeaman määritelmä on datapiste, joka on yli 1,5 kvartiiliväliä (IQR) ensimmäisen kvartiilin alapuolella ja 1,5 kvartiilien välistä aluetta kolmannen kvartiilin yläpuolella. Noiden arvojen määrittämiseksi meidän on ensin selvitettävä, mitkä kvartiilit ovat.

Excel tarjoaa QUARTILE-funktion kvartiilien laskemiseksi. Se vaatii kahta tietoa: matriisi ja kvartsi.

= QUARTILE (matriisi, kvartsi)

Jono on arvoalue arvioit. Ja kvartsi on luku, joka edustaa palautettavaa kvartiilia (esim. 1 ensimmäiselle kvartiilille, 2 toiselle kvartilille ja niin edelleen).

Huomautus: Microsoft julkaisi Excel 2010: ssä QUARTILE.INC- ja QUARTILE.EXC-toiminnot parannuksina QUARTILE-toimintoon. QUARTILE on taaksepäin yhteensopiva, kun työskentelet useiden Excel-versioiden kanssa.

Palataan esimerkkitaulukkoomme.

Ensimmäisen kvartiilin laskemiseksi voimme käyttää seuraavaa kaavaa solussa F2.

= NELJÄNNES (B2: B14,1)

Kun kirjoitat kaavaa, Excel tarjoaa luettelon vaihtoehdoista quart-argumentille.

Kolmannen kvartiilin laskemiseksi voimme syöttää edellisen kaltaisen kaavan soluun F3, mutta käyttämällä kolikkoa yhden sijasta.

= NELJÄNNES (B2: B14,3)

Nyt meillä on kvartiilidatapisteet näytetty soluissa.

Vaihe 2: Arvioi kvartiilien välinen alue

Kvartiilien välinen alue (tai IQR) on keskimääräinen 50% arvoista tiedoissa. Se lasketaan ensimmäisen kvartiiliarvon ja kolmannen kvartiiliarvon erona.

Aiomme käyttää yksinkertaista kaavaa soluun F4, joka vähentää ensimmäisen kvartiilin kolmannesta kvartiilista:

= F3-F2

Nyt voimme nähdä kvartiilien välisen alueemme näkyvissä.

Kolmas vaihe: Palauta ala- ja ylärajat

Ala- ja ylärajat ovat pienimmät ja suurimmat tietoalueen arvot, joita haluamme käyttää. Kaikki nämä sidotut arvot pienemmät tai suuremmat arvot ovat poikkeavuuksia.

Laskemme solun F5 alarajarajan kertomalla IQR-arvon 1,5: llä ja vähentämällä sen sitten Q1-datapisteestä:

= F2- (1,5 * F4)

Huomaa: Tämän kaavan sulkeet eivät ole välttämättömiä, koska kertolasku lasketaan ennen vähennysosaa, mutta ne helpottavat kaavan lukemista.

Laskettaessa solun F6 yläraja kerrotaan IQR uudelleen 1,5: llä, mutta tällä kertaa lisätään se Q3-datapisteeseen:

= F3 + (1,5 * F4)

Neljäs vaihe: Tunnista poikkeamat

Nyt kun kaikki taustalla olevat tietomme on määritetty, on aika tunnistaa syrjäisimmät datapisteet - ne, jotka ovat alempaa raja-arvoa pienempiä tai korkeammat kuin yläraja-arvo.

Käytämme OR-toimintoa tämän loogisen testin suorittamiseen ja näytämme arvot, jotka täyttävät nämä ehdot, syöttämällä seuraavan kaavan soluun C2:

= TAI (B2 $ F $ 6)

Kopioimme sitten arvon C3-C14-soluihimme. TOSI-arvo ilmaisee poikkeaman, ja kuten näette, meillä on kaksi tietoistamme.

Ohitetaan poikkeamat laskettaessa keskimääräistä keskiarvoa

Käyttämällä QUARTILE-funktiota lasketaan IQR ja työskennellään yleisimmin käytetyn poikkeaman määritelmän kanssa. Laskettaessa arvojen keskiarvon keskiarvoa ja jättämällä huomiotta poikkeamat, toiminto on nopeampaa ja helpompaa käyttää. Tämä tekniikka ei tunnista syrjäytyneitä kuin aikaisemmin, mutta se antaa meille mahdollisuuden olla joustavia sen suhteen, mitä voimme pitää ulkopuolisena osuutena.

Tarvittavaa toimintoa kutsutaan TRIMMEANiksi, ja näet sen syntaksin alla:

= TRIMMEAN (taulukko, prosenttia)

Jono on arvoalueella haluat keskimäärin. Prosentti on prosenttiosuus datapisteiden jättää ylä- ja alaosassa datajoukon (voit kirjoittaa sen prosentteina tai desimaaliarvona).

Lisäsimme alla olevan kaavan esimerkkimme soluun D3 laskeaksemme keskiarvon ja poissulkemaan 20% poikkeamista.

= TRIMMEAN (B2: B14, 20%)

Siellä sinulla on kaksi erilaista toimintoa poikkeavien käsittelyyn. Halusitpa tunnistaa ne joillekin raportointitarpeille tai sulkea ne pois laskelmista, kuten keskiarvot, Excelillä on tarpeisiisi sopiva toiminto.