Når du utfører dataanalyse, du vanligvis anta at dine verdier klynge rundt noen sentrale datapunkt (median). Men noen ganger noen av verdiene faller for langt fra det sentrale punktet. Disse verdiene er kalt uteliggere (de ligger utenfor det forventede området). Uteliggere kan skew dine statistiske analyser, som fører deg til falske eller villedende konklusjoner om dine data.
Du kan bruke et par enkle formler og betinget formatering for å markere uteliggere i dataene.
Den første trinnet i å identifisere uteliggere er å finne den statistiske sentrum av området. For å gjøre dette pinpointing, starter du ved å finne den 1. og 3. kvartil. Et kvartil
er en statistisk fordeling av et datasett i fire like grupper, og hver gruppe som utgjør 25 prosent av dataene. Den øverste 25 prosent av en samling anses å være den første kvartil, mens den nederste 25 prosent regnes som den fjerde kvartil.
I Excel, kan du lett få kvartil verdier ved hjelp av kvartil funksjon. Denne funksjonen krever to argumenter: a. Spekter av data og kvartil nummeret du ønsker
I eksempelet vist, verdiene i cellene E3 og E4 er 1. og 3. kvartil for dataene i område B3: B20.
Tar disse to kvartilene, kan du beregne statistisk 50 prosent av datasettet ved å trekke tredje kvartil fra første kvartil. Dette statistisk 50 prosent kalles interkvartilt område (IQR). Figur 9-18 viser IQR i celle E5.
Nå er spørsmålet, hvor langt fra midten 50 prosent kan en verdi sitte og likevel bli betraktet som en "rimelig" verdi? Statistikere generelt enige om at IQR * 1.5 kan brukes til å etablere en fornuftig øvre og nedre gjerdet:
lavere gjerde er lik den første kvartil - IQR * 1.5.The øvre gjerdet er lik den tredje kvartil + IQR * 1,5.
Som du kan se, celler E7 og E8 beregne den endelige øvre og nedre gjerder. Enhver verdi som er større enn den øvre gjerdet eller mindre enn den nedre gjerdet er ansett som et avvikende.
På dette punktet, er enkel å implementere den betingede formater regelen.
For å bygge denne grunnleggende formatering-regel, følgende:
Velg data cellene i målområdet (celler B3: B20 i dette eksempelet), klikk på fanen Home of the Excel båndet, og velg deretter betinget formatering → Ny regel
dette. åpner dialogen Regel Ny formatering boksen.
klikk på bruk i listeboksen øverst i dialogboksen en formel for å bestemme hvilke celler å formatere alternativ.
Dette utvalget evaluerer verdier basert på en formel som du angir. Hvis en bestemt verdi evaluerer til SANN, er betinget formatering brukt på cellen.
Legg merke til at du bruker eller funksjon for å sammenligne verdien i målcellen (B3) til både øvre og nedre gjerder finnes i cellene $ E $ 7 og $ E $ 8, henholdsvis. Hvis målcellen er større enn den øvre gjerdet eller mindre enn den nedre gjerdet, er det ansett som en avvikende og dermed vil evaluere til SANN, utløser betinget formatering
= ELLER (B3. ≪ $ E $ 8, B3 > $ E $ 7 ) Legg merke til at i formelen, ekskluderer du den absolutte referansen dollartegn ($) for målcellen (B3). Hvis du klikker på celle B3 stedet for å skrive cellereferansen, gjør Excel automatisk cellereferanse absolutt. Det er viktig at du ikke tar med de absolutte referansen dollar symboler i målcellen fordi du trenger Excel for å bruke denne formater regel basert på hver celle sin egen verdi.
dette åpner dialogen Celler på Format-boksen, der du har et komplett sett med alternativer for formatering av skrift, grensen, og fylle for målcellen. Etter at du har fullført du velger formateringsvalg, klikk på OK-knappen for å bekrefte endringene og gå tilbake til dialogboksen regel Ny formatering boksen.
Hvis du trenger å redigere betinget formatering regel bare plassere markøren i noen av datacellene innenfor formatert rekkevidde og deretter gå til kategorien Hjem, og velg betinget formatering →. Administrer regler. Dette åpner dialogen Manager betingede formateringsregler boks. Klikk regelen du vil redigere og klikk på Rediger regel knappen.