Сярод розных метадаў прагназавання нельга не вылучыць апраксімацыю. З яе дапамогай можна вырабляць прыблізныя падлікі і вылічаць плануемыя паказчыкі, шляхам замены зыходных аб'ектаў на больш простыя. У Эксэля таксама існуе магчымасць выкарыстання дадзенага метаду для прагназавання і аналізу. Давайце разгледзім, як гэты метад можна ўжыць у паказанай праграмы ўбудаванымі прыладамі.
выкананне апраксімацыі
Найменне дадзенага метаду адбываецца ад лацінскага слова proxima - "бліжэйшая" Менавіта набліжэнне шляхам спрашчэння і згладжвання вядомых паказчыкаў, выбудоўванне іх у тэндэнцыю і з'яўляецца яго асновай. Але дадзены метад можна выкарыстоўваць не толькі для прагназавання, але і для даследавання ўжо наяўных вынікаў. Бо апраксімацыя з'яўляецца, па сутнасці, спрашчэннем зыходных дадзеных, а спрошчаны варыянт даследаваць лягчэй.
Галоўны інструмент, з дапамогай якога праводзіцца згладжвання ў Excel - гэта пабудова лініі трэнду. Сутнасць складаецца ў тым, што на аснове ўжо наяўных паказчыкаў дабудоўваецца графік функцыі на будучыя перыяды. Асноўнае прызначэнне лініі трэнду, як не цяжка здагадацца, гэта складанне прагнозаў або выяўленне агульнай тэндэнцыі.
Але яна можа быць пабудаваная з прымяненнем аднаго з пяці відаў апраксімацыі:
- лінейнай;
- экспаненцыяльнай;
- лагарыфмічнай;
- паліномны;
- Спаважнаю.
Разгледзім кожны з варыянтаў больш падрабязна ў асобнасці.
Урок: Як пабудаваць лінію трэнду ў Excel
Спосаб 1: лінейнае згладжванне
Перш за ўсё, давайце разгледзім самы просты варыянт апраксімацыі, а менавіта з дапамогай лінейнай функцыі. На ім мы спынімся падрабязней за ўсё, так як выкажам агульныя моманты характэрныя і для іншых спосабаў, а менавіта пабудова графіка і некаторыя іншыя нюансы, на якіх пры разглядзе наступных варыянтаў ужо спыняцца не будзем.
Перш за ўсё, пабудуем графік, на падставе якога будзем праводзіць працэдуру згладжвання. Для пабудовы графіка возьмем табліцу, у якой памесячна указана сабекошт адзінкі прадукцыі, што вырабляецца прадпрыемствам, і адпаведная прыбытак у дадзеным перыядзе. Графічная функцыя, якую мы пабудуем, будзе адлюстроўваць залежнасць павелічэння прыбытку ад змяншэння сабекошту прадукцыі.
- Для пабудовы графіка, перш за ўсё, вылучаем слупкі "Сабекошт адзінкі прадукцыі" і "Прыбытак". Пасля гэтага перамяшчаемся ва ўкладку "Устаўка". Далей на стужцы ў блоку інструментаў "Дыяграмы" пстрыкаем па кнопцы "Кропкавая". У які адкрыўся спісе выбіраем найменне "Кропкавая з гладкімі крывымі і маркерамі". Менавіта гэты від дыяграм найбольш падыходзіць для працы з лініяй трэнду, а значыць, і для прымянення метаду апраксімацыі ў Excel.
- Графік пабудаваны.
- Для дадання лініі трэнду вылучаем яго клікам правай кнопкі мышы. З'яўляецца кантэкстнае меню. Выбіраем у ім пункт "Дадаць лінію трэнду ...".
Існуе яшчэ адзін варыянт яе дадання. У дадатковай групе ўкладак на стужцы "Праца з дыяграмамі" перамяшчаемся ва ўкладку "Макет". Далей у блоку інструментаў "Аналіз" пстрыкаем па кнопцы "Лінія трэнду". Адкрываецца спіс. Бо нам трэба ўжыць лінейную апраксімацыю, то з прадстаўленых пазіцый выбіраем "Лінейнае набліжэнне".
- Калі ж вы абралі ўсё-ткі першы варыянт дзеянняў з даданнем праз кантэкстнае меню, то адкрыецца акно фармату.
У блоку параметраў "Пабудова лініі трэнду (апраксімацыя і згладжванне)" ўсталёўваем перамыкач у пазіцыю "Лінейная".
Пры жаданні можна ўсталяваць галачку каля пазіцыі "Паказваць раўнанне на дыяграме". Пасля гэтага на дыяграме будзе адлюстроўвацца раўнанне згладжваецца функцыі.Таксама ў нашым выпадку для параўнання розных варыянтаў апраксімацыі важна ўсталяваць галачку каля пункта "Змясціць на дыяграму велічыню дакладнай апраксімацыі (R ^ 2)". Дадзены паказчык можа вар'іравацца ад 0 да 1. Чым ён вышэй, тым апраксімацыя якасней (верагодней). Лічыцца, што пры велічыні гэтага паказчыка 0,85 і вышэй згладжванне можна лічыць пэўным, а калі паказчык ніжэй, то - не.
Пасля таго, як правялі ўсе вышэйпаказаныя налады. Ціснем на кнопку "Зачыніць", Размешчаную ў ніжняй частцы акна.
- Як бачым, на графіцы лінія трэнду пабудавана. Пры лінейнай апраксімацыі яна пазначаецца чорнай прамой паласой. Названы вiд згладжвання можна ўжываць у найбольш простых выпадках, калі дадзеныя змяняюцца даволі хутка і залежнасць значэння функцыі ад аргументу відавочная.
Згладжванне, якое выкарыстоўваецца ў дадзеным выпадку, апісваецца наступнай формулай:
y = ax + b
У канкрэтна нашым выпадку формула прымае такі выгляд:
y = -0,1156x + 72,255
Велічыня дакладнасці апраксімацыі ў нас роўная 0,9418, Што з'яўляецца даволі прымальным вынікам, якія характарызуюць згладжванне, як пэўнае.
Спосаб 2: экспанентны апраксімацыя
Зараз давайце разгледзім экспанентны тып апраксімацыі ў Эксэля.
- Для таго, каб змяніць тып лініі трэнду, вылучаем яе клікам правай кнопкі мышы і ў раскрыць меню выбіраем пункт "Фармат лініі трэнду ...".
- Пасля гэтага запускаецца ўжо знаёмае нам акно фармату. У блоку выбару тыпу апраксімацыі ўсталёўваем перамыкач у становішча "Экспанентны". Астатнія настаўленьні пакінем такімі ж, як і ў першым выпадку. Пстрыкаем па кнопцы "Зачыніць".
- Пасля гэтага лінія трэнду будзе пабудавана на графіцы. Як бачым, пры выкарыстанні дадзенага метаду яна мае некалькі выгнутую форму. Пры гэтым узровень дакладнасці роўны 0,9592, Што вышэй, чым пры выкарыстанні лінейнай апраксімацыі. Экспанентны метад лепш за ўсё выкарыстоўваць у тым выпадку, калі спачатку значэння хутка змяняюцца, а потым прымаюць збалансаваную форму.
Агульны выгляд функцыі згладжвання пры гэтым такі:
y = be ^ x
дзе e - гэта падстава натуральнага лагарыфма.
У канкрэтна нашым выпадку формула прыняла наступную форму:
y = 6282,7 * e ^ (- 0,012 * x)
Спосаб 3: лагарыфмічнай згладжванне
Зараз надышла чаргу разгледзець метад лагарыфмічнай апраксімацыі.
- Тым жа спосабам, што і ў папярэдні раз праз кантэкстнае меню запускаем акно фармату лініі трэнду. Ўсталёўваем перамыкач у пазіцыю "Лагарыфмічная" і ціснем на кнопку "Зачыніць".
- Адбываецца працэдура пабудовы лініі трэнду з лагарыфмічнай апраксімацыі. Як і ў папярэднім выпадку, такі варыянт лепш выкарыстаць тады, калі першапачаткова дадзеныя хутка змяняюцца, а потым прымаюць збалансаваны выгляд. Як бачым, ўзровень дакладнасці роўны 0,946. Гэта вышэй, чым пры выкарыстанні лінейнага метаду, але ніжэй, чым якасць лініі трэнду пры экспанентным згладжванне.
У агульным выглядзе формула згладжвання выглядае так:
y = a * ln (x) + b
дзе ln - гэта велічыня натуральнага лагарыфма. Адсюль і назва метаду.
У нашым выпадку формула прымае наступны выгляд:
y = -62,81ln (x) 404,96
Спосаб 4: паліномны згладжванне
Надышоў чаргу разгледзець метад паліномны згладжвання.
- Пераходзім у акно фармату лініі трэнду, як ужо рабілі не раз. У блоку "Пабудова лініі трэнду" ўсталёўваем перамыкач у пазіцыю "Паліномны". Справа ад дадзенага пункта размешчана поле "Ступень". Пры выбары значэння "Паліномны" яно становіцца актыўным. Тут можна паказаць любы ступенню значэнне ад 2 (Ўстаноўлена па змаўчанні) да 6. Дадзены паказчык вызначае лік максімумаў і мінімумаў функцыі. Пры ўсталёўцы полинома другой ступені апісваецца толькі адзін максімум, а пры ўсталёўцы полинома шостай ступені можа быць апісана да пяці максімумаў. Для пачатку пакінем налады па змаўчанні, гэта значыць, пакажам другую ступень. Астатнія налады пакідаем такімі ж, якімі мы выстаўлялі іх у папярэдніх спосабах. Ціснем на кнопку "Зачыніць".
- Лінія трэнду з выкарыстаннем дадзенага метаду пабудавана. Як бачым, яна яшчэ больш выгнутая, чым пры выкарыстанні экспаненцыяльнай апраксімацыі. Ўзровень дакладнасці вышэй, чым пры любым з выкарыстаных раней спосабаў, і складае 0,9724.
Дадзены метад найбольш паспяхова можна ўжываць у тым выпадку, калі дадзеныя носяць пастаянна зменлівы характар. Функцыя, якая апісвае дадзены выгляд згладжвання, выглядае такім чынам:
y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ nУ нашым выпадку формула прыняла такі выгляд:
y = 0,0015 * x ^ 2-1,7202 * x + 507,01 - Зараз давайце зменім ступень полиномов, каб убачыць, ці будзе адрознівацца вынік. Вяртаемся ў акно фармату. Тып апраксімацыі пакідаем паліномны, але насупраць яго ў акне ступені усталёўваем максімальна магчымае значэнне - 6.
- Як бачым, пасля гэтага наша лінія трэнду прыняла форму ярка выяўленай крывой, у якой колькасць максімумаў роўна шасці. Ўзровень дакладнасці павысіўся яшчэ больш, склаўшы 0,9844.
Формула, якая апісвае дадзены тып згладжвання, прыняла наступны выгляд:
y = 8E-08x ^ 6-0,0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09
Спосаб 5: ступень згладжванне
У завяршэнні разгледзім метад спаважнаю апраксімацыі ў Excel.
- Перамяшчаемся ў акно "Фармат лініі трэнду". Ўсталёўваем перамыкач выгляду згладжвання у пазіцыю "Ступенная". Паказ ўраўненні і ўзроўню дакладнасці, як заўсёды, пакідаем уключанымі. Ціснем на кнопку "Зачыніць".
- Праграма фармуе лінію трэнду. Як бачым, у нашым выпадку яна ўяўляе сабой лінію з невялікім выгібам. Ўзровень дакладнасці роўны 0,9618, Што з'яўляецца даволі высокім паказчыкам. З усіх вышэйапісаных спосабаў ўзровень дакладнасці быў вышэй толькі пры выкарыстанні паліномны метаду.
Дадзены спосаб эфектыўна выкарыстоўваецца ў выпадках інтэнсіўнага змены дадзеных функцыі. Важна ўлічыць, што гэты варыянт выкарыстоўваецца і ў дачыненні толькі пры ўмове, што функцыя і аргумент не прымаюць адмоўных ці нулявых значэнняў.
Агульная формула, якая апісвае дадзены метад мае такі выгляд:
y = bx ^ n
У канкрэтна нашым выпадку яна выглядае так:
y = 6E + 18x ^ (- 6,512)
Як бачым, пры выкарыстанні канкрэтных дадзеных, якія мы ўжывалі для прыкладу, найбольшы ўзровень дакладнасці паказаў метад паліномны апраксімацыі з полиномом ў шостай ступені (0,9844), Найменшы ўзровень дакладнасці ў лінейнага метаду (0,9418). Але гэта зусім не значыць, што такая ж тэндэнцыя будзе пры выкарыстанні іншых прыкладаў. Няма, узровень эфектыўнасці ў прыведзеных вышэй метадаў можа значна адрознівацца, у залежнасці ад пэўнага выгляду функцыі, для якой будзе будавацца лінія трэнду. Таму, калі для гэтай функцыі выбраны метад найбольш эфектыўны, то гэта зусім не азначае, што ён таксама будзе аптымальным і ў іншай сітуацыі.
Калі вы пакуль не можаце адразу вызначыць, грунтуючыся на вышэйпрыведзеных рэкамендацыях, які выгляд апраксімацыі падыдзе менавіта ў вашым выпадку, то ёсць сэнс паспрабаваць усё метады. Пасля пабудовы лініі трэнду і прагляду яе ўзроўню дакладнасці можна будзе выбраць аптымальны варыянт.
