Metode kuadrat terkecil dan mencari solusi di Excel. Metode kuadrat terkecil di Excel. Analisis regresi metode kuadrat terkecil pada fungsi pangkat excel

Metode kuadrat terkecil (OLS) termasuk dalam bidang analisis regresi. Ini memiliki banyak penerapan, karena memungkinkan representasi perkiraan fungsi tertentu dengan fungsi lain yang lebih sederhana. LSM bisa sangat berguna dalam memproses observasi, dan secara aktif digunakan untuk memperkirakan beberapa kuantitas berdasarkan hasil pengukuran kuantitas lain yang mengandung kesalahan acak. Pada artikel ini, Anda akan mempelajari cara menerapkan penghitungan kuadrat terkecil di Excel.

Pernyataan masalah menggunakan contoh spesifik

Misalkan ada dua indikator X dan Y. Selain itu, Y bergantung pada X. Karena OLS menarik minat kita dari sudut pandang analisis regresi (di Excel, metodenya diimplementasikan menggunakan fungsi bawaan), kita harus segera melanjutkan ke pertimbangan a masalah tertentu.

Jadi, misalkan X adalah ruang ritel sebuah toko kelontong, diukur dalam meter persegi, dan Y adalah omset tahunan, diukur dalam jutaan rubel.

Diperlukan untuk membuat perkiraan berapa omset (Y) yang akan diperoleh toko jika memiliki ruang ritel tertentu. Jelasnya fungsi Y = f (X) meningkat, karena hipermarket menjual lebih banyak barang daripada kios.

Sedikit penjelasan tentang kebenaran data awal yang digunakan untuk prediksi

Katakanlah kita memiliki tabel yang dibuat menggunakan data untuk n penyimpanan.

Menurut statistik matematika, hasilnya akan lebih atau kurang benar jika data pada setidaknya 5-6 objek diperiksa. Selain itu, hasil “anomali” tidak dapat digunakan. Secara khusus, butik kecil elit dapat memiliki omzet yang beberapa kali lipat lebih besar dibandingkan omzet gerai ritel besar kelas “masmarket”.

Inti dari metode ini

Data tabel dapat digambarkan pada bidang kartesius berupa titik M 1 (x 1, y 1), ... M n (x n, yn). Sekarang penyelesaian masalah akan direduksi menjadi pemilihan fungsi aproksimasi y = f (x), yang mempunyai grafik yang mendekati titik M 1, M 2, .. M n.

Tentu saja, Anda dapat menggunakan polinomial derajat tinggi, tetapi opsi ini tidak hanya sulit 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, atau lebih tepatnya, koefisien a dan b.

Penilaian akurasi

Dengan perkiraan apa pun, menilai keakuratannya sangatlah penting. Mari kita nyatakan dengan e i selisih (deviasi) antara nilai fungsional dan nilai eksperimen untuk titik x i, yaitu e i = y i - f (x i).

Tentunya, untuk menilai keakuratan aproksimasi, Anda dapat menggunakan jumlah deviasi, yaitu, ketika memilih garis lurus untuk perkiraan representasi ketergantungan X pada Y, Anda harus memberikan preferensi pada garis yang memiliki nilai terkecil. jumlah e i di semua titik yang dipertimbangkan. Namun, tidak semuanya sesederhana itu, karena selain penyimpangan positif juga akan ada penyimpangan negatif.

Masalah ini dapat diselesaikan dengan menggunakan modul deviasi atau kuadratnya. Cara terakhir adalah yang paling banyak digunakan. Ini digunakan di banyak bidang, termasuk analisis regresi (diimplementasikan di Excel menggunakan dua fungsi bawaan), dan telah lama terbukti keefektifannya.

Metode kuadrat terkecil

