Regresi di Excel: contoh persamaan. regresi linier

analisis regresi - metode studi statistik untuk menunjukkan ketergantungan dari parameter satu atau lebih variabel independen. Dalam era pra-komputer, penggunaannya telah agak sulit, terutama ketika datang ke volume data yang besar. Hari ini, belajar bagaimana membangun regresi di Excel, Anda dapat memecahkan masalah statistik yang kompleks hanya dalam beberapa menit. Di bawah ini adalah contoh-contoh spesifik ekonomi.

jenis regresi

Konsep ini diperkenalkan untuk matematika oleh Francis Galton pada tahun 1886. Regresi adalah:

• linear;
• parabola;
• kekuasaan;
• eksponensial;
• hiperbolik;
• eksponensial;
• logaritmik.

CONTOH 1

Pertimbangkan masalah penentuan ketergantungan jumlah pengunduran diri dari anggota staf dari upah rata-rata di 6 perusahaan industri.

Tugas. Enam perusahaan telah menganalisis gaji bulanan rata-rata dan jumlah karyawan yang berhenti secara sukarela. Dalam bentuk tabel yang kita miliki:

	A	B	C
1	X	Jumlah pengunduran diri	gaji
2		y	30000 rubel
3	1	60	35.000 rubel
4	2	35	40000 rubel
5	3	20	45000 rubel
6	4	20	50.000 rubel
7	5	15	55.000 rubel
8	6	15	60000 rubel

Untuk masalah menentukan ketergantungan para pekerja pemisahan jumlah dari gaji rata-rata untuk model regresi 6 perusahaan memiliki bentuk persamaan Y = a ₀ + ₁ x ₁ + ... + a _k x _k, di mana x _i - mempengaruhi variabel, _i - regresi koefisien, ak - sejumlah faktor.

Y untuk tugas yang diberikan - itu adalah sebuah indikator untuk memecat seorang karyawan, faktor - gaji, yang dilambangkan dengan X.

Memanfaatkan kekuatan "Excel" spreadsheet

analisis regresi di Excel harus didahului oleh suatu aplikasi untuk data tabel yang ada built-in fungsi. Namun, untuk tujuan ini lebih baik untuk menggunakan add-in "analisis paket" yang sangat berguna. Untuk mengaktifkannya, Anda perlu:

• dengan tab "File" pergi ke "Settings";
• di jendela yang terbuka, pilih 'Add-ons';
• klik pada tombol "Go", yang terletak di kanan bawah garis "manajemen";
• menempatkan tanda centang di sebelah "Analisis ToolPak" dan mengkonfirmasi tindakan Anda dengan menekan "OK".

Jika dilakukan dengan benar, sisi kanan tab "Data", terletak di atas lembar kerja "Excel", menunjukkan tombol yang diinginkan.

Linear regresi di Excel

Sekarang bahwa Anda memiliki di tangan semua alat virtual yang diperlukan untuk perhitungan ekonometrik, kita dapat mulai untuk mengatasi masalah kita. Untuk melakukan hal ini:

• diklik pada "Analisis Data";
• klik pada tombol "regresi" di jendela yang terbuka;
• tab yang muncul untuk memperkenalkan berbagai nilai-nilai Y (jumlah pekerja pemisahan) dan X (gaji mereka);
• menegaskan kembali tindakan mereka dengan menekan «Ok» tombol.

Akibatnya, program secara otomatis akan mengisi analisis regresi data sheet spreadsheet baru. Perhatikan! Dalam Excel, ada kesempatan untuk mengatur tempat yang Anda inginkan untuk tujuan ini. Sebagai contoh, mungkin lembar yang sama, di mana nilai-nilai Y dan X, atau bahkan sebuah buku baru, khusus dirancang untuk penyimpanan data tersebut.

Hasil analisis regresi untuk R-square

Data Excel diperoleh dalam contoh data yang dianggap memiliki bentuk:

Pertama-tama, kita harus memperhatikan nilai R-squared. Ini merupakan koefisien determinasi. Dalam contoh ini, R-square = 0,755 (75,5%), m. E. Parameter yang dihitung dari model untuk menjelaskan hubungan antara parameter dianggap oleh 75,5%. Semakin tinggi nilai koefisien determinasi, model yang dipilih dianggap lebih berguna untuk tugas-tugas tertentu. Hal ini diyakini benar menggambarkan situasi nyata pada nilai R-square di atas 0,8. Jika R-square <0,5, maka analisis regresi di Excel tidak bisa dianggap wajar.

