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

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

Аппроксимацияны орындау

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

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

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

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

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

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

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

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

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

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



2. Кесте салынған.



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

   

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



4. Алайда, егер контекстік мәзір арқылы қосу арқылы әрекеттердің бірінші параметрін таңдасаңыз, онда пішім терезесі ашылады.

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

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

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



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

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

y = ax + b

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

y = -0.1156x + 72.255

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

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

Енді Excel-дағы экспоненттік түрін қарастырайық.

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



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



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

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

y = be ^ x

мұнда е - бұл табиғи логарифм негізі.

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

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

3-әдіс: Жазуды тегістеу

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

1. Алдыңғы уақытта сияқты контекстік мәзір арқылы тренд сызығы пішімі терезесін іске қосыңыз. Қосқышты орнына орнатыңыз «Логарифмдік» батырмасын басыңыз «Жабу».



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

Тегістеу формуласы тұтастай көрінеді:

y = a * ln (x) + b

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

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

y = -62,81лн (х) + 404,96

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

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

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



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

   

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

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

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

   y = 0.0015 * x ^ 2-1.7202 * x + 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. Терезеге жылжытыңыз «Trend Line пішімі». Тегістеу көрінісі ауыстырғышын орнына орнатыңыз «Қуат». Теңдеулер мен сенім деңгейлерін көрсету, әрдайым, оны қалдырыңыз. Біз батырманы басамыз «Жабу».



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

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

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

y = bx ^ n

Біздің нақты жағдайда, келесідей көрінеді:

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

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

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