Pakej MS Excel membolehkan pembinaan persamaan regresi linear paling buat kerja dengan cepat. Adalah penting untuk memahami cara mentafsir keputusan.

Memerlukan alat tambah untuk berfungsi Pakej analisis, yang mesti didayakan dalam item menu Perkhidmatan\Tambahan

Dalam Excel 2007, untuk mendayakan Pek Analisis, klik Pergi ke Sekat Pilihan Excel dengan menekan butang di sebelah kiri sudut atas, dan kemudian butang Pilihan Excel» di bahagian bawah tetingkap:

Untuk membina model regresi, pilih item Perkhidmatan\Analisis Data\Regression. (Dalam Excel 2007, mod ini berada dalam Data/Analisis Data/Regression). Kotak dialog akan muncul yang perlu diisi:

1) Selang input Y¾ mengandungi pautan ke sel yang mengandungi nilai atribut yang terhasil y. Nilai mestilah dalam lajur;

2) Selang input X¾ mengandungi pautan ke sel yang mengandungi nilai faktor. Nilai mestilah dalam lajur;

3) Tanda tangan Tag tetapkan jika sel pertama mengandungi teks penerangan (label data);

4) Tahap kebolehpercayaan¾ ialah tahap keyakinan, yang diandaikan 95% secara lalai. Jika nilai ini tidak sesuai dengan anda, maka anda perlu mendayakan ciri ini dan masukkan nilai yang diperlukan;

5) Tanda tangan Pemalar sifar disertakan jika perlu untuk membina persamaan di mana pembolehubah bebas ;

6) Pilihan keluaran menentukan di mana keputusan harus diletakkan. Mod binaan lalai Lembaran kerja baharu;

7) Sekat Kekal membolehkan anda memasukkan keluaran sisa dan pembinaan grafnya.

Hasilnya ialah maklumat yang mengandungi semua maklumat yang diperlukan dan dikumpulkan kepada tiga blok: Statistik regresi , Analisis varians , Pengeluaran baki. Mari kita pertimbangkan mereka dengan lebih terperinci.

1. Statistik regresi:

pelbagai R ditakrifkan oleh formula ( Pekali korelasi Pearson);

R (pekali penentuan);

Dinormalisasi R-persegi dikira dengan formula (digunakan untuk regresi berganda);

kesalahan biasa S dikira dengan formula ;

Pemerhatian ¾ ialah jumlah data n.

2. Analisis varians, baris Regresi:

Parameter df sama m(bilangan set faktor x);

Parameter SS ditentukan oleh formula;

Parameter CIK ditentukan oleh formula;

Perangkaan F ditentukan oleh formula;

Kepentingan F. Jika nombor yang terhasil melebihi , maka hipotesis diterima (tiada hubungan linear), jika tidak, hipotesis diterima (ada hubungan linear).

3. Analisis varians, baris Baki:

Parameter df sama ;

Parameter SS ditentukan oleh formula ;

Parameter CIK ditentukan oleh formula.

4. Analisis varians, baris Jumlah mengandungi jumlah dua lajur pertama.

5. Analisis varians, baris persimpangan Y mengandungi nilai pekali , ralat piawai dan t-statistik.

P-nilai ¾ ialah nilai aras keertian yang sepadan dengan yang dikira t- ahli perangkaan. Ditentukan oleh PELAJAR( t-statistik; ). Sekiranya P-nilai melebihi , maka pembolehubah yang sepadan adalah tidak signifikan secara statistik dan boleh dikecualikan daripada model.

bawah 95% dan 95% teratas¾ ialah had bawah dan atas 95 peratus selang keyakinan untuk pekali persamaan teori regresi linear. Jika dalam kemasukan data blok nilai tahap keyakinan ditinggalkan secara lalai, maka dua lajur terakhir akan menduplikasi yang sebelumnya. Jika pengguna telah memasukkan nilai keyakinan tersuai, maka dua lajur terakhir mengandungi nilai sempadan bawah dan atas untuk tahap keyakinan yang ditentukan.

