Excel yamac enerjisində ən kiçik kvadratlar üsulu. Excel-də ən kiçik kvadratlar metodu

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 tikirsinizsə 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; ...).

Yaxşı, iş yerində yoxlamaya bildirdik, məqalə konfrans üçün evdə yazılmışdı - indi blogda yaza bilərik. Məlumatlarımı emal edərkən başa düşdüm ki, Excel-də çox gözəl və lazımlı bir əlavə haqqında yazmağa kömək edə bilmərəm. Beləliklə, məqalə bu xüsusi əlavəyə həsr olunacaq və mən istifadə nümunəsindən istifadə edərək bu barədə sizə məlumat verəcəyəm ən kiçik kvadratlar üsulu(LSM) eksperimental məlumatları təsvir edərkən naməlum tənlik əmsallarını axtarmaq üçün.

“Həll axtarışı” əlavəsini necə aktivləşdirmək olar

Əvvəlcə bu əlavəni necə aktivləşdirəcəyimizi anlayaq.

1. “Fayl” menyusuna keçin və “Excel Seçimləri”ni seçin

2. Görünən pəncərədə “Search for a solution” seçin və “get” düyməsini basın.

3. Növbəti pəncərədə “həll üçün axtarış”ın yanındakı qutuyu qeyd edin və “OK” düyməsini basın.

4. Əlavə aktivləşdirildi - indi onu "Məlumat" menyusunda tapmaq olar.

Ən kiçik kvadrat üsulu

İndi qısaca haqqında Ən kiçik kvadratlar metodu (LSM) və harada istifadə oluna bilər.

Tutaq ki, biz bir növ eksperiment apardıqdan sonra X dəyərinin Y dəyərinə təsirini öyrənmişik.

Biz bu təsiri riyazi olaraq təsvir etmək istəyirik ki, sonra bu düsturdan istifadə edək və bilək ki, X-in qiymətini bu qədər dəyişsək, Y-nin belə-filan qiymətini alacağıq...

Çox sadə bir nümunə götürəcəyəm (şəklə bax).

Nöqtələrin düz bir xəttdə bir-birinin ardınca yerləşdiyi heç bir ağıl deyil və buna görə də asılılığımızın təsvir olunduğunu etibarlı şəkildə güman edirik. xətti funksiya y=kx+b. Eyni zamanda, tam əminik ki, X sıfıra bərabər olduqda, Y-nin dəyəri də sıfıra bərabərdir. Bu o deməkdir ki, asılılığı təsvir edən funksiya daha da sadə olacaq: y=kx (məktəb kurrikulumini xatırlayın).

Ümumiyyətlə, k əmsalını tapmalıyıq. Biz bununla edəcəyik MNC “həll axtarışı” əlavəsindən istifadə etməklə.

Metod ondan ibarətdir ki, (burada - diqqət: bu barədə düşünmək lazımdır) eksperimental olaraq əldə edilmiş və müvafiq hesablanmış dəyərlər arasındakı fərqlərin kvadratlarının cəmi minimaldır. Yəni X1=1 faktiki ölçülmüş dəyər Y1=4.6 olduqda və hesablanmış y1=f (x1) 4-ə bərabər olduqda, fərqin kvadratı (y1-Y1)^2=(4-4.6)^ olacaqdır. 2=0,36 . Aşağıdakılarla eynidir: X2=2 olduqda, Y2-nin faktiki ölçülmüş dəyəri=8,1 və hesablanmış y2 8 olduqda, fərqin kvadratı (y2-Y2)^2=(8-8,1)^2 olacaqdır. =0,01. Və bütün bu kvadratların cəmi mümkün qədər kiçik olmalıdır.

Beləliklə, LSM və istifadə ilə bağlı təlimə başlayaq Excel əlavələri "həll üçün axtarış" .

Həll tapmaq üçün əlavənin tətbiqi

1. Əgər “həll üçün axtarış” əlavəsini aktiv etməmisinizsə, o zaman nöqtəyə qayıdın "Həll axtarışı" əlavəsini necə aktivləşdirmək və onu yandırmaq olar 🙂

2. A1 xanasına “1” dəyərini daxil edin. Bu vahid y=kx funksional əlaqəmizin (k) əmsalının real dəyərinə ilk yaxınlaşma olacaq.

3. B sütununda X parametrinin qiymətləri, C sütununda Y parametrinin qiymətləri var. D sütununun xanalarına formula daxil edirik: “əmsal k X dəyərinə vurulur. ” Məsələn, D1 xanasına “=A1*B1”, D2 xanasına “=A1*B2” və s.

4. Hesab edirik ki, k əmsalı birə bərabərdir və f (x)=y=1*x funksiyası bizim həllimizə ilk yaxınlaşmadır. Y-nin ölçülmüş dəyərləri ilə y=1*x düsturu ilə hesablananlar arasındakı kvadrat fərqlərin cəmini hesablaya bilərik. Bütün bunları biz uyğun xana istinadlarını formulaya daxil etməklə əl ilə edə bilərik: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... və s. Sonda biz səhv edin və çox vaxt itirdiyimizi anlayın. Excel-də kvadrat fərqlərin cəmini hesablamaq üçün bizim üçün hər şeyi edəcək xüsusi bir "SUMQUARRENT" düsturu var. Onu A2 xanasına daxil edin və təyin edin. ilkin məlumatlar: ölçülmüş Y dəyərlərinin diapazonu (sütun C) və hesablanmış Y dəyərlərinin diapazonu (sütun D).

4. Kvadratların fərqlərinin cəmi hesablandı - indi "Məlumatlar" sekmesine keçin və "Həll üçün axtarış" seçin.

