En Z-Score er en statistisk værdi, der fortæller dig, hvor mange standardafvigelser en bestemt værdi tilfældigvis er fra gennemsnittet af hele datasættet. Du kan bruge AVERAGE og STDEV.S eller STDEV.P formler til at beregne middelværdien og standardafvigelsen af dine data og derefter bruge disse resultater til at bestemme Z-Score for hver værdi.
Indholdsfortegnelse
Hvad er en Z-Score, og hvad gør funktionerne AVERAGE, STDEV.S og STDEV.P?
En Z-Score er en enkel måde at sammenligne værdier fra to forskellige datasæt. Det er defineret som antallet af standardafvigelser væk fra middelværdien af et datapunkt. Den generelle formel ser sådan ud:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Her er et eksempel for at hjælpe med at afklare. Lad os sige, at du ville sammenligne testresultaterne for to algebraelever, der undervises af forskellige lærere. Du ved, at den første elev fik 95 % på den afsluttende eksamen i den ene klasse, og eleven i den anden klasse fik 87 %.
Ved første øjekast er karakteren på 95 % mere imponerende, men hvad nu hvis læreren i anden klasse gav en sværere eksamen? Du kan beregne Z-score for hver elevs score baseret på de gennemsnitlige scorer i hver klasse og standardafvigelsen for scorerne i hver klasse. En sammenligning af de to elevers Z-score kunne afsløre, at eleven med 87 % score klarede sig bedre i forhold til resten af deres klasse, end eleven med 98 % score gjorde i forhold til resten af deres klasse.
Den første statistiske værdi, du skal bruge, er ‘middelværdien’, og Excels “GENNEMSNIT”-funktion beregner denne værdi. Den lægger simpelthen alle værdierne i et celleområde sammen og dividerer denne sum med antallet af celler, der indeholder numeriske værdier (den ignorerer tomme celler).
Den anden statistiske værdi, vi skal bruge, er ‘standardafvigelsen’, og Excel har to forskellige funktioner til at beregne standardafvigelsen på lidt forskellige måder.
Tidligere versioner af Excel havde kun “STDEV”-funktionen, som beregner standardafvigelsen, mens dataene behandles som et ‘stikprøve’ af en population. Excel 2010 opdelte det i to funktioner, der beregner standardafvigelsen:
STDEV.S: Denne funktion er identisk med den tidligere “STDEV”-funktion. Den beregner standardafvigelsen, mens den behandler dataene som et ‘stikprøve’ af en population. Et udsnit af en befolkning kan være noget som de særlige myg indsamlet til et forskningsprojekt eller biler, der blev afsat og brugt til crashsikkerhedstest.
STDEV.P: Denne funktion beregner standardafvigelsen, mens dataene behandles som hele populationen. En hel befolkning ville være noget som alle myg på Jorden eller hver bil i en produktionsserie af en bestemt model.
Hvilken du vælger er baseret på dit datasæt. Forskellen vil normalt være lille, men resultatet af “STDEV.P”-funktionen vil altid være mindre end resultatet af “STDEV.S”-funktionen for det samme datasæt. Det er en mere konservativ tilgang at antage, at der er mere variabilitet i dataene.
Lad os se på et eksempel
For vores eksempel har vi to kolonner (“Værdier” og “Z-Score”) og tre “hjælper”-celler til lagring af resultaterne af funktionerne “AVERAGE”, “STDEV.S” og “STDEV.P”. Kolonnen “Værdier” indeholder ti tilfældige tal centreret omkring 500, og kolonnen “Z-Score” er, hvor vi vil beregne Z-score ved hjælp af resultaterne gemt i ‘hjælper’-cellerne.
Først vil vi beregne middelværdien af værdierne ved hjælp af funktionen “GENNEMSNIT”. Vælg den celle, hvor du vil gemme resultatet af “GENNEMSNIT”-funktionen.
Indtast følgende formel og tryk enter -eller- brug menuen “Formler”.
=AVERAGE(E2:E13)
For at få adgang til funktionen via menuen “Formler” skal du vælge rullemenuen “Flere funktioner”, vælge “Statistisk” og derefter klikke på “GENNEMSNIT”.
I vinduet Funktionsargumenter skal du vælge alle cellerne i kolonnen “Værdier” som input til feltet “Number1”. Du behøver ikke bekymre dig om feltet “Nummer2”.
Tryk nu på “OK”.
Dernæst skal vi beregne standardafvigelsen af værdierne ved hjælp af enten “STDEV.S” eller “STDEV.P” funktionen. I dette eksempel vil vi vise dig, hvordan du beregner begge værdier, startende med “STDEV.S.” Vælg den celle, hvor resultatet vil blive gemt.
For at beregne standardafvigelsen ved hjælp af “STDEV.S”-funktionen skal du indtaste denne formel og trykke på Enter (eller få adgang til den via menuen “Formler”).
=STDEV.S(E3:E12)
For at få adgang til funktionen via menuen “Formler” skal du vælge rullemenuen “Flere funktioner”, vælge “Statistisk”, rulle lidt ned og derefter klikke på kommandoen “STDEV.S”.
I vinduet Funktionsargumenter skal du vælge alle cellerne i kolonnen “Værdier” som input til feltet “Number1”. Du behøver heller ikke bekymre dig om feltet “Nummer2” her.
Tryk nu på “OK”.
Dernæst vil vi beregne standardafvigelsen ved hjælp af “STDEV.P”-funktionen. Vælg den celle, hvor resultatet vil blive gemt.
For at beregne standardafvigelsen ved hjælp af “STDEV.P”-funktionen skal du indtaste denne formel og trykke på Enter (eller få adgang til den via menuen “Formler”).
=STDEV.P(E3:E12)
For at få adgang til funktionen via menuen “Formler” skal du vælge rullemenuen “Flere funktioner”, vælge “Statistisk”, rulle lidt ned og derefter klikke på “STDEV.P”-formlen.
I vinduet Funktionsargumenter skal du vælge alle cellerne i kolonnen “Værdier” som input til feltet “Number1”. Igen, du behøver ikke at bekymre dig om feltet “Nummer2”.
Tryk nu på “OK”.
Nu hvor vi har beregnet middelværdien og standardafvigelsen af vores data, har vi alt, hvad vi behøver for at beregne Z-Score. Vi kan bruge en simpel formel, der refererer til cellerne, der indeholder resultaterne af funktionerne “AVERAGE” og “STDEV.S” eller “STDEV.P”.
Vælg den første celle i kolonnen “Z-Score”. Vi vil bruge resultatet af “STDEV.S”-funktionen til dette eksempel, men du kan også bruge resultatet fra “STDEV.P.”
Indtast følgende formel og tryk på Enter:
=(E3-$G$3)/$H$3
Alternativt kan du bruge følgende trin til at indtaste formlen i stedet for at skrive:
Klik på celle F3 og skriv =(
Vælg celle E3. (Du kan trykke én gang på venstre piletast eller bruge musen)
Indtast minustegnet –
Vælg celle G3, og tryk derefter på F4 for at tilføje “$” tegnene for at lave en ‘absolut’ reference til cellen (den vil cykle gennem “G3″> “$G$3” > “G$3” > “$G3” > “G3 ” hvis du fortsætter med at trykke på F4)
Type )/
Vælg celle H3 (eller I3, hvis du bruger “STDEV.P”) og tryk på F4 for at tilføje de to “$”-tegn.
Tryk på Enter
Z-Score er blevet beregnet for den første værdi. Det er 0,15945 standardafvigelser under middelværdien. For at kontrollere resultaterne kan du gange standardafvigelsen med dette resultat (6,271629 * -0,15945) og kontrollere, at resultatet er lig med forskellen mellem værdien og middelværdien (499-500). Begge resultater er ens, så værdien giver mening.
Lad os beregne Z-score for resten af værdierne. Fremhæv hele kolonnen ‘Z-Score’ startende med cellen, der indeholder formlen.
Tryk på Ctrl+D, som kopierer formlen i den øverste celle ned gennem alle de andre markerede celler.
Nu er formlen blevet ‘udfyldt’ til alle cellerne, og hver vil altid henvise til de korrekte “AVERAGE” og “STDEV.S” eller “STDEV.P” celler på grund af “$” tegnene. Hvis du får fejl, skal du gå tilbage og sørge for, at “$”-tegnene er inkluderet i den formel, du indtastede.
Beregning af Z-score uden at bruge ‘Hjælper’ celler
Hjælpeceller gemmer et resultat, ligesom dem, der gemmer resultaterne af funktionerne “AVERAGE”, “STDEV.S” og “STDEV.P”. De kan være nyttige, men er ikke altid nødvendige. Du kan springe dem helt over, når du beregner en Z-score ved i stedet at bruge følgende generaliserede formler.
Her er en, der bruger “STDEV.S”-funktionen:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Og en, der bruger “STEV.P”-funktionen:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Når du indtaster celleområderne for “Værdierne” i funktionerne, skal du sørge for at tilføje absolutte referencer (“$” ved hjælp af F4), så når du “udfylder” du ikke beregner gennemsnittet eller standardafvigelsen for et andet område af celler i hver formel.
Hvis du har et stort datasæt, kan det være mere effektivt at bruge hjælpeceller, fordi det ikke beregner resultatet af funktionerne “AVERAGE” og “STDEV.S” eller “STDEV.P” hver gang, hvilket sparer processorressourcer og at fremskynde den tid, det tager at beregne resultaterne.
Desuden tager “$G$3” færre bytes at gemme og mindre RAM at indlæse end “AVERAGE($E$3:$E$12).”. Dette er vigtigt, fordi standard 32-bit versionen af Excel er begrænset til 2 GB RAM (64-bit versionen har ingen begrænsninger på, hvor meget RAM der kan bruges).