Kaedah graf kuasa dua terkecil. Menggunakan Fungsi Excel Terbina dalam

Kaedah kuasa dua terkecil

hidup pelajaran akhir topik kita akan mengenali aplikasi yang paling terkenal FNP, yang menemui aplikasi terluas dalam pelbagai bidang sains dan aktiviti amali. Ia boleh menjadi fizik, kimia, biologi, ekonomi, sosiologi, psikologi dan sebagainya. Dengan kehendak takdir, saya sering terpaksa berurusan dengan ekonomi, dan oleh itu hari ini saya akan mengaturkan untuk anda tiket ke negara yang menakjubkan berhak Ekonometrik=) … Bagaimana anda tidak mahu itu?! Ia sangat bagus di sana - anda hanya perlu membuat keputusan! …Tetapi apa yang anda pasti mahu ialah belajar bagaimana untuk menyelesaikan masalah petak terkecil. Dan terutamanya pembaca yang rajin akan belajar untuk menyelesaikannya bukan sahaja dengan tepat, tetapi juga SANGAT PANTAS ;-) Tetapi pertama-tama pernyataan umum masalah + contoh pendamping:

Biarkan dalam beberapa bidang subjek penunjuk yang mempunyai ungkapan kuantitatif disiasat. Pada masa yang sama, terdapat setiap sebab untuk mempercayai bahawa penunjuk bergantung pada penunjuk. Andaian ini boleh hipotesis saintifik dan berdasarkan akal budi asas. Walau bagaimanapun, mari kita tinggalkan sains dan terokai lebih banyak kawasan yang menyelerakan - iaitu, kedai runcit. Nyatakan dengan:

– ruang runcit kedai runcit, persegi,
- perolehan tahunan kedai runcit, juta rubel.

Ia agak jelas apa lebih banyak kawasan kedai, semakin besar perolehannya dalam kebanyakan kes.

Katakan bahawa selepas menjalankan pemerhatian / eksperimen / pengiraan / menari dengan rebana, kami mempunyai data berangka yang boleh kami gunakan:

Dengan kedai runcit, saya fikir semuanya jelas: - ini adalah kawasan kedai pertama, - perolehan tahunannya, - kawasan kedai ke-2, - perolehan tahunannya, dsb. By the way, ia tidak perlu untuk mempunyai akses kepada bahan terperingkat- cukup anggaran yang tepat perolehan boleh diperolehi dengan cara statistik matematik . Namun, jangan terganggu, kursus pengintipan komersial sudah dibayar =)

Data jadual juga boleh ditulis dalam bentuk titik dan digambarkan dengan cara biasa bagi kita. Sistem kartesian .

Kami akan menjawab soalan penting: berapa banyak mata yang anda perlukan penyelidikan kualitatif?

Lebih besar lebih bagus. Set minimum yang boleh diterima terdiri daripada 5-6 mata. Di samping itu, dengan jumlah data yang kecil, keputusan "tidak normal" tidak boleh dimasukkan dalam sampel. Jadi, sebagai contoh, kedai elit kecil boleh membantu pesanan besar lebih daripada "rakan sekerja mereka", dengan itu memesongkan corak umum, yang akan dijumpai!

Jika ia agak mudah, kita perlu memilih fungsi , jadual yang melepasi sedekat mungkin dengan mata . Fungsi sedemikian dipanggil menghampiri (hampiran - anggaran) atau fungsi teori . Secara umumnya, di sini muncul "pemohon" yang jelas - polinomial darjat tinggi, yang grafnya melalui SEMUA titik. Tetapi pilihan ini adalah rumit, dan selalunya tidak betul. (kerana carta akan "berputar" sepanjang masa dan tidak mencerminkan arah aliran utama).

Oleh itu, fungsi yang dikehendaki mestilah cukup mudah dan pada masa yang sama mencerminkan pergantungan dengan secukupnya. Seperti yang anda mungkin rasa, salah satu kaedah untuk mencari fungsi sedemikian dipanggil petak terkecil. Pertama, mari kita menganalisis intipatinya secara umum. Biarkan beberapa fungsi menghampiri data percubaan:


Bagaimana untuk menilai ketepatan anggaran ini? Mari kita juga mengira perbezaan (penyimpangan) antara eksperimen dan nilai fungsi (kami mengkaji lukisan itu). Pemikiran pertama yang terlintas di fikiran adalah untuk menganggarkan berapa besar jumlahnya, tetapi masalahnya ialah perbezaannya boleh menjadi negatif. (Sebagai contoh, ) dan penyelewengan akibat penjumlahan tersebut akan membatalkan satu sama lain. Oleh itu, sebagai anggaran ketepatan anggaran, ia mencadangkan dirinya untuk mengambil jumlahnya modul penyelewengan:

atau dalam bentuk terlipat: (bagi yang tak tahu: ialah ikon jumlah, dan - pembolehubah tambahan - "counter", yang mengambil nilai dari 1 hingga ) .

Menghampirkan titik eksperimen dengan pelbagai fungsi, kita akan dapat makna yang berbeza, dan jelas sekali, apabila jumlah ini kurang, fungsi itu lebih tepat.

Kaedah sedemikian wujud dan dipanggil kaedah modulus terkecil. Walau bagaimanapun, dalam amalan ia telah menjadi lebih meluas. kaedah kuasa dua terkecil, di mana yang mungkin nilai negatif disingkirkan bukan oleh modulus, tetapi dengan mengkuadratkan sisihan:

, selepas itu usaha ditujukan kepada pemilihan fungsi sedemikian sehingga jumlah sisihan kuasa dua adalah sekecil mungkin. Sebenarnya, maka nama kaedah itu.

Dan kini kita kembali kepada yang lain perkara penting: seperti yang dinyatakan di atas, fungsi yang dipilih mestilah agak mudah - tetapi terdapat juga banyak fungsi sedemikian: linear , hiperbola , eksponen , logaritma , kuadratik dan lain-lain. Dan, sudah tentu, di sini saya ingin segera "mengurangkan bidang aktiviti." Apakah kelas fungsi untuk dipilih untuk penyelidikan? Primitif tetapi penerimaan yang berkesan:

- Cara paling mudah untuk menarik mata pada lukisan dan menganalisis lokasi mereka. Jika mereka cenderung berada dalam garis lurus, maka anda harus mencari persamaan garis lurus dengan nilai optimum dan . Dalam erti kata lain, tugasnya adalah untuk mencari pekali SEPERTI - supaya jumlah sisihan kuasa dua adalah yang terkecil.

Jika titik terletak, sebagai contoh, sepanjang hiperbola, maka jelaslah bahawa fungsi linear akan memberikan penghampiran yang lemah. Dalam kes ini, kami sedang mencari pekali yang paling "menguntungkan" untuk persamaan hiperbola - yang memberikan jumlah minimum kuasa dua .

Sekarang perhatikan bahawa dalam kedua-dua kes yang kita bincangkan fungsi dua pembolehubah, yang hujahnya pilihan pergantungan yang dicari:

Dan pada dasarnya, kita perlu menyelesaikan masalah standard - untuk mencari minimum fungsi dua pembolehubah.

Ingat contoh kami: katakan bahawa titik "kedai" cenderung terletak dalam garis lurus dan ada sebab untuk mempercayai kehadiran pergantungan linear perolehan daripada ruang Niaga. Mari kita cari pekali SEPERTI "a" dan "be" supaya jumlah sisihan kuasa dua adalah yang terkecil. Semuanya seperti biasa - pertama terbitan separa tertib pertama. mengikut peraturan lineariti anda boleh membezakan betul-betul di bawah ikon jumlah:

Kalau nak guna maklumat ini untuk esei atau kertas penggal - Saya akan sangat berterima kasih atas pautan dalam senarai sumber, anda akan mendapati pengiraan terperinci sedemikian di beberapa tempat:

Mari kita buat sistem standard:

Kami mengurangkan setiap persamaan dengan "dua" dan, sebagai tambahan, "memecahkan" jumlah:

Catatan : menganalisis secara bebas mengapa "a" dan "be" boleh dikeluarkan daripada ikon jumlah. By the way, secara rasmi ini boleh dilakukan dengan jumlah

Mari kita tulis semula sistem dalam bentuk "digunakan":

selepas itu algoritma untuk menyelesaikan masalah kami mula dilukis:

