Hvordan fremheve statistiske avvik i Excel

Når du utfører dataanalyse, antar du vanligvis at verdiene dine grupperer seg rundt et sentralt datapunkt (en median). Men noen ganger faller noen av verdiene for langt fra det sentrale punktet. Disse verdiene kalles outliers (de ligger utenfor det forventede området). Ytterligere kan forvrenge de statistiske analysene dine, og føre deg til falske eller misvisende konklusjoner om dataene dine.

Du kan bruke noen få enkle formler og betinget formatering for å fremheve avvikene i dataene dine.

Det første trinnet i å identifisere uteliggere er å finne det statistiske sentrum av området. For å gjøre dette, starter du med å finne 1. og 3. kvartil. En kvartil er en statistisk inndeling av et datasett i fire like grupper, der hver gruppe utgjør 25 prosent av dataene. De øverste 25 prosentene av en samling regnes som 1. kvartil, mens de nederste 25 prosent regnes som 4. kvartil.

I Excel kan du enkelt få kvartilverdier ved å bruke KVARTILE-funksjonen. Denne funksjonen krever to argumenter: et dataområde og kvartiltallet du ønsker.

I eksemplet som vises, er verdiene i cellene E3 og E4 1. og 3. kvartil for dataene i området B3:B20.

Ved å ta disse to kvartilene kan du beregne de statistiske 50 prosentene av datasettet ved å trekke den 3. kvartilen fra den 1. kvartilen. Disse statistiske 50 prosentene kalles interkvartilområdet (IQR). Figur 9-18 viser IQR i celle E5.

Nå er spørsmålet, hvor langt fra de midterste 50 prosentene kan en verdi sitte og fortsatt anses som en "rimelig" verdi? Statistikere er generelt enige om at IQR*1.5 kan brukes til å etablere et rimelig øvre og nedre gjerde:

Det nedre gjerdet er lik 1. kvartil – IQR*1,5.

Det øvre gjerdet er lik 3. kvartil + IQR*1,5.

Som du kan se, beregner cellene E7 og E8 de endelige øvre og nedre gjerdene. Enhver verdi større enn det øvre gjerdet eller mindre enn det nedre gjerdet regnes som en uteligger.

På dette tidspunktet er regelen for betinget formatering enkel å implementere.

Følg disse trinnene for å bygge denne grunnleggende formateringsregelen:

Velg datacellene i målområdet ditt (cellene B3:B20 i dette eksemplet), klikk på Hjem-fanen på Excel-båndet, og velg deretter Betinget formatering → Ny regel.

Dette åpner dialogboksen Ny formateringsregel.

I listeboksen øverst i dialogboksen klikker du alternativet Bruk en formel for å bestemme hvilke celler som skal formateres.

Dette valget evaluerer verdier basert på en formel du angir. Hvis en bestemt verdi evalueres til TRUE, brukes den betingede formateringen på den cellen.

I formelinntastingsboksen skriver du inn formelen som vises her.

Merk at du bruker ELLER-funksjonen til å sammenligne verdien i målcellen din (B3) med både øvre og nedre gjerder som finnes i henholdsvis cellen $E$7 og $E$8. Hvis målcellen er større enn det øvre gjerdet eller mindre enn det nedre gjerdet, regnes den som en uteligger og vil derfor evalueres til TRUE, og utløse den betingede formateringen.

=ELLER(B3<$e$8,b3>$E$7)

Merk at i formelen ekskluderer du de absolutte referansedollarsymbolene ($) for målcellen (B3). Hvis du klikker celle B3 i stedet for å skrive cellereferansen, gjør Excel automatisk cellereferansen absolutt. Det er viktig at du ikke inkluderer de absolutte referansedollarsymbolene i målcellen din fordi du trenger Excel for å bruke denne formateringsregelen basert på hver celles egen verdi.

Klikk på Format-knappen.

Dette åpner dialogboksen Formater celler, der du har et komplett sett med alternativer for formatering av font, kantlinje og fyll for målcellen. Etter at du har fullført valg av formateringsalternativer, klikker du på OK-knappen for å bekrefte endringene og gå tilbake til dialogboksen Ny formateringsregel.

Tilbake i dialogboksen Ny formateringsregel klikker du på OK-knappen for å bekrefte formateringsregelen.

Hvis du trenger å redigere regelen for betinget formatering, plasserer du bare markøren i en av datacellene innenfor det formaterte området ditt og går deretter til Hjem-fanen og velger Betinget formatering → Administrer regler. Dette åpner dialogboksen Conditional Formatting Rules Manager. Klikk regelen du vil redigere, og klikk deretter Rediger regel-knappen.