Un scor Z reprezintă o măsură statistică ce indică de câte abateri standard se îndepărtează o anumită valoare de media setului de date analizat. Pentru a determina această valoare, se pot folosi funcțiile AVERAGE și STDEV.S sau STDEV.P pentru a calcula media și deviația standard a datelor, iar apoi aceste rezultate sunt folosite în formula scorului Z.
Ce reprezintă Scor Z și rolul funcțiilor AVERAGE, STDEV.S și STDEV.P?
Scorul Z este un instrument util pentru compararea valorilor provenite din seturi de date distincte. El ne arată numărul de abateri standard cu care o valoare se diferențiază de media setului său. Formula generală pentru calcularea scorului Z este:
=(Valoare-AVERAGE(SetDate))/STDEV(SetDate)
Pentru a exemplifica, să considerăm compararea rezultatelor obținute la teste de doi elevi la Algebră, din clase diferite. Unul a obținut 95%, iar celălalt 87%. La prima vedere, 95% pare a fi o performanță superioară. Totuși, dacă profesorul celui de-al doilea elev a aplicat un test mai dificil? Calculând scorul Z pentru fiecare elev, bazat pe media și deviația standard ale clasei sale, am putea observa că elevul cu 87% a avut o performanță mai bună în raport cu colegii săi decât elevul cu 95% în contextul clasei sale.
Prima valoare necesară este „media”, calculată cu funcția „MEDIA” din Excel. Aceasta adună valorile dintr-un interval de celule și împarte suma la numărul de celule cu valori numerice, ignorând celulele goale.
A doua valoare este „deviația standard”, pentru care Excel oferă două funcții distincte, diferențiate prin modul ușor diferit în care calculează această valoare.
Versiunile anterioare de Excel foloseau funcția „STDEV”, care trata datele ca un eșantion dintr-o populație. Din 2010, această funcție a fost înlocuită cu două variante:
STDEV.S: Această funcție este echivalentă cu vechea „STDEV”, calculând deviația standard pe baza premizei că datele reprezintă un eșantion dintr-o populație mai mare. De exemplu, analiza țânțarilor colectați pentru un studiu sau testele de siguranță auto realizate pe câteva mașini dintr-o producție.
STDEV.P: Această funcție calculează deviația standard tratând datele ca fiind întreaga populație. Un exemplu ar fi analizarea tuturor țânțarilor de pe planetă sau a tuturor mașinilor fabricate într-un anumit model.
Alegerea funcției depinde de specificul datelor. De obicei, diferențele sunt mici, dar rezultatul „STDEV.P” va fi întotdeauna mai mic decât cel al „STDEV.S” pentru aceleași date. Utilizarea „STDEV.S” este o abordare mai conservatoare, presupunând o variabilitate mai mare a datelor.
Exemplu concret de calcul
Pentru a ilustra, vom utiliza două coloane: „Valori” și „Scor Z”, și trei celule „ajutătoare” pentru a memora rezultatele funcțiilor „AVERAGE”, „STDEV.S” și „STDEV.P”. Coloana „Valori” conține zece numere aleatorii în jurul valorii 500, iar în coloana „Scor Z” vom calcula scorul folosind rezultatele celulelor „ajutătoare”.
Începem prin a calcula media valorilor cu funcția „MEDIA”. Selectați celula în care veți afișa rezultatul.
Introduceți formula de mai jos sau folosiți meniul „Formule”.
=AVERAGE(E2:E13)
Pentru a accesa funcția din meniul „Formule”, selectați „Mai multe funcții”, apoi „Statistică” și apoi faceți clic pe „MEDIE”.
În fereastra „Argumente funcție”, selectați toate celulele din coloana „Valori” pentru câmpul „Număr1”. Nu este nevoie să vă preocupați de „Număr2”.
Apăsați „OK”.
Urmează calculul deviației standard, folosind funcția „STDEV.S” sau „STDEV.P”. În acest exemplu, vom calcula ambele valori. Începem cu „STDEV.S”. Selectați celula pentru rezultat.
Pentru a calcula deviația standard cu „STDEV.S”, introduceți formula și apăsați Enter sau accesați funcția din meniul „Formule”.
=STDEV.S(E3:E12)
Accesați „Mai multe funcții”, „Statistică” și apoi comanda „STDEV.S”.
Selectați celulele din coloana „Valori” pentru câmpul „Număr1”. Nu este nevoie de „Număr2”.
Apăsați „OK”.
Acum vom calcula deviația standard folosind funcția „STDEV.P”. Selectați celula pentru rezultat.
Introduceți formula sau accesați-o din meniul „Formule”.
=STDEV.P(E3:E12)
Alegeți „Mai multe funcții”, „Statistică” și apoi „STDEV.P”.
Selectați celulele din coloana „Valori” pentru „Număr1”. Nu vă preocupați de „Număr2”.
Apăsați „OK”.
Acum avem media și deviația standard necesare pentru calculul scorului Z. Putem folosi o formulă care face referire la celulele ce conțin rezultatele funcțiilor „AVERAGE” și „STDEV.S” sau „STDEV.P”.
Selectați prima celulă din coloana „Scor Z”. În acest exemplu vom folosi rezultatul funcției „STDEV.S”, dar se poate folosi și „STDEV.P”.
Introduceți formula:
=(E3-$G$3)/$H$3
Alternativ, puteți introduce formula folosind următorii pași:
Faceți clic pe celula F3 și tastați =(
Selectați celula E3.
Tastaț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ă.
Tip )/
Selectați celula H3 și apăsați F4 pentru a adăuga caracterele „$”.
Apăsați Enter
Scorul Z pentru prima valoare a fost calculat, rezultatul arată că aceasta se află cu 0.15945 abateri standard sub medie. Pentru a verifica, înmulțiți deviația standard cu acest rezultat (6.271629 * -0.15945) și comparați rezultatul cu diferența dintre valoare și medie (499-500). Cele două valori ar trebui să fie identice.
Pentru a calcula scorurile Z pentru restul valorilor, evidențiați întreaga coloană „Scor Z”, începând cu celula care conține formula.
Apăsați Ctrl+D, care va copia formula în toate celulele selectate.
Acum formula este aplicată tuturor celulelor, iar fiecare va face referire corect la celulele „AVERAGE” și „STDEV.S” sau „STDEV.P”, datorită caracterelor „$”. În caz de erori, verificați includerea caracterelor „$” în formula inițială.
Calculul Scorului Z fără celule „Ajutătoare”
Celulele auxiliare, care stochează rezultatele funcțiilor „MEDIE”, „STDEV.S” și „STDEV.P”, sunt utile, dar nu întotdeauna necesare. Acestea pot fi eliminate folosind formulele generalizate de mai jos:
Formula folosind „STDEV.S”:
=(Valoare-AVERAGE(Valori))/STDEV.S(Valori)
Formula folosind „STDEV.P”:
=(Valoare-AVERAGE(Valori))/STDEV.P(Valori)
Când introduceți intervalele de celule pentru „Valori”, asigurați-vă că adăugați referințe absolute („$” folosind F4) pentru a evita ca, la copierea formulei, să se calculeze media sau deviația standard a unui interval diferit de celule în fiecare formulă.
Pentru seturi mari de date, celulele ajutătoare sunt mai eficiente. Acestea evită recalcularea funcțiilor „AVERAGE” și „STDEV.S” sau „STDEV.P” de fiecare dată, economisind resurse de procesare și reducând timpul de calcul.
De asemenea, „$G$3” necesită mai puțin spațiu de stocare și memorie RAM decât „AVERAGE($E$3:$E$12)”. Acest aspect contează, mai ales la versiunile standard de 32 de biți de Excel, limitate la 2GB de RAM (versiunea de 64 de biți nu are o limită strictă în privința RAM-ului utilizabil).