Hur man beräknar en Z-poäng med Microsoft Excel

En Z-Score är ett statistiskt värde som talar om hur många standardavvikelser ett visst värde råkar vara från medelvärdet av hela datamängden. Du kan använda AVERAGE och STDEV.S eller STDEV.P formler för att beräkna medelvärdet och standardavvikelsen för dina data och sedan använda dessa resultat för att bestämma Z-Score för varje värde.

Vad är en Z-Score och vad gör funktionerna AVERAGE, STDEV.S och STDEV.P?

En Z-Score är ett enkelt sätt att jämföra värden från två olika datamängder. Det definieras som antalet standardavvikelser från medelvärdet för en datapunkt. Den allmänna formeln ser ut så här:

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

Här är ett exempel för att förtydliga. Säg att du ville jämföra testresultaten för två algebraelever som undervisats av olika lärare. Du vet att den första studenten fick 95 % på slutprovet i en klass, och studenten i den andra klassen fick 87 %.

Vid första anblicken är 95%-betyget mer imponerande, men tänk om läraren i andra klassen gav ett svårare prov? Du kan beräkna Z-poängen för varje elevs poäng baserat på de genomsnittliga poängen i varje klass och standardavvikelsen för poängen i varje klass. Att jämföra Z-poängen för de två eleverna kan avslöja att eleven med 87%-poängen gjorde det bättre i jämförelse med resten av sin klass än eleven med 98%-poängen gjorde i jämförelse med resten av sin klass.

Det första statistiska värdet du behöver är ”medelvärde” och Excels ”MEDEL”-funktion beräknar det värdet. Den lägger helt enkelt ihop alla värden i ett cellområde och delar den summan med antalet celler som innehåller numeriska värden (den ignorerar tomma celler).

Det andra statistiska värdet vi behöver är ’standardavvikelsen’ och Excel har två olika funktioner för att beräkna standardavvikelsen på lite olika sätt.

Tidigare versioner av Excel hade bara funktionen ”STDEV”, som beräknar standardavvikelsen samtidigt som data behandlas som ett ”prov” av en population. Excel 2010 delade upp det i två funktioner som beräknar standardavvikelsen:

STDEV.S: Denna funktion är identisk med den tidigare ”STDEV”-funktionen. Den beräknar standardavvikelsen samtidigt som data behandlas som ett ”prov” av en population. Ett urval av en befolkning kan vara något som de särskilda myggorna som samlats in för ett forskningsprojekt eller bilar som ställdes åt sidan och användes för krocksäkerhetstestning.
STDEV.P: Denna funktion beräknar standardavvikelsen samtidigt som data behandlas som hela populationen. En hel befolkning skulle vara ungefär som alla myggor på jorden eller varje bil i en produktionsserie av en specifik modell.

Vilket du väljer är baserat på din datamängd. Skillnaden kommer vanligtvis att vara liten, men resultatet av ”STDEV.P”-funktionen kommer alltid att vara mindre än resultatet av ”STDEV.S”-funktionen för samma datauppsättning. Det är ett mer konservativt tillvägagångssätt att anta att det finns mer variation i data.

Låt oss titta på ett exempel

För vårt exempel har vi två kolumner (”Värden” och ”Z-Score”) och tre ”hjälpar”-celler för att lagra resultaten av funktionerna ”AVERAGE”, ”STDEV.S” och ”STDEV.P”. Kolumnen ”Värden” innehåller tio slumpmässiga tal centrerade runt 500, och kolumnen ”Z-Score” är där vi kommer att beräkna Z-poängen med hjälp av resultaten lagrade i ”hjälparcellerna”.

Först kommer vi att beräkna medelvärdet av värdena med hjälp av funktionen ”MEDEL”. Välj den cell där du ska lagra resultatet av funktionen ”GENOM”.

Skriv in följande formel och tryck enter -eller- använd menyn ”Formler”.

=AVERAGE(E2:E13)

För att komma åt funktionen via menyn ”Formler”, välj rullgardinsmenyn ”Fler funktioner”, välj alternativet ”Statistisk” och klicka sedan på ”MEDEL”.

I fönstret Funktionsargument väljer du alla celler i kolumnen ”Värden” som indata för fältet ”Number1”. Du behöver inte oroa dig för fältet ”Nummer2”.

Tryck nu på ”OK”.

Därefter måste vi beräkna standardavvikelsen för värdena med antingen funktionen ”STDEV.S” eller ”STDEV.P”. I det här exemplet kommer vi att visa dig hur du beräknar båda värdena, med början med ”STDEV.S.” Välj cellen där resultatet ska lagras.

För att beräkna standardavvikelsen med funktionen ”STDEV.S”, skriv in den här formeln och tryck på Enter (eller öppna den via menyn ”Formler”).

=STDEV.S(E3:E12)