Adakah kita tahu koordinat titik-titik tersebut? Kami tahu. Jumlah boleh kita cari? Dengan mudah. Kami mengarang yang paling mudah sistem dua persamaan linear dengan dua tidak diketahui("a" dan "beh"). Kami menyelesaikan sistem, contohnya, kaedah Cramer, mengakibatkan titik pegun. Menyemak keadaan yang mencukupi untuk ekstrem, kita boleh mengesahkan bahawa pada ketika ini fungsi mencapai dengan tepat minimum. Pengesahan dikaitkan dengan pengiraan tambahan dan oleh itu kami akan meninggalkannya di belakang tabir. (jika perlu, bingkai yang hilang boleh dilihatDi sini ) . Kami membuat kesimpulan akhir:

Fungsi cara yang paling baik (sekurang-kurangnya berbanding dengan yang lain fungsi linear) membawa mata eksperimen lebih dekat . Secara kasarnya, grafnya melepasi sedekat mungkin ke titik-titik ini. Dalam tradisi ekonometrik fungsi anggaran yang terhasil juga dipanggil persamaan pasangan regresi linear .

Masalah yang sedang dipertimbangkan mempunyai besar nilai praktikal. Dalam situasi dengan contoh kita, persamaan membolehkan anda meramalkan jenis perolehan ("yig") akan berada di kedai dengan satu atau lain nilai kawasan jualan (satu atau satu lagi makna "x"). Ya, ramalan yang dihasilkan hanya akan menjadi ramalan, tetapi dalam banyak kes ia akan menjadi agak tepat.

Saya akan menganalisis hanya satu masalah dengan nombor "sebenar", kerana tidak ada kesulitan di dalamnya - semua pengiraan berada pada tahap kurikulum sekolah darjah 7-8. Dalam 95 peratus kes, anda akan diminta untuk mencari hanya fungsi linear, tetapi pada penghujung artikel saya akan menunjukkan bahawa tidak lebih sukar untuk mencari persamaan untuk hiperbola, eksponen, dan beberapa fungsi yang optimum.

Malah, ia kekal untuk mengedarkan barang yang dijanjikan - supaya anda belajar cara menyelesaikan contoh sedemikian bukan sahaja dengan tepat, tetapi juga dengan cepat. Kami mengkaji dengan teliti piawaian:

Tugasan

Hasil daripada mengkaji hubungan antara dua penunjuk, pasangan nombor berikut diperolehi:

Menggunakan kaedah kuasa dua terkecil, cari fungsi linear yang paling sesuai dengan empirikal (berpengalaman) data. Buat lukisan di mana dalam Cartesian sistem segi empat tepat koordinat untuk membina titik eksperimen dan graf bagi fungsi penghampiran . Cari jumlah sisihan kuasa dua antara nilai empirikal dan teori. Ketahui sama ada fungsi itu lebih baik (dari segi kaedah kuasa dua terkecil) anggaran titik eksperimen.

Ambil perhatian bahawa nilai "x" adalah nilai semula jadi, dan ini mempunyai ciri makna yang bermakna, yang akan saya bincangkan kemudian; tetapi mereka, sudah tentu, boleh menjadi pecahan. Di samping itu, bergantung pada kandungan tugas tertentu, kedua-dua nilai "X" dan "G" boleh menjadi negatif sepenuhnya atau sebahagiannya. Nah, kami telah diberi tugas "tidak berwajah", dan kami memulakannya penyelesaian:

Kami mencari pekali fungsi optimum sebagai penyelesaian kepada sistem:

Untuk tujuan tatatanda yang lebih padat, pembolehubah "pembilang" boleh diabaikan, kerana sudah jelas bahawa penjumlahan dijalankan dari 1 hingga .

Adalah lebih mudah untuk mengira jumlah yang diperlukan dalam bentuk jadual:


Pengiraan boleh dilakukan pada mikrokalkulator, tetapi lebih baik menggunakan Excel - lebih cepat dan tanpa ralat; tonton video pendek:

Oleh itu, kami mendapat yang berikut sistem:

Di sini anda boleh mendarabkan persamaan kedua dengan 3 dan tolak sebutan ke-2 daripada sebutan persamaan pertama dengan sebutan. Tetapi ini adalah nasib - dalam amalan, sistem sering tidak berbakat, dan dalam kes sedemikian ia menjimatkan kaedah Cramer:
, jadi sistem mempunyai penyelesaian yang unik.

Jom buat pemeriksaan. Saya faham bahawa saya tidak mahu, tetapi mengapa melangkau kesilapan yang anda benar-benar tidak boleh merinduinya? Gantikan penyelesaian yang ditemui ke sebelah kiri setiap persamaan sistem:

Bahagian kanan diterima persamaan yang sepadan, yang bermaksud bahawa sistem diselesaikan dengan betul.

Oleh itu, fungsi anggaran yang dikehendaki: – daripada semua fungsi linear data eksperimen adalah yang terbaik dianggarkan olehnya.

Tidak seperti lurus pergantungan pusing ganti kedai pada kawasannya, pergantungan yang didapati ialah terbalik (prinsip "lebih banyak - lebih sedikit"), dan fakta ini segera didedahkan oleh yang negatif pekali sudut . Fungsi memberitahu kita bahawa dengan peningkatan dalam penunjuk tertentu sebanyak 1 unit, nilai penunjuk bergantung berkurangan purata sebanyak 0.65 unit. Seperti yang mereka katakan, semakin tinggi harga soba, semakin kurang dijual.

Untuk merancang fungsi penghampiran, kita dapati dua daripada nilainya:

dan laksanakan lukisan:

Barisan yang dibina dipanggil garis aliran (iaitu, garis arah aliran linear, iaitu dalam kes am trend tidak semestinya garis lurus). Semua orang sudah biasa dengan ungkapan "menjadi dalam trend", dan saya fikir istilah ini tidak memerlukan ulasan tambahan.

Kira jumlah sisihan kuasa dua antara nilai empirikal dan teori. Secara geometri, ini ialah hasil tambah kuasa dua panjang segmen "lembayung". (dua daripadanya sangat kecil sehingga anda tidak dapat melihatnya).

Mari kita ringkaskan pengiraan dalam jadual:


Mereka sekali lagi boleh dijalankan secara manual, sekiranya saya akan memberikan contoh untuk perkara pertama:

tetapi ia lebih cekap untuk dilakukan dengan cara tertentu:

Mari ulangi: apakah maksud hasilnya? daripada semua fungsi linear fungsi mempunyai eksponen terkecil, iaitu, dalam keluarganya, ini adalah anggaran terbaik. Dan di sini, dengan cara itu, tidak sengaja. soalan akhir masalah: bagaimana jika fungsi eksponen yang dicadangkan adakah lebih baik untuk menganggarkan mata percubaan?

Mari cari jumlah sisihan kuasa dua yang sepadan - untuk membezakannya, saya akan menetapkannya dengan huruf "epsilon". Tekniknya adalah sama:

Dan sekali lagi untuk setiap pengiraan kebakaran untuk mata pertama:

Dalam Excel kita gunakan fungsi standard EXP (Sintaks boleh didapati dalam Bantuan Excel).

Kesimpulan: , yang bermaksud bahawa fungsi eksponen menghampiri titik eksperimen lebih teruk daripada garis lurus.

Tetapi perlu diperhatikan di sini bahawa "lebih teruk" adalah belum bermakna lagi, apa salahnya. Sekarang saya telah membina graf fungsi eksponen ini - dan ia juga melepasi hampir dengan mata - sehinggakan tanpa kajian analitik sukar untuk mengatakan fungsi mana yang lebih tepat.

Ini melengkapkan penyelesaian, dan saya kembali kepada persoalan nilai semula jadi hujah. Dalam pelbagai kajian, sebagai peraturan, ekonomi atau sosiologi, bulan, tahun atau selang masa yang sama lain dinomborkan dengan "X" semula jadi. Pertimbangkan, sebagai contoh, masalah berikut:

Kami mempunyai data berikut tentang perolehan runcit kedai untuk separuh pertama tahun ini:

Menggunakan penjajaran analitik garis lurus, cari volum jualan untuk bulan Julai.

