Iklan

Analisis Regresi dengan Excel


Dalam statistik, regresi merupakan salah satu peralatan yang populer digunakan, baik pada ilmu-ilmu sosial maupun ilmu-ilmu eksak. Karenanya, software-software statistik umumnya memiliki fasilitas untuk pendugaan dan analisis regresi ini. Misalnya, SPSS, Minitab, LISREL, Eviews, STATA, dan lainnya.

Sebenarnya Program Excel juga memiliki fasilitas perhitungan regresi ini. Analisis-analisisnya juga relatif lengkap. Oleh karenanya, tidak ada salahnya kita juga bisa menggunakan fasilitas ini. Selain prosedurnya lebih gampang, Program Excel umumnya terdapat di hampir semua komputer, sebagai bagian dari Microsoft Office.

Ok. Kita mulai tahapan-tahapannya.

1. Misalnya kita ingin menduga persamaan regresi untuk melihat pengaruh harga dan pendapatan terhadap permintaan suatu barang. Katakanlah kita punya 10 set data (tahun atau daerah). Permintaan kita hitung dalam jumlah unit barang, harga dalam ribu rupiah perunit barang dan pendapatan dalam ribu rupiah perkapita. Sebagai latihan ketikkan angka-angka berikut pada range A1:C11 seperti terlihat pada tampilan 1 berikut:

Tampilan 1. Data untuk Regresi


 

2. Klik menu Tool kemudian klik Data Analysis. (Catatan: jika setelah mengklik Tool, ternyata tidak muncul pilihan Data Analysis, berarti menu tersebut belum diaktifkan di program Excel Anda. Untuk mengaktifkannya, klik Tool, kemudian klik Add ins, selanjutnya conteng pada pilihan Analysis Toolpak, setelah itu klik ok. Lalu ulangi tahap 2 ini).

Tampilan yang muncul setelah mengklik Data Analysis adalah seperti tampilan 2. Selanjutnya klik Regression dan klik OK.

Tampilan 2. Data Analysis

 

3. Selanjutnya akan muncul tampilan 3 berikut:

 Tampilan 3. Regression

Isi Input Y Range (bisa dengan mengetikkan ke dalam kotak putihnya atau memblok data). Input Y Range adalah variabel yang menjadi variabel terikat (dependent variable). Kemudian isikan Input X Range. Input X Range adalah variabel yang menjadi variabel bebas (independent variable). Semua variabel bebas diblok sekaligus. Catatan: Baik Y range maupun X range, didalamnya termasuk judul/nama variabel.

Selanjutnya conteng kotak Labels. Ini artinya, memerintahkan Excel untuk membaca baris pertama dari data kita sebagai nama variabel. Anda juga bisa menconteng Constant is Zero, jika menginginkan output regresi dengan konstanta bernilai 0. Anda juga bisa menconteng Confidence Level jika ingin mengganti nilai confidence level (jika tidak diconteng, Excel akan memberikan confidence level 95%). Dalam latihan kita kedua pilihan tersebut tidak kita conteng.

Selanjutnya pada Output Option kita bisa menentukan penempatan output/hasilnya. Bisa pada worksheet baru atau workbook baru. Katakanlah kita menempatkan output di worksheet yang sama dengan data kita. Conteng Output Range dan isi kotak putihnya dengan sel pertama dimana output tersebut akan ditempatkan. Dalam contoh ini, misalnya ditempatkan pada sel A16.

Pada pilihan Residual, terdapat 4 pilihan. Anda bisa menconteng sesuai dengan keinginan. Dalam kasus ini kita conteng semua pilihan tersebut. Selanjutnya, terdapat pilihan untuk menghasilkan Normal Probability. Dalam kasus kita, juga kita conteng pilihan ini.

Setelah itu, klik OK. Maka akan muncul hasil regresi berikut:

 

SUMMARY OUTPUT

         
             

Regression Statistics

         

Multiple R

0.9714

         

R Square

0.9436

         

Adjusted R Square

0.9275

         

Standard Error

81.0698

         

Observations

10

         
             

ANOVA

           

 

df

SS

MS

F

Significance F

 

Regression

2

769993.78

384996.89

58.58

0.00

 

Residual

7

46006.22

6572.32

     

Total

9

816000.00

 

 

 

 
             

 

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

607.53

274.67

2.21

0.06

-41.97

1257.03

Harga