Excel, seperti yang Anda ketahui, memiliki fungsi AutoSum bawaan yang memungkinkan Anda menghitung nilai semua nilai yang berada dalam rentang yang dipilih. Jadi, tidak ada yang menghalangi kita untuk menghitung nilai ekspresi (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Dalam notasi matematika terlihat seperti ini:

Karena keputusan awalnya dibuat untuk memperkirakan menggunakan garis lurus, kita mempunyai:

Jadi, tugas menemukan garis lurus yang paling menggambarkan ketergantungan spesifik besaran X dan Y adalah menghitung minimum fungsi dua variabel:

Untuk melakukan ini, Anda perlu menyamakan turunan parsial terhadap variabel baru a dan b dengan nol, dan menyelesaikan sistem primitif yang terdiri dari dua persamaan dengan 2 bentuk yang tidak diketahui:

Setelah beberapa transformasi sederhana, termasuk pembagian dengan 2 dan manipulasi jumlah, kita mendapatkan:

Penyelesaiannya misalnya dengan menggunakan metode Cramer diperoleh titik stasioner dengan koefisien tertentu a* dan b*. Ini adalah minimum, yaitu untuk memprediksi berapa omset suatu toko di suatu wilayah tertentu, cocok untuk garis lurus y = a * x + b *, yang merupakan model regresi untuk contoh yang dimaksud. Tentu saja, ini tidak akan memungkinkan Anda menemukan hasil pastinya, tetapi ini akan membantu Anda mendapatkan gambaran apakah membeli area tertentu dengan kredit toko akan membuahkan hasil.

Bagaimana Menerapkan Kuadrat Terkecil di Excel

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

Untuk melakukannya, masukkan tanda “=” di sel yang akan menampilkan hasil perhitungan menggunakan metode kuadrat terkecil di Excel dan pilih fungsi “TREND”. Di jendela yang terbuka, isi kolom yang sesuai, soroti:

rentang nilai Y yang diketahui (dalam hal ini, data perputaran perdagangan);
rentang x 1 , …x n , yaitu ukuran ruang ritel;
baik nilai x yang diketahui maupun yang tidak diketahui, yang untuk itu Anda perlu mengetahui besarnya omset (untuk informasi tentang lokasinya di lembar kerja, lihat di bawah).

Selain itu, rumusnya berisi variabel logis “Const”. Jika Anda memasukkan 1 pada kolom yang sesuai, ini berarti Anda harus melakukan penghitungan, dengan asumsi b = 0.

Jika Anda ingin mengetahui perkiraan lebih dari satu nilai x, maka setelah memasukkan rumus Anda tidak boleh menekan “Enter”, tetapi Anda perlu mengetikkan kombinasi “Shift” + “Control” + “Enter” pada keyboard.

Beberapa fitur

Analisis regresi dapat diakses bahkan oleh orang bodoh. Rumus Excel untuk memprediksi nilai array variabel yang tidak diketahui—TREND—dapat digunakan bahkan oleh mereka yang belum pernah mendengar tentang kuadrat terkecil. Cukup mengetahui beberapa fitur kerjanya saja. Secara khusus:

Jika Anda menyusun 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 saat menggunakan fungsi di Excel, program akan memperlakukannya sebagai larik yang terdiri dari bilangan bulat, yang jumlahnya sesuai dengan rentang dengan nilai tertentu dari variabel kamu.
Untuk mengeluarkan array nilai “prediksi”, ekspresi untuk menghitung tren harus dimasukkan sebagai rumus array.
Jika nilai x baru tidak ditentukan, maka fungsi TREND menganggapnya sama dengan nilai yang diketahui. Jika tidak ditentukan, maka array 1 diambil sebagai argumen; 2; 3; 4;…, yang sepadan dengan rentang dengan parameter y yang sudah ditentukan.
Rentang yang berisi nilai x baru harus memiliki baris atau kolom yang sama atau lebih dengan rentang yang berisi nilai y yang diberikan. Dengan kata lain harus proporsional dengan variabel independennya.
Array dengan nilai x yang diketahui dapat berisi banyak variabel. Namun, jika kita berbicara tentang satu saja, maka rentang dengan nilai x dan y yang diberikan harus proporsional. Dalam kasus beberapa variabel, rentang dengan nilai y yang diberikan harus muat dalam satu kolom atau satu baris.

fungsi PREDIKSI

Analisis regresi di Excel 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 tidak diketahui nilai Y-nya.

Sekarang Anda mengetahui rumus di Excel untuk boneka yang memungkinkan Anda memprediksi nilai masa depan dari indikator tertentu berdasarkan tren linier.

Ini memiliki banyak penerapan, karena memungkinkan representasi perkiraan fungsi tertentu dengan fungsi lain yang lebih sederhana. LSM bisa sangat berguna dalam memproses observasi, dan secara aktif digunakan untuk memperkirakan beberapa kuantitas berdasarkan hasil pengukuran kuantitas lain yang mengandung kesalahan acak. Pada artikel ini, Anda akan mempelajari cara menerapkan penghitungan kuadrat terkecil di Excel.

Pernyataan masalah menggunakan contoh spesifik

Jadi, misalkan X adalah ruang ritel sebuah toko kelontong, diukur dalam meter persegi, dan Y adalah omset tahunan, diukur dalam jutaan rubel.

Sedikit penjelasan tentang kebenaran data awal yang digunakan untuk prediksi

Katakanlah kita memiliki tabel yang dibuat menggunakan data untuk n penyimpanan.

Inti dari metode ini

Penilaian akurasi

Metode kuadrat terkecil

Dalam notasi matematika terlihat seperti ini:

Karena keputusan awalnya dibuat untuk memperkirakan menggunakan garis lurus, kita mempunyai:

Jadi, tugas menemukan garis lurus yang paling menggambarkan ketergantungan spesifik besaran X dan Y adalah menghitung minimum fungsi dua variabel:

Setelah beberapa transformasi sederhana, termasuk pembagian dengan 2 dan manipulasi jumlah, kita mendapatkan:

Bagaimana Menerapkan Kuadrat Terkecil di Excel

rentang nilai Y yang diketahui (dalam hal ini, data perputaran perdagangan);
rentang x 1 , …x n , yaitu ukuran ruang ritel;
baik nilai x yang diketahui maupun yang tidak diketahui, yang untuk itu Anda perlu mengetahui besarnya omset (untuk informasi tentang lokasinya di lembar kerja, lihat di bawah).

Selain itu, rumusnya berisi variabel logis “Const”. Jika Anda memasukkan 1 pada kolom yang sesuai, ini berarti Anda harus melakukan penghitungan, dengan asumsi b = 0.

Beberapa fitur

Jika Anda menyusun 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 saat menggunakan fungsi di Excel, program akan memperlakukannya sebagai larik yang terdiri dari bilangan bulat, yang jumlahnya sesuai dengan rentang dengan nilai tertentu dari variabel kamu.
Untuk mengeluarkan array nilai “prediksi”, ekspresi untuk menghitung tren harus dimasukkan sebagai rumus array.
Jika nilai x baru tidak ditentukan, maka fungsi TREND menganggapnya sama dengan nilai yang diketahui. Jika tidak ditentukan, maka array 1 diambil sebagai argumen; 2; 3; 4;…, yang sepadan dengan rentang dengan parameter y yang sudah ditentukan.
Rentang yang berisi nilai x baru harus memiliki baris atau kolom yang sama atau lebih dengan rentang yang berisi nilai y yang diberikan. Dengan kata lain harus proporsional dengan variabel independennya.
Array dengan nilai x yang diketahui dapat berisi banyak variabel. Namun, jika kita berbicara tentang satu saja, maka rentang dengan nilai x dan y yang diberikan harus proporsional. Dalam kasus beberapa variabel, rentang dengan nilai y yang diberikan harus muat dalam satu kolom atau satu baris.

fungsi PREDIKSI

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 tidak diketahui nilai Y-nya.

Sekarang Anda mengetahui rumus di Excel untuk boneka yang memungkinkan Anda memprediksi nilai masa depan dari indikator tertentu berdasarkan tren linier.

Nah, di tempat kerja kami melapor ke inspeksi, artikel itu ditulis di rumah untuk konferensi - sekarang kami bisa menulis di blog. Saat saya memproses data, saya menyadari bahwa saya tidak bisa tidak menulis tentang add-in yang sangat keren dan diperlukan di Excel bernama . Jadi artikel ini akan dikhususkan untuk add-on khusus ini, dan saya akan memberi tahu Anda tentangnya menggunakan contoh penggunaan metode kuadrat terkecil(LSM) untuk mencari koefisien persamaan yang tidak diketahui saat mendeskripsikan 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. Pada jendela yang muncul, pilih “Search for a solution” dan klik “go”.

3. Di jendela berikutnya, centang kotak di samping “cari solusi” 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 ia dapat digunakan.

Katakanlah kita mempunyai sekumpulan data setelah kita melakukan suatu percobaan, dimana kita mempelajari pengaruh nilai X terhadap nilai Y.

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

Saya akan mengambil contoh yang sangat sederhana (lihat gambar).

Tidak perlu dipikirkan lagi bahwa titik-titik tersebut terletak satu demi satu seolah-olah dalam satu garis lurus, dan oleh karena itu kita berasumsi dengan aman bahwa ketergantungan kita dijelaskan oleh fungsi linier y=kx+b. Pada saat yang sama, kita yakin sepenuhnya bahwa ketika X sama dengan nol, maka 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 kami lakukan perusahaan multinasional menggunakan add-on "pencarian solusi".

Metodenya adalah (di sini - perhatian: Anda perlu memikirkannya) jumlah kuadrat selisih antara nilai yang diperoleh secara eksperimental dan nilai perhitungan yang sesuai adalah minimal. Artinya, ketika X1=1 nilai terukur sebenarnya Y1=4,6, dan y1=f (x1) yang dihitung sama dengan 4, kuadrat selisihnya adalah (y1-Y1)^2=(4-4.6)^ 2=0,36 . Sama halnya dengan yang berikut: ketika X2=2, nilai terukur sebenarnya dari Y2=8,1, dan y2 yang dihitung adalah 8, kuadrat selisihnya adalah (y2-Y2)^2=(8-8.1)^2 =0,01. Dan jumlah seluruh kuadrat ini harus sekecil mungkin.

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

Menerapkan add-in untuk menemukan solusi

1. Jika Anda belum mengaktifkan add-on “cari solusi”, kembali ke poin Cara mengaktifkan add-on “cari solusi” dan menyalakannya 🙂

2. Di sel A1, masukkan nilai “1”. Satuan ini akan menjadi perkiraan pertama terhadap nilai riil koefisien (k) dari hubungan fungsional kita y=kx.

3. Pada kolom B kita mempunyai nilai parameter X, pada kolom C kita mempunyai nilai parameter Y. Pada sel kolom D kita masukkan rumus: “koefisien k dikalikan dengan nilai X. ” Misalnya di sel D1 kita masukkan “=A1*B1”, di sel D2 kita masukkan “=A1*B2”, dst.

4. Kami percaya bahwa koefisien k sama dengan satu dan fungsi f (x)=y=1*x adalah perkiraan pertama untuk solusi kami. Kita dapat menghitung jumlah selisih kuadrat antara nilai Y yang diukur dan nilai 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... dst. Pada akhirnya kita membuat kesalahan dan menyadari bahwa kita telah membuang banyak waktu. Di Excel, untuk menghitung jumlah selisih kuadrat, ada rumus khusus, “SUMQUARRENT", yang akan melakukan segalanya untuk kita. Masukkan ke dalam sel A2 dan atur data awal: rentang nilai terukur Y (kolom C) dan rentang nilai Y terhitung (kolom D).

4. Jumlah selisih kuadrat telah dihitung - sekarang buka tab “Data” dan pilih “Cari solusi”.

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

6. Pilih sel A2 sebagai target dan atur kondisi “ditetapkan sama dengan nilai minimum”. Kita ingat bahwa ini adalah sel tempat kita menghitung jumlah kuadrat selisih antara nilai yang dihitung dan diukur, dan jumlah ini harus minimal. Klik "eksekusi".

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

P.S.

Secara umum, tentu saja, untuk memperkirakan data eksperimen di Excel, terdapat alat khusus yang memungkinkan Anda mendeskripsikan data menggunakan fungsi linier, eksponensial, pangkat, dan polinomial, sehingga Anda sering kali dapat melakukannya tanpa Pengaya “mencari solusi”.. Saya telah membicarakan semua metode perkiraan ini, jadi jika Anda tertarik, lihatlah. Tapi jika menyangkut beberapa fungsi eksotis dengan satu koefisien yang tidak diketahui atau masalah optimasi, lalu di sini bangunan atas tidak bisa datang pada waktu yang lebih baik.

Add-on pencarian solusi dapat digunakan untuk tugas lain, yang utama adalah memahami esensinya: ada sel tempat kita memilih nilai, dan ada sel target di mana kondisi untuk memilih parameter yang tidak diketahui ditentukan.
Itu saja! Pada artikel selanjutnya saya akan menceritakan sebuah dongeng tentang liburan, jadi agar tidak ketinggalan publikasi artikelnya,

Metode kuadrat terkecil (LS) didasarkan pada minimalisasi jumlah simpangan kuadrat fungsi yang dipilih dari data yang diteliti. Pada artikel ini kami akan memperkirakan data yang tersedia menggunakan fungsi linierkamu = A X + B .

Metode kuadrat terkecil(Bahasa inggris) Biasa Paling sedikit Kotak , O.L.S.) adalah salah satu metode dasar analisis regresi dalam memperkirakan parameter yang tidak diketahui model regresi menurut data sampel.

