Kaip apskaičiuoti „Z“ balą naudojant „Microsoft Excel“

„Z“ balas yra statistinė vertė, nurodanti, kiek standartinių nuokrypių tam tikra reikšmė yra iš viso duomenų rinkinio vidurkio. Galite naudoti AVERAGE ir STDEV.S arba STDEV.P formules, kad apskaičiuotumėte savo duomenų vidurkį ir standartinį nuokrypį, tada naudokite šiuos rezultatus kiekvienos vertės Z balui nustatyti.

Kas yra „Z“ balas ir ką veikia AVERAGE, STDEV.S ir STDEV.P funkcijos?

Z-balas yra paprastas būdas palyginti dvi skirtingų duomenų rinkinių vertes. Jis apibrėžiamas kaip standartinių nuokrypių, esančių nuo duomenų taško, skaičiaus. Bendra formulė atrodo taip:

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

Štai pavyzdys, padedantis paaiškinti. Pasakykite, kad norėjote palyginti dviejų skirtingų mokytojų mokomų Algebra studentų testų rezultatus. Jūs žinote, kad pirmasis studentas gavo 95% galutinio egzamino vienoje klasėje, o kitos klasės studentas pelnė 87%.

Iš pirmo žvilgsnio 95 proc. Klasė yra įspūdingesnė, bet ką daryti, jei antrosios klasės mokytojas patyrė sunkesnį egzaminą? Kiekvieno studento balo Z balą galite apskaičiuoti pagal kiekvienos klasės vidutinius balus ir kiekvienos klasės balų standartinį nuokrypį. Lyginant abiejų studentų Z-balus, buvo galima suprasti, kad studentas, turintis 87% balų, geriau, palyginti su likusia savo klasės dalimi, nei studentas, turintis 98% balų, palyginti su likusia savo klasės dalimi.

Pirmoji reikalinga statistinė vertė yra „vidurkis“ ir „Excel“ „AVERAGE“ funkcija apskaičiuoja šią vertę. Jis tiesiog prideda visas ląstelių diapazono vertes ir padalija šią sumą iš ląstelių, kuriose yra skaitmeninių reikšmių, skaičiaus (ji ignoruoja tuščias ląsteles).

Kita mums reikalinga statistinė vertė yra „standartinis nuokrypis“, o „Excel“ turi dvi skirtingas funkcijas, leidžiančias šiek tiek skirtingais būdais apskaičiuoti standartinį nuokrypį.

Ankstesnės „Excel“ versijos turėjo tik „STDEV“ funkciją, kuri apskaičiuoja standartinį nuokrypį, o duomenis tvarko kaip „gyventojų“ pavyzdį. „Excel 2010“ tai įveikė į dvi funkcijas, kurios apskaičiuoja standartinį nuokrypį:

• STDEV.S: Ši funkcija yra identiška ankstesnei „STDEV“ funkcijai. Jis apskaičiuoja standartinį nuokrypį, o duomenis tvarko kaip „gyventojų“ pavyzdį. Gyventojų pavyzdys gali būti kažkas panašaus į konkrečius uolus, surinktus mokslinių tyrimų projektui, arba automobilius, kurie buvo atidėti ir naudojami avarijų saugumo bandymams..
• STDEV.P: Ši funkcija apskaičiuoja standartinį nuokrypį, o duomenis tvarko kaip visą populiaciją. Visa populiacija būtų kažkas panašaus į visus Žemėje esančius uodus ar kiekvieną automobilį, esant konkrečiam modeliui.

Kuris pasirenkamas pagal jūsų duomenų rinkinį. Skirtumas paprastai būna mažas, tačiau „STDEV.P“ funkcijos rezultatas visada bus mažesnis už tos pačios duomenų grupės „STDEV.S“ funkcijos rezultatą. Dar labiau konservatyvus metodas manyti, kad duomenys yra įvairesni.

Pažvelkime į pavyzdį

