Jak vypočítat skóre Z pomocí programu Microsoft Excel

Z-skóre je statistická hodnota, která udává, kolik standardních odchylek se jedná o určitou hodnotu z průměrné hodnoty celého souboru dat. Můžete použít vzorce AVERAGE a STDEV.S nebo STDEV.P pro výpočet střední a standardní odchylky vašich dat a poté použít tyto výsledky k určení Z-skóre každé hodnoty.

Co je skóre Z a co dělají funkce AVERAGE, STDEV.S a STDEV.P?

Z-skóre je jednoduchý způsob porovnání hodnot ze dvou různých datových sad. Definuje se jako počet standardních odchylek od průměru datového bodu. Obecný vzorec vypadá takto:

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

Zde je příklad, který vám pomůže objasnit. Řekněme, že chcete porovnat výsledky testů dvou studentů algebry, které vyučují různí učitelé. Víte, že první student získal 95% závěrečné zkoušky v jedné třídě a student ve druhé třídě získal 87%.

Na první pohled je stupeň 95% působivější, ale co když učitel druhé třídy uděloval náročnější zkoušku? Můžete vypočítat Z-skóre skóre každého studenta na základě průměrných bodů v každé třídě a standardní odchylky skóre v každé třídě. Srovnání Z-skóre obou studentů by mohlo odhalit, že student s 87% skóre byl lepší ve srovnání se zbytkem třídy než student s 98% skóre ve srovnání se zbytkem jejich třídy.

První potřebná statistická hodnota je "střední" a funkce "AVERAGE" aplikace Excel tuto hodnotu vypočítá. Jednoduše přidává všechny hodnoty v buněčném rozsahu a dělí tuto sumu počtem buněk obsahujících číselné hodnoty (ignoruje prázdné buňky).

Další statistická hodnota, kterou potřebujeme, je "standardní odchylka" a aplikace Excel má dvě různé funkce pro výpočet směrodatné odchylky trochu odlišnými způsoby.

Předchozí verze aplikace Excel měly pouze funkci "STDEV", která vypočítá směrodatnou odchylku a zároveň zpracovává data jako "vzorek" populace. Excel 2010 zlomil to do dvou funkcí, které vypočítávají směrodatnou odchylku:

• STDEV.S: Tato funkce je shodná s předchozí funkcí "STDEV". Vypočítá směrodatnou odchylku a zároveň zpracovává data jako "vzorek" populace. Vzorek populace může být něco jako komáry shromážděné pro výzkumný projekt nebo automobily, které byly odloženy a použity pro testování bezpečnosti při nárazu.
• STDEV.P: Tato funkce vypočítá směrodatnou odchylku při zacházení s daty jako s celou populací. Celá populace by byla něco jako všichni komáři na Zemi nebo každé auto ve výrobním cyklu určitého modelu.

Výběr, který vyberete, je založen na datovém souboru. Rozdíl bude obvykle malý, ale výsledek funkce "STDEV.P" bude vždy menší než výsledek funkce "STDEV.S" pro stejnou sadu dat. Jedná se o konzervativnější přístup k předpokladu, že v datech existuje větší variabilita.

Podívejme se na příklad

Pro náš příklad máme dva sloupce ("Hodnoty" a "Z-skóre") a tři "pomocné" buňky pro ukládání výsledků funkcí "AVERAGE", "STDEV.S" a "STDEV.P". Sloupec "Hodnoty" obsahuje deset náhodných čísel se středem kolem 500 a ve sloupci Z-skóre je místo, kde vypočítáme Z-skóre pomocí výsledků uložených v buňkách pomocníka.

Nejprve vypočítáme průměr hodnot pomocí funkce "AVERAGE". Vyberte buňku, ve které uložíte výsledek funkce "AVERAGE".

Zadejte následující vzorec a stiskněte zadejte -nebo - použijte menu "Formule".

= Průměrná (E2: E13)

Chcete-li tuto funkci otevřít prostřednictvím nabídky "Formule", vyberte rozbalovací nabídku "Další funkce", vyberte možnost "Statistické" a klikněte na "Průměr".

V okně Argumenty funkce vyberte všechny buňky ve sloupci "Hodnoty" jako vstup pro pole "Číslo1". Nemusíte se obávat pole "Číslo2".

Nyní stiskněte tlačítko "OK".

Poté je třeba vypočítat směrodatnou odchylku hodnot pomocí funkce "STDEV.S" nebo "STDEV.P". V tomto příkladu vám ukážeme, jak vypočítat obě hodnoty, a to od "STDEV.S." Vyberte buňku, kde bude výsledek uložen.

Chcete-li vypočítat směrodatnou odchylku pomocí funkce "STDEV.S", zadejte tento vzorec a stiskněte klávesu Enter (nebo jej otevřete pomocí nabídky "Formule").

= STDEV.S (E3: E12)

Chcete-li tuto funkci otevřít pomocí nabídky "Formuláře", vyberte rozbalovací nabídku "Další funkce", vyberte možnost "Statistická", trochu posuňte dolů a klikněte na příkaz "STDEV.S".

