Seleksi kuadrat terkecil. Data dan perkiraan y = kx

Metode Kuadrat Terkecil Biasa (OLS).- metode matematika yang digunakan untuk menyelesaikan berbagai masalah, berdasarkan pada minimalisasi jumlah simpangan kuadrat fungsi tertentu dari variabel yang diinginkan. Ini dapat digunakan untuk "menyelesaikan" sistem persamaan yang ditentukan secara berlebihan (ketika jumlah persamaan melebihi jumlah persamaan yang tidak diketahui), untuk menemukan solusi dalam kasus sistem persamaan nonlinier biasa (tidak ditentukan secara berlebihan), untuk memperkirakan nilai titik dari beberapa fungsi. OLS adalah salah satu metode dasar analisis regresi untuk memperkirakan parameter model regresi yang tidak diketahui dari data sampel.

YouTube ensiklopedis

1 / 5

✪ Metode kuadrat terkecil. Subjek

✪ Metode kuadrat terkecil, pelajaran 1/2. Fungsi linier

✪ Ekonometrika. Kuliah 5. Metode kuadrat terkecil

✪ Mitin I.V. - Pemrosesan hasil fisik. eksperimen - Metode kuadrat terkecil (Kuliah 4)

✪ Ekonometri: Inti dari metode kuadrat terkecil #2

Subtitle

Cerita

Sampai awal abad ke-19. ilmuwan tidak memiliki aturan tertentu untuk menyelesaikan sistem persamaan yang jumlah persamaannya lebih sedikit daripada jumlah persamaannya; Sampai saat itu, teknik pribadi digunakan yang bergantung pada jenis persamaan dan kecerdasan kalkulator, dan oleh karena itu kalkulator yang berbeda, berdasarkan data pengamatan yang sama, menghasilkan kesimpulan yang berbeda. Gauss (1795) adalah orang pertama yang menggunakan metode ini, dan Legendre (1805) secara independen menemukan dan menerbitkannya dengan nama modernnya (Perancis. Méthode des moindres quarrés) . Laplace menghubungkan metode ini dengan teori probabilitas, dan ahli matematika Amerika Adrain (1808) mempertimbangkan penerapan teori probabilitasnya. Metode ini tersebar luas dan ditingkatkan melalui penelitian lebih lanjut oleh Encke, Bessel, Hansen dan lain-lain.

Inti dari metode kuadrat terkecil

Membiarkan x (\gaya tampilan x)- perlengkapan n (\gaya tampilan n) variabel yang tidak diketahui (parameter), f i (x) (\displaystyle f_(i)(x)), , m > n (\gaya tampilan m>n)- satu set fungsi dari kumpulan variabel ini. Tugasnya adalah memilih nilai-nilai tersebut x (\gaya tampilan x), sehingga nilai fungsi tersebut sedekat mungkin dengan nilai tertentu kamu aku (\gaya tampilan y_(i)). Pada dasarnya kita berbicara tentang “solusi” dari sistem persamaan yang ditentukan secara berlebihan f i (x) = y i (\displaystyle f_(i)(x)=y_(i)), i = 1 , … , m (\displaystyle i=1,\ltitik ,m) dalam artian yang ditunjukkan kedekatan maksimum bagian kiri dan kanan sistem. Inti dari metode kuadrat terkecil adalah memilih sebagai “ukuran kedekatan” jumlah simpangan kuadrat sisi kiri dan kanan. | f saya (x) − y saya |

. Dengan demikian, inti dari MNC dapat diungkapkan sebagai berikut: x (\gaya tampilan x)∑ i e i 2 = ∑ i (y i − f i (x)) 2 → min x (\displaystyle \sum _(i)e_(i)^(2)=\sum _(i)(y_(i)-f_( i)(x))^(2)\panah kanan \min _(x)) Jika sistem persamaan mempunyai solusi, maka jumlah minimum kuadrat akan sama dengan nol dan solusi eksak sistem persamaan dapat ditemukan secara analitis atau, misalnya, menggunakan berbagai metode optimasi numerik. Jika sistem terlalu ditentukan, yaitu, jumlah persamaan independen lebih besar dari jumlah variabel yang diinginkan, maka sistem tidak memiliki solusi eksak dan metode kuadrat terkecil memungkinkan kita menemukan beberapa vektor yang “optimal”. dalam arti kedekatan maksimum vektor y (\gaya tampilan y) Dan f (x) (\gaya tampilan f(x)) atau kedekatan maksimum vektor deviasi

e (\gaya tampilan e)

ke nol (kedekatan dipahami dalam arti jarak Euclidean).

Secara khusus, metode kuadrat terkecil dapat digunakan untuk "menyelesaikan" sistem persamaan linier A x = b (\gaya tampilan Ax=b) Di mana A (\gaya tampilan A) matriks ukuran persegi panjang

m × n , m > n (\displaystyle m\kali n,m>n) x (\gaya tampilan x)(yaitu jumlah baris matriks A lebih besar dari jumlah variabel yang dicari). Secara umum, sistem persamaan seperti itu tidak memiliki solusi. Oleh karena itu, sistem ini hanya dapat “diselesaikan” dalam arti memilih vektor tersebut dalam arti kedekatan maksimum vektor untuk meminimalkan "jarak" antar vektor A x (\displaystyle Kapak) b (\gaya tampilan b). Untuk melakukan ini, Anda dapat menerapkan kriteria meminimalkan jumlah kuadrat selisih antara ruas kiri dan kanan persamaan sistem, yaitu

. Mudah untuk menunjukkan bahwa menyelesaikan masalah minimisasi ini akan menghasilkan penyelesaian sistem persamaan berikut

A T A x = A T b ⇒ x = (A T A) − 1 A T b (\displaystyle A^(T)Ax=A^(T)b\Panah Kanan x=(A^(T)A)^(-1)A^ (T)b) n (\gaya tampilan n) OLS dalam analisis regresi (perkiraan data) Jika sistem persamaan mempunyai solusi, maka jumlah minimum kuadrat akan sama dengan nol dan solusi eksak sistem persamaan dapat ditemukan secara analitis atau, misalnya, menggunakan berbagai metode optimasi numerik. Jika sistem terlalu ditentukan, yaitu, jumlah persamaan independen lebih besar dari jumlah variabel yang diinginkan, maka sistem tidak memiliki solusi eksak dan metode kuadrat terkecil memungkinkan kita menemukan beberapa vektor yang “optimal”. Biarlah ada x (\gaya tampilan x) nilai beberapa variabel Jika sistem persamaan mempunyai solusi, maka jumlah minimum kuadrat akan sama dengan nol dan solusi eksak sistem persamaan dapat ditemukan secara analitis atau, misalnya, menggunakan berbagai metode optimasi numerik. Jika sistem terlalu ditentukan, yaitu, jumlah persamaan independen lebih besar dari jumlah variabel yang diinginkan, maka sistem tidak memiliki solusi eksak dan metode kuadrat terkecil memungkinkan kita menemukan beberapa vektor yang “optimal”. dalam arti kedekatan maksimum vektor x (\gaya tampilan x) didekati dengan beberapa fungsi yang diketahui dalam beberapa parameter yang tidak diketahui untuk meminimalkan "jarak" antar vektor, yaitu, menemukan nilai parameter terbaik untuk meminimalkan "jarak" antar vektor, memperkirakan nilainya secara maksimal f (x , b) (\gaya tampilan f(x,b)) ke nilai sebenarnya Jika sistem persamaan mempunyai solusi, maka jumlah minimum kuadrat akan sama dengan nol dan solusi eksak sistem persamaan dapat ditemukan secara analitis atau, misalnya, menggunakan berbagai metode optimasi numerik. Jika sistem terlalu ditentukan, yaitu, jumlah persamaan independen lebih besar dari jumlah variabel yang diinginkan, maka sistem tidak memiliki solusi eksak dan metode kuadrat terkecil memungkinkan kita menemukan beberapa vektor yang “optimal”.. Faktanya, hal ini bermuara pada kasus “memecahkan” sistem persamaan yang telah ditentukan secara berlebihan untuk meminimalkan "jarak" antar vektor:

