Hvordan (og hvorfor) å bruke Outliers-funksjonen i Excel

En outlier er en verdi som er betydelig høyere eller lavere enn de fleste verdiene i dataene dine. Når du bruker Excel til å analysere data, kan utjevningene skje resultatene. For eksempel kan gjennomsnittlig gjennomsnitt av et datasett virkelig gjenspeile dine verdier. Excel gir noen nyttige funksjoner for å hjelpe deg med å håndtere dine outliers, så la oss ta en titt.

Et raskt eksempel

I bildet nedenfor er utjevningene rimelig enkle å få øye på - verdien av to tildelt Eric og verdien av 173 tildelt Ryan. I et datasett som dette, er det enkelt nok til å oppdage og håndtere disse avvikene manuelt.

I et større sett med data vil det ikke være tilfelle. Å kunne identifisere utjevnene og fjerne dem fra statistiske beregninger er viktig - og det er hva vi skal se på hvordan du gjør i denne artikkelen.

Hvordan finne Outliers i dataene dine

For å finne avvikene i et datasett bruker vi følgende trinn:

1. Beregn 1. og 3. kvartil (vi snakker om hva de er i bare litt).
2. Evaluer interkvartileområdet (vi vil også forklare disse litt lenger nede).
3. Returner de øvre og nedre grensene av vårt datafelt.
4. Bruk disse grensene til å identifisere de ytre datapunktene.

Celleområdet til høyre for datasettet sett i bildet nedenfor vil bli brukt til å lagre disse verdiene.

La oss komme i gang.

Trinn ett: Beregn kvartilene

Hvis du deler dataene dine i kvartaler, blir hvert av disse settene kalt en kvartil. Den laveste 25% av tallene i området utgjør 1. kvartal, den neste 25% den andre kvartilen, og så videre. Vi tar dette trinnet først, fordi den mest brukte definisjonen av en outlier er et datapunkt som er mer enn 1,5 interkvartile intervall (IQR) under det første kvartalet, og 1,5 interkvartile områder over det tredje kvartilet. For å bestemme disse verdiene må vi først finne ut hva kvartilene er.

Excel gir en QUARTILE-funksjon for å beregne kvartiler. Det krever to deler av informasjon: matrisen og kvartalet.

= QUARTILE (array, quart)

De matrise er omfanget av verdier som du vurderer. Og Quart er et tall som representerer kvartilen du ønsker å returnere (for eksempel 1 for 1^st kvartil, 2 for 2. kvartil, og så videre).

Merk: I Excel 2010 lanserte Microsoft QUARTILE.INC og QUARTILE.EXC funksjoner som forbedringer til QUARTILE-funksjonen. QUARTILE er mer bakoverkompatibel når du arbeider på tvers av flere versjoner av Excel.

La oss gå tilbake til vårt eksempeltabell.

For å beregne 1^st Kvartil kan vi bruke følgende formel i celle F2.

= Kvartil (B2: B14,1)

Når du skriver inn formelen, gir Excel en liste over alternativer for quart-argumentet.

For å beregne 3^rd kvartil, kan vi skrive inn en formel som den forrige i celle F3, men bruke en tre i stedet for en.

= Kvartil (B2: B14,3)

Nå har vi kvartildatapunkter som vises i cellene.

Trinn to: Evaluer Interquartile Range

Interkvartilstanden (eller IQR) er den midterste 50% av verdiene i dataene dine. Det beregnes som forskjellen mellom den første kvartilverdien og den tredje kvartilverdien.

Vi skal bruke en enkel formel i celle F4 som subtraherer 1^st kvartil fra 3^rd kvartil:

= F3-F2

Nå kan vi se vårt interkvartile utvalg vises.

Trinn tre: Gå tilbake til nedre og øvre kant

Den nedre og øvre grensen er de minste og største verdiene av datarommet som vi vil bruke. Eventuelle verdier som er mindre eller større enn disse bundet verdier er utjevnene.

Vi beregner nedre grense grensen i celle F5 ved å multiplisere IQR verdien med 1,5 og deretter trekke den fra Q1 datapunktet:

= F2 (1,5 * F4)

Merk: Brakettene i denne formelen er ikke nødvendige fordi multiplikasjonsdelen vil beregne før subtraksjonen, men de gjør formelen enklere å lese.

For å beregne den øvre grensen i celle F6, vil vi multiplisere IQR med 1,5 igjen, men denne gangen Legg til det til Q3 datapunktet:

= F3 + (1,5 * F4)

Trinn fire: Identifiser Outliers

Nå som vi har alle våre underliggende data satt opp, er det på tide å identifisere de ytre datapunktene våre - de som er lavere enn den nedre grenseverdien eller høyere enn den øvre grenseverdien.

Vi bruker OR-funksjonen til å utføre denne logiske testen og vise verdiene som oppfyller disse kriteriene ved å skrive inn følgende formel i celle C2:

= ELLER (B2 $ F $ 6)

Vi kopierer da den verdien til våre C3-C14-celler. En sann verdi indikerer en outlier, og som du kan se har vi to i våre data.

Ignorerer utjevnene når du beregner gjennomsnittet

Ved hjelp av QUARTILE-funksjonen, la oss beregne IQR og arbeide med den mest brukte definisjonen av en outlier. Men når du beregner gjennomsnittet for en rekke verdier og ignorerer utjevnene, er det en raskere og enklere funksjon å bruke. Denne teknikken vil ikke identifisere en outlier som før, men det vil tillate oss å være fleksible med hva vi kan vurdere vår outlier-delen.

Funksjonen vi trenger kalles TRIMMEAN, og du kan se syntaksen for det nedenfor:

= TRIMMEAN (array, prosent)

De matrise er rekkevidden av verdier du vil gjennomsnittlig. De prosent er prosentandelen av datapunkter å ekskludere fra toppen og bunnen av datasettet (du kan skrive det som en prosentandel eller en desimalverdi).

Vi skrev inn formelen under i celle D3 i vårt eksempel for å beregne gjennomsnittet og utelukke 20% av utjevningene.

= TRIMMEAN (B2: B14, 20%)

---

Der har du to forskjellige funksjoner for å håndtere utliggere. Enten du vil identifisere dem for noen rapporteringsbehov eller ekskludere dem fra beregninger som gjennomsnitt, har Excel en funksjon som passer dine behov.