Z-pontszám kiszámítása a Microsoft Excel használatával

A Z-Score egy statisztikai érték, amely megmondja, hogy egy adott érték hány szórásnál fordul elő a teljes adatkészlet átlagától. Az ÁTLAG és az STDEV.S vagy az STDEV.P képletek segítségével kiszámíthatja az adatok átlagát és szórását, majd ezeket az eredményeket felhasználva meghatározhatja az egyes értékek Z-pontszámát.

Mi az a Z-Score, és mit csinálnak az ÁTLAG, az STDEV.S és az STDEV.P függvények?

A Z-Score egy egyszerű módszer két különböző adatsor értékeinek összehasonlítására. Meghatározza, hogy a standard eltérések száma eltér az adatpont átlagától. Az általános képlet így néz ki:

= (DataPoint-ÁTLAG (DataSet)) / STDEV (DataSet)

Itt van egy példa a tisztázáshoz. Tegyük fel, hogy két Algebra hallgató teszt eredményeit szerette volna összehasonlítani, különböző tanárok tanításával. Tudod, hogy az első hallgató az egyik osztályban 95% -ot kapott a záróvizsgán, a másik osztály tanulója pedig 87% -ot ért el.

Első ránézésre a 95% -os osztályzat lenyűgözőbb, de mi van akkor, ha a második osztály tanára nehezebb vizsgát ad? Kiszámíthatja az egyes diákok pontszámának Z-pontszámát az egyes osztályok átlagos pontszámai és az egyes osztályok pontszámainak szórása alapján. A két diák Z-pontszámainak összehasonlításával kiderülhet, hogy a 87% -os pontszámmal rendelkező hallgató jobban teljesített az osztály többi tagjához képest, mint a 98% -os pontszámmal rendelkező tanuló az osztály többi tagjához képest.

Az első statisztikai érték, amire szüksége van, az „átlag”, és az Excel „ÁTLAG” funkciója kiszámítja ezt az értéket. Egyszerűen összeadja az összes cellatartomány értékét, és elosztja ezt az összeget a numerikus értékeket tartalmazó cellák számával (figyelmen kívül hagyja az üres cellákat).

A másik statisztikai érték, amelyre szükségünk van, a „szórás”, és 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óiban csak az „STDEV” funkció volt, amely kiszámítja a szórást, miközben az adatokat egy populáció „mintájának” kezeli. Az Excel 2010 ezt két funkcióra bontotta, amelyek kiszámítják a szórást:

  • STDEV.S: Ez a funkció megegyezik az előző „STDEV” funkcióval. Kiszámítja a szórást, miközben az adatokat egy populáció „mintaként” kezeli. A populáció mintája lehet valami, mint például a kutatási projekt számára gyűjtött szúnyogok, vagy autók, amelyeket félretettek és a balesetbiztonsági tesztekhez használtak.
  • STDEV.P: Ez a függvény kiszámítja a szórást, miközben az adatokat a teljes populációként kezeli. A teljes populáció olyan lenne, mint a Föld összes szúnyogja, vagy egy adott modell gyártásakor minden autó.

Amit választ, az adatkészlete alapján történik. A különbség általában kicsi, de az „STDEV.P” függvény eredménye mindig kisebb lesz, mint az ugyanazon adatsor „STDEV.S” funkciójának eredménye. Konzervatívabb megközelítés feltételezni, hogy az adatokban nagyobb a változatosság.

Nézzünk meg egy példát

Például két oszlopunk van („Értékek” és „Z-Score”) és három „segítő” cella az „ÁTLAG”, „STDEV.S” és „STDEV.P” függvény eredményeinek tárolására. Az „Értékek” oszlop tíz véletlenszerű számot tartalmaz, amelyek középpontja 500 körül van, és a „Z-Score” oszlopban számoljuk ki a Z-Score értékét a „helper” cellákban tárolt eredmények felhasználásával.

Először az „ÁTLAG” funkció segítségével kiszámítjuk az értékek átlagát. Válassza ki azt a cellát, ahol tárolni fogja az „ÁTLAG” funkció eredményét.

Írja be a következő képletet, és nyomja meg az Enter-t vagy használja a „Képletek” menüt.

= ÁTLAG (E2: E13)

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

A Funkció Argumentumok ablakban válassza ki az „Értékek” oszlop összes celláját a „Szám1” mező bemenetének. Nem kell aggódnia a „Szám2” mező miatt.

Most nyomja meg az „OK” gombot.

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

