Excel-də ən kiçik kvadratlar metodundan istifadə edərək tənliklər sistemini həll edin. Excel-də ən kiçik kvadratlar metodunun tətbiqi. Proqnoz üçün istifadə olunan ilkin məlumatların düzgünlüyü haqqında bir neçə söz

Metod ən kiçik kvadratlar iki sıra ədədlər çoxluğuna ən çox uyğun gələn xətti tənliyin qurulması üçün riyazi prosedurdur. Bu metoddan istifadənin məqsədi ümumi kvadrat səhvini minimuma endirməkdir. Excel-də bu metodu hesablamalarınıza tətbiq etməyə kömək edəcək alətlər var. Bunun necə edildiyini anlayaq.

· Excel-də metoddan istifadə

o “Solution Search” əlavəsinin aktivləşdirilməsi

o Problemli şərtlər

o Həll

Excel-də metoddan istifadə

Ə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.

1. Nişana keçin "Fayl".

2. Bölmə adına klikləyin "Seçimlər".

3. Açılan pəncərədə alt bölməni seçin "Əlavələr".

4. Blokda "Nəzarət", pəncərənin aşağı hissəsində yerləşən açarı vəziyyətinə qoyun "Excel Əlavələri"(fərqli dəyər varsa) və düyməni basın "Get...".

5. Kiçik bir pəncərə açılır. Parametrin yanında bir işarə qoyuruq "Həll tapmaq". düyməsinə klikləyin "TAMAM".

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

Dərs: Excel-də həll yolu tapmaq

Problemin şərtləri

LSM-in tətbiqini təsvir edək konkret misal. İ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.

1. Birinci dəyərin solunda x nömrə qoyun 1 . Bu, birinci əmsal dəyərinin təxmini dəyəri olacaq n.

2. Sütunun sağında y başqa bir sütun əlavə edin - nx. Bu sütunun birinci xanasına əmsalı vurmaq üçün düstur yazırıq n birinci dəyişənin hər xanası üçün x. Eyni zamanda, sahə ilə əlaqəni mütləq əmsalı ilə edirik, çünki bu dəyər dəyişməyəcəkdir. düyməsinə klikləyin Daxil edin.

3. Doldurma işarəsindən istifadə edərək, bu düsturu aşağıdakı sütundakı cədvəlin bütün diapazonuna köçürün.

4. Ayrı bir xanada qiymətlərin kvadratları arasındakı fərqlərin cəmini hesablayın y Və nx. Bunu etmək üçün düyməni basın "Funksiya daxil et".

5. Açılmış vəziyyətdə "Funksiya Sihirbazı" giriş axtarır "SUMMKVARNA". Onu seçin və düyməni basın "TAMAM".

6. Arqumentlər pəncərəsi açılır. Sahədə "x massivi" y. Sahədə "massiv_y" sütun hüceyrələrinin diapazonunu daxil edin nx. Dəyərləri daxil etmək üçün kursoru sahəyə yerləşdirmək və vərəqdə müvafiq diapazonu seçmək kifayətdir. Daxil etdikdən sonra düyməni basın "TAMAM".

7. Nişana keçin "Məlumat". Alətlər qutusundakı lentdə "Təhlil" düyməsinə basın "Həll tapmaq".

8. Bu alət üçün parametrlər pəncərəsi açılır. Sahədə “Məqsəd funksiyasını optimallaşdırın” düsturla xananın ünvanını göstərin "SUMMKVARNA". Parametrdə "əvvəl" açarı vəziyyətinə qoymağınızdan əmin olun "Minimum". Sahədə "Hüceyrələrin dəyişdirilməsi"əmsal dəyəri ilə ünvanı göstərin n. düyməsinə klikləyin "Bir həll tapın".

9. Həll əmsal xanasında göstəriləcək n. Bu dəyər funksiyanın ən kiçik kvadratı olacaq. Nəticə istifadəçini qane edərsə, düyməni sıxın "TAMAM"əlavə pəncərədə.

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.

http://multitest.semico.ru/mnk.htm

Ümumi müddəalar

