Paano Makalkula ang isang Z-Score Gamit ang Microsoft Excel

Ang isang Z-Score ay isang halagang pang-istatistika na nagsasabi sa iyo kung gaano karaming mga karaniwang paglihis ng isang partikular na halaga ang nagmula sa ibig sabihin ng buong hanay ng data. Maaari mong gamitin ang mga pormula ng AVERAGE at STDEV.S o STDEV.P upang makalkula ang mean at karaniwang paglihis ng iyong data at pagkatapos ay gamitin ang mga resulta upang matukoy ang Z-Score ng bawat halaga.

Ano ang isang Z-Score at ano ang ginagawa ng mga pagpapaandar ng AVERAGE, STDEV.S, at STDEV.P?

Ang isang Z-Score ay isang simpleng paraan ng paghahambing ng mga halaga mula sa dalawang magkakaibang mga hanay ng data. Ito ay tinukoy bilang ang bilang ng mga karaniwang paglihis na malayo sa ibig sabihin ng isang data point na namamalagi. Ganito ang pangkalahatang pormula:

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

Narito ang isang halimbawa upang makatulong na linawin. Sabihin na nais mong ihambing ang mga resulta sa pagsubok ng dalawang mag-aaral ng Algebra na itinuro ng iba't ibang mga guro. Alam mo na ang unang mag-aaral ay nakakuha ng 95% sa huling pagsusulit sa isang klase, at ang mag-aaral sa kabilang klase ay nakakuha ng 87%.

Sa unang tingin, ang 95% na marka ay mas kahanga-hanga, ngunit paano kung ang guro ng pangalawang klase ay nagbigay ng isang mas mahirap na pagsusulit? Maaari mong kalkulahin ang Z-Score ng bawat marka ng mag-aaral batay sa average na mga marka sa bawat klase at ang karaniwang paglihis ng mga marka sa bawat klase. Ang paghahambing ng mga Z-Marka ng dalawang mag-aaral ay maaaring isiwalat na ang mag-aaral na may markang 87% ay mas mahusay na nagawa kumpara sa natitirang bahagi ng kanilang klase kaysa sa nag-aaral na may 98% na iskor sa paghahambing sa natitirang bahagi ng kanilang klase.

Ang unang halagang pang-istatistika na kailangan mo ay ang 'ibig sabihin' at ang function na "AVERAGE" ng Excel ay kinakalkula ang halagang iyon. Ito ay nagdadagdag lamang ng lahat ng mga halaga sa isang saklaw ng cell at hinahati sa kabuuan ng bilang ng mga cell na naglalaman ng mga halagang bilang (hindi pinapansin ang mga blangko na cell).

Ang iba pang halagang pang-istatistika na kailangan namin ay ang 'karaniwang paglihis' at ang Excel ay may dalawang magkakaibang pag-andar upang makalkula ang karaniwang paglihis sa bahagyang magkakaibang pamamaraan.

Ang mga nakaraang bersyon ng Excel ay mayroon lamang function na "STDEV", na kinakalkula ang karaniwang paglihis habang tinatrato ang data bilang isang 'sample' ng isang populasyon. Sinira iyon ng Excel 2010 sa dalawang pag-andar na kinakalkula ang karaniwang paglihis:

STDEV.S: Ang pagpapaandar na ito ay magkapareho sa nakaraang pag-andar ng "STDEV". Kinakalkula nito ang karaniwang paglihis habang tinatrato ang data bilang isang 'sample' ng isang populasyon. Ang isang sample ng isang populasyon ay maaaring isang bagay tulad ng mga partikular na lamok na nakolekta para sa isang proyekto sa pagsasaliksik o mga kotse na itinabi at ginamit para sa pagsubok sa kaligtasan ng pag-crash.
STDEV.P: Kinakalkula ng pagpapaandar na ito ang karaniwang paglihis habang tinatrato ang data bilang buong populasyon. Ang isang buong populasyon ay magiging isang bagay tulad ng lahat ng mga lamok sa Earth o bawat kotse sa isang pagpapatakbo ng isang tiyak na modelo.

Alin ang pipiliin mo ay batay sa iyong hanay ng data. Karaniwan ay magiging maliit ang pagkakaiba, ngunit ang resulta ng pag-andar na "STDEV.P" ay palaging magiging mas maliit kaysa sa resulta ng pag-andar ng "STDEV.S" para sa parehong hanay ng data. Ito ay isang mas konserbatibong diskarte upang ipagpalagay na mayroong higit na pagkakaiba-iba sa data.

