Sådan beregnes en Z-score ved hjælp af Microsoft Excel

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-formlerne til at beregne gennemsnittet og standardafvigelsen for dine data og derefter bruge disse resultater til at bestemme Z-score for hver værdi.

Hvad er en Z-score, og hvad gør AVERAGE, STDEV.S og STDEV.P-funktionerne?

En Z-score er en enkel måde at sammenligne værdier fra to forskellige datasæt på. Det defineres som antallet af standardafvigelser væk fra det gennemsnit, et datapunkt ligger. Den generelle formel ser sådan ud:

= (DataPoint-GENNEMSNITT (DataSet)) / STDEV (DataSet)

Her er et eksempel for at hjælpe med at afklare. Sig, at du ville sammenligne testresultaterne fra to algebra-studerende undervist af forskellige lærere. Du ved, at den første studerende fik 95% på den afsluttende eksamen i den ene klasse, og den studerende i den anden klasse fik 87%.

Ved første øjekast er 95% karakteren mere imponerende, men hvad hvis læreren i anden klasse aflagde en vanskeligere eksamen? Du kan beregne Z-Score for hver studerendes score baseret på gennemsnittet i hver klasse og standardafvigelsen for scorerne i hver klasse. Sammenligning af Z-Scores for de to studerende kunne afsløre, at den studerende med 87% -score gjorde det bedre i forhold til resten af ​​deres klasse end den studerende med 98% -score gjorde i sammenligning med resten af ​​deres klasse.

Den første statistiske værdi, du har brug for, er 'middelværdien', og Excels funktion "GENNEMSNIT" beregner denne værdi. Det tilføjer simpelthen alle værdierne i et celleområde og deler denne sum med antallet af celler, der indeholder numeriske værdier (det ignorerer tomme celler).

Den anden statistiske værdi, vi har brug for, 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 data behandles som en 'prøve' af en population. Excel 2010 delte det op i to funktioner, der beregner standardafvigelsen:

  • STDEV.S: Denne funktion er identisk med den tidligere “STDEV” -funktion. Det beregner standardafvigelsen, mens data behandles som en 'prøve' af en befolkning. En stikprøve af en befolkning kan være noget i retning af de særlige myg, der er indsamlet til et forskningsprojekt eller biler, der blev afsat og brugt til kollisionstest.
  • STDEV.P: Denne funktion beregner standardafvigelsen, mens data behandles som hele befolkningen. En hel befolkning ville være noget som alle myg på jorden eller enhver bil i en produktionskørsel af en bestemt model.

Hvilket 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 variation 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 "GENNEMSNIT", "STDEV.S" og "STDEV.P". Kolonnen "Værdier" indeholder ti tilfældige tal centreret omkring 500, og kolonnen "Z-score" er hvor vi beregner Z-score ved hjælp af de resultater, der er gemt i 'hjælpercellerne.

Først beregner vi gennemsnittet af værdierne ved hjælp af funktionen "GENNEMSNIT". Vælg den celle, hvor du vil gemme resultatet af funktionen "GENNEMSNIT".

Indtast følgende formel, og tryk på enter -eller- brug menuen “Formler”.

= GENNEMSNIT (E2: E13)

For at få adgang til funktionen gennem menuen "Formler" skal du vælge rullemenuen "Flere funktioner", vælge "Statistisk" og derefter klikke på "GEMIDDELIG."

I vinduet Funktionsargumenter skal du vælge alle cellerne i kolonnen "Værdier" som input til feltet "Nummer1". Du behøver ikke bekymre dig om feltet "Number2".

Tryk nu på “OK”.

Dernæst skal vi beregne standardafvigelsen for værdierne ved hjælp af enten "STDEV.S" eller "STDEV.P" -funktionen. I dette eksempel viser vi 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 gennem 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 "Nummer1". Du behøver heller ikke bekymre dig om feltet "Number2" her.

Tryk nu på “OK”.

Dernæst beregner vi 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 "Nummer1". Igen behøver du ikke bekymre dig om feltet "Number2".

Tryk nu på “OK”.

Nu hvor vi har beregnet gennemsnittet 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 “GEMIDDELLIG” og “STDEV.S” eller “STDEV.P”.

Vælg den første celle i kolonnen "Z-Score". Vi bruger resultatet af funktionen "STDEV.S" til dette eksempel, men du kan også bruge resultatet fra "STDEV.P."

Indtast følgende formel og tryk Enter:

= (E3- $ G $ 3) / $ H $ 3

Alternativt kan du bruge følgende trin til at indtaste formlen i stedet for at skrive:

  1. Klik på celle F3, og skriv =(
  2. Vælg celle E3. (Du kan trykke på venstre piletast en gang eller bruge musen)
  3. Indtast minustegnet -
  4. Vælg celle G3, tryk derefter på F4 for at tilføje “$” -tegnene for at gøre en 'absolut' henvisning til cellen (den cykler gennem "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> “G3”, hvis du fortsætter med at trykke på F4 )
  5. Type )/
  6. Vælg celle H3 (eller I3, hvis du bruger “STDEV.P”), og tryk på F4 for at tilføje de to “$” -tegn.
  7. Tryk på Enter

Z-Score er beregnet til den første værdi. Det er 0,155945 standardafvigelser under gennemsnittet. For at kontrollere resultaterne kan du gange standardafvigelsen med dette resultat (6.271629 * -0.15945) og kontrollere, at resultatet er lig forskellen mellem værdien og gennemsnittet (499-500). Begge resultater er ens, så værdien giver mening.

Lad os beregne Z-Scores for resten af ​​værdierne. Fremhæv hele 'Z-Score' kolonnen startende med cellen, der indeholder formlen.

Tryk på Ctrl + D, som kopierer formlen i den øverste celle ned gennem alle de andre valgte celler.

Nu er formlen blevet 'udfyldt' til alle cellerne, og hver vil altid henvise til de korrekte "GENNEMSNIT" 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 brug af 'hjælperceller'

Hjælperceller gemmer et resultat, ligesom dem der gemmer resultaterne af funktionerne “GENNEMSNIT”, “STDEV.S” og “STDEV.P”. De kan være nyttige, men er ikke altid nødvendige. Du kan springe dem over ved beregning af et Z-score ved hjælp af følgende generelle formler i stedet.

Her er en, der bruger “STDEV.S” -funktionen:

= (Value-AVERAGE (Værdier)) / STDEV.S (værdier)

Og en der bruger “STEV.P” -funktionen:

= (Value-AVERAGE (Værdier)) / STDEV.P (værdier)

Når du indtaster celleområderne for "Værdier" i funktionerne, skal du sørge for at tilføje absolutte referencer ("$" ved hjælp af F4), så når du 'udfylder', beregner du ikke gennemsnittet eller standardafvigelsen for et andet interval af celler i hver formel.

Hvis du har et stort datasæt, kan det være mere effektivt at bruge hjælperceller, fordi det ikke beregner resultatet af funktionerne “AVERAGE” og “STDEV.S” eller “STDEV.P” hver gang, hvilket sparer processorressourcer og fremskynde den tid, det tager at beregne resultaterne.

Desuden tager “$ G $ 3” færre byte at gemme og mindre RAM at indlæse end “GEMIDDELIG ($ 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 for, hvor meget RAM der kan bruges).