Cum se calculează un Z-Scor folosind Microsoft Excel

Un Z-Scor este o valoare statistică care vă arată cât de multe deviații standard o anumită valoare se întâmplă de la mijlocul întregului set de date. Puteți utiliza formulele AVERAGE și STDEV.S sau STDEV.P pentru a calcula deviația medie și standard a datelor și apoi utilizați aceste rezultate pentru a determina scorul Z al fiecărei valori.

Ce este un Z-Score și ce fac funcțiile AVERAGE, STDEV.S și STDEV.P?

Un Z-Scor este un mod simplu de comparare a valorilor din două seturi de date diferite. Acesta este definit ca fiind numărul abaterilor standard departe de valoarea medie a unui punct de date. Formula generală arată astfel:

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

Iată un exemplu care să vă ajute la clarificare. Spui că ați vrut să comparați rezultatele testelor a doi studenți de algebră predate de diferiți profesori. Știți că primul student a obținut 95% din examenul final dintr-o clasă, iar elevul din clasa a marcat 87%.

La prima vedere, gradul de 95% este mai impresionant, dar dacă învățătorul clasei a doua a dat un examen mai dificil? Ați putea calcula scorul Z al scorului fiecărui elev pe baza scorurilor medii din fiecare clasă și a deviației standard a scorurilor din fiecare clasă. Comparând scorurile Z de la cei doi studenți, ar putea fi arătat că elevul cu scorul de 87% a făcut mai bine în comparație cu restul clasei decât studentul cu scorul de 98% în comparație cu restul clasei.

Prima valoare statistică de care aveți nevoie este "medie" și funcția "AVERARE" din Excel calculează această valoare. Pur și simplu adaugă toate valorile dintr-o gamă de celule și împarte acea sumă cu numărul de celule care conțin valori numerice (ignoră celulele goale).

O altă valoare statistică de care avem nevoie este "abaterea standard", iar Excel are două funcții diferite pentru a calcula abaterea standard în moduri puțin diferite.

Versiunile anterioare ale programului Excel aveau doar funcția "STDEV", care calculează abaterea standard, tratând în același timp datele ca "eșantion" al unei populații. Excel 2010 a spart acest lucru în două funcții care calculează deviația standard:

STDEV.S: Această funcție este identică cu funcția anterioară "STDEV". Acesta calculează abaterea standard, tratând în același timp datele ca "eșantion" al unei populații. Un eșantion dintr-o populație ar putea fi ceva asemănător țânțarilor particulari colectați pentru un proiect de cercetare sau mașini care au fost retrase din circuitul agricol și folosite pentru testarea siguranței la impact.
STDEV.P: Această funcție calculează abaterea standard în timp ce tratează datele ca întreaga populație. O întreagă populație ar fi ceva asemănător cu toți țânțarii de pe Pământ sau cu fiecare mașină într-o scenă de producție a unui model specific.

Ceea ce alegeți se bazează pe setul de date. Diferența va fi de obicei mică, dar rezultatul funcției "STDEV.P" va fi întotdeauna mai mic decât rezultatul funcției "STDEV.S" pentru același set de date. Este o abordare mai conservatoare de a presupune că există mai multă variabilitate a datelor.

Să vedem un exemplu

Pentru exemplul nostru, avem două coloane ("Valori" și "Scorul Z") și trei celule "helper" pentru stocarea rezultatelor funcțiilor "AVERAGE", "STDEV.S" și "STDEV.P". Coloana "Valori" conține zece numere aleatorii centrate în jurul valorii de 500, iar coloana "Z-Score" este locul unde vom calcula scorul Z folosind rezultatele stocate în celulele "helper".

În primul rând, vom calcula media valorilor utilizând funcția "AVERAGE". Selectați celula în care veți stoca rezultatul funcției "AVERAGE".

Introduceți următoarea formulă și apăsați introduce -sau - utilizați meniul "Formule".

= AVERAGE (E2: E13)

Pentru a accesa funcția prin meniul "Formule", selectați meniul drop-down "Mai multe funcții", selectați opțiunea "Statistică", apoi faceți clic pe "MEDIU".

În fereastra Argumente funcționale, selectați toate celulele din coloana "Valori" ca intrare pentru câmpul "Număr1". Nu trebuie să vă faceți griji cu privire la câmpul "Număr2".

Acum, apăsați "OK".

Apoi, trebuie să calculați abaterea standard a valorilor folosind funcția "STDEV.S" sau "STDEV.P". În acest exemplu, vă vom arăta cum să calculați ambele valori, începând cu "STDEV.S". Selectați celula unde va fi stocat rezultatul.

Pentru a calcula deviația standard folosind funcția "STDEV.S", tastați această formulă și apăsați Enter (sau accesați-o prin meniul "Formule").

= STDEV.S (E3: E12)

