Z-skóre je statistická hodnota, která vám říká, kolik standardních odchylek má konkrétní hodnota od průměru celého souboru dat. K výpočtu střední hodnoty a směrodatné odchylky dat můžete použít vzorce AVERAGE a STDEV.S nebo STDEV.P a poté tyto výsledky použít k určení Z-skóre každé hodnoty.
Table of Contents
Co je Z-skóre a co dělají funkce AVERAGE, STDEV.S a STDEV.P?
Z-skóre je jednoduchý způsob porovnávání hodnot ze dvou různých souborů dat. Je definován jako počet směrodatných odchylek od průměru, v němž leží datový bod. Obecný vzorec vypadá takto:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Zde je příklad, který pomůže objasnit. Řekněme, že jste chtěli porovnat výsledky testů dvou studentů algebry vyučovaných různými učiteli. Víte, že první student získal 95 % závěrečné zkoušky v jedné třídě a student ve druhé třídě 87 %.
Známka 95 % je na první pohled působivější, ale co kdyby učitel z druhé třídy dal těžší zkoušku? Můžete vypočítat Z-skóre skóre každého studenta na základě průměrného skóre v každé třídě a standardní odchylky skóre v každé třídě. Porovnání Z-skóre dvou studentů mohlo odhalit, že student se skóre 87 % si vedl lépe ve srovnání se zbytkem své třídy než student s 98% skóre ve srovnání se zbytkem své třídy.
První statistická hodnota, kterou potřebujete, je „průměr“ a funkce „PRŮMĚR“ aplikace Excel tuto hodnotu vypočítá. Jednoduše sečte všechny hodnoty v oblasti buněk a vydělí tento součet počtem buněk obsahujících číselné hodnoty (prázdné buňky ignoruje).
Další statistickou hodnotou, kterou potřebujeme, je „směrodatná odchylka“ a Excel má dvě různé funkce pro výpočet směrodatné odchylky mírně odlišnými způsoby.
Předchozí verze Excelu měly pouze funkci „STDEV“, která vypočítává směrodatnou odchylku, přičemž data považuje za „vzorek“ populace. Excel 2010 to rozdělil na dvě funkce, které vypočítají směrodatnou odchylku:
STDEV.S: Tato funkce je totožná s předchozí funkcí „STDEV“. Vypočítá směrodatnou odchylku, přičemž data považuje za „vzorek“ populace. Vzorek populace může být něco jako konkrétní komáři sesbíraní pro výzkumný projekt nebo auta, která byla vyčleněna a použita pro testování bezpečnosti při nárazu.
STDEV.P: Tato funkce vypočítá směrodatnou odchylku, přičemž data považuje za celý soubor. Celá populace by byla něco jako všichni komáři na Zemi nebo každé auto ve výrobě určitého modelu.
To, co si vyberete, závisí na vaší datové sadě. Rozdíl bude obvykle malý, ale výsledek funkce „STDEV.P“ bude vždy menší než výsledek funkce „STDEV.S“ pro stejný soubor dat. Je konzervativnějším přístupem předpokládat, že v datech je větší variabilita.
Podívejme se na příklad
V našem příkladu máme dva sloupce („Hodnoty“ a „Skóre Z“) a tři „pomocné“ buňky pro uložení výsledků funkcí „AVERAGE“, „STDEV.S“ a „STDEV.P“. Sloupec „Hodnoty“ obsahuje deset náhodných čísel se středem kolem 500 a sloupec „Skóre Z“ je místo, kde vypočítáme skóre Z pomocí výsledků uložených v „pomocných“ buňkách.
Nejprve spočítáme průměr hodnot pomocí funkce „PRŮMĚR“. Vyberte buňku, do které uložíte výsledek funkce „PRŮMĚR“.
Zadejte následující vzorec a stiskněte Enter -nebo- použijte nabídku „Vzorce“.
=AVERAGE(E2:E13)
Pro přístup k funkci prostřednictvím nabídky „Vzorce“ vyberte rozbalovací nabídku „Další funkce“, vyberte možnost „Statistika“ a poté 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 si dělat starosti s polem „Číslo2“.
Nyní stiskněte „OK“.
Dále musíme 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, počínaje „STDEV.S.“ Vyberte buňku, do které bude výsledek uložen.
Chcete-li vypočítat směrodatnou odchylku pomocí funkce „STDEV.S“, zadejte tento vzorec a stiskněte Enter (nebo jej otevřete z nabídky „Formulas“).
=STDEV.S(E3:E12)
Chcete-li získat přístup k funkci prostřednictvím nabídky „Vzorce“, vyberte rozbalovací nabídku „Další funkce“, vyberte možnost „Statistika“, posuňte se trochu 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“. Ani zde se nemusíte starat o pole „Číslo2“.
Nyní stiskněte „OK“.
Dále vypočítáme směrodatnou odchylku pomocí funkce „STDEV.P“. Vyberte buňku, do které bude výsledek uložen.
Chcete-li vypočítat směrodatnou odchylku pomocí funkce „STDEV.P“, zadejte tento vzorec a stiskněte Enter (nebo jej otevřete z nabídky „Formulas“).
=STDEV.P(E3:E12)
Chcete-li získat přístup k funkci prostřednictvím nabídky „Vzorce“, vyberte rozbalovací nabídku „Další funkce“, vyberte možnost „Statistika“, posuňte se trochu dolů a klikně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 „OK“.
Nyní, když jsme vypočítali průměr a standardní odchylku našich dat, máme vše, co potřebujeme k výpočtu Z-skóre. 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-Score“. Pro tento příklad 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 Enter:
=(E3-$G$3)/$H$3
Alternativně můžete k zadání vzorce namísto psaní použít následující kroky:
Klikněte na buňku F3 a napište =(
Vyberte buňku E3. (Můžete jednou stisknout klávesu se šipkou doleva nebo použít myš)
Zadejte znaménko mínus –
Vyberte buňku G3 a poté stisknutím F4 přidejte znaky „$“, abyste vytvořili „absolutní“ odkaz na buňku (bude cyklicky procházet „G3“ > „$G$3″ > „G$3″ > „$G3″ > „G3 ” pokud budete pokračovat ve stisku F4)
Typ )/
Vyberte buňku H3 (nebo I3, pokud používáte „STDEV.P“) a stisknutím klávesy F4 přidejte dva znaky „$“.
stiskněte Enter
Z-skóre bylo vypočítáno pro první hodnotu. Je 0,15945 standardní odchylky pod průměrem. Chcete-li zkontrolovat výsledky, můžete tímto výsledkem vynásobit směrodatnou odchylku (6,271629 * -0,15945) a zkontrolovat, zda se výsledek rovná rozdílu mezi hodnotou a průměrem (499-500). Oba výsledky jsou stejné, takže hodnota dává smysl.
Pojďme vypočítat Z-skóre zbývajících hodnot. Zvýrazněte celý sloupec ‚Z-Score‘ počínaje buňkou obsahující vzorec.
Stiskněte Ctrl+D, čímž se vzorec z horní buňky zkopíruje dolů přes všechny ostatní vybrané buňky.
Nyní byl vzorec „vyplněn“ do všech buněk a každá bude vždy odkazovat na správné buňky „AVERAGE“ a „STDEV.S“ nebo „STDEV.P“ kvůli znakům „$“. Pokud se zobrazí chyby, vraťte se zpět a ujistěte se, že zadaný vzorec obsahuje znaky „$“.
Výpočet Z-skóre bez použití ‚pomocných‘ buněk
Pomocné buňky ukládají výsledek, jako jsou ty, které ukládají výsledky funkcí „AVERAGE“, „STDEV.S“ a „STDEV.P“. Mohou být užitečné, ale ne vždy nutné. Při výpočtu Z-skóre je můžete úplně přeskočit pomocí následujících zobecněných vzorců.
Zde je jeden používající funkci „STDEV.S“:
=(Value-AVERAGE(Values))/STDEV.S(Values)
A jeden pomocí funkce „STEV.P“:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Při zadávání rozsahů buněk pro „Hodnoty“ ve funkcích nezapomeňte přidat absolutní odkazy („$“ pomocí F4), abyste při „vyplnění“ nepočítali průměr nebo směrodatnou odchylku jiného rozsahu. buněk v každém vzorci.
Pokud máte velký soubor dat, může být efektivnější použít pomocné buňky, protože pokaždé nevypočítá výsledek funkcí „PRŮMĚR“ a „STDEV.S“ nebo „STDEV.P“, což šetří prostředky procesoru a zrychlení času potřebného k výpočtu výsledků.
Také „$G$3“ vyžaduje méně bajtů k uložení a méně RAM k načtení než „PRŮMĚRNÝ ($E$3:$E$12).“. To je důležité, protože standardní 32bitová verze Excelu je omezena na 2 GB paměti RAM (64bitová verze nemá žádná omezení, kolik paměti RAM lze použít).