Metode kuadrat terkecil dalam contoh Excele. Analisis regresi berpasangan linier. Beberapa kata tentang kebenaran data sumber yang digunakan untuk memprediksi

Metode kuadrat terkecil adalah prosedur matematika untuk membangun persamaan linear, yang paling akurat sesuai dengan set dua baris angka. Tujuan menggunakan metode ini adalah untuk meminimalkan total kesalahan kuadrat. Program Excel memiliki alat yang dengannya metode ini dapat diterapkan saat menghitung. Mari kita berurusan dengan bagaimana hal itu dilakukan.

Metode kuadrat terkecil (MNC) adalah deskripsi matematika tentang ketergantungan satu variabel dari yang kedua. Ini dapat digunakan saat memprediksi.

Mengaktifkan Add-on "Pencarian Solusi"

Untuk menggunakan MNC di Excele, Anda perlu menghidupkan add-on "Solusi Solusi"yang dinonaktifkan secara default.

Sekarang fitur Cari solusi Excel diaktifkan, dan alatnya muncul di kaset.

Kondisi masalah

Kami menggambarkan penggunaan MNK pada contoh tertentu. Kami memiliki dua baris angka x. dan y. Urutan yang disajikan dalam gambar di bawah ini.

Ketergantungan yang diberikan paling akurat dapat menggambarkan fungsi:

Pada saat yang sama, diketahui bahwa kapan x \u003d 0. y. Juga sama 0 . Oleh karena itu, persamaan ini dapat dijelaskan oleh kecanduan y \u003d nx. .

Kita harus menemukan jumlah minimum kuadrat dari perbedaan.

Keputusan

Mari kita beralih ke deskripsi aplikasi langsung dari metode ini.

Seperti yang Anda lihat, penggunaan metode metode persegi paling sedikit adalah prosedur matematika yang agak rumit. Kami menunjukkannya dalam aksi atas contoh paling sederhana, dan ada banyak kasus yang lebih kompleks. Namun, Microsoft Excel Toolkit dirancang untuk menyederhanakan perhitungan.

Metode kuadrat terkecil (MNC) mengacu pada area analisis regresi. Ini memiliki banyak aplikasi, karena memungkinkan Anda untuk melakukan representasi perkiraan fungsi yang ditentukan oleh lainnya lebih sederhana. MNA mungkin sangat berguna dalam pengambilan pengamatan, dan secara aktif digunakan untuk menilai beberapa nilai sesuai dengan hasil pengukuran kesalahan acak lainnya. Dari artikel ini Anda akan belajar cara mengimplementasikan perhitungan menggunakan metode kuadrat terkecil di Excel.

Menetapkan tugas pada contoh tertentu

Misalkan ada dua indikator X dan Y. dan Y tergantung pada X. Karena MNK tertarik pada kita dari sudut pandang analisis regresi (di Excel, metodenya diimplementasikan menggunakan fungsi tertanam), maka perlu segera pergi ke pertimbangan tugas tertentu.

Jadi, biarkan x menjadi area perdagangan dari toko makanan, diukur dalam meter persegi, dan omset tahunan Y, ditentukan dalam jutaan rubel.

Diperlukan untuk membuat perkiraan perdagangan mana (Y) akan berada di toko, jika ia memiliki satu atau area perbelanjaan lainnya. Jelas, fungsi y \u003d f (x) meningkat, karena hypermarket menjual lebih banyak produk daripada kios.

Beberapa kata tentang kebenaran data sumber yang digunakan untuk memprediksi

Misalkan kita memiliki tabel yang dibangun sesuai dengan data untuk n shops.

Menurut statistik matematika, hasilnya akan lebih atau kurang benar jika data untuk setidaknya 5-6 objek diselidiki. Selain itu, tidak mungkin untuk menggunakan hasil "anomali". Secara khusus, butik kecil elit mungkin memiliki omset pada waktu yang lebih besar daripada omset perdagangan titik perdagangan besar dari kelas Masmarket.

Esensi dari metode

Data tabel dapat digambarkan pada bidang decartian dalam bentuk poin m 1 (x 1, y 1), ... m n (x n, y n). Sekarang solusi untuk masalah dikurangi menjadi pemilihan fungsi perkiraan y \u003d f (x), yang memiliki grafik, lewat sedekat mungkin ke titik M 1, M 2, ..

Tentu saja, dimungkinkan untuk menggunakan polinomial tingkat tinggi, tetapi opsi ini tidak hanya lebih sulit, tetapi tidak benar, karena itu tidak akan mencerminkan tren utama yang diperlukan untuk mendeteksi. Solusi yang paling masuk akal adalah mencari Langsung Y \u003d AX + B, yang paling membuat data eksperimental, dan lebih tepatnya, koefisien - A dan B.

Perkiraan akurasi

Dalam perkiraan apa pun, ia memperoleh kepentingan khusus untuk menilai keakuratannya. Dikatakan oleh e i perbedaan (deviasi) antara nilai fungsional dan eksperimental untuk titik x i, mis. E i \u003d y i - f (x i).

Jelas bahwa sejumlah penyimpangan dapat digunakan untuk memperkirakan keakuratan aproksimasi, yaitu ketika Anda memilih garis lurus untuk representasi perkiraan x dari Y, Anda perlu lebih memilih yang bernilai paling kecil dari jumlah EI di semua poin yang dipertimbangkan. Namun, tidak semuanya sangat sederhana, karena bersama dengan deviasi positif hampir akan hadir dan negatif.

