Предвидувањата се многу важен елемент во речиси секое поле на активност, почнувајќи од економија до инженеринг. Има голем број на софтвер специјализиран во оваа област. За жал, не сите корисници знаат дека вообичаениот Excel табеларен процесор има во своите арсенал алатки за вршење на прогнозирање, кои според нивната ефикасност не се многу инфериорни во однос на професионалните програми. Ајде да дознаеме што се овие алатки и како да направите прогноза во пракса.
Процедура за предвидување
Целта на секое предвидување е да се идентификува тековниот тренд и да се одреди очекуваниот резултат во однос на објектот под студија во одреден момент во иднина.
Метод 1: тренд линија
Еден од најпопуларните типови на графички прогнози во Excel е екстраполација извршена со изградба на тренд линија.
Да се обидеме да го предвидиме износот на добивката на претпријатието во 3 години врз основа на податоците за овој индикатор за претходните 12 години.
- Изгради графикон за зависност врз основа на табеларни податоци кои се состојат од аргументи и вредности на функцијата. За да го направите ова, одберете го табелата, а потоа, во табулаторот "Вметни", кликнете на иконата на посакуваниот тип на дијаграм, кој се наоѓа во блокот "Графики". Потоа го одбираме соодветниот тип за специфичната ситуација. Најдобро е да се избере графицирачка шема. Можете да изберете друг изглед, но потоа, за правилно прикажување на податоците, ќе треба да ја уредите, особено, да ја отстраните линијата на аргументи и да изберете различна големина на хоризонталната оска.
- Сега треба да изградиме тренд линија. Десен-клик на која било точка од дијаграмот. Во активираното контекстно мени, запрете го изборот на ставката "Додај тренд линија".
- Се отвора прозорецот за форматирање на тренд линија. Можно е да се избере еден од шесте видови на приближување:
- Линеарна;
- Логаритамски;
- Експоненцијална;
- Моќ;
- Полином;
- Линеарно филтрирање.
Да почнеме со линеарна апроксимација.
Во полето за поставки "Прогноза" во полето "Напред на" Поставете го бројот "3,0", бидејќи треба да направиме прогноза три години пред нас. Покрај тоа, можете да ги проверите полињата "Прикажи ја равенката на табелата" и "Ставете го графикот вредноста на точноста на приближувањето (R ^ 2)". Последниот индикатор го покажува квалитетот на линијата на трендови. По поставките, кликнете на копчето. "Затвори".
- Трендот линија е изграден и можеме да го искористиме за да се утврди приближниот износ на добивка по три години. Како што можете да видите, до тоа време треба да помине за 4.500 илјади рубли. Коефициент R2, како што е споменато погоре, го прикажува квалитетот на линијата на трендови. Во нашиот случај, вредноста R2 е горе 0,89. Колку е поголем коефициентот, толку е поголема сигурноста на линијата. Неговата максимална вредност може да биде еднаква 1. Се смета дека кога односот е завршен 0,85 тренд линија е сигурен.
- Ако не сте задоволни со нивото на доверба, тогаш можете да се вратите во прозорецот за формат на тренд линија и да изберете било кој друг тип на приближување. Можете да ги испробате сите достапни опции за да најдете најточни.
Треба да се напомене дека ефективната прогноза со користење на екстраполација преку линијата на трендови може да биде ако периодот на прогноза не надминува 30% од анализираниот период. Тоа е, во анализата на периодот од 12 години, не можеме да направиме ефикасна прогноза повеќе од 3-4 години. Но, дури и во овој случај, тоа ќе биде релативно сигурно, ако во ова време нема да има виша сила или, напротив, исклучително поволни околности, кои не беа во претходните периоди.
Лекција: Како да се изгради тренд линија во Excel
Метод 2: оператор ПРОЕКТ
Екстраполација за табеларните податоци може да се направи преку стандардна Excel функција. ПРОГНОЗА. Овој аргумент припаѓа на категоријата на статистички алатки и ја има следнава синтакса:
= PREDICT (x;
"X" е аргумент, вредноста на функцијата за која сакате да ја одредите. Во нашиот случај, аргументот ќе биде година за која треба да се направи прогноза.
"Познати вредности на Y" - база на познати вредности на функцијата. Во нашиот случај, неговата улога е износот на добивката за претходните периоди.
"Познати x" - Ова се аргументите кои одговараат на познатите вредности на функцијата. Во нивната улога имаме нумерирање на годините за кои се собираат информации за добивката од претходните години.
Природно, аргументот не мора да биде временски период. На пример, може да биде температура, а вредноста на функцијата може да биде ниво на експанзија на водата кога се загрева.
При пресметувањето на овој метод се користи методот на линеарна регресија.
Ајде да ги разгледаме нијансите на операторот ПРОГНОЗА на специфичен пример. Земете ја иста маса. Ќе треба да ја знаеме прогнозата за добивката за 2018 година.
- Изберете ја празната ќелија на листот каде што сакате да го прикажете резултатот од обработката. Ние го притискаме копчето "Вметни ја функцијата".
- Отвора Функционален волшебник. Во категоријата "Статистички" одберете го името "ПРОГНОЗА"и потоа кликнете на копчето "Добро".
- Почнува аргументот. Во полето "X" наведете ја вредноста на аргументот за кој сакате да ја пронајдете вредноста на функцијата. Во нашиот случај, ова е 2018 година. Затоа, правиме рекорд "2018". Но, подобро е да го покажете овој индикатор во ќелијата на листот, и во полето "X" само дајте линк до него. Ова ќе ви овозможи да ги автоматизирате пресметките во иднина и лесно да ја промените годината доколку е потребно.
Во полето "Познати вредности на Y" наведете ги координатите на колоната "Добивка на претпријатието". Ова може да се направи со поставување на курсорот во полето, а потоа држејќи го левото копче на глувчето и со избирање на соодветната колона на листот.
Слично на ова поле "Познати x" ние внесуваме адреса на колоната "Година" со податоци за изминатиот период.
Откако ќе влезете сите информации, кликнете на копчето. "Добро".
- Операторот пресметува врз основа на внесените податоци и го прикажува резултатот на екранот. За 2018 година, се планира профит во областа на 4564.7 илјади рубли. Врз основа на добиената табела, можеме да конструираме графикон користејќи ги алатките за креирање на табели, кои беа дискутирани погоре.
- Ако ја промените годината во ќелијата што беше користена за да го внесете аргументот, резултатот ќе се промени соодветно, а графиконот автоматски ќе се ажурира. На пример, според предвидувањата во 2019 година, износот на добивка ќе изнесува 4637,8 илјади рубли.
Но, не заборавајте дека, како и во изградбата на линијата на трендови, должината на времето пред прогнозниот период не треба да надминува 30% од целиот период за кој базата на податоци е акумулирана.
Лекција: Excel екстраполација
Метод 3: оператор ТЕНДЕНЦИЈА
За предвидување, можете да користите друга функција - ТРЕНД. Таа, исто така припаѓа на категоријата на статистички оператори. Неговата синтакса е многу слична на синтаксата на алатката. ПРОГНОЗА и изгледа вака:
= TREND (Познати values_y; познати вредности_x; new_values_x; [const])
Како што можете да видите, аргументите "Познати вредности на Y" и "Познати x" целосно одговараат на истите елементи на операторот ПРОГНОЗАи аргументот "Нови x вредности" се совпаѓа со аргументот "X" претходна алатка. Покрај тоа, ТРЕНД постои дополнителен аргумент "Константа"но тоа не е задолжително и се користи само ако постојат постојани фактори.
Овој оператор најефективно се користи во присуство на линеарна зависност на функцијата.
Ајде да видиме како оваа алатка ќе работи со ист податочен низа. Со цел да се споредат добиените резултати, ја дефинираме точката за предвидување во 2019 година.
- Ние правиме клеточна ознака за да го прикажеме резултатот и да трчаме Функционален волшебник на вообичаен начин. Во категоријата "Статистички" најдете и изберете го името "ТРЕНД". Ние го притискаме копчето "Добро".
- Се отвори прозорец на оператор ТРЕНД. Во полето "Познати вредности на Y" веќе опишани погоре, внесете ги координатите на колоната "Добивка на претпријатието". Во полето "Познати x" внесете ја адресата на колоната "Година". Во полето "Нови x вредности" внесете ја референцата на ќелијата каде што се наоѓа бројот на годината на која треба да биде наведена прогнозата. Во нашиот случај, ова е 2019 година. Поле "Константа" оставете празно. Кликнете на копчето "Добро".
- Операторот ги обработува податоците и го прикажува резултатот на екранот. Како што можете да видите, износот на проектираната добивка за 2019 година, пресметан според методот на линеарна зависност, ќе биде, како и во претходниот метод на пресметка, 4637,8 илјади рубли.
Метод 4: оператор на растот
Друга функција која може да се користи за предвидување во Excel е операторот РАСТ. Таа, исто така припаѓа на статистичката група алатки, но, за разлика од претходните, не го користи методот на линеарна зависност, туку експоненцијалниот метод за пресметка. Синтаксата на оваа алатка изгледа вака:
= Раст (Познати Values_y; Познати Values_x; New_values_x; [const])
Како што можете да видите, аргументите на оваа функција точно ги повторуваат аргументите на операторот ТРЕНДтака што ние не ќе се задржиме на нивниот опис по втор пат, туку веднаш ќе се свртиме кон примената на оваа алатка во пракса.
- Изберете ја излезната ќелија за резултати и јавете се на вообичаен начин. Функционален волшебник. Во листата на статистички оператори се бараат точка "РАСТ"изберете го и кликнете на копчето "Добро".
- Се активира прозорецот на аргументите на горенаведената функција. Внесете ги податоците во полињата на овој прозорец е сосема ист како што ги внесувавме во прозорецот на аргументот на операторот ТРЕНД. Откако ќе се внесе информацијата, кликнете на копчето "Добро".
- Резултатот од обработката на податоците се прикажува на мониторот во претходно одредената ќелија. Како што можете да видите, овој пат резултатот е 4682,1 илјади рубли. Разлики од обработка на податоци на операторот ТРЕНД незначителни, но тие се достапни. Ова се должи на фактот што овие алатки користат различни методи на пресметка: методот на линеарна зависност и методот на експоненцијална зависност.
Метод 5: оператор LINEST
Оператор ЛИНИЈА кога пресметува користи метод на линеарна приближување. Не треба да се меша со линеарен метод што го користи алатката. ТРЕНД. Неговата синтакса е:
= LINEST (Познати Values_y; Познати Values_x; New_values_x; [const]; [Статистика])
Последните два аргумента се опционални. Ние сме запознаени со првите две од претходните методи. Но, веројатно забележавте дека во оваа функција не постои аргумент што укажува на новите вредности. Факт е дека оваа алатка само ја одредува промената на приходите за единица единица, која во нашиот случај е една година, но ние мораме да го пресметаме вкупниот резултат одделно, додавајќи ја последната фактичка вредност на добивка како резултат на пресметувањето на операторот ЛИНИЈАмножи со бројот на години.
- Направете избор на ќелијата во која ќе се изврши пресметката и започнете го Master of Functions. Изберете го името "LINEYN" во категоријата "Статистички" и кликнете на копчето "Добро".
- Во полето "Познати вредности на Y"од прозорецот за аргументи што се отвара, внесете ги координатите на колоната "Добивка на претпријатието". Во полето "Познати x" внесете ја адресата на колоната "Година". Останатите полиња се оставени празни. Потоа кликнете на копчето "Добро".
- Програмата пресметува и прикажува во избраната ќелија вредноста на линеарен тренд.
- Сега треба да ја дознаеме вредноста на проектираната добивка за 2019 година. Поставете го знакот "=" на било која празна ќелија на листот. Кликнете на ќелијата која го содржи вистинскиот износ на добивка за студираната минатата година (2016 година). Ние ставивме знак "+". Следно, кликнете на ќелијата која го содржи претходно пресметаниот линеарен тренд. Ние ставивме знак "*". Бидејќи помеѓу последната година од периодот на студирање (2016) и годината за која треба да се предвиди прогнозата (2019 година), лежи период од три години, го поставивме бројот во клетката "3". За да пресметате, кликнете на копчето. Внесете.
Како што можете да видите, предвидената вредност на добивката, пресметана по методот на линеарно приближување, во 2019 година ќе изнесува 4614,9 илјади рубли.
Метод 6: оператор LOGEST
Последната алатка што ќе ја разгледаме е LGGRPRIBL. Овој оператор врши пресметки врз основа на методот на експоненцијална апроксимација. Неговата синтакса ја има следнава структура:
= LOGPLPR (Познати values_y; познати вредности_x; new_values_x; [const]; [статистика])
Како што можете да видите, сите аргументи целосно ги повторуваат соодветните елементи на претходната функција. Алгоритмот за пресметување на прогнозата малку ќе се промени. Функцијата го пресметува експоненцијалниот тренд, кој ќе покаже колку пати износот на приходи ќе се промени во еден период, односно во една година. Ние ќе треба да ја најдеме разликата во добивката помеѓу последниот актуелен период и првата планирана, да ја зголемиме според бројот на планираните периоди. (3) и додадете го резултатот збирот на последниот вистински период.
- Во листата на оператори на Волшебникот за функции, одберете го името LGRFPRIBL. Кликнете на копчето. "Добро".
- Почнува аргументот. Во него внесуваме податоци точно како што тоа го правеше, користејќи ја функцијата ЛИНИЈА. Кликнете на копчето "Добро".
- Резултатот од експоненцијалниот тренд се пресметува и прикажува во наведената ќелија.
- Ние ставивме знак "=" во празна ќелија. Отворете ги заградите и одберете ја ќелијата која ја содржи вредноста на приходите за последниот вистински период. Ние ставивме знак "*" и изберете ја ќелијата која содржи експоненцијален тренд. Ние ставиме минус знак и повторно кликнете на елементот во кој е износот на приходите за последниот период. Затворете го држачот и активирајте ги ликовите. "*3+" без наводници. Повторно, кликнете на истата ќелија која беше избрана последен пат. За пресметка кликнете на копчето Внесете.
Предвидениот износ на добивка во 2019 година, кој беше пресметан со методот на експоненцијална апроксимација, ќе изнесува 4.639,2 илјади рубли, што повторно не се разликува многу од резултатите добиени во пресметката со претходните методи.
Лекција: Други статистички функции во Excel
Дознавме како да направите предвидување во Excel програмата. Графички, ова може да се направи преку примена на линијата на трендови, и аналитички, со користење на голем број вградени статистички функции. Како резултат на обработка на идентични податоци од страна на овие оператори, може да има поинаков резултат. Но, ова не е изненадувачки, бидејќи сите тие користат различни методи на пресметка. Ако флуктуацијата е мала, тогаш сите овие опции што се применуваат за одреден случај може да се сметаат за релативно сигурни.