Ya, tiada masalah: kami menomborkan bulan 1, 2, 3, 4, 5, 6 dan menggunakan algoritma biasa, akibatnya kami mendapat persamaan - satu-satunya perkara apabila tiba masanya biasanya huruf "te " (walaupun tidak kritikal). Persamaan yang terhasil menunjukkan bahawa pada separuh pertama tahun, perolehan meningkat secara purata sebanyak CU 27.74. sebulan. Dapatkan ramalan untuk bulan Julai (bulan #7): e.u.

Dan tugas yang serupa - kegelapan adalah gelap. Yang berhajat boleh guna perkhidmatan tambahan, iaitu saya Kalkulator Excel (versi demo), yang menyelesaikan masalah hampir serta-merta! Versi kerja program ini tersedia sebagai pertukaran atau untuk pembayaran simbolik.

Pada akhir pelajaran maklumat ringkas tentang mencari kebergantungan beberapa jenis lain. Sebenarnya, tiada apa yang istimewa untuk diberitahu, kerana pendekatan asas dan algoritma penyelesaian tetap sama.

Mari kita andaikan bahawa lokasi titik eksperimen menyerupai hiperbola. Kemudian, untuk mencari pekali hiperbola terbaik, anda perlu mencari minimum fungsi - mereka yang ingin boleh berbelanja pengiraan terperinci dan tampil dengan sistem yang serupa:

Dari sudut teknikal formal, ia diperoleh daripada sistem "linear". (mari tandakan dengan asterisk) menggantikan "x" dengan . Nah, jumlahnya hitung, selepas itu kepada pekali optimum "a" dan "be" di tangan.

Jika terdapat setiap sebab untuk mempercayai bahawa mata disusun di sepanjang lengkung logaritma, kemudian untuk mencari nilai optimum dan mencari minimum fungsi . Secara rasmi, dalam sistem (*) hendaklah digantikan dengan:

Apabila mengira dalam Excel, gunakan fungsi tersebut LN. Saya mengaku bahawa tidak sukar bagi saya untuk mencipta kalkulator untuk setiap kes yang sedang dipertimbangkan, tetapi ia akan menjadi lebih baik jika anda "memprogram" pengiraan sendiri. Tutorial video untuk membantu.

Dengan pergantungan eksponen, keadaannya sedikit lebih rumit. Untuk mengurangkan perkara itu kepada kes linear, ambil logaritma fungsi dan gunakan sifat logaritma:

Sekarang, membandingkan fungsi yang diperolehi dengan fungsi linear , kita sampai pada kesimpulan bahawa dalam sistem (*) mesti digantikan dengan , dan - oleh . Untuk kemudahan, kami menyatakan:

Sila ambil perhatian bahawa sistem diselesaikan berkenaan dengan dan , dan oleh itu, selepas mencari punca, anda tidak boleh lupa untuk mencari pekali itu sendiri.

Untuk menganggarkan titik eksperimen parabola optimum , harus dijumpai minimum fungsi tiga pembolehubah . Selepas melakukan tindakan standard, kami mendapat "berfungsi" berikut sistem:

Ya, sudah tentu, terdapat lebih banyak jumlah di sini, tetapi tidak ada kesulitan sama sekali apabila menggunakan aplikasi kegemaran anda. Dan akhirnya, saya akan memberitahu anda cara menyemak dan membina dengan cepat menggunakan Excel baris yang dikehendaki trend: buat carta serakan, pilih mana-mana mata dengan tetikus dan klik kanan pilih pilihan "Tambah garis aliran". Seterusnya, pilih jenis carta dan pada tab "Pilihan" aktifkan pilihan "Tunjukkan persamaan pada carta". okey

Seperti biasa, saya ingin menyiapkan satu artikel frasa yang indah, dan saya hampir menaip "Jadilah bergaya!". Tetapi lama-kelamaan dia berubah fikiran. Dan bukan kerana ia formula. Saya tidak tahu bagaimana sesiapa, tetapi saya tidak mahu mengikuti trend Amerika dan terutamanya Eropah yang dipromosikan sama sekali =) Oleh itu, saya berharap setiap daripada anda berpegang pada barisan anda sendiri!

http://www.grandars.ru/student/vysshaya-matematika/metod-naimenshih-kvadratov.html

Kaedah kuasa dua terkecil adalah salah satu kaedah yang paling biasa dan paling maju kerana ia kesederhanaan dan kecekapan kaedah untuk menganggar parameter model ekonometrik linear. Pada masa yang sama, berhati-hati tertentu harus diperhatikan apabila menggunakannya, kerana model yang dibina menggunakannya mungkin tidak memenuhi beberapa keperluan untuk kualiti parameter mereka dan, akibatnya, tidak "baik" mencerminkan corak pembangunan proses.

Mari kita pertimbangkan prosedur untuk menganggar parameter model ekonometrik linear menggunakan kaedah kuasa dua terkecil dengan lebih terperinci. Model sedemikian dalam bentuk umum boleh diwakili oleh persamaan (1.2):

y t = a 0 + a 1 x 1t +...+ a n x nt + ε t .

Data awal apabila menganggar parameter a 0 , a 1 ,..., a n ialah vektor nilai pembolehubah bersandar y= (y 1 , y 2 , ... , y T)" dan matriks nilai pembolehubah bebas

di mana lajur pertama, yang terdiri daripada satu, sepadan dengan pekali model .

Kaedah kuasa dua terkecil mendapat namanya berdasarkan prinsip asas bahawa anggaran parameter yang diperoleh berdasarkannya mesti memenuhi: jumlah kuasa dua ralat model hendaklah minimum.

Contoh penyelesaian masalah dengan kaedah kuasa dua terkecil

Contoh 2.1. Perusahaan perdagangan mempunyai rangkaian yang terdiri daripada 12 kedai, maklumat mengenai aktiviti yang dibentangkan dalam Jadual. 2.1.

Pihak pengurusan syarikat ingin mengetahui bagaimana saiz perolehan tahunan bergantung kepada ruang runcit kedai.

Jadual 2.1

Penyelesaian kuasa dua terkecil. Mari kita tentukan - perolehan tahunan kedai ke-, juta rubel; - kawasan jualan kedai ke-, ribu m 2.

Rajah.2.1. Scatterplot untuk Contoh 2.1

Untuk menentukan bentuk hubungan fungsi antara pembolehubah dan membina plot serakan (Rajah 2.1).

Berdasarkan rajah serakan, kita boleh membuat kesimpulan bahawa perolehan tahunan adalah bergantung secara positif pada kawasan jualan (iaitu, y akan meningkat dengan pertumbuhan ). Paling bentuk yang sesuai sambungan berfungsi - linear.

Maklumat untuk pengiraan selanjutnya dibentangkan dalam Jadual. 2.2. Menggunakan kaedah kuasa dua terkecil, kami menganggarkan parameter model ekonometrik satu faktor linear

Jadual 2.2

Oleh itu,

Oleh itu, dengan peningkatan dalam kawasan perdagangan sebanyak 1 ribu m 2, dengan lain-lain syarat sama rata purata perolehan tahunan meningkat sebanyak 67.8871 juta rubel.

Contoh 2.2. Pengurusan perusahaan menyedari bahawa perolehan tahunan bergantung bukan sahaja pada kawasan jualan kedai (lihat contoh 2.1), tetapi juga pada purata bilangan pelawat. Maklumat berkaitan dibentangkan dalam jadual. 2.3.

Jadual 2.3

Penyelesaian. Nyatakan - purata bilangan pelawat ke kedai ke setiap hari, ribuan orang.

Untuk menentukan bentuk hubungan fungsi antara pembolehubah dan membina plot serakan (Rajah 2.2).

Berdasarkan rajah taburan, kita boleh membuat kesimpulan bahawa perolehan tahunan adalah berkaitan secara positif dengan purata bilangan pelawat setiap hari (iaitu, y akan meningkat dengan pertumbuhan ). Bentuk pergantungan fungsi adalah linear.

nasi. 2.2. Scatterplot contohnya 2.2

Jadual 2.4

Secara umum, adalah perlu untuk menentukan parameter model ekonometrik dua faktor

y t \u003d a 0 + a 1 x 1t + a 2 x 2t + ε t

Maklumat yang diperlukan untuk pengiraan selanjutnya dibentangkan dalam Jadual. 2.4.

