Membuat grafik menggunakan metode kuadrat terkecil di excel. Kuadrat terkecil dan menemukan solusi di Excel

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.

Beras. 6

Untuk mengubah tampilan garis regresi, lakukan langkah-langkah berikut. Klik kanan pada titik-titik yang menggambarkan grafik garis, pilih perintah Jenis bagan dan mengatur 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 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. 13.

Demikian pula dengan garis tren anda dapat memilih parameter dependensi seperti

linier kamu=ax+b,

logaritma kamu=sebuah ln(x)+b,

eksponensial kamu=aeb,

kekuasaan 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 Pengukur

Yang cukup menarik adalah implementasi di MS Excel dari pemilihan parameter dengan metode kuadrat terkecil menggunakan blok keputusan. 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.

Masalah ini setara dengan masalah menemukan fungsi minimum dari lima variabel

Pertimbangkan proses penyelesaian masalah optimasi (Gbr. 14).

Biarkan nilai-nilai TETAPI, PADA, DARI, 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 rentang B2:J4, panggilan Pemandu Bagan, lalu format tampilan grafik yang dihasilkan.

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

5. REFERENSI

1. Alekseev E.R., Chesnokova O.V., Memecahkan masalah matematika komputasi dalam paket Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596s. :Saya akan. – (Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, memecahkan masalah teknik dan matematika. –M., BINOM, 2008.–260-an.

3. I. S. Berezin dan N. P. Zhidkov, Metode Komputasi, Moskow: Nauka, 1966.

4. Garnaev A.Yu., Penggunaan MS EXCEL dan VBA di bidang ekonomi dan keuangan. - St. Petersburg: BHV - Petersburg, 1999.-332p.

5. B. P. Demidovich, I. A. Maron, dan V. Z. Shuvalova, Metode Numerik Analisis.–M.: Nauka, 1967.–368p.

6. Korn G., Korn T., Buku Pegangan matematika untuk ilmuwan dan insinyur.-M., 1970, 720p.

7. Alekseev E.R., Chesnokova O.V. Pedoman untuk melakukan pekerjaan laboratorium di MS EXCEL. Untuk siswa dari semua spesialisasi. Donetsk, DonNTU, 2004. 112 hal.

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 diperlukan 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.

Kami 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 perlu dipikirkan lagi bahwa titik-titik tersebut 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. Artinya, 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". Unit ini akan menjadi pendekatan pertama untuk 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: "koefisien k dikalikan dengan 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 di 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 untuk menggambarkan 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 kita 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,

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 variabel lainnya. 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 uraikan 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 digambarkan dengan ketergantungan y=nx.

Kita harus mencari jumlah kuadrat terkecil dari selisihnya.

Larutan

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 akurasi 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.

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,

Kami mengalikan 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 menambahkan persamaan yang dihasilkan, sebagai hasilnya kami mendapatkan 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.

Larutan. 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.

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 hingga 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 x i, 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 jarak kuadrat antara nilai pengamatan y i dan i dan sering dilambangkan sebagai SSE ( jumlah dari kuadrat kesalahan (Residu), 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 dalam kenyataannya tidak ada hubungan antar variabel atau hubungan tersebut nonlinier, maka kuadrat terkecil tetap akan memilih garis “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 perlu menggunakan fungsi INDEX() tambahan. 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; ...).

Ini memiliki banyak aplikasi, karena memungkinkan representasi perkiraan dari fungsi yang diberikan oleh yang lebih sederhana lainnya. LSM dapat sangat berguna dalam memproses pengamatan, dan secara aktif digunakan untuk memperkirakan beberapa besaran dari hasil pengukuran lainnya yang mengandung kesalahan acak. Pada artikel ini, Anda akan belajar bagaimana menerapkan perhitungan kuadrat terkecil di Excel.

Pernyataan masalah pada contoh spesifik

Misalkan ada dua indikator X dan Y. Selain itu, Y bergantung pada X. Karena OLS menarik bagi kami dari sudut pandang analisis regresi (di Excel, metodenya diimplementasikan menggunakan fungsi bawaan), kami harus segera melanjutkan untuk mempertimbangkan masalah tertentu.

Jadi, misalkan X adalah area penjualan toko kelontong, diukur dalam meter persegi, dan Y adalah omset tahunan, yang ditentukan dalam jutaan rubel.

Diperlukan untuk membuat perkiraan tentang omset (Y) yang akan dimiliki toko jika memiliki satu atau beberapa ruang ritel lainnya. Jelas, fungsi Y = f (X) meningkat, karena hypermarket menjual lebih banyak barang daripada kios.

Beberapa kata tentang kebenaran data awal yang digunakan untuk prediksi

Katakanlah kita memiliki tabel yang dibangun dengan data untuk n toko.

Menurut statistik matematika, hasilnya akan kurang lebih benar jika data pada setidaknya 5-6 objek diperiksa. Juga, hasil "anomali" tidak dapat digunakan. Secara khusus, butik kecil elit dapat memiliki omset berkali-kali lebih besar daripada omset gerai besar kelas "masmarket".

Inti dari metode

Data tabel dapat ditampilkan pada bidang Cartesian sebagai titik M 1 (x 1, y 1), ... M n (x n, y n). Sekarang solusi dari masalah akan direduksi menjadi pemilihan fungsi aproksimasi y = f (x), yang memiliki grafik yang lewat sedekat mungkin ke titik M 1, M 2, .. M n .

Tentu saja, Anda dapat menggunakan polinomial tingkat tinggi, tetapi opsi ini tidak hanya sulit untuk diterapkan, tetapi juga salah, karena tidak mencerminkan tren utama yang perlu dideteksi. Solusi yang paling masuk akal adalah mencari garis lurus y = ax + b, yang paling mendekati data eksperimen, dan lebih tepatnya, koefisien - a dan b.

Skor akurasi

Untuk pendekatan apa pun, penilaian akurasinya sangat penting. Dilambangkan dengan ei perbedaan (deviasi) antara nilai fungsional dan eksperimental untuk titik x i , yaitu e i = y i - f (x i).

Jelas, untuk menilai keakuratan pendekatan, Anda dapat menggunakan jumlah penyimpangan, yaitu, ketika memilih garis lurus untuk representasi perkiraan ketergantungan X pada Y, preferensi harus diberikan kepada garis yang memiliki nilai terkecil dari jumlah e i di semua titik yang dipertimbangkan. Namun, tidak semuanya sesederhana itu, karena seiring dengan penyimpangan positif, praktis akan ada penyimpangan negatif.

Anda dapat memecahkan masalah menggunakan modul deviasi atau kuadratnya. Cara yang terakhir ini yang paling banyak digunakan. Ini digunakan di banyak bidang, termasuk analisis regresi (di Excel, implementasinya dilakukan menggunakan dua fungsi bawaan), dan telah lama terbukti efektif.

Metode kuadrat terkecil

Di Excel, seperti yang Anda ketahui, ada fungsi autosum bawaan yang memungkinkan Anda menghitung nilai semua nilai yang terletak di rentang yang dipilih. Jadi, tidak ada yang akan menghalangi kita untuk menghitung nilai ekspresi (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Dalam notasi matematika, ini terlihat seperti:

Karena keputusan awalnya dibuat untuk mendekati menggunakan garis lurus, kami memiliki:

Jadi, tugas menemukan garis lurus yang paling menggambarkan hubungan spesifik antara X dan Y sama dengan menghitung fungsi minimum dari dua variabel:

Ini membutuhkan persamaan dengan nol turunan parsial sehubungan dengan variabel baru a dan b, dan menyelesaikan sistem primitif yang terdiri dari dua persamaan dengan 2 bentuk yang tidak diketahui:

Setelah transformasi sederhana, termasuk membagi dengan 2 dan memanipulasi jumlah, kita mendapatkan:

Memecahkannya, misalnya, dengan metode Cramer, kami memperoleh titik stasioner dengan koefisien tertentu a * dan b * . Ini adalah minimum, yaitu untuk memprediksi omset toko untuk area tertentu, garis lurus y = a * x + b * cocok, yang merupakan model regresi untuk contoh yang dimaksud. Tentu saja, itu tidak akan memungkinkan Anda untuk menemukan hasil yang tepat, tetapi ini akan membantu Anda mendapatkan gambaran apakah membeli toko secara kredit untuk area tertentu akan membuahkan hasil.

Bagaimana menerapkan metode kuadrat terkecil di Excel

Excel memiliki fungsi untuk menghitung nilai kuadrat terkecil. Bentuknya sebagai berikut: TREND (nilai Y yang diketahui; nilai X yang diketahui; nilai X baru; konstan). Mari kita terapkan rumus untuk menghitung OLS di Excel ke tabel kita.

Untuk melakukan ini, di sel di mana hasil perhitungan menggunakan metode kuadrat terkecil di Excel harus ditampilkan, masukkan tanda "=" dan pilih fungsi "TREND". Di jendela yang terbuka, isi bidang yang sesuai, sorot:

• rentang nilai yang diketahui untuk Y (dalam hal ini data untuk pergantian);
• range x 1 , …x n , yaitu ukuran ruang ritel;
• dan nilai x yang diketahui dan tidak diketahui, di mana Anda perlu mengetahui ukuran omset (untuk informasi tentang lokasinya di lembar kerja, lihat di bawah).

Selain itu, ada variabel logis "Const" dalam rumus. Jika Anda memasukkan 1 di bidang yang sesuai dengannya, maka ini berarti bahwa perhitungan harus dilakukan, dengan asumsi bahwa b \u003d 0.

Jika Anda perlu mengetahui ramalan untuk lebih dari satu nilai x, maka setelah memasukkan rumus, Anda tidak boleh menekan "Enter", tetapi Anda perlu mengetikkan kombinasi "Shift" + "Control" + "Enter" ("Enter" ) pada papan ketik.

Beberapa Fitur

Analisis regresi dapat diakses bahkan untuk boneka. Rumus Excel untuk memprediksi nilai array variabel yang tidak diketahui - "TREND" - dapat digunakan bahkan oleh mereka yang belum pernah mendengar tentang metode kuadrat terkecil. Cukup mengetahui beberapa fitur pekerjaannya. Khususnya:

• Jika Anda menempatkan rentang nilai variabel y yang diketahui dalam satu baris atau kolom, maka setiap baris (kolom) dengan nilai x yang diketahui akan dianggap oleh program sebagai variabel terpisah.
• Jika rentang dengan x yang diketahui tidak ditentukan di jendela TREND, maka jika menggunakan fungsi di Excel, program akan menganggapnya sebagai array yang terdiri dari bilangan bulat, yang jumlahnya sesuai dengan rentang dengan nilai yang diberikan dari variabel y.
• Untuk menampilkan larik nilai "prediksi", ekspresi tren harus dimasukkan sebagai rumus larik.
• Jika tidak ada nilai x baru yang ditentukan, maka fungsi TREND menganggapnya sama dengan yang diketahui. Jika tidak ditentukan, maka array 1 diambil sebagai argumen; 2; 3; 4;…, yang sepadan dengan range dengan parameter y yang sudah diberikan.
• Rentang yang berisi nilai x baru harus memiliki baris atau kolom yang sama atau lebih dengan rentang dengan nilai y yang diberikan. Dengan kata lain, harus proporsional dengan variabel bebas.
• Array dengan nilai x yang diketahui dapat berisi banyak variabel. Namun, jika kita berbicara tentang hanya satu, maka rentang dengan nilai x dan y yang diberikan harus sepadan. Dalam kasus beberapa variabel, rentang dengan nilai y yang diberikan harus sesuai dalam satu kolom atau satu baris.

Fungsi PERKIRAAN

Ini diimplementasikan menggunakan beberapa fungsi. Salah satunya disebut "PREDIKSI". Mirip dengan TREND, yaitu memberikan hasil perhitungan menggunakan metode kuadrat terkecil. Namun, hanya untuk satu X, yang nilai Y tidak diketahui.

Sekarang Anda mengetahui rumus Excel untuk boneka yang memungkinkan Anda memprediksi nilai nilai masa depan dari suatu indikator menurut tren linier.