Ең кіші квадраттар әдісі (OLS) регрессиялық талдау саласына жатады. Оның көптеген қолданбалары бар, өйткені ол берілген функцияны басқа қарапайымдарымен шамамен көрсетуге мүмкіндік береді. LSM бақылауларды өңдеуде өте пайдалы болуы мүмкін және ол кездейсоқ қателерді қамтитын басқаларының өлшеу нәтижелеріне негізделген кейбір шамаларды бағалау үшін белсенді қолданылады. Бұл мақалада сіз Excel бағдарламасында ең кіші квадраттарды есептеуді қалай орындау керектігін үйренесіз.

Нақты мысалды пайдалана отырып, мәселені тұжырымдау

Екі X және Y көрсеткіші бар делік. Сонымен қатар, Y X-ға тәуелді. OLS бізді регрессиялық талдау тұрғысынан қызықтыратындықтан (Excel-де оның әдістері кіріктірілген функцияларды қолдану арқылы жүзеге асырылады), біз дереу мынаны қарастыруға көшуіміз керек. нақты мәселе.

Сонымен, X шаршы метрмен өлшенетін азық-түлік дүкенінің бөлшек сауда алаңы, ал Y миллион рубльмен өлшенетін жылдық тауар айналымы болсын.

Дүкенде осы немесе басқа сауда орындары болса, онда қандай айналым (Y) болатынын болжау талап етіледі. Гипермаркет дүңгіршекке қарағанда көбірек тауар сататындықтан, Y = f (X) функциясы өсетіні анық.

Болжау үшін пайдаланылатын бастапқы деректердің дұрыстығы туралы бірнеше сөз

Бізде n дүкенге арналған деректер арқылы құрастырылған кесте бар делік.

Математикалық статистикаға сәйкес, кем дегенде 5-6 нысан бойынша деректер зерттелсе, нәтиже азды-көпті дұрыс болады. Сонымен қатар, «аномальды» нәтижелерді пайдалану мүмкін емес. Атап айтқанда, элиталық шағын бутиктің «масмаркет» класындағы ірі сауда нүктелерінің айналымынан бірнеше есе артық айналымы болуы мүмкін.

Әдістің мәні

Кесте деректерін декарттық жазықтықта M 1 (x 1, y 1), ... M n (x n, y n) нүктелері түрінде бейнелеуге болады. Енді есептің шешімі M 1, M 2, .. M n нүктелеріне мүмкіндігінше жақын өтетін графигі бар y = f (x) жуықтау функциясын таңдауға келтіріледі.

Әрине, сіз көпмүшені пайдалана аласыз жоғары дәреже, бірақ бұл опцияны жүзеге асыру қиын ғана емес, сонымен қатар жай дұрыс емес, өйткені ол анықталуы керек негізгі трендті көрсетпейді. Ең ақылға қонымды шешім - эксперименттік мәліметтерді, дәлірек айтқанда, a және b коэффициенттерін ең жақсы жуықтайтын y = ax + b түзуін іздеу.

Дәлдік бағалау

Кез келген жуықтау кезінде оның дәлдігін бағалау ерекше маңызға ие. x i нүктесі үшін функционалдық және тәжірибелік мәндер арасындағы айырмашылықты (ауытқуды) e i арқылы белгілейік, яғни e i = y i - f (x i).

Әлбетте, жуықтау дәлдігін бағалау үшін сіз ауытқулар қосындысын пайдалана аласыз, яғни Х-тің Y-ге тәуелділігін шамамен көрсету үшін түзу сызықты таңдағанда, ең аз мәні барға артықшылық беру керек. барлық қарастырылатын нүктелердегі сома e i. Дегенмен, бәрі оңай емес, өйткені оң ауытқулармен қатар теріс де болады.

Мәселені ауытқу модульдері немесе олардың квадраттары арқылы шешуге болады. Соңғы әдіс ең кең таралған. Ол көптеген салаларда қолданылады, соның ішінде регрессиялық талдау (Excel-де екі кірістірілген функцияны пайдалана отырып жүзеге асырылады) және өзінің тиімділігін бұрыннан дәлелдеген.

Ең кіші квадрат әдісі

