Når du arbejder med data i Excel, har du ofte spørgsmålene om håndtering af ekstreme værdier i dit datasæt.
At have outliers er ret almindeligt i alle former for data, og det er vigtigt at identificere og behandle disse outliers for at sikre, at din analyse er korrekt og mere meningsfuld.
I denne vejledning viser jeg dig hvordan man finder outliers i Excel, og nogle af de teknikker, som jeg har brugt i mit arbejde til at håndtere disse udfald.
Hvad er outliers, og hvorfor er det vigtigt at finde disse?
En outlier er et datapunkt, der ligger langt ud over de andre datapunkter i datasættet. Når du har en outlier i dataene, kan det skæve dine data, hvilket kan føre til forkerte slutninger.
Lad mig give dig et enkelt eksempel.
Lad os sige, at 30 mennesker rejser i en bus fra destination A til destination B. Alle mennesker er i en lignende vægtgruppe og indkomstgruppe. Med henblik på denne vejledning, lad os overveje den gennemsnitlige vægt til at være 220 pund og den gennemsnitlige årlige indkomst til at være $ 70.000.
Nu et sted midt på vores rute stopper bussen, og Bill Gates hopper ind.
Hvad tror du nu, det ville gøre for gennemsnitsvægten og den gennemsnitlige indkomst for folk på bussen.
Selvom gennemsnitsvægten sandsynligvis ikke ændrer sig meget, vil den gennemsnitlige indkomst for folk på bussen skyde kraftigt i vejret.
Det er fordi Bill Gates indkomst er en outlier i vores gruppe, og det giver os en forkert fortolkning af dataene. Den gennemsnitlige indkomst for hver person i bussen ville være et par milliarder dollars, hvilket er langt ud over den faktiske værdi.
Når du arbejder med faktiske datasæt i Excel, kan du have outliers i enhver retning (dvs. en positiv outlier eller en negativ outlier).
Og for at sikre, at din analyse er korrekt, er du på en eller anden måde nødt til at identificere disse outliers og derefter beslutte, hvordan du bedst behandler dem.
Lad os nu se et par måder at finde outliers i Excel.
Find outliers ved at sortere dataene
Med små datasæt er en hurtig måde at identificere outliers på blot at sortere dataene og manuelt gennemgå nogle af værdierne øverst i disse sorterede data.
Og da der kan være afvigelser i begge retninger, skal du først sortere dataene i stigende rækkefølge og derefter i faldende rækkefølge og derefter gå gennem topværdierne.
Lad mig vise dig et eksempel.
Nedenfor har jeg et datasæt, hvor jeg har opkaldstider (i sekunder) til 15 opkald til kundeservice.
Nedenfor er trinene til at sortere disse data, så vi kan identificere outliers i datasættet:
- Vælg kolonneoverskriften i den kolonne, du vil sortere (celle B1 i dette eksempel)
- Klik på fanen Startside
- Klik på ikonet Sorter og filtrer i gruppen Redigering.
- Klik på Custom Sort
- I dialogboksen Sorter skal du vælge 'Varighed' i rullelisten Sorter efter og 'Størst til mindste' i rullemenuen Ordre
- Klik på Ok
Ovenstående trin sorterer opkaldsvarighedskolonnen med de højeste værdier øverst. Nu kan du manuelt scanne dataene og se, om der er nogle outliers.
I vores eksempel kan jeg se, at de to første værdier er langt højere end resten af værdierne (og de nederste to er langt lavere).
Bemærk: Denne metode fungerer med små datasæt, hvor du manuelt kan scanne dataene. Det er ikke en videnskabelig metode, men fungerer godt
Find udgangsværdier ved hjælp af kvartilfunktionerne
Lad os nu tale om en mere videnskabelig løsning, der kan hjælpe dig med at identificere, om der er udsving eller ej.
I statistik er en kvartil en fjerdedel af datasættet. For eksempel, hvis du har 12 datapunkter, så ville den første kvartil være de tre nederste datapunkter, den anden kvartil ville være de næste tre datapunkter osv.
Nedenfor er datasættet, hvor jeg vil finde outliers. For at gøre dette bliver jeg nødt til at beregne den første og den tredje kvartil, og derefter bruge den til at beregne den øvre og den nedre grænse.
Nedenfor er formlen til beregning af den første kvartil i celle E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
og her er den, der skal beregne den tredje kvartil i celle E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Nu kan jeg bruge de ovennævnte to beregninger til at få Interquartile Range (som er 50% af vores data inden for 1. og 3. kvartil)
= F3-F2
Nu vil vi bruge interkvartilområdet til at finde den nedre og øvre grænse, som ville indeholde de fleste af vores data.
Alt, hvad der er uden for disse nedre og øvre grænser, vil da blive betragtet som ekstreme.
Nedenfor er formlen til beregning af den nedre grænse:
= Kvartil1 - 1,5*(Interkvartilområde)
som i vores eksempel bliver til:
= F2-1,5*F4
Og formlen til beregning af den øvre grænse er:
= Quartile3 + 1,5*(Inter Quartile Range)
som i vores eksempel bliver til:
= F3+1,5*F4
Nu hvor vi har den øvre og nedre grænse i vores datasæt, kan vi gå tilbage til de originale data og hurtigt identificere de værdier, der ikke ligger i dette område.
En hurtig måde at gøre dette på ville være at kontrollere hver værdi og returnere en TRUE eller FALSE i en ny kolonne.
Jeg har brugt nedenstående OR -formel til at få SAND for de værdier, der er outliers.
= ELLER (B2 $ F $ 6)
Nu kan du filtrere kolonnen Outlier og kun vise de poster, hvor værdien er SAND.
Alternativt kan du også bruge betinget formatering til at markere alle de celler, hvor værdien er SAND
Bemærk: Selvom dette er en mere accepteret metode til at finde outliers i statistik. Jeg finder denne metode lidt ubrugelig i virkelige scenarier. I ovenstående eksempel er den nedre grænse beregnet med formlen -103, mens det datasæt, vi har, kun kan være positivt. Så denne metode kan hjælpe os med at finde outliers i den ene retning (høje værdier), det er ubrugeligt at identificere outliers i den anden retning.Find udgangsværdierne ved hjælp af LARGE/SMALL -funktionerne
Hvis du arbejder med en masse data (værdier i flere kolonner), kan du udtrække de største og de mindste 5 eller 7 værdier og se, om der er nogle ekstreme værdier i det.
Hvis der er nogle outliers, vil du være i stand til at identificere dem uden at skulle gå igennem alle dataene i begge retninger.
Antag, at vi har nedenstående datasæt, og vi vil gerne vide, om der er nogle outliers.
Nedenfor er formlen, der giver dig den største værdi i datasættet:
= STOR ($ B $ 2: $ B $ 16,1)
Tilsvarende vil den næststørste værdi blive givet af
= STOR ($ B $ 2: $ B $ 16,1)
Hvis du ikke bruger Microsoft 365, som har dynamiske arrays, kan du bruge nedenstående formel, og det vil give dig de fem største værdier fra datasættet med en enkelt formel:
= STOR ($ B $ 2: $ B $ 16, RÆK ($ 1: 5))
På samme måde, hvis du vil have de mindste 5 værdier, skal du bruge nedenstående formel:
= LILLE ($ B $ 2: $ B $ 16, Række ($ 1: 5))
eller følgende, hvis du ikke har dynamiske arrays:
= LILLE ($ B $ 2: $ B $ 16,1)
Når du har disse værdier, er det virkelig let at finde ud af eventuelle afvigelser i datasættet.
Selvom jeg har valgt at udtrække de største og mindste 5 værdier, kan du vælge at få 7 eller 10 baseret på, hvor stort dit datasæt er.
Jeg er ikke sikker på, om dette er en acceptabel metode til at finde outliers i Excel eller ej, men det er den metode, jeg brugte, da jeg for nogle år siden skulle arbejde med mange økonomiske data i mit job. Sammenlignet med alle de andre metoder, der er dækket i denne vejledning, fandt jeg, at denne var den mest effektive.
Sådan håndteres udliggere den rigtige måde
Hidtil har vi set de metoder, der vil hjælpe os med at finde de ekstreme i vores datasæt. Men hvad skal man gøre, når man ved, at der er ekstreme.
Her er et par metoder, du kan bruge til at håndtere outliers, så din dataanalyse er korrekt.
Slet Outliers
Den nemmeste måde at fjerne outliers fra dit datasæt er blot at slette dem. På denne måde vil det ikke skæve din analyse.
Det er en mere levedygtig løsning, når du har store datasæt, og sletning af et par outliers vil ikke påvirke den samlede analyse. Og selvfølgelig, før du sletter dataene, skal du sørge for at oprette en kopi og dykke ned i, hvad der forårsager disse udsving.
Normaliser outliers (juster værdien)
Normalisering af outliers er det, jeg plejede at gøre, da jeg var i mit fuldtidsjob. For alle outlier -værdierne ville jeg simpelthen ændre dem til en værdi, der er lidt højere end den maksimale værdi i datasættet.
Dette sørgede for, at jeg ikke sletter dataene, men samtidig lader jeg det ikke skæve mine data.
For at give dig et eksempel fra det virkelige liv, hvis du analyserer nettoresultatmargenen for virksomheder, hvor de fleste af virksomhederne ligger inden for -10%til 30%, og der er et par værdier, der er opad på 100%, I ville blot ændre disse outlier -værdier til 30% eller 35%.
Så det er nogle af de metoder, du kan bruge i Excel for at finde outliers.
Når du har identificeret outliers, kan du dykke ned i dataene og lede efter, hvad der forårsager disse, samtidig vælge en af teknikkerne til at håndtere disse outliers (som kan være at fjerne disse eller normalisere disse ved at justere værdien)
Jeg håber, at du fandt denne vejledning nyttig.