Necə az sayı mütləq qiymətdə düz xətt (2) daha yaxşı seçilir. Düz xəttin (2) seçilməsinin düzgünlüyünün bir xüsusiyyəti olaraq, kvadratların cəmini götürə bilərik.

S üçün minimum şərtlər olacaq

	(6)
	(7)

(6) və (7) tənlikləri aşağıdakı kimi yazıla bilər:

	(8)
	(9)

(8) və (9) tənliklərindən xi və y i-nin eksperimental qiymətlərindən a və b tapmaq asandır. (8) və (9) tənlikləri ilə təyin olunan (2) sətir ən kiçik kvadratlar üsulu ilə alınan xətt adlanır (bu ad S kvadratlarının cəminin minimuma malik olduğunu vurğulayır). Düz xəttin (2) təyin olunduğu (8) və (9) tənliklərə normal tənliklər deyilir.

Normal tənliklər qurmağın sadə və ümumi yolunu göstərə bilərsiniz. Təcrübə nöqtələri (1) və tənlik (2) istifadə edərək, a və b üçün tənliklər sistemi yaza bilərik

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = ax n + b,

Gəlin bu tənliklərin hər birinin sol və sağ tərəflərini birinci naməlum a əmsalına (yəni x 1, x 2, ..., x n) vuraq və yaranan tənlikləri əlavə edək, nəticədə birinci normal tənlik (8) yaransın. .

Bu tənliklərin hər birinin sol və sağ tərəflərini ikinci naməlum b əmsalına vuraq, yəni. 1-ə, və nəticədə yaranan tənlikləri əlavə edin, nəticə ikinci normal tənlikdir (9).

Normal tənlikləri əldə etməyin bu üsulu ümumidir: məsələn, funksiya üçün uyğundur

sabit qiymət var və onu eksperimental məlumatlardan müəyyən etmək lazımdır (1).

k üçün tənliklər sistemi yazıla bilər:

Ən kiçik kvadratlar metodundan istifadə edərək düz xətti (2) tapın.

Həll. Biz tapdıq:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

(8) və (9)91a+21b=179.1 tənliklərini yazırıq,

21a+6b=46.3, buradan tapırıq
a=0,98 b=4,3.

Hansı ki, elm və praktik fəaliyyətin müxtəlif sahələrində ən geniş tətbiq tapır. Bu, fizika, kimya, biologiya, iqtisadiyyat, sosiologiya, psixologiya və s. ola bilər. Taleyin iradəsi ilə mən tez-tez iqtisadiyyatla məşğul oluram və buna görə də bu gün sizin üçün heyrətamiz bir ölkəyə səyahət təşkil edəcəyəm. Ekonometriya=) ...Necə istəməzsən?! Orada çox yaxşıdır - sadəcə qərarınızı verməlisiniz! ...Amma yəqin ki, mütləq istədiyiniz şey problemləri həll etməyi öyrənməkdir ən kiçik kvadratlar üsulu. Və xüsusilə çalışqan oxucular onları nəinki dəqiq, həm də ÇOX TEZ ;-) həll etməyi öyrənəcəklər. problemin ümumi ifadəsi+ müşayiət edən nümunə:

Müəyyən bir fənn sahəsində kəmiyyət ifadəsi olan göstəriciləri öyrənək. Eyni zamanda, göstəricinin göstəricidən asılı olduğuna inanmaq üçün hər cür əsas var. Bu fərziyyə belə ola bilər elmi fərziyyə və əsas sağlam düşüncəyə əsaslanmalıdır. Bununla belə, elmi bir kənara qoyub daha iştahaaçan sahələri - yəni ərzaq mağazalarını araşdıraq. ilə işarə edək:

– ərzaq mağazasının pərakəndə satış sahəsi, kv.m.,
- ərzaq mağazasının illik dövriyyəsi, milyon rubl.

Tamamilə aydındır ki, mağaza sahəsi nə qədər böyükdürsə, əksər hallarda onun dövriyyəsi bir o qədər çox olacaqdır.

Tutaq ki, qavalla müşahidələr/təcrübələr/hesablamalar/rəqslər apardıqdan sonra ixtiyarımızda rəqəmsal məlumatlar var:

Ərzaq mağazaları ilə məncə hər şey aydındır: - bu, 1-ci mağazanın ərazisidir, - onun illik dövriyyəsi, - 2-ci mağazanın sahəsi, - illik dövriyyəsi və s. Yeri gəlmişkən, məxfi materiallara giriş heç də lazım deyil - ticarət dövriyyəsinin kifayət qədər dəqiq qiymətləndirilməsi aşağıdakı vasitələrlə əldə edilə bilər. riyazi statistika. Bununla belə, diqqətinizi yayındırmayaq, kommersiya casusluğu kursu artıq ödənişlidir =)

Cədvəl məlumatları nöqtələr şəklində də yazıla və tanış formada təsvir edilə bilər Kartezyen sistem .

Gəlin vacib suala cavab verək: Keyfiyyətli bir araşdırma üçün neçə bal lazımdır?

Nə qədər böyük, bir o qədər yaxşıdır. Minimum məqbul dəst 5-6 baldan ibarətdir. Bundan əlavə, məlumatların miqdarı kiçik olduqda, "anomal" nəticələr nümunəyə daxil edilə bilməz. Beləliklə, məsələn, kiçik bir elit mağaza "həmkarlarından" daha çox böyük sifarişlər qazana bilər və bununla da onları təhrif edə bilər. ümumi model, tapmaq üçün lazım olan budur!

Çox sadə desək, bir funksiya seçməliyik, cədvəli nöqtələrə mümkün qədər yaxın keçir . Bu funksiya deyilir yaxınlaşdıran (yaxınlaşma - yaxınlaşma) və ya nəzəri funksiya . Ümumiyyətlə, burada dərhal aşkar bir "iddiaçı" görünür - çoxhədli yüksək dərəcə, onun qrafiki BÜTÜN nöqtələrdən keçir. Ancaq bu seçim mürəkkəbdir və çox vaxt sadəcə səhvdir. (çünki qrafik hər zaman "dövrə" olacaq və əsas trendi zəif əks etdirəcək).

Beləliklə, axtarılan funksiya kifayət qədər sadə olmalı və eyni zamanda asılılığı adekvat şəkildə əks etdirməlidir. Təxmin etdiyiniz kimi, bu cür funksiyaları tapmaq üsullarından biri adlanır ən kiçik kvadratlar üsulu. Əvvəlcə ümumi mənada onun mahiyyətinə nəzər salaq. Bəzi funksiyaların təxmini eksperimental məlumatlara icazə verin:

Bu yaxınlaşmanın düzgünlüyünü necə qiymətləndirmək olar? Eksperimental və funksional qiymətlər arasındakı fərqləri (sapmaları) da hesablayaq (rəsmi öyrənirik). Ağla gələn ilk fikir, məbləğin nə qədər böyük olduğunu təxmin etməkdir, lakin problem fərqlərin mənfi ola bilməsidir. (Misal üçün, ) və bu cür cəmləmə nəticəsində sapmalar bir-birini ləğv edəcəkdir. Buna görə də, yaxınlaşmanın düzgünlüyünü qiymətləndirmək üçün cəmi götürmək yalvarır. modullar sapmalar:

və ya çökdü: (hər kəs bilmirsə: – bu, cəmi işarəsidir və – 1-dən -ə kimi dəyərlər alan köməkçi “sayıcı” dəyişəndir).

Müxtəlif funksiyaları olan eksperimental nöqtələri yaxınlaşdıraraq, əldə edəcəyik müxtəlif mənalar, və açıq-aydın, bu məbləğ daha kiçik olan yerdə, bu funksiya daha dəqiqdir.

Belə bir üsul mövcuddur və ona deyilir ən az modul üsulu. Ancaq praktikada bu, daha geniş yayılmışdır ən kiçik kvadrat üsulu, burada mümkün mənfi dəyərlər modul tərəfindən deyil, sapmaların kvadratlaşdırılması ilə aradan qaldırılır:

, bundan sonra səylər bir funksiyanın seçilməsinə yönəldilir ki, kənarların kvadratı cəmi olsun mümkün qədər kiçik idi. Əslində metodun adı da buradan gəlir.