5. Görünən menyuda dəyişdiriləcək xana kimi A1 xanasını (k əmsalı olan) seçin.

6. Hədəf olaraq A2 xanasını seçin və “minimum dəyərə bərabər təyin edin” şərtini təyin edin. Xatırlayırıq ki, bu, hesablanmış və ölçülmüş dəyərlər arasındakı fərqlərin kvadratlarının cəmini hesabladığımız xanadır və bu məbləğ minimal olmalıdır. "İcra et" düyməsini basın.

7. k əmsalı seçilmişdir. İndi hesablanmış dəyərlərin ölçülənlərə çox yaxın olduğunu yoxlaya bilərsiniz.

P.S.

Ümumiyyətlə, əlbəttə ki, Excel-də eksperimental məlumatları təxmin etmək üçün xətti, eksponensial, güc və polinom funksiyalarından istifadə edərək məlumatları təsvir etməyə imkan verən xüsusi vasitələr var, buna görə də tez-tez olmadan edə bilərsiniz. "həll üçün axtarış" əlavələri. Mən özümdə bütün bu yaxınlaşma üsulları haqqında danışdım, ona görə də maraqlanırsınızsa, baxın. Ancaq bəzi ekzotik funksiyaya gəldikdə bir naməlum əmsalla və ya optimallaşdırma problemləri, sonra burada üst quruluş daha yaxşı vaxt gələ bilməzdi.

Həll axtarışı əlavəsi digər tapşırıqlar üçün istifadə edilə bilər, əsas odur ki, mahiyyəti başa düşək: dəyər seçdiyimiz bir xana var və naməlum parametrin seçilməsi şərtinin göstərildiyi bir hədəf xana var.
Hamısı budur! Növbəti məqalədə sizə bir tətil haqqında bir nağıl danışacağam ki, məqalənin dərcini qaçırmamaq üçün,

Onun bir çox tətbiqi var, çünki verilmiş funksiyanın digər sadə funksiyalar tərəfindən təxmini təmsil olunmasına imkan verir. 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 kvadrat metrlə ölçülən ərzaq mağazasının pərakəndə satış sahəsi, Y isə milyonlarla rublla ölçülən illik dövriyyəsi olsun.

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, yüksək dərəcəli polinomdan istifadə edə bilərsiniz, 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, bir çox sahələrdə, o cümlədən reqressiya təhlilində istifadə olunur (iki daxili funksiyadan istifadə etməklə Excel-də 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 (bu halda ticarət dövriyyəsi üçün 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 müəyyən edilmiş y parametrləri ilə diapazona uyğundur.
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 qurmaq üçün riyazi prosedurdur xətti tənlik iki sıra nömrələr dəstinə ən çox uyğun gələn . Bu metoddan istifadənin məqsədi ümumi kvadrat səhvini minimuma endirməkdir. Excel-də istifadə edə biləcəyiniz alətlər var bu üsul hesablamalar zamanı. Bunun necə edildiyini anlayaq.

Ən kiçik kvadratlar metodu (LSM) bir dəyişənin digərindən asılılığının riyazi təsviridir. Proqnozlaşdırma üçün istifadə edilə bilər.

Həll tap əlavəsinin aktivləşdirilməsi

Excel-də MNC-dən istifadə etmək üçün əlavəni aktivləşdirməlisiniz "Həll tapmaq", defolt olaraq qeyri-aktivdir.

İndi funksiya Həll tapmaq Excel-də aktivləşdirilir və onun alətləri lentdə görünür.

Problemin şərtləri

LSM-in istifadəsini xüsusi bir nümunə ilə təsvir edək. İki sıra nömrələrimiz var x Və y , ardıcıllığı aşağıdakı şəkildə göstərilmişdir.

Bu asılılıq ən dəqiq funksiya ilə təsvir edilə bilər:

Eyni zamanda məlumdur ki, nə vaxt x=0 y həm də bərabərdir 0 . Buna görə də bu tənliyi asılılıq ilə təsvir etmək olar y=nx .

Fərqin kvadratlarının minimum cəmini tapmalıyıq.

Həll

Metodun birbaşa tətbiqinin təsvirinə keçək.

Gördüyünüz kimi, ən kiçik kvadratlar metodunun tətbiqi kifayət qədər mürəkkəb riyazi prosedurdur. Biz bunu sadə bir nümunə ilə hərəkətdə göstərdik, lakin daha mürəkkəb hallar var. Bununla belə, Microsoft Excel alətləri hesablamaları mümkün qədər sadələşdirmək üçün nəzərdə tutulmuşdur.

Ən kiçik kvadratlar metodu (OLS) reqressiya təhlili sahəsinə aiddir. Onun bir çox tətbiqi var, çünki verilmiş funksiyanın digər sadə funksiyalar tərəfindən təxmini təmsil olunmasına imkan verir. 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

Beləliklə, X kvadrat metrlə ölçülən ərzaq mağazasının pərakəndə satış sahəsi, Y isə milyonlarla rublla ölçülən illik dövriyyəsi olsun.

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.

Riyazi statistikaya görə, ən azı 5-6 obyekt haqqında məlumatlar araşdırılarsa, nəticələr az-çox düzgün olacaq. 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

Dəqiqliyin qiymətləndirilməsi

Ən kiçik kvadrat üsulu

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:

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

Y üçün məlum dəyərlər diapazonu (bu halda ticarət dövriyyəsi üçün 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.

Bəzi xüsusiyyətlər

Ə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 müəyyən edilmiş y parametrləri ilə diapazona uyğundur.
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ı

Excel-də reqressiya təhlili 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.