Прагназаванне - гэта вельмі важны элемент практычна любой сферы дзейнасці, пачынаючы ад эканомікі і заканчваючы інжынерыяй. Існуе вялікая колькасць праграмнага забеспячэння, які спецыялізуецца менавіта на гэтым напрамку. На жаль, далёка не ўсе карыстальнікі ведаюць, што звычайны таблічны працэсар Excel мае ў сваім арсенале прылады для выканання прагназавання, якія па сваёй эфектыўнасці мала чым саступаюць прафесійным праграмах. Давайце высвятлім, што гэта за інструменты, і як зрабіць прагноз на практыцы.
працэдура прагназавання
Мэтай любога прагназавання з'яўляецца выяўленне бягучай тэндэнцыі, і вызначэнне меркаванага выніку ў дачыненні да вывучаемага аб'екта на пэўны момант часу ў будучыні.
Спосаб 1: лінія трэнду
Адным з самых папулярных відаў графічнага прагназавання ў Эксэля з'яўляецца экстрапаляцыя выкананая пабудовай лініі трэнду.
Паспрабуем прадказаць суму прыбытку прадпрыемства праз 3 гады на аснове дадзеных па гэтым паказчыку за папярэднія 12 гадоў.
- Будуем графік залежнасці на аснове таблічных дадзеных, якія складаюцца з аргументаў і значэнняў функцыі. Для гэтага вылучаем таблічную вобласць, а затым, знаходзячыся ва ўкладцы "Устаўка", Клікаем па значку патрэбнага віду дыяграмы, які знаходзіцца ў блоку "Дыяграмы". Затым выбіраем прыдатны для канкрэтнай сітуацыі тып. Лепш за ўсё абраць кропкавую дыяграму. Можна выбраць і іншы выгляд, але тады, каб дадзеныя адлюстроўваліся карэктна, прыйдзецца выканаць рэдагаванне, у прыватнасці прыбраць лінію аргументу і выбраць іншую шкалу гарызантальнай восі.
- Цяпер нам трэба пабудаваць лінію трэнду. Робім пстрычка правай кнопкай мышы па любой з кропак дыяграмы. У Актываваць кантэкстным меню спыняем выбар на пункце "Дадаць лінію трэнду".
- Адкрываецца акно фарматавання лініі трэнду. У ім можна выбраць адзін з шасці відаў апраксімацыі:
- лінейная;
- лагарыфмічная;
- экспанентны;
- ступенная;
- паліномны;
- лінейная фільтраванне.
Давайце для пачатку абярэм лінейную апраксімацыю.
У блоку налад "Прагноз" ў полі "Наперад на" усталёўваем лік "3,0", Так як нам трэба скласці прагноз на тры гады наперад. Акрамя таго, можна ўсталяваць галачкі каля налад "Паказваць раўнанне на дыяграме" і "Змясціць на дыяграме велічыню дакладнасці апраксімацыі (R ^ 2)". Апошні паказчык адлюстроўвае якасць лініі трэнду. Пасля таго, як налады зроблены, ціснем на кнопку "Зачыніць".
- Лінія трэнду пабудавана і па ёй мы можам вызначыць прыкладную велічыню прыбытку праз тры гады. Як бачым, да таго часу яна павінна пераваліць за 4500 тыс. Рублёў. каэфіцыент R2, Як ужо было сказана вышэй, адлюстроўвае якасць лініі трэнду. У нашым выпадку велічыня R2 складае 0,89. Чым вышэй каэфіцыент, тым вышэй дакладнасць лініі. Максімальная велічыня яго можа быць роўнай 1. Прынята лічыць, што пры каэфіцыенце звыш 0,85 лінія трэнду з'яўляецца дакладнай.
- Калі ж вас не задавальняе ўзровень дакладнасці, то можна вярнуцца ў акно фармату лініі трэнду і выбраць любы іншы тып апраксімацыі. Можна перакаштаваць усе даступныя варыянты, каб знайсці найбольш дакладны.
Трэба заўважыць, што эфектыўным прагноз з дапамогай экстрапаляцыі праз лінію трэнду можа быць, калі перыяд прагназавання не перавышае 30% ад аналізаванай базы перыядаў. Гэта значыць, пры аналізе перыяду ў 12 гадоў мы не можам скласці эфектыўны прагноз больш чым на 3-4 года. Але нават у гэтым выпадку ён будзе адносна пэўным, калі за гэты час не будзе ніякіх форс-мажораў ці наадварот надзвычай спрыяльных абставінаў, якіх не было ў папярэдніх перыядах.
Урок: Як пабудаваць лінію трэнду ў Excel
Спосаб 2: аператар ПРЕДСКАЗ
Экстрапаляцыю для таблічных дадзеных можна вырабіць праз стандартную функцыю Эксэля ПРЕДСКАЗ. Гэты аргумент ставіцца да катэгорыі статыстычных інструментаў і мае наступны сінтаксіс:
= ПРЕДСКАЗ (X; известные_значения_y; вядомыя значения_x)
"X" - гэта аргумент, значэнне функцыі для якога трэба вызначыць. У нашым выпадку ў якасці аргументу будзе выступаць год, на які варта вырабіць прагназаванне.
"Вядомыя значэння y" - база вядомых значэнняў функцыі. У нашым выпадку ў яе ролі выступае велічыня прыбытку за папярэднія перыяды.
"Вядомыя значэння x" - гэта аргументы, якім адпавядаюць вядомыя значэння функцыі. У іх ролі ў нас выступае нумарацыя гадоў, за якія была сабрана інфармацыя аб прыбытку папярэдніх гадоў.
Натуральна, што ў якасці аргументу не абавязкова павінен выступаць часовай адрэзак. Напрыклад, ім можа з'яўляцца тэмпература, а значэннем функцыі можа выступаць ўзровень пашырэння вады пры награванні.
Пры вылічэнні дадзеным спосабам выкарыстоўваецца метад лінейнай рэгрэсіі.
Давайце разбярэм нюансы прымянення аператара ПРЕДСКАЗ на канкрэтным прыкладзе. Возьмем усю тую ж табліцу. Нам трэба будзе даведацца прагноз прыбылі на 2018 год.
- Вылучаем незапоўненую вочка на лісце, куды плануецца выводзіць вынік апрацоўкі. Ціснем на кнопку "Уставіць функцыю".
- адкрываецца майстар функцый. У катэгорыі "Статыстычныя" вылучаем найменне "ПРЕДСКАЗ", А затым пстрыкаем па кнопцы "OK".
- Запускаецца акно аргументаў. У полі "X" паказваем велічыню аргументу, да якога трэба адшукаць значэнне функцыі. У нашым выпадкам гэта 2018 год. Таму ўносім запіс "2018". Але лепш пазначыць гэты паказчык у вочку на лісце, а ў полі "X" проста даць спасылку на яго. Гэта дазволіць у будучыні аўтаматызаваць вылічэнні і пры патрэбе лёгка змяняць год.
У полі "Вядомыя значэння y" паказваем каардынаты слупка "Прыбытак прадпрыемства". Гэта можна зрабіць, усталяваўшы курсор у полі, а затым, заціснуўшы левую кнопку мышы і вылучыўшы адпаведны слупок на лісце.
Аналагічным чынам у поле "Вядомыя значэння x" ўносім адрас слупка "Год" з дадзенымі за мінулы перыяд.
Пасля таго, як уся інфармацыя занесеная, ціснем на кнопку "OK".
- Аператар вырабляе разлік на падставе уведзеных дадзеных і выводзіць вынік на экран. На 2018 год плануецца прыбытак у раёне 4564,7 тыс. Рублёў. На аснове атрыманай табліцы мы можам пабудаваць графік пры дапамозе інструментаў стварэння дыяграмы, аб якіх ішла гаворка вышэй.
- Калі памяняць год у вочку, якая выкарыстоўвалася для ўводу аргументу, то адпаведна зменіцца вынік, а таксама аўтаматычна абновіцца графік. Напрыклад, па прагнозах у 2019 годзе сума прыбытку складзе 4637,8 тыс. Рублёў.
Але не варта забываць, што, як і пры пабудове лініі трэнду, адрэзак часу да прагназуемага перыяду не павінен перавышаць 30% ад усяго тэрміну, за які назапашвалася база дадзеных.
Урок: Экстрапаляцыя у Excel
Спосаб 3: аператар тэндэнцыю
Для прагназавання можна выкарыстоўваць яшчэ адну функцыю - тэндэнцыю. Яна таксама адносіцца да катэгорыі статыстычных аператараў. Яе сінтаксіс шмат у чым нагадвае сінтаксіс інструмента ПРЕДСКАЗ і выглядае наступным чынам:
= Тэндэнцыю (Славутыя значения_y; вядомыя значения_x; новые_значения_x; [канст])
Як бачым, аргументы "Вядомыя значэння y" і "Вядомыя значэння x" цалкам адпавядаюць аналагічным элементам аператара ПРЕДСКАЗ, А аргумент "Новыя значэння x" адпавядае аргументу "X" папярэдняга інструмента. Акрамя таго, у тэндэнцыю маецца дадатковы аргумент "Канстанта", Але ён не з'яўляецца абавязковым і выкарыстоўваецца толькі пры наяўнасці пастаянных фактараў.
Дадзены аператар найбольш эфектыўна выкарыстоўваецца пры наяўнасці лінейнай залежнасці функцыі.
Паглядзім, як гэты інструмент будзе працаваць усе з тым жа масівам дадзеных. Каб параўнаць атрыманыя вынікі, кропкай прагназавання вызначым 2019 год.
- Вырабляем абазначэнне вочкі для вываду выніку і запускаем майстар функцый звычайным спосабам. У катэгорыі "Статыстычныя" знаходзім і вылучаем найменне "Тэндэнцыя". Ціснем на кнопку "OK".
- Адкрываецца акно аргументаў аператара тэндэнцыю. У полі "Вядомыя значэння y" ўжо апісаным вышэй спосабам заносім каардынаты калонкі "Прыбытак прадпрыемства". У полі "Вядомыя значэння x" ўводзім адрас слупка "Год". У полі "Новыя значэння x" заносім спасылку на вочка, дзе знаходзіцца нумар года, на які трэба паказаць прагноз. У нашым выпадку гэта 2019 год. поле "Канстанта" пакідаем пустым. Пстрыкаем па кнопцы "OK".
- Аператар апрацоўвае дадзеныя і выводзіць вынік на экран. Як бачым, сума прагназуемай прыбытку на 2019 г., разлічаная метадам лінейнай залежнасці, складзе, як і пры папярэднім метадзе разліку, 4637,8 тыс. Рублёў.
Спосаб 4: аператар РОСТ
Яшчэ адной функцыяй, з дапамогай якой можна вырабляць прагназаванне ў Эксэля, з'яўляецца аператар РОСТ. Ён таксама ставіцца да статыстычнай групе інструментаў, але, у адрозненне ад папярэдніх, пры разліку прымяняе не метад лінейнай залежнасці, а экспаненцыяльнай. Сінтаксіс гэтага інструмента выглядае такім чынам:
= РОСТ (Славутыя значения_y; вядомыя значения_x; новые_значения_x; [канст])
Як бачым, аргументы ў дадзенай функцыі ў дакладнасці паўтараюць аргументы аператара тэндэнцыю, Так што другі раз на іх апісанні спыняцца не будзем, а адразу пяройдзем да ўжывання гэтага інструмента на практыцы.
- Вылучаем вочка вываду выніку і ўжо звыклым шляхам выклікаем майстар функцый. У спісе статыстычных аператараў шукаем пункт "РОСТ", Вылучаем яго і пстрыкаем па кнопцы "OK".
- Адбываецца актывацыя вокны аргументаў названай вышэй функцыі. Ўводзім ў палі гэтага акна дадзеныя цалкам аналагічна таму, як мы іх ўводзілі ў акне аргументаў аператара тэндэнцыю. Пасля таго, як інфармацыя занесеная, ціснем на кнопку "OK".
- Вынік апрацоўкі дадзеных выводзіцца на манітор у паказанай раней вочку. Як бачым, на гэты раз вынік складае 4682,1 тыс. Рублёў. Адрозненні ад вынікаў апрацоўкі дадзеных аператарам тэндэнцыю нязначныя, але яны маюцца. Гэта звязана з тым, што дадзеныя інструменты ўжываюць розныя метады разліку: метад лінейнай залежнасці і метад экспаненцыяльнай залежнасці.
Спосаб 5: аператар лінейна
аператар лінейна пры вылічэнні выкарыстоўвае метад лінейнага набліжэння. Яго не варта блытаць з метадам лінейнай залежнасці, выкарыстоўваным інструментам тэндэнцыю. Яго сінтаксіс мае такі выгляд:
= Лінейна (Славутыя значения_y; вядомыя значения_x; новые_значения_x; [канст]; [статыстыка])
Апошнія два аргументу з'яўляюцца неабавязковымі. З першымі ж двума мы знаёмыя па папярэдніх спосабам. Але вы, напэўна, заўважылі, што ў гэтай функцыі адсутнічае аргумент, які паказвае на новыя значэння. Справа ў тым, што дадзены інструмент вызначае толькі змена велічыні выручкі за адзінку перыяду, які ў нашым выпадку роўны аднаму годзе, а вось агульны вынік нам трэба падлічыць асобна, дадаўшы да апошняга фактычнаму значэнні прыбытку вынік вылічэнні аператара лінейна, Памножаны на колькасць гадоў.
- Вырабляем вылучэнне вочкі, у якой будзе праводзіцца вылічэнне і запускаем Майстар функцый. вылучаем найменне "Лінейна" у катэгорыі "Статыстычныя" і ціснем на кнопку "OK".
- У полі "Вядомыя значэння y", Які адкрыўся акна аргументаў, уводны каардынаты слупка "Прыбытак прадпрыемства". У полі "Вядомыя значэння x" ўносім адрас калонкі "Год". Астатнія поля пакідаем пустымі. Затым ціснем на кнопку "OK".
- Праграма разлічвае і выводзіць у выбраную вочка значэнне лінейнага трэнду.
- Цяпер нам трэба высветліць велічыню прагназуемай прыбытку на 2019 г.. усталёўваем знак "=" у любую пустую ячэйку на лісце. Клікаем па вочку, у якой утрымліваецца фактычная велічыня прыбытку за апошні вывучаецца год (2016 г.). ставім знак "+". Далей клікаем па вочку, у якой утрымліваецца разлічаны раней лінейны трэнд. ставім знак "*". Так як паміж апошнім годам вывучаемага перыяду (2016) і годам на які трэба зрабіць прагноз (2019 г.) ляжыць тэрмін у тры гады, то усталёўваем ў вочку лік "3". Каб вырабіць разлік клікаем па кнопцы Enter.
Як бачым, прагназуемая велічыня прыбытку, разлічаная метадам лінейнага набліжэння, у 2019 годзе складзе 4614,9 тыс. Рублёў.
Спосаб 6: аператар ЛГРФПРИБЛ
Апошні інструмент, які мы разгледзім, будзе ЛГРФПРИБЛ. Гэты аператар вырабляе разлікі на аснове метаду экспанентна набліжэння. Яго сінтаксіс мае наступную структуру:
= ЛГРФПРИБЛ (Славутыя значения_y; вядомыя значения_x; новые_значения_x; [канст]; [статыстыка])
Як бачым, усе аргументы цалкам паўтараюць адпаведныя элементы папярэдняй функцыі. Алгарытм разліку прагнозу крыху зменіцца. Функцыя разлічыць экспанентны трэнд, які пакажа, у колькі разоў зменіцца сума выручкі за адзін перыяд, гэта значыць, за год. Нам трэба будзе знайсці розніцу ў прыбытку паміж апошнім фактычным перыядам і першым планавым, памножыць яе на колькасць планавых перыядаў (3) і дадаць да выніку суму апошняга фактычнага перыяду.
- У спісе аператараў Майстры функцый вылучаем найменне "ЛГРФПРИБЛ". Робім пстрычка па кнопцы "OK".
- Запускаецца акно аргументаў. У ім ўносім дадзеныя дакладна так, як гэта рабілі, ужываючы функцыю лінейна. Пстрыкаем па кнопцы "OK".
- Вынік экспанентна трэнду падлічаны і выведзены ў пазначаную вочка.
- ставім знак "=" у пустое вочка. Адкрываем дужкі і вылучаем вочка, якая змяшчае значэнне выручкі за апошні фактычны перыяд. ставім знак "*" і вылучаем вочка, якая змяшчае экспанентны трэнд. Ставім знак мінус і зноў клікаем па элементу, у якім знаходзіцца велічыня выручкі за апошні перыяд. Закрываем дужку і ўбіваем сімвалы "*3+" без двукоссяў. Зноў клікаем па той жа вочку, якую вылучалі ў апошні раз. Для правядзення разліку ціснем на кнопку Enter.
Прагназуемая сума прыбытку у 2019 годзе, якая была разлічана метадам экспанентна набліжэння, складзе 4639,2 тыс. Рублёў, што зноў не моцна адрозніваецца ад вынікаў, атрыманых пры вылічэнні папярэднімі спосабамі.
Урок: Іншыя статыстычныя функцыі ў Excel
Мы высветлілі, якімі спосабамі можна вырабіць прагназаванне у праграме Эксэля. Графічным шляхам гэта можна зрабіць праз прымяненне лініі трэнду, а аналітычным - выкарыстоўваючы цэлы шэраг убудаваных статыстычных функцый. У выніку апрацоўкі ідэнтычных дадзеных гэтымі аператарамі можа атрымацца розны вынік. Але гэта не дзіўна, бо ўсе яны выкарыстоўваюць розныя метады разліку. Калі ваганне невялікае, то ўсе гэтыя варыянты, прыдатныя да канкрэтнага выпадку, можна лічыць адносна пэўнымі.