Mari kita anggarkan parameter model ekonometrik dua faktor linear menggunakan kaedah kuasa dua terkecil.

Oleh itu,

Penilaian pekali = 61.6583 menunjukkan bahawa, perkara lain yang sama, dengan peningkatan dalam kawasan perdagangan sebanyak 1 ribu m 2, perolehan tahunan akan meningkat dengan purata 61.6583 juta rubel.

Anggaran pekali = 2.2748 menunjukkan bahawa, perkara lain adalah sama, dengan peningkatan dalam purata bilangan pelawat setiap 1 ribu orang. setiap hari, perolehan tahunan akan meningkat sebanyak purata 2.2748 juta rubel.

Contoh 2.3. Menggunakan maklumat yang dibentangkan dalam jadual. 2.2 dan 2.4, anggarkan parameter model ekonometrik faktor tunggal

di manakah nilai berpusat perolehan tahunan kedai ke-, juta rubel; - nilai berpusat purata bilangan pelawat harian ke kedai ke-t, ribu orang. (lihat contoh 2.1-2.2).

Penyelesaian. Maklumat tambahan, diperlukan untuk pengiraan, dibentangkan dalam jadual. 2.5.

Jadual 2.5

Menggunakan formula (2.35), kita memperoleh

Oleh itu,

http://www.cleverstudents.ru/articles/mnk.html

Contoh.

Data eksperimen tentang nilai pembolehubah X Dan di diberikan dalam jadual.

Hasil daripada penjajaran mereka, fungsi

menggunakan kaedah kuasa dua terkecil, anggaran data ini pergantungan linear y=ax+b(cari pilihan A Dan b). Ketahui yang mana antara dua baris yang lebih baik (dalam erti kata kaedah kuasa dua terkecil) menjajarkan data eksperimen. Buat lukisan.

Penyelesaian.

Dalam contoh kita n=5. Kami mengisi jadual untuk kemudahan mengira jumlah yang termasuk dalam formula pekali yang diperlukan.

Nilai dalam baris keempat jadual diperoleh dengan mendarabkan nilai baris ke-2 dengan nilai baris ke-3 untuk setiap nombor i.

Nilai dalam baris kelima jadual diperoleh dengan mengkuadratkan nilai baris ke-2 untuk setiap nombor i.

Nilai lajur terakhir jadual ialah jumlah nilai merentas baris.

Kami menggunakan formula kaedah kuasa dua terkecil untuk mencari pekali A Dan b. Kami menggantikannya dengan nilai yang sepadan dari lajur terakhir jadual:

Oleh itu, y=0.165x+2.184 ialah garis lurus penghampiran yang dikehendaki.

Ia kekal untuk mengetahui yang mana satu baris y=0.165x+2.184 atau lebih baik menghampiri data asal, iaitu untuk membuat anggaran menggunakan kaedah kuasa dua terkecil.

Bukti.

Supaya apabila ditemui A Dan b fungsi mengambil nilai terkecil, ia adalah perlu bahawa pada ketika ini matriks bentuk kuadratik pembezaan tertib kedua untuk fungsi adalah pasti positif. Jom tunjuk.

Pembezaan urutan kedua mempunyai bentuk:

Itu dia

Oleh itu, matriks bentuk kuadratik mempunyai bentuk

dan nilai unsur tidak bergantung pada A Dan b.

Mari kita tunjukkan bahawa matriks adalah pasti positif. Ini memerlukan sudut minor menjadi positif.

Sudut minor daripada susunan pertama . Ketaksamaan adalah ketat, kerana mata

Contoh.

Data eksperimen tentang nilai pembolehubah X Dan di diberikan dalam jadual.

Hasil daripada penjajaran mereka, fungsi

menggunakan kaedah kuasa dua terkecil, anggaran data ini dengan pergantungan linear y=ax+b(cari pilihan A Dan b). Ketahui yang mana antara dua baris yang lebih baik (dalam erti kata kaedah kuasa dua terkecil) menjajarkan data eksperimen. Buat lukisan.

Intipati kaedah kuasa dua terkecil (LSM).

Masalahnya ialah untuk mencari pekali pergantungan linear yang mana fungsi dua pembolehubah A Dan b mengambil nilai terkecil. Iaitu, berdasarkan data A Dan b jumlah sisihan kuasa dua data eksperimen daripada garis lurus yang ditemui akan menjadi yang terkecil. Ini adalah titik keseluruhan kaedah kuasa dua terkecil.

Oleh itu, penyelesaian contoh dikurangkan kepada mencari ekstrem bagi fungsi dua pembolehubah.

Terbitan formula untuk mencari pekali.

Sistem dua persamaan dengan dua tidak diketahui disusun dan diselesaikan. Mencari terbitan separa bagi fungsi oleh pembolehubah A Dan b, kita samakan derivatif ini kepada sifar.

Kami menyelesaikan sistem persamaan yang terhasil dengan sebarang kaedah (contohnya kaedah penggantian atau kaedah Cramer) dan dapatkan formula untuk mencari pekali menggunakan kaedah kuasa dua terkecil (LSM).

Dengan data A Dan b fungsi mengambil nilai terkecil. Bukti fakta ini diberikan di bawah teks pada penghujung halaman.

Itulah keseluruhan kaedah kuasa dua terkecil. Formula untuk mencari parameter a mengandungi jumlah ,,, dan parameter n- jumlah data eksperimen. Nilai jumlah ini disyorkan untuk dikira secara berasingan. Pekali b ditemui selepas pengiraan a.

Sudah tiba masanya untuk mengingati contoh asal.

Penyelesaian.

Dalam contoh kita n=5. Kami mengisi jadual untuk kemudahan mengira jumlah yang termasuk dalam formula pekali yang diperlukan.

Nilai dalam baris keempat jadual diperoleh dengan mendarabkan nilai baris ke-2 dengan nilai baris ke-3 untuk setiap nombor i.

Nilai dalam baris kelima jadual diperoleh dengan mengkuadratkan nilai baris ke-2 untuk setiap nombor i.

Nilai lajur terakhir jadual ialah jumlah nilai merentas baris.

Kami menggunakan formula kaedah kuasa dua terkecil untuk mencari pekali A Dan b. Kami menggantikannya dengan nilai yang sepadan dari lajur terakhir jadual:

Oleh itu, y=0.165x+2.184 ialah garis lurus penghampiran yang dikehendaki.

Ia kekal untuk mengetahui yang mana satu baris y=0.165x+2.184 atau lebih baik menghampiri data asal, iaitu untuk membuat anggaran menggunakan kaedah kuasa dua terkecil.

Anggaran ralat kaedah kuasa dua terkecil.

Untuk melakukan ini, anda perlu mengira jumlah sisihan kuasa dua bagi data asal daripada baris ini Dan , nilai yang lebih kecil sepadan dengan garis yang lebih baik menghampiri data asal dari segi kaedah kuasa dua terkecil.

Sejak , kemudian baris y=0.165x+2.184 menganggarkan data asal dengan lebih baik.

Ilustrasi grafik kaedah kuasa dua terkecil (LSM).

Semuanya kelihatan hebat pada carta. Garis merah ialah garisan yang ditemui y=0.165x+2.184, garis biru ialah , titik merah jambu adalah data asal.

Dalam amalan, apabila memodelkan pelbagai proses - khususnya, ekonomi, fizikal, teknikal, sosial - satu atau kaedah lain untuk mengira nilai anggaran fungsi dari nilai yang diketahui pada beberapa titik tetap digunakan secara meluas.

Masalah penghampiran fungsi seperti ini sering timbul:

apabila membina formula anggaran untuk mengira nilai kuantiti ciri proses yang dikaji mengikut data jadual yang diperoleh hasil daripada eksperimen;

dalam pengamiran berangka, pembezaan, penyelesaian persamaan pembezaan dan lain-lain.;

jika perlu untuk mengira nilai fungsi pada titik perantaraan selang yang dipertimbangkan;

apabila menentukan nilai kuantiti ciri proses di luar selang yang sedang dipertimbangkan, khususnya, semasa ramalan.

Jika, untuk memodelkan proses tertentu yang ditentukan oleh jadual, fungsi dibina yang menggambarkan proses ini lebih kurang berdasarkan kaedah kuasa dua terkecil, ia akan dipanggil fungsi penghampiran (regresi), dan tugas membina fungsi penghampiran itu sendiri akan menjadi masalah anggaran.