6. Analisis varians, baris mengandungi nilai pekali, ralat piawai, t-ahli statistik, P-nilai dan selang keyakinan untuk .

7. Sekat Pengeluaran baki mengandungi nilai-nilai yang diramalkan y(dalam tatatanda kami ialah ) dan baki .

28 Okt

Selamat petang, pembaca blog yang dikasihi! Hari ini kita akan bercakap tentang regresi bukan linear. Penyelesaian regresi linear boleh lihat di SINI.

Kaedah ini digunakan terutamanya dalam pemodelan ekonomi dan ramalan. Tujuannya adalah untuk memerhati dan mengenal pasti hubungan antara dua penunjuk.

Jenis utama regresi bukan linear ialah:

• polinomial (kuadrat, kubik);
• hiperbola;
• kuasa;
• demonstrasi;
• logaritma.

Pelbagai kombinasi juga boleh digunakan. Contohnya, untuk analisis siri masa dalam perbankan, insurans, kajian demografi, keluk Gompzer digunakan, iaitu sejenis regresi logaritma.

Dalam peramalan menggunakan regresi bukan linear, perkara utama adalah untuk mengetahui pekali korelasi, yang akan menunjukkan kepada kita sama ada terdapat hubungan rapat madu dengan dua parameter atau tidak. Sebagai peraturan, jika pekali korelasi hampir dengan 1, maka terdapat sambungan, dan ramalan akan agak tepat. satu lagi elemen penting regresi bukan linear ialah min ralat relatif (TAPI ) jika ia berada dalam selang waktu<8…10%, значит модель достаточно точна.

Mengenai ini, mungkin, kita akan menyelesaikan blok teori dan beralih kepada pengiraan praktikal.

Kami mempunyai jadual jualan kereta untuk tempoh 15 tahun (mari kita nyatakan ia sebagai X), bilangan langkah pengukuran akan menjadi hujah n, kita juga mempunyai hasil untuk tempoh ini (mari kita nyatakan ia Y), kita perlu meramalkan berapakah pendapatan pada masa hadapan. Mari bina jadual berikut:

Untuk kajian, kita perlu menyelesaikan persamaan (pergantungan Y pada X): y=ax 2 +bx+c+e. Ini ialah regresi kuadratik pasangan. Dalam kes ini, kami menggunakan kaedah kuasa dua terkecil untuk mengetahui hujah yang tidak diketahui - a, b, c. Ia akan membawa kepada sistem persamaan algebra dalam bentuk:

Untuk menyelesaikan sistem ini, kami menggunakan, sebagai contoh, kaedah Cramer. Kami melihat bahawa jumlah yang termasuk dalam sistem adalah pekali yang tidak diketahui. Untuk mengiranya, kami menambah beberapa lajur pada jadual (D, E, F, G, H) dan menandatanganinya mengikut maksud pengiraan - dalam lajur D kita kuasa dua x, dalam E ke dalam kubus, dalam F ke Kuasa ke-4, dalam G kita darabkan penunjuk x dan y, dalam H kita kuasa dua x dan darab dengan y.

Ia akan menghasilkan jadual borang yang diisi dengan yang diperlukan untuk menyelesaikan persamaan.

Mari kita bentuk matriks A sistem yang terdiri daripada pekali untuk yang tidak diketahui di sebelah kiri persamaan. Mari letakkannya dalam sel A22 dan panggilnya " A=". Kami mengikuti sistem persamaan yang telah kami pilih untuk menyelesaikan regresi.

Iaitu, dalam sel B21 kita mesti meletakkan jumlah lajur di mana kita menaikkan penunjuk X ke kuasa keempat - F17. Mari kita rujuk sel - "=F17". Seterusnya, kita memerlukan jumlah lajur di mana X dikubus - E17, kemudian kita pergi dengan ketat mengikut sistem. Oleh itu, kita perlu mengisi keseluruhan matriks.

