Metod ən kiçik kvadratlar(LSM) tədqiq olunan verilənlərdən seçilmiş funksiyanın kvadratik kənarlaşmalarının cəminin minimuma endirilməsinə əsaslanır. Bu yazıda xətti funksiyadan istifadə edərək mövcud məlumatları təxmini hesablayacağıqy = a x + b .

Ən kiçik kvadrat üsulu(İngilis dili) Adi siravi Ən azı Kvadratlar , O.L.S.) naməlum parametrlərin qiymətləndirilməsi baxımından reqressiya təhlilinin əsas üsullarından biridir reqressiya modelləri nümunə məlumatlarına görə.

Yalnız bir dəyişəndən asılı olan funksiyalar üzrə yaxınlaşmanı nəzərdən keçirək:

• Xətti: y=ax+b (bu məqalə)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Qeyd: Bu məqalədə 3-cü dərəcədən 6-cı dərəcəyə qədər çoxhədli ilə yaxınlaşma hallarına baxılır. Burada triqonometrik polinomla yaxınlaşma nəzərdən keçirilir.

Xətti asılılıq

Bizi 2 dəyişən arasındakı əlaqə maraqlandırır X Və y. Belə bir fərziyyə var y asılıdır X xətti qanuna görə y = balta + b. Bu əlaqənin parametrlərini müəyyən etmək üçün tədqiqatçı müşahidələr aparmışdır: x i-nin hər bir dəyəri üçün y i-nin ölçülməsi aparılmışdır (nümunə fayla bax). Müvafiq olaraq, 20 cüt dəyər olsun (x i; y i).

Qeyd: Dəyişiklik addımı olarsa X sabitdir, sonra qurmaq səpələnmiş süjetlər istifadə edilə bilər, əgər deyilsə, onda diaqram növündən istifadə etməlisiniz Ləkə .

Diaqramdan aydın görünür ki, dəyişənlər arasında əlaqə xəttinə yaxındır. Çoxsaylı düz xətlərdən hansının dəyişənlər arasındakı əlaqəni ən “düzgün” təsvir etdiyini başa düşmək üçün xətlərin müqayisə olunacağı meyarı müəyyən etmək lazımdır.

Belə bir meyar olaraq ifadəni istifadə edirik:

Harada ŷ i = a * x i + b ; n – dəyər cütlərinin sayı (bizim halda n=20)

Yuxarıdakı ifadə y i və ŷ i-nin müşahidə olunan dəyərləri arasındakı kvadrat məsafələrin cəmidir və çox vaxt SSE kimi işarələnir ( məbləğ of Kvadrat Səhvlər (Qalıqlar), kvadrat xətaların cəmi (qalıqlar)) .

Ən kiçik kvadrat üsulu belə bir xətti seçməkdir ŷ = balta + b, bunun üçün yuxarıdakı ifadə minimum qiymət alır.

Qeyd:İki ölçülü məkanda hər hansı bir xətt unikal olaraq 2 parametrin dəyəri ilə müəyyən edilir: a (maili) və b (növbəli).

Hesab olunur ki, kvadrat məsafələrin cəmi nə qədər kiçik olarsa, müvafiq xətt mövcud məlumatları bir o qədər yaxşı təxmin edir və x dəyişənindən y dəyərlərini proqnozlaşdırmaq üçün daha da istifadə edilə bilər. Aydındır ki, əslində dəyişənlər arasında heç bir əlaqə olmasa və ya əlaqə qeyri-xətti olsa belə, OLS yenə də “ən yaxşı” xətti seçəcəkdir. Beləliklə, ən kiçik kvadratlar metodu dəyişənlər arasında real əlaqənin olması haqqında heç nə demir, metod sadəcə olaraq belə funksiya parametrlərini seçməyə imkan verir. a Və b , bunun üçün yuxarıdakı ifadə minimaldır.

Çox mürəkkəb olmayan riyazi əməliyyatları yerinə yetirməklə (daha ətraflı məlumat üçün bax) parametrləri hesablaya bilərsiniz a Və b :

Düsturdan göründüyü kimi, parametr a kovariasiya nisbətini təmsil edir və buna görə də parametri hesablamaq üçün MS EXCEL-də A Aşağıdakı düsturlardan istifadə edə bilərsiniz (bax Xətti vərəq nümunə faylı):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) və ya

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

Həm də parametri hesablamaq üçün A = formulasından istifadə edə bilərsiniz TƏKİL(C26:C45;B26:B45). Parametr üçün b = düsturundan istifadə edin AYAQ(C26:C45;B26:B45) .

