Z-pontszám kiszámítása a Microsoft Excel segítségével

A Z-Score egy statisztikai érték, amely azt jelzi, hogy hány standard eltérés van egy adott értéknél a teljes adathalmaz átlagától. Az AVERAGE és STDEV.S vagy STDEV.P képletek segítségével kiszámíthatja az adatok átlagát és szórását, majd az eredményeket használhatja az egyes értékek Z-pontszámának meghatározására.

Mi a Z-pontszám és mit tesz az AVERAGE, STDEV.S és STDEV.P funkciók?

A Z-pontszám egy egyszerű módja annak, hogy összehasonlítsuk az értékeket két különböző adatkészletből. Ez az adatponttól mért átlagos eltérések számát jelenti. Az általános képlet így néz ki:

= (Adatpont-ÁTLAG (adatbázisba)) / STDEV (adatbázisba)

Íme egy példa a tisztázásra. Mondjuk, hogy összehasonlítani szeretné a két tanár által tanított két algebra diák eredményeit. Tudja, hogy az első hallgató 95% -ot kapott az egyik osztályban a záróvizsgáról, és a másik osztályba tartozó diák 87% -ot szerzett.

Első pillantásra a 95% -os osztály sokkal lenyűgözőbb, de mi van, ha a második osztály tanára nehezebb vizsgát szerez? Az egyes osztályok átlagos pontszámai és az egyes osztályok pontszámainak szórása alapján kiszámíthatod az egyes diákok pontszámát. A két diák Z-pontszámait összehasonlítva kiderült, hogy a 87% -os pontszámmal rendelkező diák jobban teljesítette az osztály többi részéhez képest, mint a 98% -os pontszámmal rendelkező diák, szemben a többi osztályukkal.

Az első statisztikai érték az „átlag” és az „AVERAGE” függvény kiszámítja ezt az értéket. Ez egyszerűen összeadja az összes értéket egy cellatartományban, és ezt az összeget osztja a számértékeket tartalmazó cellák számával (ez figyelmen kívül hagyja az üres cellákat).

A másik statisztikai érték, amire szükségünk van, a „standard deviáció”, az Excel két különböző funkcióval rendelkezik a szórás kiszámításához kissé eltérő módon.

Az Excel korábbi verziói csak "STDEV" funkcióval rendelkeztek, amely kiszámítja a standard eltérést, miközben az adatokat egy "minta" -ként kezeli. Az Excel 2010 ezt a funkciót két, a szórást kiszámító funkcióvá tette:

• STDEV.S: Ez a funkció megegyezik az előző „STDEV” funkcióval. Kiszámítja a standard eltérést, miközben az adatokat egy populáció „mintájának” tekinti. Egy népesség mintája lehet valami olyan, mint a kutatási projekthez gyűjtött szúnyogok, vagy a balesetbiztosítási vizsgálatokhoz használt autók..
• STDEV.P: Ez a függvény kiszámítja a standard eltérést, miközben az adatokat a teljes népességnek tekinti. Egy egész népesség olyan lenne, mint a Földön található szúnyogok, vagy egy adott modell gyártási szakaszában minden autó.

A választott adatok az adatkészletén alapulnak. A különbség általában kicsi, de az „STDEV.P” funkció eredménye mindig kisebb lesz, mint az azonos adatkészlet „STDEV.S” funkciójának eredménye. Konzervatívabb megközelítés, ha feltételezzük, hogy az adatokban nagyobb a variabilitás.

Nézzünk egy példát

Példánkban két oszlop („Értékek” és „Z-pontszám”) és három „segítő” cella van a „AVERAGE”, „STDEV.S” és „STDEV.P” funkciók tárolására. Az "Értékek" oszlop tíz véletlen számot tartalmaz 500 körül, és a "Z-Score" oszlopban a Z-pontszámot a "helper" cellákban tárolt eredmények alapján számítjuk ki..

Először az „AVERAGE” funkció segítségével számítjuk ki az értékek átlagát. Jelölje ki azt a cellát, ahol tárolja az „AVERAGE” funkció eredményét.

Írja be a következő képletet, majd nyomja meg a gombot belép -vagy - használja a „Formulas” menüt.

= ÁTLAG (E2: E13)

A „Formulák” menüben a funkció eléréséhez válassza a „További funkciók” legördülő listát, válassza ki a „Statisztikai” opciót, majd kattintson az „AVERAGE” gombra.

A Function Arguments ablakban válassza ki az „Értékek” oszlopban lévő összes cellát a „Number1” mező beviteléhez. Nem kell aggódnia a „Number2” mezőben.

Most nyomja meg az „OK” gombot.

Ezután az „STDEV.S” vagy „STDEV.P” függvény segítségével kell kiszámítani az értékek szórását. Ebben a példában megmutatjuk, hogyan számíthatunk ki mindkét értéket, kezdve a „STDEV.S.” Segítségével. Válassza ki azt a cellát, ahol az eredmény tárolódik.

A standard deviáció kiszámításához az „STDEV.S” funkcióval írja be ezt a képletet, és nyomja meg az Entert (vagy érintse meg a „Formulák” menüben).

= STDEV.S (E3: E12)

A „Formulák” menüben a funkció eléréséhez válassza ki a „További funkciók” legördülő listát, válassza ki a „Statisztikai” opciót, görgessen lefelé egy kicsit, majd kattintson a „STDEV.S” parancsra.

