Kako izračunati Z-Score z uporabo Microsoft Excel

Z-Score je statistična vrednost, ki vam pove, koliko standardnih odstopanj je določena vrednost iz povprečja celotnega nabora podatkov. Za izračun srednje vrednosti in standardnega odklona vaših podatkov lahko uporabite formule AVERAGE in STDEV.S ali STDEV.P, nato pa uporabite te rezultate, da določite Z-oceno vsake vrednosti.

Kaj je Z-ocena in kaj delajo funkcije AVERAGE, STDEV.S in STDEV.P?

Z-Score je preprost način primerjanja vrednosti iz dveh različnih podatkovnih nizov. Opredeljen je kot število standardnih odstopanj od povprečja podatkovne točke. Splošna formula izgleda takole:

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

Tukaj je primer za pojasnitev. Recimo, da ste želeli primerjati rezultate dveh študentov, ki jih poučujejo različni učitelji. Veste, da je prvi študent dobil 95% na zaključnem izpitu v enem razredu, študent v drugem razredu pa 87%..

Na prvi pogled je 95-odstotna ocena bolj impresivna, toda kaj, če je učitelj drugega razreda dal težji izpit? Oceno Z lahko izračunate na podlagi rezultatov vsakega učenca na podlagi povprečnih točk v vsakem razredu in standardnega odstopanja rezultatov v vsakem razredu. Če primerjamo Z-ocene obeh študentov, lahko ugotovimo, da je učenec z 87% točkami dosegel boljše rezultate v primerjavi s preostalim razredom kot študent z 98% točkami v primerjavi s preostalim razredom..

Prva statistična vrednost, ki jo potrebujete, je "mean" in Excelova funkcija "AVERAGE" izračuna to vrednost. Preprosto sešteje vse vrednosti v obsegu celic in to vsoto deli s številom celic, ki vsebujejo številske vrednosti (ignorira prazne celice).

Druga statistična vrednost, ki jo potrebujemo, je "standardni odklon" in Excel ima dve različni funkciji za izračun standardnega odstopanja na nekoliko drugačne načine..

Prejšnje različice programa Excel so imele samo funkcijo »STDEV«, ki izračunava standardni odklon, medtem ko podatke obravnava kot »vzorec« populacije. Excel 2010 je to prelomil v dve funkciji, ki izračunata standardno odstopanje:

STDEV.S: Ta funkcija je enaka prejšnji funkciji »STDEV«. Izračuna standardni odklon, medtem ko podatke obravnava kot „vzorec“ populacije. Vzorec populacije je lahko nekaj podobnega kot posamezni komarji, zbrani za raziskovalni projekt, ali avtomobile, ki so bili rezervirani in uporabljeni za testiranje varnosti v trku..
STDEV.P: Ta funkcija izračuna standardni odklon, medtem ko podatke obravnava kot celotno populacijo. Celotna populacija bi bila nekaj podobnega vsem komarjem na Zemlji ali vsakemu avtomobilu v proizvodnem ciklu določenega modela.

Na podlagi podatkovnega niza izberete. Razlika je navadno majhna, vendar bo rezultat funkcije “STDEV.P” vedno manjši od rezultata funkcije “STDEV.S” za isti nabor podatkov. To je bolj konzervativen pristop, ki predvideva večjo variabilnost podatkov.

Poglejmo primer

Za naš primer imamo dva stolpca (»Vrednosti« in »Z-Score«) in tri »pomožne« celice za shranjevanje rezultatov funkcij »AVERAGE«, »STDEV.S« in »STDEV.P«. Stolpec »Vrednosti« vsebuje deset naključnih številk s središčem okrog 500, stolpec »Z-Score« pa izračunamo Z-oceno z rezultati, shranjenimi v celicah »pomočnik«..

Najprej izračunamo povprečje vrednosti s funkcijo »AVERAGE«. Izberite celico, kjer boste shranili rezultat funkcije »AVERAGE«.

Vnesite naslednjo formulo in pritisnite vnesite -ali - uporabite meni »Formule«.

= POVPREČINA (E2: E13)

Za dostop do funkcije prek menija »Formule« izberite spustni meni »Več funkcij«, izberite možnost »Statistični podatki« in kliknite »POVPREČNO«.

V oknu Funkcija Argumenti izberite vse celice v stolpcu »Vrednosti« kot vnos za polje »Število1«. Ni vam treba skrbeti za polje "Number2".

Sedaj pritisnite »OK«.

Nato moramo izračunati standardni odklon vrednosti z uporabo funkcije "STDEV.S" ali "STDEV.P". V tem primeru vam bomo pokazali, kako izračunati obe vrednosti, začenši s »STDEV.S.« Izberite celico, v kateri bo shranjen rezultat.

Če želite izračunati standardno odstopanje s funkcijo »STDEV.S«, vnesite to formulo in pritisnite Enter (ali dostopate do nje prek menija »Formule«)..

= STDEV.S (E3: E12)

Za dostop do funkcije v meniju »Formule« izberite spustni meni »Več funkcij«, izberite možnost »Statistični podatki«, pomaknite se navzdol in kliknite ukaz »STDEV.S«..