Anda dapat memecahkan masalah menggunakan modul deviasi atau kotaknya. Metode terakhir didistribusikan secara luas. Ini digunakan di banyak bidang, termasuk analisis regresi (dalam excel implementasinya dilakukan dengan menggunakan dua fungsi bawaan), dan telah lama membuktikan efektivitasnya.

Metode Late Square.

Di Excel, seperti diketahui, ada fungsi autosumin bawaan, yang memungkinkan Anda menghitung nilai semua nilai yang terletak di rentang khusus. Dengan demikian, tidak ada yang mengganggu kita untuk menghitung nilai ekspresi (E 1 2 + E 2 2 + E 3 2 + ... E n 2).

Dalam catatan matematika, ia memiliki bentuk:

Karena awalnya memutuskan untuk mendekati dengan garis lurus, kami memiliki:

Dengan demikian, tugas menemukan garis lurus, yang paling menggambarkan ketergantungan spesifik dari nilai X dan Y, dikurangi menjadi perhitungan fungsi minimum dua variabel:

Untuk melakukan ini, perlu untuk menyamakan nol derivatif swasta sesuai dengan variabel baru A dan B, dan memecahkan sistem primitif yang terdiri dari dua persamaan dengan 2 spesies yang tidak diketahui:

Setelah transformasi sederhana, termasuk Divisi pada 2 dan manipulasi dengan jumlah, kami dapatkan:

Memecahkannya, misalnya, oleh perangkat, kami mendapatkan titik stasioner dengan beberapa koefisien A * dan B *. Ini minimum, yaitu, untuk prediksi, perdagangan mana yang akan berada di toko pada area tertentu, garis lurus y \u003d a * x + b * cocok, yang merupakan model regresi untuk contoh, yang dibahas. Tentu saja, itu tidak akan memungkinkan untuk menemukan hasil yang akurat, tetapi akan membantu mendapatkan gambaran apakah pembelian area beton akan dibeli.

Cara menerapkan metode kuadrat terkecil di Excel

Dalam "Excel" ada fungsi untuk menghitung nilai mna. Ini memiliki bentuk berikut: "tren" (diketahui. Nilai Y; dikenal. X Nilai; Nilai baru x; Const.). Oleskan formula perhitungan MNC di Excel ke tabel kami.

Untuk melakukan ini, di sel di mana hasil perhitungan kotak yang lebih kecil harus ditampilkan di Excel, kami memperkenalkan tanda "\u003d" dan pilih fungsi "tren". Di jendela yang terbuka, isi bidang yang sesuai, sorotan:

• rentang nilai yang diketahui untuk Y (dalam hal ini, data untuk omset);
• rentang x 1, ... x n, mis. Nilai ruang ritel;
• dan nilai X yang terkenal, dan tidak diketahui yang perlu untuk mengetahui ukuran omset (untuk informasi tentang lokasi mereka pada lembar kerja, lihat di bawah).

Selain itu, rumus memiliki variabel logis "Const". Jika Anda masuk ke bidang yang sesuai 1, ini akan berarti bahwa perhitungan harus dilakukan, percaya bahwa b \u003d 0.

Jika Anda perlu mempelajari perkiraan untuk lebih dari satu nilai X, maka setelah memasukkan rumus, Anda tidak boleh mengklik pada "Enter", tetapi Anda perlu memanggil "Shift" + "Control" + "Enter" pada kombinasi papan ketik.

Beberapa fitur

Analisis regresi dapat diakses bahkan untuk teko. Formula Excel untuk memprediksi nilai array variabel yang tidak diketahui - "tren" - bahkan mereka yang belum pernah mendengar tentang metode kuadrat terkecil dapat digunakan. Sudah cukup hanya untuk mengetahui beberapa fitur pekerjaannya. Khususnya:

• Jika Anda mengatur kisaran nilai-nilai yang diketahui dari variabel Y dalam satu baris atau kolom, setiap baris (kolom) dengan nilai X yang diketahui akan dirasakan oleh program sebagai variabel terpisah.
• Jika jendela "tren" tidak menentukan kisaran dengan X yang dikenal, maka dalam hal menggunakan fungsi di Excel, program akan mempertimbangkannya sebagai array yang terdiri dari bilangan bulat, jumlah yang sesuai dengan nilai yang ditentukan dengan nilai-nilai yang ditentukan Dari variabel y.
• Untuk mendapatkan array nilai "yang diprediksi" di pintu keluar, ekspresi untuk menghitung tren harus diberikan sebagai formula array.
• Jika nilai X baru tidak ditentukan, fungsi "tren" mempertimbangkannya sama dengan yang terkenal. Jika tidak ditentukan, array 1 diambil sebagai argumen; 2; 3; 4; ..., yang sepadan dengan kisaran dengan parameter yang sudah ditentukan Y.
• Kisaran yang berisi nilai-nilai baru X harus terdiri dari baris atau kolom yang sama atau lebih sebagai kisaran dengan nilai y yang ditentukan. Dengan kata lain, itu harus proporsional dengan variabel independen.
• Dalam array dengan nilai x yang diketahui, beberapa variabel dapat terkandung. Namun, jika kita hanya berbicara tentang satu, diperlukan bahwa rentang dengan nilai X dan Y yang ditentukan proporsional. Dalam hal beberapa variabel, perlu kisaran dengan nilai-nilai yang ditentukan dari Y ditampung dalam satu kolom atau dalam satu baris.