F (x t , b) = y t , t = 1 , … , n (\displaystyle f(x_(t),b)=y_(t),t=1,\ldots ,n).

Dalam analisis regresi dan khususnya ekonometrik, digunakan model ketergantungan probabilistik antar variabel

Y t = f (x t , b) + ε t (\displaystyle y_(t)=f(x_(t),b)+\varepsilon _(t)),

Secara khusus, metode kuadrat terkecil dapat digunakan untuk "menyelesaikan" sistem persamaan linier ε t (\displaystyle \varepsilon _(t))- yang disebut kesalahan acak model.

Dengan demikian, penyimpangan dari nilai yang diamati Jika sistem persamaan mempunyai solusi, maka jumlah minimum kuadrat akan sama dengan nol dan solusi eksak sistem persamaan dapat ditemukan secara analitis atau, misalnya, menggunakan berbagai metode optimasi numerik. Jika sistem terlalu ditentukan, yaitu, jumlah persamaan independen lebih besar dari jumlah variabel yang diinginkan, maka sistem tidak memiliki solusi eksak dan metode kuadrat terkecil memungkinkan kita menemukan beberapa vektor yang “optimal”. dari model f (x , b) (\gaya tampilan f(x,b)) sudah diasumsikan dalam model itu sendiri. Inti dari metode kuadrat terkecil (biasa, klasik) adalah menemukan parameter tersebut untuk meminimalkan "jarak" antar vektor, yang merupakan jumlah deviasi kuadrat (kesalahan; untuk model regresi sering disebut residu regresi) e t (\displaystyle e_(t)) akan minimal:

Secara khusus, metode kuadrat terkecil dapat digunakan untuk "menyelesaikan" sistem persamaan linier R S S (\displaystyle RSS)- Bahasa inggris Jumlah Sisa Kuadrat didefinisikan sebagai:

Secara umum, masalah ini dapat diselesaikan dengan metode optimasi numerik (minimisasi). Dalam hal ini yang mereka bicarakan kuadrat terkecil nonlinier(NLS atau NLLS - Kuadrat Terkecil Non-Linear Bahasa Inggris). Dalam banyak kasus, solusi analitis dapat diperoleh. Untuk menyelesaikan masalah minimalisasi, perlu dicari titik stasioner dari fungsi tersebut R S S (b) (\displaystyle RSS(b)), membedakannya menurut parameter yang tidak diketahui untuk meminimalkan "jarak" antar vektor, menyamakan turunannya dengan nol dan menyelesaikan sistem persamaan yang dihasilkan:

OLS dalam kasus regresi linier

Biarkan ketergantungan regresi menjadi linier:

Membiarkan kamu adalah vektor kolom observasi variabel yang dijelaskan, dan X (\gaya tampilan X)- Ini (n × k) (\displaystyle ((n\kali k)))-matriks observasi faktor (baris matriks adalah vektor nilai faktor dalam observasi tertentu, kolom adalah vektor nilai faktor tertentu dalam semua observasi). Representasi matriks model linier berbentuk:

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

Dengan demikian, jumlah kuadrat dari sisa regresi akan sama dengan

Diferensiasi fungsi ini terhadap vektor parameter untuk meminimalkan "jarak" antar vektor dan menyamakan turunannya dengan nol, kita memperoleh sistem persamaan (dalam bentuk matriks):

Dalam bentuk matriks yang diuraikan, sistem persamaan ini terlihat seperti ini:

(∑ x t 1 2 ∑ x t 1 x t 2 ∑ xt 1 x t 3 … ∑ x t 1 x t k ∑ xt 2 xt 1 ∑ x t 2 2 ∑ x t 2 x t 3 … ∑ x t 2 x t k ∑ x t 3 x t 1 ∑ xt 3 xt 2 ∑ x t 3 2 … ∑ x t 3 x t k ⋮ ⋮ ⋮ ⋱ ⋮ ∑ x t k x t 1 ∑ x t k x t 2 ∑ x t k x t 3 … ∑ x t k 2) (b 1 b 2 b 3 ⋮ b k) = (∑ x t 1 t ∑ xt 2 yt ∑ xt 3 y t ⋮ ∑ x t k y t) , (\displaystyle (\begin(pmatrix)\sum x_(t1)^(2)&\sum x_(t1)x_(t2)&\sum x_(t1)x_(t3)&\ldots &\jumlah x_(t1)x_(tk)\\\jumlah x_(t2)x_(t1)&\jumlah x_(t2)^(2)&\jumlah x_(t2)x_(t3)&\ltitik &\ jumlah x_(t2)x_(tk)\\\jumlah x_(t3)x_(t1)&\jumlah x_(t3)x_(t2)&\jumlah x_(t3)^(2)&\ltitik &\jumlah x_ (t3)x_(tk)\\\vdots &\vdots &\vdots &\ddots &\vdots \\\sum x_(tk)x_(t1)&\sum x_(tk)x_(t2)&\sum x_ (tk)x_(t3)&\ldots &\sum x_(tk)^(2)\\\end(pmatrix))(\begin(pmatrix)b_(1)\\b_(2)\\b_(3 )\\\vdots \\b_(k)\\\end(pmatrix))=(\begin(pmatrix)\sum x_(t1)y_(t)\\\sum x_(t2)y_(t)\\ \jumlah x_(t3)y_(t)\\\vdots \\\jumlah x_(tk)y_(t)\\\end(pmatrix)),) di mana semua jumlah diambil alih semua nilai yang valid t (\gaya tampilan t).

Jika suatu konstanta dimasukkan dalam model (seperti biasa), maka xt 1 = 1 (\displaystyle x_(t1)=1) di depan semua orang t (\gaya tampilan t), oleh karena itu, di pojok kiri atas matriks sistem persamaan terdapat jumlah observasi n (\gaya tampilan n), dan di elemen sisa baris pertama dan kolom pertama - cukup jumlah nilai variabel: ∑ x t j (\displaystyle \jumlah x_(tj)) dan elemen pertama dari sisi kanan sistem adalah ∑ y t (\displaystyle \jumlah y_(t)).

