2. Трендтермен жұмыс атқаруға арналған Excel-дің аспаптық тәсілдері.

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

І. Көмекші аспаптар уақыт периодының сандық қатарларын тұрғызуды жылдамдатады. Бірақ қолданушы бұл тәсілдерді қолданбай-ақ есепті шеше алады. Бұған кіретіндер (1) Edit, Fill, Series (Правка, Заполнить, Прогрессия) опциялары және Series диалогты терезе, сонымен бірге (2) қажетті диапазонға дейінгі екіторлы қатарды «созуды» диалогты емес ұйымдастыру.Excel-дің тек жетінші версиясынан бастап соңғы мүмкіншілік сәтті ұйымдастырылған. Әсіресе Excel 97 версиясы өте тиімді, себебі ол қатарға уақыттық форматы бар бірторлы мәндерді созуға мүмкіншілік береді.

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

ІІІ. Негізгі Excel-аспаптар динамикалық қатарлармен жұмыс істеу үшін екі топты ұстанады:

1. Трендтің математикалық және графикалық мәндерін тұрғызу әдістері, оған Insert Trendline (Тренд сызығын қосу) опциясы мен оның үш диалогты терезелері кіреді:

   • Type терезесі (Типі) – тренд формасын таңдау;

   • Trendline Formatting терезесі (Тренд сызығының форматы);

   • Options терезесі(Параметрлер)– тренд белгісін (теңдеудің математикалық түрі мен детерминация коэффициенті). Сонымен қатар тренд бойынша (алға немесе артқа) графикалық болжау үшін периодтар санының тапсырмасын қосу.

2. Сандық түрде болжауды алу әдістері мен оның опциялары:

   • Сызықтық тренд негізінде бір нүктеде болжауды есептеу үшін статистикалық функциялар тобынан FORE AST функциясы белгіленген(ПРЕДСКАЗ – Excel 97-де);

   • әртүрлі трендтер бойынша бірнеше нүктелерде болжау есептеулердің бастапқы деңгейлерін біруақытта түзету үшін автор бірфакторлы what-if моделін және оның Data, Table(Excel 97-де – Кестені орнықтыру) функцияларын қолдануды ұсынып отыр;

   • болжаудың сенімділік интервалын есептеу үшін егер басты қосындылары қарастырылған болса, онда Descriptive statistics (Сипаттау статистикалары) талдау мәліметтер әдістерінің тобындағы аспаптар қолданылады; басқа жағдайда стандартты статистикалық функциялар сериясы қолданылады:

     • STDEV(СТАНДОТКЛОН) және STDEVP(СТАНДОТКЛОНП)– басты қосындылар бойынша және таңдау бойынша стандартты ауытқуға сәйкес;

     • COUNT(СЧЕТ)– сандар жиынтығы(бақыланған) немесе қосындылар өлшемі;

     • CONFIDENCE(ДОВЕРИТ)– сенімділік интервалы.

Excel мүмкіншіліктерімен трендтерді модельдеу кезінде есепке алуға тиімді болатын практикалық тәжірибе беру:

1. Тренд белгілерін келесіге орналастыру үшін графикте көп кеңістікті босатуға талпыныңыз, себебі тренд белгілерінің нақ өзі трендтің математикалық түрі мен детерминация коэффициентін береді. Осы мақсатпен диаграмманың берілген тақырыбынан бас тартуға да болады немесе оны редактрлеу мен шрифтінің өлшемі арқылы шекті кішірейтуге болады.

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

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

3. Excel-де трендтерді тұрғызу технологиясы.

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

1. Бастапқы динамикалық қатардың ХУ-диаграммасын тұрғызу.

2. Бастапқы қатардың диаграммасының көшірмесін жасау(міндетті емес).

3. Тренд тұрғызу керек болатын көрсеткіштің сызығына тышқанның сол жақ батырмасын (ТСЖБ) басып ерекшелеу(сызық маркерленуі тиіс).

4. Контекстік-тәуелді мәзірді ашу үшін маркерленген сызық бойынша тышқанның оң жақ батырмасын (ТОЖБ) басу.

5. “Трендтің сызығын қосу” опциясын (Insert Trendline) таңдау.

6. Ашылған диалогты терезеден тренд типтерінің бір типін таңдау.

7. Сол терезеде Параметрлер қосымша бетін таңдау.

8. Ашылған диалогты терезеде тренд параметрлеріне Диаграммада теңдеуді көрсету жалауын және Диаграммаға R^2 аппроксимациясының тиісті шамасын орнықтыру жалауын орнату.