Pentru a accesa funcția prin meniul "Formule", selectați meniul drop-down "Mai multe funcții", selectați opțiunea "Statistică", derulați în jos, apoi faceți clic pe comanda "STDEV.S".

În fereastra Argumente funcționale, selectați toate celulele din coloana "Valori" ca intrare pentru câmpul "Număr1". Nu trebuie să vă faceți griji în legătură cu câmpul "Număr2" aici.

Acum, apăsați "OK".

Apoi vom calcula deviația standard folosind funcția "STDEV.P". Selectați celula unde va fi stocat rezultatul.

Pentru a calcula deviația standard folosind funcția "STDEV.P", introduceți această formulă și apăsați Enter (sau accesați-o prin meniul "Formule").

= STDEV.P (E3: E12)

Pentru a accesa funcția prin meniul "Formule", selectați meniul derulant "Mai multe funcții", selectați opțiunea "Statistică", derulați în jos, apoi faceți clic pe formatul "STDEV.P".

În fereastra Argumente funcționale, selectați toate celulele din coloana "Valori" ca intrare pentru câmpul "Număr1". Din nou, nu va trebui să vă faceți griji cu privire la câmpul "Număr2".

Acum, apăsați "OK".

Acum că am calculat deviația medie și standard a datelor noastre, avem tot ce avem nevoie pentru a calcula scorul Z. Putem folosi o formulă simplă care face referire la celulele care conțin rezultatele funcțiilor "AVERAGE" și "STDEV.S" sau "STDEV.P".

Selectați prima celulă din coloana "Z-Scor". Vom folosi rezultatul funcției "STDEV.S" pentru acest exemplu, dar puteți folosi și rezultatul din "STDEV.P".

Introduceți următoarea formulă și apăsați Enter:

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

Alternativ, puteți utiliza următorii pași pentru a introduce formula în loc să tastați:

Faceți clic pe celula F3 și tastați = (
Selectați celula E3. (Puteți apăsa butonul stânga-săgeată-cheie o dată sau utilizați mouse-ul)
Introduceți semnul minus -
Selectați celula G3 apoi apăsați F4 pentru a adăuga caracterele "$" pentru a face o referință "absolută" la celulă (va trece prin "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 "dacă continuați să apăsați F4)
Tip ) /
Selectați celula H3 (sau I3 dacă utilizați "STDEV.P") și apăsați F4 pentru a adăuga cele două caractere "$".
apasa Enter

Scorul Z a fost calculat pentru prima valoare. Este 0.15945 deviații standard sub media. Pentru a verifica rezultatele, puteți multiplica abaterea standard cu acest rezultat (6.271629 * -0.15945) și verificați dacă rezultatul este egal cu diferența dintre valoare și medie (499-500). Ambele rezultate sunt egale, deci valoarea are sens.

Să calculam Z-Scorurile celorlalte valori. Evidențiați întreaga coloană "Z-Score" începând cu celula care conține formula.

Apăsați Ctrl + D, care copiază formula din celula de sus în jos prin toate celelalte celule selectate.

Acum formula a fost "umplută" la toate celulele și fiecare va referi întotdeauna celulele "AVERAGE" și "STDEV.S" sau "STDEV.P" corecte din cauza caracterelor "$". Dacă primiți erori, reveniți și asigurați-vă că caracterele "$" sunt incluse în formula pe care ați introdus-o.

Calculul scorului Z fără a utiliza celule "Helper"

Celulele Helper stochează un rezultat, cum ar fi cele care stochează rezultatele funcțiilor "AVERAGE", "STDEV.S" și "STDEV.P". Ele pot fi utile, dar nu sunt întotdeauna necesare. Puteți să le ignorați cu totul atunci când calculați un Z-Scor, utilizând în schimb următoarele formule generalizate.

Iată unul utilizând funcția "STDEV.S":

= (Valoarea medie (valori)) / STDEV.S (Valori)

Și unul folosind funcția "STEV.P":

= (Valoarea medie (valori)) / STDEV.P (Valori)

Când introduceți intervalele de celule pentru "Valori" în funcții, asigurați-vă că adăugați referințe absolute ("$" folosind F4) astfel încât atunci când "completati" nu calculați deviația medie sau standard a unui interval diferit de celule în fiecare formulă.

Dacă aveți un set de date mare, poate fi mai eficient să utilizați celulele helper deoarece nu calculează rezultatul funcțiilor "AVERAGE" și "STDEV.S" sau "STDEV.P" de fiecare dată, salvând resursele procesorului și accelerarea timpului necesar pentru calcularea rezultatelor.

De asemenea, "$ G $ 3" are nevoie de mai puține octeți pentru stocarea și mai puțină RAM de încărcat decât "AVERAGE ($ E $ 3: $ E $ 12)". Acest lucru este important deoarece versiunea standard pe 32 de biți a programului Excel este limitată la 2 GB de RAM (versiunea pe 64 de biți nu are nici o limitare a capacității de utilizare a RAM).