Metode kuadrat terkecil (LSM) didasarkan pada meminimalkan jumlah deviasi kuadrat dari fungsi yang dipilih dari data yang diteliti. Dalam artikel ini, kami memperkirakan data yang tersedia menggunakan fungsi linierkamu = sebuah x + b .

Metode kuadrat terkecil(Bahasa inggris) Biasa Paling sedikit kotak , OLS) adalah salah satu metode dasar analisis regresi dalam hal memperkirakan parameter yang tidak diketahui model regresi sesuai dengan sampel data.

Pertimbangkan pendekatan dengan fungsi yang bergantung hanya pada satu variabel:

• Linear: y=ax+b (artikel ini)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Catatan: Kasus aproksimasi polinomial dari derajat ke-3 sampai ke-6 dibahas dalam artikel ini. Pendekatan oleh polinomial trigonometri dipertimbangkan di sini.

Ketergantungan linier

Kami tertarik pada hubungan 2 variabel X dan kamu. Ada anggapan bahwa kamu tergantung pada X menurut hukum linier kamu = kapak + b. Untuk menentukan parameter hubungan ini, peneliti melakukan observasi: untuk setiap nilai xi, dilakukan pengukuran y i (lihat file contoh). Dengan demikian, biarkan ada 20 pasang nilai (х i ; y i).

Catatan: Jika berubah selangkah demi selangkah X konstan, maka untuk membangun petak sebar dapat digunakan, jika tidak, maka Anda perlu menggunakan tipe bagan burik .

Dari diagram terlihat jelas bahwa hubungan antar variabel mendekati linier. Untuk memahami mana dari banyak garis lurus yang paling "benar" menggambarkan hubungan antar variabel, perlu untuk menentukan kriteria dengan mana garis akan dibandingkan.

Sebagai kriteria seperti itu, kami menggunakan ekspresi:

di mana ŷ saya = sebuah * x saya + b ; n – jumlah pasangan nilai (dalam kasus kami n=20)

Ekspresi di atas adalah jumlah dari jarak kuadrat antara nilai-nilai yang diamati dari y i dan i dan sering dilambangkan sebagai SSE ( jumlah dari kuadrat kesalahan (sisa), jumlah kesalahan kuadrat (sisa)) .

Metode kuadrat terkecil adalah memilih garis seperti itu ŷ = kapak + b, yang ekspresi di atas mengambil nilai minimum.

Catatan: Garis apa pun dalam ruang dua dimensi ditentukan secara unik oleh nilai 2 parameter: sebuah (kemiringan) dan b (menggeser).

Diyakini bahwa semakin kecil jumlah jarak kuadrat, semakin baik garis yang sesuai mendekati data yang tersedia dan selanjutnya dapat digunakan untuk memprediksi nilai y dari variabel x. Jelas bahwa meskipun pada kenyataannya tidak ada hubungan antar variabel atau hubungan tidak linier, LSM tetap akan memilih jalur yang “terbaik”. Dengan demikian, LSM tidak mengatakan apa-apa tentang keberadaan hubungan nyata variabel, metode ini hanya memungkinkan Anda untuk memilih parameter fungsi tersebut sebuah dan b , yang ekspresi di atas minimal.

Setelah melakukan operasi matematika yang tidak terlalu rumit (lihat untuk lebih jelasnya), Anda dapat menghitung parameternya sebuah dan b :

Seperti yang dapat dilihat dari rumus, parameter sebuah adalah rasio kovarians dan , jadi dalam MS EXCEL untuk menghitung parameter sebuah Anda dapat menggunakan rumus berikut (lihat contoh file sheet Linear):

= COVAR(B26:B45;C26:C45)/ VAR.G(B26:B45) atau

= COVARIASI.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Juga untuk menghitung parameter sebuah Anda dapat menggunakan rumus = SLEPE(C26:C45;B26:B45). Untuk parameter b gunakan rumus = INTERCUT(C26:C45;B26:B45) .

Dan terakhir, fungsi LINEST() memungkinkan Anda menghitung kedua parameter sekaligus. Untuk memasukkan rumus LINEST(C26:C45;B26:B45) pilih 2 sel berturut-turut dan tekan CTRL + MENGGESER + MEMASUKI(lihat artikel tentang). Sel kiri akan mengembalikan nilainya sebuah , di kanan b .

Catatan: Agar tidak mengacaukan input rumus array Anda juga perlu menggunakan fungsi INDEX(). Rumus = INDEKS(LINEST(C26:C45,B26:B45),1) atau hanya = LINEST(C26:C45;B26:B45) akan mengembalikan parameter yang bertanggung jawab atas kemiringan garis, mis. sebuah . Rumus = INDEKS(LINEST(C26:C45,B26:B45),2) akan mengembalikan parameter yang bertanggung jawab atas perpotongan garis dengan sumbu Y, mis. b .

Setelah menghitung parameter, petak sebar garis dapat ditarik.

Cara lain untuk menggambar garis lurus menggunakan metode kuadrat terkecil adalah alat grafik garis tren. Untuk melakukan ini, pilih diagram, pilih dari menu tab tata letak, di Analisis grup klik garis tren, kemudian Pendekatan linier .

Dengan mencentang kotak "tampilkan persamaan dalam diagram" di kotak dialog, Anda dapat memastikan bahwa parameter yang ditemukan di atas cocok dengan nilai dalam diagram.