Fungsi Predicz.

Analisis regresi di Excel diimplementasikan menggunakan beberapa fungsi. Salah satunya disebut "prediksi". Ini mirip dengan "tren", yaitu masalah perhitungan menggunakan metode kuadrat terkecil. Namun, hanya untuk satu X, yang Y tidak diketahui.

Sekarang Anda tahu rumus di Excel untuk teko, memungkinkan untuk memprediksi nilai nilai masa depan satu atau indikator lain sesuai dengan tren linier.

Salah satu metode untuk mempelajari ikatan stokastik antara tanda-tanda adalah analisis regresi.
Analisis regresi adalah kesimpulan dari persamaan regresi, yang dengannya ada nilai rata-rata variabel acak (hasil tanda), jika nilai variabel lain (atau lainnya) (faktor) diketahui. Ini termasuk langkah-langkah berikut:

1. pemilihan bentuk komunikasi (jenis persamaan regresi analitik);
2. perkiraan parameter persamaan;
3. penilaian kualitas persamaan regresi analitik.

Paling sering untuk memperkirakan penggunaan parameter metode kuadrat terkecil (MNC).
Metode kuadrat terkecil memberikan estimasi terbaik (kaya, efisien dan tidak terkunci) dari parameter persamaan regresi. Tetapi hanya jika prasyarat tertentu dilakukan relatif terhadap istilah acak (U) dan variabel independen (X) (lihat latar belakang MNC).

Masalah mengevaluasi parameter persamaan pasangan linier dengan metode kuadrat terkecil Ini terdiri dari yang berikut: Untuk mendapatkan perkiraan parameter tersebut, di mana jumlah kuadrat penyimpangan nilai aktual dari tanda efektif - y i pada nilai yang dihitung minimal.
Secara formal kriteria mnk. Anda dapat menulis seperti ini: .

Klasifikasi metode kuadrat terkecil

1. Metode kuadrat terkecil.
2. Metode maksimum yang sebenarnya (untuk model regresi linier klasik normal, normalitas residu regresi ditunda).
3. Metode umum kuadrat omna yang lebih kecil digunakan jika terjadi autokorelasi kesalahan dan dalam kasus heterosdastisitas.
4. Metode kuadrat terkecil ditangguhkan (kasus khusus OMNA dengan residu heter-visasik).

Kami mengilustrasikan esensi metode persegi terkecil klasik secara grafis. Untuk melakukan ini, kami membangun jadwal titik sesuai dengan pengamatan (x i, y i, i \u003d 1; n) dalam sistem koordinat persegi panjang (grafik titik seperti itu disebut bidang korelasi). Kami akan mencoba memilih garis lurus yang paling dekat dengan titik-titik bidang korelasi. Menurut metode kuadrat terkecil, garis dipilih sehingga jumlah kuadrat jarak vertikal antara titik-titik medan korelasi dan garis ini akan minimal.

Catatan matematika tugas ini: .
Nilai-nilai y i dan x i \u003d 1 ... n dikenal oleh kita, ini adalah data observasional. Dalam fungsi S, mereka konstanta. Variabel dalam fitur ini adalah estimasi parameter yang diinginkan -,. Untuk menemukan minimal fungsi 2-variabel, perlu untuk menghitung turunan swasta fungsi ini untuk masing-masing parameter dan menyamakannya nol, I.E. .
Akibatnya, kami memperoleh sistem 2 persamaan linear normal:
Memecahkan sistem ini, kami akan menemukan estimasi parameter yang diinginkan:

Kebenaran perhitungan parameter persamaan regresi dapat diuji dengan membandingkan jumlah (mungkin beberapa perbedaan karena perhitungan pembulatan).
Untuk menghitung estimasi parameter, Anda dapat membangun Tabel 1.
Tanda koefisien regresi menunjukkan arah komunikasi (jika b\u003e 0, baris langsung, jika b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Secara formal nilai parameter A adalah nilai rata-rata Y dengan x sama dengan nol. Jika penandatangan tidak memiliki dan tidak dapat memiliki nilai nol, maka interpretasi di atas dari parameter dan tidak masuk akal.

Estimasi Ketat Komunikasi Antara Tanda Ini dilakukan dengan menggunakan koefisien korelasi pasangan linier - R X, Y. Itu dapat dihitung oleh rumus: . Selain itu, koefisien korelasi pasangan linier dapat ditentukan melalui koefisien regresi B: .
Area nilai yang diizinkan dari koefisien linear korelasi pasangan dari -1 hingga +1. Tanda koefisien korelasi menunjukkan arah komunikasi. Jika r x, y\u003e 0, maka koneksi lurus; Jika r x, y<0, то связь обратная.
Jika koefisien ini dekat dengan satu, koneksi antara fitur dapat ditafsirkan sebagai linear yang cukup dekat. Jika modulnya sama dengan unit ê r x, y ê \u003d 1, koneksi antara tanda-tanda linear fungsional. Jika tanda-tanda X dan Y secara linear independen, maka R X, Y mendekati 0.
Untuk menghitung R X, Y juga dapat menggunakan Tabel 1.

Untuk menilai kualitas persamaan regresi yang diperoleh, koefisien determinasi teoritis dihitung - R 2 YX:

,
di mana D 2 adalah dispersi Y; dijelaskan oleh persamaan regresi;
e 2 - dispersi residual (persamaan regresi yang tidak dijelaskan);
s 2 y adalah dispersi total (lengkap)
Koefisien determinasi mencirikan proporsi variasi (dispersi) dari tanda efektif Y, dijelaskan oleh regresi (dan, akibatnya, faktor X), dalam variasi umum (dispersi) y. Koefisien determinasi R 2 YX mengambil nilai dari 0 hingga 1. Dengan demikian, nilai 1-R 2 YX mengkarakterisasi fraksi dispersi Y yang disebabkan oleh pengaruh faktor-faktor lain yang tidak terhitung dalam model dan kesalahan spesifikasi.
Dengan Regresi Linier Paired R 2 YX \u003d R 2 YX.

Yang merupakan aplikasi yang lebih luas di berbagai bidang ilmu pengetahuan dan kegiatan praktis. Itu bisa menjadi fisika, kimia, biologi, ekonomi, sosiologi, psikologi, dan sebagainya, sebagainya. Kehendak nasib sering harus berurusan dengan perekonomian, dan karena itu hari ini saya akan mengeksekusi Anda junior di negara yang luar biasa bernama Ekonometri. \u003d) ... bagaimana Anda tidak menginginkannya?! Ada yang sangat baik - Anda hanya perlu memutuskan! ... tapi inilah kenyataan bahwa Anda mungkin ingin - itu untuk belajar menyelesaikan tugas metode kuadrat terkecil. Dan terutama pembaca yang rajin akan belajar untuk menyelesaikannya tidak hanya tidak salah lagi, tetapi juga sangat cepat ;-) Tapi pertama-tama pengaturan Umum Tugas + Contoh terkait:

Misalkan di beberapa bidang subjek, indikator yang memiliki ekspresi kuantitatif diselidiki. Dalam hal ini, ada setiap alasan untuk percaya bahwa indikator tergantung pada indikator. Bantuan ini dapat menjadi hipotesis ilmiah, serta berdasarkan akal sehat dasar. Cuti, namun, sains di samping dan jelajahi lebih banyak area selera - yaitu, toko makanan. Dilincangkan dengan:

- Area perbelanjaan toko makanan, sq.m.,
- Pergantian tahunan toko makanan, juta rubel.

Jelas bahwa semakin besar area toko, semakin banyak kasus akan ada lebih dari omsetnya.

Misalkan setelah melakukan pengamatan / eksperimen / hitungan / tarian dengan rebana yang tersedia adalah data numerik:

Dengan para tamu, saya pikir semuanya jelas: - Ini adalah area toko 1, - pergantian tahunannya, - area toko ke-2, - omset tahunan, dll. Ngomong-ngomong, tidak perlu memiliki akses ke bahan rahasia sama sekali - perkiraan yang cukup akurat dari omset dapat diperoleh dengan cara statistik matematika. Namun, kami tidak terganggu, jalannya spionase komersial sudah dibayar \u003d)

Data Tabar juga dapat ditulis dalam bentuk titik dan menggambarkan yang biasa bagi kami. sistem Cartesian. .

Balas pertanyaan penting: berapa banyak poin yang dibutuhkan untuk penelitian berkualitas tinggi?

Lebih besar lebih baik. Set minimum yang diizinkan terdiri dari 5-6 poin. Selain itu, dengan sejumlah kecil data, hasil "abnormal" tidak dapat dimasukkan dalam sampel. Jadi, misalnya, toko elit kecil dapat membantu "rekan kerja mereka", sehingga mendistorsi pola keseluruhan, yang diperlukan untuk menemukan!

Jika Anda hanya perlu memilih fungsi, susunan acara yang berlalu sedekat pintu . Fitur ini disebut perkiraan (aproksimasi - aproksimasi) atau fungsi teoretis . Secara umum, di sini segera muncul "pemohon" yang jelas - tingkat tinggi, yang jadwalnya melewati semua poin. Tetapi opsi ini rumit, dan seringkali tidak benar (Karena jadwal akan "loop" sepanjang waktu dan kurang mencerminkan tren utama).

Dengan demikian, fungsi yang diinginkan harus cukup sederhana dan pada saat yang sama mencerminkan ketergantungan yang memadai. Bagaimana menurut Anda, salah satu metode untuk menemukan fungsi tersebut dan disebut metode kuadrat terkecil. Pertama kita akan menganalisisnya secara umum. Biarkan beberapa fungsi membawa data eksperimen:


Bagaimana memperkirakan keakuratan perkiraan ini? Hitung dan perbedaan (penyimpangan) antara nilai-nilai eksperimen dan fungsional (Belajar menggambar). Pikiran pertama yang terlintas dalam pikiran adalah untuk mengevaluasi seberapa besar jumlahnya, tetapi masalahnya adalah bahwa perbedaannya mungkin negatif (misalnya, ) Dan penyimpangan sebagai akibat dari penjumlahan ini akan dipisahkan bersama. Oleh karena itu, sebagai perkiraan keakuratan perkiraan, ia cocok untuk menerima jumlah tersebut modul. Penyimpangan:

