Kā aprēķināt Z-punktu skaitu, izmantojot Microsoft Excel

Z-Punkts ir statistiska vērtība, kas norāda, cik standarta novirzes konkrētā vērtība notiek no visa datu kopas vidējā lieluma. Varat izmantot AVERAGE un STDEV.S vai STDEV.P formulas, lai aprēķinātu datu vidējo un standarta novirzi, un pēc tam izmantojiet šos rezultātus, lai noteiktu katra vērtības Z-punktu skaitu.

Kas ir Z-rezultāts un ko dara AVERAGE, STDEV.S un STDEV.P funkcijas?

Z-Punkts ir vienkāršs veids, kā salīdzināt divas dažādu datu kopas. To definē kā standarta noviržu skaitu no vidējā datu punkta. Vispārīgā formula izskatās šādi:

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

Šeit ir piemērs, lai palīdzētu noskaidrot. Saka, ka jūs vēlētos salīdzināt divu Algebra skolēnu, kurus māca dažādi skolotāji, testu rezultātus. Jūs zināt, ka pirmais students ieguva 95% no gala eksāmena vienā klasē, un otras klases students ieguva 87%.

No pirmā acu uzmetiena, 95% pakāpe ir iespaidīgāka, bet kas notiks, ja otrās klases skolotāja eksāmens būtu grūtāks? Jūs varat aprēķināt katra skolēna Z-punktu skaitu, pamatojoties uz vidējiem rādītājiem katrā klasē un rezultātu standarta novirzi katrā klasē. Salīdzinot abu studentu Z-rādītājus, varēja secināt, ka students, kuram ir 87% rezultāts, ir labāk, salīdzinot ar pārējo klasi, nekā students ar 98% rezultātu, salīdzinot ar pārējo klasi.

Pirmā nepieciešamā statistiskā vērtība ir "vidējais" un Excel "AVERAGE" funkcija aprēķina šo vērtību. Tā vienkārši pievieno visas šūnu diapazona vērtības un sadala šo summu ar šūnu skaitu, kas satur skaitliskas vērtības (tā ignorē tukšās šūnas).

Cita statistiskā vērtība, kas mums nepieciešama, ir "standarta novirze", un Excel ir divas dažādas funkcijas, lai aprēķinātu standarta novirzi nedaudz atšķirīgā veidā.

Iepriekšējām Excel versijām bija tikai “STDEV” funkcija, kas aprēķina standarta novirzi, apstrādājot datus kā populācijas „paraugu”. Excel 2010 pārtrauca to divās funkcijās, kas aprēķina standarta novirzi:

• STDEV.S: Šī funkcija ir identiska iepriekšējai “STDEV” funkcijai. Tā aprēķina standarta novirzi, apstrādājot datus par iedzīvotāju “paraugu”. Iedzīvotāju paraugs varētu būt kaut kas līdzīgs konkrētiem moskītiem, kas savākti pētniecības projektam, vai automašīnām, kas tika atceltas un izmantotas avārijas drošības pārbaudēs..
• STDEV.P: Šī funkcija aprēķina standarta novirzi, apstrādājot datus kā visu populāciju. Visa populācija būtu kaut kas līdzīgs visiem uz zemes esošajiem odiem vai katram automašīnai konkrēta modeļa ražošanā.

Ko jūs izvēlaties, ir balstīts uz jūsu datu kopu. Atšķirība parasti būs neliela, bet “STDEV.P” funkcijas rezultāts vienmēr būs mazāks par “STDEV.S” funkcijas rezultātu tādai pašai datu kopai. Konservatīvāka pieeja ir pieņemt, ka dati ir lielāki.

Apskatīsim piemēru

Piemēram, mums ir divas kolonnas (“Vērtības” un “Z-Score”) un trīs “palīgs” šūnas “AVERAGE”, “STDEV.S” un “STDEV.P” funkciju glabāšanai. Ailē “Vērtības” ir desmit izlases numuri, kas centrēti ap 500, un kolonna “Z-Score”, kur mēs aprēķināsim Z-punktu skaitu, izmantojot rezultātus, kas saglabāti “palīgs” šūnās.

Pirmkārt, mēs aprēķināsim vidējo vērtību, izmantojot funkciju “AVERAGE”. Izvēlieties šūnu, kurā tiks saglabāta funkcija “AVERAGE”.

Ievadiet šādu formulu un nospiediet ievadiet -vai- izmantojiet izvēlni “Formulas”.

= VIDĒJAIS (E2: E13)

Lai piekļūtu funkcijai izvēlnē “Formulas”, nolaižamajā izvēlnē atlasiet “Vairāk funkciju”, atlasiet opciju “Statistikas” un pēc tam noklikšķiniet uz “AVERAGE”.

Logā Funkcija Argumenti atlasiet kolonnā “Vērtības” visas šūnas kā lauka “Numurs1” ievadi. Jums nav jāuztraucas par “Number2” lauku.

Tagad nospiediet „OK”.

Tālāk mums ir jāaprēķina vērtību standarta novirze, izmantojot funkciju “STDEV.S” vai “STDEV.P”. Šajā piemērā mēs parādīsim, kā aprēķināt abas vērtības, sākot ar “STDEV.S”. Atlasiet šūnu, kurā tiks saglabāts rezultāts.

Lai aprēķinātu standarta novirzi, izmantojot funkciju “STDEV.S”, ierakstiet šo formulu un nospiediet taustiņu Enter (vai piekļūstiet tai, izmantojot izvēlni “Formulas”).

= STDEV.S (E3: E12)

Lai piekļūtu funkcijai izvēlnē “Formulas”, nolaižamajā izvēlnē atlasiet “Vairāk funkciju”, atlasiet opciju “Statistika”, ritiniet uz leju mazliet un pēc tam noklikšķiniet uz komandas “STDEV.S”.

