Dalam analisis statistik, perbedaan di antara anggota set data menunjukkan seberapa jauh jarak titik data dari garis tren, juga dikenal sebagai a Garis regresi. Semakin tinggi varians, semakin tersebar titik data. Studi tentang analisis varian menunjukkan bagian mana dari varian yang dapat dijelaskan oleh karakteristik data, dan yang dapat dikaitkan dengan faktor acak. Bagian dari varian yang tidak dapat dijelaskan disebut varian residual.
Menggunakan Excel Spreadsheets untuk Menghitung Varians Sisa
Rumus untuk menghitung varians residual melibatkan banyak perhitungan kompleks. Untuk set data kecil, proses penghitungan varians residual dengan tangan bisa jadi membosankan. Untuk kumpulan data besar, tugas itu bisa melelahkan. Dengan menggunakan lembar bentang Excel, Anda hanya perlu memasukkan titik data dan memilih rumus yang benar. Program ini menangani perhitungan yang rumit dan memberikan hasil dengan cepat.
Titik data
Buka spreadsheet Excel baru dan masukkan titik data ke dalam dua kolom. Garis regresi mengharuskan setiap titik data memiliki dua elemen. Ahli statistik biasanya memberi label elemen-elemen ini "X" dan "Y." Sebagai contoh, Generic Insurance Co. ingin menemukan varian sisa dari tinggi dan berat karyawannya. Variabel X mewakili tinggi dan variabel Y mewakili berat. Masukkan ketinggian ke dalam Kolom A dan bobot ke Kolom B.
Menemukan Mean
Itu berarti mewakili rata-rata untuk setiap elemen dalam kumpulan data. Dalam contoh ini, Generic Insurance ingin menemukan rata-rata, standar deviasi, dan kovarian dari 10 tinggi dan berat karyawan. Rata-rata ketinggian yang tercantum dalam Kolom A dapat ditemukan dengan memasukkan fungsi "= AVERAGE (A1: A10)" ke dalam sel F1. Rata-rata dari bobot yang tercantum dalam Kolom B dapat ditemukan dengan memasukkan fungsi "= AVERAGE (B1: B10)" ke dalam sel F3.
Menemukan Standar Deviasi dan Kovarian
Itu standar deviasi mengukur seberapa jauh titik data tersebar dari rata-rata. Itu kovarians mengukur seberapa banyak kedua elemen titik data berubah bersama. Deviasi standar ketinggian ditemukan dengan memasukkan fungsi "= STDEV (A1: A10)" ke dalam sel F2. Deviasi standar dari bobot ditemukan dengan memasukkan fungsi "= STDEV (B1: B10)" ke dalam sel F4. Kovarians antara tinggi dan berat ditemukan dengan memasukkan fungsi "= COVAR (A1: A10; B1: B10)" ke dalam sel F5.
Menemukan Garis Regresi
Itu Garis regresi mewakili fungsi linier yang mengikuti tren titik data. Rumus untuk garis regresi terlihat seperti ini: Y = aX + b.
Pengguna dapat menemukan nilai untuk "a" dan "b" dengan menggunakan perhitungan untuk rata-rata, standar deviasi, dan kovarian. Nilai untuk "b" mewakili titik di mana garis regresi memotong sumbu Y. Nilai dapat ditemukan dengan mengambil kovarians dan membaginya dengan kuadrat dari standar deviasi nilai-X. Rumus Excel masuk ke sel F6 dan terlihat seperti ini: = F5 / F2 ^ 2.
Nilai untuk "a" mewakili kemiringan garis regresi. Rumus Excel masuk ke sel F7 dan terlihat seperti ini: = F3-F6 * F1.
Untuk melihat rumus untuk garis regresi, masukkan rangkaian string ini ke dalam sel F8:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2))))
Hitung Nilai Y
Langkah selanjutnya melibatkan menghitung nilai-Y pada garis regresi untuk nilai-X yang diberikan dalam kumpulan data. Rumus untuk menemukan nilai Y masuk ke kolom C dan terlihat seperti ini:
= $ F $ 6 * A (i) + $ F $ 7
Di mana A (i) adalah nilai untuk Kolom A di Baris (i). Rumusnya terlihat seperti ini di spreadsheet:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, dan seterusnya
Entri dalam Kolom D menunjukkan perbedaan antara nilai yang diharapkan dan aktual untuk Y. Rumusnya terlihat seperti ini:
= B (i) -C (i), Di mana B (i) dan C (i) adalah nilai dalam Baris (i) di Kolom B dan C, masing-masing.
Menemukan Varians Sisa
Itu rumus untuk varian residual masuk ke Sel F9 dan terlihat seperti ini:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Di mana SUMSQ (D1: D10) adalah jumlah kuadrat dari perbedaan antara nilai Y aktual dan yang diharapkan, dan (COUNT (D1: D10) -2) adalah jumlah titik data, minus 2 untuk derajat kebebasan dalam data.