atau dalam bentuk twisted: (Tiba-tiba seseorang tidak tahu: - Ini adalah ikon SUM, dan variabel bantu "konter", yang mengambil nilai dari 1 ke).

Mendekati poin eksperimental dengan berbagai fungsi, kami akan menerima nilai yang berbeda, dan jelas, di mana jumlah ini kurang dari fungsi dan lebih akurat.

Metode ini ada dan disebut itu metode modul paling sedikit. Namun, dalam praktiknya, ia menerima lebih banyak distribusi metode Late Square.Di mana nilai negatif yang mungkin tidak dihilangkan oleh modul, tetapi konstruksi penyimpangan di alun-alun:

, setelah itu upaya diarahkan pada pemilihan fungsi semacam itu sehingga jumlah kuadrat penyimpangan Itu sesedikit mungkin. Sebenarnya, karenanya nama metode.

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

- Poin termudah untuk menggambarkan Dalam menggambar dan menganalisis lokasi mereka. Jika mereka cenderung ditempatkan dalam garis lurus, maka Anda harus mencari persamaan langsung dengan nilai-nilai optimal dan. Dengan kata lain, tantangannya adalah menemukan koefisien seperti itu - sehingga jumlah kuadrat penyimpangan adalah yang terkecil.

Jika poinnya terletak, misalnya, oleh hyperball., Tidak jelas bahwa fungsi linier akan memberikan pendekatan yang buruk. Dalam hal ini, kami mencari koefisien yang paling "menguntungkan" untuk persamaan hiperbola - Mereka yang memberikan jumlah minimum kuadrat .

Sekarang perhatikan bahwa dalam kedua kasus yang kita bicarakan fungsi dua variabelArgumen siapa parameter dependensi yang diinginkan:

Dan pada dasarnya, kita perlu menyelesaikan tugas standar - untuk menemukan fungsi minimum dua variabel.

Ingat contoh kami: Misalkan titik "toko" cenderung terletak di garis lurus dan ada setiap alasan untuk berasumsi itu ketergantungan linear Perputaran komoditas dari area perbelanjaan. Kami akan menemukan koefisien seperti itu "A" dan "Be" dengan jumlah kuadrat penyimpangan Itu yang terkecil. Semuanya seperti biasa - pertama derivatif swasta dari urutan pertama. Berdasarkan aturan linearitas Anda dapat membedakan langsung di bawah ikon jumlah:

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

Mari kita buat sistem standar:

Kami mengurangi setiap persamaan pada "Deuce" dan, sebagai tambahan, jumlah "runtuh":

Catatan : Analisis secara independen mengapa "A" dan "be" dapat diambil dari ikon SUM. Ngomong-ngomong, itu bisa dilakukan secara formal dengan jumlahnya

Tulis ulang sistem dalam bentuk "Terapan":

Setelah itu, algoritma penyelesaian tugas kami dimulai:

Koordinat poin yang kita tahu? Kita tahu. Jumlah Bisakah kita temukan? Mudah. Make up lebih sederhana sistem dua persamaan linear dengan dua tidak diketahui("A" dan "be"). Pecahkan sistem, misalnya, metode Cramer.Akibatnya, kami mendapatkan poin stasioner. Memeriksa kondisi ekstrem yang cukup, Anda dapat memastikan bahwa pada titik ini fungsi Mencapai persis minimum. Pemeriksaan dikaitkan dengan perhitungan tambahan dan karenanya biarkan untuk adegan (Jika perlu, bingkai yang hilang dapat dilihat). Kami membuat kesimpulan terakhir:

Fungsi jalan terbaik (setidaknya dibandingkan dengan fungsi linear lainnya) Mengikat poin eksperimental . Secara kasar, jadwalnya berlalu sedekat mungkin dengan poin-poin ini. Dalam tradisi econometrics. Fungsi perkiraan yang dihasilkan juga disebut persamaan Regresi Linier Paired .

Masalah yang sedang dipertimbangkan memiliki nilai praktis yang hebat. Dalam situasi dengan teladan kita, persamaan Memungkinkan Anda memprediksi pergantian perdagangan apa ("Igarek") akan berada di toko, dengan nilai area perdagangan yang berbeda (Tom atau makna lainnya "x"). Ya, perkiraan yang dihasilkan hanya akan diperkirakan, tetapi dalam banyak kasus itu akan cukup akurat.

Saya akan mencari tahu hanya satu tugas dengan angka "nyata", karena tidak ada kesulitan di dalamnya - semua perhitungan di tingkat program sekolah 7-8 kelas. Dalam 95 persen kasus, Anda akan diundang untuk menemukan fungsi linear, tetapi pada akhir artikel saya akan menunjukkan bahwa tidak lebih sulit untuk menemukan persamaan hiperbola yang optimal, peserta pameran dan beberapa fungsi lainnya.

Bahkan, itu tetap mendistribusikan roti yang dijanjikan - sehingga Anda belajar memecahkan contoh-contoh seperti itu tidak hanya secara akurat, tetapi juga dengan cepat. Hati-hati mempelajari standar:

Sebuah tugas

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

