Z-skoori arvutamine Microsoft Exceli abil

Z-skoor on statistiline väärtus, mis ütleb teile, kui palju standardhälbeid konkreetne väärtus juhtub kogu andmekogumi keskmisest. Andmete keskmise ja standardhälbe arvutamiseks saate kasutada AVERAGE ja STDEV.S või STDEV.P valemeid ning seejärel kasutada neid tulemusi iga väärtuse Z-skoori määramiseks.

Mis on Z-skoor ja mida AVERAGE, STDEV.S ja STDEV.P funktsioonid teevad?

Z-skoor on lihtne viis kahe erineva andmekogumi väärtuste võrdlemiseks. Seda määratletakse kui standardhälbeid, mis jäävad andmepunkti keskmisest kõrvale. Üldine valem näeb välja selline:

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

Siin on näide, mis aitab selgitada. Ütle, et soovid võrrelda kahe algebra õpilase katsetulemusi, mida õpetavad erinevad õpetajad. Te teate, et esimene õpilane sai 95% lõplikust eksamist ühes klassis ja teise klassi õpilane sai 87%.

Esmapilgul on 95% -line hinne muljetavaldavam, aga mis siis, kui teise klassi õpetaja andis raskema eksami? Saate iga õpilase skoori Z-skoori arvutada iga klassi keskmise skoori ja iga klassi skooride standardhälbe alusel. Võrreldes kahe õpilase Z-skoori, võib selguda, et 87% skooriga üliõpilane oli võrreldes oma ülejäänud klassiga parem, kui 98% skooriga õpilane võrreldes ülejäänud klassiga.

Esimene statistiline väärtus, mida vajate, on 'keskmine' ja Excel 'AVERAGE' funktsioon arvutab selle väärtuse. See lihtsalt lisab kõik rakupiirkonnas olevad väärtused ja jagab selle summa arvulisi väärtusi sisaldavate rakkude arvuga (see ignoreerib tühje lahtreid).

Teine statistiline väärtus, mida vajame, on standardhälve ja Excelil on kaks erinevat funktsiooni standardhälbe arvutamiseks veidi erinevalt.

Eelmistel Exceli versioonidel oli ainult "STDEV" funktsioon, mis arvutab standardhälbe, käsitledes andmeid populatsiooni "proovina". Excel 2010 murdis selle kaheks funktsiooniks, mis arvutavad standardhälbe:

STDEV.S: See funktsioon on identne eelmise “STDEV” funktsiooniga. See arvutab standardhälbe, käsitledes andmeid populatsiooni „valimina”. Rahvastiku valim võib olla midagi sellist, mis on uurimisprojekti jaoks kogutud konkreetsed sääsed või autod, mis olid kõrvale jäetud ja mida kasutati õnnetuste ohutuse katsetamiseks..
STDEV.P: See funktsioon arvutab standardhälbe, käsitledes andmeid kogu elanikkonnana. Kogu elanikkond oleks konkreetse mudeli tootmisprotsessis midagi sellist, nagu kõik Maa sääsked või kõik autod.

Mis valite, põhineb teie andmekogumil. Erinevus on tavaliselt väike, kuid “STDEV.P” funktsiooni tulemus on alati väiksem kui sama andmekogu “STDEV.S” funktsiooni tulemus. On konservatiivsem lähenemine eeldada, et andmete varieeruvus on suurem.

Vaatame näiteks

Meie näites on meil kaks veergu („Väärtused” ja „Z-skoor”) ja kolm „abistaja” lahtrit, et salvestada “AVERAGE”, “STDEV.S” ja “STDEV.P” funktsioonide tulemusi. Veerus „Väärtused” on kümme juhuslikku numbrit, mis on koondatud umbes 500-ni, ja veerus „Z-skoor”, kus me arvutame Z-skoori, kasutades „abistaja” lahtrites salvestatud tulemusi.

Esiteks arvutame väärtuste keskmise, kasutades “AVERAGE” funktsiooni. Valige lahtr, kuhu salvestate funktsiooni “AVERAGE” tulemus.

Sisestage järgmine valem ja vajutage sisenema -või - kasutage menüüd „Valemid“.

= AVERAGE (E2: E13)

Funktsiooni avamiseks menüüst „Valemid“ valige rippmenüüst „Rohkem funktsioone“, valige suvand „Statistiline“ ja seejärel klõpsake “AVERAGE”.

Valige funktsiooni Argumentide aknas kõik väärtused veerus „Väärtused“ lahtri “Number1” sisendiks. Te ei pea muretsema “Number2” väljaga.

Nüüd vajuta "OK".

Järgmiseks peame arvutama väärtuste standardhälbe kas “STDEV.S” või “STDEV.P” funktsiooni abil. Selles näites näitame teile, kuidas arvutada mõlemad väärtused, alustades “STDEV.S.” -St. Valige lahtrisse, kuhu tulemus salvestatakse.

Standardhälbe arvutamiseks funktsiooni “STDEV.S” abil sisestage see valem ja vajutage Enter (või avage see menüü „Valemid“ kaudu).

= STDEV.S (E3: E12)