Penyelesaian sistem persamaan ini memberikan rumus umum estimasi kuadrat terkecil untuk model linier:

Untuk tujuan analitis, representasi terakhir dari rumus ini berguna (dalam sistem persamaan, ketika membagi dengan n, rata-rata aritmatika muncul sebagai pengganti jumlah). Jika dalam model regresi data terpusat, maka dalam representasi ini matriks pertama mempunyai arti matriks kovarians sampel faktor, dan matriks kedua merupakan vektor kovarians faktor dengan variabel terikat. Kalau selain datanya juga dinormalisasi untuk UMK (yaitu, pada akhirnya terstandarisasi), maka matriks pertama mempunyai arti matriks korelasi sampel faktor, vektor kedua - vektor korelasi sampel faktor dengan variabel terikat.

Properti penting dari estimasi OLS untuk model dengan konstan- garis regresi yang dibangun melewati pusat gravitasi data sampel, sehingga persamaan terpenuhi:

Secara khusus, dalam kasus ekstrim, ketika satu-satunya regressor adalah sebuah konstanta, kita menemukan bahwa estimasi OLS dari satu-satunya parameter (konstanta itu sendiri) sama dengan nilai rata-rata dari variabel yang dijelaskan. Artinya, mean aritmatika, yang dikenal karena sifat-sifatnya yang baik dari hukum bilangan besar, juga merupakan estimasi kuadrat terkecil - mean tersebut memenuhi kriteria jumlah minimum deviasi kuadrat darinya.

Kasus khusus yang paling sederhana

Dalam kasus regresi linier berpasangan y t = a + b x t + ε t (\displaystyle y_(t)=a+bx_(t)+\varepsilon _(t)), ketika ketergantungan linier dari satu variabel terhadap variabel lain diperkirakan, rumus perhitungan disederhanakan (Anda dapat melakukannya tanpa aljabar matriks). Sistem persamaannya berbentuk:

Dari sini mudah untuk menemukan estimasi koefisien:

Terlepas dari kenyataan bahwa dalam kasus umum model dengan konstanta lebih disukai, dalam beberapa kasus diketahui dari pertimbangan teoritis bahwa konstanta a (\gaya tampilan a) harus sama dengan nol. Misalnya dalam fisika hubungan antara tegangan dan arus adalah U = I ⋅ R (\displaystyle U=I\cdot R); Saat mengukur tegangan dan arus, perlu untuk memperkirakan resistansi. Dalam hal ini kita berbicara tentang model y = bx (\gaya tampilan y=bx). Dalam hal ini, alih-alih menggunakan sistem persamaan, kita mempunyai persamaan tunggal

(∑ x t 2) b = ∑ x t y t (\displaystyle \kiri(\jumlah x_(t)^(2)\kanan)b=\jumlah x_(t)y_(t)).

Oleh karena itu, rumus untuk memperkirakan koefisien tunggal berbentuk

B ^ = ∑ t = 1 n x t y t ∑ t = 1 n x t 2 = x y ¯ x 2 ¯ (\displaystyle (\hat (b))=(\frac (\sum _(t=1)^(n)x_(t )y_(t))(\jumlah _(t=1)^(n)x_(t)^(2)))=(\frac (\overline (xy))(\overline (x^(2)) ))).

Kasus model polinomial

Jika data cocok dengan fungsi regresi polinomial satu variabel f (x) = b 0 + ∑ i = 1 k b i x i (\displaystyle f(x)=b_(0)+\sum \limits _(i=1)^(k)b_(i)x^(i)), lalu, mengamati derajat x i (\gaya tampilan x^(i)) sebagai faktor independen untuk masing-masing saya (\gaya tampilan i) parameter model dapat diperkirakan berdasarkan rumus umum untuk memperkirakan parameter model linier. Untuk melakukan ini, cukup dengan memperhitungkan rumus umum bahwa dengan interpretasi seperti itu x t i x t j = x t i x t j = x t i + j (\displaystyle x_(ti)x_(tj)=x_(t)^(i)x_(t)^(j)=x_(t)^(i+j)) dalam arti kedekatan maksimum vektor x t j y t = x t j y t (\displaystyle x_(tj)y_(t)=x_(t)^(j)y_(t)). Oleh karena itu, persamaan matriks dalam hal ini akan berbentuk:

(n ∑ n x t … ∑ n x t k ∑ n x t ∑ n x t 2 … ∑ n x t k + 1 ⋮ ⋮ ⋱ ⋮ ∑ n x t k ∑ n x t k + 1 … ∑ n x t 2 k) [ b 0 b 1 ⋮ b k ] = [ ∑ n y t ∑ n t y t ⋮ ∑ n x t k y t ] .

(\displaystyle (\begin(pmatrix)n&\sum \limits _(n)x_(t)&\ldots &\sum \limits _(n)x_(t)^(k)\\\sum \limits _( n)x_(t)&\jumlah \batas _(n)x_(t)^(2)&\ltitik &\jumlah \batas _(n)x_(t)^(k+1)\\\vtitik & \vtitik &\ddots &\vdots \\\jumlah \batas _(n)x_(t)^(k)&\jumlah \batas _(n)x_(t)^(k+1)&\ltitik &\ jumlah \batas _(n)x_(t)^(2k)\end(pmatrix))(\begin(bmatrix)b_(0)\\b_(1)\\\vdots \\b_(k)\end( bmatrix))=(\begin(bmatrix)\sum \limits _(n)y_(t)\\\sum \limits _(n)x_(t)y_(t)\\\vdots \\\sum \limits _(n)x_(t)^(k)y_(t)\end(bmatriks)).)

Sifat statistik penduga OLS

1. Pertama-tama, kami mencatat bahwa untuk model linier, estimasi OLS adalah estimasi linier, sebagai berikut dari rumus di atas. Untuk estimasi OLS yang tidak bias, kondisi analisis regresi yang paling penting harus dan cukup dipenuhi: ekspektasi matematis bersyarat faktor dari kesalahan acak harus sama dengan nol. Kondisi ini khususnya terpenuhi jika
2. ekspektasi matematis dari kesalahan acak adalah nol, dan

faktor dan kesalahan acak adalah variabel  acak  independen. Kondisi kedua - kondisi eksogenitas faktor - bersifat fundamental. Jika properti ini tidak terpenuhi, maka kita dapat berasumsi bahwa hampir semua estimasi akan sangat tidak memuaskan: estimasi tersebut bahkan tidak akan konsisten (yaitu, bahkan sejumlah besar data tidak memungkinkan kita memperoleh estimasi berkualitas tinggi dalam kasus ini. ). Dalam kasus klasik, asumsi yang lebih kuat dibuat mengenai determinisme faktor, dibandingkan dengan kesalahan acak, yang secara otomatis berarti bahwa kondisi eksogenitas terpenuhi. Secara umum, untuk konsistensi estimasi, cukup memenuhi kondisi eksogenitas bersama dengan konvergensi matriks. V x (\gaya tampilan V_(x))

