Hva er uteliggere og hvordan håndtere dem i Excel?
En uteligger er en verdi som skiller seg markant fra de fleste andre verdiene i et datasett. Når man analyserer data i Excel, kan slike avvik påvirke resultatene og gi et misvisende bilde. For eksempel kan gjennomsnittet av et datasett bli forvrengt av uteliggere. Heldigvis tilbyr Excel flere praktiske verktøy for å håndtere disse avvikende verdiene. La oss se nærmere på hvordan vi kan identifisere og håndtere dem.
Et enkelt eksempel
I det følgende eksemplet er uteliggerne ganske lette å oppdage. Vi ser at Eric har verdien 2, mens Ryan har verdien 173. I et slikt lite datasett er det enkelt å finne og behandle disse manuelt.
Men når vi jobber med større datasett, blir det vanskeligere å identifisere uteliggere manuelt. Derfor er det viktig å kunne identifisere dem og ekskludere dem fra statistiske beregninger. Denne artikkelen viser deg hvordan du går frem.
Hvordan identifisere uteliggere i data
Følgende trinn brukes for å finne uteliggere i et datasett:
- Beregn første og tredje kvartil (vi kommer tilbake til hva dette er).
- Bestem interkvartilområdet (dette forklarer vi også nærmere).
- Finn øvre og nedre grenser for dataområdet.
- Bruk disse grensene for å identifisere de avvikende datapunktene.
I eksemplet nedenfor skal vi bruke celleområdet til høyre for datasettet for å lagre de nødvendige verdiene.
La oss starte med det første trinnet.
Trinn 1: Beregn kvartilene
Når vi deler dataene inn i fire like store deler, kalles hver av disse kvartiler. De laveste 25 % av verdiene utgjør første kvartil, de neste 25 % utgjør andre kvartil, og så videre. Vi beregner kvartilene først fordi en vanlig definisjon på en uteligger er et datapunkt som ligger mer enn 1,5 ganger interkvartilområdet (IQR) under første kvartil, eller 1,5 ganger IQR over tredje kvartil. For å finne disse verdiene må vi først finne kvartilene.
Excel har en funksjon som heter KVARTIL for å beregne kvartiler. Den krever to argumenter: datamatrisen og hvilken kvartil vi ønsker å finne.
=KVARTIL(matrise, kvart)
Matrisen angir dataområdet som vi evaluerer. Kvart-argumentet er et tall som representerer hvilken kvartil vi ønsker å finne (for eksempel 1 for første kvartil, 2 for andre kvartil osv.).
Merk: I Excel 2010 introduserte Microsoft funksjonene KVARTIL.INK og KVARTIL.EKS som forbedringer av KVARTIL-funksjonen. KVARTIL er mer bakoverkompatibel når du jobber på tvers av ulike versjoner av Excel.
La oss gå tilbake til eksempeltabellen vår.
For å beregne første kvartil kan vi bruke følgende formel i celle F2:
=KVARTIL(B2:B14,1)
Når du begynner å skrive inn formelen, vil Excel foreslå alternativer for kvart-argumentet.
For å beregne tredje kvartil kan vi skrive inn en lignende formel i celle F3, men vi bruker 3 i stedet for 1 som kvart-argument.
=KVARTIL(B2:B14,3)
Nå har vi kvartildataene i de angitte cellene.
Trinn 2: Beregn interkvartilområdet
Interkvartilområdet (IQR) representerer de midterste 50 % av verdiene i datasettet. Det beregnes som differansen mellom tredje og første kvartil.
Vi skal bruke en enkel formel i celle F4, som trekker første kvartil fra tredje kvartil:
=F3-F2
Nå ser vi interkvartilområdet.
Trinn 3: Bestem nedre og øvre grense
De nedre og øvre grensene er de minste og største verdiene i dataområdet som vi skal bruke. Alle verdier som er lavere eller høyere enn disse grensene, vil bli ansett som uteliggere.
Vi beregner den nedre grensen i celle F5 ved å multiplisere IQR-verdien med 1,5 og deretter trekke dette fra første kvartil:
=F2-(1.5*F4)
Merk: Parentesene i denne formelen er ikke strengt nødvendig da multiplikasjonen vil utføres før subtraksjonen, men de gjør formelen lettere å lese.
For å beregne den øvre grensen i celle F6, multipliserer vi IQR med 1,5 igjen, men denne gangen legger vi det til tredje kvartil:
=F3+(1.5*F4)
Trinn 4: Identifiser uteliggerne
Nå som vi har all den nødvendige informasjonen, er det på tide å identifisere de ytre datapunktene – de som er lavere enn den nedre grenseverdien eller høyere enn den øvre grenseverdien.
Vi vil bruke ELLER-funksjonen for å utføre denne logiske testen og vise verdiene som oppfyller disse kriteriene. Skriv inn følgende formel i celle C2:
=ELLER(B2$F$6)
Kopier deretter denne formelen nedover til cellene C3-C14. En SANN-verdi indikerer en uteligger, og vi ser at det er to slike i datasettet.
Ignorere uteliggere ved beregning av gjennomsnitt
Ved å bruke KVARTIL-funksjonen kan vi beregne IQR og jobbe med den mest brukte definisjonen av en uteligger. Men når man skal beregne gjennomsnittet av en rekke verdier og ignorere uteliggere, finnes det en raskere og enklere funksjon. Denne metoden identifiserer ikke uteliggere som i det forrige eksemplet, men den lar oss være fleksible med hva vi betrakter som avvikende verdier.
Funksjonen vi trenger heter TRIMMEAN. Nedenfor ser du syntaksen:
=TRIMMEAN(matrise, prosent)
Matrisen er dataområdet vi vil beregne gjennomsnittet for. Prosenten er prosentandelen av datapunkter som skal ekskluderes fra toppen og bunnen av datasettet (angis som en prosent eller desimalverdi).
I vårt eksempel har vi skrevet inn følgende formel i celle D3 for å beregne gjennomsnittet og ekskludere 20 % av uteliggerne:
=TRIMMEAN(B2:B14, 20%)
Dette gir deg to ulike funksjoner for å håndtere uteliggere. Enten du ønsker å identifisere dem for rapporteringsformål, eller ekskludere dem fra beregninger som gjennomsnitt, tilbyr Excel funksjoner som passer dine behov.