Tingnan natin ang isang Halimbawa

Para sa aming halimbawa, mayroon kaming dalawang mga haligi ("Mga Halaga" at "Z-Score") at tatlong mga "helper" na cell para sa pagtatago ng mga resulta ng mga pagpapaandar na "AVERAGE," "STDEV.S," at "STDEV.P". Naglalaman ang haligi ng "Mga Halaga" ng sampung mga random na numero na nakasentro sa paligid ng 500, at ang haligi na "Z-Score" ay kung saan makakalkula namin ang Z-Score gamit ang mga resulta na nakaimbak sa mga 'helper' cells.

Una, makakalkula namin ang ibig sabihin ng mga halagang ginagamit ang pagpapaandar na "AVERAGE". Piliin ang cell kung saan mo iimbak ang resulta ng pag-andar na "AVERAGE".

I-type ang sumusunod na formula at pindutin pasok -o- gamitin ang menu na "Mga Formula".

= AVERAGE (E2: E13)

Upang ma-access ang pagpapaandar sa pamamagitan ng menu na "Mga Formula", piliin ang drop-down na "Higit pang Mga Pag-andar", piliin ang opsyong "Istatistika", at pagkatapos ay mag-click sa "AVERAGE."

Sa window ng Function Arguments, piliin ang lahat ng mga cell sa haligi ng "Mga Halaga" bilang input para sa patlang na "Number1". Hindi mo kailangang magalala tungkol sa patlang na "Number2".

Pindutin ngayon ang "OK."

Susunod, kailangan nating kalkulahin ang karaniwang paglihis ng mga halaga gamit ang alinman sa "STDEV.S" o "STDEV.P" na pagpapaandar. Sa halimbawang ito, ipapakita namin sa iyo kung paano makalkula ang parehong halaga, nagsisimula sa "STDEV.S." Piliin ang cell kung saan maiimbak ang resulta.

Upang makalkula ang karaniwang paglihis gamit ang pagpapaandar na "STDEV.S", i-type ang pormulang ito at pindutin ang Enter (o i-access ito sa pamamagitan ng menu na "Mga Formula").

= STDEV.S (E3: E12)

Upang ma-access ang pagpapaandar sa pamamagitan ng menu na "Mga Formula", piliin ang drop-down na "Higit pang Mga Pag-andar", piliin ang pagpipiliang "Istatistika", mag-scroll pababa nang kaunti, at pagkatapos ay i-click ang utos na "STDEV.S".

Sa window ng Function Arguments, piliin ang lahat ng mga cell sa haligi ng "Mga Halaga" bilang input para sa patlang na "Number1". Hindi mo kailangang magalala tungkol sa patlang na "Number2" dito, alinman.

Pindutin ngayon ang "OK."

Susunod, makakalkula namin ang karaniwang paglihis gamit ang "STDEV.P" na pagpapaandar. Piliin ang cell kung saan maiimbak ang resulta.

Upang makalkula ang karaniwang paglihis gamit ang pag-andar ng "STDEV.P", i-type ang pormulang ito at pindutin ang Enter (o i-access ito sa pamamagitan ng menu na "Mga Formula").

= STDEV.P (E3: E12)

Sa window ng Function Arguments, piliin ang lahat ng mga cell sa haligi ng "Mga Halaga" bilang input para sa patlang na "Number1". Muli, hindi mo kakailanganing mag-alala tungkol sa patlang na "Number2".

Pindutin ngayon ang "OK."

Ngayon na nakalkula namin ang ibig sabihin at karaniwang paglihis ng aming data, mayroon kaming lahat na kailangan upang makalkula ang Z-Score. Maaari kaming gumamit ng isang simpleng pormula na tumutukoy sa mga cell na naglalaman ng mga resulta ng "AVERAGE" at "STDEV.S" o "STDEV.P" na mga pagpapaandar.

Piliin ang unang cell sa haligi na "Z-Score". Gagamitin namin ang resulta ng pagpapaandar na "STDEV.S" para sa halimbawang ito, ngunit maaari mo ring gamitin ang resulta mula sa "STDEV.P."