Selaras dengan algoritma Cramer, kami akan mengumpul matriks A1, serupa dengan A, di mana bukannya unsur-unsur lajur pertama, unsur-unsur bahagian kanan persamaan sistem harus diletakkan. Iaitu, jumlah lajur X kuasa dua kali Y, jumlah lajur XY, dan jumlah lajur Y.

Kita juga memerlukan dua lagi matriks - mari kita panggil mereka A2 dan A3 di mana lajur kedua dan ketiga akan terdiri daripada pekali sisi kanan persamaan. Gambar akan jadi begini.

Mengikuti algoritma yang dipilih, kita perlu mengira nilai-nilai penentu (penentu, D) matriks yang diperolehi. Jom guna formula MOPRED. Keputusan akan diletakkan dalam sel J21:K24.

Kami akan mengira pekali persamaan mengikut Cramer dalam sel yang bertentangan dengan penentu yang sepadan mengikut formula: a(dalam sel M22) - "=K22/K21"; b(dalam sel M23) — "=K23/K21"; Dengan(dalam sel M24) - "=K24 / K21".

Kami mendapat pasangan persamaan regresi kuadratik yang kami kehendaki:

y=-0.074x2 +2.151x+6.523

Mari kita anggarkan ketepatan hubungan linear dengan indeks korelasi.

Untuk mengira, tambah lajur J tambahan pada jadual (mari namakannya y*). Pengiraan adalah seperti berikut (mengikut persamaan regresi yang kami terima) - "=$m$22*B2*B2+$M$23*B2+$M$24". Mari letakkan dalam sel J2. Apa yang tinggal ialah menyeret penanda autoisi ke bawah ke sel J16.

Untuk mengira jumlah (purata Y-Y) 2, tambah lajur K dan L pada jadual dengan formula yang sepadan. Kami mengira purata untuk lajur Y menggunakan fungsi AVERAGE.

Dalam sel K25 kami meletakkan formula untuk mengira indeks korelasi - "=ROOT(1-(K17/L17))".

Kami melihat bahawa nilai 0.959 adalah sangat hampir dengan 1, yang bermaksud terdapat hubungan bukan linear yang rapat antara jualan dan tahun.

Ia kekal untuk menilai kualiti pemasangan persamaan regresi kuadratik yang diperolehi (indeks penentuan). Ia dikira dengan formula kuasa dua indeks korelasi. Iaitu, formula dalam sel K26 akan menjadi sangat mudah - "=K25*K25".

Pekali 0.920 menghampiri 1, yang menunjukkan kesesuaian berkualiti tinggi.

Langkah terakhir ialah mengira ralat relatif. Mari tambah lajur dan masukkan formula di sana: “=ABS((C2-J2)/C2), ABS — modul, nilai mutlak. Mari seret penanda ke bawah dan dalam sel M18 kita akan memaparkan nilai purata (PURATA), tetapkan format peratusan kepada sel. Keputusan yang diperoleh - 7.79% berada dalam nilai ralat yang boleh diterima<8…10%. Значит вычисления достаточно точны.

Jika perlu, kita boleh membina graf berdasarkan nilai yang diperolehi.

Contoh fail dilampirkan - LINK!

Kategori:// bertarikh 28 Oktober 2017

Analisis regresi adalah salah satu kaedah penyelidikan statistik yang paling popular. Ia boleh digunakan untuk menentukan tahap pengaruh pembolehubah bebas terhadap pembolehubah bersandar. Kefungsian Microsoft Excel mempunyai alat yang direka untuk menjalankan analisis jenis ini. Mari kita lihat apa itu dan cara menggunakannya.

Menyambung pakej analisis

Tetapi, untuk menggunakan fungsi yang membolehkan anda menjalankan analisis regresi, pertama sekali, anda perlu mengaktifkan Pakej Analisis. Hanya kemudian alat yang diperlukan untuk prosedur ini akan muncul pada reben Excel.

