Ako vypočítať skóre Z pomocou programu Microsoft Excel

Skóre Z je štatistická hodnota, ktorá vám ukáže, koľko štandardných odchýlok sa konkrétna hodnota stáva od priemeru celého súboru údajov. Môžete použiť vzorce AVERAGE a STDEV.S alebo STDEV.P na výpočet priemernej a štandardnej odchýlky vašich údajov a potom použiť tieto výsledky na určenie Z-skóre každej hodnoty.

Čo je Z-skóre a čo robia funkcie AVERAGE, STDEV.S a STDEV.P?

Z-skóre je jednoduchý spôsob porovnávania hodnôt z dvoch rôznych súborov údajov. Definuje sa ako počet štandardných odchýlok od priemeru dátového bodu. Všeobecný vzorec vyzerá takto:

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

Tu je príklad, ktorý vám pomôže objasniť. Povedzme, že chcete porovnať výsledky testov dvoch študentov algebry vyučovaných rôznymi učiteľmi. Viete, že prvý študent dostal 95% záverečnej skúšky v jednej triede a študent v inej triede získal 87%.

Na prvý pohľad je 95% trieda pôsobivejšia, ale čo keď učiteľ druhej triedy urobil zložitejšiu skúšku? Môžete vypočítať Z-skóre skóre každého študenta na základe priemerných bodov v každej triede a štandardnej odchýlky skóre v každej triede. Pri porovnaní výsledkov Z dvoch študentov by mohlo dôjsť k odhaleniu, že študent s 87% skóre bol lepší v porovnaní so zvyškom triedy ako študent s 98% skóre v porovnaní so zvyškom ich triedy.

Prvá štatistická hodnota, ktorú potrebujete, je "stredná" a hodnota "AVERAGE" programu Excel vypočíta túto hodnotu. Jednoducho pridáva všetky hodnoty v bunkovom rozsahu a delí túto sumu počtom buniek obsahujúcich číselné hodnoty (ignoruje prázdne bunky).

Ďalšia štatistická hodnota, ktorú potrebujeme, je "štandardná odchýlka" a program Excel má dve rôzne funkcie na výpočet smerodajnej odchýlky mierne odlišnými spôsobmi.

Predchádzajúce verzie programu Excel mali iba funkciu "STDEV", ktorá vypočítava štandardnú odchýlku a zaobchádza s údajmi ako "vzorka" populácie. Excel 2010 zlomil to do dvoch funkcií, ktoré vypočítavajú štandardnú odchýlku:

• STDEV.S: Táto funkcia je identická s predchádzajúcou funkciou "STDEV". Vypočíta štandardnú odchýlku pri spracovaní údajov ako "vzorky" populácie. Vzorka populácie môže byť niečo ako konkrétni komári zhromaždení pre výskumný projekt alebo autá, ktoré boli vyňaté z prevádzky a použité na testovanie bezpečnosti pri náraze.
• STDEV.P: Táto funkcia vypočíta štandardnú odchýlku pri spracovaní údajov ako celá populácia. Celá populácia by bola niečo ako všetci komári na Zemi alebo každé auto vo výrobnom procese konkrétneho modelu.

Vyberiete to podľa vášho súboru údajov. Rozdiel bude zvyčajne malý, ale výsledok funkcie "STDEV.P" bude vždy menší ako výsledok funkcie "STDEV.S" pre ten istý súbor údajov. Je to konzervatívnejší prístup k predpokladu, že v údajoch existuje väčšia variabilita.

Pozrime sa na príklad

Pre náš príklad máme dva stĺpce ("Hodnoty" a "Skóre Z") a tri "pomocné" bunky na ukladanie výsledkov funkcií "AVERAGE", "STDEV.S" a "STDEV.P". Stĺpec Hodnoty obsahuje desať náhodných čísiel umiestnených okolo 500 a v stĺpci Z-Score sa vypočíta Z-skóre pomocou výsledkov uložených v "pomocných" bunkách.

Najprv vypočítame strednú hodnotu pomocou funkcie "AVERAGE". Vyberte bunku, do ktorej sa uloží výsledok funkcie "AVERAGE".

Zadajte nasledujúci vzorec a stlačte tlačidlo vstúpiť -alebo - použite ponuku "Vzorce".

= Stredná (E2: E13)

Ak chcete prejsť na túto funkciu cez ponuku "Formuláre", vyberte rozbaľovaciu ponuku "Ďalšie funkcie", vyberte možnosť "Štatistické" a potom kliknite na položku "AVERAGE".

V okne Argumenty funkcií vyberte všetky bunky v stĺpci Hodnoty ako vstup pre pole Číslo1. Nemusíte sa obávať poľa "Číslo2".

Teraz stlačte tlačidlo "OK".

Ďalej je potrebné vypočítať štandardnú odchýlku hodnôt pomocou funkcie "STDEV.S" alebo "STDEV.P". V tomto príklade vám ukážeme, ako vypočítať obe hodnoty, začínajúc "STDEV.S." Vyberte bunku, kde bude výsledok uložený.

Ak chcete vypočítať štandardnú odchýlku pomocou funkcie "STDEV.S", zadajte tento vzorec a stlačte kláves Enter (alebo ho sprístupnite cez ponuku "Formuláre").

= STDEV.S (E3: E12)

