Z-pistemäärän laskeminen Microsoft Excelin avulla

Z-piste on tilastollinen arvo, joka kertoo, kuinka monta standardipoikkeamaa tietyn arvon sattuessa on koko tietosarjan keskiarvosta. Voit käyttää AVERAGE- ja STDEV.S- tai STDEV.P-kaavoja laskeaksesi datasi keskiarvon ja standardipoikkeaman ja käyttää sitten näitä tuloksia määrittämään kunkin arvon Z-pistemäärän.

Mikä on Z-piste ja mitä AVERAGE-, STDEV.S- ja STDEV.P-toiminnot tekevät?

Z-piste on yksinkertainen tapa verrata arvoja kahdesta eri tietojoukosta. Se määritellään standardipoikkeamien lukumääräksi, joka on poikkeava keskiarvosta, joka on datapisteessä. Yleinen kaava näyttää tältä:

= (Datapoint-keskiarvo (DataSet)) / STDEV (DataSet)

Tässä on esimerkki selventää. Sano, että haluat vertailla kahden eri opettajan opettaman Algebran opiskelijan testituloksia. Tiedät, että ensimmäinen opiskelija sai 95% loppukokeesta yhdessä luokassa, ja toisen luokan opiskelija sai 87%.

Ensi silmäyksellä 95%: n palkkaluokka on vaikuttavampi, mutta entä jos toisen luokan opettaja teki vaikeamman tentin? Voit laskea jokaisen opiskelijan pistemäärän Z-pistemäärän kunkin luokan keskiarvojen ja kunkin luokan pistemäärän keskiarvon perusteella. Vertaamalla kahden opiskelijan Z-pisteitä voitiin todeta, että 87%: n pistemäärän omaava opiskelija oli parempi kuin muualla luokassaan kuin 98%: n pistemääräinen opiskelija verrattuna muihin luokkiinsa.

Ensimmäinen tilastollinen arvo on 'keskiarvo' ja Excelin "AVERAGE" -toiminto laskee arvon. Se lisää vain kaikki solualueen arvot ja jakaa tämän summan solujen lukumäärään, joka sisältää numeerisia arvoja (se ohittaa tyhjät solut).

Toinen tarvitsemamme tilastollinen arvo on "standardipoikkeama" ja Excelillä on kaksi erilaista toimintoa, joilla lasketaan keskihajonta hieman eri tavoin.

Excelin aiemmissa versioissa oli vain "STDEV" -toiminto, joka laskee keskihajonnan käsittelemällä dataa väestön "näytteeksi". Excel 2010 rikkoi sen kahteen toimintoon, jotka laskevat keskihajonnan:

• STDEV.S: Tämä toiminto on sama kuin edellinen STDEV-toiminto. Se laskee standardipoikkeaman ja käsittelee tietoja väestön "näytteenä". Näytteenotto väestöstä saattaa olla jotain sellaista erityistä hyttysiä, jotka on kerätty tutkimushankkeelle tai autoille, jotka oli varattu ja joita käytettiin törmäysturvallisuustestaukseen.
• STDEV.P: Tämä toiminto laskee standardipoikkeaman käsittelemällä dataa koko väestönä. Koko väestö olisi jokaisen maan hyttysiä tai jokaista autoa tietyn mallin tuotannossa.

Valitsemaasi perustuu tietokokonaisuuteen. Ero on yleensä pieni, mutta "STDEV.P" -toiminnon tulos on aina pienempi kuin "STDEV.S" -toiminnon tulos samalle tietosarjalle. On konservatiivisempi lähestymistapa olettaa, että datassa on enemmän vaihtelua.

Katsotaanpa esimerkkiä

Esimerkiksi meillä on kaksi saraketta ("Arvot" ja "Z-Score") ja kolme "auttaja" -solua "AVERAGE", "STDEV.S" ja "STDEV.P" -toimintojen tallentamiseksi. Sarakkeessa "Arvot" on kymmenen satunnaislukua, jotka on keskitetty noin 500: een, ja "Z-Score" -sarakkeessa lasketaan Z-pisteet käyttämällä "auttaja" -soluihin tallennettuja tuloksia.

Ensin laskemme arvojen keskiarvon käyttämällä "AVERAGE" -toimintoa. Valitse solu, johon tallennetaan “AVERAGE” -toiminnon tulos.

Kirjoita seuraava kaava ja paina tulla sisään -tai - käytä "Kaavat" -valikkoa.

= Keskiarvo (E2: E13)

Jos haluat käyttää toimintoa "Kaavat" -valikon kautta, valitse "Lisää toimintoja" avattavasta valikosta "Tilastot" ja napsauta sitten "AVERAGE".

Valitse Toiminnalliset argumentit -ikkunassa kaikki arvot-sarakkeen solut ”Number1” -kentän syötteeksi. Sinun ei tarvitse huolehtia "Number2" -kentästä.

Paina nyt OK.

Seuraavaksi meidän on laskettava arvojen keskihajonta joko STDEV.S- tai STDEV.P-toiminnolla. Tässä esimerkissä näytämme, miten lasketaan molemmat arvot alkaen STDEV.S: stä. Valitse solu, johon tulos tallennetaan.

Voit laskea standardipoikkeaman STDEV.S-toiminnon avulla kirjoittamalla tämän kaavan ja painamalla Enter-näppäintä (tai avaamalla sen kaavojen valikon kautta).

= STDEV.S (E3: E12)