V okně Argumenty funkce vyberte všechny buňky ve sloupci "Hodnoty" jako vstup pro pole "Číslo1". Nemusíte se ani bát o pole "Číslo2".

Nyní stiskněte tlačítko "OK".

Dále vypočítáme směrodatnou odchylku pomocí funkce "STDEV.P". Vyberte buňku, kde bude výsledek uložen.

Chcete-li vypočítat směrodatnou odchylku pomocí funkce "STDEV.P", zadejte tento vzorec a stiskněte klávesu Enter (nebo jej otevřete pomocí nabídky "Formule").

= STDEV.P (E3: E12)

Chcete-li tuto funkci otevřít pomocí nabídky "Formuláře", vyberte rozbalovací nabídku "Další funkce", vyberte možnost "Statistická", přetáčejte dolů a klepněte na vzorec "STDEV.P".

V okně Argumenty funkce vyberte všechny buňky ve sloupci "Hodnoty" jako vstup pro pole "Číslo1". Opět se nebudete muset starat o pole "Číslo2".

Nyní stiskněte tlačítko "OK".

Nyní, když jsme vypočítali průměrnou a směrodatnou odchylku našich dat, máme vše, co potřebujeme k výpočtu skóre Z. Můžeme použít jednoduchý vzorec, který odkazuje na buňky obsahující výsledky funkcí "AVERAGE" a "STDEV.S" nebo "STDEV.P".

Vyberte první buňku ve sloupci "Z-skóre". V tomto příkladu použijeme výsledek funkce "STDEV.S", ale můžete také použít výsledek z "STDEV.P".

Zadejte následující vzorec a stiskněte klávesu Enter:

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

Případně můžete zadat vzorec namísto psaní pomocí následujících kroků:

1. Klepněte na buňku F3 a zadejte = (
2. Vyberte buňku E3. (Můžete stisknout tlačítko levé šipky jednou nebo pomocí myši)
3. Zadejte znaménko mínus -
4. Vyberte buňku G3 a stiskněte tlačítko F4 přidat znaky "$" pro vytvoření "absolutního" odkazu na buňku (bude to procházet přes "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 ", pokud budete pokračovat stisknutím tlačítka F4).
5. Typ ) /
6. Vyberte buňku H3 (nebo I3, pokud používáte "STDEV.P") a stiskněte F4 přidat dva znaky "$".
7. stiskněte Enter

Hodnota Z byla vypočtena pro první hodnotu. To je 0,15945 standardních odchylek pod průměrem. Chcete-li zkontrolovat výsledky, můžete standardní odchylku vynásobit tímto výsledkem (6.271629 * -0.15945) a zkontrolovat, zda se výsledek rovná rozdílu mezi hodnotou a střední hodnotou (499-500). Oba výsledky jsou stejné, takže hodnota dává smysl.

Počítáme Z-skóre ostatních hodnot. Zvýrazněte celý sloupec Z-skóre, který začíná buňkou obsahující vzorec.

Stiskněte klávesy Ctrl + D, které zkopírují vzorec v horní části buňky dolů přes všechny ostatní vybrané buňky.

Nyní byl vzorec vyplněn na všechny buňky a každý z nich vždy odkazuje na správné buňky "AVERAGE" a "STDEV.S" nebo "STDEV.P" kvůli znakům "$". Pokud dojde k chybám, jděte zpět a ujistěte se, že znaky "$" jsou zahrnuty do zadaného vzorce.

Výpočet skóre Z bez použití pomocných buněk

Pomocné buňky ukládají výsledek, jako jsou výsledky ukládání výsledků funkcí "AVERAGE", "STDEV.S" a "STDEV.P". Mohou být užitečné, ale nejsou vždy nutné. Můžete je přeskočit při výpočtu Z-Score pomocí obecných vzorců.

Zde je jedna funkce "STDEV.S":

= (Hodnota-AVERAGE (Hodnoty)) / STDEV.S (Hodnoty)

A jeden pomocí funkce "STEV.P":

= (Hodnota-AVERAGE (Hodnoty)) / STDEV.P (Hodnoty)

Při zadávání buněčných rozsahů pro "Hodnoty" ve funkcích se ujistěte, že jste přidali absolutní reference ("$" pomocí F4), takže když "vyplníte", nevypočítejte průměrnou nebo směrodatnou odchylku jiného rozsahu buněk v každé formulaci.

Pokud máte velkou sadu dat, může být efektivnější používat pomocné buňky, protože nespočítá výsledek funkcí "AVERAGE" a "STDEV.S" nebo "STDEV.P" pokaždé, když ukládá zdroje procesoru a urychlení doby potřebné k výpočtu výsledků.

Také "$ G $ 3" ​​trvá méně bajtů pro ukládání a méně RAM načíst než "AVERAGE ($ E $ 3: $ E $ 12).". To je důležité, protože standardní 32bitová verze aplikace Excel je omezena na 2 GB paměti RAM (64bitová verze nemá žádné omezení, kolik paměti RAM lze použít).