Nəhayət, LINEST() funksiyası hər iki parametri birdən hesablamağa imkan verir. Formula daxil etmək üçün LİST(C26:C45;B26:B45) Ardıcıl 2 xana seçib klikləmək lazımdır CTRL + SHIFT + ENTER(haqqında məqaləyə baxın). Dəyər sol xanada qaytarılacaq A , sağda - b .

Qeyd: Daxiletmə ilə qarışmamaq üçün massiv düsturlarıəlavə olaraq INDEX() funksiyasından istifadə etməli olacaqsınız. Formula = İNDEKS(SƏT(C26:C45,B26:B45),1) və ya sadəcə = LİST(C26:C45;B26:B45) xəttin yamacından məsul olan parametri qaytaracaq, yəni. A . Formula = İNDEKS(SƏT(C26:C45,B26:B45),2) xəttin Y oxu ilə kəsişməsindən məsul olan parametri qaytaracaq, yəni. b .

Parametrləri hesablayaraq, səpələnmə diaqramı müvafiq xətti çəkə bilərsiniz.

Ən kiçik kvadratlar metodundan istifadə edərək düz xətti çəkməyin başqa bir yolu qrafik alətidir Trend xətti. Bunu etmək üçün diaqramı seçin, menyudan seçin Layout nişanı, V qrup təhlili basın Trend xətti, sonra Xətti yaxınlaşma .

Dialoq qutusunda "tənliyi diaqramda göstər" qutusunu qeyd etməklə, yuxarıda tapılan parametrlərin diaqramdakı dəyərlərə uyğun olduğundan əmin ola bilərsiniz.

Qeyd: Parametrlərin uyğun olması üçün diaqram növü olmalıdır. Məsələ ondadır ki, diaqram qurarkən Cədvəl X oxu dəyərləri istifadəçi tərəfindən müəyyən edilə bilməz (istifadəçi yalnız nöqtələrin yerləşdiyi yerə təsir etməyən etiketləri təyin edə bilər). X qiymətlərinin əvəzinə 1 ardıcıllığı istifadə olunur; 2; 3; ... (kateqoriyaların nömrələnməsi üçün). Buna görə də, əgər inşa etsəniz trend xətti tip diaqramında Cədvəl, onda X-in faktiki dəyərləri əvəzinə bu ardıcıllığın dəyərləri istifadə olunacaq, bu da səhv nəticəyə gətirib çıxaracaq (əlbəttə ki, X-in faktiki dəyərləri 1-ci ardıcıllıqla üst-üstə düşmürsə; 2; 3; ...).

Təxmini təmsil etməyə imkan verdiyi üçün bir çox istifadəsi var verilmiş funksiya digərləri daha sadədir. LSM müşahidələrin emalında son dərəcə faydalı ola bilər və təsadüfi səhvləri ehtiva edən digərlərinin ölçmə nəticələrinə əsasən bəzi kəmiyyətləri qiymətləndirmək üçün fəal şəkildə istifadə olunur. Bu yazıda siz Excel-də ən kiçik kvadratların hesablamalarını necə həyata keçirəcəyinizi öyrənəcəksiniz.

Müəyyən bir nümunə ilə problemin ifadəsi

Tutaq ki, X və Y iki göstəricisi var. Üstəlik, Y X-dən asılıdır. OLS bizi reqressiya təhlili baxımından maraqlandırdığından (Excel-də onun metodları daxili funksiyalardan istifadə etməklə həyata keçirilir), biz dərhal aşağıdakıları nəzərə almalıyıq. konkret problem.

Beləliklə, X ilə ölçülən ərzaq mağazasının pərakəndə satış sahəsi olsun kvadrat metr, və Y milyonlarla rublla müəyyən edilmiş illik dövriyyədir.

Mağazanın bu və ya digər pərakəndə satış yeri olduqda onun hansı dövriyyəyə (Y) malik olacağı barədə proqnoz vermək tələb olunur. Aydındır ki, Y = f (X) funksiyası artır, çünki hipermarket tövlədən daha çox mal satır.

Proqnoz üçün istifadə olunan ilkin məlumatların düzgünlüyü haqqında bir neçə söz

Tutaq ki, n mağaza üçün verilənlərdən istifadə etməklə qurulmuş cədvəlimiz var.