Mari kita pertimbangkan perkiraan fungsi yang hanya bergantung pada satu variabel:

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

Catatan: Kasus pendekatan polinomial dari derajat ke-3 hingga ke-6 dibahas dalam artikel ini. Pendekatan dengan polinomial trigonometri dibahas di sini.

Ketergantungan linier

Kami tertarik pada hubungan antara 2 variabel X Dan kamu. Ada asumsi bahwa kamu tergantung pada X menurut hukum linier kamu = kapak + B. Untuk menentukan parameter hubungan tersebut, peneliti melakukan observasi: untuk setiap nilai x i dilakukan pengukuran y i (lihat contoh file). Jadi, misalkan ada 20 pasang nilai (xi; y i).

Catatan: Jika langkah perubahannya adalah X adalah konstan, maka untuk membangun plot pencar dapat digunakan, jika tidak, maka Anda perlu menggunakan tipe grafik Titik .

Terlihat dari diagram bahwa hubungan antar variabel mendekati linier. Untuk memahami garis mana yang paling “benar” menggambarkan hubungan antar variabel, perlu ditentukan kriteria garis yang akan dibandingkan.

Sebagai kriteria kami menggunakan ekspresi:

Di mana ŷ Saya = A * x saya + B ; n – jumlah pasangan nilai (dalam kasus kami n=20)