Artikel ini membincangkan keupayaan pakej MS Excel untuk menyelesaikan masalah tersebut, sebagai tambahan, kaedah dan teknik untuk membina (mencipta) regresi untuk jadual. set fungsi(yang merupakan asas analisis regresi).

Terdapat dua pilihan untuk membina regresi dalam Excel.

Menambah regresi terpilih (garis arah aliran) pada carta yang dibina berdasarkan jadual data untuk ciri proses yang dikaji (hanya tersedia jika carta dibina);

Menggunakan fungsi statistik terbina dalam lembaran kerja Excel, yang membolehkan anda mendapatkan regresi (garisan aliran) terus daripada jadual data sumber.

Menambah Garis Aliran pada Carta

Untuk jadual data yang menerangkan proses tertentu dan diwakili oleh gambar rajah, Excel mempunyai alat analisis regresi yang berkesan yang membolehkan anda:

bina berdasarkan kaedah kuasa dua terkecil dan tambah lima pada rajah jenis regresi, yang, dengan tahap ketepatan yang berbeza-beza, memodelkan proses yang sedang dikaji;

tambahkan persamaan regresi yang dibina pada rajah;

tentukan tahap pematuhan regresi yang dipilih dengan data yang dipaparkan pada carta.

Berdasarkan data carta, Excel membolehkan anda mendapatkan jenis regresi linear, polinomial, logaritma, eksponen, eksponen, yang diberikan oleh persamaan:

y = y(x)

di mana x ialah pembolehubah bebas, yang selalunya mengambil nilai jujukan nombor asli (1; 2; 3; ...) dan menghasilkan, sebagai contoh, kira detik masa proses yang dikaji (ciri) .

1 . Regresi linear adalah baik dalam memodelkan ciri yang meningkat atau menurun pada kadar yang tetap. Ini adalah model paling mudah bagi proses yang sedang dikaji. Ia dibina mengikut persamaan:

y=mx+b

di mana m ialah tangen bagi cerun regresi linear kepada paksi-x; b - koordinat titik persilangan regresi linear dengan paksi-y.

2 . Garis arah aliran polinomial berguna untuk menerangkan ciri yang mempunyai beberapa ekstrem yang berbeza (tinggi dan rendah). Pilihan darjah polinomial ditentukan oleh bilangan ekstrem ciri yang dikaji. Oleh itu, polinomial darjah kedua boleh menggambarkan proses yang hanya mempunyai satu maksimum atau minimum; polinomial darjah ketiga - tidak lebih daripada dua ekstrem; polinomial darjah keempat - tidak lebih daripada tiga ekstrem, dsb.

Dalam kes ini, garis arah aliran dibina mengikut persamaan:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

di mana pekali c0, c1, c2,... c6 adalah pemalar yang nilainya ditentukan semasa pembinaan.

3 . Garis arah aliran logaritma berjaya digunakan dalam ciri pemodelan, nilai yang berubah dengan cepat pada mulanya, dan kemudian secara beransur-ansur stabil.

y = c ln(x) + b

4 . Garis aliran kuasa memberikan hasil yang baik jika nilai pergantungan yang dikaji dicirikan oleh perubahan berterusan dalam kadar pertumbuhan. Contoh pergantungan sedemikian boleh berfungsi sebagai graf pergerakan seragam dipercepatkan kereta. Jika terdapat sifar atau nilai negatif dalam data, anda tidak boleh menggunakan garis arah aliran kuasa.

Ia dibina mengikut persamaan:

y = cxb

di mana pekali b, c adalah pemalar.

5 . Garis arah aliran eksponen harus digunakan jika kadar perubahan dalam data terus meningkat. Untuk data yang mengandungi nilai sifar atau negatif, anggaran jenis ini juga tidak berkenaan.

Ia dibina mengikut persamaan:

y=cebx

di mana pekali b, c adalah pemalar.

Apabila memilih baris Trend Excel secara automatik mengira nilai R2, yang mencirikan ketepatan anggaran: semakin hampir nilai R2 kepada perpaduan, lebih pasti garis aliran menghampiri proses yang dikaji. Jika perlu, nilai R2 sentiasa boleh dipaparkan pada rajah.

Ditentukan oleh formula:

Untuk menambah garis arah aliran pada siri data:

aktifkan carta yang dibina berdasarkan siri data, iaitu, klik dalam kawasan carta. Item Carta akan muncul dalam menu utama;

selepas mengklik pada item ini, menu akan muncul pada skrin, di mana anda harus memilih arahan Tambah garis arah aliran.

Tindakan yang sama mudah dilaksanakan jika anda menuding pada graf yang sepadan dengan salah satu siri data dan klik kanan; dalam menu konteks yang muncul, pilih arahan Tambah garis arah aliran. Kotak dialog Trendline akan muncul pada skrin dengan tab Type dibuka (Gamb. 1).

Selepas itu anda perlukan:

Pada tab Jenis, pilih jenis garis arah aliran yang diperlukan (Linear dipilih secara lalai). Untuk jenis Polinomial, dalam medan Ijazah, nyatakan darjah polinomial yang dipilih.

1 . Medan Terbina pada Siri menyenaraikan semua siri data dalam carta yang dipersoalkan. Untuk menambah garis arah aliran pada siri data tertentu, pilih namanya dalam medan siri Terbina.

Jika perlu, dengan pergi ke tab Parameter (Gamb. 2), anda boleh menetapkan parameter berikut untuk garis arah aliran:

tukar nama garis arah aliran dalam Nama medan lengkung yang hampir (dilicinkan).

tetapkan bilangan tempoh (ke hadapan atau ke belakang) untuk ramalan dalam medan Ramalan;

paparkan persamaan garis arah aliran dalam kawasan carta, yang mana anda harus membolehkan kotak semak menunjukkan persamaan pada carta;

paparkan nilai kebolehpercayaan anggaran R2 dalam kawasan rajah, yang mana anda harus membolehkan kotak semak meletakkan nilai kebolehpercayaan anggaran (R^2) pada rajah;

tetapkan titik persilangan garis arah aliran dengan paksi-Y, yang mana anda harus mendayakan kotak semak Persilangan lengkung dengan paksi-Y pada satu titik;

klik butang OK untuk menutup kotak dialog.

Terdapat tiga cara untuk mula mengedit garis arah aliran yang telah dibina:

gunakan arahan garis aliran Terpilih daripada menu Format, selepas memilih garis aliran;

pilih arahan Format Trendline daripada menu konteks, yang dipanggil dengan mengklik kanan pada trendline;

dengan mengklik dua kali pada garis arah aliran.

Kotak dialog Format Trendline akan muncul pada skrin (Gamb. 3), mengandungi tiga tab: Lihat, Jenis, Parameter dan kandungan dua yang terakhir bertepatan sepenuhnya dengan tab yang serupa pada kotak dialog Trendline (Gamb. 1-2 ). Pada tab Paparan, anda boleh menetapkan jenis garisan, warna dan ketebalannya.

Untuk memadam garis arah aliran yang telah dibina, pilih garis arah aliran untuk dipadamkan dan tekan kekunci Padam.

Kelebihan alat analisis regresi yang dipertimbangkan ialah:

kemudahan relatif untuk memplot garis arah aliran pada carta tanpa membuat jadual data untuknya;

senarai jenis garis aliran yang dicadangkan yang agak luas, dan senarai ini termasuk jenis regresi yang paling biasa digunakan;

kemungkinan meramalkan kelakuan proses yang dikaji untuk sewenang-wenangnya (dalam akal) bilangan langkah ke hadapan dan juga ke belakang;

kemungkinan mendapatkan persamaan garis arah aliran dalam bentuk analisis;

kemungkinan, jika perlu, untuk mendapatkan penilaian kebolehpercayaan anggaran.

Kelemahan termasuk perkara berikut:

pembinaan garis arah aliran dijalankan hanya jika terdapat carta yang dibina pada satu siri data;

proses penjanaan siri data untuk ciri yang dikaji berdasarkan persamaan garis arah aliran yang diperolehi untuknya agak berantakan: persamaan regresi yang dikehendaki dikemas kini dengan setiap perubahan dalam nilai siri data asal, tetapi hanya dalam kawasan carta , manakala siri data yang dibentuk berdasarkan arah aliran persamaan garis lama, kekal tidak berubah;

