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

Болжамдаудың әртүрлі әдістерінің ішінде жуықтауды бөліп көрсетуге болады. Оны қолдана отырып, бастапқы объектілерді қарапайым объектілерге ауыстыру арқылы өрескел есептер шығарып, жоспарланған көрсеткіштерді есептеуге болады. Excel бағдарламасында бұл әдісті болжау және талдау үшін қолдану мүмкіндігі де бар. Берілген бағдарламада бұл әдісті кіріктірілген құралдармен қалай қолдануға болатынын қарастырайық.

Жуықтау

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

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

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

• Сызықтық;
• Экспоненциалды;
• Логарифмдік;
• Көпмүшелік;
• Қуат.

Біз нұсқалардың әрқайсысын бөлек қарастырамыз.

Сабақ: Excel-де тренд сызығын қалай құруға болады

1-әдіс: сызықты тегістеу

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

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

1. Жоспарлау үшін, ең алдымен, бағандарды таңдаңыз «Бірлік құны» және Пайда. Осыдан кейін қойындыға өтіңіз Кірістіру. Әрі қарай, Диаграммалар құралдар тақтасындағы таспада батырманы басыңыз «Дақ». Ашылған тізімде атты таңдаңыз «Тегіс қисықтар мен маркерлермен дақ». Дәл осы диаграмма тренд сызығымен жұмыс істеуге өте ыңғайлы, сондықтан Excel бағдарламасында жуықтау әдісін қолдануға болады.



2. Кестесі құрылды.



3. Тренд сызығын қосу үшін оны тінтуірдің оң жақ батырмасын басып таңдаңыз. Контекстік мәзір пайда болады. Ондағы элементті таңдаңыз «Тренд сызығын қосу ...».

   

   Оны қосудың тағы бір мүмкіндігі бар. Таспадағы қосымша қойындыларда «Диаграммалармен жұмыс» қойындыға өтіңіз «Орналасу». Әрі қарай құралдар блогында «Талдау» түймесін басыңыз Тренд желісі. Тізім ашылады. Сызықтық жуықтауды қолдану қажет болғандықтан, біз ұсынылған позициялардан таңдаймыз «Сызықтық жуықтау».



4. Контекстік мәзір арқылы әрекеттерді қосудың бірінші нұсқасын таңдаған болсаңыз, формат терезесі ашылады.

   Параметрлер блогында «Тренд сызығын құру (жақындату және тегістеу)» ауыстырыпқосқышты қалпына қойыңыз «Сызықтық».
   Қажет болса, орналасудың жанындағы құсбелгіні қоюға болады «Диаграммада теңдеуді көрсет». Осыдан кейін тегістеу функциясының теңдеуі диаграммада көрсетіледі.

   Сондай-ақ, біздің жағдайда әртүрлі жуықтау параметрлерін салыстыру үшін оның жанындағы құсбелгіні қою керек «Диаграммада сенімді жуықтаудың мәнін салыңыз (R ^ 2)». Бұл көрсеткіш әр түрлі болуы мүмкін 0 дейін 1. Ол неғұрлым жоғары болса, жақындау жақсырақ (сенімді). Бұл көрсеткіштің мәні бар деп саналады 0,85 және одан жоғары болса, тегістеуді сенімді деп санауға болады, бірақ егер индикатор төмен болса, онда жоқ.

   Жоғарыда аталған параметрлердің барлығын орындағаннан кейін. Түймесін басыңыз Жабутерезенің төменгі жағында орналасқан.



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

Бұл жағдайда қолданылатын тегістеу келесі формуламен сипатталады:

y = ax + b

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

у = -0.1156х + 72.255

Жақындаудың дәлдігінің мәні тең 0,9418, бұл тегістеуді сенімді деп сипаттайтын айтарлықтай қолайлы нәтиже.

2-әдіс: экспоненциалды жуықтау

Енді Excel бағдарламасындағы жуықтаудың экспоненциалды түрін қарастырайық.

1. Тренд сызығының түрін өзгерту үшін оны тінтуірдің оң жақ батырмасымен басып, қалқымалы мәзірден элементті таңдаңыз «Тренд сызығының форматы ...».



2. Осыдан кейін таныс формат терезесі басталады. Жақындау түрін таңдау блогында ауыстырғышты орнатыңыз «Экспоненциалды». Қалған параметрлер бірінші жағдайдағыдай қалады. Түймесін басыңыз Жабу.



3. Осыдан кейін тренд сызбасы диаграммада орналасады. Көріп отырғаныңыздай, бұл әдісті қолданған кезде аздап иілген пішінді болады. Бұл жағдайда сенім деңгейі 0,9592, бұл сызықтық жуықтауды қолданғанға қарағанда жоғары. Экспоненциальды әдіс шамалар тез өзгеріп, содан кейін теңдестірілген форма алған кезде жақсы қолданылады.