1. Beralih ke tab "Fail".
2. Pergi ke bahagian "Tetapan".
3. Tetingkap Excel Options dibuka. Pergi ke subseksyen "Tambahan".
4. Di bahagian paling bawah tetingkap yang terbuka, kami menyusun semula suis dalam blok "Pengurusan" kepada kedudukan "Tambah-masuk Excel", jika ia berada dalam kedudukan yang berbeza. Klik pada butang "Pergi".
5. Tetingkap tambahan Excel dibuka. Tandai kotak di sebelah "Pakej Analisis". Klik pada butang "OK".

Sekarang, apabila kita pergi ke tab "Data", pada reben dalam blok alat "Analisis", kita akan melihat butang baharu - "Analisis Data".

Jenis analisis regresi

Terdapat beberapa jenis regresi:

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

Kami akan bercakap dengan lebih terperinci tentang pelaksanaan jenis analisis regresi terakhir dalam Excel nanti.

Regresi Linear dalam Excel

Di bawah, sebagai contoh, ialah jadual yang menunjukkan purata suhu udara harian di jalan dan bilangan pelanggan kedai untuk hari bekerja yang sepadan. Mari kita ketahui dengan bantuan analisis regresi dengan tepat bagaimana keadaan cuaca dalam bentuk suhu udara boleh menjejaskan kehadiran pertubuhan runcit.

Persamaan regresi linear am kelihatan seperti ini: Y = a0 + a1x1 + ... + axk. Dalam formula ini, Y bermaksud pembolehubah di mana kita cuba mengkaji pengaruh faktor. Dalam kes kami, ini ialah bilangan pembeli. Nilai x ialah pelbagai faktor yang mempengaruhi pembolehubah. Parameter a ialah pekali regresi. Iaitu, mereka menentukan kepentingan faktor tertentu. Indeks k menandakan jumlah bilangan faktor yang sama ini.

Analisis keputusan analisis

Hasil analisis regresi dipaparkan dalam bentuk jadual di tempat yang ditentukan dalam tetapan.

Salah satu penunjuk utama ialah R-square. Ia menunjukkan kualiti model. Dalam kes kami, pekali ini ialah 0.705 atau kira-kira 70.5%. Ini adalah tahap kualiti yang boleh diterima. Hubungan kurang daripada 0.5 adalah buruk.

Satu lagi penunjuk penting terletak di dalam sel di persimpangan garisan "Y-intersection" dan lajur "Pekali". Di sini ditunjukkan nilai Y yang akan ada, dan dalam kes kami, ini ialah bilangan pembeli, dengan semua faktor lain bersamaan dengan sifar. Dalam jadual ini, nilai ini ialah 58.04.

Nilai di persimpangan lajur "Pembolehubah X1" dan "Pekali" menunjukkan tahap pergantungan Y pada X. Dalam kes kami, ini ialah tahap pergantungan bilangan pelanggan kedai pada suhu. Pekali 1.31 dianggap sebagai penunjuk pengaruh yang agak tinggi.

Seperti yang anda lihat, agak mudah untuk membuat jadual analisis regresi menggunakan Microsoft Excel. Tetapi, hanya orang yang terlatih boleh bekerja dengan data yang diperoleh pada output, dan memahami intipatinya.

Kami gembira kami dapat membantu anda menyelesaikan isu tersebut.

Tanya soalan anda dalam komen, menerangkan secara terperinci intipati masalah itu. Pakar kami akan cuba menjawab secepat mungkin.

Adakah artikel ini membantu anda?

Kaedah regresi linear membolehkan kita menerangkan garis lurus yang paling sesuai dengan siri pasangan tertib (x, y). Persamaan untuk garis lurus, yang dikenali sebagai persamaan linear, diberikan di bawah:

ŷ ialah nilai jangkaan y untuk nilai x tertentu,

x - pembolehubah bebas,

a - ruas pada paksi-y untuk garis lurus,

b ialah kecerunan garis lurus.

Dalam rajah di bawah, konsep ini diwakili secara grafik:

Rajah di atas menunjukkan garis yang diterangkan oleh persamaan ŷ =2+0.5x. Segmen pada paksi-y ialah titik persilangan garis dengan paksi-y; dalam kes kita, a = 2. Kecerunan garisan, b, nisbah kenaikan garisan kepada panjang garisan, mempunyai nilai 0.5. Cerun positif bermakna garisan naik dari kiri ke kanan. Jika b = 0, garisnya adalah mendatar, yang bermaksud tiada hubungan antara pembolehubah bersandar dan bebas. Dengan kata lain, menukar nilai x tidak menjejaskan nilai y.

ŷ dan y sering keliru. Graf menunjukkan 6 pasang tertib titik dan garis, mengikut persamaan yang diberikan

Angka ini menunjukkan titik yang sepadan dengan pasangan tertib x = 2 dan y = 4. Perhatikan bahawa nilai jangkaan y mengikut garis di X= 2 ialah ŷ. Kita boleh mengesahkannya dengan persamaan berikut:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

Nilai-y ialah titik sebenar, dan nilai-ŷ ialah nilai-y yang dijangka menggunakan persamaan linear untuk nilai-x tertentu.

Langkah seterusnya adalah untuk menentukan persamaan linear yang paling sepadan dengan set pasangan tertib, kami membincangkan perkara ini dalam artikel sebelumnya, di mana kami menentukan bentuk persamaan menggunakan kaedah kuasa dua terkecil.

Menggunakan Excel untuk Mentakrifkan Regresi Linear

Untuk menggunakan alat analisis regresi terbina dalam Excel, anda perlu mengaktifkan tambahan Pakej analisis. Anda boleh menemuinya dengan mengklik pada tab Fail -> Pilihan(2007+), dalam dialog yang muncul Pilihancemerlang pergi ke tab Alat tambah. Di padang Kawalan pilih alat tambahcemerlang dan klik Pergi. Dalam tetingkap yang muncul, tandai kotak di sebelah pakej analisis, klik OKEY.

Dalam tab Data dalam kumpulan Analisis butang baharu akan muncul Analisis data.

Untuk menunjukkan cara alat tambah itu berfungsi, mari gunakan data daripada artikel sebelumnya, tempat lelaki dan perempuan berkongsi meja di bilik mandi. Masukkan data untuk contoh bilik mandi kami dalam lajur A dan B helaian kosong.

Pergi ke tab data, dalam kumpulan Analisis klik Analisis data. Dalam tetingkap yang muncul Analisis data pilih Regresi seperti yang ditunjukkan dalam rajah dan klik OK.

Tetapkan parameter regresi yang diperlukan dalam tetingkap Regresi, seperti yang ditunjukkan pada gambar:

klik OKEY. Rajah di bawah menunjukkan keputusan yang diperoleh:

Keputusan ini konsisten dengan keputusan yang kami peroleh melalui pengiraan bebas dalam artikel sebelumnya.

Analisis regresi ialah kaedah penyelidikan statistik yang membolehkan anda menunjukkan pergantungan parameter pada satu atau lebih pembolehubah tidak bersandar. Pada era pra-komputer, penggunaannya agak sukar, terutamanya apabila ia melibatkan jumlah data yang besar. Hari ini, setelah mempelajari cara membina regresi dalam Excel, anda boleh menyelesaikan masalah statistik yang kompleks dalam beberapa minit sahaja. Di bawah adalah contoh khusus dari bidang ekonomi.

Jenis regresi

Konsep itu sendiri telah diperkenalkan ke dalam matematik oleh Francis Galton pada tahun 1886. Regresi berlaku:

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

Contoh 1

Pertimbangkan masalah menentukan pergantungan bilangan ahli pasukan bersara pada gaji purata di 6 perusahaan perindustrian.

Satu tugas. Di enam perusahaan, kami menganalisis purata gaji bulanan dan bilangan pekerja yang keluar atas kerelaan mereka sendiri. Dalam bentuk jadual kami mempunyai:

Untuk masalah menentukan pergantungan bilangan pekerja yang diberhentikan pada gaji purata di 6 perusahaan, model regresi mempunyai bentuk persamaan Y = a0 + a1 × 1 + ... + akxk, di mana хi adalah pembolehubah yang mempengaruhi, ai ialah pekali regresi, dan k ialah bilangan faktor.

Untuk tugas ini, Y ialah penunjuk pekerja yang keluar, dan faktor yang mempengaruhi ialah gaji, yang kami nyatakan dengan X.

Menggunakan keupayaan hamparan "Excel"

Analisis regresi dalam Excel mesti didahului dengan aplikasi fungsi terbina dalam kepada data jadual yang tersedia. Walau bagaimanapun, untuk tujuan ini, adalah lebih baik untuk menggunakan "kit Alat Analisis" tambahan yang sangat berguna. Untuk mengaktifkannya anda perlu:

• dari tab "Fail", pergi ke bahagian "Pilihan";
• dalam tetingkap yang terbuka, pilih baris "Add-ons";
• klik pada butang "Pergi" yang terletak di bahagian bawah, di sebelah kanan baris "Pengurusan";
• tandai kotak di sebelah nama "Pakej Analisis" dan sahkan tindakan anda dengan mengklik "OK".

Jika semuanya dilakukan dengan betul, butang yang dikehendaki akan muncul di sebelah kanan tab Data, yang terletak di atas lembaran kerja Excel.

Regresi Linear dalam Excel

Kini setelah kami mempunyai semua alat maya yang diperlukan untuk melakukan pengiraan ekonometrik, kami boleh mula menyelesaikan masalah kami. Untuk ini:

• klik pada butang "Analisis Data";
• dalam tetingkap yang terbuka, klik pada butang "Regression";
• dalam tab yang muncul, masukkan julat nilai untuk Y (bilangan pekerja yang berhenti) dan untuk X (gaji mereka);
• Kami mengesahkan tindakan kami dengan menekan butang "Ok".

Akibatnya, program akan mengisi helaian hamparan baharu secara automatik dengan data analisis regresi. Catatan! Excel mempunyai keupayaan untuk menetapkan lokasi yang anda sukai secara manual untuk tujuan ini. Sebagai contoh, ia boleh menjadi helaian yang sama dengan nilai Y dan X, atau buku kerja baharu yang direka khusus untuk menyimpan data tersebut.

Analisis keputusan regresi untuk R-square

Dalam Excel, data yang diperoleh semasa pemprosesan data contoh yang dipertimbangkan kelihatan seperti ini:

Pertama sekali, anda harus memberi perhatian kepada nilai R-square. Ia adalah pekali penentuan. Dalam contoh ini, R-square = 0.755 (75.5%), iaitu, parameter yang dikira model menerangkan hubungan antara parameter yang dipertimbangkan sebanyak 75.5%. Semakin tinggi nilai pekali penentuan, lebih sesuai model yang dipilih untuk tugas tertentu. Adalah dipercayai bahawa ia menggambarkan situasi sebenar dengan betul dengan nilai kuasa dua R melebihi 0.8. Jika kuasa dua R ialah tcr, maka hipotesis ketaksignifikan bagi sebutan bebas persamaan linear ditolak.

Dalam masalah yang sedang dipertimbangkan untuk ahli bebas, menggunakan alat Excel, diperolehi bahawa t = 169.20903, dan p = 2.89E-12, iaitu kita mempunyai kebarangkalian sifar bahawa hipotesis yang betul tentang tidak signifikan ahli bebas akan menjadi ditolak. Untuk pekali pada t=5.79405 tidak diketahui, dan p=0.001158. Dalam erti kata lain, kebarangkalian bahawa hipotesis yang betul tentang tidak signifikan pekali untuk yang tidak diketahui akan ditolak ialah 0.12%.

Oleh itu, boleh dikatakan bahawa persamaan regresi linear yang terhasil adalah memadai.

Masalah kesesuaian membeli satu blok saham

Regresi berbilang dalam Excel dilakukan menggunakan alat Analisis Data yang sama. Pertimbangkan masalah yang digunakan khusus.