A Function Arguments ablakban válassza ki az „Értékek” oszlopban lévő összes cellát a „Number1” mező beviteléhez. Itt sem kell aggódnia a „Number2” mezőben.

Most nyomja meg az „OK” gombot.

Ezután kiszámítjuk a standard eltérést az „STDEV.P” funkció használatával. Válassza ki azt a cellát, ahol az eredmény tárolódik.

A standard eltérés kiszámításához az „STDEV.P” funkcióval írja be ezt a képletet, és nyomja meg az Entert (vagy érintse meg a „Formulák” menüben).

= STDEV.P (E3: E12)

A „Formulák” menüben a funkció eléréséhez válassza ki a „További funkciók” legördülő listát, válassza ki a „Statisztikai” opciót, görgessen lefelé egy kicsit, majd kattintson az „STDEV.P” képletre.

A Function Arguments ablakban válassza ki az „Értékek” oszlopban lévő összes cellát a „Number1” mező beviteléhez. Ismét nem kell aggódnia a „Number2” mezőben.

Most nyomja meg az „OK” gombot.

Most, hogy kiszámítottuk az adatok átlagát és szórását, mindannyian meg kell számolnunk a Z-pontszámot. Egy egyszerű képletet használhatunk, amely az „AVERAGE” és „STDEV.S” vagy „STDEV.P” funkciókat tartalmazó cellákat tartalmazza..

Válassza ki az első cellát a „Z-Score” oszlopban. Ehhez a példához az „STDEV.S” függvény eredményét fogjuk használni, de az „STDEV.P.

Írja be a következő képletet, majd nyomja meg az Entert:

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

Alternatív megoldásként a következő lépésekkel írhatja be a képletet a gépelés helyett:

1. Kattintson az F3 cellára és írja be = (
2. Válassza ki az E3 cellát. (Megnyomhatja a bal nyíl kulcs egyszer vagy használja az egeret)
3. Írja be a mínusz jelet -
4. Válassza ki a G3 cellát, majd nyomja meg a gombot F4 hozzáadni a „$” karaktereket, hogy „abszolút” utalást kapjon a cellára (a „G3”>$G$3 ">" G$3 ">"$G3 ">" G3 ", ha folytatja a nyomást F4)
5. típus ) /
6. Válassza ki a H3 cellát (vagy az „I3“ STDEV.P ”használatakor), majd nyomja meg a gombot F4 a két „$” karakter hozzáadásához.
7. nyomd meg az Entert

A Z-Score az első értékre lett kiszámítva. Ez az átlag alatt 0,15945 szórás. Az eredmények ellenőrzéséhez megszorozhatja a standard eltérést ezzel az eredménnyel (6.271629 * -0.15945), és ellenőrizze, hogy az eredmény megegyezik-e az érték és az átlag közötti különbséggel (499-500). Mindkét eredmény egyenlő, így az érték értelme.

Számítsuk ki a többi érték Z-pontját. Jelölje ki az egész „Z-Score” oszlopot a képletet tartalmazó cellából.

Nyomja meg a Ctrl + D billentyűkombinációt, amely átmásolja a képletet a felső cellában a többi kiválasztott cellán keresztül.

Most a képlet „tele van” az összes cellára, és mindegyik mindig a megfelelő „AVERAGE” és „STDEV.S” vagy „STDEV.P” cellákra hivatkozik a „$” karakterek miatt. Ha hibákat tapasztal, menjen vissza, és győződjön meg róla, hogy a „$” karakterek szerepelnek a megadott képletben.

A Z-pontszám kiszámítása a „Helper” cellák használata nélkül

A segítő cellák eredményeket tárolnak, mint amilyenek az „AVERAGE”, „STDEV.S” és „STDEV.P” funkciók eredményei. Ezek hasznosak lehetnek, de nem mindig szükségesek. A Z-pontozás kiszámításakor a következő általánosított képletek segítségével kihagyhatja őket.

Itt van egy „STDEV.S” funkció használata:

= (Érték-ÁTLAG (értékek)) / STDEV.S (értékek)

És az egyik a „STEV.P” funkció használatával:

= (Érték-ÁTLAG (értékek)) / STDEV.P (értékek)

Ha beírja a cellák tartományát az „Értékek” funkcióba, akkor feltétlenül adjon hozzá abszolút referenciákat („$” az F4 használatával), hogy amikor „kitölti”, akkor nem kiszámítja a különböző tartományok átlagát vagy szórását minden képletben.

Ha van egy nagy adathalmaz, akkor lehet, hogy hatékonyabb a segítő cellák használata, mert nem számítja ki az „AVERAGE” és az „STDEV.S” vagy az „STDEV.P” funkciók eredményét minden alkalommal, a processzor erőforrásait és az eredmények kiszámításához szükséges idő felgyorsítása.

Továbbá a „$ G $ 3” kevesebb bájtot tárol, és kevesebb RAM-ot tölthet be, mint az „AVERAGE ($ E $ 3: $ E $ 12)”. Ez azért fontos, mert az Excel szabványos 32 bites verziója 2 GB RAM-ra korlátozódik (a 64 bites verziónak nincs korlátozása arra vonatkozóan, hogy mennyi RAM használható).