Ekspresi di atas adalah jumlah kuadrat jarak antara nilai y i dan ŷ i yang diamati dan sering dinotasikan 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: Setiap garis dalam ruang dua dimensi ditentukan secara unik oleh nilai 2 parameter: A (kemiringan) dan B (menggeser).

Dipercaya bahwa semakin kecil jumlah jarak kuadrat, semakin baik garis yang bersesuaian 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 hubungannya nonlinier, maka OLS akan tetap memilih jalur “terbaik”. Jadi, metode kuadrat terkecil tidak mengatakan apa pun tentang adanya hubungan nyata antar variabel; metode ini hanya memungkinkan Anda memilih parameter fungsi tersebut. A Dan B , yang ekspresi di atas minimal.

Dengan melakukan operasi matematika yang tidak terlalu rumit (untuk lebih jelasnya lihat), Anda dapat menghitung parameternya A Dan B :

Seperti dapat dilihat dari rumusnya, parameternya A mewakili rasio kovarians dan , oleh karena itu di MS EXCEL untuk menghitung parameter A Anda dapat menggunakan rumus berikut (lihat File contoh lembar linier):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) atau

= KOVARIANSI.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Juga untuk menghitung parameternya A anda dapat menggunakan rumus = KEMIRINGAN(C26:C45;B26:B45). Untuk parameter B gunakan rumus = KAKI (C26:C45;B26:B45) .