ke beberapa matriks non-singular ketika ukuran sampel meningkat hingga tak terbatas.

Agar, selain konsistensi dan ketidakbiasannya, pendugaan kuadrat terkecil (biasa) juga efektif (yang terbaik di kelas pendugaan tak bias linier), sifat tambahan kesalahan acak harus dipenuhi: Asumsi ini dapat dirumuskan untuk matriks kovarians dari vektor kesalahan acak.

V (ε) = σ 2 I (\displaystyle V(\varepsilon)=\sigma ^(2)I) Model linier yang memenuhi kondisi ini disebut. Estimasi OLS untuk regresi linier klasik adalah estimasi yang tidak bias, konsisten, dan paling efektif di kelas semua estimasi linier yang tidak bias (dalam literatur bahasa Inggris, singkatan tersebut terkadang digunakan BIRU (Penaksir Tak Bias Linier Terbaik) - estimasi linier tidak bias terbaik; Dalam sastra Rusia, teorema Gauss-Markov lebih sering dikutip). Seperti yang mudah ditunjukkan, matriks kovarians dari vektor estimasi koefisien akan sama dengan:

V (b ^ O L S) = σ 2 (X T X) − 1 (\displaystyle V((\hat (b))_(OLS))=\sigma ^(2)(X^(T)X)^(-1 )).

Efisiensi berarti bahwa matriks kovarians ini “minimal” (kombinasi koefisien linier apa pun, dan khususnya koefisien itu sendiri, memiliki varians minimal), yaitu, dalam kelas penduga linier tak bias, penduga OLS adalah yang terbaik. Elemen diagonal dari matriks ini - varians dari estimasi koefisien - merupakan parameter penting dari kualitas estimasi yang diperoleh. Namun, matriks kovarians tidak dapat dihitung karena varians kesalahan acak tidak diketahui. Dapat dibuktikan bahwa pendugaan varians kesalahan acak yang tidak bias dan konsisten (untuk model linier klasik) adalah besaran:

S 2 = R S S / (n − k) (\displaystyle s^(2)=RSS/(n-k)).

Mengganti nilai ini ke dalam rumus matriks kovarians, kita memperoleh estimasi matriks kovarians. Estimasi yang dihasilkan juga tidak bias dan konsisten. Penting juga bahwa estimasi varians kesalahan (dan karenanya varians koefisien) dan estimasi parameter model merupakan variabel acak independen, yang memungkinkan diperolehnya statistik uji untuk menguji hipotesis tentang koefisien model.

Perlu dicatat bahwa jika asumsi klasik tidak terpenuhi, estimasi parameter OLS bukanlah yang paling efisien dan, dimana W (\gaya tampilan W) adalah matriks bobot pasti positif simetris. Kuadrat terkecil konvensional adalah kasus khusus dari pendekatan ini, dimana matriks bobotnya proporsional terhadap matriks identitas. Seperti diketahui, untuk matriks (atau operator) simetris terdapat perluasan W = P T P (\displaystyle W=P^(T)P). Oleh karena itu, fungsi yang ditentukan dapat direpresentasikan sebagai berikut e T P T P e = (P e) T P e = e ∗ T e ∗ (\displaystyle e^(T)P^(T)Pe=(Pe)^(T)Pe=e_(*)^(T)e_( *)), artinya, fungsi ini dapat direpresentasikan sebagai jumlah kuadrat dari beberapa “sisa” yang ditransformasikan. Dengan demikian, kita dapat membedakan kelas metode kuadrat terkecil - metode LS (Kuadrat Terkecil).

Telah dibuktikan (teorema Aitken) bahwa untuk model regresi linier umum (di mana tidak ada batasan yang dikenakan pada matriks kovarians kesalahan acak), yang paling efektif (di kelas estimasi linier tidak bias) adalah apa yang disebut estimasi. Kuadrat Terkecil yang digeneralisasi (GLS - Kuadrat Terkecil yang Digeneralisasi)- Metode LS dengan matriks bobot sama dengan matriks kovarians terbalik kesalahan acak: W = V ε − 1 (\displaystyle W=V_(\varepsilon )^(-1)).

Dapat ditunjukkan bahwa rumus estimasi GLS terhadap parameter model linier berbentuk

B ^ G L S = (X T V − 1 X) − 1 X T V − 1 y (\displaystyle (\hat (b))_(GLS)=(X^(T)V^(-1)X)^(-1) X^(T)V^(-1)y).

Oleh karena itu, matriks kovarians dari perkiraan ini akan sama dengan

V (b ^ G L S) = (X T V − 1 X) − 1 (\displaystyle V((\hat (b))_(GLS))=(X^(T)V^(-1)X)^(- 1)).

Padahal, inti dari OLS terletak pada transformasi (linier) tertentu (P) dari data asli dan penerapan OLS biasa pada data yang diubah. Tujuan dari transformasi ini adalah agar pada data yang ditransformasi kesalahan acaknya sudah memenuhi asumsi klasik.

OLS tertimbang

Dalam kasus matriks bobot diagonal (dan oleh karena itu matriks kovarians kesalahan acak), kita memiliki apa yang disebut Kuadrat Terkecil Tertimbang (WLS). Dalam hal ini, jumlah kuadrat tertimbang dari residu model diminimalkan, yaitu setiap observasi menerima “bobot” yang berbanding terbalik dengan varian kesalahan acak dalam observasi ini: e T W e = ∑ t = 1 n e t 2 σ t 2 (\displaystyle e^(T)Kita=\jumlah _(t=1)^(n)(\frac (e_(t)^(2))(\ sigma_(t)^(2)))). Faktanya, data ditransformasikan dengan melakukan pembobotan observasi (dibagi dengan jumlah yang sebanding dengan perkiraan standar deviasi kesalahan acak), dan OLS biasa diterapkan pada data berbobot.

ISBN 978-5-7749-0473-0 .

Metode kuadrat terkecil adalah prosedur matematis untuk menyusun persamaan linier yang paling sesuai dengan himpunan pasangan terurut dengan mencari nilai a dan b, koefisien dalam persamaan garis. Tujuan dari kuadrat terkecil adalah untuk meminimalkan total kesalahan kuadrat antara nilai y dan ŷ. Jika untuk setiap titik kita menentukan kesalahan ŷ, metode kuadrat terkecil meminimalkan:

dimana n = banyaknya pasangan terurut pada garis tersebut. sedekat mungkin dengan data.

Konsep ini diilustrasikan pada gambar

Berdasarkan gambar, garis yang paling sesuai dengan data, yaitu garis regresi, meminimalkan total kesalahan kuadrat dari empat titik pada grafik. Saya akan menunjukkan cara menentukannya menggunakan kuadrat terkecil dengan contoh berikut.