-13.31

4.59

-2.90

0.02

-24.17

-2.44

Pendapatan

0.36

0.09

3.78

0.01

0.13

0.58

  

RESIDUAL OUTPUT

     

PROBABILITY OUTPUT

             

Observation

Predicted Permintaan

Residuals

Standard Residuals

 

Percentile

Permintaan

1

498.2362193

1.763780707

0.024669343

 

5

300

2

262.9793289

37.02067106

0.517794321

 

15

500

3

738.2489515

-38.24895147

-0.534973821

 

25

600

4

743.0047933

56.99520671

0.797170703

 

35

700

5

747.7606351

-147.7606351

-2.066672903

 

45

800

6

880.8343319

19.16566806

0.268063052

 

55

900

7

921.2365189

78.76348113

1.10163544

 

65

1000

8

1089.956561

-89.95656081

-1.258188871

 

75

1000

9

1054.310216

45.6897843

0.639045975

 

85

1100

10

1263.432445

36.56755542

0.511456762

 

95

1300

Ada empat tabel hasil yang ditampilkan (yang tergantung pada pilihan yang kita buat sebelumnya), yaitu SUMMARY OUTPUT, ANOVA, RESIDUAL OUTPUT, dan PROBABILITY OUTPUT. Pada SUMARY OUTPUT ditampilkan nilai multiple R, R square, adjusted R square, standard error dan jumlah observasi. Pada ANOVA ditampilkan analisis variance dan nilai F serta pengujiannya. Selanjutnya ditampilkan perhitungan regresi kita yang mencakup intercept (konstanta) dan koefisien-koefisien regresi untuk masing-masing variabel. Dari hasil ini kita bisa membentuk persamaan regresi menjadi:

Permintaan = 607,53 – 13,31Harga + 0,36 Pendapatan.

Selanjutnya, pada tabel tersebut juga dimunculkan standard error, t stat, P-value, confidence level untuk 95% (karena kita tidak mengganti default nilai ini pada tahap sebelumnya).

Selain itu, karena tadi kita menconteng empat pilihan residual output dan 1 pilihan normal probability, maka juga ditampilkan 5 kurva untuk pilihan-pilihan tersebut. Tetapi seperti yang kita lihat di bawah ini, kelima kurva tersebut bertumpuk . Untuk itu, kita perlu memindahkan (menarik) kurva-kurva tersebut ke bagian yang lain dari worksheet kita sehingga bisa dibaca.

Tampilan 5. Hasil Perhitungan 2

Untuk sementara sekian dulu tulisan ini. Interpretasi mengenai hasil output Excel ini, silakan lihat pada tulisan Memahami Output Regresi dari Excel

Iklan