Metode kuadrat yang lebih kecil menemukan fungsi linear yang paling baik membawa empiris (berpengalaman) data. Buat gambar di mana dalam sistem koordinat persegi panjang Cartesian untuk membangun poin eksperimental dan grafik fungsi yang mendekati . Temukan jumlah kuadrat penyimpangan antara nilai-nilai empiris dan teoretis. Cari tahu apakah fungsinya akan lebih baik (Dari sudut pandang metode kuadrat terkecil) Oleskan poin eksperimental.

Perhatikan bahwa nilai "ICS" alami, dan memiliki makna yang bermakna karakteristik, yang akan saya ceritakan sedikit nanti; Tapi mereka, tentu saja, bisa pecahan. Selain itu, tergantung pada konten satu tugas sebagai "ICX", dan nilai "mengabaikan" dapat sepenuhnya atau sebagian negatif. Ya, kami memiliki tugas "berwajah", dan kami memulainya keputusan:

Koefisien fungsi optimal akan menemukan sebagai solusi sistem:

Untuk perekaman yang lebih kompak, variabel "counter" dapat dihilangkan, karena jelas bahwa penjumlahan dilakukan dari 1 hingga.

Perhitungan jumlah yang diperlukan lebih nyaman untuk diatur dalam bentuk tabel:


Perhitungan dapat dilakukan pada mikrokal, tetapi jauh lebih baik untuk menggunakan Excel - dan lebih cepat, dan tanpa kesalahan; Kami menonton video pendek:

Dengan demikian, kami mendapatkan yang berikut sistem:

Di sini Anda dapat mengalikan persamaan kedua untuk 3 dan dari persamaan 1 untuk mengurangi ke-2. Tetapi keberuntungan ini - dalam praktiknya sistem lebih sering tidak berbakat, dan dalam kasus tersebut menghemat metode Cramer.:
Jadi sistem memiliki solusi tunggal.

Lakukan pemeriksaan. Saya mengerti bahwa saya tidak mau, tetapi mengapa melewatkan kesalahan di mana mereka tidak bisa benar-benar terlewatkan? Ganti solusi yang ditemukan di bagian kiri setiap persamaan sistem:

Bagian kanan dari persamaan masing-masing diperoleh, itu berarti bahwa sistem diselesaikan dengan benar.

Dengan demikian, fungsi perkiraan yang diinginkan: - dari semua fungsi linier Data eksperimental terbaik mendekatinya.

Tidak seperti lurus Ketergantungan turnover toko dari kuadratnya, ketergantungan yang ditemukan adalah terbalik (prinsip "semakin sedikit"), dan fakta ini segera terdeteksi oleh negatif koefisien sudut.. Fungsi memberi tahu kami bahwa dengan peningkatan indikator tertentu pada 1 unit, nilai indikator dependen berkurang rata-rata0,65 unit. Seperti yang mereka katakan, semakin tinggi harga soba, semakin sedikit dijual.

Untuk membangun grafik fungsi perkiraan, kami akan menemukan dua nilainya:

dan lakukan gambar:


Baris yang dibangun disebut garis tren (Yaitu - garis tren linier, I.E. Dalam kasus umum, tren belum tentu garis lurus). Semua ekspresi akrab "berada dalam tren", dan, saya pikir istilah ini tidak memerlukan komentar tambahan.

Hitung jumlah kuadrat penyimpangan antara nilai empiris dan teoretis. Secara geometris - ini adalah jumlah kuadrat dari panjang segmen "raspberry" (dua di antaranya sangat kecil sehingga mereka bahkan tidak terlihat).

Perhitungan mari kita dalam tabel:


Mereka dapat dilakukan lagi secara manual, kalau-kalau saya akan membawa contoh untuk poin 1:

Tetapi jauh lebih efisien untuk melakukan cara yang diketahui:

Sekali lagi, ulangi: apa arti hasilnya? Dari semua fungsi linier fungsi Indikatornya adalah yang terkecil, yaitu dalam keluarganya, ini adalah perkiraan terbaik. Dan di sini, omong-omong, pertanyaan terakhir masalahnya tidak disengaja: bagaimana jika fungsi eksponensial yang diusulkan Apakah akan lebih baik membawa poin eksperimental?

Kami menemukan jumlah kuadrat penyimpangan yang sesuai - untuk membedakan, saya akan menunjukkan huruf mereka "epsilon". Tekniknya persis sama:


Dan lagi untuk setiap perhitungan api untuk poin 1:

Di Excel, kami menggunakan fitur standar Ecel (Sintaks dapat dilihat dalam Bantuan Exele).

Keluaran:, Oleh karena itu, fungsi eksponensial membawa poin eksperimental lebih buruk daripada langsung .

Tetapi harus dicatat bahwa "lebih buruk" adalah jangan berarti, apa yang salah. Sekarang membangun grafik fungsi eksponensial ini - dan dia juga lewat dekat dengan poin - Ya, sehingga tanpa studi analitis dan sulit untuk dikatakan, fungsi apa yang lebih akurat.

Pada keputusan ini selesai, dan saya kembali ke pertanyaan tentang nilai-nilai alami dari argumen. Dalam berbagai penelitian, sebagai aturan, ekonomi atau sosiologis, bulan numerik "es", tahun atau interval waktu yang sama lainnya. Pertimbangkan, misalnya, tugas seperti itu.

4.1. Menggunakan fungsi bawaan

Perhitungan koefisien resesi. dilakukan dengan menggunakan fungsi

