Sut i Gyfrifo Z-Scores yn Excel: Canllaw Cynhwysfawr
Ym maes ystadegau a dadansoddi data, mae deall sut mae'ch data'n cymharu â'r cyfartaledd yn hanfodol. Mae sgôr z, a elwir hefyd yn sgôr safonol, yn darparu ffordd o fesur pellter cymharol pwynt data o gymedr set ddata, wedi'i fynegi yn nhermau gwyriadau safonol. P'un a ydych chi'n dadansoddi sgoriau prawf, data ariannol, neu unrhyw set ddata rifiadol arall, gall cyfrifo sgorau z gynnig mewnwelediad dwfn i ymddygiad eich data.
Mae defnyddio Excel i gyfrifo sgorau z yn cynnig symlrwydd ac effeithlonrwydd, gan alluogi dadansoddiad cyflym o setiau data mawr ar gyfer cymharu safonedig a chanfod allanolion. Bydd y tiwtorial hwn yn eich arwain trwy ddeall beth yw sgôr z, sut i ddod o hyd iddo yn Excel, darparu enghreifftiau fformiwla, dehongli sgorau z yn eich data, a rhannu awgrymiadau pwysig i'w cofio wrth wneud y cyfrifiadau hyn. |
Beth yw sgôr z?
Mae sgôr z, a elwir hefyd yn sgôr safonol, yn fetrig ystadegol sy'n meintioli pellter pwynt data penodol o gymedr set ddata, wedi'i fynegi yn nhermau gwyriadau safonol. Mae'r mesuriad hwn yn hanfodol ar gyfer deall i ba raddau ac i ba gyfeiriad (uwchben neu is) y mae pwynt data yn gwyro oddi wrth werth cyfartalog y set ddata. Yn ei hanfod, mae sgôr z yn trawsnewid pwyntiau data i raddfa gyffredin, gan ganiatáu ar gyfer cymhariaeth syml ar draws gwahanol setiau data neu o fewn poblogaethau amrywiol, waeth beth fo'r graddfeydd mesuriad neu siapiau dosbarthiad gwreiddiol.
Mae cysylltiad agos rhwng y cysyniad o sgôr-z a'r dosraniad normal. Mae'r dosraniad normal yn gysyniad sylfaenol mewn ystadegau, sy'n cynrychioli dosbarthiad lle mae'r rhan fwyaf o arsylwadau'n clystyru o amgylch y brig canolog a'r tebygolrwydd y bydd gwerthoedd yn digwydd yn lleihau'n gymesur i'r ddau gyfeiriad o'r cymedr. Yng nghyd-destun dosraniad normal: |
- Mae tua 68% o'r data yn dod o fewn un gwyriad safonol (±1 z-sgôr) o'r cymedr, sy'n dynodi gwyriad cymedrol o'r cyfartaledd.
- Mae tua 95% o arsylwadau o fewn dau wyriad safonol (sgoriau ±2 z), gan ddangos gwyriad sylweddol ond nid eithafol.
- Mae bron i 99.7% o'r data i'w gael o fewn tri gwyriad safonol (sgoriau ±3 z), sy'n cwmpasu bron pob arsylwad o fewn dosbarthiad ac yn amlygu gwyriadau eithafol.
Mae’r sgôr-z yn arf hanfodol ar gyfer dadansoddi ystadegol, gan alluogi ymchwilwyr a dadansoddwyr i safoni arsylwadau unigol o wahanol setiau data, gan hwyluso’r gwaith o gymharu sgoriau o ddosbarthiadau gwahanol. Trwy drosi data i sgorau z, mae'n hawdd pennu pa mor anarferol neu nodweddiadol yw arsylwad penodol o fewn dosbarthiad penodol, gan ei wneud yn arf anhepgor ar gyfer cymwysiadau amrywiol, gan gynnwys canfod allanol, profi rhagdybiaeth, a normaleiddio data.
Sut i ddod o hyd i sgôr z yn Excel?
Yn Excel, nid oes un swyddogaeth benodol ar gyfer cyfrifiadura sgorau z yn uniongyrchol. Mae'r broses yn cynnwys cyfrifiadau cychwynnol o gymedr eich set ddata (μ) a gwyriad safonol (σ). Ar ôl cael yr ystadegau hanfodol hyn, mae gennych ddau brif ddull o bennu'r sgôr z:
- Dull Cyfrifo â Llaw: Cymhwyswch y fformiwla sgôr z:
=(x-μ)/σ
- lle:
- x yw'r pwynt data rydych chi'n ei archwilio,
μ yw cymedr eich set ddata,
σ yw gwyriad safonol eich set ddata. - Defnyddio'r Swyddogaeth SAFONI: Am ddull mwy integredig, Excel's SAFONI mae ffwythiant yn cyfrifo'r sgôr z yn uniongyrchol o ystyried y pwynt data, y cymedr, a'r gwyriad safonol fel mewnbynnau:
=STANDARDIZE(x, mean, standard_dev)
Enghreifftiau fformiwla i gyfrifo sgôr z yn Excel
Gan dybio bod gennych set ddata yng ngholofn A, yn ymestyn o gelloedd A2 i A101, dyma sut y byddech chi'n mynd ati i gyfrifo'r sgôr z ar gyfer y gwerthoedd hyn:
- Cyfrifwch y Cymedr (μ): Defnyddiwch y AVERAGE(ystod) swyddogaeth i ddod o hyd i'r cymedr (μ) o'ch set ddata.
=AVERAGE(A2:A101)
- Cyfrifwch y Gwyriad Safonol (σ): Dewiswch y fformiwla briodol yn seiliedig ar gyd-destun eich data.
Pwysig: Mae dewis y swyddogaeth gywir ar gyfer eich set ddata yn hanfodol ar gyfer sicrhau cyfrifiadau manwl gywir. (Ar gyfer fy nata yn A2: A101 gan gynrychioli’r boblogaeth gyfan, byddaf yn defnyddio’r fformiwla gyntaf.)
- Defnyddiwch y STDEV.P(ystod) swyddogaeth os yw'ch data yn cynrychioli'r boblogaeth gyfan (sy'n golygu nad oes grŵp mwy y mae'r gwerthoedd hyn yn cael eu samplu ohono).
=STDEV.P(A2:A101)
- Defnyddiwch y STDEV.S(ystod) swyddogaeth os yw eich data yn sampl o boblogaeth fwy neu os ydych am amcangyfrif y gwyriad safonol poblogaeth yn seiliedig ar eich sampl.
=STDEV.S(A2:A101)
- Defnyddiwch y STDEV.P(ystod) swyddogaeth os yw'ch data yn cynrychioli'r boblogaeth gyfan (sy'n golygu nad oes grŵp mwy y mae'r gwerthoedd hyn yn cael eu samplu ohono).
- Cyfrifwch y Sgôr Z ar gyfer Pwynt Data yn A2: Defnyddiwch y naill neu'r llall o'r fformiwlâu canlynol, a fydd yn rhoi'r un canlyniad. (Yn yr achos hwn, byddaf yn dewis yr ail fformiwla.)
- Cyfrifwch â llaw trwy dynnu'r cymedr o'r pwynt data a rhannu'r canlyniad hwn â'r gwyriad safonol.
=(A2 - $E$2) / $E$3
- Defnyddiwch y STANDARDIZE(x, cymedr, safon_dev) swyddogaeth.
=STANDARDIZE(A2, $E$2, $E$3)
Nodyn: Arwyddion y ddoler ($) dweud wrth y fformiwla i bob amser gyfeirio at gelloedd penodol (E2 am gymedr, E3 ar gyfer gwyriad safonol) ni waeth ble mae'r fformiwla wedi'i chopïo.
- Cyfrifwch â llaw trwy dynnu'r cymedr o'r pwynt data a rhannu'r canlyniad hwn â'r gwyriad safonol.
- Cyfrifwch Sgoriau Z ar gyfer Pob Gwerth yn Eich Set Ddata: Copïwch y fformiwla yng ngham 3 i lawr y golofn i gyfrifo sgorau z ar gyfer pob gwerth yn eich set ddata. Tip: Cliciwch ddwywaith ar ddolen llenwi'r gell i ymestyn y fformiwla yn gyflym.
- Er mwyn symleiddio eich cyfrifiad o sgorau z ar draws y set ddata gyfan heb deipio'n unigol fformiwlâu ar gyfer y gwyriad cymedrig a safonol mewn celloedd ar wahân, gallwch ddefnyddio'r naill neu'r llall o'r fformiwlâu cynhwysfawr canlynol yn uniongyrchol.
=(A2 - AVERAGE($A$2:$A$101)) / STDEV.P($A$2:$A$101)
=STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))
- Mae cynnal trachywiredd cyson trwy ddefnyddio tri lle degol ar gyfer sgorau z yn arfer canmoladwy mewn gwaith gwyddonol ac ystadegol. Cyflawnwch hyn trwy ddewis eich celloedd sgôr z a defnyddio'r Gostwng Degol opsiwn a geir yn y Nifer grŵp ar y Hafan tab.
Dehongli sgorau z mewn data
Mae dehongli sgorau z yn hanfodol i ddeall lleoliad ac arwyddocâd pwyntiau data o fewn set ddata. Mae sgôr z yn rhoi mesuriad uniongyrchol o faint o wyriadau safonol yw elfen o gymedr y set ddata, gan gynnig cipolwg ar ei safle cymharol a'i brinder.
Perthynas i'r Cymedrig
- Z-Sgôr = 0: Yn dangos perfformiad cyfartalog, gyda'r pwynt data yn union ar y cymedr.
- Sgôr Z > 0: Yn dynodi gwerthoedd uwch na'r cyfartaledd, gyda phellteroedd mwy o'r perfformiad signalau cymedrig cryfach.
- Sgôr Z < 0: Yn cynrychioli gwerthoedd is na'r cyfartaledd, lle mae sgorau is yn nodi gwyriad mwy islaw'r cymedr.
Gradd Gwyriad
- |Z-Sgôr| < 1: Mae'r pwyntiau data hyn yn agos at gyfartaledd, yn dod o fewn y prif gorff data mewn dosbarthiad arferol, gan signalu perfformiad safonol.
- |Z-Sgôr| < 2: Yn awgrymu gwyriad cymedrol oddi wrth y cymedr, gan nodi arsylwadau fel rhai anghyffredin ond dal o fewn ystod arferol o amrywiant.
- |Z-Sgôr| >2: Yn amlygu pwyntiau data anarferol yn sylweddol bell o'r cymedr, gan awgrymu o bosibl allgleifion neu wyriadau sylweddol oddi wrth y norm disgwyliedig.
Esboniad enghreifftiol:
- Mae sgôr z o 0.66 yn golygu bod y pwynt data yn 0.66 gwyriad safonol uwchlaw'r cymedr. Mae hyn yn dangos bod y gwerth yn uwch na'r cyfartaledd ond yn dal yn gymharol agos ato, gan ddod o fewn yr ystod amrywiad nodweddiadol.
- I'r gwrthwyneb, mae sgôr z o -2.1 yn dynodi bod y pwynt data 2.1 gwyriad safonol islaw'r cymedr. Mae'r gwerth hwn yn sylweddol is na'r cyfartaledd, sy'n dangos ei fod ymhellach i ffwrdd o'r ystod nodweddiadol.
Pethau i'w cofio wrth gyfrifo sgorau z yn Excel
Wrth ddefnyddio Excel i gyfrifo sgorau z, mae manwl gywirdeb a chywirdeb yn hollbwysig. Mae yna ystyriaethau hanfodol i'w cadw mewn cof er mwyn sicrhau dibynadwyedd eich canlyniadau:
- Gwiriwch am ddosbarthiad arferol: Mae sgorau Z yn fwyaf effeithiol ar gyfer data sy'n cadw at ddosraniad arferol. Os nad yw eich set ddata yn dilyn y dosbarthiad hwn, efallai na fydd sgorau z yn arf dadansoddol priodol. Ystyriwch gynnal prawf normalrwydd cyn defnyddio dadansoddiad sgôr z.
- Sicrhau defnydd cywir o fformiwla: Sicrhewch eich bod yn dewis y swyddogaeth gwyriad safonol cywir - STDEV.P ar gyfer poblogaethau cyfan a STDEV.S ar gyfer samplau - yn seiliedig ar nodweddion eich set ddata.
- Defnyddiwch gyfeiriadau absoliwt ar gyfer gwyriad cymedrig a safonol: Wrth gymhwyso fformiwlâu ar draws celloedd lluosog, defnyddiwch gyfeiriadau absoliwt (ee, $ A $ 1) ar gyfer gwyriad cymedrig a safonol yn eich fformiwla sgôr-z i sicrhau cysondeb ar draws cyfrifiadau.
- Byddwch yn ofalus o allgleifion: Mae allgleifion yn cael effaith sylweddol ar y gwyriad cymedrig a safonol, gan o bosibl ystumio'r sgorau z a gyfrifwyd.
- Sicrhau cywirdeb data: Cyn cyfrifo sgorau z, sicrhewch fod eich set ddata yn lân ac yn rhydd o wallau. Gall cofnodion data anghywir, dyblygiadau, neu werthoedd amherthnasol effeithio'n sylweddol ar y gwyriad cymedrig a safonol, gan arwain at sgorau z camarweiniol.
- Osgoi talgrynnu na blaendori cynamserol: Gall Excel drin nifer sylweddol o leoedd degol, a gall cadw'r rhain atal gwallau talgrynnu cronnus a allai ystumio'ch dadansoddiad terfynol.
Uchod mae'r holl gynnwys perthnasol sy'n ymwneud â chyfrifo sgorau z yn Excel. Gobeithio y bydd y tiwtorial yn ddefnyddiol i chi. Os ydych chi am archwilio mwy o awgrymiadau a thriciau Excel, os gwelwch yn dda cliciwch yma i gael mynediad at ein casgliad helaeth o dros filoedd o sesiynau tiwtorial.
Yr Offer Cynhyrchedd Swyddfa Gorau
Kutools for Excel - Yn Eich Helpu i Sefyll Allan O Dyrfa
Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...
Tab Office - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys Excel)
- Un eiliad i newid rhwng dwsinau o ddogfennau agored!
- Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
- Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
- Yn dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Just Like Chrome, Edge a Firefox.