LSM untuk fungsi dua variabel. Perkiraan data percobaan. Metode kuadrat terkecil. Implementasi praktis LSM untuk ketergantungan linier pada kalkulator yang tidak dapat diprogram

Contoh.

Data eksperimen tentang nilai-nilai variabel X Dan pada diberikan dalam tabel.

Sebagai hasil dari penyelarasan mereka, fungsinya

Menggunakan metode kuadrat terkecil, perkirakan data ini dengan ketergantungan linier y=ax+b(menemukan pilihan A Dan B). Cari tahu mana dari dua garis yang lebih baik (dalam arti metode kuadrat terkecil) menyelaraskan data eksperimen. Membuat gambar.

Inti dari metode kuadrat terkecil (LSM).

Masalahnya adalah menemukan koefisien ketergantungan linier yang merupakan fungsi dari dua variabel A Dan B mengambil nilai terkecil. Artinya, mengingat datanya A Dan B jumlah simpangan kuadrat dari data eksperimen dari garis lurus yang ditemukan akan menjadi yang terkecil. Ini adalah inti dari metode kuadrat terkecil.

Dengan demikian, solusi dari contoh tersebut direduksi menjadi mencari nilai ekstrem dari suatu fungsi dari dua variabel.

Penurunan rumus untuk mencari koefisien.

Sistem dua persamaan dengan dua yang tidak diketahui dikompilasi dan diselesaikan. Menemukan turunan parsial dari suatu fungsi sehubungan dengan variabel A Dan B, kita samakan turunan ini dengan nol.

Kami memecahkan sistem persamaan yang dihasilkan dengan metode apa pun (misalnya metode substitusi atau ) dan dapatkan rumus untuk mencari koefisien dengan menggunakan metode kuadrat terkecil (LSM).

Dengan data A Dan B fungsi mengambil nilai terkecil. Bukti fakta ini diberikan.

Itulah keseluruhan metode kuadrat terkecil. Rumus untuk menemukan parameter A berisi jumlah , , , dan parameter N- jumlah data percobaan. Nilai jumlah ini direkomendasikan untuk dihitung secara terpisah. Koefisien B ditemukan setelah perhitungan A.

Saatnya mengingat contoh aslinya.

Larutan.

Dalam contoh kita n=5. Kami mengisi tabel untuk kenyamanan menghitung jumlah yang termasuk dalam rumus koefisien yang diperlukan.

Nilai pada baris keempat tabel diperoleh dengan mengalikan nilai baris ke-2 dengan nilai baris ke-3 untuk setiap angka Saya.

Nilai-nilai pada baris kelima tabel diperoleh dengan mengkuadratkan nilai-nilai baris ke-2 untuk setiap angka Saya.

Nilai-nilai kolom terakhir dari tabel adalah jumlah dari nilai-nilai di seluruh baris.

Kami menggunakan rumus metode kuadrat terkecil untuk menemukan koefisien A Dan B. Kami menggantinya dengan nilai yang sesuai dari kolom terakhir tabel:

Karena itu, y=0,165x+2,184 adalah perkiraan garis lurus yang diinginkan.

Tetap mencari tahu garis mana y=0,165x+2,184 atau lebih baik mendekati data asli, yaitu membuat perkiraan menggunakan metode kuadrat terkecil.

Estimasi kesalahan metode kuadrat terkecil.

Untuk melakukan ini, Anda perlu menghitung jumlah simpangan kuadrat dari data asli dari garis-garis ini Dan , nilai yang lebih kecil sesuai dengan garis yang mendekati data asli dengan lebih baik dalam istilah metode kuadrat terkecil.

Sejak , maka garis y=0,165x+2,184 mendekati data asli dengan lebih baik.

Ilustrasi grafis dari metode kuadrat terkecil (LSM).

Semuanya tampak hebat di tangga lagu. Garis merah adalah garis yang ditemukan y=0,165x+2,184, garis biru adalah , titik merah muda adalah data asli.

Untuk apa ini, untuk apa semua perkiraan ini?

Saya pribadi menggunakan untuk menyelesaikan masalah perataan data, masalah interpolasi dan ekstrapolasi (dalam contoh asli, Anda dapat diminta untuk menemukan nilai dari nilai yang diamati y pada x=3 atau kapan x=6 menurut metode MNC). Tetapi kita akan membicarakannya lebih lanjut nanti di bagian lain situs ini.

Bukti.

Sehingga ketika ditemukan A Dan B fungsi mengambil nilai terkecil, perlu bahwa pada titik ini matriks bentuk kuadrat dari diferensial orde kedua untuk fungsi tersebut adalah positif pasti. Mari kita tunjukkan.

Ini memiliki banyak aplikasi, karena memungkinkan representasi perkiraan dari fungsi yang diberikan oleh fungsi lain yang lebih sederhana. LSM bisa sangat berguna dalam memproses pengamatan, dan secara aktif digunakan untuk memperkirakan beberapa besaran dari hasil pengukuran orang lain yang mengandung kesalahan acak. Pada artikel ini, Anda akan mempelajari cara 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 berapa omset (Y) yang akan dimiliki toko jika memiliki satu atau beberapa ruang ritel. 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 kurang lebih akan benar jika data pada setidaknya 5-6 objek diperiksa. Juga, hasil "anomali" tidak dapat digunakan. Secara khusus, butik kecil elit bisa memiliki omzet berkali-kali lipat dibandingkan omzet gerai besar sekelas “masmarket”.

Inti dari metode ini

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 tersebut akan direduksi menjadi pemilihan fungsi aproksimasi y = f (x), yang memiliki grafik yang melewati sedekat mungkin dengan titik M 1, M 2, .. M n .

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

Skor akurasi

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