Catatan: Agar parameter cocok, jenis bagan harus . Faktanya adalah ketika membuat diagram Jadwal nilai sumbu x tidak dapat diatur oleh pengguna (pengguna hanya dapat menentukan label yang tidak memengaruhi lokasi titik). Alih-alih nilai X, urutan 1 digunakan; 2; 3; … (untuk penomoran kategori). Oleh karena itu, jika membangun garis tren pada diagram tipe Jadwal, maka nilai urutan ini akan digunakan sebagai ganti nilai sebenarnya dari X, yang akan menyebabkan hasil yang salah (kecuali, tentu saja, nilai sebenarnya dari X tidak cocok dengan urutan 1; 2 ; 3; ...).

Nah, di tempat kerja mereka melapor ke inspeksi, artikel itu ditulis di rumah untuk konferensi - sekarang Anda bisa menulis di blog. Ketika saya sedang memproses data saya, saya menyadari bahwa saya tidak bisa tidak menulis tentang add-in yang sangat keren dan penting di Excel, yang disebut . Jadi artikel akan dikhususkan untuk add-in khusus ini, dan saya akan memberi tahu Anda tentang hal itu menggunakan contoh penggunaan metode kuadrat terkecil(LSM) untuk mencari koefisien persamaan yang tidak diketahui dalam deskripsi data eksperimen.

Cara mengaktifkan add-on "mencari solusi"

Pertama, mari kita cari tahu cara mengaktifkan add-on ini.

1. Buka menu "File" dan pilih "Opsi Excel"

2. Di jendela yang muncul, pilih "Search for a solution" dan klik "go".

3. Di jendela berikutnya, beri tanda centang di depan item "search for a solution" dan klik "OK".

4. Add-in diaktifkan - sekarang dapat ditemukan di item menu "Data".

Metode kuadrat terkecil

Sekarang secara singkat tentang metode kuadrat terkecil (LSM) dan di mana itu bisa diterapkan.

Katakanlah kita memiliki kumpulan data setelah kita melakukan beberapa percobaan di mana kita mempelajari efek dari nilai X pada nilai Y.

Kita ingin menggambarkan pengaruh ini secara matematis, sehingga nanti kita dapat menggunakan rumus ini dan mengetahui bahwa jika kita mengubah nilai X sebanyak itu, kita akan mendapatkan nilai Y ini dan itu ...

Mari kita ambil contoh super sederhana (lihat gambar).

Tidak salah lagi bahwa titik-titik itu terletak satu demi satu seolah-olah dalam garis lurus, dan oleh karena itu kami berasumsi dengan aman bahwa ketergantungan kami dijelaskan oleh fungsi linier y=kx+b. Pada saat yang sama, kami yakin bahwa ketika X sama dengan nol, nilai Y juga sama dengan nol. Artinya, fungsi yang menggambarkan ketergantungan akan lebih sederhana: y=kx (ingat kurikulum sekolah).

Secara umum, kita harus mencari koefisien k. Inilah yang akan kita lakukan dengan MNC menggunakan add-on "cari solusi".

Metodenya adalah (di sini - perhatian: Anda perlu memikirkannya) jumlah perbedaan kuadrat antara yang diperoleh secara eksperimental dan nilai yang dihitung terkait adalah minimal. Yaitu, ketika X1=1 nilai terukur sebenarnya Y1=4.6, dan y1=f (x1) yang dihitung adalah 4, kuadrat selisihnya adalah (y1-Y1)^2=(4-4.6)^2= 0,36 . Sama dengan berikut ini: ketika X2=2, nilai terukur sebenarnya Y2=8,1, dan y2 yang dihitung adalah 8, kuadrat selisihnya adalah (y2-Y2)^2=(8-8,1)^2=0,01. Dan jumlah semua kotak ini harus sekecil mungkin.

Jadi, mari kita mulai pelatihan penggunaan LSM dan Add-in Excel "mencari solusi" .

Penerapan solusi pencarian tambahan

1. Jika Anda tidak mengaktifkan add-on "cari solusi", kembali ke langkah Cara mengaktifkan add-on "mencari solusi" dan mengaktifkan 🙂

2. Di sel A1, masukkan nilai "1". Satuan ini akan menjadi aproksimasi pertama terhadap nilai sebenarnya dari koefisien (k) dari ketergantungan fungsional kita y=kx.

3. Di kolom B kami memiliki nilai parameter X, di kolom C - nilai parameter Y. Di sel kolom D kami memasukkan rumus: "faktor k dikalikan nilai X". Misalnya, di sel D1, masukkan "=A1*B1", di sel D2, masukkan "=A1*B2", dan seterusnya.

4. Kami percaya bahwa koefisien k sama dengan satu dan fungsi f (x) \u003d y \u003d 1 * x adalah pendekatan pertama untuk solusi kami. Kita dapat menghitung jumlah selisih kuadrat antara nilai terukur Y dan yang dihitung menggunakan rumus y=1*x. Kita dapat melakukan semua ini secara manual dengan memasukkan referensi sel yang sesuai ke dalam rumus: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... dll. Pada akhirnya kita salah dan mengerti bahwa kita telah kehilangan banyak waktu.Di Excel, untuk menghitung jumlah perbedaan kuadrat, ada rumus khusus, "SUMQDIFF", yang akan melakukan segalanya untuk kita.Mari kita masukkan ke dalam sel A2 dan atur data awal: rentang nilai terukur Y (kolom C) dan rentang nilai Y yang dihitung (kolom D).

4. Jumlah perbedaan kuadrat dihitung - sekarang buka tab "Data" dan pilih "Cari solusi".

5. Pada menu yang muncul, pilih sel A1 sebagai sel yang akan diubah (sel dengan koefisien k).

