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

Hantera Extremvärden i Excel

Ett extremvärde, eller outlier, representerar en datapunkt som avsevärt avviker från merparten av de övriga värdena i en datamängd. När du analyserar data i Excel kan dessa extremvärden snedvrida dina resultat. Till exempel kan genomsnittet av en datamängd bli missvisande på grund av enstaka, väldigt höga eller låga värden. Lyckligtvis erbjuder Excel flera användbara verktyg för att effektivt hantera sådana extremvärden. Låt oss undersöka hur.

Ett Enkelt Exempel

I det illustrerande exemplet nedan är det relativt enkelt att urskilja extremvärden: talet två som tilldelats Eric, och värdet 173 som tilldelats Ryan. I en liten datamängd är det fullt möjligt att manuellt identifiera och hantera sådana extremvärden.

I en större datamängd är det dock inte lika enkelt. Förmågan att identifiera och utesluta extremvärden från statistiska beräkningar är avgörande, och vi ska nu utforska hur man gör det i praktiken.

Hur Man Lokaliserar Extremvärden i Data

För att hitta extremvärden i en given datamängd följer vi dessa steg:

• Beräkna den första och tredje kvartilen (vi återkommer till vad dessa termer innebär).
• Bestäm interkvartilavståndet (även detta kommer vi att definiera).
• Fastställ de övre och nedre gränserna för vårt dataområde.
• Använd dessa gränser för att identifiera de avvikande datapunkterna.

I bilden nedan ser vi ett cellområde till höger om datamängden, där vi kommer att lagra dessa beräknade värden.

Låt oss börja processen.

Steg Ett: Beräkna Kvartilerna

När din data delas upp i fyra lika stora delar kallas varje del för en kvartil. De lägsta 25% av värdena utgör den första kvartilen, de nästkommande 25% den andra kvartilen och så vidare. Vi börjar med detta steg eftersom den vanligaste definitionen av ett extremvärde är en datapunkt som ligger mer än 1,5 gånger interkvartilavståndet (IQR) under den första kvartilen eller 1,5 gånger interkvartilavståndet över den tredje kvartilen. För att hitta dessa värden måste vi först veta vad kvartilerna är.

Excel har en inbyggd funktion, KVARTIL, som beräknar kvartiler. Funktionen tar två argument: datamängden (array) och vilken kvartil som ska returneras (quart).

=KVARTIL(array, quart)

Arrayen är det område med värden som ska analyseras. Kvartilen är en siffra som anger vilken kvartil som ska returneras (t.ex. 1 för den första kvartilen, 2 för den andra osv.).

Notera: I Excel 2010 introducerade Microsoft funktionerna KVARTIL.INKL och KVARTIL.EXKL som förbättringar av KVARTIL-funktionen. KVARTIL-funktionen är dock mer kompatibel med äldre versioner av Excel.

Låt oss återgå till vårt exempel.

För att beräkna den första kvartilen använder vi följande formel i cell F2:

=KVARTIL(B2:B14;1)

När du börjar skriva formeln föreslår Excel olika alternativ för argumentet ”quart”.

För att beräkna den tredje kvartilen anger vi en liknande formel i cell F3, men med en trea istället för en etta:

=KVARTIL(B2:B14;3)

Nu har vi beräknat värdena för kvartilerna som visas i cellerna.

Steg Två: Bestäm Interkvartilavståndet

Interkvartilavståndet (IQR) representerar de mittersta 50% av värdena i din datamängd. Det beräknas som skillnaden mellan den tredje och första kvartilen.

I cell F4 använder vi en enkel formel som subtraherar den första kvartilen från den tredje:

=F3-F2

Nu ser vi interkvartilavståndet i cellen.

Steg Tre: Beräkna Nedre och Övre Gränsen

De nedre och övre gränserna definierar de minsta och största värdena som vi anser vara normala inom dataintervallet. Alla värden som ligger utanför dessa gränser betraktas som extremvärden.

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

=F2-(1,5*F4)

Notera: Parenteserna i formeln är inte nödvändiga eftersom multiplikation beräknas före subtraktion, men de underlättar läsbarheten.

För att beräkna den övre gränsen i cell F6 multiplicerar vi återigen IQR med 1,5, men den här gången adderar vi det till värdet för Q3:

=F3+(1,5*F4)

Steg Fyra: Identifiera Extremvärdena

Nu när vi har alla nödvändiga data kan vi börja identifiera våra extremvärden – de värden som är lägre än den nedre gränsen eller högre än den övre.

Vi använder ELLER-funktionen för att utföra ett logiskt test och visa de värden som uppfyller kriterierna genom att ange följande formel i cell C2:

=ELLER(B2F$6)

Vi kopierar sedan den formeln till cellerna C3-C14. Värdet SANT indikerar ett extremvärde. Som vi ser har vi två extremvärden i vår data.

Undvik Extremvärden Vid Beräkning av Medelvärde

Med KVARTIL-funktionen kan vi beräkna IQR och arbeta med den vanligaste definitionen av extremvärden. Men för att beräkna medelvärdet av ett dataområde och exkludera extremvärden finns det en snabbare och enklare funktion att använda. Denna teknik identifierar inte extremvärden på samma sätt som tidigare, men ger oss flexibilitet i att bestämma vad vi vill betrakta som ett extremvärde.

Funktionen vi behöver heter TRIMMEDELVÄRDE, och dess syntax ser ut så här:

=TRIMMEDELVÄRDE(array; procent)

Arrayen är det dataområde vi vill beräkna medelvärdet av. Procenten är andelen datapunkter som ska uteslutas från toppen och botten av datamängden (du kan ange det som en procentandel eller ett decimaltal).

I vårt exempel har vi skrivit in följande formel i cell D3 för att beräkna medelvärdet och exkludera 20% av extremvärdena:

=TRIMMEDELVÄRDE(B2:B14; 0,2)

Där har ni två olika metoder för att hantera extremvärden. Oavsett om du vill identifiera dem för specifik rapportering eller utesluta dem från beräkningar av t.ex. medelvärde, så har Excel en funktion som passar dina behov.