görə riyazi statistika, ən azı 5-6 obyekt üzrə məlumatlar tədqiq edilərsə, nəticələr az-çox düzgün olacaqdır. Bundan əlavə, "anomal" nəticələr istifadə edilə bilməz. Xüsusilə, elit kiçik bir butik "masmarket" sinfinə aid böyük pərakəndə satış məntəqələrinin dövriyyəsindən bir neçə dəfə çox olan dövriyyəyə malik ola bilər.

Metodun mahiyyəti

Cədvəl məlumatları M 1 (x 1, y 1), ... M n (x n, y n) nöqtələri şəklində Kartezyen müstəvisində təsvir edilə bilər. İndi məsələnin həlli M 1, M 2, .. M n nöqtələrinə mümkün qədər yaxın keçən qrafikə malik olan y = f (x) yaxınlaşma funksiyasının seçilməsinə qədər azaldılacaqdır.

Əlbəttə ki, bir polinom istifadə edə bilərsiniz yüksək dərəcə, lakin bu seçim yalnız həyata keçirmək çətin deyil, həm də sadəcə səhvdir, çünki aşkar edilməli olan əsas tendensiyanı əks etdirməyəcəkdir. Ən ağlabatan həll eksperimental məlumatları, daha dəqiq desək, a və b əmsallarını ən yaxşı təxmin edən y = ax + b düz xəttini axtarmaqdır.

Dəqiqliyin qiymətləndirilməsi

İstənilən yaxınlaşma ilə onun düzgünlüyünü qiymətləndirmək xüsusi əhəmiyyət kəsb edir. X i nöqtəsi üçün funksional və eksperimental dəyərlər arasındakı fərqi (sapma) e i ilə işarə edək, yəni e i = y i - f (x i).

Aydındır ki, yaxınlaşmanın düzgünlüyünü qiymətləndirmək üçün sapmaların cəmindən istifadə edə bilərsiniz, yəni X-in Y-dən asılılığının təxmini təsviri üçün düz xətt seçərkən, ən kiçik dəyəri olan birinə üstünlük verməlisiniz. baxılan bütün məqamlarda cəm e i. Ancaq hər şey o qədər də sadə deyil, çünki müsbət sapmalarla yanaşı, mənfi olanlar da olacaq.

Problem sapma modullarından və ya onların kvadratlarından istifadə etməklə həll edilə bilər. Sonuncu üsul ən çox istifadə olunur. O cümlədən bir çox sahələrdə istifadə olunur reqressiya təhlili(Excel-də onun həyata keçirilməsi iki daxili funksiyadan istifadə etməklə həyata keçirilir) və effektivliyini çoxdan sübut etmişdir.

Ən kiçik kvadrat üsulu