6. Sebagai target, pilih sel A2 dan atur kondisi "set equal to the minimum value." Ingatlah bahwa ini adalah sel tempat kita menghitung jumlah selisih kuadrat antara nilai yang dihitung dan diukur, dan jumlah ini harus minimal. Kami menekan "eksekusi".

7. Koefisien k dipilih. Sekarang dapat dilihat bahwa nilai yang dihitung sekarang sangat dekat dengan yang diukur.

P.S.

Secara umum, tentu saja, untuk perkiraan data eksperimen di Excel, ada alat khusus yang memungkinkan Anda mendeskripsikan data menggunakan fungsi linier, eksponensial, pangkat, dan polinomial, sehingga Anda sering dapat melakukannya tanpa add-on "Mencari solusi". Saya berbicara tentang semua metode perkiraan ini di artikel saya, jadi jika Anda tertarik, lihatlah. Tetapi ketika datang ke beberapa fungsi eksotis dengan satu koefisien yang tidak diketahui atau masalah optimasi, maka di sini suprastruktur sebaik mungkin.

Add-in "mencari solusi" dapat digunakan untuk tugas lain, yang utama adalah memahami esensi: ada sel tempat kami memilih nilai, dan ada sel target di mana kondisi ditetapkan untuk memilih parameter yang tidak diketahui.
Itu saja! Pada artikel selanjutnya saya akan menceritakan dongeng tentang liburan, jadi agar tidak ketinggalan rilis artikel,

4.1. Menggunakan fungsi bawaan

perhitungan koefisien regresi dilakukan dengan menggunakan fungsi

LINEST(Nilai_y; Nilai_x; Konsto; statistik),

Nilai_y- array nilai y,

Nilai_x- array nilai opsional x jika array X dihilangkan, diasumsikan bahwa ini adalah array (1;2;3;...) dengan ukuran yang sama dengan Nilai_y,

Konsto- nilai boolean yang menunjukkan apakah konstanta diperlukan b sama dengan 0. Jika Konsto memiliki arti BENAR atau dihilangkan, maka b dihitung dengan cara biasa. Jika argumen Konsto adalah SALAH, maka b diasumsikan 0 dan nilainya sebuah dipilih sehingga relasi y = kapak.

Statistik- nilai boolean yang menunjukkan apakah statistik regresi tambahan diperlukan untuk dikembalikan. Jika argumen Statistik memiliki arti BENAR, maka fungsi LINEST mengembalikan statistik regresi tambahan. Jika argumen Statistik memiliki arti SALAH atau dihilangkan, maka fungsi LINEST hanya mengembalikan koefisien sebuah dan permanen b.

Harus diingat bahwa hasil dari fungsi BARIS() adalah satu set nilai - sebuah array.

Untuk perhitungan koefisien korelasi fungsi digunakan

CORREL(Array1;Array2),

mengembalikan nilai koefisien korelasi, di mana Array1- array nilai kamu, Array2- array nilai x. Array1 dan Array2 harus sama ukurannya.

CONTOH 1. Kecanduan kamu(x) disajikan dalam tabel. Membangun Garis regresi dan hitung koefisien korelasi.

kamu		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Mari masukkan tabel nilai ke dalam lembar MS Excel dan buat plot pencar. Lembar kerja akan mengambil bentuk yang ditunjukkan pada Gambar. 2.

Untuk menghitung nilai koefisien regresi sebuah dan b pilih sel A7: B7, mari kita beralih ke wizard fungsi dan dalam kategori Statistik memilih fungsi LINEST. Isi kotak dialog yang muncul seperti pada Gambar. 3 dan tekan Oke.

Akibatnya, nilai yang dihitung hanya akan muncul di sel A6(Gbr. 4). Agar nilai muncul di sel B6 Anda harus masuk ke mode edit (kunci F2) lalu tekan kombinasi tombol CTRL+SHIFT+ENTER.

Untuk menghitung nilai koefisien korelasi per sel C6 rumus berikut diperkenalkan:

C7=CORREL(B3:J3;B2:J2).

Mengetahui koefisien regresi sebuah dan b menghitung nilai fungsi kamu=kapak+b untuk diberikan x. Untuk melakukan ini, kami memperkenalkan rumus

B5=$A$7*B2+$B$7

dan salin ke jangkauan 5:J5(Gbr. 5).

Mari kita plot garis regresi pada diagram. Pilih titik eksperimental pada grafik, klik kanan dan pilih perintah data awal. Pada kotak dialog yang muncul (Gbr. 5), pilih tab Baris dan klik tombol Menambahkan. Isi kolom input, seperti yang ditunjukkan pada Gambar. 6 dan tekan tombol Oke. Garis regresi akan ditambahkan ke plot data eksperimen. Secara default, grafiknya akan ditampilkan sebagai titik-titik yang tidak terhubung dengan garis halus.

Untuk mengubah tampilan garis regresi, lakukan langkah-langkah berikut. Klik kanan pada titik yang menggambarkan grafik garis, pilih perintah Jenis bagan dan atur jenis plot pencar, seperti yang ditunjukkan pada Gambar. 7.

Jenis garis, warna dan ketebalan dapat diubah sebagai berikut. Pilih garis pada diagram, tekan tombol kanan mouse dan pilih perintah di menu konteks Format Seri Data… Selanjutnya, buat pengaturan, misalnya, seperti yang ditunjukkan pada Gambar. delapan.

Sebagai hasil dari semua transformasi, kami mendapatkan grafik data eksperimen dan garis regresi dalam satu area grafik (Gbr. 9).

4.2. Menggunakan garis tren.