Bayangkan pasangan muda yang baru saja tinggal bersama dan berbagi meja rias di kamar mandi. Pria muda itu mulai memperhatikan bahwa setengah dari mejanya terus menyusut, digantikan oleh mousse rambut dan produk kedelai. Selama beberapa bulan terakhir, pria itu telah memantau dengan cermat laju peningkatan jumlah benda di sisi mejanya. Tabel di bawah menunjukkan jumlah barang yang dikumpulkan gadis tersebut di meja rias kamar mandinya selama beberapa bulan terakhir.

Karena tujuan kita adalah untuk mengetahui apakah jumlah item bertambah seiring waktu, “Bulan” akan menjadi variabel independen, dan “Jumlah item” akan menjadi variabel dependen.

Dengan menggunakan metode kuadrat terkecil, kita menentukan persamaan yang paling sesuai dengan data dengan menghitung nilai a, titik potong y, dan b, kemiringan garis:

a = y rata-rata - bx rata-rata

dimana x avg adalah nilai rata-rata dari x, variabel bebas, y avg adalah nilai rata-rata dari y, variabel bebas.

Tabel di bawah ini merangkum perhitungan yang diperlukan untuk persamaan ini.

Kurva efek untuk contoh bak mandi kita akan diberikan oleh persamaan berikut:

Karena persamaan kita memiliki kemiringan positif sebesar 0,976, orang tersebut mempunyai bukti bahwa jumlah item di tabel meningkat seiring waktu dengan rata-rata 1 item per bulan. Grafik menunjukkan kurva efek dengan pasangan terurut.

Ekspektasi jumlah item selama enam bulan ke depan (bulan 16) dihitung sebagai berikut:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 butir

Jadi, inilah waktunya bagi pahlawan kita untuk mengambil tindakan.

Fungsi TREND di Excel

Seperti yang mungkin sudah Anda duga, Excel memiliki fungsi untuk menghitung nilai metode kuadrat terkecil. Fungsi ini disebut TREND. Sintaksnya adalah sebagai berikut:

TREND (nilai Y yang diketahui; nilai X yang diketahui; nilai X baru; konstan)

nilai Y yang diketahui – array variabel dependen, dalam kasus kami, jumlah objek di tabel

nilai yang diketahui X – array variabel independen, dalam kasus kami ini adalah bulan

nilai X baru – nilai X baru (bulan) yang mana fungsi TREN mengembalikan nilai yang diharapkan dari variabel dependen (jumlah item)

konstanta - opsional. Nilai Boolean yang menentukan apakah konstanta b harus bernilai 0.

Misalnya, gambar menunjukkan fungsi TREND yang digunakan untuk menentukan jumlah item yang diharapkan pada meja rias kamar mandi untuk bulan ke-16.

Contoh.

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

Sebagai hasil dari penyelarasannya, suatu fungsi diperoleh

Menggunakan metode kuadrat terkecil, perkirakan data ini dengan ketergantungan linier y=kapak+b(temukan parameter A Dan B). Cari tahu mana di antara dua garis yang lebih baik (dalam pengertian metode kuadrat terkecil) yang menyelaraskan data eksperimen. Buatlah gambar.

Inti dari metode kuadrat terkecil (LSM).

Tugasnya adalah menemukan koefisien ketergantungan linier di mana fungsi dua variabel berada A Dan B mengambil nilai terkecil. Artinya, diberikan A Dan B jumlah simpangan kuadrat data eksperimen dari garis lurus yang ditemukan akan menjadi yang terkecil. Inilah inti dari metode kuadrat terkecil.

Jadi, penyelesaian contohnya adalah mencari titik ekstrem dari suatu fungsi dua variabel.

Menurunkan rumus untuk mencari koefisien.

Sebuah sistem dua persamaan dengan dua hal yang tidak diketahui dikompilasi dan diselesaikan. Menemukan turunan parsial suatu fungsi oleh variabel A Dan B, kita menyamakan turunan ini dengan nol.

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

Diberikan A Dan B fungsi mengambil nilai terkecil. Bukti dari fakta ini diberikan di bawah dalam teks di akhir halaman.

Itulah keseluruhan metode kuadrat terkecil. Rumus untuk mencari parameter A berisi jumlah,,, dan parameter N- jumlah data eksperimen. Kami menyarankan untuk menghitung nilai jumlah ini secara terpisah. Koefisien B ditemukan setelah perhitungan A.

Saatnya mengingat contoh aslinya.

Larutan.

Dalam contoh kita n=5. Kami mengisi tabel untuk kemudahan 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 pada baris kelima tabel diperoleh dengan mengkuadratkan nilai pada baris ke-2 untuk setiap angka Saya.

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

Kami menggunakan rumus metode kuadrat terkecil untuk mencari koefisien A Dan B. Kami mengganti nilai yang sesuai dari kolom terakhir tabel ke dalamnya:

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

Masih mencari tahu garis yang mana kamu = 0,165x+2,184 atau lebih mendekati data asli, yaitu estimasi menggunakan metode kuadrat terkecil.

Estimasi kesalahan metode kuadrat terkecil.

Untuk melakukan ini, Anda perlu menghitung jumlah deviasi kuadrat dari data asli dari garis-garis ini Dan , nilai yang lebih kecil menunjukkan garis yang lebih mendekati data asli dalam pengertian metode kuadrat terkecil.

Sejak , maka lurus kamu = 0,165x+2,184 lebih mendekati data aslinya.

Ilustrasi grafis metode kuadrat terkecil (LS).

Semuanya terlihat jelas di grafik. Garis merah adalah garis lurus yang ditemukan kamu = 0,165x+2,184, garis biru adalah , titik merah muda adalah data asli.

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

Masalah perkiraan fungsi seperti ini sering muncul:

ketika membuat rumus perkiraan untuk menghitung nilai besaran karakteristik dari proses yang diteliti menggunakan data tabel yang diperoleh sebagai hasil percobaan;

dalam integrasi numerik, diferensiasi, penyelesaian persamaan diferensial, dll;

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

ketika menentukan nilai besaran karakteristik suatu proses di luar interval yang dipertimbangkan, khususnya ketika memprediksi.

Jika, untuk memodelkan proses tertentu yang ditentukan oleh tabel, kita membangun sebuah fungsi yang kira-kira menggambarkan proses ini berdasarkan metode kuadrat terkecil, maka itu akan disebut fungsi aproksimasi (regresi), dan masalah membangun fungsi aproksimasi itu sendiri akan disebut masalah perkiraan.

Artikel ini membahas kemampuan paket MS Excel untuk memecahkan masalah jenis ini, selain itu, menyediakan metode dan teknik untuk membangun (membuat) regresi untuk fungsi tabulasi (yang merupakan dasar analisis regresi).

Excel memiliki dua opsi untuk membuat regresi.

Menambahkan regresi (garis tren) yang dipilih ke diagram yang dibuat berdasarkan tabel data untuk karakteristik proses yang diteliti (hanya tersedia jika diagram telah dibuat);

