Dalam analisis statistik, varians antara ahli kumpulan data menunjukkan sejauh mana titik data adalah dari garis trend, juga dikenali sebagai garis regresi. Semakin tinggi varians, semakin banyak penyebaran titik data. Kajian tentang analisis varians menunjukkan bahagian-bahagian dari varians dapat dijelaskan oleh ciri-ciri data, dan yang boleh dikaitkan dengan faktor rawak. Bahagian varians yang tidak dapat dijelaskan dipanggil varians sisa.
Menggunakan Excel Spreadsheets untuk Mengira Perbezaan Sisa
Formula untuk mengira varians residual melibatkan banyak pengiraan yang kompleks. Untuk set data kecil, proses pengiraan varians sisa dengan tangan boleh membosankan. Untuk set data yang besar, tugas itu boleh meletihkan. Dengan menggunakan hamparan Excel, anda hanya perlu memasukkan titik data dan pilih formula yang betul. Program ini mengendalikan pengiraan kompleks dan menyampaikan hasil dengan cepat.
Mata Data
Buka hamparan Excel baharu dan masukkan titik data ke dalam dua lajur. Garis regresi memerlukan setiap titik data mempunyai dua elemen. Ahli statistik biasanya melabel unsur-unsur ini "X" dan "Y." Sebagai contoh, Generic Insurance Co. ingin mencari perbezaan sisa ketinggian dan berat pekerja. Pembolehubah X mewakili ketinggian dan pembolehubah Y mewakili berat. Masukkan ketinggian ke dalam Lajur A dan lajur ke Lajur B.
Mencari Mean
The maksudnya mewakili purata bagi setiap elemen dalam set data. Dalam contoh ini, Generik Insurans ingin mencari purata, sisihan piawai dan kovarian daripada ketinggian dan berat 10 pekerja. Purata ketinggian yang disenaraikan dalam Lajur A boleh didapati dengan memasukkan fungsi "= AVERAGE (A1: A10)" ke dalam sel F1. Purata berat yang disenaraikan di Ruang B boleh didapati dengan memasukkan fungsi "= AVERAGE (B1: B10)" ke dalam sel F3.
Mencari Penyimpangan dan Kovarian Piawai
The sisihan piawai mengukur sejauh mana titik data tersebar dari min. The kovarians mengukur berapa banyak dua elemen titik data berubah bersama. Penyimpangan piawai ketinggian didapati dengan memasukkan fungsi "= STDEV (A1: A10)" ke dalam sel F2. Penyimpangan piawai berat ditemui dengan memasukkan fungsi "= STDEV (B1: B10)" ke dalam sel F4. Kovarians antara ketinggian dan berat ditemui dengan memasuki fungsi "= COVAR (A1: A10; B1: B10)" ke dalam sel F5.
Mencari Talian Regresi
The garis regresi mewakili fungsi linear yang mengikuti trend mata data. Formula untuk garis regresi kelihatan seperti ini: Y = aX + b.
Pengguna dapat mencari nilai untuk "a" dan "b" dengan menggunakan pengiraan untuk cara, sisihan piawai dan kovarians. Nilai untuk "b" mewakili titik di mana garis regresi memintas paksi Y. Nilai boleh didapati dengan mengambil kovarians dan membahagikannya dengan kuadrat sisihan piawai nilai-X. Formula Excel masuk ke dalam sel F6 dan kelihatan seperti ini: = F5 / F2 ^ 2.
Nilai untuk "a" mewakili cerun garis regresi. Formula Excel masuk ke dalam sel F7 dan kelihatan seperti ini: = F3-F6 * F1.
Untuk melihat formula untuk garis regresi, masukkan penyambungan rentetan ini ke dalam sel F8:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; JIKA (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7;
Kira Nilai Y
Langkah seterusnya melibatkan pengiraan nilai-Y pada baris regresi untuk nilai-X yang diberikan dalam set data. Formula untuk mencari nilai Y masuk ke ruangan C dan kelihatan seperti ini:
= $ F $ 6 * A (i) + $ F $ 7
Di mana A (i) adalah nilai untuk Lajur A dalam Baris (i). Formula kelihatan seperti ini dalam spreadsheet:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, dan sebagainya
Penyertaan di Lajur D menunjukkan perbezaan antara nilai yang dijangkakan dan sebenar untuk Y. Rumus seperti ini:
= B (i) -C (i), Di mana B (i) dan C (i) masing-masing adalah nilai dalam Row (i) dalam Lajur B dan C.
Mencari Varians Residual
The formula untuk varians sisa masuk ke Cell F9 dan kelihatan seperti ini:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Di mana SUMSQ (D1: D10) adalah jumlah kuadrat perbezaan antara nilai-nilai sebenar dan yang dijangkakan Y, dan (COUNT (D1: D10) -2) ialah bilangan titik data, tolak 2 untuk darjah kebebasan dalam data.