İndi isə başqa bir şeyə qayıdırıq mühüm məqam: yuxarıda qeyd edildiyi kimi, seçilmiş funksiya olduqca sadə olmalıdır - lakin belə funksiyalar da çoxdur: xətti , hiperbolik, eksponensial, loqarifmik, kvadratik və s. Və təbii ki, burada mən dərhal “fəaliyyət sahəsini azaltmaq” istərdim. Tədqiqat üçün hansı sinif funksiyaları seçməliyəm? Primitiv, lakin təsirli bir texnika:

– Ən asan yol nöqtələri təsvir etməkdir rəsm üzərində və onların yerini təhlil edin. Əgər onlar düz bir xətt üzrə qaçmağa meyllidirlərsə, onda siz axtarmalısınız xəttin tənliyi optimal dəyərlərlə və . Başqa sözlə, vəzifə BELƏ əmsalları tapmaqdır ki, kvadratdan kənara çıxanların cəmi ən kiçik olsun.

Nöqtələr, məsələn, boyunca yerləşirsə hiperbola, onda xətti funksiyanın zəif yaxınlaşma verəcəyi aydındır. Bu halda biz hiperbola tənliyi üçün ən “əlverişli” əmsalları axtarırıq – kvadratların minimum cəmini verənlər .

İndi qeyd edin ki, hər iki halda söhbət gedir iki dəyişənin funksiyaları, arqumentləri kimindir asılılıq parametrləri axtarılır:

Və mahiyyətcə standart bir problemi həll etməliyik - tapın iki dəyişənin minimum funksiyası.

Nümunəmizi xatırlayaq: fərz edək ki, “mağaza” nöqtələri düz bir xətt üzərində yerləşir və buna inanmaq üçün hər cür əsas var. xətti asılılıq pərakəndə satış sahəsindən dövriyyə. BELƏ “a” və “be” əmsallarını tapaq ki, kvadratdan kənara çıxanların cəmi olsun. ən kiçik idi. Hər şey həmişəki kimi - birincisi 1-ci dərəcəli qismən törəmələr. görə xəttilik qaydası Siz cəmi ikonasının altında fərqlənə bilərsiniz:

İstifadə etmək istəyirsinizsə bu məlumat esse və ya kurs işi üçün - Mənbələr siyahısındakı keçidə görə çox minnətdar olacağam; belə ətraflı hesablamaları bir neçə yerdə tapa bilərsiniz:

Standart bir sistem yaradaq:

Hər tənliyi "iki" azaldırıq və əlavə olaraq məbləğləri "parçalayırıq":

Qeyd : “a” və “be”nin nə üçün cəmi işarəsindən kənara çıxarıla biləcəyini müstəqil təhlil edin. Yeri gəlmişkən, formal olaraq bu, məbləğlə edilə bilər

Sistemi “tətbiq olunan” formada yenidən yazaq:

bundan sonra problemimizin həlli üçün alqoritm yaranmağa başlayır:

Nöqtələrin koordinatlarını bilirikmi? Biz bilirik. Məbləğlər tapa bilerik? Asanlıqla. Ən sadəini edək iki naməlumda iki xətti tənlik sistemi(“a” və “olmaq”). Sistemi həll edirik, məsələn, Kramer üsulu, bunun nəticəsində stasionar nöqtə əldə edirik. Yoxlama ekstremum üçün kifayət qədər şərtdir, biz bu nöqtədə funksiyanın olduğunu yoxlaya bilərik dəqiq çatır minimum. Çek əlavə hesablamaları əhatə edir və buna görə də onu pərdə arxasında qoyacağıq (lazım olduqda, çatışmayan çərçivəyə baxmaq olar). Son nəticəni çıxarırıq:

Funksiya ən yaxşı yol (ən azı hər hansı digər xətti funksiya ilə müqayisədə) eksperimental nöqtələri yaxınlaşdırır . Kobud desək, onun qrafiki bu nöqtələrə mümkün qədər yaxın keçir. Ənənədə ekonometriya yaranan yaxınlaşma funksiyası da adlanır qoşalaşmış xətti reqressiya tənliyi .