Ak chcete prejsť na túto funkciu prostredníctvom ponuky "Formuláre", vyberte rozbaľovaciu ponuku "Viac funkcií", vyberte možnosť "Štatistické", posuňte sa nadol a potom kliknite na príkaz "STDEV.S".

V okne Argumenty funkcií vyberte všetky bunky v stĺpci Hodnoty ako vstup pre pole Číslo1. Nemusíte sa obávať ani poľa "Číslo2".

Teraz stlačte tlačidlo "OK".

Ďalej vypočítame štandardnú odchýlku pomocou funkcie "STDEV.P". Vyberte bunku, kde bude výsledok uložený.

Ak chcete vypočítať štandardnú odchýlku pomocou funkcie "STDEV.P", zadajte tento vzorec a stlačte kláves Enter (alebo ho prejdite cez ponuku "Formuláre").

= STDEV.P (E3: E12)

Ak chcete pristupovať k funkciám prostredníctvom ponuky "Formuláre", vyberte rozbaľovaciu ponuku "Ďalšie funkcie", vyberte možnosť "Štatistické", prejdite trochu nadol a kliknite na vzorec "STDEV.P".

V okne Argumenty funkcií vyberte všetky bunky v stĺpci Hodnoty ako vstup pre pole Číslo1. Opäť sa nebudete musieť obávať poľa "Číslo2".

Teraz stlačte tlačidlo "OK".

Teraz, keď sme vypočítali priemernú a štandardnú odchýlku našich údajov, máme všetko, čo potrebujeme na výpočet Z-skóre. Môžeme použiť jednoduchý vzorec, ktorý odkazuje na bunky obsahujúce výsledky funkcií "AVERAGE" a "STDEV.S" alebo "STDEV.P".

Vyberte prvú bunku v stĺpci Z-skóre. V tomto príklade použijeme výsledok funkcie "STDEV.S", ale môžete tiež použiť výsledok z "STDEV.P".

Zadajte nasledujúci vzorec a stlačte kláves Enter:

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

Prípadne môžete namiesto zadávania textu použiť nasledujúci postup:

1. Kliknite na bunku F3 a napíšte = (
2. Vyberte bunku E3. (Môžete stlačiť tlačidlo ľavá šípka na kľúč raz alebo pomocou myši)
3. Zadajte znamienko mínus -
4. Vyberte bunku G3 a stlačte tlačidlo F4 pridať znaky "$" na vytvorenie "absolútneho" odkazu na bunku (prechádza cez "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 ", ak budete pokračovať v stlačení tlačidla F4)
5. typ ) /
6. Vyberte bunku H3 (alebo I3, ak používate "STDEV.P") a stlačte tlačidlo F4 pridať dva znaky "$".
7. stlačte Enter

Hodnota Z bola vypočítaná pre prvú hodnotu. Je to 0,15945 štandardných odchýlok pod priemerom. Ak chcete skontrolovať výsledky, môžete tento výsledok vynásobiť týmto výsledkom (6.271629 * -0.15945) a skontrolovať, či sa výsledok rovná rozdielu medzi hodnotou a strednou hodnotou (499-500). Oba výsledky sú rovnaké, takže hodnota dáva zmysel.

Vypočítame Z-skóre ostatných hodnôt. Zvýraznite celý stĺpec "Z-skóre" začínajúci bunkou obsahujúcou vzorec.

Stlačte klávesy Ctrl + D, ktoré skopírujú vzorec v hornej bunke dole cez všetky ostatné vybrané bunky.

Teraz je vzorec "vyplnený" na všetky bunky a každý vždy odkazuje na správne bunky "AVERAGE" a "STDEV.S" alebo "STDEV.P" kvôli znakom "$". Ak sa vyskytnú chyby, vráťte sa a uistite sa, že znaky "$" sú zahrnuté do zadaného vzorca.

Výpočet skóre Z bez použitia "Helper" buniek

Pomocné bunky ukladajú výsledok, napríklad tie, ktoré ukladajú výsledky funkcií "AVERAGE", "STDEV.S" a "STDEV.P". Môžu byť užitočné, ale nie vždy nevyhnutné. Môžete ich úplne preskočiť pri výpočte Z-skóre pomocou nasledujúcich generalizovaných vzorcov.

Tu je jedna pomocou funkcie "STDEV.S":

= (Hodnota priemerom (hodnoty)) / STDEV.S (hodnoty)

A jeden pomocou funkcie "STEV.P":

= (Hodnota priemerom (hodnoty)) / STDEV.P (hodnoty)

Pri zadávaní bunkových rozsahov pre "Hodnoty" vo funkciách nezabudnite pridať absolútne referencie ("$" pomocou F4) tak, že pri vyplňovaní nevypočíte priemernú alebo štandardnú odchýlku iného rozsahu buniek v každej formulácii.

Ak máte veľký súbor údajov, môže byť efektívnejšie používať pomocné bunky, pretože nespočítava vždy výsledok funkcií "AVERAGE" a "STDEV.S" alebo "STDEV.P", čím ušetrí procesorové zdroje a čo urýchli čas na výpočet výsledkov.

Tiež, "$ G $ 3" ​​trvá menej bajtov na ukladanie a menej RAM sa načítať ako "MEDZINÁRODNÁ ($ E $ 3: $ E $ 12). To je dôležité, pretože štandardná 32-bitová verzia programu Excel je obmedzená na 2 GB RAM (64-bitová verzia nemá žiadne obmedzenia na to, koľko pamäte RAM je možné použiť).