Eksperimental məlumatların yaxınlaşması eksperimental olaraq əldə edilmiş məlumatların düyün nöqtələrində orijinal dəyərlərlə (təcrübə və ya təcrübə zamanı əldə edilən məlumatlar) ən yaxından keçən və ya üst-üstə düşən analitik funksiya ilə əvəz edilməsinə əsaslanan bir üsuldur. Hal-hazırda analitik funksiyanı təyin etməyin iki yolu var:
Keçən n-dərəcəli interpolyasiya polinomu qurmaqla birbaşa bütün nöqtələr vasitəsilə verilmiş məlumat massivi. IN bu halda yaxınlaşma funksiyası aşağıdakı kimi təmsil olunur: Laqranj şəklində interpolyasiya çoxhədli və ya Nyuton şəklində interpolyasiya çoxhədli.
Keçən n-dərəcəli yaxınlaşan çoxhədli qurmaqla məntəqələrin bilavasitə yaxınlığında verilmiş məlumat massivindən. Beləliklə, yaxınlaşma funksiyası təcrübə zamanı yarana biləcək bütün təsadüfi səs-küyü (və ya səhvləri) hamarlayır: təcrübə zamanı ölçülmüş dəyərlər özlərinə uyğun olaraq dəyişən təsadüfi amillərdən asılıdır. təsadüfi qanunlar(ölçmə və ya alət səhvləri, qeyri-dəqiqlik və ya eksperimental xətalar). Bu halda metoddan istifadə etməklə yaxınlaşma funksiyası təyin edilir ən kiçik kvadratlar.
Ən kiçik kvadrat üsulu(İngilis ədəbiyyatında Ordinary Least Squares, OLS) verilmiş eksperimental məlumat massivindən nöqtələrə ən yaxın məsafədə qurulan təxmini funksiyanın müəyyən edilməsinə əsaslanan riyazi metoddur. İlkin və F(x) yaxınlaşma funksiyalarının yaxınlığı ədədi ölçü ilə müəyyən edilir, yəni: təcrübi məlumatların F(x) yaxınlaşma əyrisindən kvadratik kənarlaşmalarının cəmi ən kiçik olmalıdır.
Ən kiçik kvadratlar üsulu ilə qurulmuş təxmini əyri
Ən kiçik kvadratlar metodundan istifadə olunur:
Tənliklərin sayı naməlumların sayından çox olduqda artıq təyin olunmuş tənlik sistemlərini həll etmək;
Adi vəziyyətdə bir həll tapmaq üçün (əlavə edilmir) qeyri-xətti sistemlər tənliklər;
Bəzi yaxınlaşma funksiyası ilə nöqtə dəyərlərini təxmin etmək.
Ən kiçik kvadratlar metodundan istifadə etməklə yaxınlaşma funksiyası verilmiş eksperimental məlumat massivindən hesablanmış yaxınlaşma funksiyasının kvadratik sapmalarının minimum cəminin şərtindən müəyyən edilir. Ən kiçik kvadratlar metodunun bu kriteriyası aşağıdakı ifadə kimi yazılır:
Düyün nöqtələrində hesablanmış yaxınlaşma funksiyasının dəyərləri,
Düyün nöqtələrində verilmiş eksperimental məlumat massivi.
Kvadrat meyar çoxhədli yaxınlaşma funksiyaları ilə yaxınlaşma məsələsinin unikal həllini təmin edən diferensiallıq kimi bir sıra “yaxşı” xüsusiyyətlərə malikdir.
Məsələnin şərtlərindən asılı olaraq, yaxınlaşma funksiyası m dərəcə çoxhədlidir
Təxmini funksiyanın dərəcəsi düyün nöqtələrinin sayından asılı deyil, lakin onun ölçüsü həmişə verilmiş eksperimental məlumat massivinin ölçüsündən (nöqtələrin sayından) az olmalıdır.
∙ Əgər yaxınlaşma funksiyasının dərəcəsi m=1 olarsa, onda cədvəl funksiyasını düz xəttlə (xətti reqressiya) yaxınlaşdırırıq.
∙ Əgər yaxınlaşma funksiyasının dərəcəsi m=2 olarsa, onda cədvəl funksiyasını təxmini edirik. kvadratik parabola(kvadrat yaxınlaşma).
∙ Əgər yaxınlaşma funksiyasının dərəcəsi m=3 olarsa, o zaman cədvəl funksiyasını kub parabola (kub yaxınlaşması) ilə yaxınlaşdırırıq.
IN ümumi hal verilmiş üçün m dərəcəyə yaxın çoxhədli qurmaq lazım olduqda cədvəl dəyərləri, bütün düyün nöqtələri üzərində kvadrat sapmaların minimum cəminin şərti aşağıdakı formada yenidən yazılır:
- m dərəcəsinin yaxınlaşan çoxhədlinin naməlum əmsalları;
Göstərilən cədvəl dəyərlərinin sayı.
Bir funksiyanın minimumunun olması üçün zəruri şərt onun naməlum dəyişənlərə münasibətdə qismən törəmələrinin sıfıra bərabər olmasıdır. . Nəticədə alırıq aşağıdakı sistem tənliklər:
Nəticəni çevirək xətti sistem tənliklər: mötərizələri açın və sərbəst şərtləri ifadənin sağ tərəfinə köçürün. Nəticədə yaranan xətti sistem cəbri ifadələr aşağıdakı formada yazılacaq:
Bu xətti cəbri ifadələr sistemi matris şəklində yenidən yazıla bilər:
Nəticə bir sistem oldu xətti tənliklər m+1 naməlumlardan ibarət olan m+1 ölçüsü. Bu sistem xətti məsələlərin həlli üçün istənilən üsuldan istifadə etməklə həll edilə bilər. cəbri tənliklər(məsələn, Qauss üsulu ilə). Həlli nəticəsində yaxınlaşma funksiyasının ilkin verilənlərdən kvadratik sapmalarının minimum cəmini təmin edən yaxınlaşma funksiyasının naməlum parametrləri tapılacaq, yəni. mümkün olan ən yaxşı kvadratik yaxınlaşma. Yadda saxlamaq lazımdır ki, mənbə məlumatının hətta bir dəyəri dəyişərsə, bütün əmsallar mənbə məlumatları ilə tamamilə müəyyən edildiyi üçün öz dəyərlərini dəyişəcəkdir.
Mənbə məlumatlarının xətti asılılıqla yaxınlaşması
(xətti reqressiya)
Nümunə olaraq, formada verilmiş yaxınlaşma funksiyasının təyin edilməsi texnikasını nəzərdən keçirək xətti asılılıq. Ən kiçik kvadratlar metoduna uyğun olaraq, kvadratdan kənara çıxanların cəminin minimumunun şərti aşağıdakı formada yazılır:
Cədvəl qovşaqlarının koordinatları;
Xətti asılılıq kimi təyin olunan yaxınlaşma funksiyasının naməlum əmsalları.
Funksiyanın minimumunun olması üçün zəruri şərt onun naməlum dəyişənlərə münasibətdə qismən törəmələrinin sıfıra bərabər olmasıdır. Nəticədə aşağıdakı tənliklər sistemini əldə edirik:
Əldə olunan xətti tənliklər sistemini çevirək.
Yaranan xətti tənliklər sistemini həll edirik. Analitik formada yaxınlaşma funksiyasının əmsalları aşağıdakı kimi müəyyən edilir (Kramer metodu):
Bu əmsallar verilmiş cədvəl qiymətlərindən (təcrübə məlumatları) yaxınlaşma funksiyasının kvadratlarının cəmini minimuma endirmək meyarına uyğun olaraq xətti yaxınlaşma funksiyasının qurulmasını təmin edir.
Ən kiçik kvadratlar metodunun həyata keçirilməsi alqoritmi
1. İlkin məlumatlar:
Ölçmələrin sayı N olan eksperimental məlumat massivi müəyyən edilmişdir
Təqribən çoxhədlinin dərəcəsi (m) müəyyən edilir
2. Hesablama alqoritmi:
2.1. Ölçüləri olan tənliklər sisteminin qurulması üçün əmsallar müəyyən edilir
Tənliklər sisteminin əmsalları ( sol tərəf tənliklər)
- tənliklər sisteminin kvadrat matrisinin sütun nömrəsinin indeksi
Xətti tənliklər sisteminin sərbəst şərtləri ( sağ hissə tənliklər)
- tənliklər sisteminin kvadrat matrisinin sıra nömrəsinin indeksi
2.2. Ölçü ilə xətti tənliklər sisteminin formalaşması.
2.3. m dərəcəyə yaxın olan çoxhədlinin naməlum əmsallarını təyin etmək üçün xətti tənliklər sisteminin həlli.
2.4.Bütün düyün nöqtələrində təxmini çoxhədlinin ilkin qiymətlərdən kvadrat sapmalarının cəminin təyini
Kvadrat sapmaların cəminin tapılan dəyəri mümkün olan minimumdur.
Digər funksiyalardan istifadə edərək yaxınlaşma
Qeyd etmək lazımdır ki, ən kiçik kvadratlar metoduna uyğun olaraq mənbə məlumatları yaxınlaşdırarkən, bəzən yaxınlaşma funksiyası kimi loqarifmik funksiya istifadə olunur, eksponensial funksiya və güc funksiyası.
Loqarifmik yaxınlaşma
Təxmini funksiyanın formanın loqarifmik funksiyası ilə verildiyi halı nəzərdən keçirək:
Ən kiçik kvadratlar metodunun mahiyyəti ondan ibarətdir zaman və ya məkanda hər hansı təsadüfi hadisənin inkişaf meylini ən yaxşı təsvir edən trend modelinin parametrlərinin tapılmasında (trend bu inkişafın meylini xarakterizə edən xəttdir). Ən kiçik kvadratlar metodunun (LSM) vəzifəsi yalnız bəzi trend modelini tapmaq deyil, ən yaxşı və ya optimal modeli tapmaqdan ibarətdir. Müşahidə olunan faktiki dəyərlər və müvafiq hesablanmış trend dəyərləri arasında kvadrat sapmaların cəmi minimal (ən kiçik) olarsa, bu model optimal olacaqdır:
Harada - standart sapma müşahidə olunan faktiki dəyər arasında
və müvafiq hesablanmış trend dəyəri,
Tədqiq olunan fenomenin faktiki (müşahidə olunan) dəyəri,
Trend modelinin hesablanmış dəyəri,
Tədqiq olunan fenomenin müşahidələrinin sayı.
MNC öz-özünə olduqca nadir hallarda istifadə olunur. Bir qayda olaraq, çox vaxt korrelyasiya tədqiqatlarında yalnız zəruri texniki texnika kimi istifadə olunur. Yadda saxlamaq lazımdır ki, MNC-nin informasiya bazası yalnız etibarlı ola bilər statistik seriyalar, və müşahidələrin sayı 4-dən az olmamalıdır, əks halda OLS hamarlaşdırma prosedurları sağlam düşüncəni itirə bilər.
MNC alət dəsti aşağıdakı prosedurlara əsaslanır:
Birinci prosedur. Məlum olur ki, seçilmiş amil-arqument dəyişdikdə nəticə atributunu dəyişmək meyli ümumiyyətlə varmı, yoxsa başqa sözlə, “ arasında əlaqə varmı? saat "Və" X ».
İkinci prosedur. Hansı xəttin (trayektoriyanın) bu tendensiyanı ən yaxşı təsvir edə və ya xarakterizə edə biləcəyi müəyyən edilir.
Üçüncü prosedur.
Misal. Tutaq ki, tədqiq olunan təsərrüfat üzrə günəbaxanın orta məhsuldarlığı haqqında məlumatımız var (cədvəl 9.1).
Cədvəl 9.1
|
Müşahidə nömrəsi |
||||||||||
|
Məhsuldarlıq, c/ha |
Ölkəmizdə günəbaxan istehsalının texnologiya səviyyəsi son 10 ildə faktiki olaraq dəyişməz qaldığından, bu o deməkdir ki, görünür, təhlil edilən dövrdə məhsuldarlığın dəyişməsi hava və iqlim şəraitinin dəyişməsindən çox asılı olub. Bu həqiqətən doğrudurmu?
İlk OLS proseduru. Təhlil edilən 10 il ərzində hava və iqlim şəraitinin dəyişməsindən asılı olaraq günəbaxan məhsuldarlığında dəyişiklik tendensiyası olması haqqında fərziyyə yoxlanılır.
Bu misalda " y "günəbaxan məhsulunu götürmək məsləhətdir və" x » – təhlil edilən dövrdə müşahidə olunan ilin sayı. arasında hər hansı bir əlaqənin olması haqqında fərziyyənin sınaqdan keçirilməsi " x "Və" y "iki yolla edilə bilər: əl ilə və kompüter proqramlarından istifadə etməklə. Əlbəttə, əgər varsa kompüter avadanlığı bu problem özü həll edir. Lakin MNC alətlərini daha yaxşı başa düşmək üçün “ arasında əlaqənin mövcudluğu haqqında fərziyyəni yoxlamaq məsləhətdir. x "Və" y » əl ilə, yalnız qələm və adi kalkulyator əlinizdə olduqda. Belə hallarda, trendin mövcudluğu ilə bağlı fərziyyə ən yaxşı şəkildə təhlil edilən dinamika seriyasının qrafik təsvirinin yeri - korrelyasiya sahəsi ilə vizual olaraq yoxlanılır:
Nümunəmizdəki korrelyasiya sahəsi yavaş-yavaş artan xətt ətrafında yerləşir. Bu, özlüyündə günəbaxan məhsuldarlığında müəyyən dəyişikliklərin mövcudluğundan xəbər verir. Yalnız korrelyasiya sahəsi dairəyə, dairəyə, ciddi şaquli və ya ciddi üfüqi buludlara bənzədikdə və ya xaotik səpələnmiş nöqtələrdən ibarət olduqda hər hansı bir meylin mövcudluğundan danışmaq mümkün deyil. Bütün digər hallarda “ arasında əlaqənin olması haqqında fərziyyə x "Və" y ", və araşdırmaya davam edin.
İkinci OLS proseduru. Təhlil olunan dövr ərzində günəbaxan məhsuldarlığının dəyişmə tendensiyasını hansı xəttin (trayektoriyanın) ən yaxşı şəkildə təsvir edə və ya xarakterizə edə biləcəyi müəyyən edilir.
Kompüter texnologiyanız varsa, optimal tendensiyanın seçilməsi avtomatik olaraq baş verir. Əl ilə emal edərkən, seçim optimal funksiya bir qayda olaraq, vizual olaraq - korrelyasiya sahəsinin yeri ilə həyata keçirilir. Yəni qrafikin növü əsasında empirik tendensiyaya (faktiki trayektoriyaya) ən yaxşı uyğun gələn xəttin tənliyi seçilir.
Məlum olduğu kimi, təbiətdə çox sayda funksional asılılıq var, buna görə də onların kiçik bir hissəsini belə vizual olaraq təhlil etmək olduqca çətindir. Xoşbəxtlikdən, real iqtisadi praktikada əksər münasibətlər ya parabola, ya hiperbola, ya da düz xətt ilə olduqca dəqiq təsvir edilə bilər. Bu baxımdan, ən yaxşı funksiyanı seçmək üçün "əl ilə" seçimi ilə özünüzü yalnız bu üç modellə məhdudlaşdıra bilərsiniz.
|
Hiperbola: |
||
|
|
|
İkinci dərəcəli parabola: 
:
Görmək asandır ki, bizim nümunəmizdə günəbaxan məhsuldarlığının təhlil edilən 10 il ərzində dəyişmə meyli ən yaxşı düz xətt ilə xarakterizə olunur, ona görə də reqressiya tənliyi düz xəttin tənliyi olacaqdır.
Üçüncü prosedur. Parametrlər hesablanır reqressiya tənliyi verilmiş xətti xarakterizə edən və ya başqa sözlə, təsvir edən analitik düstur müəyyən edilir ən yaxşı model trend.
Reqressiya tənliyinin parametrlərinin dəyərlərini tapmaq, bizim vəziyyətimizdə və parametrləri OLS-in əsasını təşkil edir. Bu proses normal tənliklər sisteminin həllinə qədər gedir.
(9.2)
Bu tənliklər sistemi Gauss metodu ilə olduqca asanlıqla həll edilə bilər. Xatırlayaq ki, həll nəticəsində, nümunəmizdə parametrlərin dəyərləri tapılır. Beləliklə, tapılan reqressiya tənliyi aşağıdakı formaya sahib olacaq: 
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, olanına üstünlük verməlisiniz. ən kiçik dəyər bütün nəzərə alınan nöqtələrdə e i cəmləri. 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).
IN riyazi qeyd 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ə, ən yaxşı təsvir edən xətti tapmaq vəzifəsi spesifik asılılıq X və Y kəmiyyətləri, 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ğundur. Ə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ü;
- həm məşhur, həm də naməlum dəyərlər x, bunun üçün dövriyyənin ölçüsünü öyrənməlisiniz (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 heç vaxt eşitməyənlər tərəfindən 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 düzürsünüzsə, onda hər bir sətir (sütun) ilə məlum dəyərlər x proqram tərəfindən ayrıca dəyişən kimi qəbul ediləcək.
- TREND pəncərəsi məlum x ilə diapazonu göstərmirsə, o zaman funksiyadan istifadə olunursa Excel proqramı onu tam ədədlərdən ibarət massiv kimi qəbul edəcək, onların sayı y dəyişəninin verilmiş qiymətləri ilə diapazona uyğundur.
- “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.
Misal.
Dəyişənlərin dəyərlərinə dair eksperimental məlumatlar X Və saat cədvəldə verilmişdir.
Onların düzülməsi nəticəsində funksiya əldə edilir 
İstifadə ən kiçik kvadrat üsulu, bu məlumatları xətti asılılıqla təxmin edin y=ax+b(parametrləri tapın A Və b). İki sətirdən hansının (ən kiçik kvadratlar metodu mənasında) eksperimental məlumatları daha yaxşı uyğunlaşdırdığını tapın. Rəsm çəkin.
Ən kiçik kvadratlar metodunun (LSM) mahiyyəti.
Tapşırıq iki dəyişənin funksiyasının yerinə yetirildiyi xətti asılılıq əmsallarını tapmaqdır A Və b
ən kiçik qiyməti alır. Yəni verilmişdir A Və b tapılmış düz xəttdən eksperimental məlumatların kvadrat sapmalarının cəmi ən kiçik olacaqdır. Ən kiçik kvadratlar metodunun bütün nöqtəsi budur.
Beləliklə, nümunənin həlli iki dəyişənli funksiyanın ekstremumunun tapılmasına gəlir.
Əmsalların tapılması üçün düsturların alınması.
İki naməlum olan iki tənlik sistemi tərtib edilir və həll edilir. Funksiyanın qismən törəmələrinin tapılması 
dəyişənlərə görə A Və b, bu törəmələri sıfıra bərabərləşdiririk. 
Yaranan tənliklər sistemini istənilən üsuldan istifadə edərək həll edirik (məsələn əvəzetmə üsulu ilə və ya Kramer üsulu) və ən kiçik kvadratlar metodundan (LSM) istifadə edərək əmsalları tapmaq üçün düsturlar əldə edin. 
verilmiş A Və b funksiyası 
ən kiçik qiyməti alır. Bu faktın sübutu verilir səhifənin sonundakı mətndə aşağıda.
Ən kiçik kvadratların bütün üsulu budur. Parametri tapmaq üçün düstur a cəmləri,,, və parametrləri ehtiva edir n- eksperimental məlumatların miqdarı. Bu məbləğlərin dəyərlərini ayrıca hesablamağı tövsiyə edirik. Əmsal b hesablamadan sonra tapılır a.
Orijinal nümunəni xatırlamağın vaxtı gəldi.
Həll.
Bizim nümunəmizdə n=5. Tələb olunan əmsalların düsturlarına daxil olan məbləğlərin hesablanmasının rahatlığı üçün cədvəli doldururuq. 
Cədvəlin dördüncü sətirindəki dəyərlər 2-ci sətrin dəyərlərini hər bir nömrə üçün 3-cü sətirin dəyərlərinə vurmaqla əldə edilir. i.
Cədvəlin beşinci cərgəsindəki dəyərlər hər bir nömrə üçün 2-ci sətirdəki dəyərlərin kvadratı ilə əldə edilir. i.
Cədvəlin son sütunundakı dəyərlər sətirlər arasında olan dəyərlərin cəmidir.
Əmsalları tapmaq üçün ən kiçik kvadratlar metodunun düsturlarından istifadə edirik A Və b. Cədvəlin son sütunundan müvafiq dəyərləri onlara əvəz edirik: 
Beləliklə, y = 0,165x+2,184- istədiyiniz təxmini düz xətt.
Sətirlərdən hansının olduğunu tapmaq qalır y = 0,165x+2,184 və ya 
ilkin məlumatları daha yaxşı təxmin edir, yəni ən kiçik kvadratlar metodundan istifadə edərək təxmin edir.
Ən kiçik kvadratlar metodunun səhv qiymətləndirilməsi.
Bunu etmək üçün, bu sətirlərdən orijinal məlumatların kvadrat sapmalarının cəmini hesablamalısınız 
Və 
, daha kiçik bir dəyər ən kiçik kvadratlar metodu mənasında orijinal məlumatı daha yaxşı təxmin edən xəttə uyğun gəlir. 
Ondan bəri, sonra düz y = 0,165x+2,184 orijinal məlumatlara daha yaxşı yaxınlaşır.
Ən kiçik kvadratlar (LS) metodunun qrafik təsviri.
Qrafiklərdə hər şey aydın görünür. Qırmızı xətt tapılan düz xəttdir y = 0,165x+2,184, mavi xəttdir 
, çəhrayı nöqtələr orijinal məlumatlardır.
Təcrübədə, müxtəlif prosesləri modelləşdirərkən - xüsusən də iqtisadi, fiziki, texniki, sosial - müəyyən sabit nöqtələrdə onların məlum dəyərlərindən funksiyaların təxmini qiymətlərinin hesablanmasının bu və ya digər üsullarından geniş istifadə olunur.
Bu cür funksiyaların yaxınlaşması problemi tez-tez yaranır:
təcrübə nəticəsində əldə edilmiş cədvəl məlumatlarından istifadə etməklə tədqiq olunan prosesin xarakterik kəmiyyətlərinin qiymətlərinin hesablanması üçün təxmini düsturlar qurarkən;
ədədi inteqrasiyada, diferensiasiyada, həllində diferensial tənliklər və s.;
zəruri hallarda, nəzərdən keçirilən intervalın aralıq nöqtələrində funksiyaların qiymətlərini hesablayın;
nəzərdə tutulan intervaldan kənar bir prosesin xarakterik kəmiyyətlərinin dəyərlərini təyin edərkən, xüsusən də proqnozlaşdırarkən.
Cədvəllə müəyyən edilmiş müəyyən prosesi modelləşdirmək üçün ən kiçik kvadratlar metodu əsasında bu prosesi təqribən təsvir edən funksiya qursaq, o, yaxınlaşma funksiyası (reqressiya) adlanacaq və yaxınlaşan funksiyaların özünün qurulması məsələsi adlanacaq. yaxınlaşma problemi.
Bu məqalədə bu tip məsələlərin həlli üçün MS Excel paketinin imkanlarından bəhs edilir, əlavə olaraq, cədvəlləşdirilmiş funksiyalar üçün reqressiyaların qurulması (yaradılması) üsulları və üsulları təqdim olunur (bu, reqressiya təhlilinin əsasını təşkil edir).
Excel reqressiya yaratmaq üçün iki seçimə malikdir.
Seçilmiş reqressiyaların əlavə edilməsi ( trend xətləri- trend xətləri) tədqiq olunan prosesin xarakteristikası üçün verilənlər cədvəli əsasında qurulmuş diaqrama (yalnız qurulmuş diaqram olduqda mümkündür);
Excel iş vərəqinin daxili statistik funksiyalarından istifadə edərək, reqressiyaları (trend xətləri) birbaşa mənbə verilənlər cədvəlindən əldə etməyə imkan verir.
Qrafikə trend xətlərinin əlavə edilməsi
Prosesi təsvir edən və diaqramla təmsil olunan verilənlər cədvəli üçün Excel-də sizə imkan verən effektiv reqressiya təhlili aləti var:
ən kiçik kvadratlar metodu əsasında qurmaq və tədqiq olunan prosesi müxtəlif dəqiqlik dərəcələri ilə modelləşdirən beş növ reqressiyanı diaqrama əlavə etmək;
qurulmuş reqressiya tənliyini diaqrama əlavə edin;
seçilmiş reqressiyanın diaqramda göstərilən məlumatlara uyğunluq dərəcəsini müəyyən etmək.
Diaqram məlumatlarına əsaslanaraq, Excel tənliklə təyin olunan xətti, çoxhədli, loqarifmik, güc, eksponensial reqressiya növlərini əldə etməyə imkan verir:
y = y(x)
burada x, tez-tez natural ədədlər ardıcıllığının (1; 2; 3; ...) dəyərlərini alan və məsələn, tədqiq olunan prosesin vaxtının (xüsusiyyətlərinin) geri sayımını yaradan müstəqil dəyişəndir.
1 . Xətti reqressiya dəyərləri sabit sürətlə artan və ya azalan xüsusiyyətlərin modelləşdirilməsi üçün yaxşıdır. Bu, tədqiq olunan proses üçün qurmaq üçün ən sadə modeldir. Tənliyə uyğun olaraq qurulur:
y = mx + b
burada m maillik bucağının tangensidir xətti reqressiya absis oxuna; b - xətti reqressiyanın ordinat oxu ilə kəsişmə nöqtəsinin koordinatı.
2 . Çoxhədli trend xətti bir neçə fərqli ekstremal (maksima və minimum) olan xüsusiyyətləri təsvir etmək üçün faydalıdır. Polinom dərəcəsinin seçimi tədqiq olunan xarakteristikanın ekstremal sayı ilə müəyyən edilir. Beləliklə, ikinci dərəcəli çoxhədli yalnız bir maksimum və ya minimum olan prosesi yaxşı təsvir edə bilər; üçüncü dərəcəli çoxhədli - iki ekstremaldan çox olmayan; dördüncü dərəcəli çoxhədli - üç ekstremaldan çox olmayan və s.
Bu halda, trend xətti tənliyə uyğun olaraq qurulur:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
burada c0, c1, c2,... c6 əmsalları tikinti zamanı qiymətləri təyin olunan sabitlərdir.
3 . Dəyərləri əvvəlcə sürətlə dəyişən və sonra tədricən sabitləşən xüsusiyyətlərin modelləşdirilməsi zamanı loqarifmik trend xətti uğurla istifadə olunur.
y = c ln(x) + b
4 . Tədqiq olunan əlaqənin dəyərləri artım sürətinin daimi dəyişməsi ilə xarakterizə olunarsa, güc qanunu meyl xətti yaxşı nəticələr verir. Belə bir asılılığa misal olaraq avtomobilin vahid sürətlənmiş hərəkətinin qrafikini göstərmək olar. Məlumatda sıfır və ya mənfi dəyərlər varsa, güc trend xəttindən istifadə edə bilməzsiniz.
Tənliyə uyğun olaraq qurulur:
y = c xb
burada b, c əmsalları sabitlərdir.
5 . Məlumatların dəyişmə sürəti davamlı olaraq artdıqda eksponensial trend xəttindən istifadə edilməlidir. Sıfır və ya mənfi dəyərləri ehtiva edən məlumatlar üçün bu cür yaxınlaşma da tətbiq edilmir.
Tənliyə uyğun olaraq qurulur:
y = c ebx
burada b, c əmsalları sabitlərdir.
Trend xəttini seçərkən Excel avtomatik olaraq R2 dəyərini hesablayır, bu da yaxınlaşmanın etibarlılığını xarakterizə edir: daha yaxın dəyər R2 vəhdət təşkil edirsə, trend xətti tədqiq olunan prosesə bir o qədər etibarlı şəkildə yaxınlaşır. Lazım gələrsə, R2 dəyəri həmişə diaqramda göstərilə bilər.
Düsturla müəyyən edilir:
Məlumat seriyasına trend xətti əlavə etmək üçün:
bir sıra məlumatlara əsaslanan qrafiki aktivləşdirin, yəni diaqram sahəsi daxilində klikləyin. Diaqram elementi əsas menyuda görünəcək;
bu elementə kliklədikdən sonra ekranda trend xətti əlavə et əmrini seçməli olduğunuz bir menyu görünəcək.
Eyni hərəkətləri siçan göstəricisini verilənlər seriyasından birinə uyğun olan qrafikin üzərinə aparıb sağ klikləməklə asanlıqla həyata keçirmək olar; Görünən kontekst menyusunda Trend xətti əlavə et əmrini seçin. “Trendline” dialoq qutusu ekranda Type sekmesi açılmış halda görünəcək (şək. 1).
Bundan sonra sizə lazımdır:
Növ sekmesinde tələb olunan trend xətti növünü seçin (Xətti tip standart olaraq seçilir). Polinom növü üçün Dərəcə sahəsində seçilmiş polinomun dərəcəsini göstərin.
1 . Quraşdırılmış seriya sahəsi sözügedən diaqramdakı bütün məlumat seriyalarını sadalayır. Xüsusi məlumat seriyasına trend xətti əlavə etmək üçün onun adını Quraşdırılmış seriyalar sahəsində seçin.
Lazım gələrsə, Parametrlər sekmesine keçərək (Şəkil 2) trend xətti üçün aşağıdakı parametrləri təyin edə bilərsiniz:
təxmini (hamarlanmış) əyri sahəsinin adı sahəsində trend xəttinin adını dəyişdirin.
Proqnoz sahəsində proqnoz üçün dövrlərin sayını (irəli və ya geri) təyin edin;
diaqram sahəsində trend xəttinin tənliyini göstərin, bunun üçün diaqram onay qutusunda tənliyi göstərməlisiniz;
diaqram sahəsində R2 yaxınlaşma etibarlılıq dəyərini göstərin, bunun üçün siz təxmini etibarlılıq dəyərini diaqramda yerləşdirin (R^2) qutusunu aktivləşdirməlisiniz;
trend xəttinin Y oxu ilə kəsişmə nöqtəsini təyin edin, bunun üçün əyrinin Y oxu ilə bir nöqtədə kəsişməsi üçün onay qutusunu aktivləşdirməlisiniz;
Dialoq qutusunu bağlamaq üçün OK düyməsini basın.
Artıq çəkilmiş trend xəttini redaktə etməyə başlamaq üçün üç yol var:
əvvəlcədən trend xəttini seçərək Format menyusundan Seçilmiş trend xətti əmrindən istifadə edin;
kontekst menyusundan trend xəttinin üzərinə sağ klikləməklə çağırılan Format trend xətti əmrini seçin;
trend xəttinə iki dəfə klikləyin.
Ekranda Trend Line Format informasiya qutusu görünəcək (Şəkil 3), üç nişanı ehtiva edir: Görünüş, Növ, Parametrlər və sonuncu ikisinin məzmunu Trend Xətti dialoq qutusunun oxşar nişanları ilə tamamilə üst-üstə düşür (Şəkil 1). -2). Görünüş sekmesinde siz xəttin növünü, rəngini və qalınlığını təyin edə bilərsiniz.
Artıq çəkilmiş trend xəttini silmək üçün silinəcək trend xəttini seçin və Sil düyməsini sıxın.
Nəzərə alınan reqressiya təhlili alətinin üstünlükləri aşağıdakılardır:
onun üçün məlumat cədvəli yaratmadan qrafiklərdə trend xəttinin qurulmasının nisbi asanlığı;
təklif olunan trend xətlərinin növlərinin kifayət qədər geniş siyahısı və bu siyahıya ən çox istifadə olunan reqressiya növləri daxildir;
tədqiq olunan prosesin davranışını ixtiyari (sağlam düşüncə hüdudları daxilində) irəli və geriyə addımlarla proqnozlaşdırmaq bacarığı;
trend xətti tənliyini analitik formada əldə etmək bacarığı;
zərurət yarandıqda, yaxınlaşmanın etibarlılığının qiymətləndirilməsinin əldə edilməsi imkanı.
Dezavantajlara aşağıdakılar daxildir:
trend xəttinin qurulması yalnız bir sıra məlumatlar əsasında qurulmuş bir diaqram olduqda həyata keçirilir;
onun üçün əldə edilən trend xətti tənliklərinə əsaslanaraq öyrənilən xarakteristika üçün məlumat seriyalarının yaradılması prosesi bir qədər qarışıqdır: tələb olunan reqressiya tənlikləri orijinal məlumat seriyasının dəyərlərində hər dəyişikliklə yenilənir, lakin yalnız diaqram sahəsi daxilində , isə məlumat seriyası köhnə trend xətti tənliyi əsasında yaradılan , dəyişməz olaraq qalır;
PivotChart hesabatlarında diaqramın və ya əlaqəli Pivot Cədvəl hesabatının görünüşünün dəyişdirilməsi mövcud trend xətlərini saxlamır, yəni trend xətləri çəkməzdən və ya PivotChart hesabatını başqa şəkildə formatlamazdan əvvəl siz hesabat tərtibatının tələb olunan tələblərə cavab verdiyinə əmin olmalısınız.
Trend xətləri qrafik, histoqram, düz qeyri-standart sahə diaqramları, bar diaqramları, səpələnmə diaqramları, qabarcıq diaqramları və fond qrafikləri kimi diaqramlarda təqdim olunan məlumat seriyalarını əlavə etmək üçün istifadə edilə bilər.
Siz 3D, normallaşdırılmış, radar, pasta və pişi diaqramlarında məlumat seriyasına trend xətləri əlavə edə bilməzsiniz.
Excel-in daxili funksiyalarından istifadə
Excel, həmçinin qrafik sahəsindən kənarda trend xətlərini çəkmək üçün reqressiya təhlili alətinə malikdir. Bu məqsədlə istifadə edə biləcəyiniz bir sıra statistik iş səhifəsi funksiyaları var, lakin onların hamısı yalnız xətti və ya eksponensial reqressiyalar qurmağa imkan verir.
Excel xətti reqressiyanın qurulması üçün bir neçə funksiyaya malikdir, xüsusən:
SLOPE və CUT.
TREND;
Eksponensial trend xəttinin qurulması üçün bir neçə funksiya, xüsusən:
LGRFPRIBL.
Qeyd etmək lazımdır ki, TREND və GROWTH funksiyalarından istifadə edərək reqressiyaların qurulması üsulları demək olar ki, eynidir. Eyni şeyi LINEST və LGRFPRIBL funksiyaları cütü haqqında da demək olar. Bu dörd funksiya üçün dəyərlər cədvəli yaratmaq reqressiyaların qurulması prosesini bir qədər çətinləşdirən massiv düsturları kimi Excel xüsusiyyətlərindən istifadə edir. Onu da qeyd edək ki, xətti reqressiyanın qurulması, fikrimizcə, SLOPE və INTERCEPT funksiyalarından istifadə etməklə ən asan yerinə yetirilir, burada birincisi xətti reqressiyanın yamacını, ikincisi isə y-də reqressiyanın kəsdiyi seqmenti təyin edir. -ox.
Reqressiya təhlili üçün daxili funksiyalar alətinin üstünlükləri bunlardır:
trend xətlərini müəyyən edən bütün daxili statistik funksiyalar üçün tədqiq olunan xarakteristikanın verilənlər seriyasının yaradılmasının kifayət qədər sadə, vahid prosesi;
yaradılan verilənlər seriyası əsasında trend xətlərinin qurulması üçün standart metodologiya;
tələb olunan sayda irəli və ya geri addımlarla tədqiq olunan prosesin davranışını proqnozlaşdırmaq bacarığı.
Dezavantajlara Excel-də digər (xətti və eksponensial istisna olmaqla) trend xətlərinin yaradılması üçün daxili funksiyaların olmaması daxildir. Bu hal çox vaxt tədqiq olunan prosesin kifayət qədər dəqiq modelini seçməyə, eləcə də reallığa yaxın proqnozlar almağa imkan vermir. Bundan əlavə, TREND və GROWTH funksiyalarından istifadə edərkən, trend xətlərinin tənlikləri məlum deyil.
Qeyd etmək lazımdır ki, müəlliflər reqressiya təhlili kursunu hər hansı bir tamlıq dərəcəsi ilə təqdim etməyi qarşılarına məqsəd qoymamışlar. Onun əsas vəzifəsi təxmini məsələlərin həlli zamanı konkret nümunələrdən istifadə etməklə Excel paketinin imkanlarını göstərməkdir; reqressiyaların qurulması və proqnozlaşdırılması üçün Excel-in hansı effektiv alətlərə malik olduğunu nümayiş etdirmək; reqressiya təhlili üzrə geniş biliyə malik olmayan istifadəçi tərəfindən belə problemlərin nisbətən asanlıqla necə həll oluna biləcəyini təsvir edin.
Xüsusi problemlərin həlli nümunələri
Sadalanan Excel alətlərindən istifadə edərək konkret problemlərin həllinə baxaq.
Problem 1
1995-2002-ci illər üçün avtomobil nəqliyyatı müəssisəsinin mənfəəti haqqında məlumat cədvəli ilə. aşağıdakıları etməlisiniz:
Diaqram qurun.
Qrafikə xətti və çoxhədli (kvadrat və kub) trend xətləri əlavə edin.
Trend xətti tənliklərindən istifadə edərək, 1995-2004-cü illər üçün hər bir trend xətti üçün müəssisə mənfəəti haqqında cədvəl məlumatları əldə edin.
Müəssisənin 2003 və 2004-cü illər üçün mənfəətinin proqnozunu tərtib edin.
Problemin həlli
Excel iş vərəqinin A4:C11 xanalarının diapazonuna Şəkildə göstərilən iş vərəqini daxil edin. 4.
B4:C11 xanalarının diapazonunu seçdikdən sonra diaqram qururuq.
Biz qurulmuş diaqramı aktivləşdiririk və yuxarıda təsvir edilən üsula uyğun olaraq Trend Xətti dialoq qutusunda trend xəttinin növünü seçdikdən sonra (şək. 1-ə baxın) diaqrama növbə ilə xətti, kvadrat və kub trend xətlərini əlavə edirik. Eyni dialoq qutusunda Parametrlər sekmesini açın (Şəkil 2-ə baxın), təqribən (hamarlanmış) əyrinin adı sahəsində əlavə olunan trendin adını daxil edin və Proqnoz: dövrlər sahəsində dəyər 2, çünki iki il üçün mənfəət proqnozu vermək planlaşdırılır. Diaqram sahəsində reqressiya tənliyini və R2 yaxınlaşma etibarlılıq dəyərini göstərmək üçün ekrandakı onay qutularında tənliyi göstərin və diaqramda yaxınlaşma etibarlılığı dəyərini (R^2) qoyun. Daha yaxşı vizual qavrayış üçün biz qurulmuş trend xətlərinin növünü, rəngini və qalınlığını dəyişdiririk, bunun üçün Trend Xətti Formatı dialoq qutusunun Görünüş nişanından istifadə edirik (bax. Şəkil 3). Əlavə edilmiş trend xətləri ilə nəticələnən diaqram Şəkildə göstərilmişdir. 5.
1995-2004-cü illər üçün hər bir tendensiya xətti üzrə müəssisə mənfəəti haqqında cədvəl məlumatları əldə etmək. Şəkildə təqdim olunan trend xətti tənliklərindən istifadə edək. 5. Bunun üçün D3:F3 diapazonunun xanalarına seçilmiş trend xəttinin növü haqqında mətn məlumatı daxil edin: Xətti trend, Kvadrat trend, Kub trend. Sonra D4 xanasına xətti reqressiya düsturunu daxil edin və doldurma markerindən istifadə edərək bu düsturu D5:D13 xana diapazonuna nisbi istinadlarla köçürün. Qeyd etmək lazımdır ki, D4:D13 xanaları diapazonundan xətti reqressiya düsturu olan hər bir xana arqument kimi A4:A13 diapazonundan müvafiq xanaya malikdir. Eynilə, kvadrat reqressiya üçün E4:E13 xanalarının diapazonunu, kub reqressiya üçün isə F4:F13 xanalarının diapazonunu doldurun. Beləliklə, müəssisənin 2003 və 2004-cü illər üzrə mənfəətinin proqnozu tərtib edilmişdir. üç tendensiyadan istifadə edir. Nəticə dəyərlər cədvəli Şəkildə göstərilmişdir. 6.
Problem 2
Diaqram qurun.
Qrafikə loqarifmik, güc və eksponensial trend xətləri əlavə edin.
Alınan tendensiya xətlərinin tənliklərini, eləcə də onların hər biri üçün R2 yaxınlaşmasının etibarlılıq qiymətlərini çıxarın.
Trend xətti tənliklərindən istifadə edərək, 1995-2002-ci illər üçün hər bir trend xətti üzrə müəssisənin mənfəəti haqqında cədvəl məlumatları əldə edin.
Bu trend xətlərindən istifadə etməklə şirkətin 2003 və 2004-cü illər üçün mənfəətinin proqnozunu hazırlayın.
Problemin həlli
1-ci məsələnin həllində verilən metodologiyaya əməl edərək, ona loqarifmik, güc və eksponensial meyl xətlərinin əlavə olunduğu diaqramı əldə edirik (şək. 7). Sonra, əldə edilmiş trend xətti tənliklərindən istifadə edərək, 2003 və 2004-cü illər üçün proqnozlaşdırılan dəyərlər də daxil olmaqla, müəssisənin mənfəəti üçün dəyərlər cədvəlini doldururuq. (şək. 8).
Şəkildə. 5 və şək. görünə bilər ki, loqarifmik meylli model yaxınlaşma etibarlılığının ən aşağı qiymətinə uyğundur.
R2 = 0,8659
R2-nin ən yüksək dəyərləri polinom tendensiyası olan modellərə uyğundur: kvadrat (R2 = 0,9263) və kub (R2 = 0,933).
Problem 3
1-ci tapşırıqda verilmiş 1995-2002-ci illər üçün avtomobil nəqliyyatı müəssisəsinin mənfəəti haqqında məlumat cədvəli ilə aşağıdakı addımları yerinə yetirməlisiniz.
TREND və GROW funksiyalarından istifadə edərək xətti və eksponensial trend xətləri üçün məlumat seriyalarını əldə edin.
TREND və GROWTH funksiyalarından istifadə edərək 2003 və 2004-cü illər üçün müəssisənin mənfəətinin proqnozunu hazırlayın.
İlkin məlumatlar və əldə edilən verilənlər seriyası üçün diaqram qurun.
Problemin həlli
1-ci məsələ üçün iş vərəqindən istifadə edək (şək. 4-ə baxın). TREND funksiyasından başlayaq:
müəssisənin mənfəəti haqqında məlum məlumatlara uyğun gələn TREND funksiyasının dəyərləri ilə doldurulmalı olan D4:D11 xanalarının diapazonunu seçin;
Insert menyusundan Function əmrini çağırın. Görünən Function Wizard dialoq qutusunda Statistika kateqoriyasından TREND funksiyasını seçin və sonra OK düyməsini sıxın. Eyni əməliyyatı standart alətlər panelindəki (Funksiya daxil et) düyməsini sıxmaqla yerinə yetirmək olar.
Görünən Function Arquments dialoq qutusunda Known_values_y sahəsinə C4:C11 xanalarının diapazonunu daxil edin; Known_values_x sahəsində - B4:B11 xanalarının diapazonu;
Daxil edilmiş düsturun massiv formuluna çevrilməsi üçün + + düymələri birləşməsindən istifadə edin.
Düstur çubuğuna daxil etdiyimiz düstur belə görünəcək: =(TREND(C4:C11,B4:B11)).
Nəticədə, D4:D11 xanalarının diapazonu TREND funksiyasının müvafiq qiymətləri ilə doldurulur (Şəkil 9).
2003 və 2004-cü illər üçün müəssisənin mənfəətinin proqnozunu vermək. zəruri:
TREND funksiyası tərəfindən proqnozlaşdırılan dəyərlərin daxil ediləcəyi D12:D13 xanalarının diapazonunu seçin.
TREND funksiyasına zəng edin və görünən Funksiya Arqumentləri dialoq qutusunda Bilinən_dəyərlər_y sahəsinə - C4:C11 xanalarının diapazonunu daxil edin; Known_values_x sahəsində - B4:B11 xanalarının diapazonu; və Yeni_dəyərlər_x sahəsində - B12:B13 xanalarının diapazonu.
Ctrl + Shift + Enter düymələri birləşməsindən istifadə edərək bu düsturu massiv düsturuna çevirin.
Daxil edilmiş düstur belə görünəcək: =(TREND(C4:C11;B4:B11;B12:B13)) və D12:D13 xanalarının diapazonu TREND funksiyasının proqnozlaşdırılan qiymətləri ilə doldurulacaq (bax. 9).
Məlumat seriyası qeyri-xətti asılılıqların təhlilində istifadə olunan və xətti analoqu TREND ilə eyni şəkildə işləyən GROWTH funksiyasından istifadə etməklə eyni şəkildə doldurulur.
Şəkil 10-da cədvəl düsturun göstərilməsi rejimində göstərilir.
İlkin məlumatlar və əldə edilmiş məlumat seriyası üçün Şəkildə göstərilən diaqram. on bir.
Problem 4
Cari ayın 1-dən 11-dək olan dövr üçün avtonəqliyyat müəssisəsinin dispetçer xidməti tərəfindən xidmətlər üçün müraciətlərin qəbulu haqqında məlumat cədvəli ilə aşağıdakı hərəkətləri yerinə yetirməlisiniz.
Xətti reqressiya üçün verilənlər seriyasını əldə edin: SLOPE və INTERCEPT funksiyalarından istifadə etməklə; LINEST funksiyasından istifadə etməklə.
LGRFPRIBL funksiyasından istifadə edərək eksponensial reqressiya üçün bir sıra məlumat əldə edin.
Yuxarıdakı funksiyalardan istifadə edərək cari ayın 12-dən 14-dək olan dövr üçün dispetçer xidmətinə müraciətlərin qəbulu ilə bağlı proqnoz hazırlayın.
Orijinal və qəbul edilmiş məlumat seriyası üçün diaqram yaradın.
Problemin həlli
Qeyd edək ki, TREND və GROWTH funksiyalarından fərqli olaraq, yuxarıda sadalanan funksiyaların heç biri (SLOPE, INTERCEPT, LINEST, LGRFPRIB) reqressiya deyil. Bu funksiyalar zəruri reqressiya parametrlərini təyin edərək yalnız köməkçi rol oynayır.
SLOPE, INTERCEPT, LINEST, LGRFPRIB funksiyalarından istifadə etməklə qurulmuş xətti və eksponensial reqressiyalar üçün TREND və GROWTH funksiyalarına uyğun xətti və eksponensial reqressiyalardan fərqli olaraq, onların tənliklərinin görünüşü həmişə məlum olur.
1 . tənliyi ilə xətti reqressiya quraq:
y = mx+b
SLOPE və INTERCEPT funksiyalarından istifadə etməklə, reqressiya mailliyi m SLOPE funksiyası ilə, sərbəst b termini isə INTERCEPT funksiyası ilə müəyyən edilir.
Bunun üçün aşağıdakı hərəkətləri həyata keçiririk:
orijinal cədvəli A4:B14 xana diapazonuna daxil edin;
m parametrinin qiyməti C19 xanasında müəyyən ediləcək. Statistik kateqoriyadan Slope funksiyasını seçin; məlum_dəyərlər_y sahəsinə B4:B14 xanalarının diapazonunu və məlum_dəyərlər_x sahəsinə A4:A14 xanalarının diapazonunu daxil edin. Düstur C19 xanasına daxil ediləcək: =SLOPE(B4:B14,A4:A14);
Bənzər bir texnikadan istifadə edərək D19 xanasındakı b parametrinin qiyməti müəyyən edilir. Və onun məzmunu belə görünəcək: =SEGMENT(B4:B14,A4:A14). Beləliklə, xətti reqressiyanın qurulması üçün tələb olunan m və b parametrlərinin dəyərləri müvafiq olaraq C19, D19 xanalarında saxlanılacaq;
Sonra C4 xanasına xətti reqressiya düsturunu aşağıdakı formada daxil edin: =$C*A4+$D. Bu düsturda C19 və D19 xanaları mütləq istinadlarla yazılır (mümkün surət çıxarma zamanı xana ünvanı dəyişməməlidir). Mütləq istinad işarəsi $ kursoru xananın ünvanına qoyduqdan sonra klaviaturadan və ya F4 düyməsindən istifadə etməklə daxil edilə bilər. Doldurma qulpundan istifadə edərək, bu düsturu C4:C17 xanalarının diapazonuna köçürün. Biz tələb olunan verilənlər seriyasını alırıq (şək. 12). Sorğuların sayı tam ədəd olduğuna görə, Hüceyrə Format pəncərəsinin Nömrə sekmesinde onluq yerlərin sayı ilə rəqəm formatını 0-a təyin etməlisiniz.
2 . İndi tənliklə verilən xətti reqressiya quraq:
y = mx+b
LINEST funksiyasından istifadə etməklə.
Bunun üçün:
LINEST funksiyasını C20:D20 xana diapazonuna massiv düsturu kimi daxil edin: =(LINEST(B4:B14,A4:A14)). Nəticədə C20 xanasında m parametrinin qiymətini, D20 xanasında b parametrinin qiymətini alırıq;
D4 xanasına formula daxil edin: =$C*A4+$D;
doldurma markerindən istifadə edərək bu düsturu D4:D17 xana diapazonuna köçürün və istədiyiniz məlumat seriyasını əldə edin.
3 . Tənlik ilə eksponensial reqressiya qururuq:
LGRFPRIBL funksiyasından istifadə edərək, oxşar şəkildə həyata keçirilir:
C21:D21 xana diapazonunda biz LGRFPRIBL funksiyasını massiv düsturu kimi daxil edirik: =( LGRFPRIBL (B4:B14,A4:A14)). Bu halda m parametrinin qiyməti C21 xanasında, b parametrinin qiyməti isə D21 xanasında müəyyən ediləcək;
düstur E4 xanasına daxil edilir: =$D*$C^A4;
doldurma markerindən istifadə edərək, bu düstur eksponensial reqressiya üçün verilənlər seriyasının yerləşəcəyi E4:E17 xanalarının diapazonuna köçürülür (bax. Şəkil 12).
Şəkildə. Şəkil 13 tələb olunan xana diapazonları ilə istifadə etdiyimiz funksiyaları, həmçinin düsturları görə biləcəyiniz cədvəli göstərir.
Böyüklük R 2 çağırdı təyin əmsalı.
Reqressiya asılılığının qurulması vəzifəsi R əmsalının maksimum qiyməti aldığı modelin (1) m əmsallarının vektorunu tapmaqdır.
R-nin əhəmiyyətini qiymətləndirmək üçün düsturdan istifadə edərək hesablanan Fişerin F testindən istifadə olunur
Harada n- nümunənin ölçüsü (təcrübələrin sayı);
k model əmsallarının sayıdır.
Əgər F verilənlər üçün bəzi kritik dəyəri keçərsə n Və k və qəbul edilmiş etimad ehtimalı, onda R dəyəri əhəmiyyətli hesab edilir. Cədvəllər kritik dəyərlər F riyazi statistikaya dair arayış kitablarında verilmişdir.
Beləliklə, R-nin əhəmiyyəti təkcə onun dəyəri ilə deyil, həm də təcrübələrin sayı ilə modelin əmsallarının (parametrlərinin) sayı arasındakı nisbətlə müəyyən edilir. Həqiqətən, sadə xətti model üçün n=2 üçün korrelyasiya nisbəti 1-ə bərabərdir (bir düz xətt həmişə müstəvidə 2 nöqtədən keçə bilər). Bununla belə, əgər eksperimental məlumatlar təsadüfi dəyişənlərdirsə, R-nin belə bir dəyərinə çox ehtiyatla etibar etmək lazımdır. Adətən, əhəmiyyətli R və etibarlı reqressiya əldə etmək üçün təcrübələrin sayının model əmsallarının (n>k) sayından əhəmiyyətli dərəcədə çox olmasını təmin etməyə çalışırlar.
Xətti reqressiya modelini qurmaq üçün sizə lazımdır:
1) eksperimental məlumatları ehtiva edən n sətir və m sütunun siyahısını hazırlayın (çıxış dəyərini ehtiva edən sütun Y siyahıda birinci və ya sonuncu olmalıdır); Məsələn, əvvəlki tapşırıqdan məlumatları götürək, “Dövr nömrəsi” adlı bir sütun əlavə edərək, dövr nömrələrini 1-dən 12-yə qədər nömrələyin. (bu dəyərlər olacaq. X)
2) Data/Data Analysis/Regression menyusuna keçin
"Alətlər" menyusunda "Məlumatların təhlili" maddəsi yoxdursa, o zaman eyni menyuda "Əlavələr" maddəsinə keçməli və "Analiz paketi" qutusunu qeyd etməlisiniz.
3) "Reqressiya" informasiya qutusunda təyin edin:
· daxiletmə intervalı Y;
· daxiletmə intervalı X;
· çıxış intervalı - hesablama nəticələrinin yerləşdiriləcəyi intervalın yuxarı sol xanası (onları yeni iş vərəqinə yerləşdirmək tövsiyə olunur);
4) "Ok" düyməsini basın və nəticələri təhlil edin.
Ən kiçik kvadrat üsulu reqressiya tənliyinin parametrlərini qiymətləndirmək üçün istifadə olunur.Xüsusiyyətlər arasında stoxastik əlaqələrin öyrənilməsi üsullarından biri reqressiya təhlilidir.
Reqressiya təhlili reqressiya tənliyinin törəməsidir, onun köməyi ilə başqa (və ya digər) dəyişənlərin (amil-atributların) qiyməti məlum olduqda təsadüfi dəyişənin (nəticə atributunun) orta qiyməti tapılır. Buraya aşağıdakı addımlar daxildir:
- əlaqə formasının seçilməsi (analitik reqressiya tənliyinin növü);
- tənliyin parametrlərinin qiymətləndirilməsi;
- analitik reqressiya tənliyinin keyfiyyətinin qiymətləndirilməsi.
Xətti qoşa münasibətdə reqressiya tənliyi aşağıdakı formanı alacaq: y i =a+b·x i +u i . Bu tənliyin a və b parametrləri verilənlər əsasında təxmin edilir statistik müşahidə x və y. Belə qiymətləndirmənin nəticəsi tənlikdir: , burada , a və b parametrlərinin təxminləridir , reqressiya tənliyindən (hesablanmış dəyər) alınan nəticə atributunun (dəyişən) qiymətidir.
Ən çox parametrləri qiymətləndirmək üçün istifadə olunur Ən kiçik kvadratlar metodu (LSM).
Ən kiçik kvadratlar metodu reqressiya tənliyinin parametrlərinin ən yaxşı (ardıcıl, səmərəli və qərəzsiz) qiymətləndirmələrini təmin edir. Lakin yalnız təsadüfi termin (u) və müstəqil dəyişən (x) ilə bağlı müəyyən fərziyyələr yerinə yetirildikdə (OLS fərziyyələrinə baxın).
Ən kiçik kvadratlar üsulu ilə xətti cüt tənliyin parametrlərinin qiymətləndirilməsi məsələsi aşağıdakı kimidir: parametrlərin bu cür təxminlərini əldə etmək üçün , nəticədə nəticə xarakteristikasının faktiki dəyərlərinin kvadratik sapmalarının cəmi - y i hesablanmış dəyərlərdən minimaldır.
Formal olaraq OLS testi belə yazmaq olar: 
.
Ən kiçik kvadratlar üsullarının təsnifatı
- Ən kiçik kvadrat üsulu.
- Maksimum ehtimal üsulu (normal klassik xətti reqressiya modeli üçün reqressiya qalıqlarının normallığı nəzərdə tutulur).
- Ümumiləşdirilmiş ən kiçik kvadratlar OLS metodu səhvlərin avtokorrelyasiyası və heteroskedastiklik halında istifadə olunur.
- Çəkili ən kiçik kvadratlar metodu ( xüsusi hal heteroskedastik qalıqlarla OLS).
Nümunəni izah edək klassik üsul qrafik olaraq ən kiçik kvadratlar. Bunun üçün müşahidə məlumatlarına (x i, y i, i=1;n) əsasən düzbucaqlı koordinat sistemində (belə səpilmə qrafası korrelyasiya sahəsi adlanır) səpələnmə qrafikini quracağıq. Korrelyasiya sahəsinin nöqtələrinə ən yaxın olan düz xətti seçməyə çalışaq. Ən kiçik kvadratlar metoduna görə xətt elə seçilir ki, korrelyasiya sahəsinin nöqtələri ilə bu xətt arasındakı şaquli məsafələrin kvadratlarının cəmi minimal olsun. 
Bu problem üçün riyazi qeyd: 
.
y i və x i =1...n qiymətləri bizə məlumdur, bunlar müşahidə məlumatlarıdır. S funksiyasında onlar sabitləri təmsil edirlər. Bu funksiyadakı dəyişənlər parametrlərin tələb olunan təxminləridir - , . İki dəyişənin funksiyasının minimumunu tapmaq üçün parametrlərin hər biri üçün bu funksiyanın qismən törəmələrini hesablamaq və onları sıfıra bərabərləşdirmək lazımdır, yəni. 
.
Nəticədə 2 normal xətti tənlik sistemi əldə edirik: 
Qərar vermək bu sistem, biz tələb olunan parametr təxminlərini tapırıq: 
Reqressiya tənliyinin parametrlərinin hesablanmasının düzgünlüyünü məbləğləri müqayisə etməklə yoxlamaq olar (hesablamaların yuvarlaqlaşdırılması ilə əlaqədar müəyyən uyğunsuzluqlar ola bilər).
Parametr təxminlərini hesablamaq üçün Cədvəl 1-i qura bilərsiniz.
Reqressiya əmsalının işarəsi b əlaqənin istiqamətini göstərir (əgər b >0 olarsa əlaqə birbaşa, b olarsa<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formal olaraq a parametrinin dəyəri x sıfıra bərabər olan y-nin orta qiymətidir. Əgər atribut-amil sıfır dəyərinə malik deyilsə və ola bilməzsə, onda a parametrinin yuxarıdakı şərhinin mənası yoxdur.
Xüsusiyyətlər arasındakı əlaqənin yaxınlığının qiymətləndirilməsi
xətti cüt korrelyasiya əmsalından - r x,y istifadə etməklə həyata keçirilir. Bu düsturla hesablana bilər: 
. Bundan əlavə, xətti cüt korrelyasiya əmsalı b reqressiya əmsalı vasitəsilə müəyyən edilə bilər: 
.
Xətti cüt korrelyasiya əmsalının məqbul dəyərlər diapazonu -1 ilə +1 arasındadır. Korrelyasiya əmsalının işarəsi əlaqənin istiqamətini göstərir. Əgər r x, y >0 olarsa, onda əlaqə birbaşadır; əgər r x, y<0, то связь обратная.
Əgər bu əmsal böyüklüyünə görə vahidliyə yaxındırsa, onda xüsusiyyətlər arasındakı əlaqə kifayət qədər yaxın xətti olan kimi şərh edilə bilər. Əgər onun modulu bir ê r x, y ê =1-ə bərabərdirsə, onda xarakteristikalar arasındakı əlaqə funksional xətti olur. Əgər x və y xüsusiyyətləri xətti müstəqildirsə, r x,y 0-a yaxındır.
r x,y hesablamaq üçün Cədvəl 1-dən də istifadə edə bilərsiniz.
Cədvəl 1
| N müşahidə | x i | y i | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 y 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| n | x n | y n | x n y n | ||
| Sütun cəmi | ∑x | ∑y | ∑xy | ||
| Orta dəyər |
 |
 |
,
burada d 2 reqressiya tənliyi ilə izah edilən y-nin dispersiyasıdır;
e 2 - y-nin qalıq (reqressiya tənliyi ilə izah olunmayan) dispersiyası;
s 2 y - y-nin ümumi (ümumi) dispersiyası.
Determinasiya əmsalı reqressiya (və deməli, x amili) ilə izah edilən nəticə atributunun y variasiyasının (dispersiyasının) ümumi variasiyada (dispersiyada) y nisbətini xarakterizə edir. R 2 yx təyin əmsalı 0-dan 1-ə qədər olan dəyərləri qəbul edir. Müvafiq olaraq, 1-R 2 yx dəyəri modeldə və spesifikasiya xətalarında nəzərə alınmayan digər amillərin təsiri nəticəsində yaranan y dispersiyasının nisbətini xarakterizə edir.
Qoşalaşmış xətti reqressiya ilə R 2 yx =r 2 yx.
