Hur (och varför) man använder Outliers-funktionen i Excel

En extremvärde är ett värde som är betydligt högre eller lägre än de flesta av värdena i din data. När du använder Excel för att analysera data kan extremvärden förvränga resultaten. Till exempel kan medelsnittet för en datamängd verkligen återspegla dina värden. Excel innehåller några användbara funktioner för att hantera dina extremvärden, så låt oss ta en titt.

Ett snabbt exempel

I bilden nedan är extremvärdena ganska lätta att upptäcka – värdet av två tilldelade till Eric och värdet av 173 tilldelat till Ryan. I en datamängd som denna är det lätt nog att upptäcka och hantera dessa extremvärden manuellt.

I en större uppsättning data kommer det inte att vara fallet. Att kunna identifiera extremvärdena 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.

Hur man hittar extremvärden i din data

För att hitta extremvärden i en datamängd använder vi följande steg:

Beräkna den 1:a och 3:e kvartilen (vi ska prata om vad de är om en liten stund).
Utvärdera interkvartilintervallet (vi kommer också att förklara dessa lite längre ner).
Returnera de övre och nedre gränserna för vårt dataintervall.
Använd dessa gränser för att identifiera de avlägsna datapunkterna.

Cellintervallet till höger om datamängden som visas i bilden nedan kommer att användas för att lagra dessa värden.

Låt oss börja.

Steg ett: Beräkna kvartilerna

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

Excel tillhandahåller en KVARTIL-funktion för att beräkna kvartiler. Det kräver två delar av information: arrayen och quart.

=QUARTILE(array, quart)

Arrayen är intervallet av värden som du utvärderar. Och kvartilen är ett tal som representerar kvartilen du vill returnera (t.ex. 1 för den första kvartilen, 2 för den andra kvartilen, och så vidare).

Obs! I Excel 2010 släppte Microsoft funktionerna QUARTILE.INC och QUARTILE.EXC som förbättringar av QUARTILE-funktionen. QUARTILE är mer bakåtkompatibel när du arbetar med flera versioner av Excel.

Låt oss återgå till vår exempeltabell.

För att beräkna den 1:a kvartilen kan vi använda följande formel i cell F2.

=QUARTILE(B2:B14,1)

När du anger formeln ger Excel en lista med alternativ för quart-argumentet.

För att beräkna den 3:e kvartilen kan vi ange en formel som den föregående i cell F3, men med en trea istället för en etta.

=QUARTILE(B2:B14,3)

Nu har vi kvartildatapunkterna som visas i cellerna.

Steg två: Utvärdera det interkvartila intervallet

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

Vi kommer att använda en enkel formel i cell F4 som subtraherar den 1:a kvartilen från den 3:e kvartilen:

=F3-F2

Nu kan vi se vårt interkvartilintervall visas.

Steg tre: Returnera den nedre och övre gränsen

De nedre och övre gränserna är de minsta och största värdena i dataintervallet som vi vill använda. Alla värden som är mindre eller större än dessa bundna värden är extremvärden.

Vi kommer att beräkna 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)

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

För att beräkna den övre gränsen i cell F6, multiplicerar vi IQR med 1,5 igen, men den här gången lägger vi till den till Q3-datapunkten:

=F3+(1.5*F4)

Steg fyra: Identifiera extremvärdena

Nu när vi har ställt in alla våra underliggande data, är det dags att identifiera våra externa datapunkter – de som är lägre än det nedre gränsvärdet eller högre än det övre gränsvärdet.

Vi kommer att använda ELLER-funktion 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 kopierar sedan det värdet till våra C3-C14-celler. Ett TRUE-värde indikerar en extremvärde, och som du kan se har vi två i vår data.

Ignorera extremvärdena vid beräkning av medelgenomsnittet

Med QUARTILE-funktionen kan vi beräkna IQR och arbeta med den mest använda definitionen av en extremvärde. Men när man beräknar medelmedelvärdet för ett intervall av värden och ignorerar extremvärden, finns det en snabbare och enklare funktion att använda. Den här tekniken kommer inte att identifiera en extremitet som tidigare, men den kommer att tillåta oss att vara flexibla med vad vi kan betrakta som vår extrema del.

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

=TRIMMEAN(array, percent)

Matrisen är intervallet av värden som du vill beräkna medelvärdet för. Procenten är procentandelen datapunkter som ska uteslutas från toppen och botten av datamängden (du kan ange det som en procentandel eller ett decimalvärde).

Vi skrev in formeln nedan i cell D3 i vårt exempel för att beräkna medelvärdet och exkludera 20 % av extremvärdena.

=TRIMMEAN(B2:B14, 20%)

Där har du två olika funktioner för att hantera extremvärden. 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.