I-type ang sumusunod na formula at pindutin ang Enter:

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

Bilang kahalili, maaari mong gamitin ang mga sumusunod na hakbang upang ipasok ang formula sa halip na mag-type:

I-click ang cell F3 at i-type =(
Piliin ang cell E3. (Maaari mong pindutin ang kaliwang-arrow-key isang beses o gamitin ang mouse)
I-type ang minus sign -
Piliin ang cell G3 pagkatapos ay pindutin F4 upang idagdag ang mga character na "$" upang makagawa ng isang 'absolute' na sanggunian sa cell (tatakbo ito sa pamamagitan ng "G3"> "$G$3 ″> “G$3″ > “$G3 ″> "G3" kung magpapatuloy ka sa pagpindot F4)
Uri )/
Piliin ang cell H3 (o I3 kung gumagamit ka ng "STDEV.P") at pindutin F4 upang idagdag ang dalawang character na "$".
pindutin ang enter

Ang Z-Score ay kinakalkula para sa unang halaga. Ito ay 0.15945 karaniwang mga paglihis sa ibaba ng ibig sabihin. Upang suriin ang mga resulta, maaari mong i-multiply ang karaniwang paglihis sa resulta na ito (6.271629 * -0.15945) at suriin na ang resulta ay katumbas ng pagkakaiba sa pagitan ng halaga at ng mean (499-500). Ang parehong mga resulta ay pantay, kaya may halaga ang halaga.

Kalkulahin natin ang Z-Scores ng natitirang mga halaga. I-highlight ang buong haligi ng 'Z-Score' na nagsisimula sa cell na naglalaman ng formula.

Pindutin ang Ctrl + D, kung saan kinokopya ang formula sa tuktok na cell pababa sa lahat ng iba pang napiling mga cell.

Ngayon ang formula ay 'napunan-down' sa lahat ng mga cell, at ang bawat isa ay palaging magre-refer ng tamang "AVERAGE" at "STDEV.S" o "STDEV.P" cells dahil sa mga character na "$". Kung nakakuha ka ng mga error, bumalik at tiyakin na ang mga character na "$" ay kasama sa pormula na iyong ipinasok.

Kinakalkula ang Z-Score nang hindi gumagamit ng 'Helper' Cells

Ang mga cell ng helper ay nag-iimbak ng isang resulta, tulad ng mga nag-iimbak ng mga resulta ng mga function na "AVERAGE," "STDEV.S," at "STDEV.P". Maaari silang maging kapaki-pakinabang ngunit hindi palaging kinakailangan. Maaari mong laktawan silang kabuuan kapag kinakalkula ang isang Z-Score sa pamamagitan ng paggamit ng mga sumusunod na pangkalahatang pormula, sa halip.

Narito ang isa na gumagamit ng pagpapaandar na "STDEV.S":

= (Halaga-AVERAGE (Mga Halaga)) / STDEV.S (Mga Halaga)

At ang isa na gumagamit ng pagpapaandar na "STEV.P":

= (Halaga-AVERAGE (Mga Halaga)) / STDEV.P (Mga Halaga)

Kapag pumapasok sa mga saklaw ng cell para sa mga "Mga Halaga" sa mga pag-andar, tiyaking magdagdag ng ganap na mga sanggunian ("$" gamit ang F4) upang kapag "pinunan" mo hindi mo kinakalkula ang average o karaniwang paglihis ng ibang saklaw ng mga cell sa bawat pormula.

Kung mayroon kang isang malaking hanay ng data, maaaring mas mahusay itong gumamit ng mga helper cell dahil hindi nito kinakalkula ang resulta ng paggana ng "AVERAGE" at "STDEV.S" o "STDEV.P" sa bawat oras, nagse-save ng mga mapagkukunan ng processor at pinapabilis ang oras na kinakailangan upang makalkula ang mga resulta.

Gayundin, ang "$ G $ 3" ay kukuha ng mas kaunting mga byte upang maiimbak at mas mababa ang RAM upang mai-load kaysa sa "AVERAGE ($ E $ 3: $ E $ 12).". Ito ay mahalaga dahil ang karaniwang 32-bit na bersyon ng Excel ay limitado sa 2GB ng RAM (ang bersyon ng 64-bit ay walang anumang mga limitasyon sa kung magkano ang maaaring magamit ng RAM).

agwebrunner.com