Hur (och varför) att använda Outliers-funktionen i Excel

En outlier är ett värde som är signifikant högre eller lägre än de flesta värdena i dina data. När Excel används för att analysera data kan utjämnare skryta resultaten. Till exempel kan medelvärdet av en dataset verkligen återspegla dina värden. Excel ger några användbara funktioner som hjälper dig att hantera dina outliers, så låt oss ta en titt.

Ett snabbt exempel

I bilden nedan är avvikarna rimligt lätta att upptäcka - värdet av två tilldelade Eric och värdet på 173 tilldelade Ryan. I en datasuppsättning som denna är det lätt att upptäcka och hantera dessa avvikare manuellt.

I en större uppsättning data kommer det inte att vara fallet. Att kunna identifiera outliersna och ta bort dem från statistiska beräkningar är viktigt - och det är vad vi ska titta på hur man gör i den här artikeln.

Så här hittar du Outliers i dina data

För att hitta outliers i en dataset använder vi följande steg:

1. Beräkna 1: a och 3: e kvartilerna (vi talar om vad de är i bara lite).
2. Utvärdera interkvartilintervallet (vi kommer också att förklara dessa lite längre ner).
3. Returnera övre och nedre gränserna för vårt dataområde.
4. Använd dessa gränser för att identifiera de avlägsna datapunkterna.

Cellområdet till höger om datasatsen som ses i bilden nedan kommer att användas för att lagra dessa värden.

Låt oss börja.

Steg 1: Beräkna kvartilerna

Om du delar dina data i kvartaler, kallas var och en av dessa uppsättningar en kvartil. Den lägsta 25% av siffrorna i intervallet utgör 1: a kvartilen, nästa 25% 2: a kvartilen, och så vidare. Vi tar detta steg först eftersom den mest använda definitionen av en outlier är en datapunkt som är mer än 1,5 interkvartilintervall (IQR) under 1: a kvartilen och 1,5 interkvartilintervall över 3: e kvartilen. För att bestämma dessa värden måste vi först ta reda på vad kvartilerna är.

Excel ger en QUARTILE-funktion för att beräkna kvartiler. Det kräver två delar av informationen: matrisen och kvartalen.

= QUARTILE (array, quart)

De array är det värdeområde som du utvärderar. Och den quart är ett tal som representerar kvartilen du vill återvända (t ex 1 för 1^st kvartil, 2 för 2: a kvartilen, och så vidare).

Notera: I Excel 2010 släppte Microsoft QUARTILE.INC och QUARTILE.EXC funktioner som förbättringar av QUARTILE-funktionen. QUARTILE är mer bakåtkompatibel när du arbetar över flera versioner av Excel.

Låt oss återvända till vårt exempelbord.

För att beräkna 1^st Quartile kan vi använda följande formel i cell F2.

= KVARTIL (B2: B14,1)

När du anger formeln ger Excel en lista över alternativ för kvartalsargumentet.

Att beräkna 3^rd kvartil, kan vi skriva in en formel som den föregående i cell F3, men använda en tre istället för en.

= KVARTIL (B2: B14,3)

Nu har vi kvartildatapunkterna som visas i cellerna.

Steg två: Utvärdera Interquartile Range

Interkvartilintervallet (eller IQR) är den mesta 50% av värdena i dina data. Det beräknas som skillnaden mellan 1: a kvartilvärdet och 3: e kvartilvärdet.

Vi ska använda en enkel formel i cell F4 som subtraherar 1^st kvartil från 3^rd kvartilen:

= F3-F2

Nu kan vi se vårt interkvartilintervall visas.

Steg tre: Återgå nedre och övre gränsen

De lägre och övre gränserna är de minsta och största värdena för det datasort som vi vill använda. Eventuella värden som är mindre eller större än dessa bundna värden är utjämnare.

Vi beräknar gränsen för den nedre gränsen i cell F5 genom att multiplicera IQR-värdet med 1,5 och sedan subtrahera det från Q1-datapunkten:

= F2- (1,5 * F4)

Notera: Häftarna i denna formel är inte nödvändiga eftersom multiplikationsdelen kommer att beräkna före subtraktionsdelen, men de gör formeln lättare att läsa.

För att beräkna övre gränsen i cell F6 multiplicerar vi IQR med 1,5 igen, men den här gången Lägg till det till Q3 datapunkt:

= F3 + (1,5 * F4)

Steg fyra: Identifiera Outliers

Nu när vi har all vår underliggande data upprättad, är det dags att identifiera våra avlägsna datapunkter-de som är lägre än det nedre gränsvärdet eller högre än det övre gränsvärdet.

Vi använder OR-funktionen för att utföra detta logiska test och visa de värden som uppfyller dessa kriterier genom att ange följande formel i cell C2:

= OR (B2 $ F $ 6)

Vi ska sedan kopiera det värdet till våra C3-C14-celler. Ett TRUE-värde anger en outlier, och som du kan se har vi två i våra data.

Ignorera utjämnare vid beräkning av medelvärdet

Med hjälp av QUARTILE-funktionen kan vi beräkna IQR och arbeta med den mest använda definitionen av en outlier. Men när man beräknar medelvärdet för ett antal värden och ignorerar utjämnare, finns det en snabbare och enklare funktion att använda. Denna teknik kommer inte att identifiera en outlier som tidigare, men det kommer att tillåta oss att vara flexibla med vad vi kan överväga vår outlier del.

Funktionen vi behöver kallas TRIMMEAN, och du kan se syntaxen för den nedan:

= TRIMMEAN (array, procent)

De array är det värdeområde du vill ha i genomsnitt. De procent är procentandelen av datapunkter att uteslutas från datasetets övre och nedre del (du kan ange det som procenttal eller ett decimalvärde).

Vi inmatade formeln nedan i cell D3 i vårt exempel för att beräkna medelvärdet och utesluta 20% av utjämnare.

= TRIMMEAN (B2: B14, 20%)

---

Där har du två olika funktioner för hantering av outliers. Oavsett om du vill identifiera dem för vissa rapporteringsbehov eller utesluta dem från beräkningar som medelvärden, har Excel en funktion som passar dina behov.