Kako izračunati Z-rezultat pomoću Microsoft Excel

Z-Score je statistička vrijednost koja vam govori koliko standardnih devijacija određena vrijednost se događa iz srednje vrijednosti cijelog skupa podataka. Formule AVERAGE i STDEV.S ili STDEV.P možete upotrijebiti za izračunavanje srednje vrijednosti i standardne devijacije podataka, a zatim pomoću tih rezultata odredite Z-ocjenu svake vrijednosti.

Što je Z-ocjena i što AVERAGE, STDEV.S i STDEV.P funkcije rade?

Z-Score je jednostavan način uspoređivanja vrijednosti iz dva različita skupa podataka. Definira se kao broj standardnih odstupanja udaljenih od srednje vrijednosti podatkovne točke. Opća formula izgleda ovako:

= (Podataka vezanih-PROSJEK (naziv)) / STDEV (naziv)

Evo primjera koji će vam pomoći razjasniti. Recimo da ste željeli usporediti rezultate dvaju studenata algebre koje su podučavali različiti učitelji. Znate da je prvi učenik dobio 95% na završnom ispitu u jednom razredu, a student u drugom razredu osvojio 87%.

Na prvi pogled, ocjena 95% je impresivnija, ali što ako je učitelj drugog razreda dao teži ispit? Možete izračunati Z-ocjenu bodova svakog učenika na temelju prosječnih ocjena u svakom razredu i standardne devijacije rezultata u svakom razredu. Uspoređujući Z-ocjene dvaju učenika, moglo bi se otkriti da je učenik s 87% rezultata postigao bolje rezultate u odnosu na ostatak svoje klase nego što je učenik s ocjenom od 98% učinio u odnosu na ostatak razreda.

Prva statistička vrijednost koja vam je potrebna je "mean", a Excelova "AVERAGE" funkcija izračunava tu vrijednost. Jednostavno dodaje sve vrijednosti u rasponu ćelija i dijeli taj zbroj na broj ćelija koje sadrže numeričke vrijednosti (zanemaruje prazne ćelije).

Druga statistička vrijednost koja nam je potrebna je "standardna devijacija", a Excel ima dvije različite funkcije za izračunavanje standardne devijacije na malo različite načine.

Prethodne verzije Excela imale su samo funkciju "STDEV", koja izračunava standardno odstupanje, dok podatke tretira kao "uzorak" populacije. Excel 2010 je to razbio u dvije funkcije koje izračunavaju standardnu devijaciju:

STDEV.S: Ova funkcija je identična prethodnoj funkciji "STDEV". Ona izračunava standardnu devijaciju, dok podatke tretira kao 'uzorak' populacije. Uzorak populacije može biti nešto poput određenih komaraca prikupljenih za istraživački projekt ili automobila koji su izdvojeni i korišteni za ispitivanje sigurnosti u sudaru.
STDEV.P: Ova funkcija izračunava standardno odstupanje dok podatke tretira kao cijelu populaciju. Cijela populacija bi bila nešto poput svih komaraca na Zemlji ili svakog automobila u proizvodnom ciklusu određenog modela.

Koji ste odabrali temelji se na vašem skupu podataka. Razlika je obično mala, ali rezultat funkcije "STDEV.P" uvijek će biti manji od rezultata funkcije "STDEV.S" za isti skup podataka. Konzervativniji je pristup pretpostavci da postoji veća varijabilnost podataka.

Pogledajmo primjer

Za naš primjer imamo dva stupca ("Vrijednosti" i "Z-Score") i tri "pomoćne" ćelije za pohranjivanje rezultata funkcija "AVERAGE", "STDEV.S" i "STDEV.P". Stupac "Vrijednosti" sadrži deset slučajnih brojeva centriranih oko 500, a stupac "Z-Score" gdje ćemo izračunati Z-ocjenu pomoću rezultata pohranjenih u ćelijama 'pomoćnica'.

Prvo ćemo izračunati srednju vrijednost pomoću funkcije "AVERAGE". Odaberite ćeliju u koju ćete pohraniti rezultat funkcije "AVERAGE".

Upišite sljedeću formulu i pritisnite Unesi -ili - koristite izbornik "Formule".

= Srednja (E2 E13)

Da biste pristupili funkciji putem izbornika "Formule", odaberite padajući izbornik "Više funkcija", odaberite opciju "Statistički", a zatim kliknite "PROSJEČNO".

U prozoru Argumenti funkcija odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Ne morate brinuti o polju "Number2".

Sada pritisnite "OK".

Zatim moramo izračunati standardno odstupanje vrijednosti pomoću funkcije "STDEV.S" ili "STDEV.P". U ovom primjeru pokazat ćemo vam kako izračunati obje vrijednosti, počevši od "STDEV.S." Odaberite ćeliju u kojoj će se pohraniti rezultat.

Da biste izračunali standardnu devijaciju pomoću funkcije "STDEV.S", upišite ovu formulu i pritisnite Enter (ili joj pristupite putem izbornika "Formule").