Konstruksi berbagai perkiraan dependensi di MS Excel diimplementasikan sebagai properti bagan - garis tren.

CONTOH 2. Sebagai hasil dari percobaan, beberapa ketergantungan tabel ditentukan.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Pilih dan bangun ketergantungan yang mendekati. Bangun grafik dependensi analitis tabular dan pas.

Penyelesaian masalah dapat dibagi ke dalam tahapan berikut: input data awal, konstruksi plot pencar dan penambahan garis tren ke plot ini.

Mari kita pertimbangkan proses ini secara rinci. Mari kita masukkan data awal ke dalam lembar kerja dan plot data eksperimen. Selanjutnya, pilih titik eksperimental pada grafik, klik kanan dan gunakan perintah Menambahkan aku garis tren(Gbr. 10).

Kotak dialog yang muncul memungkinkan Anda untuk membangun ketergantungan perkiraan.

Tab pertama (Gbr. 11) dari jendela ini menunjukkan jenis perkiraan ketergantungan.

Yang kedua (Gbr. 12) mendefinisikan parameter konstruksi:

nama ketergantungan yang mendekati;

Prakiraan maju (mundur) aktif n unit (parameter ini menentukan berapa banyak unit maju (mundur) yang diperlukan untuk memperpanjang garis tren);

apakah akan menunjukkan titik perpotongan kurva dengan garis y = konstan;

apakah akan menampilkan fungsi aproksimasi pada diagram atau tidak (tunjukkan persamaan pada parameter diagram);

Apakah akan menempatkan nilai deviasi standar pada diagram atau tidak (parameter untuk menempatkan nilai keandalan aproksimasi pada diagram).

Mari kita memilih polinomial derajat kedua sebagai ketergantungan pendekatan (Gbr. 11) dan menurunkan persamaan yang menggambarkan polinomial ini pada grafik (Gbr. 12). Diagram yang dihasilkan ditunjukkan pada gambar. tigabelas.

Demikian pula dengan garis tren anda dapat memilih parameter dependensi seperti

linier kamu=ax+b,

logaritma kamu=sebuah ln(x)+b,

eksponensial kamu=aeb,

kekuatan kamu=a x b,

polinomial kamu=ax 2 +b∙x+c, kamu=ax 3 +b∙x 2 +c∙x+d dan seterusnya, hingga dan termasuk polinomial derajat ke-6,

Penyaringan linier.

4.3. Menggunakan alat analisis opsi: Menemukan solusi.

Yang cukup menarik adalah implementasi di MS Excel dari pemilihan parameter ketergantungan fungsional dengan metode kuadrat terkecil menggunakan alat analisis opsi: Cari solusi. Teknik ini memungkinkan Anda untuk memilih parameter fungsi apa pun. Mari kita pertimbangkan kemungkinan ini pada contoh masalah berikut.

CONTOH 3. Sebagai hasil dari percobaan, ketergantungan z(t) disajikan pada tabel

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Pilih koefisien ketergantungan Z(t)=Pada 4 +Bt 3 +Ct 2 +Dt+K dengan metode kuadrat terkecil.

Soal ini ekuivalen dengan soal mencari minimum sebuah fungsi dari lima variabel

Pertimbangkan proses pemecahan masalah optimasi (Gbr. 14).

Biarkan nilai-nilai TETAPI, PADA, Dengan, D dan Ke disimpan dalam sel A7: E7. Hitung nilai teoritis dari fungsi Z(t)=At4+Bt3+Ct2+Dt+K untuk diberikan t(B2:J2). Untuk melakukan ini, di dalam sel B4 masukkan nilai fungsi pada titik pertama (sel B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Salin rumus ini ke dalam rentang 4:J4 dan dapatkan nilai fungsi yang diharapkan pada titik-titik, yang absisnya disimpan dalam sel B2:J2.

ke sel B5 kami memperkenalkan rumus yang menghitung kuadrat dari perbedaan antara titik eksperimental dan yang dihitung:

B5=(B4-B3)^2,

dan salin ke jangkauan 5:J5. Di dalam sel F7 kami akan menyimpan kesalahan kuadrat total (10). Untuk melakukan ini, kami memperkenalkan rumus:

F7 = SUM(B5:J5).

Mari kita gunakan perintah Service®Cari solusi dan memecahkan masalah optimasi tanpa kendala. Isi kolom input yang sesuai di kotak dialog yang ditunjukkan pada Gambar. 14 dan tekan tombol Lari. Jika solusi ditemukan, jendela yang ditunjukkan pada Gambar. limabelas.

Hasil dari blok keputusan akan menjadi output ke sel A7: E7nilai parameter fungsi Z(t)=At4+Bt3+Ct2+Dt+K. Dalam sel B4:J4 kita mendapatkan nilai fungsi yang diharapkan di titik awal. Di dalam sel F7 akan disimpan kesalahan kuadrat total.

Anda dapat menampilkan titik eksperimental dan garis yang dipasang di area grafik yang sama jika Anda memilih rentangnya B2:J4, panggilan Pemandu Bagan, lalu format tampilan grafik yang dihasilkan.

Beras. 17 menampilkan lembar kerja MS Excel setelah perhitungan dilakukan.

Yang menemukan aplikasi terluas di berbagai bidang sains dan praktik. Bisa fisika, kimia, biologi, ekonomi, sosiologi, psikologi dan lain sebagainya. Dengan kehendak takdir, saya sering harus berurusan dengan ekonomi, dan oleh karena itu hari ini saya akan mengaturkan Anda tiket ke negara yang luar biasa bernama ekonometrika=) … Bagaimana Anda tidak menginginkannya?! Sangat bagus di sana - Anda hanya perlu memutuskan! …Tapi yang mungkin Anda inginkan adalah belajar bagaimana memecahkan masalah kuadrat terkecil. Dan terutama pembaca yang rajin akan belajar menyelesaikannya tidak hanya secara akurat, tetapi juga SANGAT CEPAT ;-) Tapi pertama-tama pernyataan umum dari masalah+ contoh terkait:

Biarkan indikator dipelajari di beberapa bidang studi yang memiliki ekspresi kuantitatif. Pada saat yang sama, ada banyak alasan untuk percaya bahwa indikator bergantung pada indikator. Asumsi ini dapat berupa hipotesis ilmiah dan berdasarkan akal sehat dasar. Namun, mari kita kesampingkan sains, dan jelajahi area yang lebih menggugah selera - yaitu, toko kelontong. Dilambangkan dengan:

– ruang ritel toko kelontong, sq.m.,
- omset tahunan toko kelontong, juta rubel.

Cukup jelas bahwa semakin besar area toko, semakin besar omsetnya dalam banyak kasus.

Misalkan setelah melakukan pengamatan / eksperimen / perhitungan / menari dengan rebana, kami memiliki data numerik yang kami miliki:

Dengan toko kelontong, saya pikir semuanya jelas: - ini adalah area toko pertama, - omset tahunannya, - area toko ke-2, - omset tahunannya, dll. Ngomong-ngomong, sama sekali tidak perlu memiliki akses ke materi rahasia - penilaian omset yang cukup akurat dapat diperoleh dengan menggunakan statistik matematika. Namun, jangan terganggu, kursus spionase komersial sudah dibayar =)

Data tabular juga dapat ditulis dalam bentuk titik dan digambarkan dengan cara yang biasa bagi kita. sistem kartesius .

Mari kita jawab pertanyaan penting: berapa banyak poin yang diperlukan untuk studi kualitatif?

Lebih besar lebih baik. Set minimum yang dapat diterima terdiri dari 5-6 poin. Selain itu, dengan jumlah data yang sedikit, hasil “abnormal” tidak boleh dimasukkan dalam sampel. Jadi, misalnya, toko elit kecil dapat membantu pesanan yang lebih besar daripada "rekan mereka", sehingga mendistorsi pola umum yang perlu ditemukan!

Jika cukup sederhana, kita perlu memilih fungsi , jadwal yang melewati sedekat mungkin ke titik . Fungsi seperti ini disebut mendekati (perkiraan - perkiraan) atau fungsi teoritis . Secara umum, di sini "penipu" yang jelas segera muncul - polinomial tingkat tinggi, yang grafiknya melewati SEMUA titik. Tetapi opsi ini rumit, dan seringkali tidak benar. (karena grafik akan "berputar" sepanjang waktu dan kurang mencerminkan tren utama).

Dengan demikian, fungsi yang diinginkan harus cukup sederhana dan pada saat yang sama mencerminkan ketergantungan secara memadai. Seperti yang Anda duga, salah satu metode untuk menemukan fungsi seperti itu disebut kuadrat terkecil. Pertama, mari kita menganalisis esensinya secara umum. Biarkan beberapa fungsi mendekati data eksperimen:


Bagaimana cara mengevaluasi keakuratan pendekatan ini? Mari kita juga menghitung perbedaan (penyimpangan) antara nilai eksperimental dan fungsional (kami mempelajari gambarnya). Pikiran pertama yang muncul di benak adalah untuk memperkirakan seberapa besar jumlahnya, tetapi masalahnya adalah perbedaannya bisa negatif. (Sebagai contoh, ) dan penyimpangan-penyimpangan sebagai akibat dari penjumlahan tersebut akan saling meniadakan. Oleh karena itu, sebagai perkiraan keakuratan aproksimasi, ia menyarankan dirinya untuk mengambil jumlah modul penyimpangan:

atau dalam bentuk terlipat: (tiba-tiba, siapa yang tidak tahu: adalah ikon penjumlahan, dan merupakan variabel tambahan-“penghitung”, yang mengambil nilai dari 1 hingga ).

Dengan mendekati titik-titik percobaan dengan fungsi yang berbeda, kita akan memperoleh nilai yang berbeda dari , dan jelas bahwa di mana jumlah ini lebih kecil, fungsi itu lebih akurat.

Metode seperti itu ada dan disebut metode modulus terkecil. Namun, dalam praktiknya, itu menjadi jauh lebih luas. metode kuadrat terkecil, di mana kemungkinan nilai negatif dihilangkan bukan oleh modulus, tetapi dengan mengkuadratkan deviasi:

, setelah itu upaya diarahkan pada pemilihan fungsi sedemikian rupa sehingga jumlah deviasi kuadrat adalah sekecil mungkin. Sebenarnya, itulah nama metodenya.

Dan sekarang kita kembali ke poin penting lainnya: seperti disebutkan di atas, fungsi yang dipilih seharusnya cukup sederhana - tetapi ada juga banyak fungsi seperti itu: linier , hiperbolis, eksponensial, logaritma, kuadrat dll. Dan, tentu saja, di sini saya ingin segera "mengurangi bidang kegiatan". Kelas fungsi apa yang harus dipilih untuk penelitian? Teknik primitif tapi efektif:

- Cara termudah untuk menarik poin pada gambar dan menganalisis lokasi mereka. Jika mereka cenderung berada dalam garis lurus, maka Anda harus mencari persamaan garis lurus dengan nilai optimal dan . Dengan kata lain, tugasnya adalah menemukan koefisien TERSEBUT - sehingga jumlah deviasi kuadrat adalah yang terkecil.