Linene.(Nilai_Y.; NILAI_X.; const; statistik),

Nilai_Y. - Array nilai y

NILAI_X.- Array nilai opsional x.Jika suatu array h. dihilangkan, maka diasumsikan bahwa array ini (1; 2; 3; ...) dengan ukuran yang sama dengan Nilai_Y.,

const- Nilai logis yang menunjukkan apakah diperlukan konstan dgn B. sama dengan 0. Jika const memiliki arti BENAR atau dihilangkan kemudian dgn B. Dihitung dengan cara biasa. Jika argumennya const Itu penting bohong, kalau begitu dgn B. bergantung sama dengan 0 dan nilai sEBUAH. dipilih sehingga rasio y \u003d kapak.

Statistik- Nilai logis yang menunjukkan apakah akan mengembalikan statistik regresi tambahan. Jika argumennya Statistik memiliki arti BENAR, lalu berfungsi Linene. Mengembalikan statistik regresi tambahan. Jika argumennya Statistik memiliki arti SALAH atau dihilangkan, maka fungsinya Linene. Hanya mengembalikan koefisien sEBUAH. dan konstan dgn B..

Harus diingat bahwa hasil dari fungsi Linene ()banyak nilai - array.

Untuk perhitungan koefisien korelasi Fungsi yang digunakan

Cornel.(Besar-besaran1.;Besar2.),

mengembalikan nilai koefisien korelasi di mana Besar-besaran1. - Array nilai y., Besar2. - Array nilai x.. Besar-besaran1. dan Besar2. Harus satu dimensi.

Contoh 1.. Kecanduan y.(x.) Disajikan dalam tabel. Membangun garis regresi dan menghitung koefisien korelasi.

Kami memperkenalkan tabel nilai ke lembar MS Excel dan membangun jadwal titik. Daftar Kerja akan mengambil tampilan yang digambarkan pada Gambar. 2.

Untuk menghitung nilai koefisien regresi tapidan dgn B.highlight A7: B7, Beralih ke master fungsi dan dalam kategori Statistik Pilih fitur Linene.. Isi kotak dialog yang muncul seperti yang ditunjukkan pada Gambar. 3 dan klik BAIK..

Akibatnya, nilai yang dihitung hanya akan muncul di sel A6. (Gbr.4). Agar nilai muncul di dalam sel B6. Anda perlu memasukkan mode pengeditan (kunci F2)dan kemudian tekan kombinasi tombol Ctrl + Shift + Enter.

Untuk menghitung nilai koefisien korelasi di dalam sel C6. Formula berikut diperkenalkan:

C7 \u003d CORVELA (B3: J3; B2: J2).

Mengetahui koefisien regresi tapidan dgn B. Hitung nilai fungsi y.=kAPAK.+dgn B. Untuk ditentukan x.. Untuk melakukan ini, kami memperkenalkan formula

B5 \u003d $ A $ 7 * B2 + $ B $ 7

dan salin dalam kisaran C5: J5.(Gbr. 5).

Saya akan menunjukkan garis regresi dalam diagram. Sorot Poin Eksperimental pada grafik, klik kanan dan pilih perintah Data awal. Di kotak dialog yang muncul (Gbr. 5), pilih tab Baris dan klik tombol Menambahkan. Isi bidang input, seperti yang ditunjukkan pada Gambar. 6 dan tekan tombol baik. Garis regresi akan ditambahkan ke jadwal data eksperimental. Secara default, jadwalnya akan digambarkan dalam bentuk poin yang tidak dihubungkan dengan garis smoothing.

Ara. 6.

Untuk mengubah jenis jalur regresi, jalankan langkah-langkah berikut. Klik kanan pada poin yang menggambarkan jadwal baris, pilih perintah Jenis diagram.dan atur jenis diagram titik, seperti yang ditunjukkan pada Gambar. 7.

Jenis garis, warna dan ketebalannya dapat diubah sebagai berikut. Pilih baris pada diagram, klik tombol kanan mouse dan pilih perintah dalam menu konteks Format seri data ... Selanjutnya, buat instalasi, misalnya, seperti yang ditunjukkan pada Gambar. delapan.

Sebagai hasil dari semua transformasi, kami memperoleh grafik data eksperimental dan garis regresi di satu area grafis (Gbr. 9).

4.2. Menggunakan garis tren.

Konstruksi berbagai ketergantungan perkiraan di MS Excel diimplementasikan sebagai grafik properti - garis tren.

Contoh 2.. Sebagai hasil dari percobaan, beberapa ketergantungan tabular ditentukan.

Pilih dan bangun kecanduan perkiraan. Membangun grafik tabel dan ketergantungan analitik yang dipilih.

Pemecahan masalah dapat dibagi menjadi langkah-langkah berikut: memasuki data sumber, membangun jadwal titik dan menambahkan garis tren ke grafik ini.

Pertimbangkan proses ini secara rinci. Kami memperkenalkan data sumber ke lembar kerja dan membangun grafik data eksperimental. Selanjutnya, pilih titik eksperimen pada grafik, klik kanan dan gunakan perintah Menambahkanl. tren dan tren (Gbr. 10).

Kotak dialog yang muncul memungkinkan Anda untuk membangun ketergantungan yang mendekati.

Pada tab pertama (Gbr. 11) dari jendela ini, jenis ketergantungan perkiraan diindikasikan.