= STDEV.S (E3: E12)

Da biste pristupili funkciji putem izbornika "Formule", odaberite padajući izbornik "Više funkcija", odaberite opciju "Statistički", pomaknite se malo prema dolje, a zatim kliknite naredbu "STDEV.S".

U prozoru Argumenti funkcija odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Ne trebate se brinuti ni za polje "Broj2".

Sada pritisnite "OK".

Zatim ćemo izračunati standardnu devijaciju pomoću funkcije "STDEV.P". Odaberite ćeliju u kojoj će se pohraniti rezultat.

Da biste izračunali standardno odstupanje pomoću funkcije "STDEV.P", upišite ovu formulu i pritisnite Enter (ili joj pristupite kroz izbornik "Formule").

= STDEV.P (E3: E12)

U prozoru Argumenti funkcija odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Opet, ne morate brinuti o polju "Number2".

Sada pritisnite "OK".

Sada kada smo izračunali prosjek i standardnu devijaciju naših podataka, imamo sve što je potrebno za izračunavanje Z-skora. Možemo koristiti jednostavnu formulu koja upućuje na ćelije koje sadrže rezultate funkcija "AVERAGE" i "STDEV.S" ili "STDEV.P".

Odaberite prvu ćeliju u stupcu "Z-Score". U ovom primjeru koristit ćemo rezultat funkcije "STDEV.S", ali možete koristiti i rezultat "STDEV.P".

Upišite sljedeću formulu i pritisnite Enter:

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

Alternativno, možete upotrijebiti sljedeće korake za unos formule umjesto upisivanja:

Kliknite ćeliju F3 i upišite = (
Odaberite ćeliju E3. (Možete pritisnuti tipku lijeva strelica ključ jednom ili pomoću miša)
Upišite znak minus -
Odaberite ćeliju G3, a zatim pritisnite F4 da biste dodali znakove "$" za "apsolutnu" referencu na ćeliju (kretat će se kroz "G3"> “$G$3 ">" G$3 ">"$G3 ">" G3 "ako nastavite pritiskati F4)
Tip ) /
Odaberite ćeliju H3 (ili I3 ako koristite "STDEV.P") i pritisnite F4 za dodavanje dvaju znakova "$".
pritisni enter

Z-ocjena je izračunata za prvu vrijednost. To je 0,15945 standardnih odstupanja ispod srednje vrijednosti. Da biste provjerili rezultate, možete pomnožiti standardno odstupanje s ovim rezultatom (6.271629 * -0.15945) i provjeriti je li rezultat jednak razlici između vrijednosti i srednje vrijednosti (499-500). Oba rezultata su jednaka, tako da vrijednost ima smisla.

Izračunajmo Z-ocjene ostalih vrijednosti. Označite cijeli stupac 'Z-Score' počevši od ćelije koja sadrži formulu.

Pritisnite Ctrl + D, koji kopira formulu u gornjoj ćeliji kroz sve ostale odabrane ćelije.

Sada je formula "ispunjena" svim ćelijama, a svaka od njih će uvijek upućivati na ispravne "AVERAGE" i "STDEV.S" ili "STDEV.P" ćelije zbog znakova "$". Ako dobijete pogreške, vratite se i provjerite jesu li znakovi "$" uključeni u formulu koju ste unijeli.

Izračunavanje Z-rezultata bez upotrebe ćelija za pomoć

Pomoćne stanice pohranjuju rezultat, poput onih koje pohranjuju rezultate funkcija "AVERAGE", "STDEV.S" i "STDEV.P". Oni mogu biti korisni, ali nisu uvijek potrebni. Možete ih potpuno preskočiti kada izračunavate Z-ocjenu koristeći sljedeće generalizirane formule umjesto toga.

Evo jednog pomoću funkcije "STDEV.S":

= (Vrijednost-PROSJEK (vrijednosti)) / STDEV.S (vrijednosti)

I jedan pomoću funkcije "STEV.P":

= (Vrijednost-PROSJEK (vrijednosti)) / STDEV.P (vrijednosti)

Prilikom unošenja raspona ćelija za "Vrijednosti" u funkcijama, svakako dodajte apsolutne reference ("$" pomoću F4) tako da kada "popunite" ne izračunavate prosječnu ili standardnu devijaciju različitog raspona stanica u svakoj formuli.

Ako imate veliki skup podataka, možda je učinkovitije koristiti pomoćne ćelije jer ne izračunava rezultat "AVERAGE" i "STDEV.S" ili "STDEV.P" funkcija svaki put, štedeći procesorske resurse i ubrzati vrijeme potrebno za izračun rezultata.

Također, "$ G $ 3" zahtijeva manje bajtova za pohranu i manje RAM-a za učitavanje od "AVERAGE ($ E $ 3: $ E $ 12).". To je važno jer je standardna 32-bitna verzija programa Excel ograničena na 2 GB RAM-a (64-bitna verzija nema ograničenja u pogledu količine RAM-a).