Pavyzdžiui, turime dvi stulpelius („Vertės“ ir „Z-balas“) ir tris „pagalbininkų“ langelius „AVERAGE“, „STDEV.S“ ir „STDEV.P“ funkcijų saugojimui. Stulpelyje „Vertės“ yra dešimt atsitiktinių skaičių, suskirstytų į 500, o stulpelyje „Z-balas“, kur mes apskaičiuojame „Z“ balą naudojant rezultatus, išsaugotus „pagalbininko“ ląstelėse.

Pirma, mes apskaičiuosime vidutinę reikšmę naudojant „AVERAGE“ funkciją. Pasirinkite langelį, kuriame bus išsaugota funkcija „AVERAGE“.

Įveskite šią formulę ir paspauskite įveskite -arba - naudokite meniu „Formulės“.

= VIDUTINIS (E2: E13)

Norėdami pasiekti funkciją per meniu „Formulės“, pasirinkite išskleidžiamąjį meniu „Daugiau funkcijų“, pasirinkite parinktį „Statistiniai“ ir spustelėkite „AVERAGE“.

Lange „Funkcijų argumentai“ pasirinkite visas stulpelio „Vertės“ ląsteles kaip „Number1“ lauko įvestį. Jums nereikia nerimauti dėl „Number2“ lauko.

Dabar paspauskite „Gerai“.

Toliau reikia apskaičiuoti standartinį verčių nuokrypį naudojant „STDEV.S“ arba „STDEV.P“ funkciją. Šiame pavyzdyje parodysime, kaip apskaičiuoti abi reikšmes, pradedant nuo „STDEV.S“. Pasirinkite langelį, kuriame bus išsaugotas rezultatas.

Norint apskaičiuoti standartinį nuokrypį naudojant „STDEV.S“ funkciją, įveskite šią formulę ir paspauskite „Enter“ (arba atidarykite jį per „Formulės“ meniu).

= STDEV.S (E3: E12)

Norėdami pasiekti funkciją per meniu „Formulės“, pasirinkite išskleidžiamąjį meniu „Daugiau funkcijų“, pasirinkite parinktį „Statistiniai“, slinkite žemyn, tada spustelėkite „STDEV.S“ komandą.

Lange „Funkcijų argumentai“ pasirinkite visas stulpelio „Vertės“ ląsteles kaip „Number1“ lauko įvestį. Čia taip pat nereikia nerimauti dėl „Number2“ lauko.

Dabar paspauskite „Gerai“.

Toliau apskaičiuojame standartinį nuokrypį naudojant „STDEV.P“ funkciją. Pasirinkite langelį, kuriame bus išsaugotas rezultatas.

Norint apskaičiuoti standartinį nuokrypį naudojant „STDEV.P“ funkciją, įveskite šią formulę ir paspauskite „Enter“ (arba atidarykite jį per „Formulės“ meniu).

= STDEV.P (E3: E12)

Norėdami pasiekti funkciją per meniu „Formulės“, pasirinkite išskleidžiamąjį meniu „Daugiau funkcijų“, pasirinkite parinktį „Statistiniai“, slinkite žemyn, tada spustelėkite „STDEV.P“ formulę.

Lange „Funkcijų argumentai“ pasirinkite visas stulpelio „Vertės“ ląsteles kaip „Number1“ lauko įvestį. Vėlgi, jums nereikės nerimauti dėl „Number2“ lauko.

Dabar paspauskite „Gerai“.

Dabar, kai apskaičiuojame vidutinį ir standartinį mūsų duomenų nuokrypį, mes turime viską, ką reikia apskaičiuoti Z-balą. Galime naudoti paprastą formulę, kurioje nurodomos ląstelės, kuriose yra „AVERAGE“ ir „STDEV.S“ arba „STDEV.P“ funkcijų rezultatai.

„Z-Score“ stulpelyje pasirinkite pirmą langelį. Šiam pavyzdžiui naudosime „STDEV.S“ funkcijos rezultatą, tačiau taip pat galite naudoti „STDEV.P.“ Rezultatus.

