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

Z-rezultat je statistička vrijednost koja vam govori koliko je standardnih odstupanja određena vrijednost od srednje vrijednosti cijelog skupa podataka. Pomoću formula AVERAGE i STDEV.S ili STDEV.P možete izračunati srednju i standardnu ​​devijaciju podataka, a zatim pomoću tih rezultata odrediti Z-ocjenu svake vrijednosti.

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

Z-Score je jednostavan način usporedbe vrijednosti iz dva različita skupa podataka. Definiran je kao broj standardnih odstupanja od srednje vrijednosti podataka. Opća formula izgleda ovako:

= (DataPoint-PROSJEČNO (Skup podataka)) / STDEV (Skup podataka)

Evo primjera koji će vam pomoći pojasniti. Recimo da ste željeli usporediti rezultate testa dvojice učenika iz algebre koje podučavaju različiti učitelji. Znate da je prvi student dobio 95% na završnom ispitu u jednom razredu, a student u drugom razredu 87%.

Na prvi je pogled ocjena od 95% impresivnija, ali što ako učitelj drugog razreda da teži ispit? Možete izračunati Z-ocjenu rezultata svakog učenika na temelju prosječnih ocjena u svakom razredu i standardne devijacije rezultata u svakom razredu. Usporedbom Z-rezultata dvoje učenika moglo bi se otkriti da je učenik s ocjenom 87% imao bolji rezultat u odnosu na ostatak razreda, nego učenik s ocjenom 98% u usporedbi s ostatkom razreda.

Prva statistička vrijednost koja vam treba je 'prosjek', a Excelova "PROSJEČNA" funkcija izračunava tu vrijednost. Jednostavno zbraja sve vrijednosti u rasponu stanica i dijeli taj zbroj s brojem stanica koje sadrže numeričke vrijednosti (zanemaruje prazne stanice).

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

Prethodne verzije Excela imale su samo funkciju „STDEV“, koja izračunava standardno odstupanje, a podatke tretira kao „uzorak“ populacije. Excel 2010 podijelio je to na dvije funkcije koje izračunavaju standardno odstupanje:

  • STDEV.S: Ova je funkcija identična prethodnoj funkciji "STDEV". Izračunava standardnu ​​devijaciju dok podatke tretira kao 'uzorak' populacije. Uzorak populacije mogao bi biti nešto poput određenih komaraca prikupljenih za istraživački projekt ili automobila koji su odvojeni i korišteni za ispitivanje sigurnosti od sudara.
  • STDEV.P: Ova funkcija izračunava standardno odstupanje dok podatke tretira kao cjelokupnu populaciju. Čitava populacija bila bi nešto poput svih komaraca na Zemlji ili svakog automobila u proizvodnji određenog modela.

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

Pogledajmo primjer

Za naš primjer imamo dva stupca ("Vrijednosti" i "Z-rezultat") i tri ćelije "pomoćnice" za spremanje rezultata funkcija "PROSJEČNO", "STDEV.S" i "STDEV.P". Stupac "Vrijednosti" sadrži deset slučajnih brojeva usredotočenih oko 500, a u stupcu "Z-rezultat" izračunavat ćemo Z-rezultat pomoću rezultata pohranjenih u ćelijama "pomoćnika".

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

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

= PROSJEČNO (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 Funkcionalni argumenti odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Ne trebate brinuti o polju "Broj2".

Sada pritisnite "OK".

Dalje, moramo izračunati standardno odstupanje vrijednosti pomoću funkcije "STDEV.S" ili "STDEV.P". U ovom ćemo primjeru pokazati kako izračunati obje vrijednosti, počevši od "STDEV.S." Odaberite ćeliju u kojoj će biti pohranjen rezultat.

Da biste izračunali standardno odstupanje 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 Funkcionalni argumenti odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Ni ovdje ne trebate brinuti o polju "Broj2".

Sada pritisnite "OK".

Zatim ćemo izračunati standardno odstupanje pomoću funkcije "STDEV.P". Odaberite ćeliju u kojoj će biti pohranjen rezultat.

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

= STDEV.P (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 formulu "STDEV.P".

U prozoru Funkcionalni argumenti odaberite sve ćelije u stupcu "Vrijednosti" kao ulaz za polje "Broj1". Opet, nećete trebati brinuti oko polja "Broj2".

Sada pritisnite "OK".

Sad kad smo izračunali srednju i standardnu ​​devijaciju naših podataka, imamo sve što nam je potrebno za izračunavanje Z-Score. Možemo se poslužiti jednostavnom formulom koja upućuje na stanice koje sadrže rezultate funkcija "PROSJEČNO" i "STDEV.S" ili "STDEV.P".

Odaberite prvu ćeliju u stupcu "Z-Score". Za ovaj ćemo primjer upotrijebiti rezultat funkcije "STDEV.S", ali vi biste mogli koristiti i rezultat iz "STDEV.P."

Upišite sljedeću formulu i pritisnite Enter:

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

Alternativno, možete unijeti sljedeće korake za unos formule umjesto da upišete:

  1. Kliknite ćeliju F3 i upišite =(
  2. Odaberite ćeliju E3. (Možete jednom pritisnuti tipku sa strelicom ulijevo ili upotrijebiti miš)
  3. Upišite znak minus -
  4. Odaberite ćeliju G3, a zatim pritisnite F4 da biste dodali znakove "$" kako biste napravili "apsolutnu" referencu na ćeliju (ona će kružiti kroz "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> "G3" ako nastavite pritiskati F4 )
  5. Tip )/
  6. Odaberite ćeliju H3 (ili I3 ako koristite "STDEV.P") i pritisnite F4 da dodate dva znaka "$".
  7. pritisni enter

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

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

Pritisnite Ctrl + D, čime se formula u gornjoj ćeliji kopira prema dolje kroz sve ostale odabrane ćelije.

Sada je formula 'popunjena' u sve ćelije i svaka će se uvijek pozivati ​​na ispravne ćelije "PROSJEČNE" i "STDEV.S" ili "STDEV.P" 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 korištenja ćelija 'Pomoćnika'

Pomoćne stanice pohranjuju rezultat, poput onih koji pohranjuju rezultate funkcija "PROSJEČNO", "STDEV.S" i "STDEV.P". Mogu biti korisni, ali nisu uvijek potrebni. Možete ih potpuno preskočiti prilikom izračunavanja Z-rezultata pomoću sljedećih generaliziranih formula.

Evo jednog koji koristi funkciju "STDEV.S":

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

I onaj koji koristi funkciju "STEV.P":

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

Prilikom unosa raspona ćelija za "Vrijednosti" u funkcije, obavezno dodajte apsolutne reference ("$" pomoću F4) tako da prilikom "popunjavanja" ne računate prosjek ili standardno odstupanje drugog raspona stanica u svakoj formuli.

Ako imate velik skup podataka, možda je učinkovitije koristiti pomoćne ćelije jer ne izračunava svaki put rezultat funkcija „PROSJEČNO“ i „STDEV.S“ ili „STDEV.P“, štedeći resurse procesora i ubrzavajući vrijeme potrebno za izračunavanje rezultata.

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