Әдіс ең кіші квадраттар(MNC)

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

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

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

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

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

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

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

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

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

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

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

Шешім

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

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

Ең кіші квадраттар әдісі (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).

Әлбетте, жуықтау дәлдігін бағалау үшін сіз ауытқулар қосындысын пайдалана аласыз, яғни X-тің 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 бағдарламасында сызықтық тренд бойынша белгілі бір көрсеткіштің болашақ мәнін болжауға мүмкіндік беретін манекендерге арналған формулаларды білесіз.

Ең кіші квадраттар әдісі (LS) зерттелетін деректерден таңдалған функцияның квадраттық ауытқуларының қосындысын азайтуға негізделген. Бұл мақалада біз қол жетімді деректерді пайдалана отырып, шамамен аламыз сызықтық функция ж = а x + б .

Ең кіші квадрат әдісі(ағылшын) Кәдімгі Ең аз Шаршы , О.Л.С.) белгісіз параметрлерді бағалау тұрғысынан регрессиялық талдаудың негізгі әдістерінің бірі болып табылады регрессия модельдеріүлгі деректеріне сәйкес.

Бір айнымалыға ғана тәуелді функциялар бойынша жуықтауды қарастырайық:

• Сызықтық: y=ax+b (осы мақала)
• : y=a*Ln(x)+b
• : y=a*x м
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Ескерту: Бұл мақалада 3-ден 6-ға дейінгі көпмүше бойынша жуықтау жағдайлары қарастырылады. Мұнда тригонометриялық көпмүше арқылы жуықтау қарастырылады.

Сызықтық тәуелділік

Бізді 2 айнымалы арасындағы байланыс қызықтырады XЖәне ж. деген болжам бар жбайланысты Xсызықтық заңға сәйкес ж = балта + б. Бұл қатынастың параметрлерін анықтау үшін зерттеуші бақылаулар жасады: x i әрбір мәні үшін y i өлшемі жасалды (мысалы файлды қараңыз). Сәйкесінше, 20 жұп мәндер болсын (x i; y i).

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

Диаграммадан айнымалылар арасындағы байланыс сызықтыққа жақын екені анық көрінеді. Көптеген түзулердің қайсысы айнымалылар арасындағы байланысты ең «дұрыс» сипаттайтынын түсіну үшін сызықтар салыстырылатын критерийді анықтау қажет.

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

Қайда ŷ мен = а * x i + б ; n – мәндер жұптарының саны (біздің жағдайда n=20)

Жоғарыда келтірілген өрнек y i және ŷ i бақыланатын мәндерінің арасындағы квадраттық қашықтықтардың қосындысы болып табылады және көбінесе SSE ретінде белгіленеді ( сомасы ның Шаршы Қателер (Қалдықтар), квадрат қателерінің қосындысы (қалдық)) .

Ең кіші квадрат әдісіосындай жолды таңдау болып табылады ŷ = балта + б, ол үшін жоғарыдағы өрнек ең аз мәнді қабылдайды.

Ескерту:Екі өлшемді кеңістіктегі кез келген сызық 2 параметрдің мәндерімен бірегей түрде анықталады: а (еңіс) және б (ауысым).

Квадрат қашықтықтардың қосындысы неғұрлым аз болса, сәйкес сызық соғұрлым жақсырақ қол жетімді деректерге жақындайды және одан әрі х айнымалысынан y мәндерін болжау үшін пайдаланылуы мүмкін деп саналады. Шындығында айнымалылар арасында байланыс болмаса немесе байланыс сызықты емес болса да, OLS бәрібір «ең жақсы» жолды таңдайтыны анық. Осылайша, ең кіші квадраттар әдісі айнымалылар арасында нақты байланыстың болуы туралы ештеңе айтпайды, әдіс жай ғана осындай функция параметрлерін таңдауға мүмкіндік береді. а Және б , ол үшін жоғарыдағы өрнек минималды.

Өте күрделі емес математикалық операцияларды орындау арқылы (толығырақ ақпаратты қараңыз) параметрлерді есептеуге болады а Және б :

Формуладан көрініп тұрғандай, параметр а ковариацияның қатынасын білдіреді және сондықтан MS EXCEL-де параметрді есептеу үшін А Сіз келесі формулаларды пайдалана аласыз (қараңыз Сызықтық парақ мысалы файлы):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45)немесе

= КОВАРИАНС.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Сондай-ақ параметрді есептеу үшін А = формуласын қолдануға болады ҚАЙТА(C26:C45;B26:B45). Параметр үшін б = формуласын қолданыңыз АЯҚ(C26:C45;B26:B45) .

Соңында, LINEST() функциясы екі параметрді де бірден есептеуге мүмкіндік береді. Формула енгізу үшін LINEST(C26:C45;B26:B45)Қатардағы 2 ұяшықты таңдап, басу керек CTRL + SHIFT + ЕНГІЗУ(туралы мақаланы қараңыз). Мән сол жақ ұяшықта қайтарылады А , оң жақта - б .

Ескерту: Кіріспен араласпау үшін массив формулалары INDEX() функциясын қосымша пайдалану қажет болады. Формула = ИНДЕКС(ЖОЛЫ(C26:C45,B26:B45),1)немесе жай = LINEST(C26:C45;B26:B45)сызықтың еңісіне жауапты параметрді қайтарады, яғни. А . Формула = ИНДЕКС(ЖОЛЫ(C26:C45,B26:B45),2)сызықтың Y осімен қиылысуына жауапты параметрді қайтарады, яғни. б .

Параметрлерді есептеп, шашырау диаграммасысәйкес сызықты салуға болады.

Ең кіші квадраттар әдісі арқылы түзу сызықты салудың тағы бір тәсілі – график құралы Тренд сызығы. Ол үшін диаграмманы таңдаңыз, мәзірден таңдаңыз Орналасу қойындысы, В топтық талдаубасыңыз Тренд сызығы, содан кейін Сызықтық жуықтау .

Диалогтық терезеде «теңдікті диаграммада көрсету» жолына құсбелгі қою арқылы жоғарыда табылған параметрлер диаграммадағы мәндерге сәйкес келетініне көз жеткізуге болады.

Ескерту: Параметрлердің сәйкес келуі үшін диаграмма түрі болуы керек. Мәселе мынада: диаграмманы құру кезінде Кесте X осінің мәндерін пайдаланушы көрсете алмайды (пайдаланушы нүктелердің орналасуына әсер етпейтін белгілерді ғана көрсете алады). Х мәндерінің орнына 1 реттілігі қолданылады; 2; 3; ... (санаттарды нөмірлеу үшін). Сондықтан, егер сіз салсаңыз тренд сызығытиптік диаграммада Кесте, содан кейін X нақты мәндерінің орнына дұрыс емес нәтижеге әкелетін осы реттілік мәндері пайдаланылады (әрине, X нақты мәндері 1 ретпен сәйкес келмесе; 2; 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 б.