Dalam laporan Carta Pangsi, apabila anda menukar paparan carta atau laporan Jadual Pangsi yang berkaitan, garis arah aliran sedia ada tidak dikekalkan, jadi anda mesti memastikan reka letak laporan memenuhi keperluan anda sebelum anda melukis garis arah aliran atau sebaliknya memformat laporan Carta Pangsi.

Garis arah aliran boleh ditambah pada siri data yang dibentangkan pada carta seperti graf, histogram, carta kawasan tidak normal rata, bar, serakan, gelembung dan carta saham.

Anda tidak boleh menambah garis arah aliran pada siri data pada carta 3-D, Standard, Radar, Pai dan Donut.

Menggunakan terbina dalam Fungsi Excel

Excel juga menyediakan alat analisis regresi untuk memplot garis arah aliran di luar kawasan carta. Beberapa fungsi lembaran kerja statistik boleh digunakan untuk tujuan ini, tetapi kesemuanya membenarkan anda membina regresi linear atau eksponen sahaja.

Excel mempunyai beberapa fungsi untuk membina regresi linear, khususnya:

TREND;

• CERUN dan POTONG.

Serta beberapa fungsi untuk membina garis aliran eksponen, khususnya:

LGRFPaprox.

Perlu diingatkan bahawa teknik untuk membina regresi menggunakan fungsi TREND dan GROWTH secara praktikalnya sama. Perkara yang sama boleh dikatakan mengenai pasangan fungsi LINEST dan LGRFPRIBL. Untuk empat fungsi ini, apabila mencipta jadual nilai, ciri Excel seperti formula tatasusunan digunakan, yang agak mengacaukan proses membina regresi. Kami juga ambil perhatian bahawa pembinaan regresi linear, pada pendapat kami, adalah paling mudah untuk dilaksanakan menggunakan fungsi SLOPE dan INTERCEPT, di mana yang pertama menentukan kecerunan regresi linear, dan yang kedua menentukan segmen yang dipotong oleh regresi. pada paksi-y.

Kelebihan alat fungsi terbina dalam untuk analisis regresi ialah:

proses yang agak mudah bagi jenis pembentukan siri data yang sama bagi ciri yang dikaji untuk semua fungsi statistik terbina dalam yang menetapkan garis aliran;

teknik standard untuk membina garis arah aliran berdasarkan siri data yang dihasilkan;

kemungkinan untuk meramalkan tingkah laku proses yang dikaji pada jumlah yang diperlukan langkah ke hadapan atau ke belakang.

Dan kelemahannya termasuk fakta bahawa Excel tidak mempunyai fungsi terbina dalam untuk mencipta jenis garis arah aliran yang lain (kecuali linear dan eksponen). Keadaan ini selalunya tidak membenarkan memilih model yang cukup tepat bagi proses yang dikaji, serta mendapatkan ramalan yang hampir dengan realiti. Di samping itu, apabila menggunakan fungsi TREND dan GROW, persamaan garis aliran tidak diketahui.

Perlu diingatkan bahawa penulis tidak menetapkan matlamat artikel untuk membentangkan perjalanan analisis regresi dengan tahap kesempurnaan yang berbeza-beza. Tugas utamanya adalah untuk menunjukkan keupayaan pakej Excel dalam menyelesaikan masalah anggaran menggunakan contoh khusus; tunjukkan alat berkesan yang ada pada Excel untuk membina regresi dan ramalan; menggambarkan betapa mudahnya masalah sedemikian boleh diselesaikan walaupun oleh pengguna yang tidak mempunyai pengetahuan mendalam tentang analisis regresi.

Contoh penyelesaian masalah tertentu

Pertimbangkan penyelesaian masalah khusus menggunakan alat tersenarai bagi pakej Excel.

Tugasan 1

Dengan jadual data mengenai keuntungan perusahaan pengangkutan motor untuk 1995-2002. anda perlu melakukan perkara berikut.

Bina carta.

Tambahkan garis arah aliran linear dan polinomial (kuadrat dan kubik) pada carta.

Menggunakan persamaan garis arah aliran, dapatkan data jadual tentang keuntungan perusahaan bagi setiap garis arah aliran untuk 1995-2004.

Buat ramalan keuntungan untuk perusahaan untuk tahun 2003 dan 2004.

Penyelesaian masalah

Dalam julat sel A4:C11 lembaran kerja Excel, kami memasukkan lembaran kerja yang ditunjukkan dalam Rajah. 4.

Setelah memilih julat sel B4:C11, kami membina carta.

Kami mengaktifkan carta yang dibina dan, menggunakan kaedah yang diterangkan di atas, selepas memilih jenis garis arah aliran dalam kotak dialog Garis Aliran (lihat Rajah 1), kami menambah garis arah aliran linear, kuadratik dan kubik secara bergilir-gilir pada carta. Dalam kotak dialog yang sama, buka tab Parameter (lihat Rajah 2), dalam medan Nama lengkung yang menghampiri (dilicinkan), masukkan nama arah aliran yang akan ditambah dan dalam medan Ramalan ke hadapan untuk: tempoh, tetapkan nilai 2, kerana ia dirancang untuk membuat ramalan keuntungan untuk dua tahun akan datang. Untuk memaparkan persamaan regresi dan nilai kebolehpercayaan anggaran R2 dalam kawasan rajah, dayakan kotak semak Tunjukkan persamaan pada skrin dan letakkan nilai kebolehpercayaan anggaran (R^2) pada rajah. Untuk persepsi visual yang lebih baik, kami menukar jenis, warna dan ketebalan garis arah aliran yang diplot, yang mana kami menggunakan tab Paparan pada kotak dialog Format Garis Aliran (lihat Rajah 3). Carta yang terhasil dengan garis aliran tambahan ditunjukkan dalam rajah. 5.

Untuk mendapatkan data jadual mengenai keuntungan perusahaan bagi setiap garis arah aliran untuk 1995-2004. Mari kita gunakan persamaan garis arah aliran yang dibentangkan dalam rajah. 5. Untuk melakukan ini, dalam sel julat D3:F3, masukkan maklumat teks mengenai jenis garis aliran yang dipilih: Aliran linear, Aliran kuadratik, Aliran padu. Seterusnya, masukkan formula regresi linear dalam sel D4 dan, menggunakan penanda isian, salin formula ini dengan rujukan relatif kepada julat sel D5:D13. Perlu diingat bahawa setiap sel dengan formula regresi linear daripada julat sel D4:D13 mempunyai sel yang sepadan daripada julat A4:A13 sebagai hujah. Begitu juga, untuk regresi kuadratik, julat sel E4:E13 diisi, dan untuk regresi kubik, julat sel F4:F13 diisi. Oleh itu, ramalan dibuat untuk keuntungan perusahaan untuk tahun 2003 dan 2004. dengan tiga trend. Jadual nilai yang terhasil ditunjukkan dalam rajah. 6.

Tugasan 2

Bina carta.

Tambahkan garis aliran logaritma, eksponen dan eksponen pada carta.

Terbitkan persamaan garis aliran yang diperolehi, serta nilai kebolehpercayaan anggaran R2 untuk setiap satu daripadanya.

Dengan menggunakan persamaan garis arah aliran, dapatkan data jadual tentang keuntungan perusahaan untuk setiap garis arah aliran untuk 1995-2002.

Buat ramalan keuntungan untuk perniagaan untuk tahun 2003 dan 2004 menggunakan garis arah aliran ini.

Penyelesaian masalah

Mengikuti metodologi yang diberikan dalam menyelesaikan masalah 1, kami memperoleh gambar rajah dengan garis aliran logaritma, eksponen dan eksponen tambahan (Rajah 7). Selanjutnya, menggunakan persamaan garis arah aliran yang diperolehi, kami mengisi jadual nilai untuk keuntungan perusahaan, termasuk nilai ramalan untuk tahun 2003 dan 2004. (Gamb. 8).

Pada rajah. 5 dan rajah. dapat dilihat bahawa model dengan aliran logaritma sepadan dengan nilai kebolehpercayaan anggaran yang paling rendah.

R2 = 0.8659

Nilai tertinggi R2 sepadan dengan model dengan trend polinomial: kuadratik (R2 = 0.9263) dan padu (R2 = 0.933).