analisis rasio

Jumlah 64,1428 menunjukkan apa yang akan menjadi nilai Y, jika semua variabel xi dalam model kami akan disetel ulang. Dengan kata lain, dapat dikatakan bahwa nilai parameter dianalisis dipengaruhi oleh faktor selain yang dijelaskan dalam model tertentu.

Faktor berikutnya -,16285 terletak di B18 sel, menunjukkan pengaruh penting dari variabel X ke Y. Ini berarti bahwa rata-rata gaji karyawan dalam model mempengaruhi jumlah pengunduran diri dari berat -0,16285, t. E. Tingkat dampaknya sama sekali kecil. Tanda "-" menunjukkan bahwa koefisien negatif. Hal ini jelas, karena kita semua tahu bahwa semakin gaji di perusahaan, semakin sedikit orang telah menyatakan keinginan untuk mengakhiri kontrak kerja atau dipecat.

regresi berganda

Dalam istilah ini mengacu pada persamaan komunikasi dengan beberapa variabel independen dalam bentuk:

y = f (x ₁ + x ₂ + ... x _m) + ε, di mana y - adalah nilai fitur (variabel dependen), dan x _1, x _2, ... x _m - tanda-tanda faktor (variabel independen).

estimasi parameter

Untuk regresi berganda (MR) itu dilakukan dengan menggunakan metode kuadrat terkecil (LSM). Untuk persamaan linear dari bentuk Y = a + b ₁ x _{1 + ... + b m x m +} ε membangun sistem persamaan normal (cm. Di bawah)

Untuk memahami prinsip metode, kita mempertimbangkan kasus dua faktor. Kemudian kita telah situasi yang digambarkan dengan rumus

Oleh karena itu, kita memperoleh:

di mana σ - adalah varian dari fitur masing-masing, tercermin dalam indeks.

MNC berlaku untuk persamaan MR untuk standartiziruemom skala. Dalam hal ini, kita mendapatkan persamaan:

dimana t _y, t _{x 1, ...} t _xm - standartiziruemye variabel yang nilai rata-rata adalah 0; ß _i - koefisien regresi standar dan standar deviasi - 1.

Harap dicatat bahwa semua ß _i dalam hal ini didefinisikan sebagai normal dan tsentraliziruemye, karena perbandingan antara dianggap valid dan dapat diterima. Selain itu, diterima untuk melaksanakan skrining faktor, membuang orang-orang yang memiliki nilai terendah dari βi.

Masalah dengan menggunakan persamaan regresi linier

Misalkan Anda memiliki tabel dinamika harga produk N tertentu selama 8 bulan terakhir. Hal ini diperlukan untuk memutuskan apakah akuisisi partainya dengan harga 1850 rubel. / T.

	A	B	C
1	bulan	nama bulan	Harga N
2	1	Januari	1750 rubel per ton
3	2	Februari	1755 rubel per ton
4	3	Maret	1767 rubel per ton
5	4	April	1760 rubel per ton
6	5	Mei	1770 rubel per ton
7	6	Juni	1790 rubel per ton
8	7	Juli	1810 rubel per ton
9	8	Agustus	1840 rubel per ton

Untuk mengatasi masalah ini dalam tabel prosesor "Excel" diperlukan untuk menggunakan sudah dikenal misalnya alat "Analisis Data" yang disajikan di atas. Selanjutnya, pilih "Regresi" bagian dan mengatur parameter. Kita harus ingat bahwa dalam "jangkauan Masukan Y» harus diperkenalkan dengan rentang nilai dari variabel dependen (dalam hal ini harga barang di bulan tertentu dalam setahun) dan di "Input Interval X» - untuk independen (bulan). Kami mengkonfirmasi tindakan dengan mengklik «Ok». Dalam lembar kerja baru (jika demikian ditunjukkan), kita memperoleh data untuk regresi.

Kami sedang membangun pada mereka persamaan linear dari bentuk y = ax + b, dimana parameter a dan b adalah koefisien dari nomor baris dari bulan dan nama koefisien dan «Y-persimpangan" garis lembar dengan hasil analisis regresi. Dengan demikian, persamaan linear regresi (EQ) 3 untuk masalah dapat ditulis sebagai:

Harga barang N = 11.714 * 1727,54 bulan jumlah +.

atau dalam notasi aljabar

y = 11.714 x + 1727,54

analisis hasil

Untuk memutuskan apakah menerima persamaan regresi linear memadai menggunakan beberapa koefisien korelasi (CMC) dan tekad serta uji dan Fisher t-test. Dalam tabel "Excel" regresi dengan hasil yang mereka bertindak di bawah beberapa nama R, R-Square, F-t-statistik dan statistik, masing-masing.

KMC R memungkinkan untuk memperkirakan kedekatan hubungan probabilistik antara variabel independen dan dependen. Nilai tinggi menunjukkan koneksi yang cukup kuat antara "Jumlah bulan" variabel dan "harga produk N dalam rubel per 1 ton." Namun, sifat dari hubungan ini tidak diketahui.

Kuadrat dari koefisien determinasi R ² (RI) merupakan karakteristik numerik dari proporsi total pencar dan menunjukkan pencar bagian data eksperimental, yaitu, nilai-nilai variabel dependen yang sesuai dengan persamaan regresi linear. Dalam masalah ini, nilai ini adalah 84,8%, mp. E. Statistik dengan tingkat akurasi yang tinggi diperoleh dijelaskan SD.

F-statistik, juga dikenal sebagai kriteria Fisher digunakan untuk menilai signifikansi ketergantungan linear atau tidak membuktikan hipotesis membenarkan keberadaannya.

Nilai t-statistik (uji t Student) membantu mengevaluasi signifikansi koefisien setiap bebas anggota ketergantungan linear tidak diketahui. Jika nilai t-test> t _cr, hipotesis persamaan tidak penting linear istilah bebas ditolak.

Dalam masalah ini untuk jangka gratis melalui instrumen "Excel" ditemukan bahwa t = 169,20903, dan p = 2,89E-12, t. E. Memiliki probabilitas nol yang beriman akan menolak hipotesis dari minimnya istilah gratis. Untuk koefisien yang tidak diketahui pada t = 5,79405, dan p = 0,001158. Dengan kata lain, probabilitas bahwa hipotesis yang benar ditolak akan tidak penting dari koefisien untuk diketahui, adalah 0,12%.

Dengan demikian, bisa dikatakan bahwa diperoleh persamaan regresi linear memadai.

Masalah kelayakan membeli saham

regresi berganda dilakukan di Excel menggunakan "Analisis Data" yang sama alat. Pertimbangkan aplikasi tertentu.

Panduan perusahaan «NNN» harus memutuskan apakah akan membeli 20% saham dari JSC «MMM». harga paket (SP) adalah 70 juta dolar AS. Spesialis «NNN» mengumpulkan data transaksi serupa. Diputuskan untuk menilai nilai saham pada parameter tersebut, dinyatakan dalam jutaan dolar AS, seperti:

• hutang (VK);
• volume omset tahunan (VO);
• piutang (VD);
• nilai aset tetap (SOF).

Selain itu, menggunakan utang upah perusahaan (V3 U) dalam ribuan dolar AS.

Prosesor tabel keputusan sarana Excel

Pertama, Anda perlu membuat tabel data masukan. Ini adalah sebagai berikut:

Berikutnya:

• kotak panggilan "analisis data";
• dipilih "Regresi" bagian;
• jendela "Input Interval Y» diberikan berbagai variabel dependen nilai dari kolom G;
• klik pada ikon dengan panah merah di sebelah kanan jendela "Input Interval X» dan terisolasi di berbagai lembar dari semua nilai dari kolom B, C, D, F.

Tandai titik "New worksheet" dan klik "Ok".

Dapatkan analisis regresi untuk tugas ini.

Hasil penelitian dan kesimpulan

"Kumpulkan" bulat dari data yang disajikan di atas pada persamaan prosesor Excel regresi tabel sheet:

SD = 0,103 * SOF + 0.541 * VO - 0031 * VK + 0.405 * VD + 0.691 * VZP - 265.844.

Dalam bentuk matematika lebih biasa dapat ditulis sebagai:

Y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Data untuk MMM adalah sebagai berikut:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535.5	45.2	41.5	21.55	64.72

Dengan mensubstitusikannya ke dalam persamaan regresi, dapatkan angka 64,72 juta dollar Amerika. Artinya, saham MMM tidak boleh dibeli, karena nilainya 70 juta dollar AS agak dilebih-lebihkan.

Seperti yang Anda lihat, penggunaan prosesor tabel Excel dan persamaan regresi memungkinkan untuk membuat keputusan tentang kelayakan transaksi yang sangat spesifik.

Sekarang Anda tahu apa itu regresi. Contoh di Excel, yang dibahas di atas, akan membantu Anda dalam memecahkan masalah praktis dari bidang ekonometrik.