Өздеріңіз білетіндей, Excel бағдарламасында таңдалған ауқымда орналасқан барлық мәндердің мәндерін есептеуге мүмкіндік беретін кірістірілген AutoSum функциясы бар. Осылайша, бізге өрнектің мәнін есептеуге ештеңе кедергі болмайды (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Математикалық белгілерде бұл келесідей көрінеді:

Бастапқыда түзу сызықты қолдану арқылы жуықтау туралы шешім қабылданғандықтан, бізде:

Осылайша, X және Y шамаларының нақты тәуелділігін ең жақсы сипаттайтын түзуді табу міндеті екі айнымалы функцияның минимумын есептеуге келеді:

Ол үшін жаңа a және b айнымалыларына қатысты жартылай туындыларды нөлге теңестіріп, 2 белгісізі бар екі теңдеуден тұратын қарабайыр жүйені шешу керек:

Бірнеше қарапайым түрлендірулерден кейін, соның ішінде 2-ге бөлу және қосындыларды өңдеу, біз мынаны аламыз:

Оны шешу, мысалы, Крамер әдісін қолдана отырып, біз белгілі бір коэффициенттері бар стационарлық нүктені аламыз * және b *. Бұл ең аз, яғни белгілі бір аймақ үшін дүкеннің қандай айналымы болатынын болжау үшін y = a * x + b * түзу сызығы қолайлы, бұл мысал үшін регрессия үлгісі болып табылады. Әрине, бұл сізге нақты нәтижені табуға мүмкіндік бермейді, бірақ бұл сізге белгілі бір аймақты дүкен несиесіне сатып алу өте тиімді болатыны туралы түсінік алуға көмектеседі.

Excel бағдарламасында ең кіші квадраттарды қалай жүзеге асыруға болады

Excel бағдарламасында ең кіші квадраттарды пайдаланып мәндерді есептеу функциясы бар. Оның келесі пішіні бар: «TREND» (белгілі Y мәндері; белгілі X мәндері; жаңа Х мәндері; тұрақты). Excel бағдарламасында OLS есептеу формуласын кестемізге қолданайық.

Ол үшін Excel бағдарламасындағы ең кіші квадраттар әдісімен есептеу нәтижесі көрсетілетін ұяшыққа «=» белгісін енгізіп, «TREND» функциясын таңдаңыз. Ашылған терезеде тиісті өрістерді толтырып, бөлектеңіз:

• Y үшін белгілі мәндер ауқымы (бұл жағдайда тауар айналымы туралы деректер);
• диапазон x 1 , …x n , яғни сауда алаңының көлемі;
• x-тің белгілі және белгісіз мәндері, ол үшін айналым көлемін білу қажет (олардың жұмыс парағында орналасуы туралы ақпаратты төменде қараңыз).

Сонымен қатар, формулада «Const» логикалық айнымалысы бар. Сәйкес өріске 1 енгізсеңіз, бұл b = 0 деп есептей отырып, есептеулерді орындау керек дегенді білдіреді.

Егер сізге бірнеше x мәніне болжамды білу қажет болса, формуланы енгізгеннен кейін «Enter» пернесін баспау керек, бірақ пернетақтада «Shift» + «Control» + «Enter» тіркесімін теру керек.

Кейбір мүмкіндіктер

Регрессиялық талдау тіпті манекендерге де қол жетімді болуы мүмкін. Белгісіз айнымалылар массивінің мәнін болжауға арналған Excel формуласын - TREND - тіпті ең кіші квадраттар туралы ешқашан естімегендер де пайдалана алады. Оның жұмысының кейбір ерекшеліктерін білу жеткілікті. Сондай-ақ:

• Егер сіз y айнымалысының белгілі мәндерінің ауқымын бір жолға немесе бағанға орналастырсаңыз, онда x белгілі мәндері бар әрбір жолды (бағанды) бағдарлама жеке айнымалы ретінде қабылдайды.
• TREND терезесінде белгілі x диапазоны көрсетілмесе, Excel бағдарламасында функцияны пайдаланған кезде бағдарлама оны бүтін сандардан тұратын массив ретінде қарастырады, олардың саны берілген мәндері бар диапазонға сәйкес келеді. айнымалы y.
• «Болжамды» мәндердің массивін шығару үшін трендті есептеуге арналған өрнек массив формуласы ретінде енгізілуі керек.
• Егер x-тің жаңа мәндері көрсетілмесе, TREND функциясы оларды белгілі мәндерге тең деп санайды. Егер олар көрсетілмесе, онда аргумент ретінде 1-массив алынады; 2; 3; 4;…, ол бұрыннан көрсетілген y параметрлері бар диапазонға сәйкес.
• Жаңа x мәндерін қамтитын ауқымда берілген y мәндерін қамтитын ауқыммен бірдей немесе бірнеше жолдар немесе бағандар болуы керек. Басқаша айтқанда, ол тәуелсіз айнымалыларға пропорционалды болуы керек.
• Белгілі x мәндері бар массив бірнеше айнымалыларды қамтуы мүмкін. Алайда, егер біз тек біреуі туралы айтатын болсақ, онда x және y берілген мәндері бар диапазондар пропорционалды болуы керек. Бірнеше айнымалы болған жағдайда, берілген y мәндері бар диапазон бір бағанға немесе бір жолға сәйкес келуі керек.

БОЛЖАУ функциясы

Excel бағдарламасындағы регрессиялық талдау бірнеше функцияларды қолдану арқылы жүзеге асырылады. Солардың бірі «Болжау» деп аталады. Ол «TREND-ке» ұқсас, яғни ол ең кіші квадраттар әдісін қолданатын есептеулер нәтижесін береді. Алайда Y мәні белгісіз бір Х үшін ғана.

Енді сіз Excel бағдарламасында сызықтық тренд бойынша белгілі бір көрсеткіштің болашақ мәнін болжауға мүмкіндік беретін манекендерге арналған формулаларды білесіз.

Ең кіші квадраттар әдісі – екі қатар сандар жиынына ең дәл сәйкес келетін сызықтық теңдеуді құрудың математикалық процедурасы. Бұл әдісті қолданудың мақсаты жалпы квадрат қатесін азайту болып табылады. Excel бағдарламасында бұл әдісті есептеулеріңізге қолдануға көмектесетін құралдар бар. Мұның қалай жасалатынын анықтап көрейік.

· Excel бағдарламасында әдісті қолдану

o «Шешім іздеу» қосымшасын қосу

o Проблемалық жағдайлар

o Шешім

Excel бағдарламасындағы әдісті қолдану

Ең кіші квадраттар әдісі (LSM) бір айнымалының екіншісіне тәуелділігінің математикалық сипаттамасы болып табылады. Оны болжау үшін пайдалануға болады.

Шешімді табу қондырмасын қосу

Excel бағдарламасында MNC пайдалану үшін қондырманы қосу керек «Шешімін табу», ол әдепкі бойынша өшірілген.

1. Қойындыға өтіңіз «Файл».

2. Бөлім атауын басыңыз «Опциялар».

3. Ашылған терезеде ішкі бөлімді таңдаңыз «Қосымшалар».

4. Блокта «Бақылау», ол терезенің төменгі жағында орналасқан, қосқышты күйіне қойыңыз «Excel қондырмалары»(егер оның мәні басқа болса) және түймені басыңыз «Барыңыз...».

5. Шағын терезе ашылады. Параметрдің жанына құсбелгі қоямыз «Шешімін табу». Түймені басыңыз «ЖАРАЙДЫ МА».

Енді функция Шешімін табу Excel бағдарламасында іске қосылады және оның құралдары таспада пайда болады.

Сабақ: Excel бағдарламасында шешім табу

Мәселенің жағдайлары

Арнайы мысал арқылы LSM пайдалануды сипаттап көрейік. Бізде екі қатар сандар бар xЖәне ж, оның реті төмендегі суретте көрсетілген.

Бұл тәуелділікті ең дәл функция арқылы сипаттауға болады:

Сонымен бірге қашан екені белгілі x=0 yда тең 0 . Сондықтан бұл теңдеуді тәуелділік арқылы сипаттауға болады y=nx.

Айырмашылық квадраттарының ең аз қосындысын табуымыз керек.

Шешім

Әдістің тікелей қолданылуының сипаттамасына көшейік.

1. Бірінші мәннің сол жағында xнөмір қойыңыз 1 . Бұл бірінші коэффициент мәнінің жуық мәні болады n.

2. Бағанның оң жағында жбасқа баған қосыңыз - nx. Осы бағанның бірінші ұяшығына коэффициентті көбейту формуласын жазамыз nбірінші айнымалының ұяшығына x. Сонымен қатар, абсолютті коэффициенті бар өріске сілтеме жасаймыз, өйткені бұл мән өзгермейді. Түймені басыңыз Енгізіңіз.

3. Толтыру маркерінің көмегімен осы формуланы төмендегі бағандағы кестенің барлық ауқымына көшіріңіз.

4. Бөлек ұяшықта мәндердің квадраттарының арасындағы айырмашылықтардың қосындысын есептеңіз жЖәне nx. Мұны істеу үшін түймені басыңыз «Функцияны енгізу».

5. Ашылған жерде «Функция шебері»жазба іздейді «СУММКВАРНА». Оны таңдап, түймені басыңыз «ЖАРАЙДЫ МА».

6. Аргументтер терезесі ашылады. Алаңда "x массиві" ж. Алаңда "массив_y"баған ұяшықтарының ауқымын енгізіңіз nx. Мәндерді енгізу үшін жай ғана курсорды өріске қойып, парақта сәйкес ауқымды таңдаңыз. Енгізгеннен кейін түймені басыңыз «ЖАРАЙДЫ МА».

7. Қойындыға өтіңіз «Деректер». Құралдар жәшігіндегі таспада «Талдау»түймешігін басыңыз «Шешімін табу».

8. Осы құралдың параметрлер терезесі ашылады. Алаңда «Мақсат функциясын оңтайландыру»ұяшықтың адресін формуламен көрсетіңіз «СУММКВАРНА». Параметрде «Бұрын»ауыстырып-қосқышты күйіне қоюды ұмытпаңыз «Ең аз». Алаңда «Жасушаларды өзгерту»коэффициент мәні бар мекенжайды көрсетіңіз n. Түймені басыңыз «Шешімін тап».

9. Шешім коэффициент ұяшығында көрсетіледі n. Бұл мән функцияның ең кіші квадраты болады. Егер нәтиже пайдаланушыны қанағаттандырса, түймені басыңыз «ЖАРАЙДЫ МА»қосымша терезеде.

Көріп отырғаныңыздай, ең кіші квадраттар әдісін қолдану өте күрделі математикалық процедура. Біз оны қарапайым мысал арқылы іс жүзінде көрсеттік, бірақ әлдеқайда күрделі жағдайлар бар. Дегенмен, Microsoft Excel құралдары есептеулерді мүмкіндігінше жеңілдетуге арналған.

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

Жалпы ережелер

Абсолюттік мәндегі сан неғұрлым аз болса, соғұрлым таңдалған түзу жақсы болады (2). Түзу сызықты (2) таңдау дәлдігінің сипаттамасы ретінде квадраттардың қосындысын алуға болады.

S үшін минималды шарттар болады

	(6)
	(7)

(6) және (7) теңдеулерді былай жазуға болады:

	(8)
	(9)

(8) және (9) теңдеулерінен xi және y i эксперименттік мәндерінен a және b табу оңай. (8) және (9) теңдеулерімен анықталатын (2) жол ең кіші квадраттар әдісімен алынған түзу деп аталады (бұл атау S квадраттарының қосындысының минимумы бар екенін атап көрсетеді). Түзу (2) анықталатын (8) және (9) теңдеулер қалыпты теңдеулер деп аталады.

Қалыпты теңдеулерді құрудың қарапайым және жалпы әдісін көрсетуге болады. Эксперименттік нүктелер (1) және (2) теңдеуін пайдаланып, a және b үшін теңдеулер жүйесін жаза аламыз

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

Осы теңдеулердің әрқайсысының сол және оң жақтарын бірінші белгісіз а коэффициентіне (яғни x 1, x 2, ..., x n) көбейтіп, алынған теңдеулерді қосайық, нәтижесінде бірінші қалыпты теңдеу (8) шығады. .

Осы теңдеулердің әрқайсысының сол және оң жақтарын екінші белгісіз b коэффициентіне көбейтейік, яғни. 1-ге, және алынған теңдеулерді қоссақ, нәтиже екінші қалыпты теңдеу (9) болады.

Қалыпты теңдеулерді алудың бұл әдісі жалпы: ол, мысалы, функция үшін қолайлы

тұрақты шама бар және оны тәжірибелік мәліметтерден анықтау керек (1).

k үшін теңдеулер жүйесін жазуға болады:

Ең кіші квадраттар әдісін қолданып (2) түзуді табыңыз.

Шешім.Біз табамыз:

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

(8) және (9)91a+21b=179,1 теңдеулерін жазамыз,

21a+6b=46,3, осы жерден табамыз
a=0,98 b=4,3.

Ең кіші квадрат әдісірегрессия теңдеуінің параметрлерін бағалау үшін қолданылады.

Сипаттамалар арасындағы стохастикалық байланыстарды зерттеу әдістерінің бірі регрессиялық талдау болып табылады.
Регрессиялық талдау – регрессия теңдеуінің туындысы, оның көмегімен басқа (немесе басқа) айнымалылардың (фактор-атрибуттардың) мәні белгілі болса, кездейсоқ шаманың (нәтиже атрибутының) орташа мәні табылады. Ол келесі қадамдарды қамтиды:

1. байланыс формасын таңдау (аналитикалық регрессия теңдеуінің түрі);
2. теңдеу параметрлерін бағалау;
3. аналитикалық регрессия теңдеуінің сапасын бағалау.

Көбінесе белгілердің статистикалық байланысын сипаттау үшін сызықтық форма қолданылады. Сызықтық қатынастарға назар аудару оның параметрлерінің нақты экономикалық түсіндірмесімен, айнымалылардың шектеулі өзгеруімен және көп жағдайда байланыстардың сызықтық емес формаларының есептеулерді орындау үшін сызықтық түрге (логарифм немесе айнымалыларды ауыстыру арқылы) түрлендіруімен түсіндіріледі. .
Сызықтық жұптық қатынас жағдайында регрессия теңдеуі келесі формада болады: y i =a+b·x i +u i . Бұл теңдеудің a және b параметрлері x және y статистикалық бақылау деректерінен бағаланады. Мұндай бағалаудың нәтижесі теңдеу болып табылады: , мұндағы , a және b параметрлерінің бағалаулары , регрессия теңдеуінен алынған нәтиже атрибутының (айнымалының) мәні (есептелген мән).

Көбінесе параметрлерді бағалау үшін қолданылады Ең кіші квадраттар әдісі (LSM).
Ең аз квадраттар әдісі регрессия теңдеуінің параметрлерінің ең жақсы (тұрақты, тиімді және бейтарап) бағасын береді. Бірақ (u) және тәуелсіз айнымалыға (x) қатысты белгілі бір болжамдар орындалса ғана (OLS жорамалдарын қараңыз).

Ең кіші квадраттар әдісі арқылы сызықтық жұп теңдеудің параметрлерін бағалау мәселесікелесідей: параметрлердің осындай бағалауларын алу үшін , нәтижелі сипаттаманың нақты мәндерінің квадраттық ауытқуларының қосындысы есептелген мәндерден - y i - минималды болады.
Ресми түрде OLS критерийібылай жазуға болады: .

Ең кіші квадраттар әдістерінің классификациясы

1. Ең кіші квадрат әдісі.
2. Максималды ықтималдық әдісі (қалыпты классикалық сызықтық регрессия моделі үшін регрессия қалдықтарының қалыптылығы қойылған).
3. Жалпыланған ең кіші квадраттар OLS әдісі қателердің автокорреляциясы кезінде және гетероскедастикалық жағдайда қолданылады.
4. Салмақталған ең кіші квадраттар әдісі (гетероскедастық қалдықтары бар OLS ерекше жағдайы).

Мәселені мысалға келтірейік Классикалық ең кіші квадраттар әдісі. Ол үшін тікбұрышты координаталар жүйесінде бақылау деректеріне (x i, y i, i=1;n) негізделген шашырау сызбасын тұрғызамыз (мұндай шашырауды корреляциялық өріс деп атайды). Корреляция өрісінің нүктелеріне ең жақын түзу сызықты таңдауға тырысайық. Ең кіші квадраттар әдісі бойынша сызық корреляция өрісінің нүктелері мен осы сызық арасындағы тік қашықтықтардың квадраттарының қосындысы минималды болатындай етіп таңдалады.

Бұл есептің математикалық белгісі: .
y i және x i =1...n мәндері бізге белгілі, бұл бақылау деректері. S функциясында олар тұрақты мәндерді көрсетеді. Бұл функциядағы айнымалылар - , параметрлерінің қажетті бағалаулары болып табылады. Екі айнымалы функцияның минимумын табу үшін әрбір параметр үшін осы функцияның ішінара туындыларын есептеп, оларды нөлге теңестіру керек, яғни. .
Нәтижесінде біз 2 қалыпты сызықтық теңдеу жүйесін аламыз:
Бұл жүйені шеше отырып, қажетті параметрлік бағалауларды табамыз:

Регрессия теңдеуінің параметрлерін есептеудің дұрыстығын шамаларды салыстыру арқылы тексеруге болады (есептеулерді дөңгелектеуге байланысты кейбір сәйкессіздіктер болуы мүмкін).
Параметрлік бағалауларды есептеу үшін 1-кестені құрастыруға болады.
b регрессия коэффициентінің таңбасы қатынастың бағытын көрсетеді (егер b >0 болса, байланыс тура, егер b болса<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формальды түрде а параметрінің мәні х нөлге тең y орташа мәні болып табылады. Егер атрибут-фактор нөлдік мәнге ие болмаса және мүмкін болмаса, онда а параметрінің жоғарыдағы түсіндірмесі мағынасы жоқ.

Сипаттамалар арасындағы байланыстың жақындығын бағалау сызықтық жұптық корреляция коэффициенті - r x,y көмегімен жүзеге асырылады. Оны формула бойынша есептеуге болады: . Сонымен қатар сызықтық жұп корреляция коэффициентін b регрессия коэффициенті арқылы анықтауға болады: .
Сызықтық жұп корреляция коэффициентінің рұқсат етілген мәндерінің диапазоны –1-ден +1-ге дейін. Корреляция коэффициентінің таңбасы қатынастың бағытын көрсетеді. Егер r x, y >0 болса, онда байланыс тікелей болады; егер r x, y<0, то связь обратная.
Егер бұл коэффициент шама бойынша бірлікке жақын болса, онда сипаттамалар арасындағы байланысты өте жақын сызықтық деп түсіндіруге болады. Егер оның модулі бір ê r x , y ê =1 тең болса, онда сипаттамалар арасындағы байланыс функционалды сызықты болады. Егер x және y мүмкіндіктері сызықтық тәуелсіз болса, онда r x,y 0-ге жақын болады.
r x,y есептеу үшін 1-кестені де пайдалануға болады.

Алынған регрессия теңдеуінің сапасын бағалау үшін детерминацияның теориялық коэффициентін есептеңіз - R 2 yx:

,
мұндағы d 2 – регрессия теңдеуімен түсіндірілетін у дисперсиясы;
e 2 - y-ның қалдық (регрессия теңдеуімен түсіндірілмеген) дисперсиясы;
s 2 y - у-ның жалпы (жалпы) дисперсиясы.
Детерминация коэффициенті y жалпы вариациядағы (дисперсиялық) регрессиямен (және, демек, х факторымен) түсіндірілетін нәтижелік y атрибутының вариациясының (дисперсиясының) үлесін сипаттайды. R 2 yx анықтау коэффициенті 0-ден 1-ге дейінгі мәндерді қабылдайды. Сәйкесінше, 1-R 2 yx мәні үлгіде және спецификациядағы қателерде ескерілмеген басқа факторлардың әсерінен туындаған у дисперсиясының үлесін сипаттайды.
Жұптастырылған сызықтық регрессиямен R 2 yx =r 2 yx.

Ең кіші квадраттар әдісі (LSM)

n белгісізі бар m сызықтық теңдеулер жүйесі келесі түрде болады:

Үш жағдай мүмкін: m n. Алдыңғы абзацтарда m=n қарастырылған жағдай. Қашан м

Егер m>n және жүйе дәйекті болса, онда А матрицасының кем дегенде m - n сызықты тәуелді жолдары болады. Мұнда шешімді кез келген сызықтық тәуелсіз n теңдеулерді таңдау арқылы (егер олар бар болса) және X = A -1 CV формуласын қолдану арқылы, яғни есепті бұрын шешілгенге келтіру арқылы алуға болады. Бұл жағдайда алынған шешім әрқашан қалған m - n теңдеулерін қанағаттандырады.

Дегенмен, компьютерді пайдалану кезінде неғұрлым жалпы тәсілді – ең кіші квадраттар әдісін қолданған ыңғайлы.

Алгебралық ең кіші квадраттар әдісі

Алгебралық ең кіші квадраттар әдісі сызықтық теңдеулер жүйесін шешу әдісі болып табылады

Евклид нормасын азайту арқылы

Балта? б? >inf. (1.2)

Эксперименттік мәліметтерді талдау

Уақыт сәтінде болатын тәжірибені қарастырайық

Мысалы, Q(t) температурасы өлшенеді. Өлшеу нәтижелері массив арқылы белгіленсін

Тәжірибе жағдайлары өлшеулер белгілі қателікпен орындалатындай деп алайық. Бұл жағдайларда температураның өзгеру заңы Q(t) белгілі бір көпмүшені пайдаланып ізделеді

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

белгісіз коэффициенттерді анықтау, ..., мәні E(, ...,), теңдігімен анықталған

Гаусс алгебралық эксель жуықтауы

ең төменгі мәнді алды. Квадраттардың қосындысы кішірейтілгендіктен, бұл әдіс деректерге ең кіші квадраттарды жақындату деп аталады.

Егер P(t)-ті оның өрнегімен ауыстырсақ, аламыз

Массивті анықтау тапсырмасын мән минималды болатындай етіп қойайық, яғни. Ең кіші квадраттар әдісі арқылы массивті анықтайық. Ол үшін жартылай туындыларды нөлге теңестіреміз:

m × n матрицасын енгізсеңіз A = (), i = 1, 2..., m; j = 1, 2, ..., n, мұндағы

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

онда жазбаша теңдік пішінді алады

Жазбаша теңдікті матрицалармен амалдар тұрғысынан қайта жазайық. Матрицаны бағанға көбейту анықтамасы бойынша бізде бар

Транспозицияланған матрица үшін ұқсас қатынас келесідей көрінеді

Белгілеуді енгізейік: Ax векторының i-ші компонентін белгілейміз Жазылған матрицалық теңдіктерге сәйкес бізде

Матрицалық пішінде бұл теңдік келесі түрде қайта жазылуы мүмкін

A T x=A T B (1.3)

Мұндағы А – төртбұрышты m×n матрицасы. Сонымен қатар, мәліметтерді жуықтау есептерінде, әдетте, m > n. (1.3) теңдеу қалыпты теңдеу деп аталады.

Евклидтік векторлар нормасын қолданып, есепті эквивалентті матрицалық түрде жазу ең басынан мүмкін болды:

Біздің мақсатымыз бұл функцияны х-де азайту. Шешім нүктесінде минимумға жету үшін осы нүктедегі х-ке қатысты бірінші туындылар нөлге тең болуы керек. Бұл функцияның туындылары

2A T B + 2A T Ax

сондықтан шешімі сызықтық теңдеулер жүйесін қанағаттандыруы керек

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

Бұл теңдеулер қалыпты теңдеулер деп аталады. Егер А m× n матрица болса, онда A>A - n × n матрица, яғни. Қалыпты теңдеудің матрицасы әрқашан шаршы симметриялы матрица болады. Оның үстіне оның (A>Ax, x) = (Ax, Ax) ? 0.

Түсініктеме. Кейде (1.3) түріндегі теңдеудің шешімін Ax = B жүйесінің шешімі деп атайды, мұндағы А - ең кіші квадраттар әдісін қолданатын тікбұрышты m × n (m > n) матрица.

Ең кіші квадраттар мәселесін деректер нүктелерінен модель қисығына дейінгі тік қашықтықтарды азайту ретінде графикалық түрде түсіндіруге болады (1.1 суретті қараңыз). Бұл идея жуықтаудағы барлық қателер бақылаулардағы қателерге сәйкес келеді деген болжамға негізделген. Егер тәуелсіз айнымалыларда да қателер болса, онда деректерден модельге дейінгі евклидтік қашықтықты азайту дұрысырақ болуы мүмкін.

Excel бағдарламасындағы MNC

Excel бағдарламасында OLS енгізуге арналған төмендегі алгоритм барлық бастапқы деректер бұрыннан белгілі деп есептейді. Сол жақтағы жүйенің AЧX=B матрицалық теңдеуінің екі жағын А Т жүйесінің транспозицияланған матрицасына көбейтеміз:

A T AX=A T B

Содан кейін сол жақтағы теңдеудің екі жағын (A T A) -1 матрицасына көбейтеміз. Егер бұл матрица бар болса, онда жүйе анықталған. Соны ескере отырып

(A T A) -1 *(A T A)=E, аламыз

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

Алынған матрицалық теңдеу m>n үшін n белгісізі бар m сызықтық теңдеулер жүйесінің шешімі болып табылады.

Жоғарыда аталған алгоритмнің қолданылуын нақты мысал арқылы қарастырайық.

Мысал. Жүйені шешу қажет болсын

Excel бағдарламасында осы мәселеге арналған формуланы көрсету режиміндегі шешім парағы келесідей болады:

Есептеу нәтижелері:

Қажетті X векторы E11:E12 диапазонында орналасқан.

Берілген сызықтық теңдеулер жүйесін шешу кезінде келесі функциялар пайдаланылды:

1. MOBR – массивте сақталған матрица үшін кері матрицаны қайтарады.

Синтаксис: MOBR(массив).

Массив – жолдар мен бағандардың саны бірдей сандық массив.

2. MULTIPULT – матрицалардың көбейтіндісін қайтарады (матрицалар массивтерде сақталады). Нәтиже массив 1 сияқты жолдар саны және массив2 сияқты бағандар саны бірдей массив болып табылады.

Синтаксис: MULTIPLE(массив1,массив2).

Массив1, массив2 - көбейтілетін массивтер.

Жиым ауқымының жоғарғы сол жақ ұяшығына функцияны енгізгеннен кейін формуласы бар ұяшықтан бастап алапты таңдап, F2 пернесін, одан кейін CTRL+SHIFT+ENTER пернелерін басыңыз.

3. ТАСЫМАЛУ – ұяшықтардың тік жиынын көлденеңге немесе керісінше түрлендіреді. Бұл функцияны қолдану нәтижесінде бастапқы массивтің бағандарының санына тең жолдар саны және бастапқы массивтің жолдар санына тең бағандар саны бар массив пайда болады.

4.1. Кірістірілген функцияларды пайдалану

Есептеу регрессия коэффициенттеріфункциясы арқылы жүзеге асырылады

LINEST(Мәндер_y; x-мәндері; Const; статистика),

Мәндер_y- у мәндерінің массиві,

x-мәндері- мәндердің қосымша массиві x, егер массив Xалынып тасталса, бұл өлшемі бірдей массив (1;2;3;...) деп есептеледі. Мәндер_y,

Const- тұрақты мән қажет пе екенін көрсететін логикалық мән б 0-ге тең болды. Егер Constмағынасы бар ШЫНнемесе қабылданбады, онда бкәдімгі әдіспен есептеледі. Аргумент болса Constонда ЖАЛҒАН б 0 және мәндері деп қабылданады ақатынас орындалатындай етіп таңдалады y=ax.

Статистикақосымша регрессия статистикасын қайтару қажет екенін көрсететін логикалық мән. Аргумент болса Статистикамағынасы бар ШЫН, содан кейін функция LINESTқосымша регрессия статистикасын қайтарады. Аргумент болса Статистикамағынасы бар ӨТІРІКнемесе түсірілген, содан кейін функция LINESTтек коэффициентті қайтарады ажәне тұрақты б.

Функциялардың нәтижесі екенін есте ұстаған жөн LINEST()мәндер жиыны – массив.

Есептеу үшін корреляция коэффициентіфункциясы қолданылады

CORREL(Массив 1;Массив 2),

корреляция коэффициентінің мәндерін қайтару, мұнда Массив 1- мәндер массиві ж, Массив 2- мәндер массиві x. Массив 1Және Массив 2өлшемі бірдей болуы керек.

МЫСАЛ 1. Тәуелділік ж(x) кестеде берілген. Құру регрессия сызығыжәне есептеңіз корреляция коэффициенті.

ж		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

MS Excel парағына мәндер кестесін енгізіп, шашырау сызбасын құрайық. Жұмыс парағы суретте көрсетілген пішінді алады. 2.

Регрессия коэффициенттерінің мәндерін есептеу үшін АЖәне бұяшықтарды таңдаңыз A7:B7,Функция шеберіне және санатқа өтейік Статистикалықфункцияны таңдаңыз LINEST. Суретте көрсетілгендей пайда болатын диалогтық терезені толтырайық. 3 және түймесін басыңыз ЖАРАЙДЫ МА.

Нәтижесінде есептелген мән ұяшықта ғана пайда болады A6(Cурет 4). Мән ұяшықта пайда болуы үшін B6өңдеу режиміне кіру керек (перне F2)түймесін басып, пернелер тіркесімін басыңыз CTRL+SHIFT+ENTER.

Ұяшықтағы корреляция коэффициентінің мәнін есептеу C6келесі формула енгізілді:

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

Регрессия коэффициенттерін білу АЖәне бфункция мәндерін есептейік ж=балта+бберілгені үшін x. Ол үшін формуланы енгіземіз

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

және оны ауқымға көшіріңіз C5:J5(Cурет 5).

Диаграммаға регрессия сызығын салайық. Графиктегі эксперименттік нүктелерді таңдап, тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз Бастапқы деректер. Пайда болған диалогтық терезеде (Cурет 5) қойындыны таңдаңыз Қатаржәне түймені басыңыз қосу. Енгізу өрістерін суретте көрсетілгендей толтырайық. 6 және түймесін басыңыз ЖАРАЙДЫ МА. Эксперименттік деректер графигіне регрессия сызығы қосылады. Әдепкі бойынша оның графигі тегістеу сызықтарымен қосылмаған нүктелер ретінде салынады.

Күріш. 6

Регрессия сызығының көрінісін өзгерту үшін келесі қадамдарды орындаңыз. Сызықтық графикті бейнелейтін нүктелерді тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз Диаграмма түріжәне суретте көрсетілгендей шашырау диаграммасының түрін орнатыңыз. 7.

Сызықтың түрін, түсін және қалыңдығын келесідей өзгертуге болады. Диаграммадағы жолды таңдап, тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден пәрменді таңдаңыз Деректер қатарының пішімі...Содан кейін, мысалы, суретте көрсетілгендей параметрлерді жасаңыз. 8.

Барлық түрлендірулер нәтижесінде эксперименттік деректердің графигін және бір графикалық аймақтағы регрессия сызығын аламыз (9-сурет).

4.2. Тренд сызығын пайдалану.

MS Excel-де әртүрлі жуықтау тәуелділіктерін құру диаграмма қасиеті ретінде жүзеге асырылады - тренд сызығы.

МЫСАЛ 2. Тәжірибе нәтижесінде белгілі бір кестелік тәуелділік анықталды.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Жақындаушы тәуелділікті таңдап, құрастыр. Кестелік және таңдалған аналитикалық тәуелділіктердің графиктерін тұрғызыңыз.

Есепті шешуді келесі кезеңдерге бөлуге болады: бастапқы деректерді енгізу, шашырау сызбасын құру және осы графикке тренд сызығын қосу.

Бұл процесті егжей-тегжейлі қарастырайық. Жұмыс парағына бастапқы деректерді енгізіп, эксперименттік мәліметтерді сызып көрейік. Содан кейін графиктегі эксперимент нүктелерін таңдап, тінтуірдің оң жақ түймешігімен басып, пәрменді пайдаланыңыз қосул тренд сызығы(Cурет 10).

Пайда болған диалогтық терезе жуықтау қатынасын құруға мүмкіндік береді.

Бұл терезенің бірінші қойындысы (11-сурет) жуықтау тәуелділігінің түрін көрсетеді.

Екіншісінде (12-сурет) құрылыс параметрлері анықталады:

· жуықтауыштың атауы;

· болжамды алға (артқа) бойынша nбірлік (бұл параметр тренд сызығын алға (артқа) қанша бірлікке ұзарту керектігін анықтайды);

қисық сызықтың түзумен қиылысу нүктесін көрсету керек пе y=const;

· диаграммада жуықтау функциясын көрсету немесе көрсетпеу (диаграммадағы теңдеуді көрсету опциясы);

· диаграммада стандартты ауытқудың мәнін орналастыру керек пе, жоқ па (диаграммаға жуықтау сенімділігінің мәнін орналастыру нұсқасы).

Жақындаушы тәуелділік ретінде екінші дәрежелі көпмүшені таңдап алайық (11-сурет) және осы көпмүшені сипаттайтын теңдеуді графикте көрсетейік (12-сурет). Алынған диаграмма суретте көрсетілген. 13.

Сол сияқты пайдалану тренд сызықтарысияқты тәуелділіктердің параметрлерін таңдауға болады

сызықтық ж=a∙x+б,

логарифмдік ж=a∙ln(x)+б,

· экспоненциалды ж=a∙e b,

· тыныштандырғыш ж=a∙x b,

көпмүшелік ж=a∙x 2 +b∙x+в, ж=a∙x 3 +b∙x 2 +c∙x+dжәне т.б., 6-дәрежелі көпмүшені қоса алғанда,

· сызықтық фильтрация.

4.3. Шешуші блокты қолдану

MS Excel бағдарламасында шешуші блоктың көмегімен ең кіші квадраттар әдісі арқылы параметрлерді таңдауды жүзеге асыру маңызды қызығушылық тудырады. Бұл әдіс кез келген түрдегі функцияның параметрлерін таңдауға мүмкіндік береді. Мысал ретінде келесі есепті пайдалана отырып, бұл мүмкіндікті қарастырайық.

МЫСАЛ 3. Тәжірибе нәтижесінде кестеде берілген z(t) тәуелділігі алынды

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Тәуелділік коэффициенттерін таңдаңыз Z(t)=4 +Bt 3 +Ct 2 +Dt+K кезіндеең кіші квадраттар әдісі.

Бұл есеп бес айнымалы функцияның минимумын табу есебіне тең

Оңтайландыру мәселесін шешу процесін қарастырайық (14-сурет).

Құндылықтар болсын А, IN, МЕН, DЖәне TOжасушаларда сақталады A7:E7. Функцияның теориялық мәндерін есептейік З(т)=4 +Bt 3 +Ct 2 +Dt+K кезіндеберілгені үшін т(B2:J2). Мұны істеу үшін ұяшықта B4бірінші нүктеге функцияның мәнін енгізіңіз (ұяшық B2):

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

Осы формуланы диапазонға көшірейік C4:J4және абсциссалары ұяшықтарда сақталатын нүктелердегі функцияның күтілетін мәнін алыңыз B2:J2.

Ұяшыққа B5Эксперименттік және есептелген нүктелер арасындағы айырмашылықтың квадратын есептейтін формуланы енгізейік:

B5=(B4-B3)^2,

және оны ауқымға көшіріңіз C5:J5. Ұяшықта F7біз жалпы квадраттық қатені сақтаймыз (10). Ол үшін формуланы енгізіңіз:

F7 = СУММ(B5:J5).

Пәрменді қолданайық Service®Шешімді іздеужәне шектеусіз оңтайландыру мәселесін шешу. Суретте көрсетілген диалогтық терезедегі енгізу өрістерін сәйкесінше толтырайық. 14 және түймесін басыңыз Орындау. Егер шешім табылса, терезе суретте көрсетілген. 15.

Шешім блогының нәтижесі ұяшықтарға шығарылады A7:E7параметр мәндеріфункциялары З(т)=4 +Bt 3 +Ct 2 +Dt+K кезінде. Жасушаларда B4:J4Біз алып жатырмыз күтілетін функция мәнібастапқы нүктелерде. Ұяшықта F7сақталады жалпы квадрат қатесі.

Ауқымды таңдау арқылы бір графикалық аймақта тәжірибе нүктелерін және бекітілген сызықты көрсетуге болады B2:J4, қоңырау шалыңыз Диаграмма шеберісодан кейін пішімдеу сыртқы түріграфиктерді алды.

Күріш. 17 есептеулер орындалған соң MS Excel жұмыс парағын көрсетеді.

5. ӘДЕБИЕТТЕР

1. Алексеев Е.Р., Чеснокова О.В., Mathcad12, MATLAB7, Maple9 пакеттерінде есептеу математикасының есептерін шешу. – NT Press, 2006.–596 б. :il. –(Оқулық)

2. Алексеев Е.Р., Чеснокова О.В., Е.А. Рудченко, Сцилаб, инженерлік-математикалық есептерді шешу. –М., БИНОМ, 2008.–260 б.

3. Березин И.С., Жидков Н.П., Есептеу әдістері.– М.: Наука, 1966. – 632 б.

4. Гарнаев А.Ю., MS EXCEL және VBA бағдарламаларын экономика және қаржы саласында қолдану. – Петербург: БХВ – Петербург, 1999.–332 б.

5. Демидович Б.П., Марон И.А., Шувалова В.З., Талдаудың сандық әдістері.– М.: Наука, 1967. – 368 б.

6. Корн Г., Корн Т., Ғалымдар мен инженерлерге арналған математика анықтамалығы.– М., 1970, 720 б.

7. Алексеев Е.Р., Чеснокова О.В. MS EXCEL-де зертханалық жұмыстарды орындау бойынша әдістемелік нұсқаулар. Барлық мамандықтардың студенттеріне арналған. Донецк, ДонНТУ, 2004. 112 б.