Lompat ke konten Lompat ke sidebar Lompat ke footer
Beranda / Tutorial / Tutorial Excel

Cara Tutorial Menghitung Regresi Linier Berganda di Microsoft Excel

Oleh Iman Jihad Fadillah Posting Komentar

Regresi linier berganda adalah metode statistik yang digunakan untuk mengukur hubungan antara dua atau lebih variabel independen dengan satu variabel dependen. Dalam regresi linier berganda, kita mencari persamaan yang dapat menggambarkan hubungan antara variabel independen dan variabel dependen. Microsoft Excel adalah salah satu program pengolah data yang sering digunakan untuk melakukan analisis regresi linier berganda.

Outline Artikel

Pada artikel ini, kita akan membahas langkah-langkah untuk melakukan analisis regresi linier berganda di Microsoft Excel 2013, 2016, dan 2019.

Langkah 1: Siapkan Data

Langkah pertama dalam analisis regresi linier berganda adalah menyiapkan data. Data yang akan digunakan harus memiliki minimal dua variabel independen dan satu variabel dependen. Dalam contoh ini, kita akan menggunakan data yang berisi informasi tentang penjualan mobil berdasarkan faktor-faktor seperti harga, ukuran mesin, dan tahun produksi.

Langkah 2: Membuat Regresi Linier Berganda

Berikut adalah tutorial untuk menghitung regresi linier berganda menggunakan fungsi Linest di Microsoft Excel, berdasarkan data Anda:

  • Masukkan data Anda ke dalam lembar kerja Excel, dengan variabel dependen Anda (Y) di kolom C, dan variabel independen Anda (X1 dan X2) di kolom A dan B. Pastikan data Anda benar-benar terorganisir dengan benar.
  • Tentukan rentang sel yang akan menampung hasil perhitungan regresi. Misalnya, Anda dapat menggunakan sel E2:G5.
  • Di sel E2, masukkan rumus berikut: =LINEST(C2:C31, A2:B31, TRUE, TRUE)

Penjelasan rumus:

  • C2:C31 adalah rentang sel yang berisi data variabel dependen Anda.
  • A2:B31 adalah rentang sel yang berisi data variabel independen Anda.
  • TRUE menunjukkan bahwa Anda ingin menghitung intersep, atau konstanta, dalam model regresi. Jika Anda tidak ingin menghitung intersep, ubah nilai menjadi FALSE.
  • TRUE menunjukkan bahwa Anda ingin mengembalikan output dalam bentuk array, sehingga fungsi LINEST menghasilkan beberapa nilai sekaligus.
  • Tekan tombol Ctrl + Shift + Enter untuk memasukkan rumus sebagai rumus matriks. Anda akan melihat bahwa sel E2:G5 sekarang berisi beberapa angka.

Selanjutnya, Anda harus memformat rentang sel E2:G5 agar sesuai dengan output regresi.

  • Di sel E7, masukkan teks "Koefisien". Di sel F7, masukkan teks "Estimasi".
  • Di sel E8, masukkan teks "Intersep". Di sel F8, masukkan rumus berikut: =INDEX($E$2:$G$5, 1, 1)
  • Di sel E9, masukkan teks "X1". Di sel F9, masukkan rumus berikut: =INDEX($E$2:$G$5, 1, 2)
  • Di sel E10, masukkan teks "X2". Di sel F10, masukkan rumus berikut: =INDEX($E$2:$G$5, 1, 3)
  • Di sel G7-10 masukkan ulang rumus diatas dengan mengganti kode 1 di tengah index seperti berikut: =INDEX($E$2:$G$5, 2, X), nilai ini akan menjadi Std.Eror untuk masing-masing nilai
  • Kemudian di sel H7-10 silahkan bagi kolom F dengan G sehingga mendapatkan nilai t hitung

Setelah Anda menyelesaikan langkah-langkah di atas, tabel Anda akan menampilkan koefisien regresi, termasuk intersep, koefisien X1, dan koefisien X2.