Jelas, untuk menilai keakuratan perkiraan, Anda dapat menggunakan jumlah penyimpangan, yaitu, ketika memilih garis lurus untuk representasi perkiraan ketergantungan X pada Y, preferensi harus diberikan kepada 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. Metode yang terakhir adalah yang paling banyak digunakan. Ini digunakan di banyak bidang, termasuk analisis regresi (di Excel, penerapannya dilakukan dengan 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 dari semua nilai yang terletak di 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, ini terlihat seperti:

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

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

Ini membutuhkan persamaan dengan turunan parsial nol 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 membaginya dengan 2 dan memanipulasi jumlahnya, kita mendapatkan:

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

Cara menerapkan metode kuadrat terkecil di Excel

Excel memiliki fungsi untuk menghitung nilai kuadrat terkecil. Ini memiliki bentuk berikut: TREND (nilai Y yang diketahui; nilai X yang diketahui; nilai X baru; konstanta). Mari 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 kolom yang sesuai, sorot:

rentang nilai yang diketahui untuk Y (dalam hal ini data untuk turnover);
rentang x 1 , …x n , yaitu ukuran ruang ritel;
dan nilai x yang diketahui dan tidak diketahui, yang perlu Anda ketahui ukuran omsetnya (untuk informasi tentang lokasinya di lembar kerja, lihat di bawah).

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

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

Beberapa Fitur

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

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 larik 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 larik 1 diambil sebagai argumen; 2; 3; 4;…, yang sepadan dengan rentang dengan parameter yang sudah diberikan y.
Rentang yang berisi nilai x baru harus memiliki baris atau kolom yang sama atau lebih sebagai rentang dengan nilai y yang diberikan. Dengan kata lain, itu harus proporsional dengan variabel independen.
Array dengan nilai x yang diketahui dapat berisi banyak variabel. Namun, jika kita hanya berbicara tentang satu, maka rentang dengan nilai x dan y yang diberikan harus sepadan. Dalam kasus beberapa variabel, rentang dengan nilai y yang diberikan harus sesuai dengan satu kolom atau satu baris.

fungsi PERKIRAAN

Ini diimplementasikan menggunakan beberapa fungsi. Salah satunya disebut "PREDIKSI". Ini 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 suatu indikator menurut tren linier.

Metode kuadrat terkecil adalah salah satu yang paling umum dan paling berkembang karena sifatnya kesederhanaan dan efisiensi metode untuk memperkirakan parameter linier. Pada saat yang sama, kehati-hatian tertentu harus diperhatikan saat menggunakannya, karena model yang dibangun dengannya mungkin tidak memenuhi sejumlah persyaratan untuk kualitas parameternya dan, akibatnya, tidak mencerminkan pola pengembangan proses dengan "baik".

Mari kita pertimbangkan prosedur untuk memperkirakan parameter model ekonometrik linier menggunakan metode kuadrat terkecil secara lebih rinci. Model seperti itu dalam bentuk umum dapat diwakili oleh persamaan (1.2):

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t .

Data awal saat menaksir parameter a 0 , a 1 ,..., a n adalah vektor nilai dari variabel dependen y= (y 1 , y 2 , ... , y T)" dan matriks nilai variabel bebas

di mana kolom pertama, terdiri dari satu, sesuai dengan koefisien model .

Metode kuadrat terkecil mendapatkan namanya berdasarkan prinsip dasar bahwa estimasi parameter yang diperoleh berdasarkan itu harus memenuhi: jumlah kuadrat dari kesalahan model harus minimal.

Contoh penyelesaian masalah dengan metode kuadrat terkecil

Contoh 2.1. Perusahaan dagang tersebut memiliki jaringan yang terdiri dari 12 toko, informasi kegiatannya disajikan pada Tabel. 2.1.

Manajemen perusahaan ingin mengetahui berapa besaran tahunan bergantung pada area penjualan toko.

Tabel 2.1

Nomor toko	Omset tahunan, juta rubel	Area perdagangan, ribuan m 2

Solusi kuadrat terkecil. Mari kita tentukan - omset tahunan toko -th, juta rubel; - luas toko ke -th, ribuan m 2.

Gambar 2.1. Scatterplot untuk Contoh 2.1

Untuk menentukan bentuk hubungan fungsional antar variabel dan membuat scatterplot (Gambar 2.1).

Berdasarkan diagram pencar, kita dapat menyimpulkan bahwa omset tahunan bergantung secara positif pada area penjualan (yaitu, y akan meningkat seiring dengan pertumbuhan ). Bentuk koneksi fungsional yang paling tepat adalah − linier.

Informasi untuk perhitungan lebih lanjut disajikan pada Tabel. 2.2. Dengan menggunakan metode kuadrat terkecil, kami memperkirakan parameter model ekonometrik satu faktor linier

Tabel 2.2

Dengan demikian,

Oleh karena itu, dengan peningkatan area perdagangan sebesar 1.000 m 2, hal lain dianggap sama, omset tahunan rata-rata meningkat sebesar 67,8871 juta rubel.

Contoh 2.2. Manajemen perusahaan memperhatikan bahwa omset tahunan tidak hanya bergantung pada area penjualan toko (lihat contoh 2.1), tetapi juga pada jumlah pengunjung rata-rata. Informasi yang relevan disajikan dalam tabel. 2.3.

Tabel 2.3

Larutan. Nyatakan - jumlah rata-rata pengunjung ke toko th per hari, ribuan orang.

Untuk menentukan bentuk hubungan fungsional antar variabel dan membuat scatterplot (Gambar 2.2).

Berdasarkan diagram pencar, dapat disimpulkan bahwa omset tahunan berhubungan positif dengan rata-rata jumlah pengunjung per hari (yaitu, y akan meningkat dengan pertumbuhan ). Bentuk ketergantungan fungsional adalah linier.

Beras. 2.2. Scatterplot misalnya 2.2

Tabel 2.4

Secara umum, perlu ditentukan parameter model ekonometrik dua faktor

y t \u003d a 0 + a 1 x 1 t + a 2 x 2 t + ε t

Informasi yang diperlukan untuk perhitungan lebih lanjut disajikan pada Tabel. 2.4.

Mari kita perkirakan parameter model ekonometrik dua faktor linier menggunakan metode kuadrat terkecil.

Dengan demikian,

Evaluasi koefisien = 61,6583 menunjukkan bahwa, hal lain dianggap sama, dengan peningkatan area perdagangan sebesar 1 ribu m 2, omset tahunan akan meningkat rata-rata 61,6583 juta rubel.

Metode kuadrat terkecil

Metode kuadrat terkecil ( MNK, OLS, Kuadrat Terkecil Biasa) - salah satu metode dasar analisis regresi untuk memperkirakan parameter model regresi yang tidak diketahui dari data sampel. Metode ini didasarkan pada meminimalkan jumlah kuadrat residu regresi.

Perlu dicatat bahwa metode kuadrat terkecil itu sendiri dapat disebut sebagai metode untuk menyelesaikan masalah di area mana pun, jika solusinya terdiri dari atau memenuhi kriteria tertentu untuk meminimalkan jumlah kuadrat dari beberapa fungsi dari variabel yang tidak diketahui. Oleh karena itu, metode kuadrat terkecil juga dapat digunakan untuk perkiraan representasi (perkiraan) dari fungsi yang diberikan oleh fungsi lain (lebih sederhana), ketika menemukan sekumpulan besaran yang memenuhi persamaan atau batasan, yang jumlahnya melebihi jumlah besaran tersebut. , dll.

Inti dari MNC

Biarkan beberapa model (parametrik) ketergantungan probabilistik (regresi) antara variabel (dijelaskan). y dan banyak faktor (variabel penjelas) X

di mana adalah vektor dari parameter model yang tidak diketahui

- Kesalahan model acak.

Biarlah ada juga sampel pengamatan dari nilai-nilai variabel yang ditunjukkan. Membiarkan menjadi nomor observasi (). Kemudian adalah nilai-nilai variabel pada pengamatan ke -th. Kemudian, untuk nilai parameter b yang diberikan, dimungkinkan untuk menghitung nilai teoritis (model) dari variabel yang dijelaskan y:

Nilai residual tergantung dari nilai parameter b.

Inti dari LSM (biasa, klasik) adalah menemukan parameter b yang jumlah kuadrat dari residunya (eng. Jumlah Sisa Kuadrat) akan minimal:

Dalam kasus umum, masalah ini dapat diselesaikan dengan metode numerik optimasi (minimisasi). Dalam hal ini, seseorang berbicara tentang kuadrat terkecil nonlinier(NLS atau NLLS - Bahasa Inggris. Kuadrat Terkecil Non Linear). Dalam banyak kasus, solusi analitik dapat diperoleh. Untuk menyelesaikan masalah minimisasi, perlu untuk menemukan titik stasioner dari fungsi dengan membedakannya sehubungan dengan parameter yang tidak diketahui b, menyamakan turunannya dengan nol, dan menyelesaikan sistem persamaan yang dihasilkan:

Jika kesalahan acak model terdistribusi normal, memiliki varians yang sama, dan tidak berkorelasi satu sama lain, estimasi parameter kuadrat terkecil sama dengan estimasi metode kemungkinan maksimum (MLM).

LSM dalam kasus model linier

Biarkan ketergantungan regresi menjadi linier:

Membiarkan y- vektor kolom pengamatan dari variabel yang dijelaskan, dan - matriks pengamatan faktor (baris matriks - vektor nilai faktor dalam pengamatan tertentu, dengan kolom - vektor nilai faktor tertentu dalam semua pengamatan) . Representasi matriks dari model linier memiliki bentuk:

Kemudian vektor estimasi variabel yang dijelaskan dan vektor residu regresi akan sama

karenanya, jumlah kuadrat dari residu regresi akan sama dengan

Membedakan fungsi ini sehubungan dengan vektor parameter dan menyamakan turunannya dengan nol, kami memperoleh sistem persamaan (dalam bentuk matriks):

Solusi dari sistem persamaan ini memberikan rumus umum untuk perkiraan kuadrat terkecil untuk model linier:

Untuk tujuan analitis, representasi terakhir dari rumus ini ternyata bermanfaat. Jika data dalam model regresi terpusat, maka dalam representasi ini matriks pertama memiliki arti matriks kovarians sampel faktor, dan yang kedua adalah vektor kovarians faktor dengan variabel dependen. Jika, selain itu, datanya juga dinormalisasi di SKO (yaitu, pada akhirnya terstandarisasi), maka matriks pertama memiliki arti matriks korelasi sampel faktor, vektor kedua - vektor korelasi sampel faktor dengan variabel dependen.

Properti penting estimasi LLS untuk model dengan konstanta- garis regresi yang dibangun melewati pusat gravitasi data sampel, yaitu persamaan terpenuhi:

Secara khusus, dalam kasus ekstrim, ketika satu-satunya regressor adalah konstanta, kami menemukan bahwa estimasi OLS dari parameter tunggal (konstanta itu sendiri) sama dengan nilai rata-rata dari variabel yang dijelaskan. Yaitu, rata-rata aritmatika, yang dikenal karena sifat baiknya dari hukum bilangan besar, juga merupakan perkiraan kuadrat terkecil - memenuhi kriteria untuk jumlah minimum penyimpangan kuadrat darinya.

Contoh: regresi sederhana (berpasangan).

Dalam kasus regresi linier berpasangan, rumus perhitungan disederhanakan (Anda dapat melakukannya tanpa aljabar matriks):

Properti perkiraan OLS

Pertama-tama, kami mencatat bahwa untuk model linier, perkiraan kuadrat terkecil adalah perkiraan linier, sebagai berikut dari rumus di atas. Untuk estimasi OLS yang tidak bias, perlu dan cukup untuk memenuhi syarat terpenting analisis regresi: ekspektasi matematis dari kesalahan acak yang tergantung pada faktor harus sama dengan nol. Kondisi ini terpenuhi, khususnya jika

ekspektasi matematis dari kesalahan acak adalah nol, dan
faktor dan kesalahan acak adalah variabel acak independen.

Kondisi kedua - kondisi faktor eksogen - bersifat fundamental. Jika sifat ini tidak terpenuhi, maka kita dapat berasumsi bahwa hampir semua perkiraan akan sangat tidak memuaskan: perkiraan tersebut bahkan tidak akan konsisten (yaitu, bahkan jumlah data yang sangat besar tidak memungkinkan untuk memperoleh perkiraan kualitatif dalam kasus ini). Dalam kasus klasik, asumsi yang lebih kuat dibuat tentang determinisme faktor, berbeda dengan kesalahan acak, yang secara otomatis berarti kondisi eksogen terpenuhi. Dalam kasus umum, untuk konsistensi perkiraan, cukup untuk memenuhi kondisi eksogen bersama dengan konvergensi matriks ke beberapa matriks non-singular dengan peningkatan ukuran sampel hingga tak terhingga.

Agar, selain konsistensi dan ketidakberpihakan, estimasi kuadrat terkecil (biasa) juga efektif (yang terbaik di kelas estimasi tak bias linier), properti tambahan dari kesalahan acak harus dipenuhi:

Asumsi ini dapat dirumuskan untuk matriks kovarians dari vektor kesalahan acak

Model linear yang memenuhi kondisi ini disebut klasik. Estimasi OLS untuk regresi linier klasik adalah estimasi yang tidak bias, konsisten, dan paling efisien di kelas semua estimasi tidak bias linier (dalam literatur bahasa Inggris, singkatan terkadang digunakan biru (Estimator Unbaised Linear Terbaik) adalah estimasi tak bias linier terbaik; dalam literatur domestik, teorema Gauss-Markov lebih sering dikutip). Seperti yang mudah ditunjukkan, matriks kovarian dari vektor estimasi koefisien akan sama dengan:

Kuadrat terkecil yang digeneralisasikan

Metode kuadrat terkecil memungkinkan generalisasi yang luas. Alih-alih meminimalkan jumlah kuadrat dari residu, seseorang dapat meminimalkan beberapa bentuk kuadrat pasti positif dari vektor residu , di mana beberapa matriks bobot pasti positif simetris. Kuadrat terkecil biasa adalah kasus khusus dari pendekatan ini, ketika matriks bobot sebanding dengan matriks identitas. Seperti diketahui dari teori matriks simetris (atau operator), ada dekomposisi untuk matriks tersebut. Oleh karena itu, fungsional yang ditentukan dapat direpresentasikan sebagai berikut, yaitu, fungsional ini dapat direpresentasikan sebagai jumlah kuadrat dari beberapa "residu" yang diubah. Dengan demikian, kita dapat membedakan kelas metode kuadrat terkecil - metode LS (Kuadrat Terkecil).

Terbukti (teorema Aitken) bahwa untuk model regresi linier umum (di mana tidak ada batasan yang dikenakan pada matriks kovarian kesalahan acak), yang paling efektif (dalam kelas estimasi tak bias linier) adalah estimasi yang disebut. OLS umum (OMNK, GLS - Kuadrat Terkecil Umum)- Metode LS dengan matriks bobot sama dengan matriks kovarians terbalik dari kesalahan acak: .

Dapat ditunjukkan bahwa rumus estimasi GLS dari parameter model linier memiliki bentuk

Matriks kovarian dari perkiraan ini, masing-masing, akan sama dengan

Faktanya, inti dari OLS terletak pada transformasi (linier) tertentu (P) dari data asli dan penerapan kuadrat terkecil biasa ke data yang diubah. Tujuan dari transformasi ini adalah agar data yang ditransformasikan, kesalahan acak sudah memenuhi asumsi klasik.

Kuadrat terkecil tertimbang

Dalam kasus matriks bobot diagonal (dan karenanya matriks kovarian kesalahan acak), kita memiliki apa yang disebut kuadrat terkecil tertimbang (WLS - Kuadrat Terkecil Tertimbang). Dalam hal ini, jumlah kuadrat tertimbang dari residual model diminimalkan, yaitu, setiap pengamatan menerima "bobot" yang berbanding terbalik dengan varian kesalahan acak dalam pengamatan ini: . Faktanya, data diubah dengan memberi bobot pada pengamatan (dibagi dengan jumlah yang sebanding dengan standar deviasi yang diasumsikan dari kesalahan acak), dan kuadrat terkecil normal diterapkan pada data yang diberi bobot.

Beberapa kasus khusus penerapan LSM dalam praktek

Perkiraan Linear

Pertimbangkan kasus ketika, sebagai hasil dari mempelajari ketergantungan kuantitas skalar tertentu pada kuantitas skalar tertentu (Ini dapat berupa, misalnya, ketergantungan tegangan pada kekuatan arus: , di mana adalah nilai konstan, resistansi konduktor ), besaran-besaran ini diukur, sebagai akibatnya nilai dan nilai yang sesuai. Data pengukuran harus dicatat dalam sebuah tabel.

Meja. Hasil pengukuran.

Pengukuran No.
1
2
3
4
5
6

Pertanyaannya berbunyi seperti ini: nilai koefisien apa yang dapat dipilih untuk menggambarkan ketergantungan dengan paling baik? Menurut kuadrat terkecil, nilai ini harus sedemikian rupa sehingga jumlah kuadrat penyimpangan nilai dari nilai

sangat minim

Jumlah simpangan kuadrat memiliki satu ekstrem - minimum, yang memungkinkan kita menggunakan rumus ini. Mari kita cari nilai koefisien dari rumus ini. Untuk melakukan ini, kami mengubah sisi kirinya sebagai berikut:

Rumus terakhir memungkinkan kita menemukan nilai koefisien , yang diperlukan dalam soal.

Cerita

Sampai awal abad XIX. ilmuwan tidak memiliki aturan tertentu untuk menyelesaikan sistem persamaan di mana jumlah yang tidak diketahui kurang dari jumlah persamaan; Sampai saat itu, metode tertentu digunakan, bergantung pada jenis persamaan dan kecerdikan kalkulator, dan oleh karena itu kalkulator yang berbeda, mulai dari data pengamatan yang sama, menghasilkan kesimpulan yang berbeda. Gauss (1795) dikreditkan dengan penerapan pertama metode ini, dan Legendre (1805) secara mandiri menemukan dan menerbitkannya dengan nama modernnya (fr. Methode des moindres quarres ) . Laplace menghubungkan metode tersebut dengan teori probabilitas, dan matematikawan Amerika Adrain (1808) mempertimbangkan penerapan probabilistiknya. Metode ini tersebar luas dan diperbaiki dengan penelitian lebih lanjut oleh Encke, Bessel, Hansen dan lain-lain.

Alternatif penggunaan MNC

Ide metode kuadrat terkecil juga dapat digunakan dalam kasus lain yang tidak terkait langsung dengan analisis regresi. Faktanya adalah bahwa jumlah kuadrat adalah salah satu ukuran kedekatan yang paling umum untuk vektor (metrik Euclidean dalam ruang berdimensi hingga).

Salah satu penerapannya adalah "menyelesaikan" sistem persamaan linier di mana jumlah persamaan lebih besar daripada jumlah variabel

dimana matriksnya bukan bujur sangkar, melainkan persegi panjang.

Sistem persamaan seperti itu, dalam kasus umum, tidak memiliki solusi (jika peringkat sebenarnya lebih besar dari jumlah variabel). Oleh karena itu, sistem ini dapat "diselesaikan" hanya dalam artian memilih vektor sedemikian untuk meminimalkan "jarak" antara vektor dan . Untuk melakukan ini, Anda dapat menerapkan kriteria untuk meminimalkan jumlah perbedaan kuadrat dari bagian kiri dan kanan dari persamaan sistem, yaitu . Mudah untuk menunjukkan bahwa solusi dari masalah minimisasi ini mengarah ke solusi dari sistem persamaan berikut

Contoh.

Data eksperimen tentang nilai-nilai variabel X Dan pada diberikan dalam tabel.

Sebagai hasil dari penyelarasan mereka, fungsinya

Inti dari metode kuadrat terkecil (LSM).

Dengan demikian, solusi dari contoh tersebut direduksi menjadi mencari nilai ekstrem dari suatu fungsi dari dua variabel.

Penurunan rumus untuk mencari koefisien.

Sistem dua persamaan dengan dua yang tidak diketahui dikompilasi dan diselesaikan. Menemukan turunan parsial dari fungsi oleh variabel A Dan B, kita samakan turunan ini dengan nol.

Kami memecahkan sistem persamaan yang dihasilkan dengan metode apa pun (misalnya metode substitusi atau Metode Cramer) dan dapatkan rumus untuk mencari koefisien menggunakan metode kuadrat terkecil (LSM).

Dengan data A Dan B fungsi mengambil nilai terkecil. Bukti fakta ini diberikan di bawah teks di akhir halaman.

Itulah keseluruhan metode kuadrat terkecil. Rumus untuk menemukan parameter A berisi jumlah ,,, dan parameter N- jumlah data percobaan. Nilai jumlah ini direkomendasikan untuk dihitung secara terpisah. Koefisien B ditemukan setelah perhitungan A.

Saatnya mengingat contoh aslinya.

Larutan.

Dalam contoh kita n=5. Kami mengisi tabel untuk kenyamanan menghitung jumlah yang termasuk dalam rumus koefisien yang diperlukan.

Nilai pada baris keempat tabel diperoleh dengan mengalikan nilai baris ke-2 dengan nilai baris ke-3 untuk setiap angka Saya.

Nilai-nilai pada baris kelima tabel diperoleh dengan mengkuadratkan nilai-nilai baris ke-2 untuk setiap angka Saya.

Nilai-nilai kolom terakhir dari tabel adalah jumlah dari nilai-nilai di seluruh baris.

Kami menggunakan rumus metode kuadrat terkecil untuk menemukan koefisien A Dan B. Kami menggantinya dengan nilai yang sesuai dari kolom terakhir tabel:

Karena itu, y=0,165x+2,184 adalah perkiraan garis lurus yang diinginkan.

Tetap mencari tahu garis mana y=0,165x+2,184 atau lebih baik mendekati data asli, yaitu membuat perkiraan menggunakan metode kuadrat terkecil.

Estimasi kesalahan metode kuadrat terkecil.

Sejak , maka garis y=0,165x+2,184 mendekati data asli dengan lebih baik.

Ilustrasi grafis dari metode kuadrat terkecil (LSM).

Semuanya tampak hebat di tangga lagu. Garis merah adalah garis yang ditemukan y=0,165x+2,184, garis biru adalah , titik merah muda adalah data asli.

Dalam praktiknya, ketika memodelkan berbagai proses - khususnya, ekonomi, fisik, teknis, sosial - satu atau metode lain untuk menghitung nilai perkiraan fungsi dari nilai yang diketahui pada beberapa titik tetap banyak digunakan.

Masalah perkiraan fungsi semacam ini sering muncul:

ketika menyusun rumus perkiraan untuk menghitung nilai kuantitas karakteristik dari proses yang diteliti menurut data tabel yang diperoleh sebagai hasil percobaan;

dalam integrasi numerik, diferensiasi, menyelesaikan persamaan diferensial, dll.;

jika perlu menghitung nilai fungsi pada titik tengah dari interval yang dipertimbangkan;

saat menentukan nilai kuantitas karakteristik proses di luar interval yang dipertimbangkan, khususnya, saat meramalkan.

Jika, untuk memodelkan proses tertentu yang ditentukan oleh tabel, sebuah fungsi dibangun yang menggambarkan proses ini secara kasar berdasarkan metode kuadrat terkecil, itu akan disebut fungsi pendekatan (regresi), dan tugas membangun fungsi perkiraan itu sendiri akan menjadi masalah aproksimasi.

Artikel ini membahas kemungkinan paket MS Excel untuk memecahkan masalah seperti itu, selain itu, metode dan teknik untuk membangun (membuat) regresi untuk fungsi yang diberikan secara tabular (yang merupakan dasar analisis regresi) diberikan.

Ada dua opsi untuk membangun regresi di Excel.

Menambahkan regresi terpilih (garis tren) ke bagan yang dibuat berdasarkan tabel data untuk karakteristik proses yang dipelajari (hanya tersedia jika bagan dibuat);

Menggunakan fungsi statistik bawaan lembar kerja Excel, yang memungkinkan Anda mendapatkan regresi (garis tren) langsung dari tabel data sumber.

Menambahkan Garis Tren ke Bagan

Untuk tabel data yang menjelaskan proses tertentu dan diwakili oleh diagram, Excel memiliki alat analisis regresi efektif yang memungkinkan Anda untuk:

bangun berdasarkan metode kuadrat terkecil dan tambahkan ke diagram lima jenis regresi yang memodelkan proses yang diteliti dengan berbagai tingkat akurasi;

tambahkan persamaan regresi yang dibangun ke diagram;

tentukan tingkat kepatuhan regresi yang dipilih dengan data yang ditampilkan pada grafik.

Berdasarkan data bagan, Excel memungkinkan Anda mendapatkan jenis regresi linier, polinomial, logaritmik, eksponensial, eksponensial, yang diberikan oleh persamaan:

y = y(x)

di mana x adalah variabel independen, yang sering kali mengambil nilai dari urutan bilangan asli (1; 2; 3; ...) dan menghasilkan, misalnya, hitungan mundur waktu proses yang diteliti (karakteristik) .

1 . Regresi linier bagus dalam memodelkan fitur yang meningkat atau menurun pada tingkat yang konstan. Ini adalah model paling sederhana dari proses yang diteliti. Itu dibangun sesuai dengan persamaan:

y=mx+b

di mana m adalah garis singgung kemiringan regresi linier terhadap sumbu x; b - koordinat titik perpotongan regresi linier dengan sumbu y.

2 . Garis tren polinomial berguna untuk mendeskripsikan karakteristik yang memiliki beberapa perbedaan ekstrem (tertinggi dan terendah). Pilihan derajat polinomial ditentukan oleh jumlah ekstrem dari karakteristik yang diteliti. Dengan demikian, polinomial derajat kedua dapat menggambarkan dengan baik suatu proses yang hanya memiliki satu maksimum atau minimum; polinomial derajat ketiga - tidak lebih dari dua ekstrem; polinomial derajat keempat - tidak lebih dari tiga ekstrem, dll.

Dalam hal ini, garis tren dibangun sesuai dengan persamaan:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

dimana koefisien c0, c1, c2,... c6 adalah konstanta yang nilainya ditentukan selama konstruksi.

3 . Garis tren logaritmik berhasil digunakan dalam karakteristik pemodelan, yang nilainya berubah dengan cepat pada awalnya, dan kemudian stabil secara bertahap.

y = c ln(x) + b

4 . Garis tren kekuatan memberikan hasil yang baik jika nilai ketergantungan yang dipelajari dicirikan oleh perubahan laju pertumbuhan yang konstan. Contoh dari ketergantungan semacam itu dapat berupa grafik pergerakan mobil yang dipercepat secara seragam. Jika ada nilai nol atau negatif dalam data, Anda tidak dapat menggunakan power trendline.

Itu dibangun sesuai dengan persamaan:

y = cxb

di mana koefisien b, c adalah konstanta.

5 . Garis tren eksponensial harus digunakan jika laju perubahan data terus meningkat. Untuk data yang mengandung nilai nol atau negatif, pendekatan semacam ini juga tidak berlaku.

Itu dibangun sesuai dengan persamaan:

y=cebx

di mana koefisien b, c adalah konstanta.

Saat memilih garis tren, Excel secara otomatis menghitung nilai R2, yang mencirikan akurasi perkiraan: semakin dekat nilai R2 dengan satu, semakin andal garis tren mendekati proses yang diteliti. Jika perlu, nilai R2 selalu dapat ditampilkan pada diagram.

Ditentukan oleh rumus:

Untuk menambahkan garis tren ke seri data:

aktifkan bagan yang dibuat berdasarkan seri data, yaitu, klik di dalam area bagan. Item Bagan akan muncul di menu utama;

setelah mengklik item ini, sebuah menu akan muncul di layar, di mana Anda harus memilih perintah Tambahkan garis tren.

Tindakan yang sama mudah diterapkan jika Anda mengarahkan kursor ke grafik yang sesuai dengan salah satu rangkaian data dan klik kanan; di menu konteks yang muncul, pilih perintah Tambahkan garis tren. Kotak dialog Trendline akan muncul di layar dengan tab Type dibuka (Gbr. 1).

Setelah itu Anda membutuhkan:

Pada tab Jenis, pilih jenis garis tren yang diperlukan (Linear dipilih secara default). Untuk jenis Polinomial, pada kolom Derajat, tentukan derajat polinomial yang dipilih.

1 . Bidang Seri Dibangun mencantumkan semua seri data dalam bagan yang dimaksud. Untuk menambahkan garis tren ke seri data tertentu, pilih namanya di bidang Seri bawaan.

Jika perlu, dengan masuk ke tab Parameter (Gbr. 2), Anda dapat mengatur parameter berikut untuk garis tren:

ubah nama garis tren di Nama bidang kurva yang mendekati (dihaluskan).

atur jumlah periode (maju atau mundur) untuk prakiraan di bidang Prakiraan;

tampilkan persamaan garis tren di area bagan, yang mana Anda harus mengaktifkan kotak centang tampilkan persamaan pada bagan;

tampilkan nilai reliabilitas aproksimasi R2 di area diagram, yang mana Anda harus mengaktifkan kotak centang beri nilai reliabilitas aproksimasi (R^2) pada diagram;

atur titik perpotongan garis tren dengan sumbu Y, yang mana Anda harus mengaktifkan kotak centang untuk perpotongan kurva dengan sumbu Y pada suatu titik;

klik tombol OK untuk menutup kotak dialog.

Ada tiga cara untuk mulai mengedit garis tren yang sudah dibuat:

gunakan perintah Garis tren yang dipilih dari menu Format, setelah memilih garis tren;

pilih perintah Format Trendline dari menu konteks, yang disebut dengan mengklik kanan pada trendline;

dengan mengklik dua kali pada garis tren.

Kotak dialog Format Trendline akan muncul di layar (Gbr. 3), berisi tiga tab: View, Type, Parameters, dan isi dari dua tab terakhir benar-benar sesuai dengan tab serupa dari kotak dialog Trendline (Gbr. 1-2 ). Pada tab Tampilan, Anda dapat mengatur jenis garis, warna, dan ketebalannya.

Untuk menghapus garis tren yang sudah dibuat, pilih garis tren yang akan dihapus dan tekan tombol Hapus.

Keuntungan dari alat analisis regresi yang dipertimbangkan adalah:

relatif mudahnya memplot garis tren pada grafik tanpa membuat tabel data untuknya;

daftar jenis garis tren yang diusulkan cukup luas, dan daftar ini mencakup jenis regresi yang paling umum digunakan;

kemungkinan memprediksi perilaku proses yang diteliti untuk jumlah langkah maju dan mundur yang sewenang-wenang (dalam akal sehat);

kemungkinan memperoleh persamaan garis tren dalam bentuk analitik;

kemungkinan, jika perlu, untuk mendapatkan penilaian keandalan perkiraan.

Kerugiannya meliputi poin-poin berikut:

pembuatan garis tren dilakukan hanya jika ada grafik yang dibangun di atas serangkaian data;

proses menghasilkan seri data untuk karakteristik yang diteliti berdasarkan persamaan garis tren yang diperoleh agak berantakan: persamaan regresi yang diinginkan diperbarui dengan setiap perubahan nilai seri data asli, tetapi hanya di dalam area bagan , sementara deret data yang dibentuk berdasarkan tren persamaan garis lama, tetap tidak berubah;

Dalam laporan PivotChart, saat Anda mengubah tampilan bagan atau laporan PivotTable terkait, garis tren yang sudah ada tidak dipertahankan, sehingga Anda harus memastikan bahwa tata letak laporan memenuhi persyaratan Anda sebelum menggambar garis tren atau memformat laporan PivotChart.

Garis tren dapat ditambahkan ke seri data yang disajikan pada bagan seperti grafik, histogram, bagan area non-normalisasi datar, bagan batang, sebar, gelembung, dan bagan saham.

Anda tidak dapat menambahkan garis tren ke rangkaian data pada bagan 3-D, Standar, Radar, Pai, dan Donat.

Menggunakan Fungsi Excel Bawaan

Excel juga menyediakan alat analisis regresi untuk memplot garis tren di luar area grafik. Sejumlah fungsi lembar kerja statistik dapat digunakan untuk tujuan ini, tetapi semuanya memungkinkan Anda untuk membuat regresi linier atau eksponensial saja.

Excel memiliki beberapa fungsi untuk membangun regresi linier, khususnya:

KECENDERUNGAN;

LERENG dan CUT.

Serta beberapa fungsi untuk membangun garis tren eksponensial, khususnya:

LGRFPkira-kira.

Perlu dicatat bahwa teknik menyusun regresi menggunakan fungsi TREND dan GROWTH secara praktis sama. Hal yang sama dapat dikatakan tentang pasangan fungsi LINEST dan LGRFPRIBL. Untuk keempat fungsi ini, saat membuat tabel nilai, fitur Excel seperti rumus array digunakan, yang agak mengacaukan proses pembuatan regresi. Kami juga mencatat bahwa konstruksi regresi linier, menurut kami, paling mudah diimplementasikan menggunakan fungsi SLOPE dan INTERCEPT, di mana yang pertama menentukan kemiringan regresi linier, dan yang kedua menentukan segmen yang dipotong oleh regresi pada sumbu y.

Keuntungan alat fungsi bawaan untuk analisis regresi adalah:

proses yang cukup sederhana dari jenis pembentukan seri data yang sama dari karakteristik yang diteliti untuk semua fungsi statistik bawaan yang menetapkan garis tren;

teknik standar untuk membangun garis tren berdasarkan seri data yang dihasilkan;

kemampuan untuk memprediksi perilaku proses yang diteliti untuk jumlah langkah maju atau mundur yang diperlukan.

Dan kerugiannya termasuk fakta bahwa Excel tidak memiliki fungsi bawaan untuk membuat jenis garis tren lainnya (kecuali linier dan eksponensial). Keadaan ini seringkali tidak memungkinkan untuk memilih model yang cukup akurat dari proses yang diteliti, serta mendapatkan perkiraan yang mendekati kenyataan. Selain itu, saat menggunakan fungsi TREND dan GROW, persamaan garis tren tidak diketahui.

Perlu dicatat bahwa penulis tidak menetapkan tujuan artikel untuk menyajikan kursus analisis regresi dengan berbagai tingkat kelengkapan. Tugas utamanya adalah menunjukkan kemampuan paket Excel dalam memecahkan masalah aproksimasi menggunakan contoh spesifik; menunjukkan alat apa yang efektif yang dimiliki Excel untuk membangun regresi dan peramalan; mengilustrasikan betapa relatif mudahnya masalah tersebut dapat diselesaikan bahkan oleh pengguna yang tidak memiliki pengetahuan mendalam tentang analisis regresi.

Contoh pemecahan masalah tertentu

Pertimbangkan solusi untuk masalah tertentu menggunakan alat paket Excel yang terdaftar.

Tugas 1

Dengan tabel data keuntungan usaha angkutan motor tahun 1995-2002. Anda perlu melakukan hal berikut.

Buat bagan.

Tambahkan garis tren linier dan polinomial (kuadrat dan kubik) ke bagan.

Dengan menggunakan persamaan garis tren, dapatkan data tabel keuntungan perusahaan untuk setiap garis tren tahun 1995-2004.

Buat ramalan keuntungan untuk perusahaan untuk tahun 2003 dan 2004.

Solusi dari masalah

Di rentang sel A4:C11 lembar kerja Excel, kami memasukkan lembar kerja yang ditunjukkan pada Gambar. 4.

Setelah memilih rentang sel B4:C11, kami membuat bagan.

Kami mengaktifkan bagan yang dibuat dan, menggunakan metode yang dijelaskan di atas, setelah memilih jenis garis tren di kotak dialog Garis Tren (lihat Gbr. 1), kami secara bergantian menambahkan garis tren linier, kuadrat, dan kubik ke bagan. Di kotak dialog yang sama, buka tab Parameter (lihat Gbr. 2), di Nama bidang kurva perkiraan (dihaluskan), masukkan nama tren yang akan ditambahkan, dan di bidang Prakiraan maju untuk: periode, atur nilai 2, karena direncanakan untuk membuat perkiraan keuntungan untuk dua tahun ke depan. Untuk menampilkan persamaan regresi dan nilai reliabilitas aproksimasi R2 di area diagram, aktifkan kotak centang Tampilkan persamaan di layar dan tempatkan nilai reliabilitas aproksimasi (R^2) pada diagram. Untuk persepsi visual yang lebih baik, kami mengubah jenis, warna, dan ketebalan garis tren yang diplot, yang kami gunakan tab Lihat dari kotak dialog Format Garis Tren (lihat Gbr. 3). Bagan yang dihasilkan dengan garis tren tambahan ditunjukkan pada gambar. 5.

Untuk mendapatkan data tabel keuntungan perusahaan untuk setiap garis tren tahun 1995-2004. Mari gunakan persamaan garis tren yang disajikan pada gambar. 5. Untuk melakukannya, dalam sel rentang D3:F3, masukkan informasi tekstual tentang jenis garis tren yang dipilih: Tren linier, Tren kuadrat, Tren kubik. Selanjutnya, masukkan rumus regresi linier di sel D4 dan, dengan menggunakan penanda isian, salin rumus ini dengan referensi relatif ke rentang sel D5:D13. Perlu diperhatikan bahwa setiap sel dengan rumus regresi linier dari rentang sel D4:D13 memiliki sel yang sesuai dari rentang A4:A13 sebagai argumen. Demikian pula, untuk regresi kuadrat, rentang sel E4:E13 diisi, dan untuk regresi kubik, rentang sel F4:F13 diisi. Jadi, ramalan dibuat untuk keuntungan perusahaan untuk tahun 2003 dan 2004. dengan tiga tren. Tabel nilai yang dihasilkan ditunjukkan pada gambar. 6.

Tugas 2

Buat bagan.

Tambahkan garis tren logaritmik, eksponensial, dan eksponensial ke grafik.

Turunkan persamaan dari garis tren yang diperoleh, serta nilai reliabilitas perkiraan R2 untuk masing-masingnya.

Dengan menggunakan persamaan garis tren, dapatkan data tabel keuntungan perusahaan untuk setiap garis tren tahun 1995-2002.

Buat ramalan laba untuk bisnis tahun 2003 dan 2004 dengan menggunakan garis tren ini.

Solusi dari masalah

Mengikuti metodologi yang diberikan dalam memecahkan masalah 1, kami memperoleh diagram dengan menambahkan garis tren logaritmik, eksponensial, dan eksponensial (Gbr. 7). Selanjutnya, dengan menggunakan persamaan garis tren yang diperoleh, kami mengisi tabel nilai keuntungan perusahaan, termasuk nilai prediksi untuk tahun 2003 dan 2004. (Gbr. 8).

Pada ara. 5 dan gbr. dapat dilihat bahwa model dengan tren logaritmik sesuai dengan nilai reliabilitas aproksimasi terendah

R2 = 0,8659

Nilai R2 tertinggi sesuai dengan model dengan tren polinomial: kuadrat (R2 = 0,9263) dan kubik (R2 = 0,933).

Tugas 3

Dengan tabel data laba usaha angkutan motor tahun 1995-2002 yang diberikan pada tugas 1, Anda harus melakukan langkah-langkah berikut.

Dapatkan rangkaian data untuk garis tren linier dan eksponensial menggunakan fungsi TREND dan GROW.

Dengan menggunakan fungsi TREND dan GROWTH, buatlah ramalan keuntungan untuk perusahaan pada tahun 2003 dan 2004.

Untuk data awal dan seri data yang diterima, buatlah diagram.

Solusi dari masalah

Mari gunakan lembar kerja tugas 1 (lihat Gambar 4). Mari kita mulai dengan fungsi TREND:

pilih rentang sel D4:D11, yang harus diisi dengan nilai fungsi TREND yang sesuai dengan data laba perusahaan yang diketahui;

panggil perintah Fungsi dari menu Sisipkan. Di kotak dialog Function Wizard yang muncul, pilih fungsi TREND dari kategori Statistical, lalu klik tombol OK. Operasi yang sama dapat dilakukan dengan menekan tombol (Sisipkan fungsi) dari toolbar standar.

Di kotak dialog Argumen Fungsi yang muncul, masukkan rentang sel C4:C11 di bidang Nilai_dikenal; di bidang Known_values_x - rentang sel B4:B11;

untuk menjadikan rumus yang dimasukkan sebagai rumus array, gunakan kombinasi tombol + + .

Rumus yang kita masukkan di bilah rumus akan terlihat seperti: =(TREND(C4:C11;B4:B11)).

Hasilnya, rentang sel D4:D11 diisi dengan nilai fungsi TREND yang sesuai (Gbr. 9).

Untuk membuat ramalan keuntungan perusahaan untuk tahun 2003 dan 2004. diperlukan:

pilih rentang sel D12:D13, di mana nilai yang diprediksi oleh fungsi TREND akan dimasukkan.

panggil fungsi TREND dan di kotak dialog Argumen Fungsi yang muncul, masukkan di bidang Nilai_dikenal - rentang sel C4:C11; di bidang Known_values_x - rentang sel B4:B11; dan di bidang New_values_x - rentang sel B12:B13.

ubah rumus ini menjadi rumus array menggunakan pintasan keyboard Ctrl + Shift + Enter.

Rumus yang dimasukkan akan terlihat seperti: =(TREND(C4:C11;B4:B11;B12:B13)), dan rentang sel D12:D13 akan diisi dengan nilai prediksi dari fungsi TREND (lihat Gbr. 9).

Demikian pula, seri data diisi menggunakan fungsi PERTUMBUHAN, yang digunakan dalam analisis dependensi nonlinier dan bekerja persis sama dengan TREND pasangan liniernya.

Gambar 10 menunjukkan tabel dalam mode tampilan rumus.

Untuk data awal dan seri data yang diperoleh, diagram ditunjukkan pada gambar. sebelas.

Tugas 4

Dengan tabel data penerimaan aplikasi untuk layanan oleh layanan pengiriman perusahaan angkutan motor untuk periode dari tanggal 1 hingga tanggal 11 bulan berjalan, tindakan berikut harus dilakukan.

Dapatkan seri data untuk regresi linier: menggunakan fungsi SLOPE dan INTERCEPT; menggunakan fungsi LINEST.

Ambil rangkaian data untuk regresi eksponensial menggunakan fungsi LYFFPRIB.

Dengan menggunakan fungsi-fungsi di atas, buat perkiraan tentang penerimaan aplikasi ke layanan pengiriman untuk periode dari tanggal 12 hingga 14 bulan berjalan.

Untuk seri data asli dan yang diterima, buat diagram.

Solusi dari masalah

Perhatikan bahwa, tidak seperti fungsi TREND dan GROW, tidak ada fungsi yang tercantum di atas (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) yang merupakan regresi. Fungsi-fungsi ini hanya memainkan peran tambahan, menentukan parameter regresi yang diperlukan.

Untuk regresi linier dan eksponensial yang dibangun menggunakan fungsi SLOPE, INTERCEPT, LINEST, LGRFPRIB, kemunculan persamaannya selalu diketahui, berbeda dengan regresi linier dan eksponensial yang sesuai dengan fungsi TREND dan PERTUMBUHAN.

1 . Mari kita buat regresi linier yang memiliki persamaan:

y=mx+b

menggunakan fungsi SLOPE dan INTERCEPT, dengan kemiringan regresi m ditentukan oleh fungsi SLOPE, dan suku konstan b - oleh fungsi INTERCEPT.

Untuk melakukan ini, kami melakukan tindakan berikut:

masukkan tabel sumber di rentang sel A4:B14;

nilai parameter m akan ditentukan di sel C19. Pilih dari kategori Statistik fungsi Kemiringan; masukkan rentang sel B4:B14 di kolom known_values_y dan rentang sel A4:A14 di kolom known_values_x. Rumus akan dimasukkan ke dalam sel C19: =SLOPE(B4:B14;A4:A14);

menggunakan metode serupa, nilai parameter b di sel D19 ditentukan. Dan isinya akan terlihat seperti ini: = INTERCEPT(B4:B14;A4:A14). Dengan demikian, nilai parameter m dan b, yang diperlukan untuk membuat regresi linier, masing-masing akan disimpan dalam sel C19, D19;

kemudian kita masukkan rumus regresi linier pada cell C4 berupa : = $C * A4 + $D. Dalam rumus ini, sel C19 dan D19 ditulis dengan referensi absolut (alamat sel tidak boleh berubah dengan kemungkinan penyalinan). Tanda referensi absolut $ dapat diketik dari keyboard atau menggunakan tombol F4, setelah menempatkan kursor pada alamat sel. Menggunakan gagang isian, salin rumus ini ke rentang sel C4:C17. Kami mendapatkan seri data yang diinginkan (Gbr. 12). Karena jumlah permintaan adalah bilangan bulat, Anda harus mengatur format angka pada tab Angka di jendela Format Sel dengan jumlah tempat desimal menjadi 0.

2 . Sekarang mari kita buat regresi linier yang diberikan oleh persamaan:

y=mx+b

menggunakan fungsi LINEST.

Untuk ini:

masukkan fungsi LINEST sebagai rumus array ke dalam rentang sel C20:D20: =(LINEST(B4:B14;A4:A14)). Hasilnya, kita mendapatkan nilai parameter m di sel C20, dan nilai parameter b di sel D20;

masukkan rumus di sel D4: =$C*A4+$D;

salin rumus ini menggunakan penanda isian ke rentang sel D4:D17 dan dapatkan seri data yang diinginkan.

3 . Kami membangun regresi eksponensial yang memiliki persamaan:

dengan bantuan fungsi LGRFPRIBL, ini dilakukan dengan cara yang sama:

dalam rentang sel C21:D21, masukkan fungsi LGRFPRIBL sebagai rumus larik: =( LGRFPRIBL (B4:B14;A4:A14)). Dalam hal ini, nilai parameter m akan ditentukan di sel C21, dan nilai parameter b akan ditentukan di sel D21;

rumus dimasukkan ke sel E4: =$D*$C^A4;

menggunakan penanda isian, rumus ini disalin ke rentang sel E4:E17, tempat rangkaian data untuk regresi eksponensial akan ditempatkan (lihat Gambar 12).

Pada ara. 13 memperlihatkan tabel tempat kita dapat melihat fungsi yang kita gunakan dengan rentang sel yang diperlukan, serta rumus.

Nilai R 2 ditelepon koefisien determinasi.

Tugas membangun ketergantungan regresi adalah menemukan vektor koefisien m dari model (1) di mana koefisien R mengambil nilai maksimum.

Untuk menilai signifikansi R digunakan Fisher's F-test yang dihitung dengan rumus

Di mana N- ukuran sampel (jumlah percobaan);

k adalah jumlah koefisien model.

Jika F melebihi beberapa nilai kritis untuk data N Dan k dan tingkat kepercayaan yang diterima, maka nilai R dianggap signifikan. Tabel nilai kritis F diberikan dalam buku referensi statistik matematika.

Dengan demikian, signifikansi R tidak hanya ditentukan oleh nilainya, tetapi juga oleh rasio antara jumlah percobaan dan jumlah koefisien (parameter) model. Memang, rasio korelasi untuk n=2 untuk model linier sederhana adalah 1 (melalui 2 titik pada bidang, Anda selalu dapat menggambar satu garis lurus). Namun, jika data eksperimen adalah variabel acak, nilai R seperti itu harus dipercaya dengan sangat hati-hati. Biasanya, untuk mendapatkan R yang signifikan dan regresi yang andal, ini bertujuan untuk memastikan bahwa jumlah percobaan secara signifikan melebihi jumlah koefisien model (n>k).

Untuk membangun model regresi linier, Anda harus:

1) siapkan daftar n baris dan m kolom berisi data eksperimen (kolom berisi nilai keluaran Y harus pertama atau terakhir dalam daftar); misalnya, ambil data dari tugas sebelumnya, tambahkan kolom yang disebut "nomor periode", penomoran nomor periode dari 1 hingga 12. (ini akan menjadi nilai X)

2) masuk ke menu Data/Analisis Data/Regresi