Sådan (og hvorfor) bruges Outliers-funktionen i Excel

En outlier er en værdi, der er signifikant højere eller lavere end de fleste værdier i dine data. Når du bruger Excel til at analysere data, kan outliers skæve resultaterne. For eksempel kan gennemsnittet af et datasæt virkelig afspejle dine værdier. Excel giver et par nyttige funktioner til at hjælpe med at administrere dine outliers, så lad os tage et kig.

Et hurtigt eksempel

På billedet nedenfor er det relativt let at få øje på outliers - værdien af ​​to tildelt Eric og værdien af ​​173 tildelt Ryan. I et datasæt som dette er det let nok at få øje på og håndtere disse afvigelser manuelt.

I et større datasæt vil det ikke være tilfældet. At være i stand til at identificere outliers og fjerne dem fra statistiske beregninger er vigtig - og det er det, vi vil se på, hvordan vi gør i denne artikel.

Sådan finder du outliers i dine data

For at finde outliers i et datasæt bruger vi følgende trin:

  1. Beregn 1. og 3. kvartil (vi taler kun om, hvad det er).
  2. Evaluer interkvartilområdet (vi forklarer dem også lidt længere nede).
  3. Returner den øvre og nedre grænse for vores dataområde.
  4. Brug disse grænser til at identificere de afsidesliggende datapunkter.

Celleområdet til højre for datasættet set i billedet nedenfor bruges til at gemme disse værdier.

Lad os komme igang.

Trin et: Beregn kvartilerne

Hvis du deler dine data i kvartaler, kaldes hvert af disse sæt en kvartil. De laveste 25% af numrene i området udgør 1. kvartil, de næste 25% 2. kvartil osv. Vi tager dette trin først, fordi den mest anvendte definition af en outlier er et datapunkt, der er mere end 1,5 interkvartile områder (IQR'er) under 1. kvartil, og 1,5 interkvartile områder over 3. kvartil. For at bestemme disse værdier skal vi først finde ud af, hvad kvartilerne er.

Excel giver en QUARTILE-funktion til beregning af kvartiler. Det kræver to stykker information: arrayet og kvartet.

= QUARTILE (matrix, kvart)

Den matrix er den række af værdier, som du evaluerer. Og kvarten er et tal, der repræsenterer kvartilen, du ønsker at returnere (f.eks. 1 for 1. kvartil, 2 for 2. kvartil osv.).

Bemærk: I Excel 2010 frigav Microsoft QUARTILE.INC og QUARTILE.EXC-funktionerne som forbedringer af QUARTILE-funktionen. QUARTILE er mere bagudkompatibel, når du arbejder på tværs af flere versioner af Excel.

Lad os vende tilbage til vores eksemplet tabel.

For at beregne 1. kvartil kan vi bruge følgende formel i celle F2.

= KVARTIL (B2: B14,1)

Når du indtaster formlen, giver Excel en liste over muligheder for kvartargumentet.

For at beregne 3. kvartil kan vi indtaste en formel som den forrige i celle F3, men ved hjælp af en tre i stedet for en.

= KVARTIL (B2: B14,3)

Nu har vi kvartildatapunkterne vist i cellerne.

Trin to: Evaluer Interquartile Range

Interkvartilområdet (eller IQR) er de midterste 50% af værdierne i dine data. Det beregnes som forskellen mellem 1. kvartilværdi og 3. kvartilværdi.

Vi skal bruge en simpel formel i celle F4, der trækker 1. kvartil fra 3. kvartil:

= F3-F2

Nu kan vi se vores interkvartile rækkevidde vises.

Trin tre: Returner de nedre og øvre grænser

Den nedre og den øvre grænse er de mindste og største værdier i det dataområde, som vi vil bruge. Eventuelle værdier, der er mindre eller større end disse bundne værdier, er outliers.

Vi beregner den nedre grænse i celle F5 ved at gange IQR-værdien med 1,5 og derefter trække den fra Q1-datapunktet:

= F2- (1,5 * F4)

Bemærk: parenteserne i denne formel er ikke nødvendige, fordi multiplikationsdelen beregnes før subtraktionsdelen, men de gør formlen lettere at læse.

For at beregne den øvre grænse i celle F6 multiplicerer vi IQR med 1,5 igen, men denne gang tilføjes den til Q3-datapunktet:

= F3 + (1,5 * F4)

Trin fire: Identificer outliers

Nu hvor vi har konfigureret alle vores underliggende data, er det tid til at identificere vores eksterne datapunkter - dem, der er lavere end den nedre grænse eller højere end den øvre grænse.

Vi bruger ELLER-funktionen til at udføre denne logiske test og vise de værdier, der opfylder disse kriterier ved at indtaste følgende formel i celle C2:

= ELLER (B2 $ F $ 6)

Vi kopierer derefter denne værdi til vores C3-C14 celler. En SAND værdi angiver en outlier, og som du kan se, har vi to i vores data.

Ignorering af outliers ved beregning af gennemsnittet

Brug QUARTILE-funktionen til at beregne IQR og arbejde med den mest anvendte definition af en outlier. Men når man beregner gennemsnittet for en række værdier og ignorerer outliers, er der en hurtigere og lettere funktion at bruge. Denne teknik identificerer ikke en outlier som før, men den giver os mulighed for at være fleksible med det, vi måske betragter vores outlier-del.

Den funktion, vi har brug for, hedder TRIMMEAN, og du kan se syntaksen for den nedenfor:

= TRIMMEAN (array, procent)

Den matrix er den række af værdier, du ønsker at gennemsnittet. Den procent er den procentdel af datapunkter at udelukke fra toppen og bunden af datasættet (du kan indtaste det som en procentdel eller en decimal værdi).

Vi indtastede nedenstående formel i celle D3 i vores eksempel for at beregne gennemsnittet og ekskludere 20% af outliers.

= TRIMMEAN (B2: B14, 20%)

Der har du to forskellige funktioner til håndtering af outliers. Uanset om du vil identificere dem til nogle rapporteringsbehov eller udelukke dem fra beregninger som gennemsnit, har Excel en funktion, der passer til dine behov.