Selain itu, Anda juga dapat menghitung Residual standard error, Multiple R-squared, F-statistic, dan Dof dengan rumus berikut:

  • Di sel E12, masukkan teks "Residual standard error". Di sel F12, masukkan rumus berikut: =INDEX($E$2:$G$5;3;2)
  • Di sel E13, masukkan teks "Multiple R-squared". Di sel F13, masukkan rumus berikut: =INDEX($E$2:$G$5;3;1)
  • Di sel E14, masukkan teks "F-statistic". Di sel F14, masukkan rumus berikut: =INDEX($E$2:$G$5;4;1)
  • Di sel E15, masukkan teks " Dof". Di sel F15, masukkan rumus berikut: =INDEX($E$2:$G$5;4;2)

Langkah 3: Interpretasi Hasil

Setelah Anda melakukan perhitungan regresi linier berganda di Excel, maka akan muncul hasil perhitungan yang terdiri dari beberapa nilai seperti koefisien estimasi, standar error, t-test, residual standard error, multiple R-squared, F-statistic, dan degrees of freedom (dof). Berikut adalah penjelasan dari setiap nilai tersebut:

Koefisien Estimasi

Koefisien estimasi menunjukkan seberapa besar pengaruh setiap variabel independen terhadap variabel dependen. Koefisien ini dinyatakan dalam bentuk angka, dan semakin besar nilainya, semakin besar pula pengaruh variabel independen terhadap variabel dependen. Sebaliknya, semakin kecil nilainya, semakin kecil pula pengaruh variabel independen terhadap variabel dependen.

Std. Error

Std. Error atau standar error menunjukkan seberapa akurat koefisien estimasi yang dihitung. Semakin kecil nilai standar error, semakin akurat pula koefisien estimasi yang dihasilkan.

t-test

t-test menunjukkan apakah koefisien estimasi yang dihasilkan signifikan atau tidak. Jika nilai t-test lebih besar dari nilai kritis tertentu, maka koefisien estimasi dianggap signifikan secara statistik.

Residual Standard Error

Residual standard error atau sering disebut sebagai standar error residual menunjukkan seberapa akurat model regresi dalam memprediksi nilai variabel dependen. Semakin kecil nilai residual standard error, semakin akurat pula model regresi dalam memprediksi nilai variabel dependen.

Multiple R-squared

Multiple R-squared atau R-squared berganda menunjukkan seberapa besar proporsi variasi dalam variabel dependen yang dapat dijelaskan oleh semua variabel independen yang digunakan dalam model regresi. Nilai R-squared berkisar antara 0 dan 1, dan semakin tinggi nilainya, semakin baik pula model regresi dalam menjelaskan variasi dalam variabel dependen.

F-Statistic

F-statistic menunjukkan apakah model regresi secara keseluruhan signifikan atau tidak. Nilai F-statistic yang tinggi menunjukkan bahwa model regresi signifikan secara statistik.

Degrees of Freedom (dof)

Degrees of Freedom atau derajat kebebasan adalah jumlah data yang dapat bervariasi bebas dalam suatu perhitungan statistik. Pada perhitungan regresi linier berganda, nilai dof tergantung pada jumlah variabel independen yang digunakan.

Dalam interpretasi hasil perhitungan regresi linier berganda di Excel, Anda harus memperhatikan nilai-nilai di atas untuk memastikan bahwa model regresi yang dibuat signifikan secara statistik dan mampu menjelaskan variasi dalam variabel dependen dengan baik. Dengan memahami nilai-nilai tersebut, Anda dapat membuat kesimpulan yang lebih akurat dan mendalam tentang data yang sedang Anda analisis.

Contoh Soal

Contoh kasus yang digunakan adalah untuk menganalisis hubungan antara curah hujan dengan penjualan payung. Curah hujan dijadikan variabel bebas (X1), Durasi Hujan dijadikan variabel bebas (X2) dan penjualan payung dijadikan variabel terikat (Y). Tujuan analisis ini adalah untuk mengetahui seberapa besar pengaruh curah hujan dan durasi hujan terhadap penjualan payung.

Curah Hujan

Durasi Hujan

Penjualan Payung

3.2

2.5

20

4.5

3.1

25

2.1

1.8

15

5.7

4.2

30

6.3

5.1

35

2.8

2

18

4.1

3.4

24

5.5

4.8

32

1.9

1.5

