Cum se calculează un scor Z folosind Microsoft Excel

Un scor Z este o valoare statistică care vă spune câte abateri standard se întâmplă să fie o anumită valoare de la media întregului set de date. Puteți utiliza formulele AVERAGE și STDEV.S sau STDEV.P pentru a calcula media și abaterea standard a datelor dvs. și apoi utilizați acele 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-Score este o modalitate simplă de a compara valorile din două seturi de date diferite. Este definit ca numărul de abateri standard de la media se află un punct de date. Formula generală arată astfel:

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

Iată un exemplu pentru a clarifica. Să presupunem că ați vrut să comparați rezultatele testelor a doi elevi de Algebră predați de profesori diferiți. Știi că primul student a obținut 95% la examenul final la o clasă, iar studentul din cealaltă clasă a obținut 87%.

La prima vedere, nota de 95% este mai impresionantă, dar dacă profesorul clasei a doua ar da un examen mai dificil? Puteți calcula scorul Z al punctajului fiecărui elev pe baza scorurilor medii din fiecare clasă și a abaterii standard a scorurilor din fiecare clasă. Compararea scorurilor Z ale celor doi elevi ar putea dezvălui că elevul cu scorul de 87% s-a descurcat mai bine în comparație cu restul clasei decât a făcut elevul cu scorul de 98% în comparație cu restul clasei lor.

Prima valoare statistică de care aveți nevoie este „media”, iar funcția „MEDIA” din Excel calculează acea valoare. Pur și simplu adună toate valorile dintr-un interval de celule și împarte acea sumă la numărul de celule care conțin valori numerice (ignoră celulele goale).

Cealaltă valoare statistică de care avem nevoie este „abaterea standard”, iar Excel are două funcții diferite pentru a calcula abaterea standard în moduri ușor diferite.

Versiunile anterioare de Excel aveau doar funcția „STDEV”, care calculează abaterea standard în timp ce tratează datele ca un „eșantion” al unei populații. Excel 2010 a împărțit asta în două funcții care calculează abaterea standard:

• STDEV.S: Această funcție este identică cu funcția anterioară „STDEV”. Acesta calculează abaterea standard în timp ce tratează datele ca pe un „eșantion” al unei populații. Un eșantion dintr-o populație ar putea fi ceva asemănător anumitor țânțari colectați pentru un proiect de cercetare sau mașini care au fost puse deoparte și utilizate pentru testarea siguranței la accident.
• STDEV.P: Această funcție calculează abaterea standard în timp ce tratează datele ca întreaga populație. O întreagă populație ar fi ceva ca toți țânțarii de pe Pământ sau fiecare mașină dintr-o serie de producție a unui anumit model.

Ceea ce alegeți se bazează pe setul dvs. 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 să presupunem că există mai multă variabilitate a datelor.

Să ne uităm la un exemplu

Pentru exemplul nostru, avem două coloane („Valori” și „Z-Score”) și trei celule „ajutor” 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 în care vom calcula Z-Score folosind rezultatele stocate în celulele „ajutor”.

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

Introdu următoarea formulă și apăsați enter -sau- utilizați meniul „Formule”.

 =MEDIE(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 „MEDIE”.

În fereastra Argumente ale funcției, selectați toate celulele din coloana „Valori” ca intrare pentru câmpul „Număr1”. Nu trebuie să vă faceți griji pentru câmpul „Număr2”.

Acum apăsați „OK”.

În continuare, trebuie să calculăm abaterea standard a valorilor folosind fie funcția „STDEV.S”, fie „STDEV.P”. În acest exemplu, vă vom arăta cum să calculați ambele valori, începând cu „STDEV.S.” Selectați celula în care va fi stocat rezultatul.

Pentru a calcula abaterea standard folosind funcția „STDEV.S”, introduceț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 puțin în jos, apoi faceți clic pe comanda „STDEV.S”.

În fereastra Argumente ale funcției, selectați toate celulele din coloana „Valori” ca intrare pentru câmpul „Număr1”. Nici aici nu trebuie să vă faceți griji pentru câmpul „Număr2”.

Acum apăsați „OK”.

În continuare, vom calcula abaterea standard folosind funcția „STDEV.P”. Selectați celula în care va fi stocat rezultatul.

Pentru a calcula abaterea 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 drop-down „Mai multe funcții”, selectați opțiunea „Statistică”, derulați puțin în jos, apoi faceți clic pe formula „STDEV.P”.

În fereastra Argumente ale funcției, 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 media și abaterea standard a datelor noastre, avem tot ce ne trebuie pentru a calcula Z-Score. 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-Score”. Vom folosi rezultatul funcției „STDEV.S” pentru acest exemplu, dar puteți utiliza și rezultatul de la „STDEV.P”.

Introdu următoarea formulă și apasă Enter:

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

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

1. Faceți clic pe celula F3 și tastați =(
2. Selectați celula E3. (Puteți apăsa tasta săgeată stânga o dată sau puteți folosi mouse-ul)
3. Introdu semnul minus -
4. Selectați celula G3, apoi apăsați F4 pentru a adăuga caracterele „$” pentru a face o referință „absolută” la celulă (se va trece prin „G3” > „ $ G $ 3″ > „G $ 3″ > „ $ G3″ > „G3” dacă apăsați în continuare F4 )
5. Tip )/
6. Selectați celula H3 (sau I3 dacă utilizați „STDEV.P”) și apăsați F4 pentru a adăuga cele două caractere „$”.
7. apasa Enter

Scorul Z a fost calculat pentru prima valoare. Este cu 0,15945 abateri standard sub medie. Pentru a verifica rezultatele, puteți înmulți 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ă calculăm scorurile Z ale restului valorilor. 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 „completată” în toate celulele și fiecare va face întotdeauna referire la celulele corecte „AVERAGE” și „STDEV.S” sau „STDEV.P” din cauza caracterelor „$”. Dacă primiți erori, întoarceți-vă și asigurați-vă că caracterele „$” sunt incluse în formula pe care ați introdus-o.

Calcularea scorului Z fără a utiliza celulele „Ajutor”.

Celulele auxiliare stochează un rezultat, precum cele care stochează rezultatele funcțiilor „MEDIA”, „STDEV.S” și „STDEV.P”. Ele pot fi utile, dar nu sunt întotdeauna necesare. Le puteți sări peste ele atunci când calculați un Scor Z utilizând, în schimb, următoarele formule generalizate.

Iată unul care utilizează funcția „STDEV.S”:

 =(Valoare-MEDIE(Valori))/STDEV.S(Valori)

Și unul folosind funcția „STEV.P”:

 =(Valoare-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 „compleați” să nu calculați media sau abaterea standard a unui interval diferit. de celule în fiecare formulă.

Dacă aveți un set mare de date, poate fi mai eficient să utilizați celule helper, deoarece nu calculează rezultatul funcțiilor „AVERAGE” și „STDEV.S” sau „STDEV.P” de fiecare dată, economisind resursele procesorului și accelerarea timpului necesar calculării rezultatelor.

De asemenea, „$G$3” necesită mai puțini octeți pentru stocare și mai puțină memorie RAM pentru a încărca decât „AVERAGE($E$3:$E$12).”. Acest lucru este important deoarece versiunea standard de 32 de biți a Excel este limitată la 2 GB de RAM (versiunea de 64 de biți nu are nicio limită în ceea ce privește cantitatea de RAM care poate fi utilizată).