Når du arbeider med data i Excel, har du ofte problemene med å håndtere ekstremer i datasettet ditt.
Å ha outliers er ganske vanlig i alle typer data, og det er viktig å identifisere og behandle disse outliers for å sikre at analysen din er korrekt og mer meningsfull.
I denne opplæringen viser jeg deg hvordan finne utskillere i Excel, og noen av teknikkene som jeg har brukt i arbeidet mitt for å håndtere disse utfallene.
Hva er Outliers og hvorfor er det viktig å finne disse?
En outlier er et datapunkt som ligger langt utover de andre datapunktene i datasettet. Når du har en outlier i dataene, kan det skjeve dataene dine som kan føre til feil slutninger.
La meg gi deg et enkelt eksempel.
La oss si at 30 mennesker reiser i en buss fra destinasjon A til destinasjon B. Alle menneskene er i en lignende vektgruppe og inntektsgruppe. For formålet med denne opplæringen, la oss vurdere gjennomsnittsvekten til 220 pund og den gjennomsnittlige årlige inntekten til $ 70 000.
Nå et sted midt på ruten vår stopper bussen, og Bill Gates hopper inn.
Hva tror du dette ville gjøre med gjennomsnittsvekten og gjennomsnittsinntekten til personene på bussen.
Selv om gjennomsnittsvekten sannsynligvis ikke vil endre seg mye, kommer gjennomsnittsinntekten til personene på bussen til å skyte kraftig i været.
Det er fordi Bill Gates inntekt er en outlier i gruppen vår, og det gir oss en feil tolkning av dataene. Gjennomsnittlig inntekt for hver person på bussen vil være noen få milliarder dollar, noe som er langt utover den faktiske verdien.
Når du arbeider med faktiske datasett i Excel, kan du ha avvik i alle retninger (dvs. en positiv outlier eller en negativ outlier).
Og for å være sikker på at analysen din er riktig, må du på en eller annen måte identifisere disse ekstremene og deretter bestemme hvordan du skal behandle dem best.
La oss nå se et par måter å finne utskillere i Excel.
Finn Outliers ved å sortere dataene
Med små datasett er en rask måte å identifisere utfallere på, bare å sortere dataene og manuelt gå gjennom noen av verdiene øverst i disse sorterte dataene.
Og siden det kan være avvik i begge retninger, må du først sortere dataene i stigende rekkefølge og deretter i synkende rekkefølge og deretter gå gjennom toppverdiene.
La meg vise deg et eksempel.
Nedenfor har jeg et datasett der jeg har samtaletid (i sekunder) for 15 kundeservicesamtaler.
Nedenfor er trinnene for å sortere disse dataene slik at vi kan identifisere ytterpunktene i datasettet:
- Velg kolonneoverskriften i kolonnen du vil sortere (celle B1 i dette eksemplet)
- Klikk på kategorien Hjem
- Klikk på ikonet Sorter og filtrer i redigeringsgruppen.
- Klikk på Egendefinert sortering
- I dialogboksen Sorter velger du 'Varighet' i rullegardinlisten Sorter etter og 'Størst til minste' i rullegardinmenyen Bestilling.
- Klikk Ok
Trinnene ovenfor vil sortere anropsvarighetskolonnen med de høyeste verdiene øverst. Nå kan du manuelt skanne dataene og se om det er noen avvik.
I vårt eksempel kan jeg se at de to første verdiene er langt høyere enn resten av verdiene (og de to nederste er langt lavere).
Merk: Denne metoden fungerer med små datasett der du kan skanne dataene manuelt. Det er ikke en vitenskapelig metode, men fungerer bra
Finne utoverskridelser ved hjelp av kvartilfunksjonene
La oss nå snakke om en mer vitenskapelig løsning som kan hjelpe deg med å identifisere om det er noen avvik eller ikke.
I statistikk er en kvartil en fjerdedel av datasettet. For eksempel, hvis du har 12 datapunkter, vil den første kvartilen være de tre nederste datapunktene, den andre kvartilen vil være de neste tre datapunktene, og så videre.
Nedenfor er datasettet der jeg ønsker å finne utfallene. For å gjøre dette må jeg beregne den første og den tredje kvartilen, og deretter bruke den til å beregne den øvre og nedre grensen.
Nedenfor er formelen for å beregne den første kvartilen i celle E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
og her er den som skal beregne den tredje kvartilen i celle E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Nå kan jeg bruke de to ovennevnte beregningene for å få Interquartile Range (som er 50% av dataene våre i 1. og 3. kvartil)
= F3-F2
Nå vil vi bruke interkvartilområdet for å finne den nedre og øvre grensen som vil inneholde de fleste dataene våre.
Alt som ligger utenfor disse nedre og øvre grensene vil da bli betraktet som ekstreme.
Nedenfor er formelen for å beregne den nedre grensen:
= Kvartil1 - 1,5*(Interkvartilområde)
som i vårt eksempel blir:
= F2-1,5*F4
Og formelen for å beregne den øvre grensen er:
= Quartile3 + 1.5*(Inter Quartile Range)
som i vårt eksempel blir:
= F3+1,5*F4
Nå som vi har øvre og nedre grense i datasettet vårt, kan vi gå tilbake til de opprinnelige dataene og raskt identifisere de verdiene som ikke ligger i dette området.
En rask måte å gjøre dette på er å kontrollere hver verdi og returnere en TRUE eller FALSE i en ny kolonne.
Jeg har brukt OR -formelen nedenfor for å få TRUE for de verdiene som er outliers.
= ELLER (B2 $ F $ 6)
Nå kan du filtrere Outlier -kolonnen og bare vise postene der verdien er SANN.
Alternativt kan du også bruke betinget formatering til å markere alle cellene der verdien er SANN
Merk: Selv om dette er en mer akseptert metode for å finne avvik i statistikk. Jeg synes denne metoden er litt ubrukelig i virkelige scenarier. I eksemplet ovenfor er den nedre grensen beregnet med formelen -103, mens datasettet vi har bare kan være positivt. Så denne metoden kan hjelpe oss med å finne utskillere i den ene retningen (høye verdier), det er ubrukelig å identifisere ekstremer i den andre retningen.Finne de ekstreme ved hjelp av de store/små funksjonene
Hvis du arbeider med mye data (verdier i flere kolonner), kan du trekke ut de største og de minste 5 eller 7 verdiene og se om det er noen avvik i den.
Hvis det er noen avvik, vil du kunne identifisere dem uten å måtte gå gjennom alle dataene i begge retninger.
Anta at vi har datasettet nedenfor, og vi vil vite om det er noen avvik.
Nedenfor er formelen som gir deg den største verdien i datasettet:
= STOR ($ B $ 2: $ B $ 16,1)
På samme måte vil den nest største verdien bli gitt av
= STOR ($ B $ 2: $ B $ 16,1)
Hvis du ikke bruker Microsoft 365, som har dynamiske matriser, kan du bruke formelen nedenfor, og den vil gi deg de fem største verdiene fra datasettet med en enkelt formel:
= STOR ($ B $ 2: $ B $ 16, RAD ($ 1: 5))
På samme måte, hvis du vil ha de minste 5 verdiene, bruker du formelen nedenfor:
= LITEN ($ B $ 2: $ B $ 16, RAD ($ 1: 5))
eller følgende hvis du ikke har dynamiske matriser:
= LITEN ($ B $ 2: $ B $ 16,1)
Når du har disse verdiene, er det veldig enkelt å finne ut av ekstremer i datasettet.
Selv om jeg har valgt å trekke ut de største og minste 5 verdiene, kan du velge å få 7 eller 10 basert på hvor stort datasettet ditt er.
Jeg er ikke sikker på om dette er en akseptabel metode for å finne outliers i Excel eller ikke, men dette er metoden jeg brukte da jeg måtte jobbe med mange økonomiske data i jobben min for noen år siden. Sammenlignet med alle de andre metodene som er dekket i denne opplæringen, fant jeg at denne var den mest effektive.
Slik håndterer du avvikere på riktig måte
Så langt har vi sett metodene som vil hjelpe oss med å finne utoverskridelsene i datasettet vårt. Men hva skal du gjøre når du vet at det er ekstreme.
Her er et par metoder som du kan bruke til å håndtere ekstremer slik at dataanalysen din er korrekt.
Slett Outliers
Den enkleste måten å fjerne ekstremer fra datasettet ditt er å bare slette dem. På denne måten vil det ikke skje analyse.
Det er en mer levedyktig løsning når du har store datasett, og sletting av et par ekstremer vil ikke påvirke den samlede analysen. Og selvfølgelig, før du sletter dataene, må du sørge for at du oppretter en kopi og går nærmere inn på hva som forårsaker disse outliers.
Normaliser outliers (juster verdien)
Det å normalisere de ekstreme er det jeg pleide å gjøre da jeg var i full jobb. For alle outlier -verdiene ville jeg ganske enkelt endre dem til en verdi som er litt høyere enn maksimalverdien i datasettet.
Dette sørget for at jeg ikke sletter dataene, men samtidig lar jeg det ikke skjeve dataene mine.
For å gi deg et eksempel fra det virkelige liv, hvis du analyserer netto fortjenestemargin for selskaper, hvor de fleste selskapene ligger innenfor -10%til 30%, og det er et par verdier som er oppover 100%, jeg ville ganske enkelt endre disse outlier -verdiene til 30% eller 35%.
Så dette er noen av metodene du kan bruke i Excel for å finne utfall.
Når du har identifisert utfallene, kan du fordype deg i dataene og se etter hva som forårsaker disse, samtidig velge en av teknikkene for å håndtere disse utfallene (som kan være å fjerne disse eller normalisere disse ved å justere verdien)
Jeg håper du synes denne opplæringen var nyttig.
