En avvikare är ett värde som är betydligt högre eller lägre än de flesta av värdena i dina data. När du använder Excel för att analysera data, extremvärden som kan förvränga resultaten. Till exempel, medeltalet av en uppsättning data kan verkligen speglar dina värderingar. Excel innehåller några användbara funktioner för att hjälpa dig att hantera din extremvärden, så låt oss ta en titt.
Ett Snabbt Exempel
I bilden nedan, extremvärden är relativt lätt att upptäcka—värdet av två tilldelats Eric och värde av 173 tilldelats Ryan. I en datamängd här, det är lätt att upptäcka och hantera dem som är avvikande värden manuellt.
I en större uppsättning av data, som inte kommer att vara fallet. Att kunna identifiera extremvärden och ta bort dem från statistiska beräkningar är viktigt—och det är vad vi kommer att titta på hur man gör i denna artikel.
Hur att Hitta Extremvärden i dina Uppgifter
För att hitta extremvärden i en uppsättning data, använder vi följande steg:
- Beräkna den 1: a och 3: e kvartiler (vi kommer att prata om vad de är i bara lite).
- Utvärdera kvartilavstånd (vi kommer även att förklara dessa lite längre ner).
- Avkastning de övre och nedre gränser för våra data räckvidd.
- Använd dessa gränser för att identifiera avvikande datapunkter.
Cellområdet på den högra av de uppgifter som syns i bilden nedan kommer att användas för att lagra dessa värden.
Låt oss komma igång.
Steg Ett: Beräkna Kvartiler
Om du delar upp dina data till kvartal, var och en av dessa grupper kallas en kvartilen. De lägsta 25% av nummer i intervallet göra upp den 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 avvikare är en datapunkt som är mer än 1,5 interquartile intervall (IQRs) under 1: a kvartilen, och 1,5 interquartile varierar över 3: e kvartilen. För att bestämma dessa värden måste vi först räkna ut vad kvartiler är.
Excel erbjuder en KVARTILEN funktion för att beräkna kvartiler. Det krävs två typer av information: den mängd och de quart.
=KVARTILEN(array, quart)
Matrisen är de värden som du håller på att utvärdera. Och quart är ett nummer som representerar kvartilen du vill återgå (till exempel, 1 för 1: a kvartilen, 2 för 2: a kvartilen, och så vidare).
Obs: I Excel 2010, Microsoft släppte KVARTILEN.INC och KVARTILEN.EXC funktioner som förbättringar till KVARTILEN funktion. KVARTILEN är mer kompatibla bakåt när man arbetar över flera versioner av Excel.
Låt oss återvända till vårt exempel på en tabell.
För att beräkna den 1: a Kvartilen vi kan använda följande formel i cell F2.
=KVARTILEN(C2:C14,1)
När du skriver in formeln i Excel innehåller en lista över alternativ för quart argument.
För att beräkna den 3: e kvartilen, kan vi ange en formel som den förra i cell F3, men med tre i stället för en.
=KVARTILEN(C2:C14,3)
Nu har vi fått kvartilen datapunkter visas i cellerna.
Steg Två: att Utvärdera kvartilavstånd
Kvartilavståndet (eller IQR) är den mellersta 50% av värdena i dina data. Det beräknas som skillnaden mellan den 1: a kvartil värde och den 3: e kvartil värde.
Vi kommer att använda en enkel formel i cell F4 som subtraherar 1: a kvartil från 3: e kvartilen:
=F3-F2
Nu kan vi se vår kvartilavstånd visas.
Steg Tre: Tillbaka den Nedre och Övre Gränserna
De nedre och övre gränserna är de minsta och största värdena av data sortiment 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 lägre gräns gräns i cell F5 genom att multiplicera IQR värde av 1,5 och sedan subtrahera det från Q1 datapunkt:
=F2-(1.5*F4)
Obs: Den parentes i denna formel är inte nödvändigt eftersom multiplikation del kommer att beräkna innan subtraktion del, men de gör formeln lättare att läsa.
För att beräkna den övre gränsen i cell F6, vi ska multiplicera IQR med 1,5 igen, men den här gången lägger till det i Q3 datapunkt:
=F3+(1.5*F4)
Steg Fyra: Identifiera Extremvärden
Nu när vi har fått alla våra underliggande data ställa upp, det är dags att identifiera våra perifera uppgifter poäng—de som är lägre än den nedre gränsen värde eller högre än den övre gränsen i värde.
Vi kommer att använda ELLER funktion för att utföra detta logiskt test och visa på de värden som uppfyller dessa kriterier genom att skriva in följande formel i cell C2:
=ELLER(B2<$F$5,B2>$F$6)
Vi kommer då att kopiera värde i våra C3-C14 celler. Ett SANT värde indikerar en avvikare, och som ni kan se, vi har fått två i våra data.
Ignorera Extremvärden vid Beräkningen av Medeltalet
Hjälp KVARTILEN funktion låt oss beräkna IQR och arbeta med det mest använda definitionen av en avvikare. Men när beräkna medelvärdet i genomsnitt för en mängd värden och bortser från extremvärden, det är en snabbare och enklare funktion att använda. Denna teknik kommer inte att identifiera en avvikare som innan, men det kommer att ge oss möjlighet att vara flexibla med vad vi kan tänka på vår avvikande delen.
Den funktion vi behöver kallas TRIMMEAN, och du kan se syntaxen för det nedan:
=TRIMMEAN(array, procent)
Matrisen är de värden du vill genomsnitt. I procent är den andel av data punkter för att utesluta från toppen och botten av data (du kan ange det som en procentsats eller ett decimaltal).
Vi gick in i formeln nedan i cell D3 i våra exempel för att beräkna den genomsnittliga och utesluta 20% av extremvärden.
=TRIMMEAN(B2:B14, 20%)
Där har du två olika funktioner för hantering av extremvärden. Om du vill att identifiera dem för några rapportering behov eller för att utesluta dem från beräkningar såsom medelvärden, Excel har en funktion för att passa dina behov.