En Z-score er en statistisk verdi som forteller deg hvor mange standardavvik en bestemt verdi tilfeldigvis er fra gjennomsnittet av hele datasettet. Du kan bruke AVERAGE og STDEV.S eller STDEV.P formler for å beregne gjennomsnittet og standardavviket til dataene dine, og deretter bruke disse resultatene til å bestemme Z-score for hver verdi.
Innholdsfortegnelse
Hva er en Z-score og hva gjør funksjonene AVERAGE, STDEV.S og STDEV.P?
En Z-score er en enkel måte å sammenligne verdier fra to forskjellige datasett. Det er definert som antall standardavvik fra gjennomsnittet et datapunkt ligger. Den generelle formelen ser slik ut:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Her er et eksempel for å avklare. Si at du ønsket å sammenligne testresultatene til to algebra-elever undervist av forskjellige lærere. Du vet at den første studenten fikk 95 % på den avsluttende eksamenen i den ene klassen, og studenten i den andre klassen fikk 87 %.
Ved første øyekast er karakteren på 95 % mer imponerende, men hva om læreren i andre klasse ga en vanskeligere eksamen? Du kan beregne Z-poengsummen til hver elevs poengsum basert på gjennomsnittlig poengsum i hver klasse og standardavviket til poengsummen i hver klasse. Sammenligning av Z-poengsummen til de to elevene kan avsløre at eleven med 87 %-skåren gjorde det bedre sammenlignet med resten av klassen enn eleven med 98 %-skåren gjorde det sammenlignet med resten av klassen.
Den første statistiske verdien du trenger er «gjennomsnittet», og Excels «GJENNOMSNITTLIG»-funksjon beregner denne verdien. Den legger ganske enkelt sammen alle verdiene i et celleområde og deler den summen med antall celler som inneholder numeriske verdier (den ignorerer tomme celler).
Den andre statistiske verdien vi trenger er «standardavviket» og Excel har to forskjellige funksjoner for å beregne standardavviket på litt forskjellige måter.
Tidligere versjoner av Excel hadde bare «STDEV»-funksjonen, som beregner standardavviket mens dataene behandles som et «utvalg» av en populasjon. Excel 2010 delte det inn i to funksjoner som beregner standardavviket:
STDEV.S: Denne funksjonen er identisk med den forrige «STDEV»-funksjonen. Den beregner standardavviket mens den behandler dataene som et «utvalg» av en populasjon. Et utvalg av en populasjon kan være noe som den spesielle myggen samlet inn for et forskningsprosjekt eller biler som ble satt til side og brukt til kollisjonssikkerhetstesting.
STDEV.P: Denne funksjonen beregner standardavviket mens dataene behandles som hele populasjonen. En hel befolkning vil være noe som alle mygg på jorden eller hver bil i en produksjonsserie av en bestemt modell.
Hvilken du velger er basert på datasettet ditt. Forskjellen vil vanligvis være liten, men resultatet av «STDEV.P»-funksjonen vil alltid være mindre enn resultatet av «STDEV.S»-funksjonen for samme datasett. Det er en mer konservativ tilnærming å anta at det er mer variasjon i dataene.
La oss se på et eksempel
For eksempelet vårt har vi to kolonner («Verdier» og «Z-Score») og tre «hjelper»-celler for å lagre resultatene av funksjonene «AVERAGE», «STDEV.S» og «STDEV.P». «Verdier»-kolonnen inneholder ti tilfeldige tall sentrert rundt 500, og «Z-Score»-kolonnen er der vi vil beregne Z-score ved å bruke resultatene lagret i «hjelper»-cellene.
Først vil vi beregne gjennomsnittet av verdiene ved å bruke funksjonen «GJENNOMSNITT». Velg cellen der du vil lagre resultatet av «GJENNOMSNITTLIG»-funksjonen.
Skriv inn følgende formel og trykk enter -eller- bruk «Formler»-menyen.
=AVERAGE(E2:E13)
For å få tilgang til funksjonen gjennom «Formler»-menyen, velg «Flere funksjoner»-rullegardinmenyen, velg «Statistisk»-alternativet, og klikk deretter på «GJENNOMSNITTLIG».
I vinduet Funksjonsargumenter velger du alle cellene i «Verdier»-kolonnen som inndata for «Nummer1»-feltet. Du trenger ikke å bekymre deg for «Nummer2»-feltet.
Trykk nå «OK.»
Deretter må vi beregne standardavviket til verdiene ved å bruke enten «STDEV.S» eller «STDEV.P» funksjonen. I dette eksemplet vil vi vise deg hvordan du beregner begge verdiene, og starter med «STDEV.S.» Velg cellen der resultatet skal lagres.
For å beregne standardavviket ved å bruke «STDEV.S»-funksjonen, skriv inn denne formelen og trykk Enter (eller få tilgang til den gjennom «Formler»-menyen).
=STDEV.S(E3:E12)
For å få tilgang til funksjonen gjennom «Formler»-menyen, velg rullegardinmenyen «Flere funksjoner», velg «Statistisk»-alternativet, bla litt ned og klikk deretter på «STDEV.S»-kommandoen.
I vinduet Funksjonsargumenter velger du alle cellene i «Verdier»-kolonnen som inndata for «Number1»-feltet. Du trenger ikke å bekymre deg for «Number2»-feltet her heller.
Trykk nå «OK.»
Deretter vil vi beregne standardavviket ved å bruke «STDEV.P»-funksjonen. Velg cellen der resultatet skal lagres.
For å beregne standardavviket ved å bruke «STDEV.P»-funksjonen, skriv inn denne formelen og trykk Enter (eller få tilgang til den gjennom «Formler»-menyen).
=STDEV.P(E3:E12)
For å få tilgang til funksjonen gjennom «Formler»-menyen, velg «Flere funksjoner»-rullegardinmenyen, velg «Statistisk»-alternativet, bla litt ned og klikk deretter «STDEV.P»-formelen.
I vinduet Funksjonsargumenter velger du alle cellene i «Verdier»-kolonnen som inndata for «Number1»-feltet. Igjen, du trenger ikke å bekymre deg for «Number2»-feltet.
Trykk nå «OK.»
Nå som vi har beregnet gjennomsnittet og standardavviket til dataene våre, har vi alt vi trenger for å beregne Z-score. Vi kan bruke en enkel formel som refererer til cellene som inneholder resultatene av funksjonene «AVERAGE» og «STDEV.S» eller «STDEV.P».
Velg den første cellen i «Z-Score»-kolonnen. Vi vil bruke resultatet av «STDEV.S»-funksjonen for dette eksemplet, men du kan også bruke resultatet fra «STDEV.P.»
Skriv inn følgende formel og trykk Enter:
=(E3-$G$3)/$H$3
Alternativt kan du bruke følgende trinn for å skrive inn formelen i stedet for å skrive:
Klikk celle F3 og skriv =(
Velg celle E3. (Du kan trykke på venstre piltast én gang eller bruke musen)
Skriv inn minustegnet –
Velg celle G3 og trykk deretter F4 for å legge til «$»-tegnene for å lage en «absolutt» referanse til cellen (den vil gå gjennom «G3» > «$G$3» > «G$3» > «$G3» > «G3″ » hvis du fortsetter å trykke F4)
Type )/
Velg celle H3 (eller I3 hvis du bruker «STDEV.P») og trykk F4 for å legge til de to «$»-tegnene.
trykk enter
Z-score er beregnet for den første verdien. Det er 0,15945 standardavvik under gjennomsnittet. For å sjekke resultatene kan du multiplisere standardavviket med dette resultatet (6,271629 * -0,15945) og kontrollere at resultatet er lik differansen mellom verdien og gjennomsnittet (499-500). Begge resultatene er like, så verdien gir mening.
La oss beregne Z-score for resten av verdiene. Marker hele «Z-Score»-kolonnen som starter med cellen som inneholder formelen.
Trykk Ctrl+D, som kopierer formelen i den øverste cellen ned gjennom alle de andre valgte cellene.
Nå er formelen «fylt ut» til alle cellene, og hver av cellene vil alltid referere til de riktige «AVERAGE» og «STDEV.S» eller «STDEV.P»-cellene på grunn av «$»-tegnene. Hvis du får feil, gå tilbake og sørg for at «$»-tegnene er inkludert i formelen du skrev inn.
Beregning av Z-poengsum uten å bruke «hjelper»-celler
Hjelpeceller lagrer et resultat, som de som lagrer resultatene for funksjonene «AVERAGE», «STDEV.S» og «STDEV.P». De kan være nyttige, men er ikke alltid nødvendige. Du kan hoppe over dem helt når du beregner en Z-score ved å bruke følgende generaliserte formler i stedet.
Her er en som bruker «STDEV.S»-funksjonen:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Og en som bruker «STEV.P»-funksjonen:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Når du legger inn celleområdene for «Verdier» i funksjonene, må du sørge for å legge til absolutte referanser («$» ved hjelp av F4), slik at når du «fyller ut» du ikke beregner gjennomsnittet eller standardavviket for et annet område av celler i hver formel.
Hvis du har et stort datasett, kan det være mer effektivt å bruke hjelpeceller fordi det ikke beregner resultatet av funksjonene «AVERAGE» og «STDEV.S» eller «STDEV.P» hver gang, noe som sparer prosessorressurser og raskere tid det tar å beregne resultatene.
Dessuten tar «$G$3» færre byte å lagre og mindre RAM å laste enn «AVERAGE($E$3:$E$12).». Dette er viktig fordi standard 32-biters versjon av Excel er begrenset til 2 GB RAM (64-bit versjonen har ingen begrensninger på hvor mye RAM som kan brukes).