Jika titik-titik itu terletak, misalnya, di sepanjang hiperbola, maka jelas bahwa fungsi linier akan memberikan aproksimasi yang buruk. Dalam hal ini, kami mencari koefisien yang paling "menguntungkan" untuk persamaan hiperbola - mereka yang memberikan jumlah kuadrat minimum .

Sekarang perhatikan bahwa dalam kedua kasus yang kita bicarakan fungsi dua variabel, yang argumennya adalah opsi ketergantungan yang dicari:

Dan pada intinya, kita perlu memecahkan masalah standar - untuk menemukan minimal fungsi dari dua variabel.

Ingat contoh kita: misalkan titik "toko" cenderung terletak pada garis lurus dan ada banyak alasan untuk mempercayai kehadirannya ketergantungan linier omset dari area perdagangan. Mari kita cari koefisien TERSEBUT "a" dan "menjadi" sehingga jumlah deviasi kuadrat adalah yang terkecil. Semuanya seperti biasa - pertama turunan parsial dari orde pertama. Berdasarkan aturan linearitas anda dapat membedakan tepat di bawah ikon jumlah:

Jika Anda ingin menggunakan informasi ini untuk esai atau kursus, saya akan sangat berterima kasih atas tautan dalam daftar sumber, Anda tidak akan menemukan perhitungan terperinci seperti itu di mana pun:

Mari kita membuat sistem standar:

Kami mengurangi setiap persamaan dengan "dua" dan, sebagai tambahan, "memecah" jumlahnya:

Catatan : menganalisis secara independen mengapa "a" dan "menjadi" dapat dikeluarkan dari ikon jumlah. Ngomong-ngomong, secara formal ini bisa dilakukan dengan penjumlahan

Mari kita tulis ulang sistem dalam bentuk "terapan":

setelah itu algoritma untuk memecahkan masalah kita mulai ditarik:

Apakah kita tahu koordinat titik-titiknya? Kita tahu. jumlah bisa kita temukan? Mudah. Kami membuat yang paling sederhana sistem dua persamaan linier dengan dua yang tidak diketahui("a" dan "beh"). Kami memecahkan sistem, misalnya, Metode Cramer, menghasilkan titik stasioner . memeriksa kondisi yang cukup untuk ekstrim, kita dapat memverifikasi bahwa pada titik ini fungsinya mencapai tepat minimum. Verifikasi dikaitkan dengan perhitungan tambahan dan oleh karena itu kami akan meninggalkannya di belakang layar. (jika perlu, bingkai yang hilang dapat dilihat). Kami menarik kesimpulan akhir:

Fungsi jalan terbaik (setidaknya dibandingkan dengan fungsi linier lainnya) membawa poin eksperimental lebih dekat . Secara kasar, grafiknya melewati sedekat mungkin ke titik-titik ini. Dalam tradisi ekonometrika fungsi aproksimasi yang dihasilkan juga disebut persamaan regresi linier berpasangan .

Masalah yang sedang dipertimbangkan sangat penting secara praktis. Dalam situasi dengan contoh kita, persamaan memungkinkan Anda untuk memprediksi omset seperti apa ("yg") akan berada di toko dengan satu atau lain nilai area penjualan (satu atau arti lain dari "x"). Ya, ramalan yang dihasilkan hanya akan menjadi ramalan, tetapi dalam banyak kasus ternyata cukup akurat.

Saya akan menganalisis hanya satu masalah dengan angka "nyata", karena tidak ada kesulitan di dalamnya - semua perhitungan berada di level kurikulum sekolah di kelas 7-8. Dalam 95 persen kasus, Anda akan diminta untuk mencari fungsi linier saja, tetapi di akhir artikel saya akan menunjukkan bahwa tidak sulit lagi menemukan persamaan untuk hiperbola optimal, eksponen, dan beberapa fungsi lainnya.

Faktanya, tetap mendistribusikan barang yang dijanjikan - sehingga Anda belajar bagaimana menyelesaikan contoh-contoh seperti itu tidak hanya secara akurat, tetapi juga dengan cepat. Kami mempelajari standar dengan cermat:

Tugas

Sebagai hasil dari mempelajari hubungan antara dua indikator, pasangan angka berikut diperoleh:

Dengan menggunakan metode kuadrat terkecil, temukan fungsi linier yang paling mendekati fungsi empiris (berpengalaman) data. Buatlah gambar di mana, dalam sistem koordinat persegi panjang Cartesian, plot titik-titik eksperimental dan grafik fungsi aproksimasi . Temukan jumlah deviasi kuadrat antara nilai empiris dan teoritis. Cari tahu apakah fungsinya lebih baik (dalam hal metode kuadrat terkecil) perkiraan titik percobaan.

Perhatikan bahwa nilai "x" adalah nilai alami, dan ini memiliki makna makna yang khas, yang akan saya bicarakan nanti; tetapi mereka, tentu saja, dapat berupa pecahan. Selain itu, tergantung pada konten tugas tertentu, nilai "X" dan "G" dapat sepenuhnya atau sebagian negatif. Nah, kami telah diberi tugas "tanpa wajah", dan kami memulainya keputusan:

Kami menemukan koefisien fungsi optimal sebagai solusi untuk sistem:

Untuk keperluan notasi yang lebih ringkas, variabel “penghitung” dapat dihilangkan, karena sudah jelas bahwa penjumlahan dilakukan dari 1 hingga .