Menggunakan fungsi statistik bawaan pada lembar kerja Excel, memungkinkan Anda memperoleh regresi (garis tren) secara langsung berdasarkan tabel data sumber.

Menambahkan garis tren ke grafik

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

membangun berdasarkan metode kuadrat terkecil dan menambahkan lima jenis regresi ke diagram, yang memodelkan proses yang diteliti dengan berbagai tingkat akurasi;

tambahkan persamaan regresi yang dibangun ke diagram;

menentukan tingkat kesesuaian regresi yang dipilih dengan data yang ditampilkan pada grafik.

Berdasarkan data grafik, Excel memungkinkan Anda memperoleh jenis regresi linier, polinomial, logaritmik, pangkat, eksponensial, yang ditentukan oleh persamaan:

kamu = kamu(x)

dimana x adalah variabel bebas yang sering mengambil nilai barisan bilangan asli (1; 2; 3; ...) dan menghasilkan, misalnya, hitungan mundur waktu dari proses yang diteliti (karakteristik).

1 . Regresi linier baik untuk memodelkan karakteristik yang nilainya naik atau turun dengan laju yang konstan. Ini adalah model yang paling sederhana untuk dibangun untuk proses yang sedang dipelajari. Itu dibangun sesuai dengan persamaan:

kamu = mx + b

dimana m adalah garis singgung kemiringan regresi linier terhadap sumbu x; b - koordinat titik potong regresi linier dengan sumbu ordinat.

2 . Garis tren polinomial berguna untuk menggambarkan karakteristik yang memiliki beberapa titik ekstrem yang berbeda (maksimum dan minimum). Pilihan derajat polinomial ditentukan oleh jumlah ekstrem dari karakteristik yang diteliti. Jadi, polinomial derajat kedua dapat menggambarkan 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:

kamu = 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 ketika memodelkan karakteristik yang nilainya awalnya berubah dengan cepat dan kemudian stabil secara bertahap.

y = c ln(x) + b

4 . Garis tren hukum pangkat memberikan hasil yang baik jika nilai hubungan yang diteliti dicirikan oleh perubahan laju pertumbuhan yang konstan. Contoh ketergantungan tersebut adalah grafik gerak dipercepat beraturan sebuah mobil. Jika terdapat nilai nol atau negatif pada data, Anda tidak dapat menggunakan garis tren pangkat.

Dibangun sesuai dengan persamaan:

kamu = c xb

dimana koefisien b, c adalah konstanta.

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

Dibangun sesuai dengan persamaan:

y = c ebx

dimana koefisien b, c adalah konstanta.

Saat memilih garis tren, Excel secara otomatis menghitung nilai R2, yang mencirikan keandalan perkiraan: semakin dekat nilai R2 ke satu, semakin andal garis tren tersebut mendekati proses yang sedang dipelajari. Jika perlu, nilai R2 selalu dapat ditampilkan pada grafik.

Ditentukan dengan rumus:

Untuk menambahkan garis tren ke rangkaian data:

mengaktifkan bagan berdasarkan serangkaian data, yaitu klik di dalam area bagan. Item Diagram 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 dapat dengan mudah diterapkan dengan menggerakkan penunjuk tetikus ke grafik yang sesuai dengan salah satu rangkaian data dan mengklik kanan; Di menu konteks yang muncul, pilih perintah Tambahkan garis tren. Kotak dialog Trendline akan muncul di layar dengan tab Type terbuka (Gbr. 1).

Setelah ini Anda perlu:

Pilih tipe garis tren yang diperlukan pada tab Type (tipe Linear dipilih secara default). Untuk tipe Polinomial, pada kolom Derajat, tentukan derajat polinomial yang dipilih.

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

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

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

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

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

tampilkan nilai keandalan perkiraan R2 di area diagram, yang mana Anda harus mengaktifkan kotak centang Tempatkan nilai keandalan perkiraan pada diagram (R^2);

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.

Untuk mulai mengedit garis tren yang sudah digambar, ada tiga cara:

gunakan perintah Selected trend line dari menu Format, setelah sebelumnya memilih garis tren;

pilih perintah Format garis tren dari menu konteks, yang dipanggil dengan mengklik kanan pada garis tren;

klik dua kali pada garis tren.

Kotak dialog Format Garis Tren akan muncul di layar (Gbr. 3), berisi tiga tab: Tampilan, Jenis, Parameter, dan konten dari dua tab terakhir sepenuhnya bertepatan dengan tab serupa pada kotak dialog Garis Tren (Gbr. 1 -2). Pada tab View, Anda dapat mengatur jenis garis, warna dan ketebalannya.

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

Keuntungan dari alat analisis regresi yang dipertimbangkan adalah:

relatif mudahnya membuat 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;

kemampuan untuk memprediksi perilaku proses yang diteliti dengan sejumlah langkah maju dan mundur secara sewenang-wenang (dalam batas akal sehat);

kemampuan memperoleh persamaan garis tren dalam bentuk analitis;

kemungkinan, jika perlu, untuk memperoleh penilaian atas keandalan perkiraan.

Kerugiannya antara lain sebagai berikut:

konstruksi garis tren hanya dilakukan jika terdapat diagram yang dibangun di atas serangkaian data;

proses menghasilkan rangkaian data untuk karakteristik yang diteliti berdasarkan persamaan garis tren yang diperoleh agak berantakan: persamaan regresi yang diperlukan diperbarui dengan setiap perubahan nilai rangkaian data asli, tetapi hanya dalam area grafik , sedangkan rangkaian data yang dibentuk berdasarkan persamaan garis lama, trennya tetap tidak berubah;

Dalam laporan PivotChart, mengubah tampilan bagan atau laporan PivotTable terkait tidak mempertahankan garis tren yang ada, artinya sebelum Anda menggambar garis tren atau memformat laporan PivotChart, Anda harus memastikan bahwa tata letak laporan memenuhi persyaratan yang diperlukan.

Garis tren dapat digunakan untuk melengkapi rangkaian data yang disajikan pada bagan seperti grafik, histogram, bagan area datar non-standar, bagan batang, bagan sebar, bagan gelembung, dan bagan saham.

Anda tidak dapat menambahkan garis tren ke rangkaian data dalam diagram 3D, normalisasi, radar, lingkaran, dan donat.

Menggunakan fungsi bawaan Excel

Excel juga memiliki alat analisis regresi untuk merencanakan garis tren di luar area grafik. Ada sejumlah fungsi lembar kerja statistik yang dapat digunakan untuk tujuan ini, namun semuanya hanya mengizinkan regresi linier atau eksponensial.

Excel memiliki beberapa fungsi untuk membangun regresi linier, khususnya:

KECENDERUNGAN;

• LERENG dan POTONG.

Serta beberapa fungsi untuk membangun garis tren eksponensial, khususnya:

LGRFPRIBL.

