Cara Mengira Skor Z Menggunakan Microsoft Excel
Z-Score adalah nilai statistik yang memberitahu anda berapa banyak sisihan piawai suatu nilai tertentu dari rata-rata keseluruhan set data. Anda boleh menggunakan formula AVERAGE dan STDEV.S atau STDEV.P untuk mengira min dan sisihan piawai data anda dan kemudian menggunakan hasil tersebut untuk menentukan Z-Skor setiap nilai.
Apa itu Z-Skor dan apa fungsi AVERAGE, STDEV.S, dan STDEV.P?
Z-Score adalah kaedah mudah untuk membandingkan nilai dari dua set data yang berbeza. Ia ditakrifkan sebagai bilangan sisihan piawai dari titik titik data. Formula umum kelihatan seperti ini:
= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)
Inilah 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% pada peperiksaan akhir di satu kelas, dan pelajar di kelas lain mendapat markah 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 di setiap kelas dan sisihan piawai skor di setiap kelas. Membandingkan skor-Z kedua pelajar dapat menunjukkan bahawa pelajar dengan skor 87% menunjukkan prestasi yang lebih baik berbanding dengan kelas mereka yang lain berbanding pelajar dengan skor 98% berbanding dengan kelas mereka yang lain.
Nilai statistik pertama yang anda perlukan adalah fungsi 'min' dan Excel "AVERAGE" mengira nilai tersebut. Ia hanya menambahkan semua nilai dalam julat sel dan membahagi jumlahnya dengan jumlah 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 dengan cara yang sedikit berbeza.
Versi Excel sebelumnya hanya mempunyai fungsi "STDEV", yang mengira sisihan piawai sambil memperlakukan data sebagai 'sampel' populasi. Excel 2010 membahagikannya kepada dua fungsi yang mengira sisihan piawai:
- STDEV.S: Fungsi ini sama dengan fungsi "STDEV" sebelumnya. Ia mengira sisihan piawai sambil memperlakukan data sebagai 'sampel' populasi. Sampel populasi mungkin seperti nyamuk tertentu yang dikumpulkan untuk projek penyelidikan atau kereta yang disisihkan dan digunakan untuk ujian keselamatan kemalangan.
- STDEV.P: Fungsi ini mengira sisihan piawai sambil memperlakukan data sebagai keseluruhan populasi. Seluruh penduduk akan menjadi seperti semua nyamuk di Bumi atau setiap kereta dalam proses produksi model tertentu.
Yang anda pilih adalah berdasarkan set data anda. Perbezaannya biasanya kecil, tetapi hasil dari fungsi "STDEV.P" akan selalu lebih kecil daripada hasil fungsi "STDEV.S" untuk set data yang sama. Ini adalah pendekatan yang lebih konservatif untuk menganggap terdapat lebih banyak perubahan dalam data.
Mari Lihat Contoh
Sebagai contoh, kami mempunyai dua lajur ("Nilai" dan "Z-Skor") dan tiga sel "penolong" untuk menyimpan hasil fungsi "RATA-RATA", "STDEV.S," dan "STDEV.P". Lajur "Nilai" mengandungi sepuluh nombor rawak yang berpusat di sekitar 500, dan lajur "Z-Skor" adalah tempat kita akan mengira Skor-Z menggunakan hasil yang disimpan di sel 'penolong'.
Pertama, kita akan mengira nilai rata-rata menggunakan fungsi "RATA-RATA". Pilih sel di mana anda akan menyimpan hasil fungsi "RATA-RATA".
Ketik formula berikut dan tekan enter -atau- gunakan menu "Formula".
= RATA-RATA (E2: E13)
Untuk mengakses fungsi melalui menu "Rumus", pilih menu tarik-turun "Lebih Banyak Fungsi", pilih opsi "Statistik", dan kemudian klik "RATA-RATA"
Di tetingkap Fungsi Argumen, pilih semua sel di lajur "Nilai" sebagai input untuk bidang "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 tersebut, bermula dengan "STDEV.S." Pilih sel tempat hasilnya akan disimpan.
Untuk mengira sisihan piawai menggunakan fungsi "STDEV.S", ketik formula ini dan tekan Enter (atau aksesnya melalui menu "Formula").
= STDEV.S (E3: E12)
Untuk mengakses fungsi melalui menu "Rumus", pilih drop-down "Fungsi Lebih Banyak", pilih pilihan "Statistik", tatal ke bawah sedikit, dan kemudian klik perintah "STDEV.S".
Di tetingkap Fungsi Argumen, pilih semua sel di lajur "Nilai" sebagai input untuk bidang "Nombor1". Anda juga tidak perlu risau tentang bidang "Number2" di sini.
Sekarang tekan "OK."
Seterusnya, kita akan mengira sisihan piawai menggunakan fungsi "STDEV.P". Pilih sel tempat hasilnya akan disimpan.
Untuk mengira sisihan piawai menggunakan fungsi "STDEV.P", ketik formula ini dan tekan Enter (atau aksesnya melalui menu "Rumus").
= STDEV.P (E3: E12)
Untuk mengakses fungsi melalui menu "Rumus", pilih drop-down "Fungsi Lebih Banyak", pilih pilihan "Statistik", tatal ke bawah sedikit, dan kemudian klik formula "STDEV.P".
Di tetingkap Fungsi Argumen, pilih semua sel di lajur "Nilai" sebagai input untuk bidang "Nombor1". Sekali lagi, anda tidak perlu bimbang tentang bidang "Number2".
Sekarang tekan "OK."
Setelah kita mengira min dan sisihan piawai data kita, kita mempunyai semua yang kita perlukan untuk mengira Z-Skor. Kita dapat menggunakan formula sederhana yang merujuk pada sel-sel yang berisi hasil fungsi "AVERAGE" dan "STDEV.S" atau "STDEV.P".
Pilih sel pertama di lajur "Z-Score". Kami akan menggunakan hasil fungsi "STDEV.S" untuk contoh ini, tetapi anda juga dapat menggunakan hasilnya dari "STDEV.P."
Taipkan 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 taip
=( - Pilih sel E3. (Anda boleh menekan kekunci anak panah kiri sekali atau menggunakan tetikus)
- Taipkan tanda tolak
- - Pilih sel G3 kemudian tekan F4 untuk menambahkan aksara "$" untuk membuat rujukan 'mutlak' ke sel (ia akan berpusing melalui "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> "G3" jika anda terus menekan F4 )
- Jenis
)/ - Pilih sel H3 (atau I3 jika Anda menggunakan "STDEV.P") dan tekan F4 untuk menambahkan dua watak "$".
- tekan enter
Z-Skor telah dikira untuk nilai pertama. Ini adalah 0.15945 sisihan piawai di bawah min. Untuk memeriksa hasilnya, anda boleh menggandakan sisihan piawai dengan hasil ini (6.271629 * -0.15945) dan periksa bahawa hasilnya sama dengan perbezaan antara nilai dan min (499-500). Kedua-dua hasilnya sama, jadi nilainya masuk akal.
Mari kirakan Skor-Z nilai selebihnya. Sorot keseluruhan lajur 'Z-Score' bermula dengan sel yang mengandungi formula.
Tekan Ctrl + D, yang menyalin formula di sel atas ke bawah melalui semua sel terpilih yang lain.
Sekarang formula telah 'diisi' ke semua sel, dan masing-masing akan selalu merujuk sel "AVERAGE" dan "STDEV.S" atau "STDEV.P" yang betul kerana watak "$". Sekiranya anda mendapat kesilapan, kembali dan pastikan aksara "$" disertakan dalam formula yang anda masukkan.
Mengira Skor-Z tanpa menggunakan Sel 'Helper'
Sel pembantu menyimpan hasilnya, seperti sel yang menyimpan hasil fungsi "AVERAGE", "STDEV.S," dan "STDEV.P". Mereka boleh berguna tetapi tidak selalu diperlukan. Anda boleh melupakannya sama sekali ketika mengira Skor-Z dengan menggunakan formula umum berikut.
Inilah yang menggunakan fungsi "STDEV.S":
= (Nilai-RATA-RATA (Nilai)) / STDEV.S (Nilai)
Dan satu menggunakan fungsi "STEV.P":
= (Nilai-RATA-RATA (Nilai)) / STDEV.P (Nilai)
Ketika memasukkan rentang sel untuk "Nilai" dalam fungsi, pastikan untuk menambahkan rujukan mutlak ("$" menggunakan F4) sehingga ketika anda 'mengisi' Anda tidak menghitung rata-rata atau sisihan piawai dari julat yang berbeda sel dalam setiap formula.
Sekiranya anda mempunyai set data yang besar, mungkin lebih efisien menggunakan sel pembantu kerana tidak menghitung hasil fungsi "RATA-RATA" dan "STDEV.S" atau "STDEV.P" setiap kali, menjimatkan sumber prosesor dan mempercepat masa yang diperlukan untuk mengira hasilnya.
Juga, "$ G $ 3" memerlukan lebih sedikit bait untuk menyimpan dan kurang RAM untuk dimuat daripada "RATA-RATA ($ E $ 3: $ E $ 12).". Ini penting kerana versi standard 32-bit Excel terhad kepada 2GB RAM (versi 64-bit tidak mempunyai batasan berapa banyak RAM dapat digunakan).
