En z-score er et statistisk mål som indikerer hvor mange standardavvik en spesifikk observasjon avviker fra gjennomsnittet for hele datasettet. For å kalkulere dette, benyttes funksjonene AVERAGE og STDEV.S eller STDEV.P til å finne henholdsvis gjennomsnitt og standardavvik. Disse resultatene brukes deretter til å bestemme z-score for hver enkelt verdi.
Hva er Z-score, og hvordan fungerer AVERAGE, STDEV.S og STDEV.P?
Z-score fungerer som et verktøy for å sammenligne data fra ulike datagrupper. En z-score defineres som antall standardavvik et datapunkt befinner seg fra gjennomsnittet. Den grunnleggende formelen for å beregne z-score er:
=(Datapunkt-GJENNOMSNITT(Datasett))/STANDARDAVVIK(Datasett)
La oss illustrere med et eksempel: Tenk deg at du ønsker å sammenligne testresultatene til to algebra-elever som har forskjellig lærer. Den ene eleven oppnådde 95 % på sin avsluttende eksamen, mens den andre fikk 87 %. Umiddelbart kan man anta at 95 % er den beste prestasjonen. Men hva om eksamenen til den andre læreren var betydelig vanskeligere? Z-score kalkuleres ved hjelp av gjennomsnitt og standardavvik for hver elevs klasse. Ved å sammenligne elevenes z-score, kan vi faktisk finne ut at eleven med 87 % presterte bedre sammenlignet med sin klasse, enn eleven med 95 % gjorde i sin klasse.
Den første statistiske verdien vi trenger, er «gjennomsnittet», og dette beregnes med Excels funksjon «GJENNOMSNITT». Denne funksjonen summerer alle verdiene i et område og deler summen på antallet celler som inneholder numeriske verdier. Tomme celler ignoreres.
Den andre viktige verdien er «standardavviket». Excel tilbyr to funksjoner for å beregne dette på litt forskjellige måter.
Eldre versjoner av Excel hadde kun «STANDARDAVVIK»-funksjonen, som beregner standardavviket basert på at dataene er et utvalg av en større populasjon. I Excel 2010 ble dette delt i to funksjoner:
STDEV.S: Denne funksjonen tilsvarer den tidligere «STANDARDAVVIK»-funksjonen. Den beregner standardavviket når dataene er et «utvalg» fra en populasjon. Et utvalg kan for eksempel være et utvalg mygg for forskning, eller et antall biler som er testet for kollisjonssikkerhet.
STDEV.P: Denne funksjonen kalkulerer standardavviket ut ifra at dataene representerer hele populasjonen. Et eksempel på en hel populasjon kan være alle mygg på jorden, eller hver bil i en bestemt produksjonsserie av en modell.
Valget mellom disse to funksjonene avhenger av datasettet. Forskjellen i resultatet er ofte liten, men «STDEV.P» vil alltid gi en mindre verdi enn «STDEV.S» for det samme datasettet. Det kan være en mer konservativ tilnærming å anta at det er mer variasjon i dataene.
Eksempel
I dette eksemplet har vi to kolonner: «Verdier» og «Z-score». Vi har også tre celler for å lagre resultatene av «GJENNOMSNITT», «STDEV.S» og «STDEV.P»-funksjonene. «Verdier»-kolonnen inneholder ti tilfeldige tall rundt 500, og i «Z-score»-kolonnen skal vi beregne z-scorene ved å bruke de lagrede resultatene.
Først beregner vi gjennomsnittet av verdiene med «GJENNOMSNITT»-funksjonen. Velg cellen der du vil lagre resultatet av funksjonen.
Skriv inn følgende formel og trykk enter, eller bruk «Formler»-menyen.
=AVERAGE(E2:E13)
For å finne funksjonen via «Formler»-menyen, velg «Flere funksjoner», deretter «Statistisk», og til slutt klikk på «GJENNOMSNITT».
I vinduet for funksjonsargumenter velger du alle cellene i «Verdier»-kolonnen som inndata for «Nummer1»-feltet. Du trenger ikke å fylle ut «Nummer2»-feltet.
Klikk «OK».
Nå må vi beregne standardavviket med enten «STDEV.S» eller «STDEV.P»-funksjonen. I dette eksemplet viser vi hvordan du beregner begge deler. Vi starter med «STDEV.S». Velg cellen der resultatet skal lagres.
For å beregne standardavviket med «STDEV.S»-funksjonen, skriv inn denne formelen og trykk Enter (eller finn den via «Formler»-menyen).
=STDEV.S(E3:E12)
For å få tilgang til funksjonen via «Formler»-menyen, velg «Flere funksjoner», «Statistisk», og klikk deretter på «STDEV.S».
I vinduet for funksjonsargumenter, velg alle cellene i «Verdier»-kolonnen som input for «Number1»-feltet. Du trenger heller ikke å fylle ut «Number2»-feltet.
Trykk «OK».
Nå skal vi kalkulere standardavviket ved hjelp av «STDEV.P»-funksjonen. Velg cellen hvor resultatet skal lagres.
For å beregne standardavviket med «STDEV.P»-funksjonen, skriv inn følgende formel og trykk Enter (eller finn den gjennom «Formler»-menyen).
=STDEV.P(E3:E12)
For å få tilgang til funksjonen via «Formler»-menyen, velg «Flere funksjoner», deretter «Statistisk», og klikk på «STDEV.P».
I vinduet for funksjonsargumenter velger du alle cellene i «Verdier»-kolonnen som input for «Number1»-feltet. Du trenger heller ikke å fylle ut «Number2»-feltet her.
Trykk «OK».
Nå som vi har beregnet gjennomsnittet og standardavviket, har vi alt vi trenger for å beregne z-score. Vi bruker en formel som refererer til cellene som inneholder resultatene fra «GJENNOMSNITT» og enten «STDEV.S» eller «STDEV.P».
Velg den første cellen i «Z-score»-kolonnen. Vi vil bruke resultatet fra «STDEV.S» i dette eksemplet, men du kan også benytte resultatet fra «STDEV.P».
Skriv inn følgende formel og trykk Enter:
=(E3-$G$3)/$H$3
Alternativt kan du bruke disse trinnene for å skrive inn formelen:
Klikk på celle F3 og skriv =(
Velg celle E3 (bruk venstre piltast eller mus).
Skriv inn minustegnet -.
Velg celle G3 og trykk F4 for å lage en absolutt referanse (den vil gå gjennom «G3» > «$G$3» > «G$3» > «$G3» > «G3» ved gjentatt trykk på F4).
Skriv )/.
Velg celle H3 (eller I3 om du bruker «STDEV.P») og trykk F4 for å legge til absolutt referanse.
Trykk Enter.
Z-scoren er nå beregnet for den første verdien. Det er 0,15945 standardavvik under gjennomsnittet. For å bekrefte resultatet, kan du multiplisere standardavviket med dette resultatet (6,271629 * -0,15945) og sjekke om det er lik differansen mellom verdien og gjennomsnittet (499-500). Begge resultater samsvarer, og dermed gir beregningen mening.
La oss kalkulere z-score for de resterende verdiene. Marker hele «Z-score»-kolonnen, inkludert cellen som inneholder formelen.
Trykk Ctrl+D, som kopierer formelen fra den øverste cellen ned til de andre valgte cellene.
Formelen er nå kopiert til alle cellene, og hver celle vil referere til de korrekte «GJENNOMSNITT» og «STDEV.S» eller «STDEV.P»-cellene, takket være «$»-tegnene. Hvis du får feil, sjekk at «$»-tegnene er inkludert i formelen.
Beregning av Z-score uten «hjelpeceller»
Hjelpeceller, som de som lagrer resultatene for «GJENNOMSNITT», «STDEV.S» og «STDEV.P» funksjonene, kan være nyttige, men er ikke nødvendig. Du kan hoppe over dem ved å bruke følgende formler:
Her er formelen som benytter «STDEV.S»-funksjonen:
=(Verdi-GJENNOMSNITT(Verdier))/STDEV.S(Verdier)
Og her er formelen som bruker «STDEV.P»-funksjonen:
=(Verdi-GJENNOMSNITT(Verdier))/STDEV.P(Verdier)
Når du skriver inn celleområdene for «Verdier» i formlene, må du huske å legge til absolutte referanser («$» ved bruk av F4), slik at når du fyller formelen, beregnes ikke gjennomsnittet og standardavviket for andre celler.
Ved store datasett kan det være mer effektivt å bruke hjelpeceller. Dette fordi det ikke kalkulerer «GJENNOMSNITT» og «STDEV.S» eller «STDEV.P» for hver z-score, noe som reduserer prosessortiden.
I tillegg tar «$G$3» færre byte å lagre og mindre RAM å laste inn enn «AVERAGE($E$3:$E$12)». Dette er viktig, da standard 32-bits Excel er begrenset til 2 GB RAM (64-bits versjonen har ikke slike begrensninger).