Perlu diperhatikan bahwa teknik pembuatan regresi menggunakan fungsi TREND dan GROWTH hampir sama. Hal yang sama juga berlaku pada pasangan fungsi LINEST dan LGRFPRIBL. Untuk keempat fungsi ini, pembuatan tabel nilai menggunakan fitur Excel seperti rumus array, yang agak mengacaukan proses pembuatan regresi. Perhatikan juga bahwa konstruksi regresi linier, menurut pendapat kami, paling mudah dilakukan dengan menggunakan fungsi SLOPE dan INTERCEPT, dengan fungsi pertama menentukan kemiringan regresi linier, dan fungsi kedua menentukan segmen yang dicegat oleh regresi pada y. -sumbu.

Keuntungan alat fungsi bawaan untuk analisis regresi adalah:

proses yang cukup sederhana dan seragam untuk menghasilkan rangkaian data dari karakteristik yang diteliti untuk semua fungsi statistik bawaan yang menentukan garis tren;

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

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

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 memperoleh perkiraan yang mendekati kenyataan. Selain itu, saat menggunakan fungsi TREND dan GROWTH, persamaan garis tren tidak diketahui.

Perlu dicatat bahwa penulis tidak bermaksud menyajikan analisis regresi dengan tingkat kelengkapan apa pun. Tugas utamanya adalah menunjukkan, dengan menggunakan contoh spesifik, kemampuan paket Excel saat memecahkan masalah perkiraan; mendemonstrasikan alat efektif apa yang dimiliki Excel untuk membuat regresi dan perkiraan; mengilustrasikan bagaimana masalah tersebut dapat diselesaikan dengan relatif mudah bahkan oleh pengguna yang tidak memiliki pengetahuan luas tentang analisis regresi.

Contoh pemecahan masalah tertentu

Mari pertimbangkan untuk memecahkan masalah tertentu menggunakan alat yang tercantum dalam paket Excel.

Masalah 1

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

Buat diagram.

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

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

Buatlah perkiraan keuntungan perusahaan untuk tahun 2003 dan 2004.

Solusi masalah

Dalam rentang sel A4:C11 lembar kerja Excel, masukkan lembar kerja yang ditunjukkan pada Gambar. 4.

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

Kami mengaktifkan diagram yang dibuat dan, sesuai dengan metode yang dijelaskan di atas, setelah memilih jenis garis tren di kotak dialog Garis Tren (lihat Gambar 1), kami secara bergantian menambahkan garis tren linier, kuadrat, dan kubik ke diagram. Di kotak dialog yang sama, buka tab Parameter (lihat Gambar 2), di bidang Nama kurva perkiraan (dihaluskan), masukkan nama tren yang ditambahkan, dan di bidang Perkiraan maju untuk: periode, atur nilai 2, karena direncanakan membuat perkiraan keuntungan untuk dua tahun ke depan. Untuk menampilkan persamaan regresi dan nilai reliabilitas aproksimasi R2 pada 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 dibuat, untuk itu kami menggunakan tab Tampilan pada kotak dialog Format Garis Tren (lihat Gambar 3). Diagram yang dihasilkan dengan garis tren tambahan ditunjukkan pada Gambar. 5.

Memperoleh data tabel keuntungan perusahaan untuk setiap garis tren tahun 1995-2004.

Mari kita gunakan persamaan garis tren yang disajikan pada Gambar. 5. Untuk melakukan ini, dalam sel rentang D3:F3, masukkan informasi teks 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 dicatat bahwa setiap sel dengan rumus regresi linier dari rentang sel D4:D13 memiliki argumen sel yang sesuai dari rentang A4:A13. Demikian pula, untuk regresi kuadratik, isikan rentang sel E4:E13, dan untuk regresi kubik, isikan rentang sel F4:F13. Dengan demikian, perkiraan laba perusahaan untuk tahun 2003 dan 2004 telah disusun. menggunakan tiga tren. Tabel nilai yang dihasilkan ditunjukkan pada Gambar. 6.

Buat diagram.

Masalah 2

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

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

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

Solusi masalah

Mengikuti metodologi yang diberikan dalam menyelesaikan masalah 1, kita memperoleh diagram dengan garis tren logaritmik, pangkat, dan eksponensial ditambahkan ke dalamnya (Gbr. 7). Selanjutnya, dengan menggunakan persamaan garis tren yang diperoleh, kita mengisi tabel nilai laba perusahaan, termasuk nilai prediksi untuk tahun 2003 dan 2004. (Gbr. 8).

Pada Gambar. 5 dan gambar. terlihat bahwa model dengan tren logaritmik memiliki nilai reliabilitas aproksimasi yang paling rendah

R2 = 0,8659

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

Masalah 3

Dengan tabel data laba suatu usaha angkutan motor tahun 1995-2002 yang diberikan pada tugas 1, maka perlu dilakukan langkah-langkah sebagai berikut.

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

Dengan menggunakan fungsi TREND dan GROWTH, buatlah perkiraan laba perusahaan untuk tahun 2003 dan 2004.

Buatlah diagram untuk data asli dan rangkaian data yang dihasilkan.

Solusi masalah

Mari kita gunakan lembar kerja untuk Soal 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. Pada kotak dialog Function Wizard yang muncul, pilih fungsi TREND dari kategori Statistik, lalu klik tombol OK. Operasi yang sama dapat dilakukan dengan mengklik tombol (Sisipkan Fungsi) pada toolbar standar.

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

Untuk menjadikan rumus yang dimasukkan menjadi rumus array, gunakan kombinasi tombol ++ .

Rumus yang kita masukkan pada formula bar akan terlihat seperti: =(TREND(C4:C11,B4:B11)).

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

Membuat perkiraan laba perusahaan untuk tahun 2003 dan 2004. diperlukan:

pilih rentang sel D12:D13 di mana nilai prediksi fungsi TREND akan dimasukkan.

panggil fungsi TREND dan di kotak dialog Argumen Fungsi yang muncul, masukkan di bidang Diketahui_nilai_y - 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 kombinasi tombol 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 fungsi TREND (lihat Gambar. 9).

Seri data diisi dengan cara yang sama menggunakan fungsi GROWTH, yang digunakan dalam analisis ketergantungan nonlinier dan bekerja dengan cara yang persis sama seperti TREND liniernya.

Gambar 10 menunjukkan tabel dalam mode tampilan rumus.

Untuk data awal dan rangkaian data yang diperoleh, diagramnya ditunjukkan pada Gambar. 11.

Masalah 4

Dengan tabel data penerimaan permintaan jasa oleh dinas pengiriman suatu perusahaan angkutan motor untuk periode tanggal 1 sampai dengan tanggal 11 bulan berjalan, perlu dilakukan tindakan sebagai berikut.

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

Dapatkan rangkaian data untuk regresi eksponensial menggunakan fungsi LGRFPRIBL.

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

Buat diagram untuk seri data asli dan yang diterima.

Solusi masalah

Perhatikan bahwa, tidak seperti fungsi TREND dan GROWTH, tidak ada satu pun fungsi yang tercantum di atas (SLOPE, INTERCEPT, LINEST, LGRFPRIB) yang merupakan regresi. Fungsi-fungsi ini hanya memainkan peran pendukung, 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 berhubungan dengan fungsi TREND dan GROWTH.

