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