Tugasan 3

Dengan jadual data tentang keuntungan perusahaan pengangkutan motor untuk 1995-2002, diberikan dalam tugasan 1, anda mesti melakukan langkah berikut.

Dapatkan siri data untuk garis arah aliran linear dan eksponen menggunakan fungsi TREND dan GROW.

Menggunakan fungsi TREND dan GROWTH, buat ramalan keuntungan untuk perusahaan untuk tahun 2003 dan 2004.

Untuk data awal dan siri data yang diterima, bina gambar rajah.

Penyelesaian masalah

Mari gunakan lembaran kerja tugasan 1 (lihat Rajah 4). Mari kita mulakan dengan fungsi TREND:

pilih julat sel D4:D11, yang harus diisi dengan nilai-nilai fungsi TREND yang sepadan dengan data yang diketahui mengenai keuntungan perusahaan;

panggil perintah Fungsi dari menu Sisipkan. Dalam kotak dialog Wizard Fungsi yang muncul, pilih fungsi TREND daripada kategori Statistik, dan kemudian klik butang OK. Operasi yang sama boleh dilakukan dengan menekan butang (fungsi Sisipkan) bar alat standard.

Dalam kotak dialog Argumen Fungsi yang muncul, masukkan julat sel C4:C11 dalam medan Known_values_y; dalam medan Known_values_x - julat sel B4:B11;

untuk menjadikan formula yang dimasukkan sebagai formula tatasusunan, gunakan kombinasi kekunci + + .

Formula yang kita masukkan dalam bar formula akan kelihatan seperti: =(TREND(C4:C11;B4:B11)).

Akibatnya, julat sel D4:D11 diisi dengan nilai yang sepadan bagi fungsi TREND (Rajah 9).

Untuk membuat ramalan keuntungan syarikat bagi tahun 2003 dan 2004. perlu:

pilih julat sel D12:D13, di mana nilai yang diramalkan oleh fungsi TREND akan dimasukkan.

panggil fungsi TREND dan dalam kotak dialog Fungsi Argumen yang muncul, masukkan dalam medan Known_values_y - julat sel C4:C11; dalam medan Known_values_x - julat sel B4:B11; dan dalam medan New_values_x - julat sel B12:B13.

tukar formula ini menjadi formula tatasusunan menggunakan pintasan papan kekunci Ctrl + Shift + Enter.

Formula yang dimasukkan akan kelihatan seperti: =(TREND(C4:C11;B4:B11;B12:B13)), dan julat sel D12:D13 akan diisi dengan nilai ramalan fungsi TREND (lihat Rajah. 9).

Begitu juga, siri data diisi menggunakan fungsi GROWTH, yang digunakan dalam analisis kebergantungan tak linear dan berfungsi sama seperti TREND rakan sejawatnya.

Rajah 10 menunjukkan jadual dalam mod paparan formula.

Untuk data awal dan siri data yang diperoleh, rajah ditunjukkan dalam rajah. sebelas.

Tugasan 4

Dengan jadual data mengenai penerimaan permohonan untuk perkhidmatan oleh perkhidmatan penghantaran perusahaan pengangkutan motor untuk tempoh dari 1 hingga 11 hari bulan semasa, tindakan berikut mesti dilakukan.

Dapatkan siri data untuk regresi linear: menggunakan fungsi SLOPE dan INTERCEPT; menggunakan fungsi LINEST.

Dapatkan semula siri data untuk regresi eksponen menggunakan fungsi LYFFPRIB.

Menggunakan fungsi di atas, buat ramalan tentang penerimaan permohonan kepada perkhidmatan penghantaran untuk tempoh dari 12 hingga 14 hari bulan semasa.

Untuk siri data asal dan diterima, bina gambar rajah.

Penyelesaian masalah

Ambil perhatian bahawa, tidak seperti fungsi TREND dan GROW, tiada satu pun fungsi yang disenaraikan di atas (CERUN, MINTASAN, LINEST, LGRFPRIB) adalah regresi. Fungsi ini hanya memainkan peranan tambahan, menentukan parameter regresi yang diperlukan.

Untuk regresi linear dan eksponen yang dibina menggunakan fungsi SLOPE, INTERCEPT, LINEST, LGRFPRIB, penampilan persamaannya sentiasa diketahui, berbeza dengan regresi linear dan eksponen yang sepadan dengan fungsi TREND dan GROWTH.

1 . Mari kita bina regresi linear yang mempunyai persamaan:

y=mx+b

menggunakan fungsi SLOPE dan INTERCEPT, dengan kecerunan regresi m ditentukan oleh fungsi SLOPE, dan istilah malar b - oleh fungsi INTERCEPT.

Untuk melakukan ini, kami melakukan tindakan berikut:

masukkan jadual sumber dalam julat sel A4:B14;

nilai parameter m akan ditentukan dalam sel C19. Pilih daripada kategori Statistik fungsi Cerun; masukkan julat sel B4:B14 dalam medan_values_y yang diketahui dan julat sel A4:A14 dalam medan_values_x yang diketahui. Formula akan dimasukkan ke dalam sel C19: =SLOPE(B4:B14;A4:A14);

menggunakan kaedah yang sama, nilai parameter b dalam sel D19 ditentukan. Dan kandungannya akan kelihatan seperti ini: = INTERCEPT(B4:B14;A4:A14). Oleh itu, nilai-nilai parameter m dan b, yang diperlukan untuk membina regresi linear, akan disimpan, masing-masing, dalam sel C19, D19;

kemudian kita masukkan formula regresi linear dalam sel C4 dalam bentuk: = $ C * A4 + $ D. Dalam formula ini, sel C19 dan D19 ditulis dengan rujukan mutlak (alamat sel tidak boleh berubah dengan kemungkinan penyalinan). Tanda rujukan mutlak $ boleh ditaip sama ada dari papan kekunci atau menggunakan kekunci F4, selepas meletakkan kursor pada alamat sel. Menggunakan pemegang isian, salin formula ini ke julat sel C4:C17. Kami mendapat siri data yang dikehendaki (Rajah 12). Oleh kerana bilangan permintaan ialah integer, anda harus menetapkan format nombor pada tab Nombor pada tetingkap Format Sel dengan bilangan tempat perpuluhan kepada 0.

2 . Sekarang mari kita bina regresi linear yang diberikan oleh persamaan:

y=mx+b

menggunakan fungsi LINEST.

Untuk ini:

masukkan fungsi LINEST sebagai formula tatasusunan ke dalam julat sel C20:D20: =(LINEST(B4:B14;A4:A14)). Hasilnya, kita mendapat nilai parameter m dalam sel C20, dan nilai parameter b dalam sel D20;

masukkan formula dalam sel D4: =$C*A4+$D;

salin formula ini menggunakan penanda isian ke julat sel D4:D17 dan dapatkan siri data yang dikehendaki.

3 . Kami membina regresi eksponen yang mempunyai persamaan:

dengan bantuan fungsi LGRFPRIBL, ia dilakukan dengan cara yang sama:

dalam julat sel C21:D21, masukkan fungsi LGRFPRIBL sebagai formula tatasusunan: =( LGRFPRIBL (B4:B14;A4:A14)). Dalam kes ini, nilai parameter m akan ditentukan dalam sel C21, dan nilai parameter b akan ditentukan dalam sel D21;

formula dimasukkan ke dalam sel E4: =$D*$C^A4;

menggunakan penanda isian, formula ini disalin ke julat sel E4:E17, di mana siri data untuk regresi eksponen akan ditempatkan (lihat Rajah 12).

Pada rajah. 13 menunjukkan jadual di mana kita boleh melihat fungsi yang kita gunakan dengan julat sel yang diperlukan, serta formula.

Nilai R 2 dipanggil pekali penentuan.

Tugas membina pergantungan regresi adalah untuk mencari vektor pekali m model (1) di mana pekali R mengambil nilai maksimum.

Untuk menilai kepentingan R, ujian F Fisher digunakan, dikira dengan formula

di mana n- saiz sampel (bilangan eksperimen);

k ialah bilangan pekali model.

Jika F melebihi beberapa nilai kritikal untuk data n Dan k dan tahap keyakinan yang diterima, maka nilai R dianggap signifikan. Jadual nilai kritikal F diberikan dalam buku rujukan tentang statistik matematik.