Pada yang kedua (Gbr. 12) mendefinisikan parameter konstruksi:

· Nama ketergantungan yang mendekati;

· Prakiraan maju (kembali) pada n. Unit (parameter ini menentukan jumlah unit di depan (belakang) mana yang harus diperluas garis tren);

· Tunjukkan apakah titik persimpangan kurva dengan garis lurus y \u003d const.;

· Tampilkan fungsi perkiraan pada diagram atau tidak (parameter untuk menunjukkan persamaan pada diagram);

· Apakah diagram standar deviasi atau tidak (parameter ditempatkan pada diagram nilai keakuratan aproksimasi).

Kami memilih polinomial tingkat kedua sebagai ketergantungan yang mendekati (Gbr. 11) dan menghapus persamaan yang menggambarkan polinomial ini pada grafik (Gbr. 12). Diagram yang dihasilkan disajikan pada Gambar. 13.

Begitu pula dengan bantuan garis tren Anda dapat memilih parameter dependensi tersebut sebagai

· Linier y.=a ∙ X.+dgn B.,

· Logaritma y.=a ∙ ln.(x.)+dgn B.,

· Eksponensial y.=a ∙ e b,

· Kekuasaan y.=a ∙ x b,

· Polinomial. y.=a ∙ X. 2 +b ∙ x.+c., y.=a ∙ X. 3 +b ∙ x. 2 +c ∙ x + d Dan seterusnya, ke tingkat ke-6 inklusif polinomial,

· Penyaringan linier.

4.3. Menggunakan blok yang menentukan

Bunga yang signifikan adalah implementasi parameter di MS Excel menggunakan kotak yang lebih kecil menggunakan blok yang menentukan. Teknik ini memungkinkan Anda untuk memilih parameter fungsi apa pun. Pertimbangkan fitur ini pada contoh tugas berikut.

Contoh 3.. Sebagai hasil dari percobaan, ketergantungan z (t) disajikan dalam tabel

Ambil koefisien kecanduan Z (t) \u003d pada 4 + bt 3 + ct 2 + dt + k Metode kuadrat terkecil.

Tugas ini setara dengan tugas menemukan fungsi minimum lima variabel.

Pertimbangkan proses pemecahan masalah optimasi (Gbr. 14).

Biarkan makna TAPI, DI, DARI, D. dan UNTUK Disimpan dalam sel A7: E7.. Menghitung nilai-nilai teoritis fungsi Dgn zat(t.)=Pada 4 + bt 3 + ct 2 + dt + k Untuk ditentukan t.(B2: j2.). Untuk melakukan ini di sel B4. Kami memasukkan nilai fungsi di titik pertama (sel B2.):

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

Salin formula ini dalam kisaran C4: J4. Dan kami memperoleh nilai yang diharapkan dari fungsi pada titik, absen yang disimpan dalam sel B2: j2..

Di sel. B5. Kami memperkenalkan formula yang menghitung kuadrat dari perbedaan antara titik eksperimen dan dihitung:

B5 \u003d (B4-B3) ^ 2,

dan salin dalam kisaran C5: J5.. Di sel. F7. Kami akan menyimpan kesalahan kuadrat total (10). Untuk melakukan ini, kami memperkenalkan formula:

F7 \u003d Jumlah (B5: J5).

Kami menggunakan tim Service®Poisk Solutions. dan memecahkan masalah optimasi tanpa batasan. Isi bidang input yang sesuai di kotak dialog yang ditunjukkan pada Gambar. 14 dan tekan tombol Melakukan. Jika solusinya ditemukan, jendela ditunjukkan pada Gambar. limabelas.

Hasil dari blok yang menentukan akan menjadi kesimpulan dalam sel A7: E7.nilai parameter Fungsi Dgn zat(t.)=Pada 4 + bt 3 + ct 2 + dt + k. Dalam sel B4: J4. Menerima fungsi nilai yang diharapkan Pada titik awal. Di sel. F7. akan disimpan total Kesalahan Kuadrat.

Memetakan titik eksperimental dan baris yang dipilih dalam satu area grafis dapat jika dialokasikan rentang B2: j4.Panggilan Master Chart.Dan kemudian memformat tampilan grafik yang diterima.

Ara. 17 Menampilkan daftar kerja MS Excel setelah perhitungan dilakukan.

5. Daftar referensi

1. Alekseev E.r., chesnokova o.v., memecahkan masalah komputasi matematika dalam paket mathcad12, matlab7, maple9. - NT Press, 2006.-596c. : Il. - (tutorial)

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

3. Berezin I.S., Lodine N.P., metode perhitungan. - M.: Sains, 1966.-632C.

4. Garnaev A.Yu., menggunakan MS Excel dan VBA di bidang ekonomi dan keuangan. - SPB.: BHV - Petersburg, 1999.-332C.

5. Demidovich B.P., Maron dan A., Shuvalova v.z., metode numerik untuk menganalisis. - M.: Sains, 1967.-368С.

6. Jagung G., Korn T., Direktori Matematika untuk Ilmuwan dan Insinyur. - M., 1970, 720-an.

7. Alekseev E.r., Chesnokova O.V. Instruksi metodis untuk melakukan pekerjaan laboratorium di MS Excel. Untuk siswa dari semua spesialisasi. Donetsk, Donntu, 2004. 112 p.