9. (Еркімізше) алға немесе артқа кететін мүмкін болжауды көрсету үшін периодтардың қажетті санын алу.

10. (Еркімізше) диаграмма белгісіне түсетін, модельденген трендтің атын жазу. Атауы үнсіз келісім бойынша, бірақ полиномиалды трендтің дәрежесін көрсетуінсіз-ақ беріледі. Сондықтан да бірнеше полиномиалды трендтерді модельдеу кезінде осы мүмкіншілікті қолдансақ болады.

11. Егер теңдеудің бос мүшесі керек болмаса, 0 нүктесінде У осьімен қисықты қиып өту жалауын активтендіру керек.

12. OK батырмасын активтендіру.

13. Әрбір жаңа тренд алу үшін 2-ші пункттен 12-ші пунктке дейінгі технологияны қайталау.

Ең алдымен диаграммада ТСЖБ басу арқылы тренд тұрғызылып отырған бастапқы қатардың сызығын ерекшелеу керек. Егер диаграммада бір көрсеткіштен көп болса және сызықтар жақын орналасса, онда әрбір жаңа трендті модельдеу үшін диаграмманы көшірген жөн. Содан кейін ерекшеленген сызық бойынша ТОЖБ басып қосымша опцияның мәзірін ашамыз, мұнда ТСЖБ көмегімен Тренд сызығын қосу опциясы (Insert Trendline) таңдалады. Опцияға рұқсат жоқ, егер мәліметтердің сызығы ерекшеленбеген болса – алдыңғы қадамға қайтуға тура келеді. Егер де барлығы дұрыс болса, онда негізгі диалогты терезе Тренд сызығы (Trendline) тренд модельдеудің мүмкіншілігін ұсынады, сондықтан да ол регрессияның бес негізгі типтерін қолданады:

1. Linear–сызықты,

2. Logaritmic–логарифмдік,

3. Polinomial– полиномиалды (екіден алтыға дейінгі дәрежелер Дәреже(Order) ашылған тізімінде сұралады),

4. Power– дәрежелік,

5. Exponential– экспоненциалды.

Регрессияның осы түрлері үшін сәйкес жалаушаларды орнатуды қолданушы теңдеудің (Equation)түрі мен (R²) детерминация коэффициентінің мәні автоматты түрде түсетін трендтің белгісінің мәнін сұрай алады.

Трендтің берілген формасы кезінде (Х) уақыт факторының есебінде (У) бақыланған белгісінің вариациялық қандай бөлігі түсіндірілгендігін соңғысы көрсетіп отыр. Трендтің басқа формасы кезінде түсіндірілген вариацияның бөлігі өзгеруі мүмкін.

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

Ауытқымалы орташа жайында (Moving Average). Статистика ережесі бойынша ауытқымалы орташаны әдетте шашыраңқы мәліметтердің қатесін азайту мақсатымен жылдам тербелісті тегістеу үшін қатардың үлкен өлшемін қайта қарау кезінде регрессияны есептеуге дейін қолданады, және тек содан кейін ғана тегістелген сызықтармен тренд тұрғызылады. Қолданушы өзі Нүктелер(Period) ашылған тізімінде орташаланған периодтардың нүктелер санын белгілейді. Тренд белгісі (теңдеудің түрі мен R²) ауытқымалы орташа үшін қолданылмайды. Ауытқымалы орташаның әдісі басқа әдістердің комбинациясында және әсіресе, толық бастапқы қосындыларда трендтің жалғыз типін тұрғызу болмайтын кезде қолданылуы мүмкін, бірақ қатардың жеке бөліктері теңдеудің әртүрлі типтерімен жақсы аппроксимацияланады. Экономика мен әлеуметтануда мұндай мән-жайлар көрсеткіштің көрсеткеніндей уақыттың қандай да бір периодында процестің ағынды шарттары біршама өзгеріп отырады.

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

Бастапқы мәліметтердің жиынтығының бірінде трендтердің 9 теңдеуіне дейін алуға болады. Зерттеушінің тапсырмасы тиімді болжауды тұрғызу керек болатын ең жақсы трендті таңдаудан тұрады. Трендтерді модельдеу тәжірибесі бар, сценарийдің жалпы типі мен процестің даму шартын түсінетін(оптимистикалық, шынайылық, пессимистік) менеджер, нақты экономикалық көрсеткіштер(табыстар, пайдалар, шығындар, қорлар және т.б.) үшін лайықты заңдылық тәуелділіктерді жақсы біле отырып, барлық альтернативтік типтерді қабылдамайды. Өзге жағдайда, бірлікке жақындайтын детерминация коэффициентінің жоғарғы мәнімен теңдеуді іздеу үшін максимум альтернатив зерттеген жөн.

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