Excel, bildiyiniz kimi, seçilmiş diapazonda yerləşən bütün dəyərlərin dəyərlərini hesablamağa imkan verən daxili AutoSum funksiyasına malikdir. Beləliklə, bizə ifadənin qiymətini hesablamağa heç nə mane olmayacaq (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Riyazi qeydlərdə bu belə görünür:

Əvvəlcə düz xəttdən istifadə edərək təqribən qərar verildiyi üçün bizdə:

Beləliklə, X və Y kəmiyyətlərinin xüsusi asılılığını ən yaxşı təsvir edən düz xəttin tapılması vəzifəsi iki dəyişənin funksiyasının minimumunun hesablanmasına gəlir:

Bunun üçün yeni a və b dəyişənlərinə görə qismən törəmələri sıfıra bərabərləşdirməli və formanın 2 naməlumlu iki tənlikdən ibarət primitiv sistemi həll etməlisiniz:

2-yə bölmə və cəmlərin manipulyasiyası daxil olmaqla bəzi sadə çevrilmələrdən sonra əldə edirik:

Məsələn, Kramer metodundan istifadə edərək, müəyyən a * və b * əmsalları olan stasionar nöqtəni əldə edirik. Bu minimumdur, yəni müəyyən bir sahə üçün mağazanın hansı dövriyyəyə malik olacağını proqnozlaşdırmaq üçün sözügedən nümunə üçün reqressiya modeli olan y = a * x + b * düz xətti uyğun gəlir. Əlbəttə ki, bu, dəqiq nəticə tapmağa imkan verməyəcək, ancaq mağaza krediti ilə müəyyən bir sahənin alınmasının fayda verib-verməyəcəyi barədə fikir əldə etməyə kömək edəcək.

Excel-də ən kiçik kvadratları necə tətbiq etmək olar

Excel ən kiçik kvadratlardan istifadə edərək dəyərləri hesablamaq funksiyasına malikdir. Onun aşağıdakı forması var: “TREND” (məlum Y dəyərləri; məlum X dəyərləri; yeni X dəyərləri; sabit). Excel-də OLS-nin hesablanması düsturunu cədvəlimizə tətbiq edək.

Bunun üçün Excel-də ən kiçik kvadratlar üsulu ilə hesablamanın nəticəsinin göstərilməli olduğu xanaya “=” işarəsini daxil edin və “TREND” funksiyasını seçin. Açılan pəncərədə müvafiq sahələri dolduraraq vurğulayın:

• Y üçün məlum dəyərlər diapazonu (in bu halda ticarət dövriyyəsi haqqında məlumatlar);
• diapazon x 1 , …x n , yəni pərakəndə satış sahəsinin ölçüsü;
• x-nin həm məlum, həm də naməlum dəyərləri, bunun üçün dövriyyənin ölçüsünü tapmaq lazımdır (iş vərəqindəki yerləri haqqında məlumat üçün aşağıya baxın).

Bundan əlavə, formula "Const" məntiqi dəyişənini ehtiva edir. Müvafiq sahəyə 1 daxil etsəniz, bu, b = 0 olduğunu nəzərə alaraq hesablamaları aparmağınız deməkdir.

Birdən çox x dəyəri üçün proqnozu tapmaq lazımdırsa, düsturu daxil etdikdən sonra "Enter" düyməsini basmamalısınız, ancaq klaviaturada "Shift" + "Control" + "Enter" birləşməsini yazmalısınız.

Bəzi xüsusiyyətlər

Reqressiya təhlili hətta dummies üçün də əlçatan ola bilər. Naməlum dəyişənlər massivinin dəyərini proqnozlaşdırmaq üçün Excel düsturu - TREND - hətta ən kiçik kvadratlar haqqında eşitməyənlər tərəfindən də istifadə edilə bilər. Onun işinin bəzi xüsusiyyətlərini bilmək kifayətdir. Xüsusilə:

• Əgər y dəyişəninin məlum dəyərlərinin diapazonunu bir sətirdə və ya sütunda təşkil etsəniz, məlum x qiymətləri olan hər bir sətir (sütun) proqram tərəfindən ayrıca dəyişən kimi qəbul ediləcəkdir.
• TREND pəncərəsində x-i məlum olan diapazon göstərilməyibsə, Excel-də funksiyadan istifadə edərkən proqram onu ​​tam ədədlərdən ibarət massiv kimi qəbul edəcək, onların sayı verilmiş dəyərlərin diapazonuna uyğundur. dəyişən y.
• “Proqnozlaşdırılmış” dəyərlər massivini çıxarmaq üçün trendin hesablanması üçün ifadə massiv düsturu kimi daxil edilməlidir.
• Əgər x-in yeni dəyərləri göstərilməyibsə, TREND funksiyası onları məlum olanlara bərabər hesab edir. Əgər onlar göstərilməyibsə, onda arqument kimi 1-ci massiv götürülür; 2; 3; 4;…, bu artıq diapazona uyğundur verilmiş parametrlər y.
• Yeni x dəyərlərini ehtiva edən diapazon, verilmiş y dəyərlərini ehtiva edən sıra ilə eyni və ya daha çox sətir və ya sütuna malik olmalıdır. Başqa sözlə, müstəqil dəyişənlərə mütənasib olmalıdır.
• Məlum x dəyərləri olan massivdə çox dəyişən ola bilər. Ancaq yalnız birindən danışırıqsa, verilən x və y dəyərləri olan diapazonların mütənasib olması tələb olunur. Bir neçə dəyişən olduqda, verilmiş y dəyərləri olan aralığın bir sütuna və ya bir sıraya uyğun olması lazımdır.

PROQNOZLAMA funksiyası

Bir neçə funksiyadan istifadə etməklə həyata keçirilir. Onlardan biri “PROQNOZLAMA” adlanır. O, “TREND”ə bənzəyir, yəni ən kiçik kvadratlar metodundan istifadə edərək hesablamaların nəticəsini verir. Bununla belə, yalnız Y-nin dəyəri bilinməyən bir X üçün.

İndi Excel-də xətti tendensiyaya görə müəyyən bir göstəricinin gələcək dəyərini proqnozlaşdırmağa imkan verən dummies formullarını bilirsiniz.

Ən kiçik kvadratlar metodu (LSM)

Sistem m xətti tənliklər n naməlum formaya malikdir:

Üç hal mümkündür: m n. Əvvəlki paraqraflarda m=n-nin nəzərdən keçirildiyi hal. Nə zaman m

Əgər m>n və sistem ardıcıldırsa, onda A matrisinin ən azı m - n xətti asılı sətirləri var. Burada n istənilən xətti müstəqil tənlik seçməklə (əgər onlar mövcuddursa) və X = A -1 CV düsturunu tətbiq etməklə, yəni problemi əvvəllər həll edilmiş tənliyə endirməklə həlli tapmaq olar. Bu halda, alınan həll həmişə qalan m - n tənliklərini təmin edəcəkdir.

Bununla belə, kompüterdən istifadə edərkən daha ümumi yanaşmadan - ən kiçik kvadratlar metodundan istifadə etmək daha rahatdır.

Cəbri ən kiçik kvadratlar üsulu

Cəbri ən kiçik kvadratlar üsulu xətti tənliklər sistemlərinin həlli üçün bir üsuldur

Evklid normasını minimuma endirməklə

balta? b? >inf. (1.2)

Eksperimental məlumatların təhlili

Gəlin bəzi təcrübələri nəzərdən keçirək ki, bu zaman anlarda olur

Məsələn, temperatur Q(t) ölçülür. Ölçmə nəticələri bir sıra ilə müəyyən edilsin

Fərz edək ki, təcrübə şərtləri elədir ki, ölçmələr məlum xəta ilə aparılır. Bu hallarda müəyyən çoxhəddən istifadə etməklə Q(t) temperaturun dəyişmə qanunu axtarılır

P(t) = + + + ... +,

naməlum əmsalların müəyyən edilməsi, ..., E(, ...,) dəyərinin bərabərliyi ilə müəyyən edilən mülahizələrdən

gauss cəbri exel yaxınlaşması

minimum dəyəri götürdü. Kvadratların cəmi minimuma endirildiyi üçün bu üsul verilənlərə ən kiçik kvadratların yaxınlaşması adlanır.

P(t)-ni onun ifadəsi ilə əvəz etsək, alarıq

Bir massiv müəyyən etmək vəzifəsini qoyaq ki, dəyər minimal olsun, yəni. Ən kiçik kvadratlar metodundan istifadə edərək massivi təyin edək. Bunun üçün qismən törəmələri sıfıra bərabərləşdiririk:

m × n matrisini daxil etsəniz A = (), i = 1, 2..., m; j = 1, 2, ..., n, harada

I = 1, 2..., m; j = 1, 2, ..., n,

onda yazılı bərabərlik formasını alacaq

Yazılı bərabərliyi matrislərlə əməliyyatlar baxımından yenidən yazaq. Bir matrisin bir sütuna vurulmasının tərifinə görə, biz var

Köçürülən matris üçün oxşar əlaqə bu kimi görünür

Qeydi təqdim edək: Axe vektorunun i-ci komponentini işarə edəcəyik Yazılı matris bərabərliklərinə uyğun olaraq,

Matris şəklində bu bərabərlik kimi yenidən yazmaq olar

A T x=A T B (1.3)

Burada A düzbucaqlı m×n matrisidir. Üstəlik, verilənlərin yaxınlaşması məsələlərində, bir qayda olaraq, m > n. (1.3) tənliyinə normal tənlik deyilir.

Problemi ekvivalent matris formasında yazmaq vektorların Evklid normasından istifadə edərək əvvəldən mümkün idi:

Məqsədimiz bu funksiyanı x-də minimuma endirməkdir. Həll nöqtəsində minimuma çatmaq üçün bu nöqtədə x-ə görə ilk törəmələr sıfıra bərabər olmalıdır. Bu funksiyanın törəmələri

2A T B + 2A T Balta

və buna görə də həll xətti tənliklər sistemini təmin etməlidir

(A T A)x = (A T B).

Bu tənliklərə normal tənliklər deyilir. Əgər A m× n matrisidirsə, onda A>A - n × n matrisdir, yəni. Normal tənliyin matrisi həmişə kvadrat simmetrik matrisdir. Üstəlik, (A>Ax, x) = (Ax, Ax) ? 0.

Şərh. Bəzən (1.3) formalı tənliyin həlli Ax = B sisteminin həlli adlanır, burada A ən kiçik kvadratlar üsulu ilə düzbucaqlı m × n (m > n) matrisidir.

Ən kiçik kvadratlar problemi qrafik olaraq məlumat nöqtələrindən model əyrisinə qədər şaquli məsafələrin minimuma endirilməsi kimi şərh edilə bilər (bax Şəkil 1.1). Bu fikir təxmin edilən bütün səhvlərin müşahidələrdəki səhvlərə uyğun olması fərziyyəsinə əsaslanır. Müstəqil dəyişənlərdə də səhvlər varsa, o zaman verilənlərdən modelə qədər olan Evklid məsafəsini minimuma endirmək daha məqsədəuyğun ola bilər.

Excel-də MNC

Excel-də OLS-in tətbiqi üçün aşağıdakı alqoritm bütün ilkin məlumatların artıq məlum olduğunu güman edir. Soldakı sistemin AЧX=B matris tənliyinin hər iki tərəfini A T sisteminin köçürülmüş matrisinə vururuq:

A T AX=A T B

Sonra soldakı tənliyin hər iki tərəfini (A T A) -1 matrisinə vururuq. Bu matris varsa, sistem müəyyən edilir. Bunu nəzərə alaraq

(A T A) -1 *(A T A)=E, alırıq

X=(A T A) -1 A T B.

Alınan matris tənliyi m>n üçün n naməlum olan m xətti tənliklər sisteminin həllidir.

Yuxarıdakı alqoritmin tətbiqini nəzərdən keçirək konkret misal.

Misal. Sistemi həll etmək lazım olsun

Excel-də bu problem üçün düstur ekranı rejimində həll vərəqi belə görünür:

Hesablama nəticələri:

Tələb olunan X vektoru E11:E12 diapazonunda yerləşir.

Verilmiş xətti tənliklər sistemini həll edərkən aşağıdakı funksiyalardan istifadə edilmişdir:

1. MOBR - qaytarır tərs matris massivdə saxlanılan matris üçün.

Sintaksis: MOBR(massiv).

Massiv bərabər sayda sətir və sütundan ibarət rəqəmli massivdir.

2. MULTIPULT - matrislərin hasilini qaytarır (matrislər massivlərdə saxlanılır). Nəticə massiv1 ilə eyni sayda sətir və massiv2 ilə eyni sayda sütun olan massivdir.

Sintaksis: MULTIPLE(massiv1,massiv2).

Massiv1, massiv2 çoxala bilən massivlərdir.

Massiv diapazonunun yuxarı sol xanasına funksiya daxil etdikdən sonra düsturu olan xanadan başlayaraq massivi seçin, F2 düyməsini basın və sonra CTRL+SHIFT+ENTER düymələrini basın.

3. NƏQLİM - şaquli xanalar toplusunu üfüqi birinə və ya əksinə çevirir. Bu funksiyadan istifadə nəticəsində sətirlərin sayı orijinal massivin sütunlarının sayına, sütunların sayı isə ilkin massivin sətirlərinin sayına bərabər olan massiv yaranır.

4.1. Daxili funksiyalardan istifadə

Hesablama reqressiya əmsalları funksiyasından istifadə etməklə həyata keçirilir

LİST(Dəyərlər_y; x-qiymətləri; Const; statistika),

Dəyərlər_y- y dəyərlərinin massivi,

x-qiymətləri- isteğe bağlı dəyərlər massivi x, əgər massiv X buraxılıb, onun ilə eyni ölçülü massiv (1;2;3;...) olduğu güman edilir. Dəyərlər_y,

Const- sabitin tələb olunub-olunmadığını göstərən məntiqi dəyər b 0-a bərabər idi. Əgər Const mənası var DOĞRU və ya buraxıldı, onda b adi qaydada hesablanır. Əgər mübahisə Const deməli YANLIŞdır b 0 və qiymətlər olduğu qəbul edilir aəlaqənin yerinə yetirilməsi üçün seçilir y=ax.

Statistikaəlavə reqressiya statistikasının qaytarılmasının tələb olunub-olunmadığını göstərən boolean dəyərdir. Əgər mübahisə Statistika mənası var DOĞRU, sonra funksiya LİSTəlavə reqressiya statistikasını qaytarır. Əgər mübahisə Statistika mənası var YALAN və ya buraxılmış, sonra funksiya LİST yalnız əmsalı qaytarır a və daimi b.

Funksiyaların nəticəsi olduğunu xatırlamaq lazımdır LINEST() dəyərlər toplusudur - massiv.

Hesablama üçün korrelyasiya əmsalı funksiyasından istifadə edilir

CORREL(Massiv 1;Massiv 2),

korrelyasiya əmsalının dəyərlərini qaytarmaq, burada Massiv 1- dəyərlər massivi y, Massiv 2- dəyərlər massivi x. Massiv 1 Və Massiv 2 eyni ölçüdə olmalıdır.

NÜMUNƏ 1. Asılılıq y(x) cədvəldə təqdim olunur. qurmaq reqressiya xətti və hesablayın korrelyasiya əmsalı.

MS Excel vərəqinə dəyərlər cədvəlini daxil edək və səpələnmə qrafiki quraq. İş vərəqi Şəkildə göstərilən formanı alacaq. 2.

Reqressiya əmsallarının dəyərlərini hesablamaq üçün A Və b hüceyrələri seçin A7:B7, Funksiya sehrbazına və kateqoriyasına gedək Statistik funksiya seçin LİST. Şəkildə göstərildiyi kimi görünən dialoq qutusunu dolduraq. 3 və basın tamam.

Nəticədə hesablanmış dəyər yalnız xanada görünəcək A6(Şəkil 4). Dəyərin xanada görünməsi üçün B6 redaktə rejiminə daxil olmalısınız (açar F2), və sonra düymələr kombinasiyasını basın CTRL+SHIFT+ENTER.

Hüceyrədəki korrelyasiya əmsalının dəyərini hesablamaq üçün C6 aşağıdakı formula təqdim edildi:

C7=CORREL(B3:J3;B2:J2).

Reqressiya əmsallarını bilmək A Və b funksiyanın qiymətlərini hesablayaq y=balta+b verildiyi üçün x. Bunun üçün düsturu təqdim edirik

B5=$A$7*B2+$B$7

və onu diapazona köçürün C5:J5(şək. 5).

Diaqramda reqressiya xəttini çəkək. Qrafikdə eksperimental nöqtələri seçin, sağ klikləyin və əmri seçin İlkin məlumatlar. Görünən dialoq qutusunda (şək. 5) nişanı seçin Sıra və düyməni basın əlavə et. Şəkildə göstərildiyi kimi giriş sahələrini dolduraq. 6 və düyməni basın tamam. Eksperimental məlumat qrafikinə reqressiya xətti əlavə olunacaq. Varsayılan olaraq, onun qrafiki hamarlama xətləri ilə birləşdirilməyən nöqtələr kimi çəkiləcəkdir.

Reqressiya xəttinin görünüşünü dəyişdirmək üçün aşağıdakı addımları yerinə yetirin. Xətti qrafiki təsvir edən nöqtələrə sağ vurun və əmri seçin Diaqram növü və şəkildə göstərildiyi kimi səpilmə diaqramının növünü təyin edin. 7.

Xəttin növü, rəngi və qalınlığı aşağıdakı kimi dəyişdirilə bilər. Diaqramda bir xətt seçin, sağ klikləyin və kontekst menyusunda əmri seçin Məlumat seriyası formatı... Sonra, məsələn, Şəkildə göstərildiyi kimi parametrləri edin. 8.

Bütün çevrilmələr nəticəsində bir qrafik sahədə eksperimental məlumatların qrafikini və reqressiya xəttini əldə edirik (şək. 9).

4.2. Trend xəttinin istifadəsi.

MS Excel-də müxtəlif təxmini asılılıqların qurulması diaqram mülkiyyəti kimi həyata keçirilir - trend xətti.

NÜMUNƏ 2. Təcrübə nəticəsində müəyyən bir cədvəl asılılığı müəyyən edilmişdir.

Təxmini asılılığı seçin və qurun. Cədvəl və seçilmiş analitik asılılıqların qrafiklərini qurun.

Məsələnin həllini aşağıdakı mərhələlərə bölmək olar: ilkin məlumatların daxil edilməsi, səpələnmə qrafikinin qurulması və bu qrafikə trend xəttinin əlavə edilməsi.

Bu prosesi ətraflı nəzərdən keçirək. İş vərəqinə ilkin məlumatları daxil edək və eksperimental məlumatların qrafikini çəkək. Sonra, qrafikdə eksperimental nöqtələri seçin, sağ klikləyin və əmrdən istifadə edin əlavə et l trend xətti(şək. 10).

Görünən dialoq qutusu sizə təxmini asılılıq yaratmağa imkan verir.

Bu pəncərənin birinci nişanı (şək. 11) təxmini asılılığın növünü göstərir.

İkincidə (Şəkil 12) tikinti parametrləri müəyyən edilir:

· təqribən asılılığın adı;

· tərəfindən irəli (geri) proqnoz n vahidlər (bu parametr trend xəttinin neçə vahid irəli (geri) uzadılması lazım olduğunu müəyyən edir);

əyrinin düz xəttlə kəsişmə nöqtəsinin göstərilib-göstərilməməsi y=const;

· diaqramda təxmini funksiyanı göstərmək və ya göstərməmək (diaqramda tənliyi göstərmək seçimi);

· diaqramda standart kənarlaşmanın qiymətinin yerləşdirilməsi və ya qoyulmaması (diaqramda yaxınlaşma etibarlılığının qiymətinin yerləşdirilməsi variantı).

Təxmini asılılıq kimi ikinci dərəcəli çoxhədli seçək (şək. 11) və bu çoxhədmi təsvir edən tənliyi qrafikdə göstərək (şək. 12). Nəticədə diaqram Şəkildə göstərilmişdir. 13.

Eynilə istifadə trend xətləri kimi asılılıqların parametrlərini seçə bilərsiniz

xətti y=a∙x+b,

loqarifmik y=a∙ln(x)+b,

· eksponensial y=a∙e b,

· sakitləşdirici y=a∙x b,

çoxhədli y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d və s., daxil olmaqla, 6-cı dərəcəli çoxhədli qədər,

· xətti filtrasiya.

4.3. Seçimlərin təhlili alətindən istifadə: Həll tapmaq.

Seçimlərin təhlili alətindən istifadə edərək ən kiçik kvadratlar metodundan istifadə edərək funksional əlaqənin parametrlərinin seçilməsinin MS Excel-də həyata keçirilməsi əhəmiyyətli maraq doğurur: Həll axtarın. Bu texnika istənilən növ funksiyanın parametrlərini seçməyə imkan verir. Nümunə olaraq aşağıdakı problemdən istifadə edərək bu imkanı nəzərdən keçirək.

NÜMUNƏ 3. Təcrübə nəticəsində cədvəldə verilmiş z(t) asılılığı alınmışdır

Asılılıq əmsallarını seçin Z(t)=4 +Bt 3 +Ct 2 +Dt+K-daən kiçik kvadratlar üsulu.

Bu problem beş dəyişənli funksiyanın minimumunun tapılması məsələsinə bərabərdir

Optimallaşdırma məsələsinin həlli prosesini nəzərdən keçirək (şək. 14).

Qoy dəyərlər A, IN, İLƏ, D Və TO hüceyrələrdə saxlanılır A7:E7. Funksiyanın nəzəri dəyərlərini hesablayaq Z(t)=4 +Bt 3 +Ct 2 +Dt+K-da verildiyi üçün t(B2:J2). Bunu etmək üçün hüceyrədə B4 funksiyanın dəyərini birinci nöqtəyə daxil edin (xana B2):

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

Gəlin bu düsturu diapazona köçürək C4:J4 və absisləri xanalarda saxlanılan nöqtələrdə funksiyanın gözlənilən qiymətini alın B2:J2.

Hüceyrəyə B5 Eksperimental və hesablanmış nöqtələr arasındakı fərqin kvadratını hesablayan bir düstur təqdim edək:

B5=(B4-B3)^2,

və onu diapazona köçürün C5:J5. Bir hüceyrədə F7 cəmi kvadrat xətanı (10) saxlayacağıq. Bunu etmək üçün formula daxil edin:

F7 = SUM(B5:J5).

Komandadan istifadə edək Service® Həll axtarın və optimallaşdırma problemini məhdudiyyətsiz həll edin. Şəkildə göstərilən dialoq qutusundakı giriş sahələrini müvafiq olaraq dolduraq. 14 və düyməni basın İcra etmək. Həll tapılarsa, Şəkildə göstərilən pəncərə. 15.

Qərar blokunun nəticəsi hüceyrələrə çıxarılacaq A7:E7parametr dəyərləri funksiyaları Z(t)=4 +Bt 3 +Ct 2 +Dt+K-da. Hüceyrələrdə B4:J4 alırıq gözlənilən funksiya dəyəri başlanğıc nöqtələrində. Bir hüceyrədə F7 saxlanacaq ümumi kvadrat xəta.

Siz diapazon seçərək bir qrafik sahəsində eksperimental nöqtələri və quraşdırılmış xətti göstərə bilərsiniz B2:J4, zəng edin Diaqram Sihirbazı, sonra isə yaranan qrafiklərin görünüşünü formatlaşdırın.

düyü. 17 hesablamalar aparıldıqdan sonra MS Excel iş vərəqini göstərir.