Baxılan problem böyükdür praktik əhəmiyyəti. Bizim nümunə vəziyyətimizdə, Eq. hansı ticarət dövriyyəsini proqnozlaşdırmağa imkan verir ("İqrek") mağaza satış sahəsinin bu və ya digər dəyərinə sahib olacaq (“x”in bu və ya digər mənası). Bəli, ortaya çıxan proqnoz yalnız bir proqnoz olacaq, lakin bir çox hallarda kifayət qədər dəqiq olacaq.

Mən "real" nömrələrlə yalnız bir problemi təhlil edəcəyəm, çünki orada heç bir çətinlik yoxdur - bütün hesablamalar səviyyədədir məktəb kurikulumu 7-8 sinif. 95 faiz hallarda sizdən sadəcə xətti funksiyanı tapmağınız xahiş olunacaq, lakin məqalənin ən sonunda optimal hiperbolanın, eksponensialın və bəzi digər funksiyaların tənliklərini tapmaq daha çətin olmadığını göstərəcəyəm.

Əslində, yalnız vəd edilmiş yaxşılıqları yaymaq qalır - belə nümunələri yalnız dəqiq deyil, həm də tez həll etməyi öyrənə bilərsiniz. Standartı diqqətlə öyrənirik:

Tapşırıq

İki göstərici arasındakı əlaqənin öyrənilməsi nəticəsində aşağıdakı cüt ədədlər əldə edilmişdir:

Ən kiçik kvadratlar metodundan istifadə edərək, empirikə ən yaxşı yaxınlaşan xətti funksiyanı tapın (təcrübəli) data. Dekart düzbucaqlı koordinat sistemində təcrübi nöqtələrin və yaxınlaşma funksiyasının qrafikinin qurulması üçün çertyoj çəkin. . Empirik və nəzəri qiymətlər arasındakı kvadratik kənarlaşmaların cəmini tapın. Xüsusiyyətin daha yaxşı olub olmadığını öyrənin (ən kiçik kvadratlar metodu baxımından) eksperimental nöqtələri yaxınlaşdırın.

Nəzərə alın ki, “x” mənaları təbiidir və bunun xarakterik mənalı mənası var ki, bu barədə bir az sonra danışacağam; lakin onlar, əlbəttə, fraksiya da ola bilər. Bundan əlavə, müəyyən bir tapşırığın məzmunundan asılı olaraq həm "X", həm də "oyun" dəyərləri tamamilə və ya qismən mənfi ola bilər. Yaxşı, bizə “simasız” tapşırıq verildi və biz ona başlayırıq həll:

Sistemin həlli kimi optimal funksiyanın əmsallarını tapırıq:

Daha yığcam qeyd etmək üçün "sayıcı" dəyişəni buraxıla bilər, çünki toplamanın 1-dən -ə qədər aparıldığı artıq aydındır.

Tələb olunan məbləğləri cədvəl şəklində hesablamaq daha rahatdır:

Hesablamalar mikrokalkulyatorda aparıla bilər, lakin Excel-dən istifadə etmək daha yaxşıdır - həm daha sürətli, həm də səhvsiz; qısa videoya baxın:

Beləliklə, aşağıdakıları əldə edirik sistemi:

Burada ikinci tənliyi 3 və vura bilərsiniz 1-ci tənliyin həddi ilə 2-cini çıxarın. Ancaq bu şansdır - praktikada sistemlər çox vaxt hədiyyə deyil və belə hallarda qənaət edir Kramer üsulu:
, yəni sistemin unikal həlli var.

yoxlayaq. Başa düşürəm ki, istəmirsiniz, amma niyə tamamilə qaçırılmayan səhvləri atlayın? Tapılan həlli sistemin hər bir tənliyinin sol tərəfində əvəz edək:

Müvafiq tənliklərin sağ tərəfləri alınır ki, bu da sistemin düzgün həll edildiyini bildirir.

Beləliklə, istədiyiniz yaxınlaşma funksiyası: – dən bütün xətti funksiyalar Eksperimental məlumatları ən yaxşı şəkildə təxmin edən odur.

