Bagaimana Menghitung Z-Score Menggunakan Microsoft Excel

Z-Score adalah nilai statistik yang memberitahu anda berapa banyak penyimpangan piawai nilai tertentu yang berlaku daripada min keseluruhan set data. Anda boleh menggunakan formula AVERAGE dan STDEV.S atau STDEV.P untuk mengira sisihan min dan piawai data anda dan kemudian gunakan hasil tersebut untuk menentukan Z-Skor bagi setiap nilai.

Apakah Z-Score dan apakah fungsi AVERAGE, STDEV.S, dan STDEV.P?

Z-Score adalah cara mudah untuk membandingkan nilai-nilai dari dua set data yang berbeza. Ia ditakrifkan sebagai bilangan penyimpangan piawai dari mean data titik terletak. Formula umum kelihatan seperti ini:

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

Berikut adalah contoh untuk membantu menjelaskan. Katakan anda ingin membandingkan keputusan ujian dua pelajar Algebra yang diajar oleh guru yang berbeza. Anda tahu pelajar pertama mendapat 95% peperiksaan akhir dalam satu kelas, dan pelajar di kelas lain menjaringkan 87%.

Pada pandangan pertama, gred 95% lebih mengagumkan, tetapi bagaimana jika guru kelas kedua memberikan peperiksaan yang lebih sukar? Anda boleh mengira skor Z-skor setiap pelajar berdasarkan skor purata dalam setiap kelas dan sisihan piawai skor dalam setiap kelas. Membandingkan Z-Markah kedua-dua pelajar itu dapat mendedahkan bahawa pelajar dengan skor 87% lebih baik dibandingkan dengan yang lain daripada pelajar berbanding dengan skor 98% berbanding dengan kelas yang lain.

Nilai statistik pertama yang anda perlukan adalah 'bermakna' dan fungsi "AVERAGE" Excel menghitung nilai itu. Ia hanya menambahkan semua nilai dalam julat sel dan membahagikan jumlah itu dengan bilangan sel yang mengandungi nilai berangka (ia mengabaikan sel kosong).

Nilai statistik lain yang kita perlukan adalah 'sisihan piawai' dan Excel mempunyai dua fungsi yang berbeza untuk mengira sisihan piawai dalam cara yang sedikit berbeza.

Versi sebelumnya Excel hanya mempunyai fungsi "STDEV", yang mengira sisihan piawai semasa merawat data sebagai 'contoh' pada populasi. Excel 2010 memecahkan bahawa kepada dua fungsi yang mengira sisihan piawai:

STDEV.S: Fungsi ini adalah sama dengan fungsi "STDEV" sebelum ini. Ia mengira sisihan piawai ketika merawat data sebagai 'contoh' populasi. Sampel penduduk mungkin sesuatu seperti nyamuk tertentu yang dikumpulkan untuk projek penyelidikan atau kereta yang diketepikan dan digunakan untuk ujian keselamatan kemalangan.
STDEV.P: Fungsi ini mengira sisihan piawai semasa merawat data sebagai keseluruhan populasi. Seluruh penduduk akan menjadi seperti semua nyamuk di Bumi atau setiap kereta dalam proses pengeluaran model tertentu.

Yang anda pilih adalah berdasarkan pada set data anda. Perbezaannya biasanya kecil, tetapi hasil fungsi "STDEV.P" akan selalu lebih kecil daripada hasil fungsi "STDEV.S" untuk set data yang sama. Ia adalah pendekatan yang lebih konservatif untuk mengandaikan terdapat variasi dalam data.

Mari kita lihat Contoh

Untuk contoh kami, kami mempunyai dua lajur ("Nilai" dan "Z-Markah") dan tiga sel "penolong" untuk menyimpan hasil fungsi "AVERAGE," "STDEV.S," dan "STDEV.P" Lajur "Nilai" mengandungi sepuluh nombor rawak yang berpusat di sekitar 500, dan lajur "Z-Markah" adalah di mana kita akan mengira Z-Markah menggunakan hasil yang disimpan dalam sel 'pembantu'.

Pertama, kita akan mengira nilai min menggunakan fungsi "AVERAGE". Pilih sel di mana anda akan menyimpan hasil fungsi "AVERAGE".

Ketik formula berikut dan tekan masukkan -atau - gunakan menu "Formula".

= RINGKAS (E2: E13)

Untuk mengakses fungsi melalui menu "Formula", pilih drop-down "Lebih Banyak Fungsi", pilih pilihan "Statistik", kemudian klik pada "AVERAGE."

Dalam tetingkap Fungsi Argumen, pilih semua sel dalam lajur "Nilai" sebagai input untuk medan "Nombor1". Anda tidak perlu bimbang tentang bidang "Number2".

Sekarang tekan "OK."

Seterusnya, kita perlu mengira sisihan piawai nilai menggunakan fungsi "STDEV.S" atau "STDEV.P". Dalam contoh ini, kami akan menunjukkan kepada anda cara mengira kedua-dua nilai, bermula dengan "STDEV.S." Pilih sel di mana hasilnya akan disimpan.

Untuk mengira sisihan piawai menggunakan fungsi "STDEV.S", taip dalam formula ini dan tekan Enter (atau akses melalui menu "Formula").

= STDEV.S (E3: E12)