Pengurusan NNN mesti membuat keputusan mengenai kesesuaian untuk membeli 20% kepentingan dalam MMM SA. Kos pakej (JV) ialah 70 juta dolar AS. Pakar NNN mengumpul data mengenai transaksi yang serupa. Ia telah memutuskan untuk menilai nilai blok saham mengikut parameter tersebut, dinyatakan dalam berjuta-juta dolar AS, seperti:

• akaun belum bayar (VK);
• pusing ganti tahunan (VO);
• akaun belum terima (VD);
• kos aset tetap (SOF).

Di samping itu, parameter tunggakan gaji perusahaan (V3 P) dalam ribuan dolar AS digunakan.

Penyelesaian menggunakan hamparan Excel

Pertama sekali, anda perlu membuat jadual data awal. Ia kelihatan seperti ini:

• panggil tetingkap "Analisis Data";
• pilih bahagian "Regression";
• dalam kotak "Selang input Y" masukkan julat nilai pembolehubah bersandar dari lajur G;
• klik pada ikon dengan anak panah merah di sebelah kanan tetingkap "Input interval X" dan pilih julat semua nilai dari lajur B, C, D, F pada helaian.

Pilih "Lembaran Kerja Baharu" dan klik "Ok".

Dapatkan analisis regresi untuk masalah yang diberikan.

Pemeriksaan keputusan dan kesimpulan

"Kami mengumpul" daripada data bulat yang dibentangkan di atas pada helaian hamparan Excel, persamaan regresi:

SP \u003d 0.103 * SOF + 0.541 * VO - 0.031 * VK + 0.405 * VD + 0.691 * VZP - 265.844.

Dalam bentuk matematik yang lebih biasa, ia boleh ditulis sebagai:

y = 0.103*x1 + 0.541*x2 - 0.031*x3 +0.405*x4 +0.691*x5 - 265.844

Data untuk JSC "MMM" dibentangkan dalam jadual:

Menggantikannya ke dalam persamaan regresi, mereka mendapat angka 64.72 juta dolar AS. Ini bermakna bahawa saham JSC MMM tidak boleh dibeli, kerana nilainya sebanyak 70 juta dolar AS agak berlebihan.

Seperti yang anda lihat, penggunaan hamparan Excel dan persamaan regresi memungkinkan untuk membuat keputusan termaklum mengenai kebolehlaksanaan transaksi yang sangat spesifik.

Sekarang anda tahu apa itu regresi. Contoh-contoh dalam Excel yang dibincangkan di atas akan membantu anda menyelesaikan masalah praktikal dari bidang ekonometrik.

Pakej MS Excel membolehkan anda melakukan kebanyakan kerja dengan cepat apabila membina persamaan regresi linear. Adalah penting untuk memahami cara mentafsir keputusan. Untuk membina model regresi, pilih Tools\Data Analysis\Regression (dalam Excel 2007, mod ini terletak di bahagian Data/Data Analysis/Regression). Kemudian salin keputusan yang diperoleh ke dalam blok untuk dianalisis.

Data awal:

Keputusan analisis

Sertakan dalam laporan
Pengiraan parameter persamaan regresi
Bahan teori
Persamaan regresi pada skala piawai
Pekali Korelasi Berbilang (Indeks Korelasi Berbilang)
Pekali keanjalan separa
Penilaian perbandingan pengaruh faktor yang dianalisis pada ciri berkesan (d - pekali penentuan berasingan)

Menyemak kualiti persamaan regresi yang dibina
Kepentingan pekali regresi b i (statistik-t. Ujian-t pelajar)
Kepentingan persamaan secara keseluruhan (F-statistik. Kriteria Fisher). Pekali penentuan
Kriteria F separa

Tahap keertian 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Menunjukkan pengaruh beberapa nilai (bebas, bebas) ke atas pembolehubah bersandar. Sebagai contoh, bagaimana bilangan penduduk yang aktif dari segi ekonomi bergantung pada bilangan perusahaan, gaji dan parameter lain. Atau: bagaimanakah pelaburan asing, harga tenaga, dsb. mempengaruhi tahap KDNK.

