Penghampiran data eksperimen adalah kaedah berdasarkan menggantikan data yang diperoleh secara eksperimen dengan fungsi analitik yang paling hampir melepasi atau bertepatan pada titik nod dengan nilai asal (data yang diperoleh semasa eksperimen atau eksperimen). Pada masa ini, terdapat dua cara untuk menentukan fungsi analisis:
Dengan membina polinomial interpolasi n-darjah yang lulus secara langsung melalui semua titik tatasusunan data yang diberikan. DALAM dalam kes ini fungsi penghampiran diwakili sebagai: polinomial interpolasi dalam bentuk Lagrange atau polinomial interpolasi dalam bentuk Newton.
Dengan membina polinomial menghampiri darjah n yang lulus di kawasan berhampiran mata daripada tatasusunan data yang diberikan. Oleh itu, fungsi penghampiran melancarkan semua hingar rawak (atau ralat) yang mungkin timbul semasa eksperimen: nilai yang diukur semasa eksperimen bergantung pada faktor rawak yang berubah-ubah mengikut mereka sendiri. undang-undang rawak(kesilapan pengukuran atau instrumen, ketidaktepatan atau kesilapan eksperimen). Dalam kes ini, fungsi anggaran ditentukan menggunakan kaedah petak terkecil.
Kaedah kuasa dua terkecil(dalam kesusasteraan Inggeris Ordinary Least Squares, OLS) ialah kaedah matematik berdasarkan penentuan fungsi penghampiran yang dibina dalam jarak yang paling hampir dengan mata daripada tatasusunan data eksperimen tertentu. Kehampiran fungsi asal dan anggaran F(x) ditentukan oleh ukuran berangka, iaitu: jumlah sisihan kuasa dua data eksperimen daripada lengkung penghampiran F(x) hendaklah yang terkecil.
Anggaran lengkung yang dibina menggunakan kaedah kuasa dua terkecil
Kaedah kuasa dua terkecil digunakan:
Untuk menyelesaikan sistem persamaan yang terlalu ditentukan apabila bilangan persamaan melebihi bilangan yang tidak diketahui;
Untuk mencari penyelesaian dalam kes biasa (tidak ditindih) sistem tak linear persamaan;
Untuk menganggarkan nilai mata dengan beberapa fungsi anggaran.
Fungsi penghampiran menggunakan kaedah kuasa dua terkecil ditentukan daripada keadaan jumlah minimum sisihan kuasa dua bagi fungsi penghampiran yang dikira daripada tatasusunan data eksperimen tertentu. Kriteria kaedah kuasa dua terkecil ini ditulis sebagai ungkapan berikut:
Nilai fungsi anggaran yang dikira pada titik nod,
Tatasusunan data eksperimen yang diberikan pada titik nod.
Kriteria kuadratik mempunyai beberapa sifat "baik", seperti kebolehbezaan, memberikan penyelesaian unik kepada masalah anggaran dengan fungsi anggaran polinomial.
Bergantung kepada keadaan masalah, fungsi anggaran ialah polinomial darjah m
Darjah fungsi penghampiran tidak bergantung pada bilangan titik nod, tetapi dimensinya mestilah sentiasa kurang daripada dimensi (bilangan titik) tatasusunan data eksperimen yang diberikan.
∙ Jika darjah fungsi anggaran ialah m=1, maka kita menganggarkan fungsi jadual dengan garis lurus (regresi linear).
∙ Jika darjah fungsi menghampiri ialah m=2, maka kita menganggarkan fungsi jadual parabola kuadratik(penghampiran kuadratik).
∙ Jika darjah fungsi penghampiran ialah m=3, maka kita menganggarkan fungsi jadual dengan parabola padu (penghampiran padu).
DALAM kes am apabila perlu untuk membina polinomial anggaran darjah m untuk diberi nilai jadual, syarat untuk jumlah minimum sisihan kuasa dua ke atas semua titik nod ditulis semula dalam bentuk berikut:
- pekali tidak diketahui polinomial hampir darjah m;
Bilangan nilai jadual yang ditentukan.
Syarat yang diperlukan untuk kewujudan minimum fungsi ialah kesamaan kepada sifar terbitan separa berkenaan dengan pembolehubah yang tidak diketahui. . Hasilnya kita dapat sistem berikut persamaan:
Mari kita ubah yang terhasil sistem linear persamaan: buka kurungan dan gerakkan sebutan bebas ke sebelah kanan ungkapan. Sistem linear yang terhasil ungkapan algebra akan ditulis dalam bentuk berikut:
Sistem ungkapan algebra linear ini boleh ditulis semula dalam bentuk matriks:
Hasilnya ialah satu sistem persamaan linear dimensi m+1, yang terdiri daripada m+1 tidak diketahui. Sistem ini boleh diselesaikan menggunakan sebarang kaedah untuk menyelesaikan masalah linear. persamaan algebra(contohnya, dengan kaedah Gaussian). Hasil daripada penyelesaian itu, parameter yang tidak diketahui bagi fungsi penghampiran akan ditemui yang memberikan jumlah minimum sisihan kuasa dua bagi fungsi anggaran daripada data asal, i.e. penghampiran kuadratik terbaik. Perlu diingat bahawa jika walaupun satu nilai data sumber berubah, semua pekali akan mengubah nilainya, kerana ia ditentukan sepenuhnya oleh data sumber.
Penghampiran data sumber mengikut pergantungan linear
(regresi linear)
Sebagai contoh, pertimbangkan teknik untuk menentukan fungsi anggaran, yang diberikan dalam bentuk pergantungan linear. Selaras dengan kaedah kuasa dua terkecil, syarat untuk jumlah minimum sisihan kuasa dua ditulis dalam bentuk berikut:
Koordinat nod jadual;
Pekali tidak diketahui bagi fungsi penghampiran, yang dinyatakan sebagai pergantungan linear.
Syarat yang diperlukan untuk kewujudan minimum fungsi ialah kesamaan kepada sifar terbitan separa berkenaan dengan pembolehubah yang tidak diketahui. Hasilnya, kami memperoleh sistem persamaan berikut:
Mari kita ubah sistem persamaan linear yang terhasil.
Kami menyelesaikan sistem persamaan linear yang terhasil. Pekali bagi fungsi anggaran dalam bentuk analisis ditentukan seperti berikut (kaedah Cramer):
Pekali ini memastikan pembinaan fungsi penghampiran linear mengikut kriteria meminimumkan jumlah kuasa dua fungsi anggaran daripada nilai jadual yang diberikan (data eksperimen).
Algoritma untuk melaksanakan kaedah kuasa dua terkecil
1. Data awal:
Tatasusunan data eksperimen dengan bilangan ukuran N ditentukan
Darjah polinomial yang hampir (m) ditentukan
2. Algoritma pengiraan:
2.1. Pekali ditentukan untuk membina sistem persamaan dengan dimensi
Pekali sistem persamaan ( sebelah kiri persamaan)
- indeks nombor lajur matriks segi empat sama sistem persamaan
Sebutan bebas sistem persamaan linear ( bahagian kanan persamaan)
- indeks nombor baris matriks segi empat sama sistem persamaan
2.2. Pembentukan sistem persamaan linear dengan dimensi .
2.3. Menyelesaikan sistem persamaan linear untuk menentukan pekali yang tidak diketahui bagi polinomial menghampiri darjah m.
2.4 Penentuan jumlah sisihan kuasa dua bagi polinomial yang hampir daripada nilai asal pada semua titik nod
Nilai yang ditemui bagi jumlah sisihan kuasa dua adalah minimum yang mungkin.
Pengiraan menggunakan fungsi lain
Perlu diingat bahawa apabila menganggarkan data sumber mengikut kaedah kuasa dua terkecil, fungsi logaritma kadangkala digunakan sebagai fungsi anggaran, fungsi eksponen dan fungsi kuasa.
Penghampiran logaritma
Mari kita pertimbangkan kes apabila fungsi anggaran diberikan oleh fungsi logaritma bentuk:
Intipati kaedah kuasa dua terkecil ialah dalam mencari parameter model trend yang paling menggambarkan kecenderungan pembangunan mana-mana fenomena rawak dalam masa atau ruang (trend ialah garis yang mencirikan kecenderungan perkembangan ini). Tugas kaedah kuasa dua terkecil (LSM) adalah untuk mencari bukan sahaja beberapa model trend, tetapi untuk mencari model terbaik atau optimum. Model ini akan menjadi optimum jika jumlah sisihan kuasa dua 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,
Nilai pengiraan model aliran,
Bilangan pemerhatian terhadap fenomena yang dikaji.
MNC jarang digunakan sendiri. Sebagai peraturan, selalunya ia digunakan hanya sebagai teknik teknikal 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 OLS mungkin hilang akal.
Kit alat MNC bermuara 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, adakah terdapat hubungan antara " di "Dan" X ».
Prosedur kedua. Ia ditentukan garis (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
|
Nombor pemerhatian |
||||||||||
|
Produktiviti, c/ha |
Oleh kerana tahap teknologi dalam pengeluaran bunga matahari di negara kita kekal hampir tidak berubah sejak 10 tahun yang lalu, ini bermakna, nampaknya, turun naik hasil dalam tempoh yang dianalisis sangat bergantung kepada turun naik dalam cuaca dan keadaan iklim. Adakah ini benar-benar benar?
Prosedur OLS pertama. Hipotesis tentang kewujudan trend dalam perubahan hasil bunga matahari bergantung kepada perubahan cuaca dan keadaan iklim selama 10 tahun yang dianalisis diuji.
Dalam contoh ini, untuk " y " adalah dinasihatkan untuk mengambil hasil bunga matahari, dan untuk " x » – bilangan tahun yang diperhatikan dalam tempoh yang dianalisis. Menguji hipotesis tentang kewujudan sebarang hubungan antara " x "Dan" y "boleh dilakukan dengan dua cara: secara manual dan menggunakan program komputer. Sudah tentu, jika ada kelengkapan komputer masalah ini selesai sendiri. Tetapi untuk lebih memahami alat MNC, adalah dinasihatkan untuk menguji hipotesis tentang kewujudan hubungan antara " x "Dan" y » secara manual, apabila hanya pen dan kalkulator biasa berada di tangan. Dalam kes sedemikian, hipotesis tentang kewujudan arah aliran paling baik diperiksa secara visual oleh lokasi imej grafik siri dinamik yang dianalisis - medan korelasi:
Medan korelasi dalam contoh kami terletak di sekitar garis yang semakin meningkat secara perlahan. Ini dengan sendirinya menunjukkan kewujudan trend tertentu dalam perubahan dalam hasil bunga matahari. Adalah mustahil untuk bercakap tentang kehadiran sebarang kecenderungan hanya apabila medan korelasi kelihatan seperti bulatan, bulatan, awan menegak atau mendatar ketat, atau terdiri daripada titik yang berselerak secara huru-hara. Dalam semua kes lain, hipotesis tentang kewujudan hubungan antara " x "Dan" y ", dan teruskan penyelidikan.
Prosedur OLS kedua. Ia ditentukan garisan (trajektori) yang terbaik boleh menggambarkan atau mencirikan arah aliran perubahan dalam hasil bunga matahari sepanjang tempoh yang dianalisis.
Jika anda mempunyai teknologi komputer, pemilihan arah aliran optimum berlaku secara automatik. Apabila memproses secara manual, pilihan fungsi optimum dijalankan, sebagai peraturan, secara visual - oleh lokasi medan korelasi. Iaitu, berdasarkan jenis graf, persamaan garis yang paling sesuai dengan aliran empirikal (trajektori sebenar) dipilih.
Seperti yang diketahui, secara semula jadi terdapat pelbagai jenis kebergantungan berfungsi, jadi sangat sukar untuk menganalisis secara visual walaupun sebahagian kecil daripadanya. Mujurlah, dalam amalan ekonomi sebenar, kebanyakan perhubungan boleh diterangkan dengan agak 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.
|
Hiperbola: |
||
|
|
|
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 tertentu, atau dengan kata lain, formula analisis ditentukan yang menerangkan model terbaik trend.
Mencari nilai parameter persamaan regresi, dalam kes kami, parameter dan , ialah teras OLS. Proses ini datang untuk menyelesaikan sistem persamaan normal.
(9.2)
Sistem persamaan ini boleh diselesaikan dengan agak mudah dengan kaedah Gauss. Mari kita ingat bahawa sebagai hasil daripada penyelesaian, dalam contoh kita, nilai-nilai parameter dan dijumpai. Oleh itu, persamaan regresi yang ditemui akan mempunyai bentuk berikut: 
Ia mempunyai banyak aplikasi, kerana ia membenarkan perwakilan anggaran fungsi tertentu oleh yang lain yang lebih mudah. LSM boleh menjadi sangat berguna dalam memproses pemerhatian, dan ia digunakan secara aktif untuk menganggar beberapa kuantiti berdasarkan hasil pengukuran yang lain yang mengandungi ralat rawak. Dalam artikel ini, anda akan belajar cara melaksanakan pengiraan kuasa dua terkecil dalam Excel.
Pernyataan masalah menggunakan contoh tertentu
Katakan terdapat dua penunjuk X dan Y. Lebih-lebih lagi, Y bergantung pada X. Oleh kerana OLS menarik minat kita dari sudut analisis regresi (dalam Excel kaedahnya dilaksanakan menggunakan fungsi terbina dalam), kita harus segera beralih kepada mempertimbangkan masalah tertentu.
Jadi, biarkan X ialah ruang runcit kedai runcit, diukur dalam meter persegi, dan Y ialah perolehan tahunan, diukur dalam berjuta-juta rubel.
Ia dikehendaki membuat ramalan tentang perolehan (Y) yang akan dimiliki oleh kedai jika ia mempunyai ruang runcit ini atau itu. Jelas sekali, fungsi Y = f (X) semakin meningkat, kerana pasar raya besar menjual lebih banyak barangan daripada gerai.
Beberapa perkataan tentang ketepatan data awal yang digunakan untuk ramalan
Katakan kita mempunyai jadual yang dibina menggunakan data untuk n stor.
mengikut statistik matematik, keputusan akan lebih kurang betul jika data pada sekurang-kurangnya 5-6 objek diperiksa. Di samping itu, keputusan "anomali" tidak boleh digunakan. Khususnya, butik kecil elit boleh mempunyai perolehan yang beberapa kali lebih besar daripada perolehan kedai runcit besar kelas "pasaran besar".
Intipati kaedah
Data jadual boleh digambarkan pada satah Cartes dalam bentuk titik M 1 (x 1, y 1), ... M n (x n, y n). Sekarang penyelesaian kepada masalah akan dikurangkan kepada pemilihan fungsi anggaran y = f (x), yang mempunyai graf yang melepasi sedekat mungkin ke titik M 1, M 2, .. M n.
Sudah tentu, anda boleh menggunakan polinomial darjah tinggi, tetapi pilihan ini bukan sahaja sukar untuk dilaksanakan, tetapi juga tidak betul, kerana ia tidak akan mencerminkan trend utama yang perlu dikesan. Penyelesaian yang paling munasabah ialah mencari garis lurus y = ax + b, yang paling sesuai menghampiri data eksperimen, atau lebih tepat lagi, pekali a dan b.
Penilaian ketepatan
Dengan sebarang anggaran, menilai ketepatannya adalah amat penting. Mari kita nyatakan dengan e i perbezaan (sisihan) antara nilai kefungsian dan eksperimen untuk titik x i, iaitu e i = y i - f (x i).
Jelas sekali, untuk menilai ketepatan anggaran, anda boleh menggunakan jumlah sisihan, iaitu, apabila memilih garis lurus untuk perwakilan anggaran pergantungan X pada Y, anda perlu memberi keutamaan kepada yang mempunyai nilai terkecil jumlah e i pada semua mata yang dipertimbangkan. Walau bagaimanapun, tidak semuanya begitu mudah, kerana bersama-sama dengan sisihan positif akan ada juga yang negatif.
Isu ini boleh diselesaikan menggunakan modul sisihan atau petak mereka. Kaedah terakhir adalah yang paling banyak digunakan. Ia digunakan dalam banyak bidang, termasuk analisis regresi (dilaksanakan dalam Excel menggunakan dua fungsi terbina dalam), dan telah lama membuktikan keberkesanannya.
Kaedah kuasa dua terkecil
Excel, seperti yang anda ketahui, mempunyai fungsi AutoSum terbina dalam yang membolehkan anda mengira nilai semua nilai yang terletak dalam julat yang dipilih. Oleh itu, tiada apa yang akan menghalang kita daripada mengira nilai ungkapan (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
DALAM tatatanda matematik ia kelihatan seperti:
Oleh kerana keputusan pada mulanya dibuat untuk menganggar menggunakan garis lurus, kami mempunyai:
Oleh itu, tugas mencari garis yang paling menggambarkan pergantungan khusus kuantiti X dan Y, turun untuk mengira minimum fungsi dua pembolehubah:
Untuk melakukan ini, anda perlu menyamakan derivatif separa berkenaan dengan pembolehubah baru a dan b kepada sifar, dan menyelesaikan sistem primitif yang terdiri daripada dua persamaan dengan 2 bentuk yang tidak diketahui:
Selepas beberapa transformasi mudah, termasuk pembahagian dengan 2 dan manipulasi jumlah, kami mendapat:
Menyelesaikannya, sebagai contoh, menggunakan kaedah Cramer, kita memperoleh titik pegun dengan pekali tertentu a * dan b *. Ini adalah minimum, iaitu untuk meramalkan jumlah pusing ganti yang akan dimiliki oleh kedai untuk kawasan tertentu, garis lurus y = a * x + b * adalah sesuai, iaitu model regresi untuk contoh yang dipersoalkan. Sudah tentu, ia tidak akan membenarkan anda mencari hasil yang tepat, tetapi ia akan membantu anda mendapatkan idea sama ada membeli kawasan tertentu secara kredit kedai akan membuahkan hasil.
Cara Melaksanakan Kuasa Dua Terkecil dalam Excel
Excel mempunyai fungsi untuk mengira nilai menggunakan kuasa dua terkecil. Ia mempunyai bentuk berikut: "TREND" (nilai Y diketahui; nilai X diketahui; nilai X baharu; pemalar). Mari gunakan formula untuk mengira OLS dalam Excel pada jadual kami.
Untuk melakukan ini, masukkan tanda "=" dalam sel di mana hasil pengiraan menggunakan kaedah kuasa dua terkecil dalam Excel harus dipaparkan dan pilih fungsi "TREND". Dalam tetingkap yang terbuka, isikan medan yang sesuai, menyerlahkan:
- julat nilai yang diketahui untuk Y (dalam kes ini, data untuk perolehan perdagangan);
- julat x 1 , …x n , iaitu saiz ruang runcit;
- kedua-duanya terkenal dan nilai yang tidak diketahui x, yang mana anda perlu mengetahui saiz perolehan (untuk maklumat tentang lokasi mereka pada lembaran kerja, lihat di bawah).
Di samping itu, formula mengandungi pembolehubah logik "Const". Jika anda memasukkan 1 dalam medan yang sepadan, ini bermakna anda harus menjalankan pengiraan, dengan mengandaikan bahawa b = 0.
Sekiranya anda perlu mengetahui ramalan untuk lebih daripada satu nilai x, maka selepas memasukkan formula anda tidak boleh menekan "Enter", tetapi anda perlu menaip kombinasi "Shift" + "Control" + "Enter" pada papan kekunci.
Beberapa ciri
Analisis regresi boleh diakses walaupun kepada dummies. Formula Excel untuk meramalkan nilai tatasusunan pembolehubah yang tidak diketahui—TREND—boleh digunakan walaupun oleh mereka yang tidak pernah mendengar tentang kuasa dua terkecil. Cukup sekadar mengetahui beberapa ciri kerjanya. khususnya:
- Jika anda menyusun julat nilai yang diketahui bagi pembolehubah y dalam satu baris atau lajur, maka setiap baris (lajur) dengan nilai yang diketahui x akan dianggap oleh program sebagai pembolehubah yang berasingan.
- Jika tetingkap TREND tidak menunjukkan julat dengan x yang diketahui, maka jika fungsi itu digunakan dalam program Excel akan menganggapnya sebagai tatasusunan yang terdiri daripada integer, bilangan yang sepadan dengan julat dengan nilai pembolehubah y yang diberikan.
- Untuk mengeluarkan tatasusunan nilai "diramalkan", ungkapan untuk mengira aliran mesti dimasukkan sebagai formula tatasusunan.
- Jika nilai baru x tidak dinyatakan, maka fungsi TREND menganggapnya sama dengan yang diketahui. Jika ia tidak dinyatakan, maka tatasusunan 1 diambil sebagai hujah; 2; 3; 4;…, yang sepadan dengan julat dengan parameter y yang telah ditetapkan.
- Julat yang mengandungi nilai x baharu mesti mempunyai baris atau lajur yang sama atau lebih seperti julat yang mengandungi nilai y yang diberikan. Dalam erti kata lain, ia mestilah berkadar dengan pembolehubah bebas.
- Tatasusunan dengan nilai x yang diketahui boleh mengandungi berbilang pembolehubah. Walau bagaimanapun, jika kita bercakap tentang hanya satu, maka julat dengan nilai x dan y yang diberikan perlu berkadar. Dalam kes beberapa pembolehubah, julat dengan nilai y yang diberikan perlu dimuatkan dalam satu lajur atau satu baris.
Fungsi RAMALAN
Dilaksanakan menggunakan beberapa fungsi. Salah satunya dipanggil "PREDICTION". Ia serupa dengan "TREND", iaitu ia memberikan hasil pengiraan menggunakan kaedah kuasa dua terkecil. Walau bagaimanapun, hanya untuk satu X, yang mana nilai Y tidak diketahui.
Kini anda mengetahui formula dalam Excel untuk boneka yang membolehkan anda meramalkan nilai masa depan penunjuk tertentu mengikut arah aliran linear.
Contoh.
Data eksperimen tentang nilai pembolehubah X Dan di diberikan dalam jadual.
Hasil daripada penjajaran mereka, fungsi itu diperolehi 
menggunakan kaedah kuasa dua terkecil, anggaran data ini dengan pergantungan linear y=ax+b(cari parameter 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).
Tugasnya adalah untuk mencari pekali pergantungan linear di mana fungsi dua pembolehubah A Dan b
mengambil nilai terkecil. Iaitu, diberi 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, menyelesaikan contoh adalah untuk mencari ekstrem fungsi dua pembolehubah.
Menerbitkan formula untuk mencari pekali.
Sistem dua persamaan dengan dua tidak diketahui disusun dan diselesaikan. Mencari terbitan separa bagi suatu fungsi 
oleh pembolehubah A Dan b, kita samakan derivatif ini kepada sifar. 
Kami menyelesaikan sistem persamaan yang terhasil menggunakan sebarang kaedah (contohnya dengan kaedah penggantian atau kaedah Cramer) dan dapatkan formula untuk mencari pekali menggunakan kaedah kuasa dua terkecil (LSM). 
Diberi A Dan b fungsi 
mengambil nilai terkecil. Bukti fakta ini diberikan di bawah dalam teks pada penghujung halaman.
Itulah keseluruhan kaedah kuasa dua terkecil. Formula untuk mencari parameter a mengandungi jumlah ,,, dan parameter n- jumlah data eksperimen. Kami mengesyorkan untuk mengira nilai amaun ini 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 dalam baris ke-2 untuk setiap nombor i.
Nilai dalam lajur terakhir jadual adalah jumlah nilai di seluruh baris.
Kami menggunakan formula kaedah kuasa dua terkecil untuk mencari pekali A Dan b. Kami menggantikan nilai yang sepadan dari lajur terakhir jadual kepada mereka: 
Oleh itu, y = 0.165x+2.184- garis lurus anggaran yang dikehendaki.
Ia kekal untuk mengetahui yang mana satu baris y = 0.165x+2.184 atau 
lebih baik menghampiri data asal, iaitu 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 dalam erti kata kaedah kuasa dua terkecil. 
Sejak , kemudian lurus y = 0.165x+2.184 lebih baik menghampiri data asal.
Ilustrasi grafik kaedah kuasa dua terkecil (LS).
Semuanya jelas kelihatan pada graf. Garis merah ialah garis lurus 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 daripada nilai yang diketahui pada titik tetap tertentu digunakan secara meluas.
Masalah penghampiran fungsi jenis ini sering timbul:
apabila membina formula anggaran untuk mengira nilai kuantiti ciri proses yang dikaji menggunakan data jadual yang diperoleh hasil daripada eksperimen;
dalam pengamiran berangka, pembezaan, penyelesaian persamaan pembezaan dan lain-lain.;
jika perlu, hitung nilai fungsi pada titik perantaraan selang yang dipertimbangkan;
apabila menentukan nilai kuantiti ciri proses di luar selang yang dipertimbangkan, khususnya semasa meramal.
Jika, untuk memodelkan proses tertentu yang ditentukan oleh jadual, kami membina fungsi yang lebih kurang menerangkan proses ini berdasarkan kaedah kuasa dua terkecil, ia akan dipanggil fungsi penghampiran (regresi), dan masalah membina fungsi penghampiran itu sendiri akan dipanggil. masalah anggaran.
Artikel ini membincangkan keupayaan pakej MS Excel untuk menyelesaikan masalah jenis ini, di samping itu, ia menyediakan kaedah dan teknik untuk membina (mencipta) regresi untuk fungsi jadual (yang merupakan asas analisis regresi).
Excel mempunyai dua pilihan untuk membina regresi.
Menambah regresi terpilih ( garis trend- garis arah aliran) ke dalam gambar rajah yang dibina berdasarkan jadual data untuk ciri proses yang sedang dikaji (hanya tersedia jika terdapat gambar rajah yang dibina);
Menggunakan fungsi statistik terbina dalam lembaran kerja Excel, membolehkan anda memperoleh regresi (garisan aliran) terus daripada jadual data sumber.
Menambah garis arah aliran pada carta
Untuk jadual data yang menerangkan proses dan diwakili oleh gambar rajah, Excel mempunyai alat analisis regresi yang berkesan yang membolehkan anda:
bina berdasarkan kaedah kuasa dua terkecil dan tambahkan lima jenis regresi pada rajah, yang memodelkan proses yang dikaji dengan pelbagai darjah ketepatan;
tambahkan persamaan regresi yang dibina pada rajah;
tentukan tahap kesesuaian regresi yang dipilih dengan data yang dipaparkan pada carta.
Berdasarkan data carta, Excel membolehkan anda memperoleh jenis regresi linear, polinomial, logaritma, kuasa, eksponen, yang ditentukan oleh persamaan:
y = y(x)
di mana x ialah pembolehubah bebas yang sering mengambil nilai jujukan nombor asli (1; 2; 3; ...) dan menghasilkan, sebagai contoh, kira detik masa proses yang dikaji (ciri).
1 . Regresi linear adalah baik untuk memodelkan ciri-ciri yang nilainya meningkat atau menurun pada kadar yang tetap. Ini adalah model paling mudah untuk dibina untuk proses yang dikaji. Ia dibina mengikut persamaan:
y = mx + b
di mana m ialah tangen bagi sudut kecondongan regresi linear kepada paksi absis; b - koordinat titik persilangan regresi linear dengan paksi ordinat.
2 . Garis arah aliran polinomial berguna untuk menerangkan ciri yang mempunyai beberapa ekstrem yang berbeza (maksima dan minima). 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 ialah pemalar yang nilainya ditentukan semasa pembinaan.
3 . Garis arah aliran logaritma berjaya digunakan apabila memodelkan ciri-ciri yang nilainya pada mulanya berubah dengan cepat dan kemudian secara beransur-ansur stabil.
y = c ln(x) + b
4 . Garis arah aliran undang-undang kuasa memberikan hasil yang baik jika nilai hubungan yang dikaji dicirikan oleh perubahan berterusan dalam kadar pertumbuhan. Contoh pergantungan sedemikian ialah graf pergerakan seragam kereta. Jika terdapat sifar atau nilai negatif dalam data, anda tidak boleh menggunakan garis aliran kuasa.
Dibina mengikut persamaan:
y = c xb
di mana pekali b, c ialah pemalar.
5 . Garis arah aliran eksponen harus digunakan apabila kadar perubahan dalam data terus meningkat. Untuk data yang mengandungi nilai sifar atau negatif, anggaran jenis ini juga tidak berkenaan.
Dibina mengikut persamaan:
y = c ebx
di mana pekali b, c ialah pemalar.
Apabila memilih garis arah aliran, Excel secara automatik mengira nilai R2, yang mencirikan kebolehpercayaan anggaran: daripada nilai yang lebih dekat R2 kepada perpaduan, lebih pasti garis aliran menghampiri proses yang dikaji. Jika perlu, nilai R2 sentiasa boleh dipaparkan pada carta.
Ditentukan oleh formula:
Untuk menambah garis arah aliran pada siri data:
aktifkan carta berdasarkan satu siri data, iaitu klik dalam kawasan carta. Item Rajah akan muncul dalam menu utama;
selepas mengklik pada item ini, menu akan muncul pada skrin di mana anda harus memilih arahan Add trend line.
Tindakan yang sama boleh dilaksanakan dengan mudah dengan menggerakkan penuding tetikus ke atas graf yang sepadan dengan salah satu siri data dan klik kanan; Dalam menu konteks yang muncul, pilih arahan Tambah garis arah aliran. Kotak dialog Trend Line akan muncul pada skrin dengan tab Type dibuka (Gamb. 1).
Selepas ini anda perlukan:
Pilih jenis garis arah aliran yang diperlukan pada tab Jenis (jenis Linear dipilih secara lalai). Untuk jenis Polinomial, dalam medan Ijazah, nyatakan darjah polinomial yang dipilih.
1 . Medan Dibina pada siri menyenaraikan semua siri data dalam carta yang dipersoalkan. Untuk menambah garis 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 rajah, yang mana anda harus mendayakan persamaan tunjukkan pada kotak semak rajah;
paparkan nilai kebolehpercayaan anggaran R2 dalam kawasan rajah, yang mana anda harus membolehkan kotak semak Letakkan nilai kebolehpercayaan anggaran pada rajah (R^2);
tetapkan titik persilangan garis arah aliran dengan paksi Y, yang mana anda harus mendayakan kotak semak untuk persilangan lengkung dengan paksi Y pada satu titik;
Klik butang OK untuk menutup kotak dialog.
Untuk mula mengedit garis arah aliran yang telah dilukis, terdapat tiga cara:
gunakan arahan garis aliran Terpilih daripada menu Format, setelah memilih garis aliran sebelum ini;
pilih arahan Format garis aliran daripada menu konteks, yang dipanggil dengan mengklik kanan pada garis aliran;
klik dua kali pada garis arah aliran.
Kotak dialog Format Garisan Trend 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 Trend Line (Gamb. 1). -2). Pada tab Paparan, anda boleh menetapkan jenis garisan, warna dan ketebalannya.
Untuk memadam garis aliran yang telah dilukis, pilih garis arah aliran untuk dipadamkan dan tekan kekunci Padam.
Kelebihan alat analisis regresi yang dipertimbangkan ialah:
kemudahan relatif untuk membina 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;
keupayaan untuk meramalkan kelakuan proses yang dikaji oleh bilangan langkah ke hadapan dan juga ke belakang yang sewenang-wenangnya (dalam had akal);
keupayaan untuk mendapatkan persamaan garis arah aliran dalam bentuk analisis;
kemungkinan, jika perlu, untuk mendapatkan penilaian kebolehpercayaan anggaran.
Kelemahannya termasuk yang berikut:
pembinaan garis aliran dijalankan hanya jika terdapat gambar rajah yang dibina pada satu siri data;
proses menjana siri data untuk ciri yang dikaji berdasarkan persamaan garis arah aliran yang diperoleh untuknya agak berantakan: persamaan regresi yang diperlukan dikemas kini dengan setiap perubahan dalam nilai siri data asal, tetapi hanya dalam kawasan rajah , manakala siri data, dijana berdasarkan persamaan garis arah aliran lama, kekal tidak berubah;
Dalam laporan Carta Pangsi, menukar paparan carta atau laporan Jadual Pangsi yang berkaitan tidak mengekalkan garis arah aliran sedia ada, bermakna sebelum anda melukis garis arah aliran atau sebaliknya memformat laporan Carta Pangsi, anda harus memastikan bahawa reka letak laporan memenuhi keperluan yang diperlukan.
Garis arah aliran boleh digunakan untuk menambah siri data yang dibentangkan pada carta seperti graf, histogram, carta kawasan tidak piawai rata, carta bar, carta serakan, carta gelembung dan carta saham.
Anda tidak boleh menambah garis arah aliran pada siri data dalam carta 3D, ternormal, radar, pai dan donat.
Menggunakan fungsi terbina dalam Excel
Excel juga mempunyai alat analisis regresi untuk memplot garis arah aliran di luar kawasan carta. Terdapat beberapa fungsi lembaran kerja statistik yang boleh anda gunakan untuk tujuan ini, tetapi kesemuanya hanya membenarkan anda membina regresi linear atau eksponen.
Excel mempunyai beberapa fungsi untuk membina regresi linear, khususnya:
CERUN dan POTONG.
TREND;
Serta beberapa fungsi untuk membina garis aliran eksponen, khususnya:
LGRFPRIBL.
Perlu diingatkan bahawa teknik untuk membina regresi menggunakan fungsi TREND dan GROWTH adalah hampir sama. Perkara yang sama boleh dikatakan mengenai pasangan fungsi LINEST dan LGRFPRIBL. Untuk empat fungsi ini, mencipta jadual nilai menggunakan ciri Excel seperti formula tatasusunan, yang agak mengganggu proses membina regresi. Mari kita perhatikan juga bahawa pembinaan regresi linear, pada pendapat kami, paling mudah dicapai menggunakan fungsi CERUN dan INTERCEPT, di mana yang pertama menentukan kecerunan regresi linear, dan yang kedua menentukan segmen yang dipintas oleh regresi pada paksi-y.
Kelebihan alat fungsi terbina dalam untuk analisis regresi ialah:
proses yang agak mudah dan seragam untuk menjana siri data bagi ciri yang dikaji untuk semua fungsi statistik terbina dalam yang mentakrifkan garis arah aliran;
metodologi standard untuk membina garis aliran berdasarkan siri data yang dijana;
keupayaan untuk meramalkan tingkah laku proses yang dikaji dengan bilangan langkah yang diperlukan ke hadapan atau ke belakang.
Kelemahannya termasuk fakta bahawa Excel tidak mempunyai fungsi terbina dalam untuk mencipta jenis garis arah aliran (kecuali linear dan eksponen) yang lain. 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 GROWTH, persamaan garis aliran tidak diketahui.
Perlu diingatkan bahawa penulis tidak menetapkan untuk membentangkan kursus analisis regresi dengan sebarang tahap kesempurnaan. Tugas utamanya adalah untuk menunjukkan, menggunakan contoh khusus, keupayaan pakej Excel semasa menyelesaikan masalah anggaran; tunjukkan alat berkesan yang ada pada Excel untuk membina regresi dan ramalan; menggambarkan bagaimana masalah sedemikian boleh diselesaikan dengan agak mudah walaupun oleh pengguna yang tidak mempunyai pengetahuan yang luas tentang analisis regresi.
Contoh penyelesaian masalah tertentu
Mari lihat menyelesaikan masalah khusus menggunakan alat Excel yang disenaraikan.
Masalah 1
Dengan jadual data mengenai keuntungan perusahaan pengangkutan motor untuk 1995-2002. anda perlu melakukan perkara berikut:
Bina gambar rajah.
Tambahkan garis arah aliran linear dan polinomial (kuadrat dan kubik) pada carta.
Menggunakan persamaan garis arah aliran, dapatkan data jadual tentang keuntungan perusahaan untuk setiap garis arah aliran untuk 1995-2004.
Buat ramalan untuk keuntungan perusahaan untuk tahun 2003 dan 2004.
Penyelesaian masalah
Dalam julat sel A4:C11 lembaran kerja Excel, masukkan lembaran kerja yang ditunjukkan dalam Rajah. 4.
Setelah memilih julat sel B4:C11, kami membina gambar rajah.
Kami mengaktifkan gambar rajah yang dibina dan, mengikut kaedah yang diterangkan di atas, selepas memilih jenis garis arah aliran dalam kotak dialog Garis Aliran (lihat Rajah 1), kami secara bergilir-gilir menambah garis arah aliran linear, kuadratik dan kubik pada rajah. Dalam kotak dialog yang sama, buka tab Parameter (lihat Rajah 2), dalam medan Nama lengkung yang menghampiri (dilicinkan), masukkan nama arah aliran yang 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 persamaan tunjukkan pada kotak semak 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 dibina, yang mana kami menggunakan tab Paparan kotak dialog Format Garisan Aliran (lihat Rajah 3). Gambar rajah yang terhasil dengan garis aliran tambahan ditunjukkan dalam Rajah. 5.
Untuk mendapatkan data jadual tentang 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 tentang jenis garis arah 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 sebagai hujah sel yang sepadan daripada julat A4:A13. Begitu juga, untuk regresi kuadratik, isikan julat sel E4:E13, dan untuk regresi kubik, isikan julat sel F4:F13. Oleh itu, ramalan untuk keuntungan perusahaan untuk tahun 2003 dan 2004 telah disusun. menggunakan tiga trend. Jadual nilai yang terhasil ditunjukkan dalam Rajah. 6.
Masalah 2
Bina gambar rajah.
Tambahkan garis aliran logaritma, kuasa dan eksponen pada carta.
Terbitkan persamaan garis arah aliran yang diperolehi, serta nilai kebolehpercayaan anggaran R2 bagi setiap satu daripadanya.
Menggunakan persamaan garis arah aliran, dapatkan data jadual tentang keuntungan perusahaan untuk setiap garis arah aliran untuk 1995-2002.
Buat ramalan keuntungan syarikat 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, kuasa dan eksponen ditambah kepadanya (Rajah 7). Seterusnya, menggunakan persamaan garis arah aliran yang diperolehi, kami mengisi jadual nilai untuk keuntungan perusahaan, termasuk nilai ramalan untuk tahun 2003 dan 2004. (Gamb. 8).
Dalam 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).
Masalah 3
Dengan jadual data mengenai keuntungan perusahaan pengangkutan motor untuk 1995-2002, diberikan dalam tugasan 1, anda mesti melakukan langkah berikut.
Dapatkan siri data untuk garis aliran linear dan eksponen menggunakan fungsi TREND dan GROW.
Menggunakan fungsi TREND dan GROWTH, buat ramalan keuntungan perusahaan untuk tahun 2003 dan 2004.
Bina gambar rajah untuk data asal dan siri data yang terhasil.
Penyelesaian masalah
Mari gunakan lembaran kerja untuk Masalah 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 arahan 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 mengklik butang (Sisipkan Fungsi) pada 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 menjadi formula tatasusunan, gunakan kombinasi kekunci + + .
Formula yang kami 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 perusahaan untuk 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 kombinasi 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).
Siri data diisi dengan cara yang sama menggunakan fungsi GROWTH, yang digunakan dalam analisis kebergantungan tak linear dan berfungsi dengan cara yang sama seperti TREND rakan linearnya.
Rajah 10 menunjukkan jadual dalam mod paparan formula.
Untuk data awal dan siri data yang diperoleh, rajah ditunjukkan dalam Rajah. sebelas.
Masalah 4
Dengan jadual data mengenai penerimaan permohonan untuk perkhidmatan oleh perkhidmatan penghantaran perusahaan pengangkutan motor untuk tempoh dari 1 hingga 11 bulan semasa, anda mesti melakukan tindakan berikut.
Dapatkan siri data untuk regresi linear: menggunakan fungsi SLOPE dan INTERCEPT; menggunakan fungsi LINEST.
Dapatkan satu siri data untuk regresi eksponen menggunakan fungsi LGRFPRIBL.
Menggunakan fungsi di atas, buat ramalan tentang penerimaan permohonan kepada perkhidmatan penghantaran untuk tempoh dari 12 hingga 14 bulan semasa.
Buat gambar rajah untuk siri data asal dan diterima.
Penyelesaian masalah
Ambil perhatian bahawa, tidak seperti fungsi TREND dan GROWTH, tiada satu pun fungsi yang disenaraikan di atas (CERUN, INTERCEPT, LINEST, LGRFPRIB) adalah regresi. Fungsi ini hanya memainkan peranan sokongan, 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 dengan persamaan:
y = mx+b
menggunakan fungsi SLOPE dan INTERCEPT, dengan cerun regresi m ditentukan oleh fungsi SLOPE, dan istilah bebas b oleh fungsi INTERCEPT.
Untuk melakukan ini, kami melakukan tindakan berikut:
masukkan jadual asal ke dalam julat sel A4:B14;
nilai parameter m akan ditentukan dalam sel C19. Pilih fungsi Cerun daripada kategori Statistik; 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 dalam sel C19: =SLOPE(B4:B14,A4:A14);
Menggunakan teknik yang sama, nilai parameter b dalam sel D19 ditentukan. Dan kandungannya akan kelihatan seperti: =SEGMEN(B4:B14,A4:A14). Oleh itu, nilai parameter m dan b yang diperlukan untuk membina regresi linear akan disimpan dalam sel C19, D19, masing-masing;
Seterusnya, 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 semasa 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 dalam julat sel C4:C17. Kami memperoleh siri data yang diperlukan (Rajah 12). Disebabkan oleh fakta bahawa bilangan permintaan adalah integer, anda harus menetapkan format nombor dengan bilangan tempat perpuluhan kepada 0 pada tab Nombor pada tetingkap Format Sel.
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 dalam julat sel C20:D20: =(LINEST(B4:B14,A4:A14)). Hasilnya, kami memperoleh 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 dalam julat sel D4:D17 dan dapatkan siri data yang dikehendaki.
3 . Kami membina regresi eksponen dengan persamaan:
menggunakan fungsi LGRFPRIBL ia dilakukan dengan cara yang sama:
Dalam julat sel C21:D21 kita 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).
Dalam Rajah. Rajah 13 menunjukkan jadual di mana anda boleh melihat fungsi yang kami gunakan dengan julat sel yang diperlukan, serta formula.
Magnitud 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 menggunakan 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 kebarangkalian keyakinan yang diterima, maka nilai R dianggap signifikan. Meja 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 ringkas adalah sama dengan 1 (satu garis lurus sentiasa boleh dilukis melalui 2 titik pada satah). Walau bagaimanapun, jika data eksperimen adalah pembolehubah rawak, nilai R sedemikian harus dipercayai dengan sangat berhati-hati. Biasanya, untuk mendapatkan R yang ketara dan regresi yang boleh dipercayai, mereka berusaha untuk memastikan bahawa bilangan eksperimen dengan ketara melebihi bilangan pekali model (n>k).
Untuk membina model regresi linear anda perlukan:
1) sediakan senarai n baris dan m lajur yang mengandungi data eksperimen (lajur yang mengandungi nilai output Y mestilah yang pertama atau terakhir dalam senarai); Sebagai contoh, mari kita ambil data daripada tugas sebelumnya, menambah lajur yang dipanggil "No Tempoh.", nomborkan 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" dalam menu yang sama dan tandakan kotak pilihan "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 (adalah disyorkan untuk meletakkannya pada lembaran kerja baharu);
4) klik "Ok" dan analisis keputusan.
Kaedah kuasa dua terkecil digunakan untuk menganggar parameter persamaan regresi.Salah satu kaedah untuk mengkaji hubungan stokastik antara ciri ialah analisis regresi.
Analisis regresi ialah terbitan persamaan regresi, dengan bantuan nilai purata pembolehubah rawak (atribut hasil) didapati jika nilai pembolehubah lain (atau lain-lain) (atribut faktor) diketahui. Ia termasuk langkah-langkah berikut:
- pemilihan bentuk sambungan (jenis persamaan regresi analitikal);
- anggaran parameter persamaan;
- penilaian kualiti persamaan regresi analitikal.
Dalam kes perhubungan berpasangan linear, persamaan regresi akan mengambil bentuk: y i =a+b·x i +u i . Parameter a dan b persamaan ini dianggarkan daripada data pemerhatian statistik x dan y. Hasil daripada penilaian tersebut ialah persamaan: , di mana , ialah anggaran parameter a dan b , ialah nilai atribut yang terhasil (pembolehubah) yang diperoleh daripada persamaan regresi (nilai yang dikira).
Selalunya digunakan untuk menganggar parameter kaedah kuasa dua terkecil (LSM).
Kaedah kuasa dua terkecil menyediakan anggaran terbaik (konsisten, cekap dan tidak berat sebelah) bagi parameter persamaan regresi. Tetapi hanya jika andaian tertentu mengenai istilah rawak (u) dan pembolehubah bebas (x) dipenuhi (lihat andaian OLS).
Masalah menganggar parameter persamaan pasangan linear menggunakan kaedah kuasa dua terkecil adalah seperti berikut: untuk mendapatkan anggaran parameter sedemikian, , di mana jumlah sisihan kuasa dua bagi nilai sebenar ciri terhasil - y i daripada nilai yang dikira - adalah minimum.
Secara formal ujian OLS boleh ditulis seperti ini: 
.
Pengelasan kaedah kuasa dua terkecil
- Kaedah kuasa dua terkecil.
- Kaedah kemungkinan maksimum (untuk model regresi linear klasik biasa, kenormalan sisa regresi didalilkan).
- Kaedah OLS kuasa dua terkecil umum digunakan dalam kes autokorelasi ralat dan dalam kes heteroskedastisitas.
- Kaedah kuasa dua terkecil berwajaran ( kes istimewa OLS dengan sisa heteroskedastik).
Mari kita gambarkan maksudnya kaedah klasik segi empat sama terkecil secara grafik. Untuk melakukan ini, kami akan membina plot serakan berdasarkan data cerapan (x i, y i, i=1;n) dalam sistem koordinat segi empat tepat (plot serakan sedemikian dipanggil medan korelasi). Mari cuba pilih garis lurus yang paling hampir dengan titik medan korelasi. Mengikut kaedah kuasa dua terkecil, garisan dipilih supaya jumlah kuasa dua jarak menegak antara titik medan korelasi dan garis ini adalah minimum. 
Notasi matematik untuk masalah ini: 
.
Nilai y i dan x i =1...n diketahui oleh kami; ini adalah data pemerhatian. Dalam fungsi S mereka mewakili pemalar. Pembolehubah dalam fungsi ini ialah anggaran yang diperlukan bagi parameter - , . Untuk mencari minimum fungsi dua pembolehubah, adalah perlu untuk mengira derivatif separa fungsi ini untuk setiap parameter dan menyamakannya dengan sifar, i.e. 
.
Hasilnya, kita memperoleh sistem 2 persamaan linear normal: 
Memutuskan sistem ini, kami dapati anggaran parameter yang diperlukan: 
Ketepatan pengiraan parameter persamaan regresi boleh disemak dengan membandingkan jumlah (mungkin terdapat beberapa percanggahan disebabkan pembundaran pengiraan).
Untuk mengira anggaran parameter, anda boleh membina Jadual 1.
Tanda pekali regresi b menunjukkan arah perhubungan (jika b >0, perhubungan adalah langsung, jika b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Secara formal, nilai parameter a ialah nilai purata y dengan x sama dengan sifar. Jika faktor-atribut tidak dan tidak boleh mempunyai nilai sifar, maka tafsiran parameter a di atas tidak masuk akal.
Menilai keakraban hubungan antara ciri
dijalankan menggunakan pekali korelasi pasangan linear - r x,y. Ia boleh dikira menggunakan formula: 
. Selain itu, pekali korelasi pasangan linear boleh ditentukan melalui pekali regresi b: 
.
Julat nilai yang boleh diterima bagi pekali korelasi pasangan linear adalah dari -1 hingga +1. Tanda pekali korelasi menunjukkan arah hubungan. Jika r x, y >0, maka sambungan adalah terus; jika r x, y<0, то связь обратная.
Sekiranya pekali ini hampir dengan kesatuan dalam magnitud, maka hubungan antara ciri-ciri boleh ditafsirkan sebagai satu linear yang agak rapat. Jika modulnya adalah sama dengan satu ê r x , y ê =1, maka hubungan antara ciri-ciri adalah linear berfungsi. Jika ciri x dan y tidak bersandar secara linear, maka r x,y adalah hampir dengan 0.
Untuk mengira r x,y, anda juga boleh menggunakan Jadual 1.
Jadual 1
| N pemerhatian | x i | y i | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 y 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| n | x n | y n | x n y n | ||
| Jumlah Lajur | ∑x | ∑y | ∑xy | ||
| Nilai purata |
 |
 |
,
dengan d 2 ialah varians y yang dijelaskan oleh persamaan regresi;
e 2 - varians sisa (tidak dijelaskan oleh persamaan regresi) bagi y;
s 2 y - jumlah (jumlah) varians bagi y.
Pekali penentuan mencirikan perkadaran variasi (serakan) atribut terhasil y dijelaskan oleh regresi (dan, akibatnya, faktor x) dalam jumlah variasi (serakan) y. Pekali penentuan R 2 yx mengambil nilai dari 0 hingga 1. Sehubungan itu, nilai 1-R 2 yx mencirikan bahagian varians y yang disebabkan oleh pengaruh faktor lain yang tidak diambil kira dalam model dan ralat spesifikasi.
Dengan regresi linear berpasangan, R 2 yx =r 2 yx.