Untuk mengakses fungsi melalui menu "Formula", pilih drop-down "Lebih Fungsi", pilih pilihan "Statistik", tatal ke bawah sedikit, dan kemudian klik arahan "STDEV.S".

Dalam tetingkap Fungsi Argumen, pilih semua sel dalam lajur "Nilai" sebagai input untuk medan "Nombor1". Anda tidak perlu bimbang tentang bidang "Number2" di sini.

Sekarang tekan "OK."

Seterusnya, kami akan mengira sisihan piawai menggunakan fungsi "STDEV.P". Pilih sel di mana hasilnya akan disimpan.

Untuk mengira sisihan piawai menggunakan fungsi "STDEV.P", taip dalam formula ini dan tekan Enter (atau akses melalui menu "Formula").

= STDEV.P (E3: E12)

Untuk mengakses fungsi melalui menu "Formula", pilih drop-down "Lebih Fungsi", pilih pilihan "Statistik", tatal ke bawah sedikit, dan kemudian klik formula "STDEV.P".

Dalam tetingkap Fungsi Argumen, pilih semua sel dalam lajur "Nilai" sebagai input untuk medan "Nombor1". Sekali lagi, anda tidak perlu bimbang tentang bidang "Number2".

Sekarang tekan "OK."

Sekarang bahawa kami telah menghitung sisihan min dan standard data kami, kami mempunyai semua yang kita perlukan untuk mengira Z-Score. Kita boleh menggunakan formula mudah yang merujuk sel yang mengandungi hasil fungsi "AVERAGE" dan "STDEV.S" atau "STDEV.P".

Pilih sel pertama dalam lajur "Z-Markah". Kami akan menggunakan hasil fungsi "STDEV.S" untuk contoh ini, tetapi anda juga boleh menggunakan hasil daripada "STDEV.P."

Ketik formula berikut dan tekan Enter:

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

Sebagai alternatif, anda boleh menggunakan langkah-langkah berikut untuk memasukkan formula dan bukannya menaip:

Klik sel F3 dan ketik = (
Pilih sel E3. (Anda boleh tekan kekunci anak panah kiri sekali atau gunakan tetikus)
Taip tanda tolak -
Pilih sel G3 kemudian tekan F4 untuk menambah aksara "$" untuk membuat rujukan 'mutlak' pada sel (ia akan berputar melalui "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 "jika anda terus menekan F4)
Taipkan ) /
Pilih sel H3 (atau I3 jika anda menggunakan "STDEV.P") dan tekan F4 untuk menambah dua "$" aksara.
tekan enter

Z-Score telah dikira untuk nilai pertama. Ia adalah 0.15945 sisihan piawai di bawah min. Untuk menyemak hasilnya, anda boleh menggandakan sisihan piawai dengan hasil ini (6.271629 * -0.15945) dan pastikan hasilnya sama dengan perbezaan antara nilai dan min (499-500). Kedua-dua keputusan adalah sama, jadi nilai itu masuk akal.

Mari kita kirakan Z-Skor dari seluruh nilai-nilai. Serlahkan lajur 'Z-Score' yang bermula dengan sel yang mengandungi formula.

Tekan Ctrl + D, yang menyalin formula di sel teratas ke semua sel yang dipilih.

Sekarang formula telah 'diisi ke bawah' ke semua sel, dan setiap sel akan merujuk sel-sel yang betul "AVERAGE" dan "STDEV.S" atau "STDEV.P" kerana aksara "$". Jika anda mendapat ralat, kembali dan pastikan aksara "$" dimasukkan dalam formula yang anda masukkan.

Mengira Z-Markah tanpa menggunakan 'Helper' Sel

Sel pembantu menyimpan hasil, seperti yang menyimpan hasil fungsi "AVERAGE," "STDEV.S," dan "STDEV.P". Mereka boleh berguna tetapi tidak selalu diperlukan. Anda boleh melangkau mereka sama sekali apabila mengira Z-Markah dengan menggunakan formula umum yang berikut, sebaliknya.

Berikut ini satu fungsi "STDEV.S":

= (Value-AVERAGE (Nilai)) / STDEV.S (Nilai)

Dan yang menggunakan fungsi "STEV.P":

= (Value-AVERAGE (Nilai)) / STDEV.P (Nilai)

Apabila memasuki julat sel untuk "Nilai" dalam fungsi, pastikan anda menambah rujukan mutlak ("$" menggunakan F4) supaya apabila anda 'mengisi' anda tidak mengira sisihan purata atau piawai julat yang berbeza sel dalam setiap formula.

Sekiranya anda mempunyai set data yang besar, ia mungkin lebih berkesan menggunakan sel penolong kerana ia tidak menghitung hasil dari fungsi "AVERAGE" dan "STDEV.S" atau "STDEV.P" setiap kali, menyimpan sumber pemproses dan mempercepatkan masa yang diambil untuk mengira hasilnya.

Juga, "$ G $ 3" mengambil bait yang lebih sedikit untuk menyimpan dan kurang RAM untuk dimuat daripada "AVERAGE ($ E $ 3: $ E $ 12).". Ini penting kerana versi Excel standard 32-bit terhad kepada 2GB RAM (versi 64-bit tidak mempunyai apa-apa batasan mengenai berapa banyak RAM yang boleh digunakan).