Analisis regresi adalah salah satu kaedah yang paling popular penyelidikan statistik. Ia boleh digunakan untuk menentukan tahap pengaruh pembolehubah tidak bersandar ke atas pembolehubah bersandar. Dalam kefungsian Microsoft Excel Terdapat alat yang direka untuk melakukan analisis jenis ini. Mari lihat apa itu dan cara menggunakannya.
Menyambung pakej analisis
Tetapi, untuk menggunakan fungsi yang membolehkan anda melakukan analisis regresi, anda perlu mengaktifkan Pakej Analisis terlebih dahulu. Hanya kemudian alat yang diperlukan untuk prosedur ini akan muncul pada reben Excel.
- Beralih ke tab "Fail".
- Pergi ke bahagian "Tetapan".
- Tetingkap Excel Options dibuka. Pergi ke subseksyen "Tambahan".
- Di bahagian paling bawah tetingkap yang terbuka, gerakkan suis dalam blok "Kawalan" ke kedudukan " Tambahan Excel“jika ia berada dalam kedudukan yang berbeza. Klik pada butang "Pergi".
- Tetingkap tambahan Excel yang tersedia 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;
- penenang;
- logaritma;
- eksponen;
- demonstratif;
- hiperbola;
- regresi linear.
Kami akan bercakap dengan lebih terperinci tentang melaksanakan jenis analisis regresi terakhir dalam Excel nanti.
Regresi Linear dalam Excel
Di bawah, sebagai contoh, ialah jadual yang menunjukkan purata suhu udara harian di luar dan bilangan pelanggan kedai untuk hari bekerja yang sepadan. Mari kita ketahui menggunakan analisis regresi dengan tepat bagaimana cuaca dalam bentuk suhu udara boleh menjejaskan kehadiran sesebuah pertubuhan runcit.
Persamaan regresi linear am adalah seperti berikut: Y = a0 + a1x1 +…+ akhk. 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, merekalah yang menentukan kepentingan sesuatu faktor. 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-kuadrat. Ia menunjukkan kualiti model. Dalam kes kami, pekali ini ialah 0.705 atau kira-kira 70.5%. Ini adalah tahap kualiti yang boleh diterima. Kebergantungan kurang daripada 0.5 adalah buruk.
Satu lagi penunjuk penting terletak di dalam sel di persimpangan baris pintasan Y dan lajur Pekali. Ini menunjukkan nilai Y yang akan ada, dan dalam kes kami, ini ialah bilangan pembeli, dengan semua faktor lain sama dengan sifar. Dalam jadual ini nilai yang diberi bersamaan dengan 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 agak kadar tinggi pengaruh.
Seperti yang kita lihat, menggunakan program Microsoft Excel agak mudah untuk membuat jadual analisis regresi. Tetapi hanya orang yang terlatih boleh bekerja dengan data output dan memahami intipatinya.
Kami gembira kerana kami dapat membantu anda menyelesaikan masalah tersebut.
Tanya soalan anda dalam ulasan, menerangkan intipati masalah secara terperinci. Pakar kami akan cuba menjawab secepat mungkin.
Adakah artikel ini membantu anda?
Kaedah regresi linear membolehkan kami menerangkan garis lurus yang paling sesuai dengan siri pasangan tertib (x, y). Persamaan untuk garis lurus, yang dikenali sebagai persamaan linear, diberikan di bawah:
ŷ - nilai jangkaan y pada tetapkan nilai X,
x - pembolehubah bebas,
a - ruas pada paksi-y untuk garis lurus,
b ialah kecerunan garis lurus.
Rajah di bawah menggambarkan konsep ini secara grafik:
Rajah di atas menunjukkan garisan yang diterangkan oleh persamaan ŷ =2+0.5x. Pintasan-y ialah titik di mana garis itu bersilang 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 mewakili titik sebenar dan nilai ŷ ialah nilai y yang dijangka menggunakan persamaan linear untuk nilai x yang diberikan.
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 mesti mengaktifkan tambahan Pakej analisis. Anda boleh menemuinya dengan mengklik pada tab Fail -> Pilihan(2007+), dalam kotak dialog yang muncul PilihanExcel pergi ke tab Alat tambah. Di padang Kawalan pilih Alat tambahExcel dan klik Pergi. Dalam tetingkap yang muncul, tandai kotak di sebelah Pakej analisis, klik OKEY.
Dalam tab Data dalam kumpulan Analisis akan muncul butang baru 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 daripada contoh tab 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 yang kami peroleh dengan melakukan pengiraan kami sendiri dalam artikel sebelumnya.
Analisis regresi ialah kaedah statistik penyelidikan yang membolehkan anda menunjukkan pergantungan parameter tertentu pada satu atau lebih pembolehubah tidak bersandar. Pada era pra-komputer, penggunaannya agak sukar, terutamanya apabila ia datang ke jumlah yang besar data. 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 daripada bidang ekonomi.
Jenis Regresi
Konsep ini sendiri telah diperkenalkan ke dalam matematik oleh Francis Galton pada tahun 1886. Regresi berlaku:
- linear;
- parabola;
- penenang;
- eksponen;
- hiperbola;
- demonstratif;
- logaritma.
Contoh 1
Mari kita pertimbangkan masalah menentukan pergantungan bilangan ahli pasukan yang berhenti pada gaji purata di 6 perusahaan perindustrian.
Tugasan. Di enam perusahaan, purata gaji bulanan dan bilangan pekerja yang berhenti kerana sesuka hati. Dalam bentuk jadual kami mempunyai:
Untuk tugas menentukan pergantungan bilangan pekerja yang berhenti pada gaji purata di 6 perusahaan, model regresi mempunyai bentuk persamaan Y = a0 + a1×1 +…+аkxk, di mana хi adalah pembolehubah yang mempengaruhi, ai ialah pekali regresi, dan k ialah bilangan faktor.
Untuk tugasan ini, Y ialah penunjuk pekerja yang berhenti kerja, dan faktor yang mempengaruhi ialah gaji, yang kami nyatakan dengan X.
Menggunakan keupayaan pemproses hamparan Excel
Analisis regresi dalam Excel mesti didahului dengan menggunakan fungsi terbina dalam pada data jadual sedia ada. Walau bagaimanapun, untuk tujuan ini adalah lebih baik untuk menggunakan alat tambah "Pek Analisis" 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 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, di sebelah kanan tab "Data", yang terletak di atas lembaran kerja "Excel", anda akan melihat butang yang dikehendaki.
Regresi Linear dalam Excel
Kini setelah kami mempunyai semua alat maya yang diperlukan untuk menjalankan 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 diisi secara automatik daun baru pemproses meja data analisis regresi. Catatan! Excel membolehkan anda menetapkan lokasi yang anda sukai secara manual untuk tujuan ini. Sebagai contoh, ia boleh menjadi helaian yang sama di mana nilai Y dan X terletak, atau malah sebuah buku baru, direka khusus untuk menyimpan data sedemikian.
Analisis keputusan regresi untuk R-kuadrat
DALAM data Excel yang diperoleh semasa pemprosesan data contoh yang sedang dipertimbangkan mempunyai bentuk:
Pertama sekali, anda harus memberi perhatian kepada nilai R-kuadrat. Ia mewakili pekali penentuan. DALAM dalam contoh ini R-square = 0.755 (75.5%), iaitu, parameter yang dikira model menerangkan pergantungan antara parameter yang dipertimbangkan sebanyak 75.5%. Semakin tinggi nilai pekali penentuan, model yang dipilih dianggap lebih sesuai untuk tugas tertentu. Ia dianggap untuk menerangkan dengan betul keadaan sebenar apabila nilai R-square melebihi 0.8. Jika R-kuasa dua ialah tcr, maka hipotesis tentang ketidaksignifikan sebutan bebas bagi persamaan linear ditolak.
Dalam masalah yang sedang dipertimbangkan untuk istilah bebas, menggunakan alat Excel, didapati bahawa t = 169.20903, dan p = 2.89E-12, iaitu, kita mempunyai kebarangkalian sifar bahawa hipotesis yang betul tentang ketidaksignifikan istilah bebas akan ditolak . Untuk pekali untuk t=5.79405 yang 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 kebolehlaksanaan pembelian blok saham
Regresi berbilang dalam Excel dilakukan menggunakan alat Analisis Data yang sama. Mari kita pertimbangkan masalah aplikasi tertentu.
Pengurusan syarikat NNN mesti membuat keputusan mengenai kesesuaian untuk membeli 20% kepentingan dalam MMM JSC. Kos pakej (SP) ialah 70 juta dolar AS. Pakar NNN telah 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);
- volum pusing ganti tahunan (VO);
- akaun belum terima (VD);
- kos aset tetap (COF).
Di samping itu, parameter tunggakan gaji perusahaan (V3 P) dalam ribuan dolar AS digunakan.
Penyelesaian menggunakan pemproses hamparan Excel
Pertama sekali, anda perlu membuat jadual data sumber. 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 anak panah merah di sebelah kanan tetingkap "Julat Input X" dan serlahkan pada helaian julat semua nilai daripada lajur B,C,D,F.
Tandai item "Lembaran kerja baharu" dan klik "Ok".
Dapatkan analisis regresi untuk masalah tertentu.
Kajian keputusan dan kesimpulan
Kami "mengumpul" daripada data bulat yang dibentangkan di atas pada helaian jadual Pemproses Excel, persamaan regresi:
SP = 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 MMM JSC dibentangkan dalam jadual:
Menggantikannya ke dalam persamaan regresi, kita mendapat angka 64.72 juta dolar AS. Ini bermakna bahawa saham MMM JSC tidak berbaloi untuk dibeli, kerana nilainya sebanyak 70 juta dolar AS agak melambung.
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 Excel yang dibincangkan di atas akan membantu anda membuat keputusan masalah praktikal daripada bidang ekonometrik.
Analisis regresi ialah kaedah penyelidikan statistik yang membolehkan anda menunjukkan pergantungan parameter tertentu pada satu atau lebih pembolehubah bebas. Dalam 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 ini sendiri telah diperkenalkan ke dalam matematik pada tahun 1886. Regresi berlaku:
- linear;
- parabola;
- penenang;
- eksponen;
- hiperbola;
- demonstratif;
- logaritma.
Contoh 1
Mari kita pertimbangkan masalah menentukan pergantungan bilangan ahli pasukan yang berhenti pada gaji purata di 6 perusahaan perindustrian.
Tugasan. Di enam perusahaan, purata gaji bulanan dan bilangan pekerja yang berhenti secara sukarela telah dianalisis. Dalam bentuk jadual kami mempunyai:
Bilangan orang yang berhenti | Gaji |
||
30,000 rubel |
|||
35,000 rubel |
|||
40,000 rubel |
|||
45,000 rubel |
|||
50,000 rubel |
|||
55,000 rubel |
|||
60,000 rubel |
Untuk tugas menentukan kebergantungan bilangan pekerja yang berhenti kerja pada purata gaji di 6 perusahaan, model regresi mempunyai bentuk persamaan Y = a 0 + a 1 x 1 +...+a k x k, di mana x i ialah pembolehubah yang mempengaruhi, a i ialah pekali regresi, dan k ialah bilangan faktor.
Untuk masalah ini, Y ialah penunjuk berhenti pekerja, dan faktor yang mempengaruhi ialah gaji, yang kami nyatakan dengan X.
Menggunakan keupayaan pemproses hamparan Excel
Analisis regresi dalam Excel mesti didahului dengan menggunakan fungsi terbina dalam pada data jadual sedia ada. Walau bagaimanapun, untuk tujuan ini adalah lebih baik untuk menggunakan alat tambah "Pek Analisis" 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 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 diperlukan akan muncul di sebelah kanan tab "Data", yang terletak di atas lembaran kerja Excel.
dalam Excel
Kini setelah kami mempunyai semua alat maya yang diperlukan untuk menjalankan 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 hamparan baharu secara automatik dengan data analisis regresi. Catatan! Excel membolehkan anda menetapkan lokasi yang anda sukai secara manual untuk tujuan ini. Sebagai contoh, ini boleh menjadi helaian yang sama di mana nilai Y dan X berada, atau buku kerja baharu yang direka khusus untuk menyimpan data sedemikian.
Analisis keputusan regresi untuk R-kuadrat
Dalam Excel, data yang diperoleh semasa pemprosesan data dalam contoh yang sedang dipertimbangkan mempunyai bentuk:
Pertama sekali, anda harus memberi perhatian kepada nilai R-kuadrat. Ia mewakili 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, semakin sesuai model yang dipilih untuk tugas tertentu. Ia dianggap untuk menerangkan dengan betul keadaan sebenar apabila nilai R-square melebihi 0.8. Jika R-kuasa dua<0,5, то такой анализа регрессии в Excel нельзя считать резонным.
Analisis Odds
Nombor 64.1428 menunjukkan nilai Y jika semua pembolehubah xi dalam model yang kita pertimbangkan ditetapkan semula kepada sifar. Dengan kata lain, boleh dikatakan bahawa nilai parameter yang dianalisis juga dipengaruhi oleh faktor lain yang tidak diterangkan dalam model tertentu.
Pekali seterusnya -0.16285, terletak dalam sel B18, menunjukkan berat pengaruh pembolehubah X pada Y. Ini bermakna purata gaji bulanan pekerja dalam model yang dipertimbangkan mempengaruhi bilangan berhenti dengan berat -0.16285, i.e. tahap pengaruhnya sangat kecil. Tanda "-" menunjukkan bahawa pekali adalah negatif. Ini jelas, kerana semua orang tahu bahawa semakin tinggi gaji di perusahaan, semakin sedikit orang yang menyatakan keinginan untuk menamatkan kontrak pekerjaan atau berhenti.
Regresi berganda
Istilah ini merujuk kepada persamaan hubungan dengan beberapa pembolehubah bebas dalam bentuk:
y=f(x 1 +x 2 +…x m) + ε, dengan y ialah ciri paduan (pembolehubah bersandar), dan x 1, x 2,…x m ialah ciri faktor (pembolehubah bebas).
Anggaran Parameter
Untuk regresi berganda(MR) ia dijalankan menggunakan kaedah kuasa dua terkecil (OLS). Untuk persamaan linear dalam bentuk Y = a + b 1 x 1 +…+b m x m + ε kita membina sistem persamaan normal (lihat di bawah)
Untuk memahami prinsip kaedah, pertimbangkan kes dua faktor. Kemudian kita mempunyai situasi yang diterangkan oleh formula
Dari sini kita dapat:
di mana σ ialah varians ciri sepadan yang ditunjukkan dalam indeks.
OLS boleh digunakan pada persamaan MR pada skala piawai. Dalam kes ini kita mendapat persamaan:
di mana t y, t x 1, … t xm ialah pembolehubah piawai, yang mana nilai purata adalah sama dengan 0; β i ialah pekali regresi piawai, dan sisihan piawai ialah 1.
Sila ambil perhatian bahawa semua β i dalam kes ini dinyatakan sebagai normal dan berpusat, oleh itu perbandingan mereka antara satu sama lain dianggap betul dan boleh diterima. Di samping itu, adalah kebiasaan untuk menyaring faktor dengan membuang faktor yang mempunyai nilai βi terendah.
Masalah Menggunakan Persamaan Regresi Linear
Katakan kita mempunyai jadual dinamik harga untuk produk tertentu N sepanjang 8 bulan yang lalu. Ia adalah perlu untuk membuat keputusan mengenai kesesuaian untuk membeli satu kumpulan pada harga 1850 rubel/t.
nombor bulan | nama bulan | harga produk N |
|
1750 rubel setiap tan |
|||
1755 rubel setiap tan |
|||
1767 rubel setiap tan |
|||
1760 rubel setiap tan |
|||
1770 rubel setiap tan |
|||
1790 rubel setiap tan |
|||
1810 rubel setiap tan |
|||
1840 rubel setiap tan |
|||
Untuk menyelesaikan masalah ini dalam pemproses hamparan Excel, anda perlu menggunakan alat "Analisis Data", yang telah diketahui daripada contoh yang dibentangkan di atas. Seterusnya, pilih bahagian "Regression" dan tetapkan parameter. Perlu diingat bahawa dalam medan "Selang input Y" julat nilai mesti dimasukkan untuk pembolehubah bersandar (dalam kes ini, harga barang dalam bulan tertentu dalam setahun), dan dalam "Selang input X" - untuk pembolehubah bebas (nombor bulan). Sahkan tindakan dengan mengklik "Ok". Pada helaian baharu (jika dinyatakan demikian) kami memperoleh data untuk regresi.
Menggunakannya, kami membina persamaan linear dalam bentuk y=ax+b, di mana parameter a dan b ialah pekali garisan dengan nama nombor bulan dan pekali dan garisan "Y-simpangan" dari helaian dengan keputusan analisis regresi. Oleh itu, persamaan regresi linear (LR) untuk tugasan 3 ditulis sebagai:
Harga produk N = 11.714* nombor bulan + 1727.54.
atau dalam tatatanda algebra
y = 11.714 x + 1727.54
Analisis keputusan
Untuk memutuskan sama ada persamaan regresi linear yang terhasil adalah mencukupi, pekali korelasi berganda (MCC) dan penentuan digunakan, serta ujian Fisher dan ujian t Pelajar. Dalam hamparan Excel dengan hasil regresi, ia dipanggil berbilang R, R-kuadrat, F-statistik dan t-statistik, masing-masing.
KMC R memungkinkan untuk menilai keakraban hubungan kebarangkalian antara pembolehubah bebas dan bersandar. Nilainya yang tinggi menunjukkan hubungan yang agak kuat antara pembolehubah "Bilangan bulan" dan "Harga produk N dalam rubel setiap 1 tan". Walau bagaimanapun, sifat hubungan ini masih tidak diketahui.
Kuasa dua pekali penentuan R2 (RI) ialah ciri berangka perkadaran jumlah serakan dan menunjukkan serakan bahagian mana data eksperimen, i.e. nilai pembolehubah bersandar sepadan dengan persamaan regresi linear. Dalam masalah yang sedang dipertimbangkan, nilai ini bersamaan dengan 84.8%, iaitu, data statistik diterangkan dengan tahap ketepatan yang tinggi oleh SD yang terhasil.
Statistik F, juga dipanggil ujian Fisher, digunakan untuk menilai kepentingan pergantungan linear, menyangkal atau mengesahkan hipotesis kewujudannya.
(Ujian pelajar) membantu menilai kepentingan pekali dengan istilah yang tidak diketahui atau bebas bagi hubungan linear. Jika nilai ujian-t > tcr, maka hipotesis tentang ketidaksignifikan sebutan bebas persamaan linear ditolak.
Dalam masalah yang sedang dipertimbangkan untuk istilah bebas, menggunakan alat Excel, didapati bahawa t = 169.20903, dan p = 2.89E-12, iaitu, kita mempunyai kebarangkalian sifar bahawa hipotesis yang betul tentang ketidaksignifikan istilah bebas akan ditolak . Untuk pekali untuk t=5.79405 yang 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 kebolehlaksanaan pembelian blok saham
Regresi berbilang dalam Excel dilakukan menggunakan alat Analisis Data yang sama. Mari kita pertimbangkan masalah aplikasi tertentu.
Pengurusan syarikat NNN mesti membuat keputusan mengenai kesesuaian untuk membeli 20% kepentingan dalam MMM JSC. Kos pakej (SP) ialah 70 juta dolar AS. Pakar NNN telah 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);
- volum pusing ganti tahunan (VO);
- akaun belum terima (VD);
- kos aset tetap (COF).
Di samping itu, parameter tunggakan gaji perusahaan (V3 P) dalam ribuan dolar AS digunakan.
Penyelesaian menggunakan pemproses hamparan Excel
Pertama sekali, anda perlu membuat jadual data sumber. 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 "Selang input X" dan serlahkan julat semua nilai dari lajur B, C, D, F pada helaian.
Tandai item "Lembaran kerja baharu" dan klik "Ok".
Dapatkan analisis regresi untuk masalah tertentu.
Kajian keputusan dan kesimpulan
Kami "mengumpul" persamaan regresi daripada data bulat yang dibentangkan di atas pada hamparan Excel:
SP = 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 MMM JSC dibentangkan dalam jadual:
Menggantikannya ke dalam persamaan regresi, kita mendapat angka 64.72 juta dolar AS. Ini bermakna bahawa saham MMM JSC tidak berbaloi untuk dibeli, kerana nilainya sebanyak 70 juta dolar AS agak melambung.
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 Excel yang dibincangkan di atas akan membantu anda menyelesaikan masalah praktikal dalam bidang ekonometrik.
Pakej MS Excel membolehkan anda melakukan kebanyakan kerja dengan cepat apabila membina persamaan regresi linear. Adalah penting untuk memahami cara mentafsir keputusan yang diperolehi.
Memerlukan tambahan untuk berfungsi Pakej analisis, yang mesti didayakan dalam item menu Perkhidmatan\Tambahan
Dalam Excel 2007, untuk mendayakan pakej analisis, anda perlu klik pergi untuk menyekat Pilihan Excel dengan menekan butang di sebelah kiri bucu atas, dan kemudian butang Pilihan Excel"di bahagian bawah tetingkap:
Untuk membina model regresi, anda mesti memilih item Perkhidmatan\Analisis Data\Regression. (Dalam Excel 2007, mod ini berada dalam blok Data/Analisis Data/Regression). Kotak dialog akan muncul yang perlu anda isi:
1) Selang input Y¾ mengandungi pautan ke sel yang mengandungi nilai ciri yang terhasil y. Nilai mesti disusun dalam lajur;
2) Selang input X¾ mengandungi pautan ke sel yang mengandungi nilai faktor. Nilai mesti disusun dalam lajur;
3) Tanda tangan Tag ditetapkan jika sel pertama mengandungi teks penerangan(tandatangan data);
4) Tahap kebolehpercayaan¾ ialah tahap keyakinan, yang dianggap sebagai 95% secara lalai. Jika anda tidak berpuas hati dengan nilai ini, maka anda perlu mendayakan bendera ini dan masukkan nilai yang diperlukan;
5) Tanda tangan Malar-sifar disertakan jika perlu untuk membina persamaan di mana pembolehubah bebas ialah ;
6) Pilihan Output menentukan di mana keputusan harus diletakkan. Secara lalai mod binaan Lembaran kerja baharu;
7) Sekat Lebihan makanan membolehkan anda memasukkan keluaran sisa dan pembinaan grafnya.
Akibatnya, maklumat yang mengandungi semua maklumat yang diperlukan dan dikumpulkan kepada tiga blok: Statistik regresi, Analisis varians, Pengeluaran baki. Mari kita lihat mereka dengan lebih dekat.
1. Statistik regresi:
pelbagai R ditentukan 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 dengan ;
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 pekali, ralat piawai dan t-statistik.
P-nilai ¾ ialah nilai aras keertian yang sepadan dengan yang dikira t-ahli perangkaan. Ditentukan oleh fungsi STUDIST( t-statistik; ). Jika 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 selang keyakinan 95 peratus untuk pekali persamaan regresi linear teori. Jika nilai kebarangkalian keyakinan dalam blok input data dibiarkan pada nilai lalainya, maka dua lajur terakhir akan menduplikasi yang sebelumnya. Jika pengguna telah memasukkan nilai keyakinan, dua lajur terakhir mengandungi nilai sempadan bawah dan atas untuk tahap keyakinan yang ditentukan.
6. Analisis varians, garisan mengandungi nilai pekali, ralat piawai, t-ahli statistik, P-nilai dan selang keyakinan untuk .
7. Sekat Pengeluaran baki mengandungi nilai yang diramalkan y(dalam tatatanda kami ini ialah ) dan residu .
Perubahan dalam ciri paduan y adalah disebabkan oleh variasi dalam ciri faktor x. Bahagian varians dijelaskan oleh regresi dalam jumlah varians ciri yang terhasil mencirikan pekali penentuan R 2. Untuk hubungan linear, pekali penentuan adalah sama dengan kuasa dua pekali korelasi:R 2 = r xy 2 , dengan r xy ialah pekali korelasi.
Sebagai contoh, nilai R 2 = 0.83 bermakna dalam 83% kes perubahan dalam x membawa kepada perubahan dalam y. Dengan kata lain, ketepatan memilih persamaan regresi adalah tinggi.
Dikira untuk menilai kualiti pemadanan persamaan regresi. Untuk model yang boleh diterima, adalah dicadangkan bahawa pekali penentuan harus lebih besar daripada 50%. Model dengan pekali penentuan melebihi 80% boleh dianggap agak baik. Nilai pekali penentuan R 2 = 1 bermakna pergantungan fungsi antara pembolehubah.
Bila regresi tak linear pekali penentuan dikira menggunakan kalkulator ini. Dengan regresi berganda, pekali penentuan boleh didapati melalui perkhidmatan Regresi Berganda
Secara umum, pekali penentuan didapati dengan formula: atau
Peraturan untuk menambah varians:
,
di manakah jumlah sisihan kuasa dua;
- jumlah sisihan kuasa dua disebabkan oleh regresi (“dijelaskan” atau “faktorial”);
- jumlah baki sisihan kuasa dua.
Menggunakan kalkulator dalam talian ini anda boleh mengira pekali penentuan dan kepentingannya diperiksa (Contoh penyelesaian).
Arahan. Nyatakan jumlah data input. Penyelesaian yang terhasil disimpan dalam Fail perkataan. Templat juga dibuat secara automatik untuk menguji penyelesaian dalam Excel.
Pakej MS Excel membolehkan anda melakukan kebanyakan kerja dengan cepat apabila membina persamaan regresi linear. Adalah penting untuk memahami cara mentafsir keputusan yang diperolehi. Untuk membina model regresi, anda mesti memilih Tools\Data Analysis\Regression (dalam Excel 2007 mod ini berada dalam blok Data/Data Analysis/Regression). Kemudian salin keputusan ke dalam blok untuk analisis.
