Daftar isi
Regresi linear bisa dikerjakan di Excel tanpa perangkat lunak tambahan, cukup dengan add-in bawaan Analysis ToolPak. Hasilnya sama dengan SPSS untuk koefisien dan uji signifikansinya, sehingga cocok untuk belajar atau memeriksa ulang hasil. Konsep regresinya sendiri dibahas di regresi linear sederhana dan regresi linear berganda.
Data contoh
Nilai ujian, motivasi, dan jam belajar 12 siswa (rekaan)
| Siswa | Motivasi (X1) | Jam belajar per minggu (X2) | Nilai ujian (Y) |
|---|---|---|---|
| 1 | 60 | 5 | 70 |
| 2 | 72 | 8 | 76 |
| 3 | 55 | 4 | 66 |
| 4 | 80 | 10 | 85 |
| 5 | 68 | 7 | 70 |
| 6 | 75 | 6 | 81 |
| 7 | 62 | 6 | 66 |
| 8 | 85 | 12 | 90 |
| 9 | 70 | 9 | 78 |
| 10 | 58 | 3 | 67 |
| 11 | 77 | 8 | 77 |
| 12 | 66 | 7 | 77 |
Susun data dengan judul di baris 1, X1 dan X2 di kolom bersebelahan (misalnya B dan C), dan Y di kolom D.
Langkah di Excel
- Aktifkan Analysis ToolPak bila belum (lihat pertanyaan pertama di bawah).
- Pilih tab Data → Data Analysis → Regression → OK.
- Input Y Range: pilih D1:D13 (termasuk judul).
- Input X Range: pilih B1:C13. Semua variabel bebas harus berada di kolom yang bersebelahan.
- Centang Labels, karena baris pertama berisi judul.
- Biarkan Confidence Level 95%. Jangan centang Constant is Zero.
- Pilih New Worksheet Ply sebagai tempat hasil. Centang Residuals dan Normal Probability Plots bila ingin memeriksa sisaan.
- Klik OK.
Membaca outputnya
Regression Statistics
| Nilai | Arti | |
|---|---|---|
| Multiple R | 0,931 | korelasi antara Y dan nilai prediksinya |
| R Square | 0,866 | 86,6% variasi nilai ujian dijelaskan X1 dan X2 bersama-sama |
| Adjusted R Square | 0,837 | R² yang disesuaikan dengan banyak variabel bebas; laporkan untuk regresi berganda |
| Standard Error | 3,118 | galat baku estimasi, dalam satuan Y |
| Observations | 12 | banyak data |
Penjelasan R² dan Adjusted R² ada di koefisien determinasi.
ANOVA (uji F, simultan)
| df | SS | MS | F | Significance F | |
|---|---|---|---|---|---|
| Regression | 2 | 566,733 | 283,366 | 29,141 | 0,000117 |
| Residual | 9 | 87,517 | 9,724 | ||
| Total | 11 | 654,250 |
Significance F adalah nilai p uji F. Karena 0,000117 < 0,05, X1 dan X2 secara simultan berpengaruh signifikan terhadap Y. Di SPSS, angka yang sama ditulis Sig. ,000.
Tabel koefisien (uji t, parsial)
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
|---|---|---|---|---|---|---|
| Intercept | 26,979 | 10,713 | 2,518 | 0,033 | 2,744 | 51,213 |
| Motivasi | 0,647 | 0,225 | 2,872 | 0,018 | 0,137 | 1,157 |
| Jam belajar | 0,512 | 0,822 | 0,622 | 0,549 | −1,348 | 2,371 |
- Persamaannya: Ŷ = 26,979 + 0,647 X1 + 0,512 X2.
- Motivasi berpengaruh signifikan (P-value 0,018 < 0,05). Setiap kenaikan 1 poin motivasi, nilai ujian naik 0,647 poin bila jam belajar dianggap tetap.
- Jam belajar tidak berpengaruh signifikan secara parsial (0,549 > 0,05). Selang kepercayaannya (−1,348 sampai 2,371) memuat nol.
Untuk t tabel, gunakan df Residual (9): t tabel dua sisi α 5% = 2,262.
Semua angka di halaman ini rekaan untuk latihan dan sudah dihitung ulang dengan R.
Yang tidak disediakan Excel
| Kebutuhan skripsi | Di Excel | Cara melengkapinya |
|---|---|---|
| Koefisien terstandar (Beta) | tidak ada | Beta = B × (simpangan baku X / simpangan baku Y); untuk contoh ini 0,777 dan 0,168 |
| Multikolinearitas (VIF) | tidak ada | regresikan X1 terhadap X2, lalu VIF = 1 / (1 − R²); untuk contoh ini 4,928 |
| Normalitas residual | hanya plot | uji Shapiro-Wilk pada kolom residual; untuk contoh ini p = 0,043, sehingga normalitas residual perlu diperhatikan |
| Heteroskedastisitas, autokorelasi | tidak ada | lihat uji asumsi klasik |
Contoh ini memperlihatkan dua hal yang mudah terlewat bila hanya memakai Excel. Pertama, motivasi dan jam belajar berkorelasi tinggi (r = 0,893), yang ikut menjelaskan mengapa jam belajar tidak signifikan secara parsial. Kedua, residualnya belum tentu normal. Keduanya perlu dibahas di Bab 4.
Alternatif: fungsi LINEST
Tanpa ToolPak, koefisien bisa dihitung dengan fungsi larik:
=LINEST(D2:D13;B2:C13;TRUE;TRUE)
Hasilnya berupa tabel 5 baris tanpa label. Baris pertama berisi koefisien dalam urutan terbalik (X2, X1, lalu konstanta), baris kedua galat bakunya, baris ketiga R² dan Standard Error, baris keempat F dan df residual, baris kelima SS regresi dan SS residual. Karena urutan dan tanpa labelnya mudah membingungkan, ToolPak lebih disarankan untuk skripsi.
Kesalahan umum
- Kolom X tidak bersebelahan, sehingga salah satu variabel bebas tidak bisa dimasukkan. Pindahkan kolomnya dulu.
- Labels dicentang tetapi judul tidak ikut dipilih (atau sebaliknya), sehingga baris data pertama terbaca sebagai judul.
- Mencentang Constant is Zero, yang memaksa garis regresi melalui titik nol.
- Membaca Significance F sebagai F hitung.
- Melaporkan regresi tanpa uji asumsi klasik.
Pertanyaan umum
Menu Data Analysis tidak muncul. Bagaimana?
Aktifkan dulu add-in Analysis ToolPak. Di Windows: File → Options → Add-ins → Manage: Excel Add-ins → Go → centang Analysis ToolPak → OK. Di Mac: Tools → Excel Add-ins → centang Analysis ToolPak. Menu Data Analysis akan muncul di ujung kanan tab Data.
Kenapa muncul pesan 'Input range contains non-numeric data'?
Ada sel berisi teks, spasi, atau kosong di rentang yang dipilih, atau baris judul ikut terpilih tanpa mencentang Labels. Pastikan semua sel berisi angka dan centang Labels bila baris pertama berisi nama variabel.
Apakah hasil regresi Excel sama dengan SPSS?
Koefisien, R², F, t, dan nilai p-nya sama. Perbedaannya, Excel tidak menampilkan koefisien terstandar (Beta), Tolerance, VIF, Durbin-Watson, dan uji normalitas residual.
Berapa variabel bebas yang bisa dimasukkan?
Analysis ToolPak menerima hingga 16 variabel bebas, dan semua kolomnya harus bersebelahan.