Cara melakukan regresi di excel

Jumpa lagi ya Gaes….. bagaimana kabar?…. mantan sehat kan?…..hahaha…. lupakan saja dulu, yang lalu biarlah berlalu, kali ini kita akan membahas tentang analisis regresi sederhana menggunakan bantuna Microsoft Excel.

Bila tulisan sebelumnya kita telah membahas dasar dari analisis regresi sederhana. Bila Anda belum membaca silahkan baca dulu di SINI. Bila Anda sudah membaca, kita lanjutkan menggunakan satu set alat statistik yang telah disediakan oleh pengembang Ms Excel yang diberi nama “Analysis ToolPak”. Alat ini merupakan satu set perangkat “Data Analysis” dalam kelompok pengolahan DATA di Ms Excel.

Memunculkan AnalysisToolPak

Sebelum melakukan analisis regresi menggunakan analysis toolpak excel, Anda harus mengaktifkan dulu fitur ini di Ms Excel Anda seperti pada gambar berikut.

Bila Anda belum pernah menggunakan alat ini Anda bisa membaca petunjuk pemasangan di SINI untuk Ms Excel 2007 ke bawah, atau di SINI untuk Ms Excel 2010 ke atas.

Nah, bila Analysis ToolPak sudah berhasil dipasang maka kita akan segera memulai analisis regresi sederhana ini. Untuk mempermudah, kita pakai contoh yang sama dengan artikel “PENGANTAR ANALISIS REGRESI SEDERHANA”. Misalnya saya adalah seorang manajer toko sepeda motor dengan data penjualan dan jumlah sales sebagai berikut:

Singkat cerita, saya berencana menambah seorang tenaga sales menjadi 6 orang sales, kira-kira berapa penjualan tiap harinya?. Dalam tulisan sebelumnya telah kita hitung secara manual yang hasilnya adalah:

Y = 1,85 + 0,25X + e   

atau

Y’  = 1,85 + 0,25X

Dari persamaan

Y = a + bX + e   

atau

Y’  = a + bX

Dengan menggunakan Analysis ToolPak, kita tidak perlu lagi menghitung secara manual. Kita cukup memberi perintah pada komputer dan dalam sekejap komputer akan menyelesaikan penghitungan tersebut. Berikut langkah-langkahnya:

• Siapkan datanya, buat file baru di Ms Excel dan buat tabel data seperti di bawah ini.

• Buka Analysis ToolPak dengan cara pilih tool bar “DATA” kemudian pilih “Data Analysis” seperti gambar di bawah ini.

• Lanjutkan dengan memilih “Regression” dengan menggeser “Scroll Bar” lalu klik “OK”
• Setelah keluar menu “Regression” isi Range Data untuk Y dan kemudian X. Cara mengisi range data cara klik tombol yang ada panah merah diagonalnya, kemudian pilih seluruh range Y, setelah terpilih akhiri dengan “Enter”. Untuk X lakukan prosedur yang sama.

• Setelah pengisian data variabel Y dan X selesai, lanjutkan dengan mencentang “Labels” karena kita telah memilih cell nama variabel yaitu X dan Y. bila anda hanya memasukkan data saja “Labels” tidak perlu dicentang.
• Untuk output atau hasil analisis disediakan 3 (tiga) pilihan yaitu: di dalam lembar kerja yang sama dengan data kita, di lembar kerja yang lain (New Worksheet), atau di file yang berbeda (New Workbook).

• Untuk kali ini kita akan tampilkan hasil regresi di Sheet yang sama dengan data tadi. Sehingga dibutuhkan ruang kosong untuk menampilkan data. Proses pengisian range cell output ini sama dengan pengisian range data Y dan X

• Pada pilihan residual, Anda bisa memilih output residual apa saja yang anda butuhkan. Kali ini saya hanya ingin menampilkan perbandingan Y’ dan Y, atau Y estimasi dan Y aktual saja. Maka, saya memilih hanya mencentang “Line Fit Plots”.
• Setelah pengisian menu Regression selesai akhiri dengan klik “OK”. Dalam sekejab output akan keluar di range output tadi sperti pada gambar di bawah ini. Hasilnya sama kan Gaes… dengan hitung manual…

• Pada grafik “Line Fit Plots” di bawah ini menunjukkan selisih antara Y estimasi dan Y aktual. Y aktual ditandai dengan titik biru dan Y estimasi dengan titik jingga.

Demikan Gaes… cara melakukan regresi dengan Analysis ToolPak di Ms Excel. Mudah dan cepat bukan?….

Sekarang kita akan menginterpretasikan hasil persamaan regresi sederhana menggunakan excel tersebut. Masih dengan cerita yang sama, yaitu saya berencana menambah seorang tenaga sales menjadi 6 orang sales. Untuk mengestimasinya kita menggunakan persamaan regresi sederhana. Dengan rumus persamaan sebagai berikut:

Y = a + bX + e   

atau

Y’  = a + bX

Dan hasil penghitungan menggunakan rumus OLS adalah sebagai berikut.

Y = 1,85 + 0,25X + e   

atau

Y’  = 1,85 + 0,25X

Jadi bila jumlah sales toko saya sebanyak 6 orang maka estimasi penjualan rata-rata setiap harinya adalah:

Y’  = 1,85 + 0,25(6)

Y’  = 1,85 + 1,5

Y’  = 3,35

Estimasi penjualan saya menjadi rata-rata 3,35 unit motor per harinya. Atau setiap penambahan seorang sales maka rata-rata penjualan motor per hari akan bertambah menjadi 0,25 unit. Angka ini dalam regresi disebut koefisien regresi atau bila diterjemahkan dalam grafik disebut slope (kemiringan). Bila slope positif maka grafik akan naik seiring dengan bertambahnya nilai X begitu pula sebaliknya. Hal ini juga sering disebut pengaruh positif. Bila nilai koefisien ini negatif maka dikatakan sebagai pengaruh negatif X terhadap Y.

Persamaan di atas juga berarti bahwa ketika saya tidak menggunakan tenaga sales seorang pun maka penjualan motor rata-rata tiap hari adalah 1,85 unit. Angka ini dalam regresi disebut dengan istilah konstanta atau intercept, seperti pada gambar berikut ini.

Baiklah Gaes… cukup sekian dulu ya bahasan kita tentang analisis regresi sederhana menggunakan bantuan Ms Excel …. Sampai jumpa lagi di lain kesempatan…. (maglearning.id).