Voit käyttää toimintoa "Kaavat" -valikosta valitsemalla avattavasta "Lisää toimintoja", valitsemalla "Tilastollinen" -vaihtoehdon, vierittämällä alas hieman ja napsauttamalla sitten STDEV.S-komentoa.

Valitse Toiminnalliset argumentit -ikkunassa kaikki arvot-sarakkeen solut ”Number1” -kentän syötteeksi. Sinun ei tarvitse myöskään huolehtia "Number2" -kentästä.

Paina nyt OK.

Seuraavaksi laskemme standardipoikkeaman käyttämällä STDEV.P-toimintoa. Valitse solu, johon tulos tallennetaan.

Voit laskea standardipoikkeaman STDEV.P-toiminnon avulla kirjoittamalla tämän kaavan ja painamalla Enter-näppäintä (tai avaamalla sen kaavojen valikon kautta).

= STDEV.P (E3: E12)

Voit käyttää toimintoa "Kaavat" -valikosta valitsemalla avattavan "Lisää toimintoja" -valinnan, valitsemalla "Tilastollinen" -vaihtoehdon, vierittämällä alas hieman ja napsauttamalla sitten STDEV.P-kaavaa.

Valitse Toiminnalliset argumentit -ikkunassa kaikki arvot-sarakkeen solut ”Number1” -kentän syötteeksi. Jälleen sinun ei tarvitse huolehtia "Number2" -kentästä.

Paina nyt OK.

Nyt kun olemme laskeneet tietojen keskiarvon ja keskihajonnan, meillä on kaikki, mitä tarvitsemme Z-pistemäärän laskemiseksi. Voimme käyttää yksinkertaista kaavaa, joka viittaa soluihin, jotka sisältävät "AVERAGE" - ja "STDEV.S" - tai "STDEV.P" -toimintojen tulokset.

Valitse ensimmäinen solu "Z-Score" -sarakkeesta. Käytämme tässä esimerkissä “STDEV.S” -toiminnon tuloksia, mutta voit myös käyttää tulosta ”STDEV.P.”

Kirjoita seuraava kaava ja paina Enter:

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

Vaihtoehtoisesti voit syöttää kaavan kirjoittamisen sijaan seuraavasti:

1. Napsauta solua F3 ja kirjoita = (
2. Valitse solu E3. (Voit painaa Vasen nuoli-näppäin kerran tai käytä hiirtä)
3. Kirjoita miinusmerkki -
4. Valitse solu G3 ja paina sitten F4 lisätä "$" -merkit, jotta "absoluuttinen" viittaus soluun (se kiertää "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 ", jos painat edelleen F4)
5. Tyyppi ) /
6. Valitse solu H3 (tai I3, jos käytät “STDEV.P”) ja paina F4 lisätä kaksi "$" -merkkiä.
7. paina Enter

Z-pisteet on laskettu ensimmäiselle arvolle. Se on keskiarvon alapuolella 0,15945 standardipoikkeamaa. Tulosten tarkistamiseksi voit kertoa standardipoikkeaman tällä tuloksella (6.271629 * -0.15945) ja tarkistaa, että tulos on yhtä suuri kuin arvon ja keskiarvon (499-500) välinen ero. Molemmat tulokset ovat yhtä suuret, joten arvo on järkevää.

Lasketaan loput arvot Z-pisteistä. Korosta koko Z-piste-sarake, joka alkaa kaavasta.

Paina Ctrl + D, joka kopioi kaavan ylempään soluun kaikkien muiden valittujen solujen läpi.

Nyt kaava on "täytetty" kaikkiin soluihin, ja kukin viittaa aina oikeaan "AVERAGE" ja "STDEV.S" tai "STDEV.P" -soluihin "$" -merkkien vuoksi. Jos saat virheitä, mene takaisin ja varmista, että $ $ -merkit sisältyvät syötettyyn kaavaan.

Z-pistemäärän laskeminen ilman Helper-soluja

Auttajasolut tallentavat tuloksen, kuten ne, jotka tallentavat AVERAGE-, STDEV.S- ja STDEV.P-toimintojen tulokset. Ne voivat olla hyödyllisiä, mutta eivät aina välttämättömiä. Voit ohittaa ne kokonaan laskettaessa Z-pistettä käyttämällä seuraavia yleistettyjä kaavoja.

Tässä käytetään “STDEV.S” -toimintoa:

= (Value-keskiarvo (arvot)) / STDEV.S (arvot)

Ja toinen toiminto "STEV.P":

= (Value-keskiarvo (arvot)) / STDEV.P (arvot)

Kun syötät solujen alueet "Arvot" -toimintoihin, muista lisätä absoluuttisia viitteitä ("$" käyttämällä F4: tä), jotta et laskisi keskiarvoa tai eri poikkeamaa eri alueille solujen määrä kaikissa kaavoissa.

Jos sinulla on suuri tietokokonaisuus, auttajasolujen käyttö voi olla tehokkaampaa, koska se ei laskea "AVERAGE" - ja "STDEV.S" - tai "STDEV.P" -toimintojen tuloksia joka kerta, mikä säästää prosessorin resursseja nopeuttaa tulosten laskemiseen kuluvaa aikaa.

Lisäksi ”$ G $ 3” tallentaa vähemmän tavuja ja vähemmän RAM-muistia kuin ”AVERAGE ($ E $ 3: $ E $ 12).”. Tämä on tärkeää, koska tavallinen 32-bittinen Excel-versio on rajoitettu 2 Gt: n muistiin (64-bittisellä versiolla ei ole rajoituksia siitä, kuinka paljon RAM-muistia voidaan käyttää).