Funktsioonide avamiseks menüü „Valemid“ kaudu valige rippmenüüst „Rohkem funktsioone“, valige suvand „Statistiline“, kerige veidi alla ja seejärel klõpsake käsku „STDEV.S”.

Valige funktsiooni Argumentide aknas kõik väärtused veerus „Väärtused“ lahtri “Number1” sisendiks. Samuti ei pea te siin muretsema välja “Number2”.

Nüüd vajuta "OK".

Seejärel arvutame standardhälbe, kasutades funktsiooni “STDEV.P”. Valige lahtr, kuhu tulemus salvestatakse.

Standardhälbe arvutamiseks funktsiooni „STDEV.P” abil sisestage see valem ja vajutage Enter (või avage see menüü „Valemid“ kaudu).

= STDEV.P (E3: E12)

Funktsioonide avamiseks menüüs „Valemid“ valige rippmenüüst „Rohkem funktsioone“, valige suvand „Statistiline“, kerige veidi alla ja seejärel klõpsake „STDEV.P” valemit.

Valige funktsiooni Argumentide aknas kõik väärtused veerus „Väärtused“ lahtri “Number1” sisendiks. Jällegi ei pea te muretsema “Number2” väljaga.

Nüüd vajuta "OK".

Nüüd, kui oleme arvutanud meie andmete keskmise ja standardhälbe, on meil kõik, mida vajame Z-skoori arvutamiseks. Me saame kasutada lihtsat valemit, mis viitab rakkudele, mis sisaldavad funktsioone AVERAGE ja STDEV.S või STDEV.P.

Valige veerus “Z-Score” esimene element. Selle näite puhul kasutame funktsiooni "STDEV.S" tulemust, kuid võite kasutada ka tulemust "STDEV.P."

Sisestage järgmine valem ja vajuta Enter:

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

Teise võimalusena saate valemit sisestamise asemel kasutada järgmiste sammudega:

Klõpsake lahtrit F3 ja tüüp = (
Valige lahtr E3. (Võite vajutada vasak-nooleklahv või kasutage hiirt)
Tippige miinusmärk -
Valige lahtr G3, seejärel vajutage F4 "$" tähemärkide lisamiseks "absoluutseks" viiteks lahtrile (see liigub läbi "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 ", kui vajutate jätkuvalt F4)
Tüüp ) /
Valige lahtr H3 (või I3, kui kasutate “STDEV.P”) ja vajutage F4 kahe "$" tähemärgi lisamiseks.
Vajutage Enter

Z-skoor on arvutatud esimese väärtuse jaoks. See on keskmisest allapoole 0,15945 standardhälvet. Tulemuste kontrollimiseks saate korrigeerida standardhälvet selle tulemusega (6.271629 * -0.15945) ja kontrollida, kas tulemus on võrdne väärtuse ja keskmise (499-500) vahega. Mõlemad tulemused on võrdsed, nii et väärtus on mõttekas.

Arvutame ülejäänud väärtuste Z-skoorid. Tõstke esile kogu "Z-Score" veerg, alustades valemiga sisalduvast lahtrist.

Vajutage Ctrl + D, mis kopeerib valemi ülemisse lahtrisse läbi kõigi teiste valitud lahtrite.

Nüüd on valem kõikidele rakkudele "täidetud" ja igaüks viitab alati õige "AVERAGE" ja "STDEV.S" või "STDEV.P" rakkudele "$" tähemärkide tõttu. Kui teil tekib vigu, mine tagasi ja veenduge, et sisestatavas valemis sisalduvad "$" tähemärgid.

Z-skoori arvutamine ilma "Helper" rakke kasutamata

Abistajarakud salvestavad tulemuse, nagu need, mis salvestavad “AVERAGE”, “STDEV.S” ja “STDEV.P” funktsioonide tulemusi. Need võivad olla kasulikud, kuid mitte alati vajalikud. Z-skoori arvutamisel saate neid üldjuhul vahele jätta, kasutades järgmisi üldistatud valemeid.

Siin on üks funktsioon STDEV.S:

= (Väärtus-AVERAGE (väärtused)) / STDEV.S (väärtused)

Ja teine funktsioon „STEV.P”:

= (Väärtus-AVERAGE (väärtused)) / STDEV.P (väärtused)

Kui sisestate funktsioonide „Väärtuste” lahtrivahemikke, lisage kindlasti absoluutsed viited („$” F4 abil), nii et kui te „täitke alla”, siis ei arvutata teistsuguse vahemiku keskmist või standardhälvet rakkude igas valemis.

Kui teil on suur andmekogum, võib osutuda kasulikumaks abistajarakkude kasutamine, sest see ei arvuta iga kord AVERAGE ja STDEV.S või STDEV.P funktsioonide tulemusi, säästes protsessori ressursse ja tulemuste arvutamiseks kuluva aja kiirendamine.

Samuti võtab „$ G $ 3” salvestamiseks vähem baite ja vähem laaditavat RAM-i kui “AVERAGE ($ E $ 3: $ E $ 12)”. See on oluline, sest standardne 32-bitine Exceli versioon on piiratud 2 GB RAM-iga (64-bitisel versioonil ei ole mingeid piiranguid, kui palju RAM-i saab kasutada).