När du arbetar med data i Excel har du ofta problem med att hantera outliers i din datamängd.
Att ha outliers är ganska vanligt i alla typer av data, och det är viktigt att identifiera och behandla dessa outliers för att se till att din analys är korrekt och mer meningsfull.
I den här självstudien visar jag dig hur man hittar outliers i Excel, och några av de tekniker som jag har använt i mitt arbete för att hantera dessa extrema.
Vad är outliers och varför är det viktigt att hitta dessa?
En outlier är en datapunkt som ligger långt bortom de andra datapunkterna i datamängden. När du har en outlier i data kan det skeva dina data vilket kan leda till felaktiga slutsatser.
Låt mig ge dig ett enkelt exempel.
Låt oss säga att 30 personer reser i en buss från destination A till destination B. Alla människor är i en liknande viktgrupp och inkomstgrupp. För syftet med denna handledning, låt oss betrakta genomsnittsvikten till 220 pund och den genomsnittliga årliga inkomsten till 70 000 dollar.
Nu någonstans mitt på vår rutt stannar bussen och Bill Gates hoppar in.
Vad tror du att detta skulle göra med medelvikten och medelinkomsten för folket på bussen?
Även om medelvikten sannolikt inte kommer att förändras så mycket, kommer medelinkomsten för personerna på bussen att skjuta i höjden kraftigt.
Det beror på att Bill Gates inkomst är en outlier i vår grupp, och det ger oss en felaktig tolkning av uppgifterna. Medelinkomsten för varje person på bussen skulle vara några miljarder dollar, vilket är långt bortom det verkliga värdet.
När du arbetar med faktiska datamängder i Excel kan du ha avvikare i vilken riktning som helst (dvs. en positiv avvikelse eller en negativ avvikelse).
Och för att säkerställa att din analys är korrekt måste du på något sätt identifiera dessa avvikelser och sedan bestämma hur du bäst behandlar dem.
Låt oss nu se ett par sätt att hitta outliers i Excel.
Hitta outliers genom att sortera data
Med små datamängder är ett snabbt sätt att identifiera avvikare att helt enkelt sortera data och manuellt gå igenom några av värdena högst upp i denna sorterade data.
Och eftersom det kan finnas avvikelser i båda riktningarna, se till att du först sorterar data i stigande ordning och sedan i fallande ordning och sedan går igenom toppvärdena.
Låt mig visa dig ett exempel.
Nedan har jag en datauppsättning där jag har samtalstider (i sekunder) för 15 kundtjänstsamtal.
Nedan följer stegen för att sortera dessa data så att vi kan identifiera outliers i datamängden:
- Välj kolumnrubriken i den kolumn du vill sortera (cell B1 i det här exemplet)
- Klicka på fliken Hem
- Klicka på ikonen Sortera och filtrera i gruppen Redigering.
- Klicka på Anpassad sortering
- I dialogrutan Sortera väljer du "Varaktighet" i rullgardinsmenyn Sortera efter och "Störst till minsta" i listrutan Beställning
- Klicka på Ok
Stegen ovan skulle sortera samtalskolumnen med de högsta värdena överst. Nu kan du manuellt skanna data och se om det finns några avvikelser.
I vårt exempel kan jag se att de två första värdena är mycket högre än resten av värdena (och de två nedre är långt lägre).
Obs! Denna metod fungerar med små datamängder där du kan skanna data manuellt. Det är ingen vetenskaplig metod men fungerar bra
Hitta outliers med hjälp av kvartilfunktionerna
Låt oss nu prata om en mer vetenskaplig lösning som kan hjälpa dig att identifiera om det finns några avvikelser eller inte.
I statistiken är en kvartil en fjärdedel av datamängden. Om du till exempel har 12 datapunkter är den första kvartilen de tre nedre datapunkterna, den andra kvartilen är de tre följande datapunkterna och så vidare.
Nedan är datauppsättningen där jag vill hitta outliers. För att göra detta måste jag beräkna den första och den tredje kvartilen, och sedan använda den för att beräkna den övre och den nedre gränsen.
Nedan är formeln för att beräkna den första kvartilen i cell E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
och här är den som beräknar den tredje kvartilen i cell E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Nu kan jag använda ovanstående två beräkningar för att få Interquartile Range (vilket är 50% av våra data inom 1: a och 3: e kvartilen)
= F3-F2
Nu kommer vi att använda interkvartilintervallet för att hitta den nedre och övre gränsen som skulle innehålla de flesta av våra data.
Allt som ligger utanför dessa nedre och övre gränser skulle då betraktas som extrema.
Nedan är formeln för att beräkna den nedre gränsen:
= Kvartil1 - 1,5*(Interkvartilintervall)
som i vårt exempel blir:
= F2-1,5*F4
Och formeln för att beräkna den övre gränsen är:
= Quartile3 + 1,5*(Inter Quartile Range)
som i vårt exempel blir:
= F3+1,5*F4
Nu när vi har den övre och nedre gränsen i vår datamängd kan vi gå tillbaka till de ursprungliga data och snabbt identifiera de värden som inte ligger inom detta intervall.
Ett snabbt sätt att göra detta skulle vara att kontrollera varje värde och returnera ett SANT eller FALSKT i en ny kolumn.
Jag har använt nedanstående OR -formel för att få SANT för de värden som är avvikande.
= ELLER (B2 $ F $ 6)
Nu kan du filtrera kolumnen Outlier och bara visa de poster där värdet är SANT.
Alternativt kan du också använda villkorlig formatering för att markera alla celler där värdet är SANT
Notera: Även om detta är en mer accepterad metod för att hitta avvikelser i statistik. Jag tycker att denna metod är lite oanvändbar i verkliga scenarier. I exemplet ovan är den nedre gränsen beräknad med formeln -103, medan datauppsättningen vi bara kan vara positiv. Så den här metoden kan hjälpa oss att hitta avvikelser i ena riktningen (höga värden), det är värdelöst att identifiera avvikare i den andra riktningen.Hitta outliers Använd de stora/små funktionerna
Om du arbetar med mycket data (värden i flera kolumner) kan du extrahera de största och de minsta 5 eller 7 värdena och se om det finns några avvikelser i den.
Om det finns några avvikelser kommer du att kunna identifiera dem utan att behöva gå igenom all data i båda riktningarna.
Anta att vi har nedanstående dataset och vi vill veta om det finns några avvikelser.
Nedan är formeln som ger dig det största värdet i datamängden:
= STOR ($ B $ 2: $ B $ 16,1)
På samma sätt kommer det näst största värdet att ges av
= STOR ($ B $ 2: $ B $ 16,1)
Om du inte använder Microsoft 365, som har dynamiska matriser, kan du använda formeln nedan och det ger dig de fem största värdena från datamängden med en enda formel:
= STOR ($ B $ 2: $ B $ 16, RAD ($ 1: 5))
På samma sätt, om du vill ha de minsta 5 värdena, använd följande formel:
= LITEN ($ B $ 2: $ B $ 16, RAD ($ 1: 5))
eller följande om du inte har dynamiska matriser:
= LITEN ($ B $ 2: $ B $ 16,1)
När du väl har dessa värden är det väldigt enkelt att ta reda på eventuella avvikelser i datamängden.
Medan jag har valt att extrahera de största och minsta 5 värdena, kan du välja att få 7 eller 10 baserat på hur stor din dataset är.
Jag är inte säker på om det här är en acceptabel metod för att hitta outliers i Excel eller inte, men det här är den metod som jag använde när jag fick arbeta med mycket ekonomisk data i mitt jobb för några år sedan. Jämfört med alla andra metoder som omfattas av denna handledning, tyckte jag att den här var den mest effektiva.
Hur man hanterar outliers på rätt sätt
Hittills har vi sett metoderna som hjälper oss att hitta avvikelserna i vår datamängd. Men vad ska man göra när man vet att det finns outliers.
Här är ett par metoder som du kan använda för att hantera outliers så att din dataanalys är korrekt.
Ta bort Outliers
Det enklaste sättet att ta bort outliers från din datamängd är att helt enkelt radera dem. På så sätt snedvrider det inte din analys.
Det är en mer livskraftig lösning när du har stora datamängder och att radera ett par avvikelser inte påverkar den övergripande analysen. Och naturligtvis, innan du tar bort data, se till att du skapar en kopia och fördjupar dig i vad som orsakar dessa outliers.
Normalisera outliers (justera värdet)
Att normalisera outliers är vad jag brukade göra när jag var på mitt heltidsjobb. För alla outlier -värden skulle jag helt enkelt ändra dem till ett värde som är något högre än maxvärdet i datamängden.
Detta såg till att jag inte tar bort data men samtidigt låter jag det inte snedvrida mina data.
För att ge dig ett verkligt exempel, om du analyserar nettovinstmarginalen för företag, där de flesta företagen ligger inom -10%till 30%, och det finns ett par värden som är uppåt till 100%, jag skulle helt enkelt ändra dessa outlier -värden till 30% eller 35%.
Så det här är några av de metoder som du kan använda i Excel för att hitta outliers.
När du har identifierat avvikelserna kan du fördjupa dig i data och leta efter vad som orsakar dessa, samtidigt välja en av teknikerna för att hantera dessa avvikare (vilket kan vara att ta bort dessa eller normalisera dessa genom att justera värdet)
Jag hoppas att du fann denna handledning användbar.
