Hur man använder Outliers-funktionen i Excel

När du analyserar data kan extremvärden förvränga resultaten och göra dem mindre tillförlitliga. Till exempel, om ett eller flera av dina värden är avsevärt högre än majoriteten av data, gör de medelvärdet högre, vilket kanske inte speglar data som helhet. Att ta bort dessa utanförliggande datapunkter är därför ett viktigt steg för att utföra tillförlitliga statistiska beräkningar. Utan någon dedikerad extremvärdesfunktion är det enklaste sättet att utföra ett extremvärdestest i Excel att använda interkvartilintervall för att identifiera extremvärden, men du kan också använda Trimmean-funktionen för att uppskatta detsamma resultat.

Det interkvartila dataintervallet är det område som täcks av "rutan" på en ruta-och-morrhår-plot, eller mer exakt, resultatet av att subtrahera värdet för den första kvartilen av data från den tredje kvartil. Excel innehåller en inbyggd funktion för att beräkna valfri kvartil för dina data. Hitta en reservcell och skriv "=KVARTIL([dataintervall], [kvartiltal])" och skriv cellintervallet för din data där det står "[dataintervall]" och kvartilen du vill ha där det står "[kvartil siffra]."

Till exempel, om du har data i cellerna A2 till A101 och du vill ha värdet för den första kvartilen, anger du "=KVARTIL(A2:A101, 1)" för att hitta värdet. För den första delen av argumentet kan du markera relevanta celler med musen, men efter kommatecken måste du skriva numret på den kvartil du vill ha. För den tredje kvartilen med samma data skriver du "=KVARTIL(A2:A101, 3)" för att få resultatet.

Använd en annan tom cell och subtrahera värdet på den första kvartilcellen från värdet på den tredje kvartilcellen. Om den första kvartilen finns i cell C2 och den tredje kvartilen är i cell D2, skriv "=D2-C2" för att få resultatet. Detta är det interkvartila området.

För att hitta extremvärden kan du nu använda interkvartilintervallet i extremvärdesformeln, som anger att den övre gränsen för data är värdet på tredje kvartilen plus 1,5 gånger interkvartilen, och den nedre gränsen är värdet av den första kvartilen minus 1,5 gånger interkvartilen räckvidd.

Om det första kvartilvärdet är i C2, är det tredje kvartilvärdet i cell D2 och interkvartilområdet är i cell E2 skulle du skriva "=C2-(1,5 * E2)" för att hitta den nedre gränsen och "=D2+(1,5 * E2)" för att hitta den övre begränsa. I allmänhet anger du "=[första kvartilen] – (1,5 * [interkvartilintervall])" för att hitta den nedre gränsen och "=[tredje kvartilen] + (1,5 * [interkvartilintervall])" för att hitta den övre gränsen.

Allt under den nedre gränsen eller över den övre gränsen är en outlier.

För att avsluta avvikelsetestet i Excel, använd den logiska "ELLER"-funktionen för att identifiera vilka värden i din dataklass som är extremvärden på ett effektivt sätt. Ange "=ELLER([datacell]>[övre gräns], [datacell]$F$2, A2

Om värdet i A2 är över den övre gränsen eller under den nedre gränsen, visar den "TRUE", vilket indikerar att värdet är ett extremvärde. Du kan dra ner den här formeln genom att klicka i det nedre högra hörnet av cellen med formeln och dra den nedåt så att den avslutas bredvid den slutliga datacellen för att utföra samma beräkning på var och en datapunkt.

Du kan också markera data och gå till "Villkorlig formatering" i avsnittet "Stiler" på fliken "Hem" om du vill ändra formateringen för extremvärdena. Välj "Ny regel" och markera alternativet "Använd en formel för att bestämma vilka celler som ska formateras". Skriv samma formel som i föregående stycke och klicka sedan på "Format" alternativet för att välja den unika formateringen för extremvärden.

Funktionen "Trimmean" är ett enklare sätt att identifiera extremvärden. Skriv "=TRIMMEAN([dataintervall], [proportion to trim])" för att använda funktionen, med cellintervallet som innehåller data istället för "[dataintervall]" och en decimal procent du vill trimma där det står "[proportion to trim]." Detta tar bort extremvärdena i toppen och botten och beräknar sedan medelvärdet baserat på dessa återstående. Så, om du trimmade 10 procent, skulle det ta bort de översta 5 procenten och de nedersta 5 procenten innan du beräknade medelvärdet.

Om data går från A2 till A101 och du vill trimma de extrema 5 procenten av värdena, anger du "=TRIMMEAN(A2:A101, 0,05)" för att hitta det justerade medelvärdet. Du kan trimma 15 procent genom att skriva "=TRIMMEAN(A2:A101, 0,15)" istället.