Hasil analisis membolehkan anda membuat keutamaan. Dan berdasarkan faktor utama, untuk meramalkan, merancang pembangunan kawasan keutamaan, membuat keputusan pengurusan.

Regresi berlaku:

linear (y = a + bx);

parabola (y = a + bx + cx 2);

eksponen (y = a * exp(bx));

Kuasa (y = a*x^b);

hiperbolik (y = b/x + a);

logaritma (y = b * 1n(x) + a);

eksponen (y = a * b^x).

Pertimbangkan contoh membina model regresi dalam Excel dan mentafsirkan hasilnya. Mari kita ambil jenis regresi linear.

Satu tugas. Di 6 perusahaan, purata gaji bulanan dan bilangan pekerja yang keluar dianalisis. Ia adalah perlu untuk menentukan pergantungan bilangan pekerja bersara pada gaji purata.

Model regresi linear mempunyai bentuk berikut:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Di mana a ialah pekali regresi, x ialah pembolehubah yang mempengaruhi, dan k ialah bilangan faktor.

Dalam contoh kami, Y ialah penunjuk berhenti pekerja. Faktor yang mempengaruhi ialah upah (x).

Excel mempunyai fungsi terbina dalam yang boleh digunakan untuk mengira parameter model regresi linear. Tetapi alat tambah Analysis ToolPak akan melakukannya dengan lebih pantas.

Aktifkan alat analisis yang berkuasa:

1. Klik butang "Pejabat" dan pergi ke tab "Pilihan Excel". "Tambahan".

2. Di bawah, di bawah senarai juntai bawah, dalam medan "Pengurusan" akan ada tulisan "Excel Add-in" (jika tiada, klik pada kotak semak di sebelah kanan dan pilih). Dan butang Pergi. klik.

3. Senarai alat tambah yang tersedia dibuka. Pilih "Pakej Analisis" dan klik OK.

Setelah diaktifkan, alat tambah akan tersedia di bawah tab Data.

Sekarang kita akan berurusan secara langsung dengan analisis regresi.

1. Buka menu alat Analisis Data. Pilih "Regression".

2. Menu akan dibuka untuk memilih nilai input dan pilihan output (tempat untuk memaparkan hasilnya). Dalam medan untuk data awal, kami menunjukkan julat parameter yang diterangkan (Y) dan faktor yang mempengaruhinya (X). Selebihnya mungkin selesai atau tidak.

3. Selepas mengklik OK, program akan memaparkan pengiraan pada helaian baru (anda boleh memilih selang untuk dipaparkan pada helaian semasa atau menetapkan output ke buku kerja baharu).

Pertama sekali, kami memberi perhatian kepada R-square dan pekali.

R-square ialah pekali penentuan. Dalam contoh kami, ia adalah 0.755, atau 75.5%. Ini bermakna parameter yang dikira model menjelaskan hubungan antara parameter yang dikaji sebanyak 75.5%. Lebih tinggi pekali penentuan, lebih baik model. Baik - melebihi 0.8. Lemah - kurang daripada 0.5 (analisis sedemikian hampir tidak boleh dianggap munasabah). Dalam contoh kami - "tidak buruk".

Pekali 64.1428 menunjukkan apakah Y akan menjadi jika semua pembolehubah dalam model yang dipertimbangkan adalah sama dengan 0. Iaitu, faktor lain yang tidak diterangkan dalam model juga mempengaruhi nilai parameter yang dianalisis.

Pekali -0.16285 menunjukkan berat pembolehubah X pada Y. Iaitu, purata gaji bulanan dalam model ini mempengaruhi bilangan berhenti dengan berat -0.16285 (ini adalah pengaruh yang kecil). Tanda "-" menunjukkan kesan negatif: semakin tinggi gaji, semakin kurang berhenti. Yang adil.