Cara Menghitung Z-Score Menggunakan Microsoft Excel

Z-Score adalah nilai statistik yang memberi tahu Anda berapa banyak deviasi standar yang kebetulan menjadi nilai tertentu dari rata-rata seluruh kumpulan data. Anda dapat menggunakan rumus AVERAGE dan STDEV.S atau STDEV.P untuk menghitung mean dan deviasi standar data Anda dan kemudian menggunakan hasil tersebut untuk menentukan Z-Score dari setiap nilai.

Apa itu Z-Score dan apa fungsi AVERAGE, STDEV.S, dan STDEV.P?

Z-Score adalah cara sederhana untuk membandingkan nilai dari dua kumpulan data yang berbeda. Ini didefinisikan sebagai jumlah deviasi standar dari rata-rata titik data berada. Rumus umumnya terlihat seperti ini:

= (DataPoint-AVERAGE (Kumpulan Data)) / STDEV (Kumpulan Data)

Berikut contoh untuk membantu memperjelas. Katakanlah Anda ingin membandingkan hasil tes dari dua siswa Aljabar yang diajar oleh guru yang berbeda. Anda tahu bahwa siswa pertama mendapat 95% pada ujian akhir di satu kelas, dan siswa di kelas lain mendapat nilai 87%.

Sekilas, nilai 95% lebih mengesankan, tetapi bagaimana jika guru kelas dua memberikan ujian yang lebih sulit? Anda dapat menghitung Z-Score dari setiap nilai siswa berdasarkan nilai rata-rata di setiap kelas dan standar deviasi dari nilai di setiap kelas. Membandingkan Nilai-Z dari kedua siswa dapat mengungkapkan bahwa siswa dengan nilai 87% lebih baik dibandingkan dengan siswa lainnya di kelas daripada siswa dengan nilai 98% dibandingkan dengan siswa lainnya di kelas.

Nilai statistik pertama yang Anda butuhkan adalah 'mean' dan fungsi "AVERAGE" Excel menghitung nilai itu. Ini hanya menambahkan semua nilai dalam rentang sel dan membagi jumlah itu dengan jumlah sel yang berisi nilai numerik (mengabaikan sel kosong).

Nilai statistik lain yang kita butuhkan adalah 'standar deviasi' dan Excel memiliki dua fungsi berbeda untuk menghitung simpangan baku dengan cara yang sedikit berbeda.

Versi Excel sebelumnya hanya memiliki fungsi "STDEV", yang menghitung simpangan baku sambil memperlakukan data sebagai 'sampel' populasi. Excel 2010 memecahnya menjadi dua fungsi yang menghitung deviasi standar:

  • STDEV.S: Fungsi ini identik dengan fungsi "STDEV" sebelumnya. Ini menghitung deviasi standar sambil memperlakukan data sebagai 'sampel' dari suatu populasi. Sampel populasi mungkin seperti nyamuk tertentu yang dikumpulkan untuk proyek penelitian atau mobil yang disisihkan dan digunakan untuk pengujian keselamatan kecelakaan.
  • STDEV.P: Fungsi ini menghitung simpangan baku sambil memperlakukan data sebagai keseluruhan populasi. Seluruh populasi akan menjadi sesuatu seperti semua nyamuk di Bumi atau setiap mobil dalam proses produksi model tertentu.

Yang Anda pilih didasarkan pada kumpulan data Anda. Perbedaannya biasanya kecil, tetapi hasil dari fungsi "STDEV.P" akan selalu lebih kecil daripada hasil fungsi "STDEV.S" untuk kumpulan data yang sama. Ini adalah pendekatan yang lebih konservatif untuk mengasumsikan ada lebih banyak variabilitas dalam data.

Mari Kita Lihat Contoh

Untuk contoh kita, kita memiliki dua kolom ("Nilai" dan "Z-Score") dan tiga sel "pembantu" untuk menyimpan hasil dari fungsi "AVERAGE", "STDEV.S," dan "STDEV.P". Kolom "Nilai" berisi sepuluh nomor acak yang berpusat di sekitar 500, dan kolom "Skor-Z" adalah tempat kita akan menghitung Skor-Z menggunakan hasil yang disimpan di sel 'penolong'.

Pertama, kita akan menghitung mean dari nilai menggunakan fungsi "AVERAGE". Pilih sel tempat Anda akan menyimpan hasil dari fungsi "AVERAGE".

Ketik rumus berikut dan tekan enter -atau- gunakan menu "Rumus".

= RATA-RATA (E2: E13)

Untuk mengakses fungsi melalui menu "Rumus", pilih menu tarik-turun "Fungsi Lainnya", pilih opsi "Statistik", lalu klik "RATA-RATA".

Di jendela Function Arguments, pilih semua sel di kolom "Values" sebagai input untuk bidang "Number1". Anda tidak perlu khawatir tentang bidang "Number2".

Sekarang tekan "OK."

Selanjutnya, kita perlu menghitung deviasi standar dari nilai-nilai tersebut menggunakan fungsi "STDEV.S" atau "STDEV.P". Dalam contoh ini, kami akan menunjukkan kepada Anda bagaimana menghitung kedua nilai, dimulai dengan "STDEV.S." Pilih sel tempat hasil akan disimpan.