Lebih mudah untuk menghitung jumlah yang diperlukan dalam bentuk tabel:


Perhitungan dapat dilakukan pada mikrokalkulator, tetapi jauh lebih baik menggunakan Excel - lebih cepat dan tanpa kesalahan; tonton video singkatnya:

Dengan demikian, kita mendapatkan yang berikut sistem:

Di sini Anda dapat mengalikan persamaan kedua dengan 3 dan kurangi suku ke-2 dari suku persamaan ke-1 dengan suku. Tapi ini keberuntungan - dalam praktiknya, sistem seringkali tidak berbakat, dan dalam kasus seperti itu menghemat Metode Cramer:
, sehingga sistem memiliki solusi yang unik.

Mari kita lakukan pemeriksaan. Saya mengerti bahwa saya tidak mau, tetapi mengapa melewatkan kesalahan di mana Anda benar-benar tidak dapat melewatkannya? Substitusikan solusi yang ditemukan ke ruas kiri setiap persamaan sistem:

Bagian yang tepat dari persamaan yang sesuai diperoleh, yang berarti bahwa sistem diselesaikan dengan benar.

Jadi, fungsi aproksimasi yang diinginkan: – dari semua fungsi linier data eksperimen paling baik didekati olehnya.

Tidak seperti lurus ketergantungan omset toko pada luasnya, ketergantungan yang ditemukan adalah membalik (prinsip "semakin banyak - semakin sedikit"), dan fakta ini segera terungkap oleh yang negatif koefisien sudut. Fungsi memberitahu kita bahwa dengan peningkatan indikator tertentu sebesar 1 unit, nilai indikator dependen menurun rata-rata sebesar 0,65 unit. Seperti yang mereka katakan, semakin tinggi harga soba, semakin sedikit yang dijual.

Untuk memplot fungsi aproksimasi, kami menemukan dua nilainya:

dan jalankan gambarnya:


Garis yang dibangun disebut garis tren (yaitu, garis tren linier, yaitu dalam kasus umum, tren tidak harus berupa garis lurus). Semua orang akrab dengan ungkapan "menjadi tren", dan saya pikir istilah ini tidak perlu komentar tambahan.

Hitung jumlah simpangan kuadrat antara nilai empiris dan teoritis. Secara geometris, ini adalah jumlah kuadrat dari panjang segmen "merah" (dua di antaranya sangat kecil sehingga Anda bahkan tidak dapat melihatnya).

Mari kita rangkum perhitungannya dalam sebuah tabel:


Mereka dapat dilakukan lagi secara manual, untuk berjaga-jaga saya akan memberikan contoh untuk poin pertama:

tetapi jauh lebih efisien untuk melakukan cara yang sudah diketahui:

Mari kita ulangi: apa arti dari hasil Dari semua fungsi linier fungsi eksponen adalah yang terkecil, yaitu aproksimasi terbaik dalam keluarganya. Dan di sini, omong-omong, pertanyaan terakhir dari masalah ini bukanlah kebetulan: bagaimana jika fungsi eksponensial yang diusulkan akan lebih baik untuk mendekati titik percobaan?

Mari kita cari jumlah deviasi kuadrat yang sesuai - untuk membedakannya, saya akan menunjuknya dengan huruf "epsilon". Tekniknya persis sama:


Dan lagi untuk setiap perhitungan api untuk poin pertama:

Di Excel, kami menggunakan fungsi standar EXP (Sintaks dapat ditemukan di Bantuan Excel).

Kesimpulan: , jadi fungsi eksponensial mendekati titik eksperimen lebih buruk daripada garis lurus .

Tetapi perlu dicatat di sini bahwa "lebih buruk" adalah belum berarti, apa yang salah. Sekarang saya membuat grafik fungsi eksponensial ini - dan juga mendekati titik - sedemikian rupa sehingga tanpa studi analitis sulit untuk mengatakan fungsi mana yang lebih akurat.

Ini melengkapi solusinya, dan saya kembali ke pertanyaan tentang nilai-nilai alami dari argumen tersebut. Dalam berbagai penelitian, sebagai aturan, ekonomi atau sosiologis, bulan, tahun atau interval waktu lain yang sama diberi nomor dengan "X" alami. Pertimbangkan, misalnya, masalah seperti itu.

Metode kuadrat terkecil adalah prosedur matematis untuk membangun persamaan linier yang paling cocok dengan himpunan dua deret bilangan. Tujuan dari metode ini adalah untuk meminimalkan kesalahan kuadrat total. Excel memiliki alat yang dapat digunakan untuk menerapkan metode ini dalam perhitungan. Mari kita lihat bagaimana hal itu dilakukan.

Menggunakan Metode di Excel

o Mengaktifkan add-on Solver

o Kondisi tugas

o Keputusan

Menggunakan Metode di Excel

Metode kuadrat terkecil (LSM) adalah deskripsi matematis dari ketergantungan satu variabel pada yang lain. Dapat digunakan untuk peramalan.

Aktifkan add-in Solver

Untuk menggunakan OLS di Excel, Anda harus mengaktifkan add-in "Mencari Solusi", yang dinonaktifkan secara default.

1. Buka tab "Mengajukan".

2. Klik pada nama bagian "Pilihan".

3. Di jendela yang terbuka, hentikan pemilihan pada subbagian "Add-on".

4. Di blok "Kontrol", yang terletak di bagian bawah jendela, atur sakelar ke posisi "Add-In Excel"(jika memiliki nilai yang berbeda) dan klik tombol "Pergi...".

5. Sebuah jendela kecil terbuka. Beri tanda centang di sebelah opsi "Mencari Solusi". Klik pada tombol Oke.

