Hvordan bruke Outliers-funksjonen i Excel

Når du analyserer data, kan avvikere skjeve resultatene og gjøre dem mindre pålitelige. For eksempel, hvis én eller flere av verdiene dine er vesentlig høyere enn majoriteten av dataene, gjør de gjennomsnittet høyere, noe som kanskje ikke gjenspeiler dataene som helhet. Å fjerne disse ytre datapunktene er derfor et viktig skritt for å utføre pålitelige statistiske beregninger. Uten dedikert avvikerfunksjon er den enkleste måten å utføre en avviktest i Excel ved å bruke interkvartilområde for å identifisere uteliggere, men du kan også bruke Trimmean-funksjonen for å tilnærme det samme resultat.

Interkvartilområdet av data er området som dekkes av "boksen" på et boks-og-hårhår-plott, eller mer nøyaktig, resultatet av å trekke verdien for den første kvartilen av dataene fra verdien for den tredje kvartil. Excel inneholder en innebygd funksjon for å beregne kvartil for dataene dine. Finn en ledig celle og skriv "=KVARTIL([dataområde], [kvartiltall])" og legg inn celleområdet for dataene dine der det står "[dataområde]" og kvartilen du vil ha der det står "[kvartil Nummer]."

For eksempel, hvis du har data i cellene A2 til A101 og du vil ha verdien for den første kvartilen, skriver du inn "=KVARTIL(A2:A101, 1)" for å finne verdien. For den første delen av argumentet kan du markere de relevante cellene med musen, men etter kommaet må du skrive nummeret på kvartilen du ønsker. For den tredje kvartilen med samme data skriver du "=KVARTIL(A2:A101, 3)" for å få resultatet.

Bruk en annen tom celle, trekk verdien av den første kvartilcellen fra verdien til den tredje kvartilcellen. Hvis den første kvartilen er i celle C2 og den tredje kvartilen er i celle D2, skriv "=D2-C2" for å få resultatet. Dette er interkvartilområdet.

For å finne uteliggere kan du nå bruke interkvartilområdet i uteliggerformelen, som sier at den øvre grensen for dataene er verdien av tredje kvartil pluss 1,5 ganger interkvartilområdet, og den nedre grensen er verdien av den første kvartilen minus 1,5 ganger interkvartilen område.

Hvis den første kvartilverdien er i C2, er den tredje kvartilverdien i celle D2, og interkvartilområdet er i celle E2 skriver du "=C2-(1,5 * E2)" for å finne den nedre grensen og "=D2+(1,5 * E2)" for å finne den øvre grensen grense. Generelt angir du "=[første kvartil] – (1,5 * [interkvartilområde])" for å finne den nedre grensen og "=[tredje kvartil] + (1,5 * [interkvartilområde])" for å finne den øvre grensen.

Alt under den nedre grensen eller over den øvre grensen er en uteligger.

For å fullføre uteliggertesten i Excel, bruk den logiske "ELLER"-funksjonen for å identifisere hvilke verdier i dataklassen din som er uteliggere på en effektiv måte. Skriv inn "=ELLER([datacelle]>[øvre grense], [datacelle]$F$2, A2

Hvis verdien i A2 er over den øvre grensen eller under den nedre grensen, viser den "TRUE", som indikerer at verdien er en uteligger. Du kan dra denne formelen ned ved å klikke nederst til høyre i cellen med formelen og dra den ned slik at den avsluttes ved siden av den endelige datacellen for å utføre samme beregning på hver datapunkt.

Du kan også markere dataene, og gå til "Betinget formatering" i "Stiler"-delen av "Hjem"-fanen hvis du vil endre formateringen for avvikene. Velg "Ny regel" og marker alternativet "Bruk en formel for å bestemme hvilke celler som skal formateres". Skriv inn samme formel som i forrige avsnitt, og klikk deretter på "Format" for å velge den unike formateringen for uteliggere.

"Trimmean"-funksjonen er en enklere måte å identifisere uteliggere på. Skriv "=TRIMMEAN([dataområde], [proporsjon til trim])" for å bruke funksjonen, med celleområdet som inneholder data i stedet for "[dataområde]" og en desimal prosentandelen du vil trimme der det står «[proporsjon for å trimme]». Dette fjerner ekstremverdiene øverst og nederst og beregner deretter gjennomsnittet basert på disse gjenstående. Så hvis du trimmet 10 prosent, ville det fjerne de øverste 5 prosentene og de nederste 5 prosentene før du beregner gjennomsnittet.

Hvis dataene går fra A2 til A101 og du vil trimme de ytterste 5 prosentene av verdiene, skriver du inn "=TRIMMEAN(A2:A101, 0,05)" for å finne det justerte gjennomsnittet. Du kan trimme 15 prosent ved å skrive "=TRIMMEAN(A2:A101, 0,15)" i stedet.