12

3.8

2.9

22

4.6

3.8

26

2.5

2.1

16

6.1

5

36

3.6

2.7

21

5.2

4.5

28

2.3

1.9

14

3.9

3.2

23

4.8

3.9

27

2.9

2.2

19

6.5

5.5

38

5.4

4.6

31

4.4

3.6

25

3.5

2.8

20

2.2

1.7

14

5.8

4.9

33

3.3

2.6

19

4.9

4

28

6.2

5.2

36

2.7

2

17

4.3

3.5

24

Tahapan kurang lebih sama dengan contoh penjelasan pada poin Langkah 2: Membuat Regresi Linier Berganda, karena memang dibuatkan berdasarkan contoh kasus pada data ini. Adapun langkah awal yang dilakukan adalah memasukkan rumus LINEST sesuai penjelasan sebelumnya, dan contoh gambar dibawah ini:

Langkah berikutnya adalah membuat rumus pada penjelasan pada langkah 2, sehingga diperoleh secara lengkap seperti berikut:

Interpretasi Output

Model regresi linier berganda yang diberikan adalah sebagai berikut:

Penjualan_payung = 2.9871 + 3.5953 * Curah_hujan + 1.8710 * Durasi_hujan

Berikut adalah interpretasi dari setiap parameter model:

Intersep (2.9871): Ini adalah nilai tebakan ketika semua variabel prediktor (Curah_hujan dan Durasi_hujan) sama dengan nol. Dalam hal ini, ini menunjukkan jumlah penjualan payung yang diharapkan ketika tidak ada hujan sama sekali.

Koefisien untuk Curah_hujan (3.5953): Ini menunjukkan seberapa banyak penjualan payung yang diharapkan meningkat dengan peningkatan satu unit curah hujan, semua variabel lainnya konstan. alam hal ini, jika curah hujan meningkat satu satuan, maka penjualan payung akan meningkat sebesar 3.5953.

Koefisien untuk Durasi_hujan (1.8710): Ini menunjukkan seberapa banyak penjualan payung yang diharapkan meningkat dengan peningkatan satu unit durasi hujan, semua variabel lainnya konstan. Dalam hal ini, jika durasi hujan meningkat satu satuan, maka penjualan payung akan meningkat sebesar 1.8710.

Std. Error: Ini adalah estimasi standar deviasi kesalahan (galat) dari setiap koefisien estimasi. Dalam hal ini, semakin rendah nilai Std. Error, semakin akurat estimasi koefisien.

t test: Ini adalah pengujian hipotesis untuk setiap koefisien estimasi. Hipotesis yang diuji adalah bahwa koefisien itu sama dengan nol. Jika nilai t test besar (mendekati nol), maka kita dapat menolak hipotesis bahwa koefisien itu sama dengan nol. Dalam hal ini, semua koefisien memiliki nilai t test yang besar, sehingga dapat dianggap signifikan secara statistik.

Residual standard error (0.905955684): Ini adalah estimasi standar deviasi kesalahan (galat) model regresi. Semakin rendah nilai residual standard error, semakin akurat model.

Multiple R-squared (0.985701735): Ini adalah koefisien determinasi yang menunjukkan seberapa besar variabilitas data yang dapat dijelaskan oleh model. Nilai 0.9857 menunjukkan bahwa model dapat menjelaskan 98,57% variasi dalam data.

F-statistic (930.6705156): Ini adalah pengujian hipotesis untuk seluruh model regresi. Hipotesis yang diuji adalah bahwa tidak ada koefisien yang signifikan secara bersama-sama. Jika nilai F-statistic besar, maka kita dapat menolak hipotesis tersebut. Dalam hal ini, nilai F-statistic yang besar menunjukkan bahwa model secara signifikan dapat menjelaskan variasi dalam data.

Dof (27): Ini adalah derajat kebebasan residual, yang menunjukkan jumlah pengamatan yang digunakan untuk mengestimasi koefisien model, dikurangi jumlah variabel prediktor dan konstanta. Dalam hal ini, terdapat 27 derajat kebebasan residual

Posting Komentar untuk "Cara Tutorial Menghitung Regresi Linier Berganda di Microsoft Excel"