För att komma åt funktionen via menyn ”Formler”, välj rullgardinsmenyn ”Fler funktioner”, välj alternativet ”Statistisk”, scrolla ner lite och klicka sedan på kommandot ”STDEV.S”.

I fönstret Funktionsargument väljer du alla celler i kolumnen ”Värden” som indata för fältet ”Number1”. Du behöver inte oroa dig för ”Number2”-fältet här heller.

Tryck nu på ”OK”.

Därefter kommer vi att beräkna standardavvikelsen med funktionen ”STDEV.P”. Välj cellen där resultatet ska lagras.

För att beräkna standardavvikelsen med funktionen ”STDEV.P”, skriv in den här formeln och tryck på Enter (eller öppna den via menyn ”Formler”).

=STDEV.P(E3:E12)

För att komma åt funktionen via menyn ”Formler”, välj rullgardinsmenyn ”Fler funktioner”, välj alternativet ”Statistisk”, scrolla ner lite och klicka sedan på formeln ”STDEV.P”.

I fönstret Funktionsargument väljer du alla celler i kolumnen ”Värden” som indata för fältet ”Number1”. Återigen, du behöver inte oroa dig för fältet ”Number2”.

Tryck nu på ”OK”.

Nu när vi har beräknat medelvärdet och standardavvikelsen för våra data, har vi allt vi behöver för att beräkna Z-poängen. Vi kan använda en enkel formel som refererar till cellerna som innehåller resultaten av funktionerna ”AVERAGE” och ”STDEV.S” eller ”STDEV.P”.

Välj den första cellen i kolumnen ”Z-Score”. Vi kommer att använda resultatet av ”STDEV.S”-funktionen för det här exemplet, men du kan också använda resultatet från ”STDEV.P.”

Skriv in följande formel och tryck på Enter:

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

Alternativt kan du använda följande steg för att ange formeln istället för att skriva:

Klicka på cell F3 och skriv =(
Välj cell E3. (Du kan trycka på vänsterpilen en gång eller använda musen)
Skriv minustecknet –
Välj cell G3 och tryck sedan på F4 för att lägga till ”$”-tecknen för att göra en ”absolut” referens till cellen (den kommer att gå igenom ”G3” > ”$G$3″ > ”G$3″ > ”$G3″ > ”G3 ” om du fortsätter att trycka på F4)
Typ )/
Välj cell H3 (eller I3 om du använder ”STDEV.P”) och tryck på F4 för att lägga till de två ”$”-tecknen.
tryck enter

Z-poängen har beräknats för det första värdet. Det är 0,15945 standardavvikelser under medelvärdet. För att kontrollera resultaten kan du multiplicera standardavvikelsen med detta resultat (6,271629 * -0,15945) och kontrollera att resultatet är lika med skillnaden mellan värdet och medelvärdet (499-500). Båda resultaten är lika, så värdet är vettigt.

Låt oss beräkna Z-poängen för resten av värdena. Markera hela kolumnen ”Z-Score” som börjar med cellen som innehåller formeln.

Tryck på Ctrl+D, vilket kopierar formeln i den översta cellen ner genom alla andra markerade celler.

Nu har formeln ”ifyllts” till alla celler, och var och en kommer alltid att referera till rätt ”AVERAGE” och ”STDEV.S” eller ”STDEV.P” celler på grund av ”$”-tecknen. Om du får fel, gå tillbaka och se till att ”$”-tecknen ingår i formeln du angav.

Beräkna Z-poängen utan att använda ”hjälparceller”.

Hjälpceller lagrar ett resultat, som de som lagrar resultaten för funktionerna ”AVERAGE”, ”STDEV.S” och ”STDEV.P”. De kan vara användbara men är inte alltid nödvändiga. Du kan hoppa över dem helt när du beräknar en Z-poäng genom att använda följande generaliserade formler istället.

Här är en som använder ”STDEV.S”-funktionen:

=(Value-AVERAGE(Values))/STDEV.S(Values)

Och en som använder funktionen ”STEV.P”:

=(Value-AVERAGE(Values))/STDEV.P(Values)

När du anger cellområdena för ”Värden” i funktionerna, var noga med att lägga till absoluta referenser (”$” med F4) så att när du ”fyller ner” du inte beräknar medelvärdet eller standardavvikelsen för ett annat område av celler i varje formel.

Om du har en stor datamängd kan det vara mer effektivt att använda hjälpceller eftersom det inte beräknar resultatet av funktionerna ”AVERAGE” och ”STDEV.S” eller ”STDEV.P” varje gång, vilket sparar processorresurser och påskynda tiden det tar att beräkna resultaten.

Dessutom tar ”$G$3” färre byte att lagra och mindre RAM att ladda än ”AVERAGE($E$3:$E$12).”. Detta är viktigt eftersom standardversionen av 32-bitars Excel är begränsad till 2 GB RAM (64-bitarsversionen har inga begränsningar för hur mycket RAM-minne som kan användas).