V oknu Funkcija Argumenti izberite vse celice v stolpcu »Vrednosti« kot vnos za polje »Število1«. Prav tako vam ni treba skrbeti za polje "Number2".

Sedaj pritisnite »OK«.

Nadalje bomo s funkcijo “STDEV.P” izračunali standardni odmik. Izberite celico, v kateri bo shranjen rezultat.

Za izračun standardnega odklona s funkcijo “STDEV.P” vnesite to formulo in pritisnite Enter (ali dostopate do nje prek menija “Formule”)..

= STDEV.P (E3: E12)

Za dostop do funkcije prek menija »Formule« izberite spustni meni »Več funkcij«, izberite možnost »Statistični podatki«, pomaknite se navzdol in kliknite gumb »STDEV.P«..

V oknu Funkcija Argumenti izberite vse celice v stolpcu »Vrednosti« kot vnos za polje »Število1«. Še enkrat vam ne bo treba skrbeti za polje "Number2".

Sedaj pritisnite »OK«.

Zdaj, ko smo izračunali povprečno in standardno odstopanje naših podatkov, imamo vse, kar potrebujemo za izračun Z-vrednosti. Lahko uporabimo preprosto formulo, ki se nanaša na celice, ki vsebujejo rezultate funkcij »AVERAGE« in »STDEV.S« ali »STDEV.P«..

Izberite prvo celico v stolpcu »Z-Score«. Za ta primer bomo uporabili rezultat funkcije »STDEV.S«, vendar lahko uporabite tudi rezultat »STDEV.P«.

Vnesite naslednjo formulo in pritisnite Enter:

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

Lahko pa uporabite naslednje korake za vnos formule, namesto da vnesete:

Kliknite celico F3 in vnesite = (
Izberite celico E3. (Lahko pritisnete tipko puščična tipka levo enkrat ali z miško)
Vnesite znak minus -
Izberite celico G3 in pritisnite F4 za dodajanje znakov »$« za izdelavo »absolutnega« sklicevanja na celico (cikel bo skozi »G3«> »$G$3 ">" G$3 ">"$G3 ">" G3 ", če nadaljujete s pritiskanjem F4)
Vrsta ) /
Izberite celico H3 (ali I3, če uporabljate »STDEV.P«) in pritisnite F4 za dodajanje dveh znakov »$«.
Pritisnite Enter

Z-ocena je izračunana za prvo vrednost. To je 0,15945 standardnih odstopanj pod povprečjem. Za preverjanje rezultatov lahko standardni odklon pomnožite s tem rezultatom (6.271629 * -0.15945) in preverite, ali je rezultat enak razliki med vrednostjo in srednjo vrednostjo (499-500). Oba rezultata sta enaka, zato je vrednost smiselna.

Izračunajmo Z-ocene preostalih vrednosti. Označite celoten stolpec 'Z-Score', začenši s celico, ki vsebuje formulo.

Pritisnite Ctrl + D, ki kopira formulo v zgornji celici navzdol skozi vse druge izbrane celice.

Zdaj je bila formula 'zapolnjena' do vseh celic, vsaka pa se bo vedno sklicevala na pravilne celice “AVERAGE” in “STDEV.S” ali “STDEV.P” zaradi znakov “$”. Če dobite napake, se vrnite in preverite, ali so znaki »$« vključeni v formulo, ki ste jo vnesli.

Izračunavanje Z-ocene brez uporabe celic 'Helper'

Celice pomočnikov shranijo rezultat, kot so tiste, ki shranjujejo rezultate funkcij »AVERAGE«, »STDEV.S« in »STDEV.P«. Lahko so koristne, vendar niso vedno potrebne. Lahko jih popolnoma preskočite, ko izračunate Z-oceno, z uporabo naslednjih splošnih formul.

Tukaj uporabljate funkcijo »STDEV.S«:

= (Vrednost - AVERAGE (vrednosti)) / STDEV.S (vrednosti)

In ena s funkcijo "STEV.P":

= (Vrednost - AVERAGE (Vrednosti)) / STDEV.P (Vrednosti)

Pri vnosu obsegov celic za “Vrednosti” v funkcijah, dodajte absolutne reference (“$” z uporabo F4), tako da, ko boste 'izpolnili', ne boste izračunali povprečnega ali standardnega odstopanja drugega območja celic v vsaki formuli.

Če imate velik nabor podatkov, je morda bolj učinkovito uporabljati pomožne celice, ker ne izračuna rezultatov funkcij »AVERAGE« in »STDEV.S« ali »STDEV.P« vsakič, pri čemer se prihranijo sredstva za procesorje in pospešiti čas, potreben za izračun rezultatov.

Tudi "$ G $ 3" zahteva manj bajtov za shranjevanje in manj RAM-a za nalaganje kot "AVERAGE ($ E $ 3: $ E $ 12).". To je pomembno, ker je standardna 32-bitna različica Excela omejena na 2 GB RAM-a (64-bitna različica nima omejitev glede količine RAM-a).