Kā aprēķināt Z rādītāju, izmantojot Microsoft Excel

Z rādītājs ir statistiska vērtība, kas norāda, cik standarta noviržu gadījumā konkrētā vērtība ir no visas datu kopas vidējā. Lai aprēķinātu datu vidējo un standartnovirzi, varat izmantot formulas AVERAGE un STDEV.S vai STDEV.P un pēc tam izmantot šos rezultātus, lai noteiktu katras vērtības Z-rādītāju.

Kas ir Z rādītājs un ko dara funkcijas AVERAGE, STDEV.S un STDEV.P?

Z-rādītājs ir vienkāršs veids, kā salīdzināt divu dažādu datu kopu vērtības. To definē kā standarta noviržu skaitu no vidējā datu punkta. Vispārīgā formula izskatās šādi:

= [DataPoint-AVERAGE (datu kopa)] / STDEV (datu kopa)

Šis piemērs palīdzēs precizēt. Pieņemsim, ka vēlaties salīdzināt divu Algebra studentu testa rezultātus, kurus māca dažādi skolotāji. Jūs zināt, ka pirmais students ieguva 95% no gala eksāmena vienā klasē, bet otras klases skolnieks ieguva 87%.

No pirmā acu uzmetiena 95% atzīme ir iespaidīgāka, bet ja otrās klases skolotājs pasniegtu grūtāku eksāmenu? Jūs varētu aprēķināt katra skolēna rezultāta Z-punktu, pamatojoties uz katras klases vidējiem rādītājiem un katrā klasē iegūto punktu standartnovirzi. Salīdzinot abu studentu Z-rādītājus, varētu atklāt, ka studentam ar 87% vērtējumu klājās labāk nekā pārējai viņu klasei, nekā studentam ar 98% punktu, salīdzinot ar pārējo viņu klasi.

Pirmā nepieciešamā statistiskā vērtība ir “vidējā”, un Excel funkcija “AVERAGE” aprēķina šo vērtību. Tas vienkārši saskaita visas vērtības šūnu diapazonā un sadala šo summu ar šūnu skaitu, kas satur skaitliskas vērtības (tas ignorē tukšās šūnas).

Otra mums vajadzīgā statistiskā vērtība ir “standartnovirze”, un programmai Excel ir divas dažādas funkcijas, lai nedaudz atšķirīgi aprēķinātu standartnovirzi.

Iepriekšējās Excel versijās bija tikai funkcija “STDEV”, kas aprēķina standartnovirzi, vienlaikus apstrādājot datus kā populācijas “paraugu”. Excel 2010 sadalīja to divās funkcijās, kas aprēķina standarta novirzi:

  • STDEV.S: Šī funkcija ir identiska iepriekšējai “STDEV” funkcijai. Tas aprēķina standartnovirzi, vienlaikus apstrādājot datus kā populācijas “izlasi”. Populācijas paraugs varētu būt kaut kas līdzīgs konkrētiem odiem, kas savākti pētniecības projektam, vai automašīnām, kas tika atstātas malā un izmantotas avārijas drošības testēšanai.
  • STDEV.P: Šī funkcija aprēķina standartnovirzi, vienlaikus apstrādājot datus kā visu populāciju. Visa populācija būtu kaut kas līdzīgs visiem odiem uz Zemes vai katrai automašīnai konkrēta modeļa sērijā.

Jūsu izvēlētais ir balstīts uz jūsu datu kopu. Atšķirība parasti būs maza, taču funkcijas “STDEV.P” rezultāts vienmēr būs mazāks nekā funkcijas “STDEV.S” rezultāts tai pašai datu kopai. Tā ir konservatīvāka pieeja, pieņemot, ka datos ir lielāka mainība.

Apskatīsim piemēru

Piemēram, mums ir divas kolonnas (“Vērtības” un “Z-rādītājs”) un trīs “palīga” šūnas funkciju “AVERAGE”, “STDEV.S” un “STDEV.P” rezultātu glabāšanai. Kolonnā “Vērtības” ir desmit nejauši izvēlēti skaitļi, kuru centrā ir 500, un kolonnā “Z-Score” mēs aprēķinām Z-Score, izmantojot rezultātus, kas saglabāti šūnās “palīgs”.

Pirmkārt, mēs aprēķināsim vērtību vidējo vērtību, izmantojot funkciju “AVERAGE”. Atlasiet šūnu, kurā glabāsiet funkcijas “AVERAGE” rezultātu.

Ievadiet šādu formulu un nospiediet taustiņu Enter vai izmantojiet izvēlni “Formulas”.

= VIDĒJAIS (E2: E13)

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

Logā Funkciju argumenti atlasiet visas šūnas slejā “Vērtības” kā lauka “Number1” ievadi. Jums nav jāuztraucas par lauku “Number2”.

Tagad nospiediet “OK”.

Pēc tam mums jāaprēķina vērtību standartnovirze, 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ā rezultāts tiks saglabāts.

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

= STDEV.S (E3: E12)