Terakhir, fungsi LINEST() memungkinkan Anda menghitung kedua parameter sekaligus. Untuk memasukkan rumus GARIS(C26:C45;B26:B45) Anda harus memilih 2 sel berturut-turut dan klik CTRL + MENGGESER + MEMASUKI(lihat artikel tentang). Nilainya akan dikembalikan di sel kiri A , di kanan - B .

Catatan: Untuk menghindari mengacaukan input rumus susunan Anda juga perlu menggunakan fungsi INDEX(). Rumus = INDEKS(GARIS(C26:C45,B26:B45),1) atau hanya = GARIS(C26:C45;B26:B45) akan mengembalikan parameter yang bertanggung jawab atas kemiringan garis, mis. A . Rumus = INDEKS(GARIS(C26:C45,B26:B45),2) akan mengembalikan parameter yang bertanggung jawab atas perpotongan garis dengan sumbu Y, yaitu. B .

Setelah menghitung parameternya, diagram sebar Anda dapat menggambar garis yang sesuai.

Cara lain untuk menggambar garis lurus menggunakan metode kuadrat terkecil adalah dengan alat grafik Garis tren. Untuk melakukan ini, pilih diagram, pilih dari menu tab tata letak, V Analisis kelompok klik Garis tren, Kemudian Perkiraan 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 parameternya cocok, tipe diagramnya harus . Intinya adalah ketika membuat diagram Jadwal Nilai sumbu X tidak dapat ditentukan oleh pengguna (pengguna hanya dapat menentukan label yang tidak mempengaruhi lokasi titik). Alih-alih nilai X, urutan 1 digunakan; 2; 3; ... (untuk penomoran kategori). Oleh karena itu, jika Anda membangun garis tren pada diagram tipe Jadwal, maka alih-alih nilai sebenarnya dari X, nilai dari barisan ini akan digunakan, yang akan menghasilkan hasil yang salah (kecuali, tentu saja, nilai sebenarnya dari X tidak sesuai dengan barisan 1; 2; 3; ...).