A standard eltérés kiszámításához az „STDEV.S” funkció használatával írja be ezt a képletet, és nyomja meg az Enter billentyűt (vagy érje el a „Képletek” menüből).

= STDEV.S (E3: E12)

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

A Funkció Argumentumok ablakban válassza ki az „Értékek” oszlop összes celláját a „Szám1” mező bemenetének. Itt sem kell aggódnia a „Szám2” mező miatt.

Most nyomja meg az „OK” gombot.

Ezután kiszámoljuk a szórást az „STDEV.P” függvény segítségével. Válassza ki azt a cellát, ahol az eredmény tárolásra kerül.

A standard eltérés kiszámításához az „STDEV.P” funkció segítségével írja be ezt a képletet, és nyomja meg az Enter billentyűt (vagy érje el a „Képletek” menüből).

= STDEV.P (E3: E12)

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

A Funkció Argumentumok ablakban válassza ki az „Értékek” oszlop összes celláját a „Szám1” mező bemenetének. Ismét nem kell aggódnia a „Szám2” mező miatt.

Most nyomja meg az „OK” gombot.

Most, hogy kiszámoltuk adataink átlagát és szórását, minden szükséges megvan a Z-Pontszám kiszámításához. Használhatunk egy egyszerű képletet, amely az „ÁTLAG” és „STDEV.S” vagy „STDEV.P” függvény eredményeit tartalmazó cellákra hivatkozik.

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

Írja be a következő képletet, és nyomja meg az Enter billentyűt:

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

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

  1. Kattintson az F3 cellára, és írja be =(
  2. Válassza ki az E3 cellát. (Nyomhatja meg egyszer a balra nyíl billentyűt , vagy használhatja az egeret)
  3. Írja be a mínusz jelet -
  4. Válassza ki a G3 cellát, majd nyomja meg az F4 billentyűt a „$” karakterek hozzáadásához, hogy 'abszolút' hivatkozást tegyen a cellára (a „G3”> „ $ G $ 3”> „G $ 3”> „ $ G3 ″> „G3”, ha továbbra is megnyomja az F4 billentyűt )
  5. típus )/
  6. Válassza ki a H3 cellát (vagy az I3-at, ha az „STDEV.P” fájlt használja), és nyomja meg az F4 billentyűt a két „$” karakter hozzáadásához.
  7. nyomd meg az Entert

Az első értékre kiszámolták a Z-pontszámot. Ez 0,15945 szórás az átlag alatt van. Az eredmények ellenőrzéséhez megszorozhatja a szórást ezzel az eredménnyel (6,271629 * -0,15945), és ellenőrizheti, 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éknek van értelme.

Számítsuk ki a többi érték Z-pontszámát. Jelölje ki a teljes „Z-Score” oszlopot a képletet tartalmazó cellával kezdve.

Nyomja meg a Ctrl + D billentyűkombinációt, amely a felső cellában lévő képletet lefelé másolja az összes többi kijelölt cellán keresztül.

Most a képletet az összes cellára 'kitöltötték', és mindegyikük mindig a megfelelő „ÁTLAG” és „STDEV.S” vagy „STDEV.P” cellákra hivatkozik majd a „$” karakterek miatt. Ha hibákat észlel, térjen vissza, és ellenőrizze, hogy a „$” karakterek szerepelnek-e a megadott képletben.

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

A segítő cellák tárolnak egy eredményt, mint például az „ÁTLAG”, az „STDEV.S” és az „STDEV.P” funkciók eredményeit. Hasznosak lehetnek, de nem mindig szükségesek. A Z-pontszám kiszámításakor ezeket teljesen kihagyhatja, ahelyett, hogy a következő általános képleteket használná.

Itt található az „STDEV.S” funkció:

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

És egy a „STEV.P” funkciót használva:

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

Amikor beírja a függvények „Értékek” cellatartományait, feltétlenül adjon meg abszolút referenciákat („$” az F4 használatával), hogy „kitöltés” ​​során ne egy másik tartomány átlagát vagy szórását számítsa ki sejtek minden képletben.

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

Ezenkívül a „$ G $ 3” tárolásához kevesebb bájt és kevesebb RAM betöltése szükséges, mint az „AVERAGE ($ E $ 3: $ E $ 12)”. Ez azért fontos, mert az Excel szokásos 32 bites verziója 2 GB RAM-ra korlátozódik (a 64 bites verzióban nincsenek korlátozások a RAM felhasználhatóságára vonatkozóan).