Excel-де тренд теңдеуінің математикалық түрі және R² толығымен диаграммада тренд белгісіне шығады. Түзетілген бастапқы қатардың сандық түрлерін алу үшін қосымша процедура қажет: (1) кестенің бос бағанасының бірінші ұяшығына диаграммадан тренд формуласын көшіру немесе алып бару (формула Excel ережесіне сай, У-тен емес, теңдеу белгісінен басталуы тиіс); (2) Х-тің (Х ретінде) орнына периодтар бағанасының сәйкес ұяшығына сілтеме беру; (3) түзетілген мәндердің қалған ұяшықтар бағанасына формуланы көшіру.

№2 Зертханалық жұмыс.

Сызықты модельге экспоненциялды тегістеу жүргізу.

Жұмыстың мақсаты: стационарлы емес динамикалық қатарға сызықты экспоненциялды тегістеу жүргізу арқылы экономикалық көрсеткіштің болжау мәнін анықтау.

Теориялық бөлім: Секірмелі мінездемесі бар уақыттық тізбекті аналитикалық түрде көрсету үшін экспоненциялды тегістеу әдісі жиі қолданылады.

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

Экспоненциялды тегістеу кезінде көрсеткіштің тенденциясы мына номинал түрінде жазылады:

(1)

Осы (1) модельдің бейімделу қасиеті болу үшін, әр уақыт периоды өткен сайын модельдің парамертлерін корректировкалау қажет.

(1) модельді бірінші реттік экспоненциялды орталар ( ), екінші реттік , к – реттік ,…, (p+1) реттік экспоненциялды орталар жиынтығы ретінде көрсетуге болатыны дәлелденген. Экспоненциялды орталар мына формулалар арқылы табылады:

(2)

Мұндағы а- тегістеу параметрі.

Экспоненциялды орталар бір-бірімен байланысты, сондықтан бұл орталарды мына формулалар арқылы табуға болады:

(3)

Болжау бағаларының статистикалық дұрыстылығы тегістеу а параметрін дұрыс таңдап алуға көп байланысты. Тегістеу параметрі (а) аз болған сайын болжау деңгейінде динамикалық тізбектің барлық элемнттері, ал көп болған сайын – динамикалық тізбектің барлық элементтері, ал көп болған сайын – динамикалық тізбектің соңғы деңгейлері көбірек әсер етеді. Тегістеу параметрі неғұрлым үлкен болған сайын, болжау модельдерінің бейімделу қасиеті жоғары, ал тегістеу параметрін кішілеу қылып алған сайын болжау модельдерінің бейімделу қасиеттері жоғары, ал тегістеу параметрін кішілеу қылып алған сайын болжау өзгеру көрсеткіштің ұзақ период аралығында өзгеру тенденциясын көрсетеді. а тегістеу параметрін таңдап алғанда болжаудың қабылдау периоды үлкен орын алады. Қысқа периодты болжауларға а параметрін үлкен қылып алу керек. Бұл жағдайда объектінің қазіргі және соның алдыңғы ақпараттары болжау функциясына көбірек әсер етеді. Болжау периоды өскен сайын тегістеу параметрі кіші болуы керек.

Зерттеп отырған процесстің динамикалық қасиеттері бірқалыпты болмаған жағдайда экспоненциялды тегістеу әдістерінің дұрыстылығы кемиді.

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

Экспоненциялды тегістеу әдісі арқылы болжаудың сенімділігін арттырудың бірнеше жолы бар. Оның бірі уақыттық тізбектің барлық жерінде біркелкі тегістеу параметрін қолданбай, өзгеріп отыратын а параметрін қолдану. Уақыттық тізбектің мәндерінің қатты өзгеретін жерінде а параметрін үлкендеу алып, ал көрсеткіштің тенденциясы бірқалыпты болатын уақыттық тізбектің жері үшін а параметрін аз алады. Соның нәтижесінде модель көрсеткіштің болжау мәнін анықтау үшін, оптималды модельге жақындайды.

Көп жағдайда жиі кездесетін полиномның сызықтық және параболдық түрін қарастырайық:

(2), (3)

Сызықтық модельге экспоненциялды тегістеу әдісін қолданайық.

№1 кестеде кәсіпорынның өнім шығару көлемінің мәліметтері берілген. Болжау функциясының параметрлерін, 1 және 2 наурызға (шартты t=21, t=22 күндерге) өнім шығару көлемінің болашақтағы болжау мәнін анықтау керек.

№1 кесте