Untuk menghitung deviasi standar menggunakan fungsi "STDEV.S", ketik rumus ini dan tekan Enter (atau akses melalui menu "Rumus").

= STDEV.S (E3: E12)

Untuk mengakses fungsi melalui menu "Rumus", pilih drop-down "Fungsi Lainnya", pilih opsi "Statistik", gulir ke bawah sedikit, lalu klik perintah "STDEV.S".

Di jendela Function Arguments, pilih semua sel di kolom "Values" sebagai input untuk bidang "Number1". Anda juga tidak perlu mengkhawatirkan bidang "Nomor2" di sini.

Sekarang tekan "OK."

Selanjutnya, kami akan menghitung deviasi standar menggunakan fungsi "STDEV.P". Pilih sel tempat hasil akan disimpan.

Untuk menghitung deviasi standar menggunakan fungsi "STDEV.P", ketik rumus ini dan tekan Enter (atau akses melalui menu "Rumus").

= STDEV.P (E3: E12)

Untuk mengakses fungsi melalui menu "Rumus", pilih drop-down "Fungsi Lainnya", pilih opsi "Statistik", gulir ke bawah sedikit, lalu klik rumus "STDEV.P".

Di jendela Function Arguments, pilih semua sel di kolom "Values" sebagai input untuk bidang "Number1". Sekali lagi, Anda tidak perlu khawatir tentang bidang "Number2".

Sekarang tekan "OK."

Sekarang setelah kita menghitung mean dan deviasi standar dari data kita, kita memiliki semua yang kita butuhkan untuk menghitung Z-Score. Kita bisa menggunakan rumus sederhana yang mereferensikan sel yang berisi hasil dari fungsi "AVERAGE" dan "STDEV.S" atau "STDEV.P".

Pilih sel pertama di kolom "Z-Score". Kita akan menggunakan hasil dari fungsi "STDEV.S" untuk contoh ini, tetapi Anda juga bisa menggunakan hasil dari "STDEV.P."

Ketik rumus berikut dan tekan Enter:

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

Alternatifnya, Anda dapat menggunakan langkah-langkah berikut untuk memasukkan rumus daripada mengetik:

  1. Klik sel F3 dan ketik =(
  2. Pilih sel E3. (Anda dapat menekan tombol panah kiri sekali atau menggunakan mouse)
  3. Ketik tanda minus -
  4. Pilih sel G3 lalu tekan F4 untuk menambahkan karakter "$" untuk membuat referensi 'absolut' ke sel (ini akan menggilir "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> “G3” jika Anda terus menekan F4 )
  5. Tipe )/
  6. Pilih sel H3 (atau I3 jika Anda menggunakan "STDEV.P") dan tekan F4 untuk menambahkan dua karakter "$".
  7. tekan enter

Z-Score telah dihitung untuk nilai pertama. Ini adalah deviasi standar 0,15945 di bawah mean. Untuk memeriksa hasil, Anda dapat mengalikan deviasi standar dengan hasil ini (6.271629 * -0.15945) dan memeriksa apakah hasilnya sama dengan selisih antara nilai dan mean (499-500). Kedua hasil itu sama, jadi nilainya masuk akal.

Mari kita hitung Z-Scores dari nilai lainnya. Sorot seluruh kolom 'Z-Score' dimulai dengan sel yang berisi rumus.

Tekan Ctrl + D, yang menyalin rumus di sel atas ke bawah melalui semua sel lain yang dipilih.

Sekarang rumus telah 'diisi' ke semua sel, dan masing-masing akan selalu mengacu pada sel "AVERAGE" dan "STDEV.S" atau "STDEV.P" yang benar karena karakter "$". Jika Anda mendapatkan kesalahan, kembali dan pastikan karakter "$" disertakan dalam rumus yang Anda masukkan.

Menghitung Z-Score tanpa menggunakan Sel 'Helper'

Sel pembantu menyimpan hasil, seperti sel yang menyimpan hasil dari fungsi "AVERAGE", "STDEV.S," dan "STDEV.P". Mereka bisa berguna tetapi tidak selalu diperlukan. Anda dapat melewatinya sama sekali saat menghitung Skor-Z dengan menggunakan rumus umum berikut ini.

Ini salah satu yang menggunakan fungsi "STDEV.S":

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

Dan yang menggunakan fungsi "STEV.P":

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

Saat memasukkan rentang sel untuk "Nilai" dalam fungsi, pastikan untuk menambahkan referensi absolut ("$" menggunakan F4) sehingga saat Anda 'mengisi' Anda tidak menghitung rata-rata atau deviasi standar dari rentang yang berbeda jumlah sel di setiap formula.

Jika Anda memiliki kumpulan data yang besar, mungkin lebih efisien untuk menggunakan sel pembantu karena tidak menghitung hasil dari fungsi "AVERAGE" dan "STDEV.S" atau "STDEV.P" setiap saat, menghemat sumber daya prosesor dan mempercepat waktu yang diperlukan untuk menghitung hasil.

Selain itu, "$ G $ 3" ​​membutuhkan lebih sedikit byte untuk disimpan dan lebih sedikit RAM untuk dimuat daripada "AVERAGE ($ E $ 3: $ E $ 12).". Ini penting karena Excel versi 32-bit standar dibatasi hingga 2GB RAM (versi 64-bit tidak memiliki batasan berapa banyak RAM yang dapat digunakan).