Тегістеу функциясының жалпы формасы келесідей:

y = be ^ x болады

қайда е табиғи логарифмнің негізі болып табылады.

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

y = 6282.7 * e ^ (- 0.012 * x)

3-әдіс: логарифмдік тегістеу

Енді логарифмдік жуықтау әдісін қарастырудың кезегі келді.

1. Бұрынғыдай тренд сызығындағы формат терезесін контекстік меню арқылы іске қосамыз. Ауыстырғышты орнына қойыңыз «Логарифмдік» түймесін басыңыз Жабу.



2. Логарифмдік жуықтаумен тренд сызығын салу тәртібі бар. Алдыңғы жағдайдағыдай, бұл опция бастапқыда деректер тез өзгеріп, содан кейін теңдестірілген көрініс болған кезде жақсы қолданылады. Көріп отырғаныңыздай, сенім деңгейі 0,946 құрайды. Бұл сызықтық әдісті қолданғаннан гөрі жоғары, бірақ экспоненциалды тегістейтін тренд сызығының сапасынан төмен.

Жалпы, тегістейтін формула келесідей:

y = a * ln (x) + b

қайда лн табиғи логарифмнің мәні. Осылайша әдіс атауы.

Біздің жағдайда формула келесі форманы алады:

у = -62,81лн (х) +404.96

4-әдіс: полиномды тегістеу

Көпмүшеліктерді тегістеу әдісін қарастыратын уақыт жетті.

1. Тренд сызығының пішімі терезесіне бірнеше рет жасалған сияқты өтіңіз. Блокта «Тренд сызығын құру» ауыстырыпқосқышты қалпына қойыңыз «Көпмүшелік». Осы тармақтың оң жағында өріс орналасқан «Дәреже». Мәнді таңдағанда «Көпмүшелік» белсенді болады. Мұнда сіз кез келген қуат мәнін көрсете аласыз 2 (әдепкі бойынша орнатылған) 6. Бұл көрсеткіш функцияның максимум және минимум санын анықтайды. Екінші дәрежелі көпмүшені орнату кезінде тек бір максимум сипатталады, ал алтыншы дәрежелі көпмүшені орнату кезінде бес максимумға дейін сипаттауға болады. Алдымен әдепкі параметрлерді қалдырайық, яғни екінші дәрежені көрсетеміз. Қалған параметрлерді бұрынғы әдістермен бірдей етіп қалдырамыз. Түймесін басыңыз Жабу.



2. Осы әдісті қолдана отырып тренд сызығы сызылған. Көріп отырғаныңыздай, экспоненциалдық жақындауды қолданғаннан гөрі ол қисық. Сенімділік деңгейі бұрын қолданылған әдістерге қарағанда жоғары және болып табылады 0,9724.

   

   Деректер үнемі өзгеріп тұратын болса, бұл әдісті сәтті қолдануға болады. Тегістеудің бұл түрін сипаттайтын функция мынандай көрінеді:

   y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n

   Біздің жағдайда формула келесі форманы алды:

   у = 0.0015 * х ^ 2-1.7202 * х + 507.01

3. Енді нәтиженің басқаша болатынын білу үшін көпмүшеліктердің дәрежесін өзгертейік. Формат терезесіне ораламыз. Біз жуықтау түрін полиномдық күйде қалдырамыз, бірақ оған қарама-қарсы, дәреже терезесінде мүмкін болатын ең үлкен мәнді орнатамыз - 6.



4. Көріп отырғаныңыздай, осыдан кейін біздің тренд сызығымыз максима саны алты болатын қисық түрінде болды. Сенім деңгейі одан да арта түсті 0,9844.

Тегістеудің бұл түрін сипаттайтын формула келесі форманы алады:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

5-әдіс: қуатты тегістеу

Қорытындылай келе, біз Excel-де қуат заңын жуықтау әдісін қарастырамыз.

1. Біз терезеге көшеміз Тренд сызығының форматы. Тегістегіш түрін қалпына қойыңыз «Қуат». Теңдеу дисплейі және сенімділік деңгейі, әдеттегідей, қалады. Түймесін басыңыз Жабу.



2. Бағдарлама тренд сызығын қалыптастырады. Көріп отырғаныңыздай, біздің жағдайда бұл сәл иілген сызық. Сенімділік деңгейі 0,9618, бұл өте жоғары көрсеткіш. Жоғарыда аталған барлық әдістердің ішінде сенімділік полиномиялық әдісті қолданған кезде ғана жоғары болды.

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

Бұл әдісті сипаттайтын жалпы формула келесі формада болады:

y = bx ^ n

Біздің нақты жағдайда бұл келесідей:

y = 6E + 18x ^ (- 6.512)

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

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