Ай күндері	Шартты күндер	өнім шығару көлемі
4 5 6 7 8 9 10 11 12 13	1 2 3 4 5 6 7 8 9 10	15 16 13 14 14 16 17 18 17 16

Ай күндері	Шартты күндер	өнім шығару көлемі
18 19 20 21 22 23 26 27 28 29	11 12 13 14 15 16 17 18 19 20	15 15 14 13 16 18 19 19 18 18

Көрсеткіштің дөзгеру динамикасы мына теңдеумен жазылады:

(14)

№2 кестеде көргендей көрсеткіштің саналған мәндері У_t айнымалысының өзгеру тенденциясын шамамен дұрыс көрсетеді, (Функцияның статистикалық керектілігі F – Фишер критериі арқылы тексерілген).

Экспоненциялды тегістеу әдісі арқылы болжау үшін ф тегістеу параметрін анықтауымыз керек. m – тегістеу интервалының ұзындығын шамамен уақыттық тізбектің ұзындығына тең алайық: m=18.

a=2/(m+1)/2(18+1)=0,1053

Сызықтық модель үшін бастапқы экспоненциялды орталарды (5) формула арқылы анықтайық (а₀, а₁ – параметрлері ретінде (14) теңдеудің параметрлерін аламыз):

(7) формула арқылы болжау функциясының параметрлерін анықтайық:

;

(t=1, l=1) регрессия теңдеуі арқылы өнім шығару көлемнің l+1=1+1=2 уақытына (11) формула арқылы экспоненциялды орталарды санауға болады:

(7) формула арқылы болжау функциясының параметрі:

t+1(t=2) уақытына көрсеткіштің саналған мәні:

Алгоритм бойынша барлық t (t≤n) уақыты үшін экспоненциялды орталарды, функцияның тегістелген мәндерін анықтаймыз. Шығарылған мәліметтерді №2 кестеге енгізейік.

№2 кесте

Шартты күндер	Көрсеткішінің берілген мәндері, Уt	өнім шығару көлемінің шығарылған мәндері, Уt	Ye
			a=0,053	a=0,5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20	15 16 13 14 14 16 17 18 17 16 15 15 14 13 16 18 19 19 18 18	15,57 15,6 15,63 15,66 15,69 15,72 15,75 15,78 15,81 15,84 15,87 15,9 15,93 15,96 15,99 16,02 16,05 16,08 16,11 16,14	–– 15,57 15,695 15,15 14,93 14,72 14,99 15,41 16,36 16,54 16,48 16,23 16,03 16,66 15,32 15,29 15,85 16,52 17,11 17,31	–– 15,57 16,01 13,12 13,37 13,58 15,75 17,35 18,66 17,81 16,41 14,96 14,90 13,61 12,47 15,31 18,19 19,86 20,05 18,85

t=n уақыты үшін табылған тегістелген болжау функциясы:

(15)

15. модель арқылы 1 және 2 шартқа (l=1, l=2) өнім өткізу көлемінің болжау мәні:

№2 кестеде көріп отырғандай, сызықтық модельдің параметрлері санақтың әр қадамында алдындағы аталған мәндердің қатесіне байланысты жөнделіп отырады.

Қысқа периодқа болжау жасау үшін m тегістеу интервалын қысқа алайық: m=3

A=2/(m+1)=2/(3+1)=0,5

a=0,5 тегістеу параметрі үшін саналған функцияның тегістелген мәндері №2 кестеде көрсетілген.

№1 кестедегі көрсеткіштің (өнім шығару көлемі) берілген мәндері және №2 кестедегі a=0,1053 және a=0,5 тегістеу параметрлері үшін осы көрсеткіштің тегістелген мәндері арқылы графиктер тұрғызайық:

№1 графикте көрсетілген қисық сызықты талдай отыра a=0,5 тегістеу параметрі үшін табылған болжау функциясының шығарылған мәндері арқылы құрылған график көрсеткіштің тенденциясын шамамен дұрыс көрсете алатынын көреміз.

a=0,5 тегістеу параметрі үшін 1 және 2 наурызға (t=21, t=22) табылған өнім шығару көлемінің болжау мәндері:

a=0,1053 тегістеу параметрі үшін табылған болжау мәндерінің қатесін (12) формула арқылы анықтаймыз:

σ₂₀₊₁=1,88

Санақтың нәтижелерін №3 кестеге ендірейік:

Мерзім	Болжау	Болжау қатесі	Сенімді интервал
1 наурыз 2 наурыз	18,33 18,44	+1,95 +2,32	20,28 20,75	16,38 16,13