Sekarang fungsinya Menemukan Solusi di Excel diaktifkan, dan alatnya muncul di pita.

Pelajaran: Menemukan Solusi di Excel

Kondisi masalah

Mari kita jelaskan penerapan LSM pada contoh spesifik. Kami memiliki dua baris angka x dan kamu, yang urutannya ditunjukkan pada gambar di bawah ini.

Ketergantungan ini paling akurat dapat dijelaskan oleh fungsi:

Pada saat yang sama, diketahui bahwa x=0 y juga sama 0 . Oleh karena itu, persamaan ini dapat dijelaskan dengan ketergantungan y=nx.

Kita harus mencari jumlah kuadrat terkecil dari selisihnya.

Keputusan

Mari kita lanjutkan ke deskripsi aplikasi langsung dari metode ini.

1. Di sebelah kiri nilai pertama x beri nomor 1 . Ini akan menjadi nilai perkiraan dari nilai pertama koefisien n.

2. Di sebelah kanan kolom kamu tambahkan kolom lain nx. Di sel pertama kolom ini kami menulis rumus untuk mengalikan koefisien n ke sel variabel pertama x. Pada saat yang sama, kami membuat tautan ke bidang dengan koefisien absolut, karena nilai ini tidak akan berubah. Kami mengklik tombol Memasuki.

3. Dengan menggunakan gagang isian, salin rumus ini ke seluruh rentang tabel di kolom di bawah ini.

4. Di sel terpisah, kami menghitung jumlah perbedaan kuadrat nilai kamu dan nx. Untuk melakukan ini, klik tombol "Sisipkan Fungsi".

5. Di tempat terbuka "Penyihir Fungsi" mencari entri "SUMMKVRAZN". Pilih dan klik tombol Oke.

6. Jendela argumen terbuka. di lapangan "Array_x" kamu. di lapangan "Array_y" masukkan rentang sel kolom nx. Untuk memasukkan nilai, cukup tempatkan kursor di bidang dan pilih rentang yang sesuai pada lembar. Setelah masuk, klik tombol Oke.

7. Pergi ke tab "Data". Pada pita di kotak peralatan "Analisis" klik tombolnya "Mencari Solusi".

8. Jendela parameter alat akan terbuka. di lapangan "Optimalkan fungsi objektif" tentukan alamat sel dengan rumus "SUMMKVRAZN". Dalam parameter "Sebelum" pastikan untuk mengatur sakelar ke posisi "Minimum". di lapangan "Mengganti Sel" tentukan alamat dengan nilai koefisien n. Klik pada tombol "Mencari solusi".

9. Solusinya akan ditampilkan di sel koefisien n. Nilai inilah yang akan menjadi kuadrat terkecil dari fungsi tersebut. Jika hasilnya memuaskan pengguna, maka klik tombol Oke di jendela tambahan.

Seperti yang Anda lihat, penerapan metode kuadrat terkecil adalah prosedur matematika yang agak rumit. Kami telah menunjukkannya dalam tindakan dengan contoh paling sederhana, tetapi ada kasus yang jauh lebih kompleks. Namun, toolkit Microsoft Excel dirancang untuk menyederhanakan perhitungan sebanyak mungkin.

http://multitest.semico.ru/mnk.htm

Ketentuan umum

Semakin kecil angka dalam nilai absolut, semakin baik garis lurus (2) dipilih. Sebagai karakteristik ketepatan pemilihan garis lurus (2), kita dapat mengambil jumlah kuadrat

Kondisi minimum untuk S adalah

	(6)
	(7)

Persamaan (6) dan (7) dapat ditulis dalam bentuk berikut:

	(8)
	(9)

Dari persamaan (8) dan (9) mudah untuk mencari a dan b dari nilai percobaan xi dan y i . Garis (2) yang didefinisikan oleh persamaan (8) dan (9) disebut garis yang diperoleh dengan metode kuadrat terkecil (nama ini menekankan bahwa jumlah kuadrat S memiliki minimum). Persamaan (8) dan (9), dari mana garis lurus (2) ditentukan, disebut persamaan normal.

Hal ini dimungkinkan untuk menunjukkan cara yang sederhana dan umum untuk menyusun persamaan normal. Dengan menggunakan titik percobaan (1) dan persamaan (2), kita dapat menuliskan sistem persamaan untuk a dan b

y 1 \u003d kapak 1 +b,
y2=ax2+b, ...		(10)
yn=axn+b,

Kalikan bagian kiri dan kanan dari masing-masing persamaan ini dengan koefisien pada a yang tidak diketahui pertama (yaitu x 1 , x 2 , ..., x n) dan tambahkan persamaan yang dihasilkan, menghasilkan persamaan normal pertama (8).

Kami mengalikan sisi kiri dan kanan dari masing-masing persamaan ini dengan koefisien b kedua yang tidak diketahui, yaitu dengan 1, dan tambahkan persamaan yang dihasilkan, menghasilkan persamaan normal kedua (9).

Metode untuk memperoleh persamaan normal ini bersifat umum: cocok, misalnya, untuk fungsi

adalah nilai konstan dan harus ditentukan dari data eksperimen (1).

Sistem persamaan untuk k dapat ditulis:

Temukan garis (2) menggunakan metode kuadrat terkecil.

Keputusan. Kami menemukan:

X i =21, y i =46.3, x i 2 =91, x i y i=179.1.

Kami menulis persamaan (8) dan (9)91a+21b=179.1,

21a+6b=46.3, dari sini kita temukan
a=0,98 b=4,3.