Fərqli düz mağazanın dövriyyəsinin onun sahəsindən asılılığı, tapılan asılılıqdır tərs ("nə qədər çox, bir o qədər az" prinsipi), və bu fakt dərhal mənfi ilə üzə çıxır yamac. Funksiya müəyyən bir göstəricinin 1 vahid artması ilə asılı göstəricinin dəyərinin azaldığını söyləyir orta 0,65 vahid. Necə deyərlər, qarabaşaq yarmasının qiyməti nə qədər bahadırsa, o qədər az satılır.

Təxmini funksiyanın qrafikini çəkmək üçün onun iki qiymətini tapırıq:

və rəsmini yerinə yetirin:

Qurulmuş düz xətt deyilir trend xətti (yəni xətti trend xətti, yəni ümumi halda trend mütləq düz xətt deyil). “Trenddə olmaq” ifadəsi ilə hər kəs tanışdır və hesab edirəm ki, bu terminin əlavə şərhə ehtiyacı yoxdur.

Kvadrat sapmaların cəmini hesablayaq empirik və nəzəri dəyərlər arasında. Həndəsi olaraq bu, "moruq" seqmentlərinin uzunluqlarının kvadratlarının cəmidir (onlardan ikisi o qədər kiçikdir ki, hətta görünmür).

Hesablamaları cədvəldə ümumiləşdirək:

Yenə də, onlar əl ilə edilə bilər; hər halda, 1-ci nöqtə üçün bir nümunə verəcəyəm:

lakin bunu artıq məlum üsulla etmək daha effektivdir:

Bir daha təkrar edirik: Əldə edilən nəticənin mənası nədir? From bütün xətti funksiyalar y funksiyası göstərici ən kiçikdir, yəni ailəsində ən yaxşı yaxınlaşmadır. Və burada, yeri gəlmişkən, problemin son sualı təsadüfi deyil: əgər təklif olunan eksponensial funksiya eksperimental nöqtələri yaxınlaşdırmaq daha yaxşı olardı?

Kvadrat sapmaların müvafiq cəmini tapaq - ayırd etmək üçün onları "epsilon" hərfi ilə işarələyəcəyəm. Texnika tamamilə eynidır:

Və yenə də, hər halda, 1-ci nöqtə üçün hesablamalar:

Excel-də biz standart funksiyadan istifadə edirik EXP (sintaksis Excel Yardımında tapıla bilər).

Nəticə: , bu o deməkdir ki, eksponensial funksiya düz xəttdən daha pis eksperimental nöqtələrə yaxınlaşır. .

Ancaq burada qeyd etmək lazımdır ki, “daha pis” hələ demək deyil, səhv nədir. İndi mən bu eksponensial funksiyanın qrafikini qurmuşam və o da nöqtələrə yaxın keçir - o qədər ki, analitik araşdırma olmadan hansı funksiyanın daha dəqiq olduğunu söyləmək çətindir.

Bu, həlli yekunlaşdırır və mən mübahisənin təbii dəyərləri sualına qayıdıram. Müxtəlif tədqiqatlarda, adətən iqtisadi və ya sosioloji, təbii “X”lər ayları, illəri və ya digər bərabər zaman intervallarını saymaq üçün istifadə olunur. Məsələn, aşağıdakı problemi nəzərdən keçirək.

Ən kiçik kvadratlar metodu (OLS) reqressiya təhlili sahəsinə aiddir. 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, 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 y = a * x + b * düz xətti uyğundur, hansı reqressiya modeli Sözügedən nümunə üçün. Ə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ı

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.

Ən kiçik kvadratlar metodu (LS) öyrənilən 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 istifadə edilə bilər aşağıdakı düsturlar(santimetr. 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 = düsturundan 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ərlə üst-üstə düşdüyünə ə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ə ediləcək, bu da səhv nəticəyə səbəb olacaqdır (əlbəttə ki, əgər faktiki dəyərlər X ardıcıllıqla 1-ə uyğun gəlmir; 2; 3; ...).

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:

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

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ı

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.