Hvordan (og hvorfor) at bruge Outliers-funktionen i Excel
En outlier er en værdi, som er betydeligt højere eller lavere end de fleste af værdierne i dine data. Når du bruger Excel til at analysere data, kan outliers skævre resultaterne. For eksempel kan gennemsnittet af et datasæt virkelig afspejle dine værdier. Excel giver nogle nyttige funktioner til at hjælpe med at håndtere dine outliers, så lad os tage et kig.
Et hurtigt eksempel
I billedet nedenfor er outliersne rimeligt let at få øje på - værdien af to tildelt til Eric og værdien af 173 tildelt til Ryan. I et datasæt som dette er det nemt nok at få øje på og håndtere disse afvigere manuelt.
I et større sæt data vil det ikke være tilfældet. At kunne identificere outliers og fjerne dem fra statistiske beregninger er vigtigt - og det er det, vi vil se på, hvordan man gør i denne artikel.
Sådan Find Outliers i dine data
For at finde outliers i et datasæt bruger vi følgende trin:
- Beregn 1. og 3. kvartil (vi taler om, hvad de er i bare en smule).
- Vurder interkvartilstanden (vi vil også forklare disse lidt længere nede).
- Returner de øverste og nederste grænser af vores dataregment.
- Brug disse grænser til at identificere de yderste datapunkter.
Celleområdet til højre for datasættet, der ses i billedet nedenfor, bruges til at gemme disse værdier.
Lad os komme igang.
Trin 1: Beregn kvartilerne
Hvis du deler dine data i kvartaler, kaldes hver af disse sæt et kvartil. Den laveste 25% af numrene i intervallet udgør 1. kvartil, den næste 25% den 2. kvartil og så videre. 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 at beregne kvartiler. Det kræver to stykker information: array og quart.
= QUARTILE (array, quart)
Det matrix er den række af værdier, som du vurderer. Og Quart er et tal, der repræsenterer det kvartil, du ønsker at vende tilbage (fx 1 for 1st kvartil, 2 for 2. kvartil og så videre).
Bemærk: I Excel 2010 frigjente 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 eksempeltabel.
At beregne 1st Kvartil kan vi bruge følgende formel i celle F2.
= QUARTILE (B2: B14,1)
Når du indtaster formlen, giver Excel en liste over muligheder for quart-argumentet.
At beregne 3rd kvartil, kan vi indtaste en formel som den forrige i celle F3, men bruge en tre i stedet for en.
= QUARTILE (B2: B14,3)
Nu har vi de kvartil-datapunkter, der vises i cellerne.
Trin to: Evaluer Interquartile Range
Interkvartilstanden (eller IQR) er den midterste 50% af værdierne i dine data. Det beregnes som forskellen mellem den 1. kvartilværdi og den 3. kvartilværdi.
Vi skal bruge en simpel formel til celle F4, der trækker 1 frast kvartil fra 3rd kvartil:
= F3-F2
Nu kan vi se vores interkvartile rækkevidde vises.
Trin tre: Ret de nedre og øvre grænser
De nederste og øvre grænser er de mindste og største værdier af det datarråde, som vi vil bruge. Eventuelle værdier, der er mindre eller større end disse bundne værdier, er afvigelserne.
Vi beregner grænsen for 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 beregner 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øje det til Q3 datapunktet:
= F3 + (1,5 * F4)
Trin fire: Identificer Outliers
Nu hvor vi har alle vores underliggende data oprettet, er det tid til at identificere vores fjernliggende datapunkter - dem der er lavere end den nedre grænse værdi eller højere end den øvre grænse værdi.
Vi bruger OR-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:
= OR (B2 $ F $ 6)
Vi kopierer derefter denne værdi til vores C3-C14-celler. En TRUE værdi angiver en outlier, og som du kan se, har vi to i vores data.
Ignorerer outliers ved beregning af gennemsnittet
Ved hjælp af QUARTILE-funktionen lad os beregne IQR og arbejde med den mest anvendte definition af en outlier. Men når man beregner middelgennemsnittet for en række værdier og ignorerer outliers, er der en hurtigere og lettere funktion at bruge. Denne teknik vil ikke identificere en outlier som før, men det vil gøre det muligt for os at være fleksible med, hvad vi måske overvejer vores outlier-del.
Funktionen vi har brug for hedder TRIMMEAN, og du kan se syntaxen til det nedenfor:
= TRIMMEAN (array, procent)
Det matrix er den række af værdier, du vil have gennemsnittet. Det procent er procentdelen af datapunkter udelukket fra toppen og bunden af datasættet (du kan indtaste det som en procent eller en decimalværdi).
Vi indtastede nedenstående formel i celle D3 i vores eksempel for at beregne gennemsnittet og udelukke 20% af outliers.
= TRIMMEAN (B2: B14, 20%)
Der har du to forskellige funktioner til håndtering af outliers. Uanset om du vil identificere dem for nogle rapporteringsbehov eller udelukke dem fra beregninger som gennemsnit, har Excel en funktion, der passer til dine behov.