1 . Mari kita buat regresi linier dengan persamaan:

kamu = mx+b

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

Untuk melakukan ini, kami melakukan tindakan berikut:

masukkan tabel asli ke dalam cell range A4:B14;

nilai parameter m akan ditentukan di sel C19. Pilih fungsi Kemiringan dari kategori Statistik; masukkan rentang sel B4:B14 di bidang nilai_yang diketahui dan rentang sel A4:A14 di bidang nilai_yang diketahui_x.

Rumusnya akan dimasukkan pada sel C19: =SLOPE(B4:B14,A4:A14);

Selanjutnya masukkan rumus regresi linier pada sel C4 dengan bentuk: =$C*A4+$D. Dalam rumus ini, sel C19 dan D19 ditulis dengan referensi absolut (alamat sel tidak boleh berubah selama kemungkinan penyalinan). Tanda referensi absolut $ dapat diketik dari keyboard atau menggunakan tombol F4, setelah menempatkan kursor pada alamat sel.

2 Dengan menggunakan gagang isian, salin rumus ini ke dalam rentang sel C4:C17. Kami memperoleh seri data yang diperlukan (Gbr. 12). Karena jumlah aplikasi adalah bilangan bulat, Anda harus mengatur format angka dengan jumlah desimal ke 0 pada tab Angka di jendela Format Sel.

kamu = mx+b

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

menggunakan fungsi LINEST.

Untuk melakukan ini:

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

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

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

. Kami membangun regresi eksponensial dengan persamaan:

menggunakan fungsi LGRFPRIBL dilakukan dengan cara yang sama:

Pada range sel C21:D21 kita masukkan fungsi LGRFPRIBL sebagai rumus array: =( 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;

rumusnya dimasukkan ke dalam 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 Gambar. Gambar 13 menunjukkan tabel di mana Anda dapat melihat fungsi yang kami gunakan dengan rentang sel yang diperlukan, serta rumusnya. Besarnya 2 R ditelepon.

koefisien determinasi

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

Secara khusus, metode kuadrat terkecil dapat digunakan untuk "menyelesaikan" sistem persamaan linier N Untuk menilai signifikansi R digunakan uji F Fisher yang dihitung dengan menggunakan rumus

- ukuran sampel (jumlah percobaan);

k adalah jumlah koefisien model. N Dan Jika F melebihi beberapa nilai kritis untuk data tersebut k

Dengan demikian, signifikansi R tidak hanya ditentukan oleh nilainya, tetapi juga oleh rasio antara jumlah percobaan dan jumlah koefisien (parameter) model. Memang benar, rasio korelasi n=2 untuk model linier sederhana sama dengan 1 (satu garis lurus selalu dapat ditarik melalui 2 titik pada sebuah bidang). 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, mereka berupaya memastikan bahwa jumlah eksperimen secara signifikan melebihi jumlah koefisien model (n>k).

Untuk membangun model regresi linier, Anda memerlukan:

1) siapkan daftar n baris dan m kolom yang berisi data eksperimen (kolom berisi nilai keluaran Y harus menjadi yang pertama atau terakhir dalam daftar); Sebagai contoh, mari kita ambil data dari tugas sebelumnya, tambahkan kolom bernama “Nomor Periode.”, beri nomor pada nomor periode dari 1 hingga 12. (ini akan menjadi nilainya X)

2) masuk ke menu Data/Analisis Data/Regresi

Jika item "Analisis Data" di menu "Alat" tidak ada, maka Anda harus membuka item "Add-In" di menu yang sama dan mencentang kotak "Paket Analisis".

3) di kotak dialog "Regresi", atur:

· interval masukan Y;

· interval masukan X;

· interval keluaran - sel kiri atas interval tempat hasil perhitungan akan ditempatkan (disarankan untuk menempatkannya pada lembar kerja baru);

4) klik "OK" dan analisis hasilnya.

Metode kuadrat terkecil (OLS) memungkinkan Anda memperkirakan berbagai besaran menggunakan hasil banyak pengukuran yang mengandung kesalahan acak.

Karakteristik MNE

Ide utama dari metode ini adalah bahwa jumlah kesalahan kuadrat dianggap sebagai kriteria keakuratan penyelesaian masalah, yang ingin diminimalkan. Saat menggunakan metode ini, pendekatan numerik dan analitis dapat digunakan.

Secara khusus, sebagai implementasi numerik, metode kuadrat terkecil melibatkan pengambilan pengukuran sebanyak mungkin terhadap variabel acak yang tidak diketahui. Selain itu, semakin banyak perhitungan, semakin akurat solusinya. Berdasarkan kumpulan perhitungan ini (data awal), diperoleh kumpulan solusi estimasi lainnya, yang kemudian dipilih yang terbaik. Jika himpunan solusi diparameterisasi, maka metode kuadrat terkecil akan direduksi untuk mencari nilai parameter yang optimal.

Sebagai pendekatan analitis dalam penerapan LSM pada sekumpulan data awal (pengukuran) dan sekumpulan solusi yang diharapkan, ditentukan suatu hal tertentu (fungsional), yang dapat dinyatakan dengan rumus yang diperoleh sebagai hipotesis tertentu yang memerlukan konfirmasi. Dalam hal ini, metode kuadrat terkecil dilakukan untuk menemukan fungsi minimum pada himpunan kesalahan kuadrat dari data asli.

Harap dicatat bahwa ini bukan kesalahannya sendiri, tetapi kuadrat kesalahannya. Mengapa? Faktanya adalah sering kali penyimpangan pengukuran dari nilai eksak bersifat positif dan negatif. Saat menentukan rata-rata, penjumlahan sederhana dapat menghasilkan kesimpulan yang salah tentang kualitas estimasi, karena pembatalan nilai positif dan negatif akan mengurangi kekuatan pengambilan sampel beberapa pengukuran. Dan akibatnya, keakuratan penilaian.

Untuk mencegah hal ini terjadi, deviasi kuadrat dijumlahkan. Terlebih lagi, untuk menyamakan dimensi nilai terukur dan estimasi akhir, jumlah kesalahan kuadrat diekstraksi.

Beberapa aplikasi MNC

MNC banyak digunakan di berbagai bidang. Misalnya, dalam teori probabilitas dan statistik matematika, metode ini digunakan untuk menentukan karakteristik variabel acak seperti simpangan baku, yang menentukan lebar rentang nilai variabel acak.

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 bisa mempunyai omzet berkali-kali lipat lebih besar dibandingkan omzet gerai ritel besar kelas “masmarket”.

Inti dari metode ini

Data tabel dapat digambarkan pada bidang kartesius sebagai 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 (di Excel, ini diimplementasikan 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 metode 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 array yang terdiri dari bilangan bulat, yang jumlahnya sesuai dengan rentang dengan nilai yang diberikan. 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 banyak 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 hanya membicarakan satu, 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.