Oleh itu, kepentingan R ditentukan bukan sahaja oleh nilainya, tetapi juga oleh nisbah antara bilangan eksperimen dan bilangan pekali (parameter) model. Sesungguhnya, nisbah korelasi untuk n=2 untuk model linear mudah ialah 1 (melalui 2 mata pada satah, anda sentiasa boleh melukis satu garis lurus). Walau bagaimanapun, jika data eksperimen adalah pembolehubah rawak, nilai R sedemikian harus dipercayai dengan berhati-hati. Biasanya, untuk mendapatkan R yang signifikan dan regresi yang boleh dipercayai, ia bertujuan untuk memastikan bilangan eksperimen melebihi jumlah pekali model (n>k) dengan ketara.

Untuk membina linear model regresi perlu:

1) sediakan senarai n baris dan m lajur yang mengandungi data eksperimen (lajur yang mengandungi nilai output Y mestilah sama ada yang pertama atau yang terakhir dalam senarai); sebagai contoh, mari kita ambil data tugas sebelumnya, menambah lajur yang dipanggil "nombor tempoh", menomborkan nombor tempoh dari 1 hingga 12. (ini akan menjadi nilai X)

2) pergi ke menu Data/Analisis Data/Regression

Jika item "Analisis Data" dalam menu "Alat" tiada, maka anda harus pergi ke item "Tambahan" pada menu yang sama dan tandai kotak "Pakej Analisis".

3) dalam kotak dialog "Regression", tetapkan:

selang input Y;

selang input X;

selang keluaran - sel kiri atas selang di mana keputusan pengiraan akan diletakkan (disyorkan untuk meletakkannya pada lembaran kerja baharu);

4) klik "Ok" dan analisis keputusan.

Intipati kaedah kuasa dua terkecil ialah dalam mencari parameter model trend yang paling menggambarkan trend pembangunan beberapa fenomena rawak dalam masa atau ruang (trend ialah garis yang mencirikan trend perkembangan ini). Tugas kaedah kuasa dua terkecil (OLS) adalah untuk mencari bukan sahaja beberapa model trend, tetapi untuk mencari yang terbaik atau model optimum. Model ini akan menjadi optimum jika jumlah sisihan piawai antara nilai sebenar yang diperhatikan dan nilai aliran yang dikira sepadan adalah minimum (paling kecil):

di mana - sisihan piawai antara nilai sebenar yang diperhatikan

dan nilai aliran yang dikira sepadan,

Nilai sebenar (diperhatikan) fenomena yang dikaji,

Anggaran nilai model aliran,

Bilangan pemerhatian terhadap fenomena yang dikaji.

MNC jarang digunakan sendiri. Sebagai peraturan, selalunya ia digunakan hanya sebagai teknik yang diperlukan dalam kajian korelasi. Perlu diingat bahawa asas maklumat MNC hanya boleh dipercayai siri statistik, dan bilangan pemerhatian tidak boleh kurang daripada 4, jika tidak, prosedur pelicinan LSM mungkin hilang akal.

Kit alat OLS dikurangkan kepada prosedur berikut:

Prosedur pertama. Ternyata sama ada terdapat sebarang kecenderungan sama sekali untuk menukar atribut terhasil apabila faktor-argumen yang dipilih berubah, atau dengan kata lain, sama ada terdapat hubungan antara " di "Dan" X ».

Prosedur kedua. Ia ditentukan garisan (trajektori) yang paling sesuai untuk menggambarkan atau mencirikan aliran ini.

Prosedur ketiga.

Contoh. Katakan kita mempunyai maklumat tentang purata hasil bunga matahari untuk ladang yang dikaji (Jadual 9.1).

Jadual 9.1

Oleh kerana tahap teknologi dalam pengeluaran bunga matahari di negara kita tidak banyak berubah sejak 10 tahun yang lalu, ini bermakna, kemungkinan besar, turun naik hasil dalam tempoh yang dianalisis sangat bergantung kepada turun naik dalam keadaan cuaca dan iklim. Adakah benar?

Prosedur MNC pertama. Hipotesis tentang kewujudan trend perubahan dalam hasil bunga matahari bergantung kepada perubahan cuaca dan keadaan iklim dalam tempoh 10 tahun yang dianalisis sedang diuji.

DALAM contoh ini belakang" y » adalah dinasihatkan untuk mengambil hasil bunga matahari, dan untuk « x » ialah bilangan tahun yang diperhatikan dalam tempoh yang dianalisis. Menguji hipotesis tentang kewujudan sebarang hubungan antara " x "Dan" y » boleh dilakukan dalam dua cara: secara manual dan menggunakan program komputer. Sudah tentu, jika ada Teknologi komputer masalah ini menyelesaikan sendiri. Tetapi, untuk lebih memahami kit alat OLS, adalah dinasihatkan untuk menguji hipotesis tentang kewujudan hubungan antara " x "Dan" y » secara manual, apabila hanya pen dan kalkulator biasa. Dalam kes sedemikian, hipotesis kewujudan arah aliran diuji dengan terbaik secara visual mengikut lokasi imej grafik daripada siri dinamik yang dianalisis - medan korelasi:

Medan korelasi dalam contoh kami terletak di sekitar garis yang meningkat perlahan. Ini dengan sendirinya menunjukkan wujudnya trend tertentu dalam perubahan hasil bunga matahari. Adalah mustahil untuk bercakap tentang kehadiran mana-mana arah aliran hanya apabila medan korelasi kelihatan seperti bulatan, bulatan, awan menegak atau mendatar ketat, atau terdiri daripada titik bertaburan secara rawak. Dalam semua kes lain, adalah perlu untuk mengesahkan hipotesis kewujudan hubungan antara " x "Dan" y dan meneruskan penyelidikan.

Prosedur MNC kedua. Ia ditentukan garisan (trajektori) yang paling sesuai untuk menggambarkan atau mencirikan arah aliran dalam perubahan hasil bunga matahari untuk tempoh yang dianalisis.

Dengan adanya teknologi komputer, pemilihan trend optimum berlaku secara automatik. Dengan pemprosesan "manual", pilihan fungsi optimum dijalankan, sebagai peraturan, dengan cara visual - dengan lokasi medan korelasi. Iaitu, mengikut jenis carta, persamaan garis dipilih, yang paling sesuai dengan aliran empirikal (ke trajektori sebenar).

Seperti yang anda ketahui, secara semula jadi terdapat pelbagai jenis kebergantungan berfungsi, jadi sangat sukar untuk menganalisis secara visual walaupun sebahagian kecil daripadanya. Nasib baik, dalam amalan ekonomi sebenar, kebanyakan perhubungan boleh digambarkan dengan tepat sama ada dengan parabola, atau hiperbola, atau garis lurus. Dalam hal ini, dengan pilihan "manual" untuk memilih fungsi terbaik, anda boleh mengehadkan diri anda kepada tiga model ini sahaja.

Parabola tertib kedua: :

Adalah mudah untuk melihat bahawa dalam contoh kami, trend dalam perubahan hasil bunga matahari selama 10 tahun yang dianalisis adalah yang terbaik dicirikan oleh garis lurus, jadi persamaan regresi akan menjadi persamaan garis lurus.

Prosedur ketiga. Parameter dikira persamaan regresi mencirikan baris ini, atau dengan kata lain, formula analisis ditentukan yang menerangkan model terbaik trend.

Mencari nilai parameter persamaan regresi, dalam kes kami, parameter dan , adalah teras LSM. Proses ini dikurangkan untuk menyelesaikan sistem persamaan biasa.

(9.2)

Sistem persamaan ini agak mudah diselesaikan dengan kaedah Gauss. Ingat bahawa sebagai hasil daripada penyelesaian, dalam contoh kami, nilai parameter dan dijumpai. Oleh itu, persamaan regresi yang ditemui akan mempunyai bentuk berikut:

Tidak menemui jawapan kepada soalan anda? Tengok sini

"Jangan tangguh sehingga esok apa yang anda boleh tangguhkan sehingga lusa" - Mark Twain

"Rahsia perubahan adalah untuk memberi tumpuan kepada mencipta yang baru, bukan melawan yang lama" - Socrates

Tafsiran Heksagram 15 adalah yang paling tepat