Metode kuadrat terkecil adalah prosedur matematis untuk membuat persamaan linier yang paling akurat memuat dua rangkaian angka. Tujuan penggunaan metode ini adalah untuk meminimalkan total square error. Excel memiliki alat yang dapat membantu Anda menerapkan metode ini pada perhitungan Anda. Mari kita cari tahu bagaimana hal ini dilakukan.

· Menggunakan metode di Excel

o Mengaktifkan add-on “Pencarian Solusi”.

o Kondisi masalah

o Solusi

Menggunakan metode di Excel

Metode kuadrat terkecil (LSM) merupakan gambaran matematis ketergantungan suatu variabel terhadap variabel lainnya. Ini dapat digunakan untuk peramalan.

Mengaktifkan add-on Temukan Solusi

Untuk menggunakan MNC di Excel, Anda perlu mengaktifkan add-in "Menemukan solusi", yang dinonaktifkan secara default.

1. Buka tab "Mengajukan".

2. Klik pada nama bagian "Pilihan".

3. Di jendela yang terbuka, pilih subbagian "Pengaya".

4. Di blok "Kontrol", yang terletak di bagian bawah jendela, atur sakelar ke posisinya "Add-in Excel"(jika nilainya berbeda) dan klik tombol "Pergi...".

5. Sebuah jendela kecil terbuka. Kami memberi tanda centang di sebelah parameter "Menemukan solusi". Klik pada tombol "OKE".

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

Pelajaran: Menemukan solusi di Excel

Kondisi permasalahan

Mari kita jelaskan penggunaan LSM menggunakan contoh spesifik. Kami memiliki dua baris angka X Dan kamu, urutannya ditunjukkan pada gambar di bawah.

Ketergantungan ini paling akurat dijelaskan oleh fungsi:

Pada saat yang sama, diketahui kapan x=0 tahun juga setara 0 . Oleh karena itu, persamaan ini dapat digambarkan dengan ketergantungan y=nx.

Kita harus mencari jumlah minimum kuadrat selisihnya.

Larutan

Mari kita beralih ke deskripsi penerapan langsung metode ini.

1. Di sebelah kiri nilai pertama X menaruh nomor 1 . Ini akan menjadi nilai perkiraan dari nilai koefisien pertama N.

2. Di sebelah kanan kolom kamu tambahkan kolom lain - nx. Di sel pertama kolom ini kita menulis rumus untuk mengalikan koefisien N per sel dari variabel pertama X. Pada saat yang sama, kami membuat tautan ke bidang dengan koefisien absolut, karena nilai ini tidak akan berubah. Klik pada tombol Memasuki.

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

4. Di sel terpisah, hitung jumlah selisih kuadrat nilainya kamu Dan nx. Untuk melakukan ini, klik tombol "Masukkan Fungsi".

5. Di tempat terbuka "Penyihir Fungsi" mencari entri "SUMMKVARNA". Pilih dan tekan tombolnya "OKE".

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

7. Buka tab "Data". Pada pita di kotak peralatan "Analisis" klik pada tombol tersebut "Menemukan solusi".

8. Jendela parameter untuk alat ini terbuka. Di lapangan “Optimalkan fungsi tujuan” menunjukkan alamat sel dengan rumus "SUMMKVARNA". Dalam parameternya "Sebelum" pastikan untuk mengatur sakelar ke posisinya "Minimum". Di lapangan "Mengganti Sel" menunjukkan alamat dengan nilai koefisien N. Klik pada tombol "Mencari solusi".

9. Solusinya akan ditampilkan di sel koefisien N. Nilai ini akan menjadi kuadrat terkecil dari fungsi tersebut. Jika hasilnya memuaskan pengguna, klik tombol "OKE" di jendela tambahan.

Seperti yang Anda lihat, penerapan metode kuadrat terkecil adalah prosedur matematika yang agak rumit. Kami menunjukkannya dalam tindakan menggunakan contoh sederhana, tetapi ada banyak kasus yang lebih kompleks. Namun, alat Microsoft Excel dirancang untuk menyederhanakan penghitungan sebanyak mungkin.

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

Ketentuan umum

Semakin kecil angka nilai absolutnya, semakin baik garis lurus yang dipilih (2). Sebagai ciri ketelitian pemilihan garis lurus (2), kita dapat mengambil jumlah kuadrat

Kondisi minimum untuk S adalah

	(6)
	(7)

Persamaan (6) dan (7) dapat ditulis sebagai berikut:

	(8)
	(9)

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

Anda dapat menunjukkan cara sederhana dan umum untuk menyusun persamaan normal. Dengan menggunakan titik percobaan (1) dan persamaan (2), kita dapat menulis sistem persamaan a dan b

kamu 1 =kapak 1 +b,
kamu 2 =kapak 2 +b, ...		(10)
y n = kapak n + b,

Mari kalikan ruas kiri dan kanan masing-masing persamaan ini dengan koefisien a yang pertama tidak diketahui (yaitu dengan x 1, x 2, ..., x n) dan tambahkan persamaan yang dihasilkan, sehingga diperoleh persamaan normal pertama (8) .

Mari kita kalikan ruas kiri dan kanan masing-masing persamaan ini dengan koefisien b kedua yang tidak diketahui, yaitu. dengan 1, dan menjumlahkan persamaan yang dihasilkan, hasilnya adalah persamaan normal kedua (9).

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

ada nilai konstanta dan harus ditentukan dari data eksperimen (1).

Sistem persamaan k dapat ditulis:

Temukan garis lurus (2) dengan menggunakan metode kuadrat terkecil.

Larutan. Kami menemukan:

X saya =21, y saya =46,3, x saya 2 =91, x saya y saya =179,1.

Kita tuliskan persamaan (8) dan (9)91a+21b=179.1,

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