Lai piekļūtu funkcijai, izmantojot izvēlni “Formulas”, atlasiet nolaižamo izvēlni “Papildu funkcijas”, atlasiet opciju “Statistika”, nedaudz ritiniet uz leju un pēc tam noklikšķiniet uz komandas “STDEV.S”.

Logā Funkciju argumenti atlasiet visas šūnas slejā “Vērtības” kā lauka “Number1” ievadi. Arī šeit nav jāuztraucas par lauku “Number2”.

Tagad nospiediet “OK”.

Pēc tam mēs aprēķināsim standartnovirzi, izmantojot funkciju “STDEV.P”. Atlasiet šūnu, kurā rezultāts tiks saglabāts.

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

= STDEV.P (E3: E12)

Lai piekļūtu funkcijai, izmantojot izvēlni “Formulas”, atlasiet nolaižamo izvēlni “Papildu funkcijas”, atlasiet opciju “Statistika”, nedaudz ritiniet uz leju un pēc tam noklikšķiniet uz formulas “STDEV.P”.

Logā Funkciju argumenti atlasiet visas šūnas slejā “Vērtības” kā lauka “Number1” ievadi. Atkal jums nebūs jāuztraucas par lauku “Number2”.

Tagad nospiediet “OK”.

Tagad, kad esam aprēķinājuši savu datu vidējo un standartnovirzi, mums ir viss nepieciešamais, lai aprēķinātu Z-rādītāju. Mēs varam izmantot vienkāršu formulu, kas atsaucas uz šūnām, kas satur funkciju “AVERAGE” un “STDEV.S” vai “STDEV.P” rezultātus.

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

Ievadiet šādu formulu un nospiediet taustiņu Enter:

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

Varat arī rakstīšanas vietā izmantot šādas darbības, lai ievadītu formulu:

  1. Noklikšķiniet uz šūnas F3 un ierakstiet =(
  2. Atlasiet šūnu E3. (Varat vienreiz nospiest kreiso bulttaustiņu vai izmantot peli)
  3. Ierakstiet mīnusa zīmi -
  4. Atlasiet šūnu G3, pēc tam nospiediet taustiņu F4, lai pievienotu rakstzīmes “$”, lai šūnā izdarītu “absolūtu” atsauci (tā pārvietosies pa “G3”> “ $ G $ 3”> “G $ 3”> “ $ G3 ″> “G3”, ja turpina nospiest taustiņu F4 )
  5. Tips )/
  6. Atlasiet šūnu H3 (vai I3, ja izmantojat “STDEV.P”) un nospiediet F4, lai pievienotu divas “$” rakstzīmes.
  7. Nospiediet Enter

Pirmajai vērtībai ir aprēķināts Z rādītājs. Tas ir 0,15945 standartnovirzes zem vidējā. Lai pārbaudītu rezultātus, varat reizināt standarta novirzi 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 (499-500). Abi rezultāti ir vienādi, tāpēc vērtībai ir jēga.

Aprēķināsim pārējo vērtību Z-rādītājus. Iezīmējiet visu kolonnu Z-Score, sākot ar šūnu, kurā ir formula.

Nospiediet Ctrl + D, kas kopē formulu augšējā šūnā lejup pa visām pārējām atlasītajām šūnām.

Tagad formula ir “aizpildīta” visām šūnām, un katra rakstzīmju “$” dēļ vienmēr atsauces uz pareizajām šūnām “AVERAGE” un “STDEV.S” vai “STDEV.P”. Ja rodas kļūdas, atgriezieties un pārliecinieties, vai ievadītajā formulā ir iekļautas rakstzīmes “$”.

Z-rādītāja aprēķināšana, neizmantojot šūnas “Palīgs”

Palīgšūnas saglabā rezultātu, piemēram, funkcijas, kurās tiek glabāti funkciju “AVERAGE”, “STDEV.S” un “STDEV.P” rezultāti. Tie var būt noderīgi, bet ne vienmēr ir nepieciešami. Aprēķinot Z-rādītāju, tos var pilnībā izlaist, tā vietā izmantojot šīs vispārinātās formulas.

Šeit ir viens, 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 funkciju “Vērtības” šūnu diapazonus, noteikti pievienojiet absolūtās atsauces (“$”, izmantojot F4), lai “aizpildot” jūs neaprēķinātu cita diapazona vidējo vai standarta novirzi šūnu katrā formulā.

Ja jums ir liela datu kopa, var būt efektīvāk izmantot palīgu šūnas, jo tā katru reizi neaprēķina funkciju “AVERAGE” un “STDEV.S” vai “STDEV.P” rezultātu, ietaupot procesora resursus un paātrinot laiku, kas vajadzīgs rezultātu aprēķināšanai.

Turklāt “$ G $ 3” glabāšanai nepieciešams mazāk baitu un mazākas RAM ielādēšanai nekā “AVERAGE ($ E $ 3: $ E $ 12).”. Tas ir svarīgi, jo standarta 32 bitu Excel versija ir ierobežota ar 2 GB RAM (64 bitu versijai nav ierobežojumu attiecībā uz to, cik daudz RAM var izmantot).