Logā Funkcija Argumenti atlasiet kolonnā “Vērtības” visas šūnas kā lauka “Numurs1” ievadi. Arī šeit nav jāuztraucas par „Number2” lauku.

Tagad nospiediet „OK”.

Tālāk mēs aprēķināsim standarta novirzi, izmantojot funkciju “STDEV.P”. Atlasiet šūnu, kurā tiks saglabāts rezultāts.

Lai aprēķinātu standarta novirzi, izmantojot funkciju “STDEV.P”, ierakstiet šo formulu un nospiediet taustiņu Enter (vai piekļūstiet tai, izmantojot izvēlni “Formulas”).

= STDEV.P (E3: E12)

Lai piekļūtu funkcijai izvēlnē “Formulas”, nolaižamajā izvēlnē atlasiet “Vairāk funkciju”, atlasiet opciju “Statistikas”, ritiniet uz leju mazliet un pēc tam noklikšķiniet uz formulas “STDEV.P”.

Logā Funkcija Argumenti atlasiet kolonnā “Vērtības” visas šūnas kā lauka “Numurs1” ievadi. Atkal, jums nav jāuztraucas par „Number2” lauku.

Tagad nospiediet „OK”.

Tagad, kad mēs esam aprēķinājuši mūsu datu vidējo un standarta novirzi, mums ir viss, kas nepieciešams, lai aprēķinātu Z-punktu. Mēs varam izmantot vienkāršu formulu, kas atsaucas uz šūnām ar “AVERAGE” un “STDEV.S” vai “STDEV.P” funkciju rezultātiem.

Kolonnā “Z-Score” atlasiet pirmo šūnu. Šajā piemērā izmantosim “STDEV.S” funkcijas rezultātus, bet jūs varētu izmantot arī rezultātu no “STDEV.P”.

Ierakstiet šādu formulu un nospiediet Enter:

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

Varat arī izmantot šādas darbības, lai ievadītu formulu, nevis ierakstītu:

1. Noklikšķiniet uz šūnas F3 un ierakstiet = (
2. Izvēlieties šūnu E3. (Varat nospiest kreisās bultiņas taustiņu vienreiz vai izmantojiet peli)
3. Ierakstiet mīnusa zīmi -
4. Atlasiet šūnu G3, tad nospiediet F4 lai pievienotu “$” rakstzīmes, lai padarītu “absolūtu” atsauci uz šūnu (tas iet caur „G3”> “$G$3 ">" G$3 ">"$G3 ">" G3 ", ja turpināt nospiest F4)
5. Tips ) /
6. Izvēlieties šūnu H3 (vai I3, ja lietojat “STDEV.P”) un nospiediet F4 lai pievienotu divas “$” rakstzīmes.
7. Nospiediet Enter

Z-rādītājs ir aprēķināts pirmajai vērtībai. Tas ir 0.15945 standarta novirzes zem vidējā. Lai pārbaudītu rezultātus, standarta rezultātu var reizināt ar šo rezultātu (6.271629 * -0.15945) un pārbaudīt, vai rezultāts ir vienāds ar starpību starp vērtību un vidējo vērtību (499-500). Abi rezultāti ir vienādi, tāpēc vērtība ir jēga.

Aprēķināsim pārējo vērtību Z-punktus. Iezīmējiet visu “Z-Score” kolonnu, sākot ar šūnu ar formulu.

Nospiediet Ctrl + D, kas kopē formulu augšējā šūnā, izmantojot visas pārējās atlasītās šūnas.

Tagad formula ir aizpildīta uz visām šūnām, un katra no tām vienmēr atsauksies uz pareizajām “AVERAGE” un “STDEV.S” vai “STDEV.P” šūnām, jo ​​ir “$” rakstzīmes. Ja saņemat kļūdas, dodieties atpakaļ un pārliecinieties, ka ievadītajā formulā ir iekļautas rakstzīmes “$”.

Z-punktu aprēķināšana, neizmantojot “Helper” šūnas

Palīdzības šūnas glabā rezultātu, piemēram, tās, kas glabā “AVERAGE”, “STDEV.S” un “STDEV.P” funkciju rezultātus. Tie var būt noderīgi, bet ne vienmēr ir nepieciešami. Jūs varat izlaist tos pilnībā, aprēķinot Z-punktu skaitu, izmantojot šādas vispārinātās formulas.

Lūk, izmantojot funkciju “STDEV.S”:

= (Vērtība-AVERAGE (vērtības)) / STDEV.S (vērtības)

Un viens, izmantojot funkciju “STEV.P”:

= (Vērtība-AVERAGE (vērtības)) / STDEV.P (vērtības)

Ievadot šūnu diapazonu “Vērtībām” funkcijās, noteikti pievienojiet absolūtās atsauces (“$”, izmantojot F4), lai, “aizpildot”, jūs neparedzat vidējo vai standarta novirzi no cita diapazona šūnu skaits katrā formulā.

Ja jums ir liels datu kopums, var būt lietderīgāk izmantot palīgiekārtas, jo tas ne vienmēr aprēķina “AVERAGE” un “STDEV.S” vai “STDEV.P” funkciju rezultātus, ietaupot procesora resursus un paātrināt rezultātu aprēķināšanas laiku.

Arī “$ G $ 3” saglabā mazāk baitu, lai saglabātu un mazāk RAM, lai ielādētu, nekā “AVERAGE ($ E $ 3: $ E $ 12)”. Tas ir svarīgi, jo standarta 32 bitu Excel versija ir ierobežota līdz 2 GB RAM (64 bitu versijai nav nekādu ierobežojumu attiecībā uz to, cik daudz RAM var izmantot).