32 Tanggapan

  1. mas, kalo buat grafik regresi linier yg skala x dan y nya sama gimana ya?? mohon infonya. terima kasih.

  2. DEAR COLLEAGUES AND FRIENDS,

    SAYA INGIN MENGINFORMASIKAN BAHWA TELAH TERBIT BUKU BARU YANG BERJUDUL “APLIKASI ANALISIS DATA STATISTIK UNTUK ILMU SOSIAL SAINS DENGAN STATA” BUKU INI TERDIRI DARI DUA VOLUME, YAITU UNTUK VOLUME 1 MEMBAHAS TENTANG ANALISIS UNIVARIAT, MULTIVARIAT, PARAMETRIK DAN NON-PARAMETRIK SERTA SAMPLING, POPULASI, VALIDITAS, RELIABILITAS DAN SEBAGAINYA (BUKUNYA SUDAH TERBIT SAAT INI DAN DAFTAR ISINYA DAPAT DILIHAT PADA ATTACH FILE) DAN VOLUME 2 AKAN MEMBAHAS TENTANG SEM, BAYESIAN DAN MULTILEVEL MODELING (SEDANG DI PROSES). BAGI YANG BELUM MENGENAL PROGRAM STATA, PERLU DIKETAHUI BAHWA STATA MERUPAKAN PROGRAM STATISTIK YANG SANGAT LENGKAP DAN POWERFUL. MENGAPA? KARENA SELAIN MENYEDIAKAN FITUR ANALISIS MULTIVARIAT YANG KOMPLIT, STATA JUGA DAPAT DIGUNAKAN UNTUK SEM, BAYESIAN, MULTILEVEL MODELING DAN BAHKAN FITUR TIME-SERIES NYA ITU SERUPA DENGAN EVIEWS. STATA JUGA SUDAH TERINTEGRASI DENGAN R PEKCAGES DAN MEMPUNYAI TAMPILAN GRAFIK YANG LUAR BIASA. BUKU INI DITULIS DENGAN TUJUAN UNTUK MENGISI KEKOSONGAN LITERATUR BUKU STATA YANG ADA DI INDOENSIA DAN DILUAR NEGERI PROGRAM STATA SANGAT POPULAR DIGUNAKAN DI BERBAGAI UNIVERSITAS TERNAMA. ISI DARI BUKU INI JUGA SUDAH DIBUAT DENGAN STANDAR KUALITAS YANG TINGGI SEHINGGA DIHARAPKAN DAPAT MENJADI PEGANGAN BAGI PARA PENELITI, MAHASISWA MAUPUN DOSEN PENGAMPU MATA KULIAH STATISTIK. TUNGGU APA LAGI UNTUK MEMILIKI BUKU INI? SEGERA HUBUNGI hengkylatan@yahoo.com. Thanks

  3. terima kasih pak. sangat membantu tugas saya 🙂

  4. sangat membantu bro. terima kasih 🙂

  5. Assalamu’alaikum Ya MR.SPSS Bapk Juanidi(Mhon maap gelas akademinya tidak dilayangkan)
    Perkenalkan sya mahasiswa Undar – Jombng
    kemrin pda wktu pembelajaran SPSS di asuh oleh Bpk Junadi(nma beliau jgg sama dg Bpk)
    wktu praktek dg smua mahasiswa dg dosen mengalami kesulitan pengoprasian antara excel 2000 dan excel windows 7
    settingannya berbeda dosen kami kesulitan untuk memberikan arahan karena beliau pkenya excel windows 2000
    pada menu tampilan Regresion
    akhirnya berimbas pada nilai nilai tidak sama(padahal data sama semua mahasiswa sama) dan pemakaian rumus jg ditolak oleh program

    Tlg kami di bantu untuk lebih memahami dan mempaktekkan antara Teory dan Praktek dilapangan

  6. pak mw nanya nih,,
    klo misalnya nilai intercept tidak bisa keluar hasilnya atau hanya tertulis #N/A,
    dimanakah letak kesalahannya ?

  7. wah membantu sekali tulisannya, khususnya untuk orang2 yg ga familiar dengan rumus2 sophisticated di excell, dan terpaksa berurusan dengan rumus2 itu sbg tuntutan tugas kuliah seperti saya. Terima kasih 🙂

  8. mau numpang tanya..klo cara regresi berganda gimana ya??

  9. ass. siang mas, aq minta tolong dibuatkan analisis regresi berganda terhadap data saya, kalau bisa aq minta email mas dan tolong dikirimkan ke e-mail saya gusti_nhl@yahoo.com

  10. terima kasih banyak Pak ilmunya….

  11. Mohon maaf saya ingin bertanya, saya mencoba menggunakan excel untuk menghitung proyeksi dan prognosa data pasien. fungsi yang saya pakai forecast =(H$5;$C7:$G7;$C$5:$G$5)
    namun ternyata tidak bisa jalan. Apakah bapak ada email?saya ingin mengirim email kepada bapak
    Terimakasih

    Sepertinya cara penulisan fungsinya kurang tepat. Bisa diemailkan kasus lebih lengkapnya ke saya. Mudah-mudahan bisa saya bantu

  12. siiipppppppp, sangat membantu

  13. pak, bagaimana cara memindahkan data dari spss ke excel

    tinggal copy paste seperti biasa saja

  14. terimakasih pak…
    berkat postingnya ane terbantu sekali menyelesaikan tugas….

  15. trimakasih pak atas tulisannya . . . sangat bermanfaat . . . semoga amalan ilmunya mendapat balasan dari Yang Maha Pengasih

  16. pak numpang bertanya, : kalo exel 2007, ditampilannya tidak ada Tool dan Data analysis, bagaimana mengaktifkan fungsi regresinya?
    Terima kasih.

  17. thanks atas Inspirasi’y………….

  18. pak dengan data yang sama kok hasilnya berbeda ya? standart errornya lebih besar. bagaimana cara ubahnya ya?
    kemarin dulu sy lakukan hasilnya benar, lalu hari ini saya coba lagi dengan data yang sama, hasilnya berbeda. bisa tolong bantu? terimakasih pak.

    Coba dicek lagi. Mungkin salah waktu memblok range datanya

  19. ass pak.
    saya ingin nanya.
    bagaimana caranya menentukan trenline yng tepat.
    sedangakan pada kasus saya, dengan menggunakan trenline polynomial mendapatkan r yang besar namun persamaanya jika saya cek kok tidak tepat.
    trima kasih sebelumnya

    Lihat beberapa tulisan saya yang terkait dengan estimasi dan pemilihan model pada bagian tip-trik.

  20. mohon bantuannya untuk menentukan grafik residual vs normal% prob, klo dilakukan secara manual tanpa bantuan perangkat lunak, gimana yach??thanks

  21. kalo data panel bisa pake excel? trims

    kayaknya tidak bisa.

  22. pak,saya mau tanya,,analisis penelitian saya mempunyai 8 variabel bebas dan 1 variabel terikat,. Apakah perhitungannya bisa menggunakan excel?? bagaimana caranya pak?? trimaksih sebelumnya.

    Bisa saja. Kalau menggunakan analisis regresi, coba lihat tulisan saya mengenai regresi dengan excel pada blog ini

  23. pak kok tetep gak bisa

  24. nice info pak… sangat berguna… 🙂

  25. pak saya mau nanya, saya sedang melakukan trend analisis data hujan dengan metode mann-kendall trend analisis… bagaimana ya carannya dengan menggunakan SPSS atau Excel….. terima kasih

    • pak..saya juga sedang mempelajari methode mann-kendall… pertanyaannya sama dengan pak wisnu, lebih baik saya menggunakan excel atau spss… terima kasih

  26. ass pak..saya mo nanya… klo sudh melakukan regresi linear dgn excel spt diatas, kapan kita melakukan uji asumsi klasiknya pak…apa heterskedastisitas bisa juga pake excel pak..?? makasi

    Sebaiknya menggunakan SPSS, fasilitas yang disediakan lebih banyak

  27. ass wrwb, pak sy mau tanya kalo pakai regresi berganda bagaimana pengerjaannya di excel , mohon penjelasannya, makasih pak wasalaam

    Contoh diatas adalah regresi berganda

  28. pak…sy minta tolong dong.
    saya kesulitan nih di excel.
    Misal saya punya persamaan Y = Vo – a1*b1 – a2*b2 – a3*b3..dst.

    untuk Vo, dan variabel b sudah diketahui, nah gmn caranya mencari variabel a menggunakan excel.

    mohon bantuannya pa?

    Vo dan b (maksudnya b1, b2, b3 dst) sudah diketahui, tetapi a (maksudnya a1,a2, a3 dst) belum diketahui.
    Kata kuncinya disini adalah berapa banyak nilai dalam persamaan yang belum diketahui ? Jika ada dua, maka kita butuh minimal dua persamaan untuk menyelesaikannya. Jika tiga, maka kita butuh minimal tiga persamaan untuk menyelesaikannya.
    Ini dinamakan sistem persamaan linear.
    Untuk memecahkan sistem persamaan linear ini secara manual membutuhkan beberapa tahap perhitungan dan metode yang umum digunakan adalah metode matriks (terutama jika persamaannya banyak). Oleh karenanya, kita tidak dapat membuat satu rumus saja di excel.
    Salah satu cara menyelesaikan sistem persamaan linear ini, silakan baca tulisan saya mengenai Pemecahan Sistem Persamaan Linear dengan Add-in Matrix di Excel yang ada di blog ini

  29. makasih atas tipsnya 😀

  30. mhn maaf pak mau tanya, utk nentukan linier, kuadratik, kubik , gimana caranya apakah dgan melihat sebaran populasi data, atau gmana, ? mhn pencerahanya ,

    Lihat tulisan saya disini, disini, disini

  31. Kalo buat grafiknya gmn caranya?
    terutama yang regresi kuadratik. pake spss tambah bingung .makasih

    Jawab:Kalo grafik diatas tidak perlu dibuat secara terpisah. Itu hasil dari pilihan ketika kita menconteng Residual plots,line fit plots dan normal probability plots.
    Untuk regresi kuadratik, tinggal dikuadratkan dulu nilai-nilai dalam variabelnya, lalu lakukan regresi seperti biasa.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: