Regresi dalam Excel: contoh persamaan. linear regresi

analisis regresi - kaedah kajian statistik untuk menunjukkan pergantungan parameter satu atau lebih pemboleh ubah. Dalam era pra-komputer, penggunaannya telah agak sukar, terutama apabila ia datang kepada jumlah yang besar data. Hari ini, belajar bagaimana untuk membina sebuah regresi dalam Excel, anda boleh menyelesaikan masalah statistik kompleks dalam hanya beberapa minit. Di bawah ini adalah contoh-contoh khusus ekonomi.

jenis regresi

Konsep ini telah diperkenalkan kepada matematik oleh Francis Galton pada tahun 1886. Regresi ialah:

• linear;
• parabola;
• kuasa;
• eksponen;
• hiperbola;
• eksponen;
• logaritma.

CONTOH 1

Pertimbangkan masalah menentukan pergantungan jumlah peletakan jawatan ahli kakitangan daripada gaji purata dalam 6 syarikat industri.

Tugas. enam syarikat telah menganalisa gaji bulanan purata dan bilangan pekerja yang berhenti secara sukarela. Dalam bentuk jadual kita mempunyai:

	A	B	C
1	X	Bilangan peletakan jawatan	gaji
2		y	30000 Rubles
3	1	60	35000 Rubles
4	2	35	40000 Rubles
5	3	20	45000 Rubles
6	4	20	50.000 Rubles
7	5	15	55000 Rubles
8	6	15	60000 Rubles

Untuk masalah menentukan pergantungan jumlah pemisahan pekerja daripada gaji purata bagi 6 perusahaan model regresi mempunyai bentuk persamaan Y = a ₀ + ₁ x ₁ + ... + a _k x _k, di mana x _i - mempengaruhi pembolehubah, _i a - pekali regresi, ak - beberapa faktor.

Y untuk tugas yang diberikan - ia merupakan petunjuk kepada api pekerja, faktor penyumbang - gaji, yang diwakili oleh X.

Memanfaatkan kuasa "Excel" hamparan

analisis regresi dalam Excel harus didahului oleh suatu permohonan kepada data jadual yang sedia ada terbina dalam fungsi. Walau bagaimanapun, bagi tujuan ini ia adalah lebih baik untuk menggunakan "analisis paket" add-dalam sangat berguna. Untuk membolehkannya, anda perlu:

• dengan tab "File" pergi ke "Tetapan";
• dalam tetingkap yang terbuka, pilih 'tambah';
• klik pada butang "Go", yang terletak di sebelah kanan bahagian bawah baris "pengurusan";
• meletakkan tanda semak di sebelah "Analisis ToolPak" dan sahkan tindakan anda dengan menekan "OK".

Jika dilakukan dengan betul, sebelah kanan tab "Data", yang terletak di atas lembaran kerja "Excel", menunjukkan butang yang dikehendaki.

Linear Regression dalam Excel

Sekarang bahawa anda mempunyai di tangan semua peralatan maya yang diperlukan untuk pengiraan ekonometrik, kita boleh mula untuk menangani masalah kita. Untuk melakukan ini:

• butang diklik pada "Analisis Data";
• klik pada butang "regresi" dalam tetingkap yang terbuka;
• tab yang muncul untuk memperkenalkan julat nilai Y (jumlah pemisahan pekerja) dan X (gaji mereka);
• mengesahkan tindakan mereka dengan menekan butang «Ok».

Hasilnya, program ini secara automatik akan mengisi lembaran hamparan analisis regresi data baru. Memberi perhatian! Dalam Excel, terdapat peluang untuk menetapkan tempat, yang lebih suka untuk tujuan ini. Sebagai contoh, ia mungkin kunci yang sama, di mana nilai-nilai Y dan X, atau bahkan sebuah buku baru, yang direka khusus untuk menyimpan data tersebut.

keputusan analisis regresi bagi R-square

Data yang diperolehi dalam Excel data contoh dianggap mempunyai bentuk:

Pertama sekali, kita perlu memberi perhatian kepada nilai R-kuasa dua. Ia mewakili pekali penentuan. Dalam contoh ini, R-square = 0,755 (75.5%), m. E. Parameter dikira model untuk menjelaskan hubungan antara parameter dipertimbangkan oleh 75.5%. nilai yang lebih tinggi pekali penentuan, model yang dipilih dianggap lebih berguna untuk tugas-tugas tertentu. Ia dipercayai betul menggambarkan keadaan sebenar pada nilai R-square di atas 0.8. Jika R-square <0.5, maka analisis regresi dalam Excel tidak boleh dianggap munasabah.

analisis nisbah

Nombor 64,1428 menunjukkan apa yang akan menjadi nilai Y, jika semua xi pembolehubah dalam model kami akan ditetapkan semula. Dalam erti kata lain, ia boleh dikatakan bahawa nilai parameter yang dianalisis dipengaruhi oleh faktor-faktor selain daripada yang dinyatakan dalam model tertentu.

Faktor seterusnya -,16285 bertempat di B18 bimbit, menunjukkan pengaruh penting dalam pemboleh ubah untuk Y. Ini bermakna bahawa gaji purata pekerja dalam model yang memberi kesan kepada bilangan peletakan jawatan daripada berat -,16285, t. E. tahap kesan pada semua kecil. Tanda "-" menunjukkan bahawa pekali adalah negatif. Ia adalah jelas, kerana kita semua tahu bahawa gaji lebih dalam perusahaan, yang kurang orang telah menyatakan hasrat untuk menamatkan kontrak pekerjaan atau dibuang kerja.

regresi

Di bawah istilah ini merujuk kepada persamaan komunikasi dengan beberapa pembolehubah bebas dalam bentuk:

y = f (x ₁ + x ₂ + ... x _m) + ε, di mana y - adalah skor ciri (pembolehubah bersandar), dan x _1, x _2, ... x _m - adalah tanda-tanda faktor (pembolehubah bebas).

anggaran parameter

Untuk pelbagai regresi (MR) ia dilakukan dengan menggunakan kaedah kuasa dua terkecil (LSM). Untuk persamaan linear bentuk Y = a + b ₁ x _{1 + ... + b m x m +} ε membina sistem persamaan normal (cm. Di bawah)

Untuk memahami prinsip kaedah, kami mengambil kira kes dua faktor. Kemudian kami telah keadaan yang dihuraikan oleh formula

Oleh itu, kita peroleh:

di mana σ - adalah varians ciri masing-masing, yang dicerminkan dalam indeks.

MNC adalah berkenaan dengan persamaan MR untuk standartiziruemom besaran. Dalam kes ini, kita akan mendapat persamaan:

mana t _y, t _{x 1, ...} t _xm - standartiziruemye pembolehubah yang nilai purata adalah 0; β _i - pekali regresi piawai dan sisihan standard - 1.

Sila ambil perhatian bahawa semua β _i dalam kes ini ditakrifkan sebagai normal dan tsentraliziruemye, oleh itu perbandingan di antara dianggap sah dan boleh diterima. Di samping itu, ia diterima untuk menjalankan pemeriksaan faktor, membuang mereka yang mempunyai nilai-nilai terendah βi.

Masalah dengan menggunakan persamaan regresi linear

Katakan anda mempunyai jadual dinamik harga sebuah N produk tertentu untuk 8 bulan yang lalu. Ia adalah perlu untuk menentukan sama ada pengambilalihan partinya pada harga 1850 Rubles. / T.

	A	B	C
1	bulan	menamakan bulan	harga N
2	1	januari	1750 Rubles setiap tan
3	2	februari	1755 Rubles setiap tan
4	3	Mac	1767 Rubles setiap tan
5	4	April	1760 Rubles setiap tan
6	5	boleh	1770 Rubles setiap tan
7	6	jun	1790 Rubles setiap tan
8	7	julai	1810 Rubles setiap tan
9	8	ogos	1840 Rubles setiap tan

Untuk menyelesaikan masalah ini dalam pemproses tabular "Excel" diperlukan untuk digunakan sudah diketahui sebagai contoh alat "Analisis Data" dibentangkan di atas. Seterusnya, pilih "Regression" dan parameter yang disediakan. Kita harus ingat bahawa dalam "pelbagai Input Y» perlu diperkenalkan kepada julat nilai pembolehubah bersandar (dalam kes ini, harga barangan di bulan tertentu dalam setahun) dan dalam "Input selang X» - dengan bebas (bulan tersebut). Kami mengesahkan tindakan dengan mengklik «Ok». Dalam lembaran kerja baru (jika dinyatakan demikian), kami mendapatkan data untuk regresi.

Kami sedang membina ke atas mereka persamaan linear bentuk y = ax + b, di mana sebagai parameter a dan b ialah pekali dari nombor garis bulan dan nama pekali dan «Y-persimpangan" garis kunci dengan keputusan analisis regresi. Oleh itu, persamaan linear regresi (EQ) 3 untuk masalah ini boleh ditulis sebagai:

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

atau dalam notasi algebra

y = 11.714 x + 1727,54

analisis keputusan

Untuk membuat keputusan sama ada menerima persamaan regresi linear secukupnya menggunakan pekali korelasi berganda (CMC) dan keazaman serta ujian dan ujian-t Fisher. Dalam jadual "Excel" regresi dengan keputusan yang mereka bertindak di bawah nama yang banyak R, R-Square, F-t-statistik dan statistik, masing-masing.

KMC R membolehkan untuk menganggarkan hubungan kebarangkalian keakraban di antara pembolehubah bebas dan bergantung. nilai yang tinggi menunjukkan sambungan yang cukup kuat antara "Bilangan bulan" yang berubah-ubah dan "N Harga produk dalam Rubles setiap 1 tan." Walau bagaimanapun, sifat hubungan ini tidak diketahui.

Kuasa dua pekali penentuan R ² (RI) adalah ciri berangka bahagian daripada jumlah berselerak dan menunjukkan berselerak bahagian data eksperimen, iaitu, nilai-nilai pembolehubah bersandar yang sepadan dengan persamaan regresi linear. Dalam masalah ini, nilai ini adalah 84.8%, mp. E. Statistik dengan tahap yang tinggi ketepatan diperolehi diterangkan SD.

F-statistik, juga dikenali sebagai Fisher kriteria digunakan untuk menilai kepentingan pergantungan linear atau membuktikan sebaliknya fakta hipotesis mengesahkan kewujudannya.

Nilai t-statistik (t ujian Pelajar) membantu menilai kepentingan pekali di mana-mana anggota pergantungan linear tidak diketahui percuma. Jika nilai ujian-t> t _cr, hipotesis tidak penting persamaan linear istilah percuma ditolak.

Dalam masalah ini bagi tempoh percuma melalui instrumen "Excel" didapati bahawa t = 169,20903, dan p = 2,89E-12, t. E. Ada kebarangkalian sifar yang setia akan ditolak hipotesis tidak penting istilah percuma. Untuk pekali diketahui pada t = 5,79405, dan p = 0,001158. Dalam erti kata lain, kebarangkalian bahawa hipotesis betul ditolak akan tidak bererti pekali untuk yang tidak diketahui, ialah 0.12%.

Oleh itu, ia boleh dikatakan bahawa yang diperolehi persamaan regresi linear secukupnya.

Masalah kebijaksanaan membeli saham

regresi berganda telah dilakukan dalam Excel menggunakan "Analisis Data" alat yang sama. Mempertimbangkan permohonan tertentu.

Panduan syarikat «NNN» perlu membuat keputusan sama ada untuk membeli 20% saham JSC «MMM». harga pakej (SP) adalah 70 juta dolar Amerika. Pakar «NNN» mengumpul data bagi urusniaga yang sama. Ia telah memutuskan untuk menilai nilai saham di parameter seperti, dinyatakan dalam berjuta-juta dolar Amerika, seperti:

• pemiutang (VK);
• jumlah perolehan tahunan (VO);
• belum terima (VD);
• Nilai harta yang (SOF).

Di samping itu, menggunakan hutang gaji perusahaan (V3 U) dalam beribu-ribu dolar Amerika.

Pemproses jadual keputusan cara Excel

Pertama anda perlu membuat jadual data input. Ia adalah seperti berikut:

seterusnya:

• panggilan kotak "analisis data";
• dipilih "Regression" seksyen;
• tingkap "Input selang pelbagai Y» ditadbir nilai pembolehubah bersandar dari ruangan G;
• klik pada ikon dengan anak panah merah di sebelah kanan tingkap "Input X selang» dan terisolasi pada pelbagai sehelai semua nilai lajur B, C, D, F.

Tanda titik "lembaran kerja New" dan klik "Ok".

Dapatkan analisis regresi untuk tugas ini.

Hasil kajian dan kesimpulan

"Kumpul" bulat daripada data dibentangkan di atas di atas meja lembaran persamaan Excel pemproses regresi:

SD = 0.103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265.844.

Dalam bentuk matematik yang lebih biasa ia boleh ditulis sebagai:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265.844

Data untuk «MMM» JSC dibentangkan dalam jadual di bawah:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

Menggantikan mereka ke dalam persamaan regresi, memperoleh angka 64.720.000 dolar Amerika. Ini bermakna bahawa saham JSC «MMM» tidak perlu membeli, kerana kos mereka agak terlalu mahal pada 70 juta dolar Amerika.

Seperti yang anda lihat, penggunaan spreadsheet "Excel" dan persamaan regresi yang dibenarkan untuk membuat keputusan dimaklumkan mengenai kebijaksanaan transaksi agak khusus.

Sekarang anda tahu apa regresi. Contoh untuk Excel, yang dibincangkan di atas, akan membantu anda dalam menyelesaikan masalah praktikal ekonometrik.