Болжау - бұл экономикадан инжинирингке дейінгі кез келген қызметтің барлық салаларында өте маңызды элемент. Бұл салада маманданған бағдарламалық қамтамасыз етудің көп саны бар. Өкінішке орай, барлық пайдаланушылар әдеттегі Excel электрондық кесте процессорының өздерінің арсеналдық құралдарында болжауды орындау үшін қолданатындығын білмейді, бұл олардың тиімділігі кәсіби бағдарламалардан әлдеқайда кем емес. Осы құралдардың не екенін біліп, іс жүзінде қалай болжам жасау керектігін білейік.
Болжау тәртібі
Кез-келген болжаудың мақсаты - қазіргі трендті анықтау және зерттелетін объектіге болашақта белгілі бір кезеңде күтілетін нәтижені анықтау.
1-әдіс: тренд сызығы
Excel-дағы графикалық болжаудың ең танымал түрлерінің бірі - тренд сызығын құру арқылы жүзеге асырылатын экстраполяция.
Алдыңғы 12 жылдағы осы көрсеткіш бойынша деректер негізінде кәсіпорынның 3 жылдағы пайда көлемін болжауға тырысамыз.
- Функцияның аргументтері мен мәндерінен тұратын кестелік деректерге негізделген тәуелділікті график құрыңыз. Мұны істеу үшін кесте кеңістігін таңдап, қойындыда болыңыз «Кірістіру», блокта орналасқан диаграмманың қажетті түрінің белгішесін басыңыз «Диаграммалар». Содан кейін біз белгілі бір жағдайды таңдап аламыз. Шашырау диаграммасын таңдау жақсы. Басқа көріністі таңдауға болады, бірақ содан кейін, деректер дұрыс көрсетілуі үшін, дәлірек айтқанда, дәлел жолын жойып, көлденең осьтің басқа ауқымын таңдаңыз.
- Енді тренд сызығын құру керек. Біз диаграммадағы кез-келген нүктені тінтуірдің оң жағымен басамыз. Белсенді контекстік мәзірде элементтегі таңдауды тоқтатыңыз «Тренд сызығын қосу».
- Тренд сызығын пішімдеу терезесі ашылады. 6 аппроксимация түрін таңдауға болады:
- Сызықтық;
- Логарифмдік;
- Экспоненциалды;
- Қуат;
- Полиномиальды;
- Сызықты сүзгілеу.
Сызықтық жақындаумен бастаймыз.
Параметрлер терезесінде «Болжам» алаңда «Алға» нөмірді орнатыңыз "3,0", өйткені біз алдағы үш жылға болжам жасауымыз керек. Сонымен қатар, құсбелгілерді тексеруге болады «Диаграммадағы теңдеуді көрсету» және «Диаграмма жақындағаның (R ^ 2) дәлдігінің мәнін салыңыз». Соңғы индикатор тренд сызығының сапасын көрсетеді. Параметрлер жасалғаннан кейін түймесін басыңыз. «Жабу».
- Тренд сызығы салынып, біз оны үш жыл өткеннен кейін болжанған пайда мөлшерін анықтау үшін пайдалана аламыз. Көріп отырсыздар, сол уақытқа дейін ол 4,500 мың рубльге жетуі керек. Коэффициенті R2, Жоғарыда айтылғандай тренд сызығының сапасын көрсетеді. Біздің жағдайда құндылығы R2 жоғары 0,89. Коэффициент неғұрлым жоғары болса, желінің сенімділігі жоғарырақ болады. Оның максималды мәні тең болуы мүмкін 1. Арақатынас аяқталған кезде саналады 0,85 тренд сызығы сенімді.
- Сенімділік деңгейіне қанағаттанбасаңыз, тренд сызығы пішімі терезесіне оралуға және кез келген басқа жуықтау түрін таңдауға болады. Сізге барынша қол жетімді нұсқаларды табуға болады.
Тренд сызығымен экстраполяцияны пайдалана отырып, болжамды кезең болжанатын кезеңнің 30% -ынан аспайтын болса, тиімді болжам болуы мүмкін. Яғни, 12 жыл кезеңін талдау кезінде біз 3-4 жылдан артық тиімді болжам жасай алмаймыз. Бірақ бұл жағдайда тіпті осы кезеңде ешқандай форс-мажор болмайды немесе керісінше өткен кезеңдерде болмаған өте қолайлы жағдайлар болмаса, салыстырмалы түрде сенімді болады.
Сабақ: Excel бағдарламасында тренд сызығын қалай құруға болады
2-әдіс: операторы FORECAST
Кестелік деректер үшін экстраполяция стандартты Excel функциясы арқылы жасалуы мүмкін. Болжау. Бұл аргумент статистикалық құралдар санатына жатады және келесі синтаксисті бар:
= PREDICT (x; белгілі_y_y; белгілі мәндер_x)
«X» - дәлел, функцияның мәнін анықтағыңыз келеді. Біздің жағдайда болжам дәлел болуға тиіс жыл болады.
«Белгілі Y құндылықтары» - функцияның белгілі мәндерінің базасы. Біздің жағдайда оның рөлі алдыңғы кезеңдердегі пайда көлемін білдіреді.
«Белгілі x» - Бұл функцияның белгілі мәндеріне сәйкес келетін дәлелдер. Біздің рөлімізде өткен жылдардағы пайда туралы ақпарат жиналатын жылдардың нөмірленуі бар.
Әрине, дәлел міндетті түрде уақыт кезеңі болмауы керек. Мысалы, температура болуы мүмкін, ал функцияның мәні қыздырылған кезде судың кеңею деңгейі болуы мүмкін.
Бұл әдісті есептеу кезінде сызықтық регрессия әдісі қолданылады.
Оператордың нюанстарын қарастырайық Болжау белгілі бір мысал. Барлық үстелді алыңыз. Бізге 2018 жылы түсетін болжамды білу қажет.
- Өңдеу нәтижесін көрсетгіңіз келген парақтағы бос ұяшықты таңдаңыз. Біз батырманы басамыз «Функцияны кірістіру».
- Ашылады Функция шебері. Санатта «Статистикалық» атты таңдаңыз «FORECAST»батырмасын басыңыз «ОК».
- Аргумент терезесі басталады. Алаңда «X» функцияның мәнін тапқыңыз келетін дәлелдің мәнін көрсетіңіз. Біздің жағдайда бұл 2018 жыл. Сондықтан біз рекорд жасаймыз "2018". Бірақ осы көрсеткішті парақтағы ұяшықта және өрісте көрсету керек «X» оған сілтеме беріңіз. Бұл болашақта есептеулерді автоматтандыруға мүмкіндік береді және егер қажет болса, жыл оңай өзгереді.
Алаңда «Белгілі Y құндылықтары» бағанның координаттарын көрсетіңіз «Кәсіпорынның пайдасы». Мұны курсорды өріске қою арқылы, содан кейін сол жақ тінтуір түймешігін басып, парақтағы тиісті бағанды таңдау арқылы жасауға болады.
Сол сияқты далада «Белгілі x» біз бағанның мекенжайын енгіземіз «Жыл» өткен кезеңдегі деректермен.
Барлық ақпарат енгізілгеннен кейін, түймені басыңыз. «ОК».
- Оператор енгізілген деректер негізінде есептейді және нәтижені экранға шығарады. 2018 жылы пайда 4564,7 мың рубль көлемінде жоспарланған. Алынған кестеге сүйене отырып, жоғарыда талқыланған диаграмманы жасау құралдарын пайдаланып графикті құрай аламыз.
- Аргумент енгізу үшін пайдаланылған ұяшықтағы жылын өзгертсеңіз, нәтиже сәйкес өзгертіледі және графика автоматты түрде жаңартылады. Мысалы, болжам бойынша 2019 жылы пайда көлемі 4637,8 мың рубльді құрайды.
Бірақ тренд сызығының құрылысында сияқты, болжанған уақытқа дейін уақыттың ұзақтығы деректер базасы жинақталған барлық кезеңнің 30% -ынан аспауы керек екенін ұмытпаңыз.
Сабақ: Excel экстраполяциясы
3-әдіс: оператор TENDENCY
Болжау үшін сіз басқа функцияны пайдалана аласыз - TREND. Ол сондай-ақ статистикалық операторлардың санатына жатады. Оның синтаксисі құралдың синтаксисіне ұқсас. Болжау және келесідей көрінеді:
= TREND (белгілі мәндер_x; белгілі мәндер_x; new_values_x; [const])
Көріп тұрғаныңыздай, дәлелдер «Белгілі Y құндылықтары» және «Белгілі x» оператордың элементтеріне толық сәйкес келеді Болжаужәне дәлел «Жаңа х мәндері» дәлелге сәйкес келеді «X» алдыңғы құрал. Сонымен қатар, TREND қосымша дәлел бар «Тұрақты»бірақ бұл міндетті емес және тұрақты факторлар болған кезде ғана қолданылады.
Бұл оператор функцияның желілік тәуелділігін барынша тиімді пайдаланылады.
Бұл құралды бір деректер массивімен қалай жұмыс істейтінін көрейік. Алынған нәтижелерді салыстыру үшін 2019 жылы болжау нүктесін анықтаймыз.
- Нәтижені көрсету және іске қосу үшін ұяшық белгісін жасаймыз Функция шебері әдеттегідей. Санатта «Статистикалық» атын тауып, таңдап алыңыз «TREND». Біз батырманы басамыз «ОК».
- Оператор дәлел терезесі ашылады TREND. Алаңда «Белгілі Y құндылықтары» жоғарыда сипатталған, бағанның координаттарын енгізіңіз «Кәсіпорынның пайдасы». Алаңда «Белгілі x» бағанның мекенжайын енгізіңіз «Жыл». Алаңда «Жаңа х мәндері» болжамды көрсететін жылдың нөмірі орналасқан ұяшыққа сілтеме енгізіңіз. Біздің жағдайда бұл 2019 жыл. Өріс «Тұрақты» бос қалдырыңыз. Түймешікті басыңыз «ОК».
- Оператор деректерді өңдейді және нәтижені экранға шығарады. Өздеріңіз көріп отырғандай, желілік тәуелділік әдісімен есептелген 2019 жылға болжанған пайда көлемі алдыңғы әдіс бойынша 4637,8 мың рубльге тең болады.
4-әдіс: GROWTH операторы
Excel бағдарламасында болжау үшін қолдануға болатын басқа функция GROWTH операторы болып табылады. Ол сондай-ақ құралдардың статистикалық топтарына жатады, бірақ бұрынғыдан өзгеше сызықтық тәуелділік әдісін қолданбайт, бірақ есептеу үшін экспоненталық әдіс. Бұл құралдың синтаксисі келесідей:
= GROWTH (белгілі мәндер_y; белгілі мәндер_x; New_values_x; const])
Көріп отырғаныңыздай, осы функцияның дәлелдері оператордың дәлелдерін дәл қайталайды TRENDсондықтан біз олардың сипаттамасына екінші рет тоқталмаймыз, бірақ бұл құралды іс жүзінде қолдануды бірден бастайды.
- Нәтиже шығару ұяшығын таңдап, оны әдеттегі жолмен шақырыңыз. Функция шебері. Статистикалық операторлар тізімінде тармақ іздейді «ӨСУ»оны таңдаңыз және түймені басыңыз «ОК».
- Жоғарыда көрсетілген функцияның дәлел терезесінің активтендіруі орын алады. Деректерді осы терезенің өрістеріне енгізіңіз, олар оператордың дәлел терезесінде енгізгеніміздей TREND. Ақпарат енгізілгеннен кейін түймесін басыңыз «ОК».
- Деректерді өңдеу нәтижесі бұрын көрсетілген ұяшықта мониторда көрсетіледі. Көріп отырсыздар, бұл жолы нәтиже 4682,1 мың рубль болды. Оператор деректерін өңдеуден айырмашылығы TREND шамалы, бірақ олар қол жетімді. Бұл құралдардың әр түрлі есептеу әдістерін қолдануы: сызықтық тәуелділік және экспоненттік тәуелділік әдісі.
5-әдіс: LINEST операторы
Оператор LINE есептеу кезінде сызықты жуықтау әдісін қолданады. Бұл құралды қолданатын сызықтық әдіспен шатастырмау керек. TREND. Оның синтаксисі:
= LINEST (Белгілі мәндер_y; Белгілі мәндер_x; New_values_x; [const]; [Статистика])
Соңғы екі дәлел қосымша болып табылады. Алғашқы екі әдісті алдыңғы әдістермен білеміз. Бірақ сіз бұл функцияда жаңа құндылықтарды көрсететін дәлел жоқ екенін байқадыңыз. Өйткені, бұл құрал тек қана бір жылдық кезеңдегі табыстың өзгеруін анықтайды, бұл біздің жағдайда бір жыл, бірақ біз жалпы нәтижені жеке-жеке есептеп, соңғы нақты пайда нормасына операторды есептеудің нәтижесін қосу керек LINEжыл санына көбейтілген.
- Есептеулер орындалатын ұяшықтың таңдауын жасаңыз және Функциялар шебері іске қосыңыз. Атын таңдаңыз «ЛИНЕЙН» санатында «Статистикалық» батырмасын басыңыз «ОК».
- Алаңда «Белгілі Y құндылықтары»ашылатын аргумент терезесінің, бағанның координаттарын енгізіңіз «Кәсіпорынның пайдасы». Алаңда «Белгілі x» бағанның мекенжайын енгізіңіз «Жыл». Қалған өрістер бос қалдырылады. Содан кейін түймесін басыңыз «ОК».
- Бағдарлама таңдалған ұяшықта сызықтық трендтің мәнін есептеп көрсетеді.
- Енді 2019 жылға жоспарланған пайдадың мәнін білуіміз керек. Белгіні орнатыңыз "=" парақтағы бос ұяшыққа. Өткен жылы оқыған (2016) нақты пайда мөлшерін қамтитын ұяшықты басыңыз. Біз белгі қойдық "+". Бұдан бұрын есептелген желілік трендті қамтитын ұяшықты басыңыз. Біз белгі қойдық "*". Оқу кезеңінің (2016) өткен жылы және болжам жасалатын жыл (2019) арасындағы кезеңнен бастап, үш жыл кезеңі өтіп, біз ұяшықтағы санды орнаттық "3". Есептеу үшін түймені басыңыз. Кіру.
Көріп отырғаныңыздай, сызықтық жақындау әдісімен есептелген пайдадың болжамды құны 2019 жылы 4614,9 мың рубльді құрайды.
6-әдіс: LOGEST операторы
Біз қарастыратын соңғы құрал LGGRPRIBL. Бұл оператор экспоненттік жуықтау әдісіне негізделген есептеулерді орындайды. Оның синтаксисінде келесі құрылым бар:
= LOGPLPR (белгілі мәндер_және белгілі мәндер_x; new_values_x; [const]; [статистика])
Көріп отырғандай, барлық аргументтер бұрынғы функцияның тиісті элементтерін қайталайды. Болжамды есептеу алгоритмі сәл өзгереді. Функция экспоненталық трендті есептейді, ол бір мезгілде кірістің сомасы қанша рет өзгеретінін көрсетеді, яғни бір жыл. Бізге соңғы нақты кезең мен бірінші жоспарланған арасындағы айырмашылықты табу керек, оны жоспарланған кезеңдер санына көбейту керек. (3) және нәтижеге соңғы нақты кезеңнің сомасын қосыңыз.
- Функциялар шебері операторларының тізімінде атауды таңдаңыз LGRFPRIBL. Түймешікті басыңыз. «ОК».
- Аргумент терезесі басталады. Онда функцияны пайдаланып, дәл деректерді енгіземіз LINE. Түймешікті басыңыз «ОК».
- Экспоненциалды тренд нәтижесі көрсетілген ұяшықта есептеледі және көрсетіледі.
- Біз белгі қойдық "=" бос ұяшықта. Жақшаларды ашып, соңғы нақты кезең үшін кіріс мәнін қамтитын ұяшықты таңдаңыз. Біз белгі қойдық "*" экспоненталық трендті қамтитын ұяшықты таңдаңыз. Біз минус белгісін қойып, соңғы кезеңдегі кіріс сомасы болып табылатын элементті қайта басамыз. Кницаны жабыңыз және таңбаларды жүргізіңіз. "*3+" баға белгілерінсіз. Тағы да соңғы рет таңдалған бір ұяшықты басыңыз. Есептеу үшін түймені басыңыз Кіру.
Экспоненциалды жуықтау әдісімен есептелген 2019 жылы болжанып отырған пайда көлемі 4 639,2 мың рубльді құрайды, бұл бұрынғы әдістермен есептеуде алынған нәтижелерден айтарлықтай ерекшеленбейді.
Сабақ: Excel бағдарламасындағы басқа статистикалық функциялар
Excel бағдарламасында болжауды қалай жасау керектігін білдік. Графикалық тұрғыдан, бұл тренд сызығын қолдану арқылы және аналитикалық тұрғыдан бірнеше статистикалық функциялардың көмегімен жасалуы мүмкін. Осы операторлардың ұқсас деректерін өңдеу нәтижесінде басқа нәтиже болуы мүмкін. Бірақ бұл таңқаларлық емес, өйткені олардың әрқайсысы есептеудің әр түрлі әдістерін қолданады. Егер флуктуация аз болса, онда осы опциялардың барлығына белгілі бір жағдайға қатысты қолдануға болады.
