Kako (in zakaj) uporabljati funkcijo Outliers v Excelu

Outlier je vrednost, ki je znatno višja ali nižja od večine vrednosti v podatkih. Ko Excel uporablja za analizo podatkov, lahko outliers izkrivljajo rezultate. Na primer, povprečje podatkovnega niza lahko resnično odraža vaše vrednosti. Excel ponuja nekaj uporabnih funkcij, ki vam pomagajo pri upravljanju vaših odstopanj, zato poglejmo.

Hitri primer

Na spodnji sliki je opaziti, da so outliersi zlahka opazni - vrednost dveh, ki sta dodeljeni Ericu, in vrednost 173, dodeljeno Ryanu. V podatkovnem nizu, kot je ta, je dovolj preprosto, da se ta odstopanja ročno opazijo in obravnavajo.

V večjem nizu podatkov to ne bo tako. Pomembno je, da lahko identificiramo odstopanja in jih odstranimo iz statističnih izračunov - in to bomo preučili v tem članku..

Kako najti outliers v svojih podatkih

Da bi našli outliers v nizu podatkov, uporabljamo naslednje korake:

Izračunajte 1. in 3. kvartil (govorili bomo o tem, kaj so le malo).
Ocenite interkvartilni razpon (to bomo pojasnili še nekoliko navzdol).
Vrnite zgornjo in spodnjo mejo našega podatkovnega območja.
Uporabite te meje za identifikacijo oddaljenih podatkovnih točk.

Območje celic na desni strani podatkovnega niza, prikazanega na spodnji sliki, bo uporabljeno za shranjevanje teh vrednosti.

Začnimo.

Prvi korak: Izračunajte kvartile

Če podatke razdelite na četrtine, se vsak od teh nizov imenuje kvartil. Najnižji 25% številk v razponu predstavlja 1. kvartil, naslednjih 25% 2. kvartil in tako naprej. Ta korak najprej vzamemo, ker je najbolj razširjena definicija outlierja podatkovna točka, ki je več kot 1,5 interkvartilnih razponov (IQR) pod 1. kvartilom, in 1,5 interkvartilnih razponov nad 3. kvartilom. Za določitev teh vrednot moramo najprej ugotoviti, kaj so kvartili.

Excel ponuja funkcijo QUARTILE za izračun kvartilov. Zahteva dva podatka: matriko in quart.

= QUARTILE (matrika, quart)

The matrika je obseg vrednosti, ki jih ocenjujete. In quart je številka, ki predstavlja kvartil, ki ga želite vrniti (npr. 1 za 1^st kvartil, 2 za 2. kvartil in tako naprej).

Opomba: V Excelu 2010 je Microsoft izdal funkcije QUARTILE.INC in QUARTILE.EXC kot izboljšave funkcije QUARTILE. QUARTILE je bolj kompatibilen pri delu v več različicah Excela.

Vrnimo se k naši primerni tabeli.

Za izračun 1^st Kvartil lahko uporabimo naslednjo formulo v celici F2.

= QUARTILE (B2: B14,1)

Ko vnesete formulo, Excel ponuja seznam možnosti za argument quart.

Za izračun 3^rd v kvartilu lahko v celico F3 vnesemo formulo, kot je prejšnja, vendar uporabimo tri namesto ene.

= QUARTILE (B2: B14,3)

Zdaj imamo v celicah prikazane kvartilne podatkovne točke.

Drugi korak: Ocenite interkvartilni razpon

Medkvartilni razpon (ali IQR) je srednji 50% vrednosti v vaših podatkih. Izračuna se kot razlika med prvo četrtino in tretjo kvartilno vrednostjo.

Uporabili bomo preprosto formulo v celici F4, ki odšteva 1^st kvartil od 3. \ t^rd kvartil:

= F3-F2

Sedaj lahko vidimo prikazan naš medkvartilni razpon.

Tretji korak: Vrnite spodnjo in zgornjo mejo

Spodnja in zgornja meja sta najmanjši in največji vrednosti podatkovnega obsega, ki ga želimo uporabiti. Vse vrednosti, ki so manjše ali večje od teh vezanih vrednosti, so izstopajoči.

Izračunamo spodnjo mejo v celici F5 tako, da pomnožimo vrednost IQR s 1,5 in jo nato odštejemo od podatkovne točke Q1:

= F2- (1,5 * F4)

Opomba: Oklepaji v tej formuli niso potrebni, ker bo del množenja izračunan pred delom odštevanja, vendar bo formula lažje brati.

Da bi izračunali zgornjo mejo v celici F6, bomo IQR pomnožili z 1,5, vendar tokrat add na podatkovno točko Q3:

= F3 + (1,5 * F4)

Četrti korak: Identificirajte outliers

Zdaj, ko imamo vse naštete podatke, je čas, da ugotovimo naše oddaljene podatkovne točke - tiste, ki so nižje od vrednosti spodnje meje ali višje od zgornje vrednosti..

Za izvedbo tega logičnega testa bomo uporabili funkcijo ALI in prikazali vrednosti, ki ustrezajo tem merilom, tako da v celico C2 vnesete naslednjo formulo:

= ALI (B2 $ F $ 6)

Nato bomo to vrednost kopirali v naše C3-C14 celice. Vrednost TRUE označuje outlier in kot lahko vidite, imamo dva v naših podatkih.

Ignoriranje outliersja pri izračunu povprečne vrednosti

Z uporabo funkcije QUARTILE izračunamo IQR in delamo z najbolj razširjeno definicijo outlier-ja. Vendar pa pri izračunavanju povprečja povprečij za vrsto vrednosti in ignoriranje outliers, je hitrejša in lažja funkcija za uporabo. Ta tehnika ne bo identificirala izbruha kot prej, vendar pa nam bo omogočila, da smo prilagodljivi s tistim, kar bi lahko obravnavali naš zunanji del.

Funkcija, ki jo potrebujemo, se imenuje TRIMMEAN in spodaj lahko vidite njeno sintakso:

= TRIMMEAN (matrika, odstotki)

The matrika je obseg vrednosti, ki jih želite povprečiti. The odstotkov je odstotek podatkovnih točk, ki jih je treba izključiti z vrha in dna niza podatkov (lahko ga vnesete kot odstotek ali decimalno vrednost).

Spodnjo formulo smo vnesli v celico D3 v našem primeru, da izračunamo povprečje in izključimo 20% odstopanj.

= TRIMMEAN (B2: B14, 20%)

Na voljo imate dve različni funkciji za ravnanje z outliers. Ne glede na to, ali jih želite prepoznati za nekatere potrebe poročanja ali jih izključiti iz izračunov, kot so povprečja, ima Excel funkcijo, ki ustreza vašim potrebam.