Įveskite šią formulę ir paspauskite Enter:

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

Arba galite naudoti šiuos veiksmus, kad įvestumėte formulę vietoj rašymo:

1. Spustelėkite langelį F3 ir įveskite = (
2. Pasirinkite langelį E3. (Galite paspausti kairysis rodyklės klavišas vieną kartą arba naudokite pelę)
3. Įveskite minuso ženklą -
4. Pasirinkite langelį G3, tada paspauskite F4 pridėti „$“ simbolius, kad būtų galima padaryti „absoliučią“ nuorodą į langelį (jis pereis per „G3“> “$G$3 ">" G$3 ">"$G3 ">" G3 ", jei ir toliau spaudžiate F4)
5. Tipas ) /
6. Pasirinkite langelį H3 (arba I3, jei naudojate „STDEV.P“) ir paspauskite F4 pridėti du „$“ simbolius.
7. paspauskite ENTER

Z balas apskaičiuotas pirmajai vertei. Tai yra 0,155945 standartiniai nuokrypiai, žemesni už vidurkį. Norėdami patikrinti rezultatus, standartinį nuokrypį galite padauginti iš šio rezultato (6.271629 * -0.15945) ir patikrinti, ar rezultatas yra lygus skirtumui tarp vertės ir vidurkio (499-500). Abu rezultatai yra lygūs, todėl reikšmė yra prasminga.

Apskaičiuokite kitų vertybių Z-balus. Pažymėkite visą „Z-Score“ stulpelį, pradedant nuo ląstelės, kurioje yra formulė.

Paspauskite Ctrl + D, kuris kopijuoja formulę viršutinėje ląstelėje per visas kitas pasirinktas ląsteles.

Dabar formulė buvo „užpildyta“ visoms ląstelėms, ir kiekviena jų visada nurodys teisingas „AVERAGE“ ir „STDEV.S“ arba „STDEV.P“ ląsteles dėl „$“ simbolių. Jei gausite klaidų, grįžkite ir įsitikinkite, kad „$“ simboliai yra įtraukti į įvestą formulę.

Z balo skaičiavimas nenaudojant „Helper“ ląstelių

Pagalbinės ląstelės saugo rezultatą, pvz., Išsaugo „AVERAGE“, „STDEV.S“ ir „STDEV.P“ funkcijų rezultatus. Jie gali būti naudingi, bet ne visada būtini. Apskaičiuojant „Z“ balą galite juos visiškai praleisti naudodami toliau pateiktas apibendrintas formules.

Čia naudojama „STDEV.S“ funkcija:

= (Vertė-AVERAGE (reikšmės)) / STDEV.S (vertės)

Ir vieną, naudojant „STEV.P“ funkciją:

= (Vertė-AVERAGE (reikšmės)) / STDEV.P (reikšmės)

Įvedę į „Vertes“ elementų ląstelių diapazonus, būtinai pridėkite absoliučias nuorodas („$“ naudodami F4), kad kai „užpildysite“, nesate apskaičiuojate vidutinio ar vidutinio skirtingo diapazono nuokrypio kiekvienoje formulėje.

Jei turite didelį duomenų rinkinį, tai gali būti efektyviau naudoti pagalbines ląsteles, nes jis neatlieka „AVERAGE“ ir „STDEV.S“ arba „STDEV.P“ funkcijų rezultatų kiekvieną kartą, taupydamas procesoriaus išteklius ir pagreitinti laiko skaičiavimą.

Be to, „$ G $ 3“ saugo mažiau baitų ir mažiau atminties - nei „AVERAGE ($ E $ 3: $ E $ 12)“. Tai svarbu, nes standartinė 32 bitų „Excel“ versija yra apribota iki 2 GB RAM (64 bitų versija neturi jokių apribojimų, kiek RAM gali būti naudojama).