Алати за прогнозирање у програму Мицрософт Екцел

Прогноза је веома важан елемент у скоро свакој области активности, од економије до инжењеринга. Постоји велика количина софтвера који се специјално специјализује у том правцу. Нажалост, сви корисници не знају да уобичајена табела Екцел има своје арсеналне алате за предвиђање, које својим ефективности нису много инфериорније од професионалних програма. Хајде да сазнамо шта су ови алати и како направити предвиђање у пракси.

Процес прогнозирања

Циљ било којег предвиђања је идентификација тренутног тренда и одредити очекивани резултат у односу на предмет који се проучава у одређеном тренутку у будућности.

Метод 1: Трендлине

Један од најпопуларнијих врста графичког предвиђања у Екцелу је екстраполација извршена изградњом линије трендова.

Покушајмо прогнозирати профит предузећа за 3 године на основу података о овом показатељу за претходних 12 година.

1. Зависимо зависност на основу табеларних података који се састоје од аргумената и вредности функције. Да бисте то урадили, изаберите област таблице, а затим, док се налазите на картици "Убаци" , кликните на икону жељеног дијаграма који се налази у дијаметру "Дијаграми" . Затим одаберите одговарајући тип за одређену ситуацију. Најбоље је одабрати битну слику. Можете да изаберете другачији приказ, али онда, како би се подаци правилно приказали, мораћете да изводите измене, посебно уклоните линију аргумената и изаберете другу скалу за хоризонталну осу.
2. Сада морамо направити линију трендова. Десни клик на било који од тачака графикона. У активираном контекстном менију изаберите "Додај линију тренда" .
3. Отвара се прозор за форматирање линије тренда. Може изабрати једну од шест врста апроксимације:
   • Линеар ;
   • Логаритамски ;
   • Експоненцијални ;
   • Степен снаге ;
   • Полиномиал ;
   • Линеарно филтрирање .

   Хајде прво да изаберемо линеарну апроксимацију.

   У оквиру поставки "Прогноза" у пољу "Прослеђивање" поставите број "3.0" , јер морамо направити прогнозу за три године у будућности. Поред тога, можете да проверите поставке "Покажите једначину на дијаграму" и "Поставите вредност прецизности апроксимације (Р ^ 2) на дијаграму" . Последњи индикатор одражава квалитет линије тренда. Након подешавања, кликните на дугме "Затвори" .

4. Линија трендова је изграђена и на њему можемо утврдити приближни износ добити у року од три године. Као што видимо, до тада би требало да премаши 4500 хиљада рубаља. Коефицијент Р2 , као што је горе речено, одражава квалитет линије трендова. У нашем случају, Р2 је 0.89 . Што је већи коефицијент, већа је поузданост линије. Његова максимална вредност може бити једнака 1 . Опћенито је прихваћено да је са коефицијентом изнад 0,85 линија трендова поуздана.
5. Ако нисте задовољни степеном поузданости, можете се вратити у прозор формата линије трендова и изабрати било коју другу врсту апроксимације. Можете пробати све расположиве опције да бисте нашли најтачније.

   Треба напоменути да је ефикасна прогноза која користи екстраполацију кроз линију тренда може бити ако процијењени период не прелази 30% базиране на анализираном периоду. То јест, када анализирамо период од 12 година, не можемо направити ефикасну прогнозу више од 3-4 године. Али чак иу овом случају, то ће бити релативно поуздано, уколико током овог времена неће бити више силе или напротив изузетно повољних околности које нису биле у претходним периодима.

Лекција: Како изградити линију трендова у Екцелу

Метод 2: Оператор ПРЕДИЦТИОН

Екстраполација за табеларне податке може се извршити помоћу стандардне функције Еккел ПРЕСЦАСЕ . Овај аргумент спада у категорију статистичких алата и има следећу синтаксу:

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

"Кс" је аргумент чија вредност функције треба одредити. У нашем случају, година у којој се очекује прогнозирање служиће као аргумент.

"Познате вредности и" су основа познатих вриједности функције. У нашем случају, улога профита за претходне периоде је у њеној улози.

"Познате вредности к" су аргументи којима одговарају познате вредности функције. У својој улози, ми смо бројани годинама, за које су прикупљене информације о добити из претходних година.

Наравно, временска линија не мора нужно бити аргумент. На пример, може бити температура, а вредност функције може бити ниво ширења воде када се загреје.

При израчунавању ове методе користи се метод линеарне регресије.

Хајде да погледамо нијансе коришћења оператора ПРЕСЦАСЕ за одређени пример. Узми исти сто. Мораћемо да сазнамо прогнозу профита за 2018. годину.

1. Изаберите празну ћелију на листу где желите да одштампате резултат обраде. Кликните на дугме "Убаци функцију" .
2. Отвара се Чаробњак за функције . У категорији "Статистички" одаберите назив "ПРЕСЦАСЕ" , а затим кликните на дугме "ОК" .
3. Почиње прозор аргумената. У пољу "Кс" унесемо вредност аргумента на који желимо да пронађемо вредност функције. У нашем случају, ово је 2018. Стога улазимо у унос "2018" . Али боље је да наведете овај индикатор у ћелији на листу, а у пољу "Кс" само наведите везу са њом. Ово ће у будућности омогућити аутоматизацију прорачуна и, ако је потребно, лако промијенити годину.

   У пољу "Познате вредности и" уносимо координате колоне "Ентерприсе профит" . Ово се може урадити постављањем курсора на пољу, а затим држећи лијево дугме миша и одабиром одговарајуће колоне на листу.

   Слично томе, у пољу "Познате вредности к" уносимо адресу колоне "Година" са подацима за протекли период.

   Након што унесете све информације, кликните на дугме "ОК" .

4. Оператор израчунава на основу унетих података и приказује резултат на екрану. За 2018. планиран је профит од око 4564,7 хиљада рубаља. На основу добијене табеле, можемо графикон графикон употребљавати помоћу алата за графикон који су горе описани.
5. Ако промените годину у ћелију која је коришћена за унос аргумента, резултат ће се променити у складу с тим, а графикон ће се аутоматски ажурирати. На пример, према прогнозама из 2019. године, износ добити ће бити 4637,8 хиљада рубаља.

Али не заборавите да, као иу изградњи линије трендова, временски интервал до прогнозираног периода не би требало да прелази 30% целокупног периода за који је збирка базе података накупљена.

Лекција: Екстраполација у Екцелу

Метод 3: ТРЕНДС оператора

За предвиђање можете користити још једну функцију - ТРЕНДОВИ . Такође спада у категорију статистичких оператора. Његова синтакса на много начина подсећа на синтаксу алата ПРЕСЦАСЕ и изгледа овако:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Као што видите, аргументи "Познате вредности и" и "Познате вредности к" у потпуности одговарају сличним елементима оператора ПРЕСЦАСЕ , а аргумент "Нове вредности к" одговара аргументу "Кс" претходног алата. Поред тога, ТРЕНДОВИ имају додатни аргумент "Константа" , али то није обавезно и користи се само ако постоје константни фактори.

Овај оператор се најефикасније користи у присуству линеарне зависности функције.

Да видимо како ће овај алат функционисати све са истим низом података. Да бисмо успоредили добијене резултате, одредићемо тачку прогнозирања у 2019. години.

1. Именујемо ћелију за резултат резултата и покренемо чаробњак за функцију на уобичајени начин. У категорији "Статистички" проналазимо и изаберемо назив "ТРЕНДОВИ" . Кликните на дугме "ОК" .
2. Отвара оквир аргументације ТЕНДЕНЦИ оператора. У пољу "Познате вредности и" уносимо координате колоне "Ентерприсе Профит " на горе описани начин. У пољу "Познате вредности к" унесите адресу колоне "Година" . У пољу "Нев к валуес" уносимо референцу на ћелију гдје се налази број године, на који желите да наведете прогнозу. У нашем случају, ово је 2019. Поље "Константно" остане празно. Кликом на дугме "ОК" .
3. Оператор обрађује податке и приказује резултат на екрану. Као што можете видети, сума прогнозиране добити за 2019, израчуната методом линеарне зависности, направиће, као у претходном начину обрачуна, 4637,8 хиљада рубаља.

Метод 4: Оператор РАСТ

Друга функција са којом можете направити предвиђања у програму Екцел је оператер РАСТ. Такође се односи на статистичку групу алата, али за разлику од претходних, метода се примењује не методом линеарне зависности, већ експоненцијалном методом. Синтакса овог алата изгледа овако:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Као што видите, аргументи ове функције тачно понављају аргументе оператора ТЕНДЕНЦИ , тако да их нећемо дискутовати по други пут, али ћемо се одмах обратити апликацији овог алата у пракси.

1. Изаберите излазну излазну ћелију и позовите чаробњака за функцију на уобичајени начин. На листи статистичких оператора потражите ставку "РАСТ" , изаберите је и кликните на дугме "ОК" .
2. Прозор аргумента горе наведене функције је активиран. Ми уносимо податке у поља овог прозора на исти начин као што смо их унели у прозор аргумента ТЕНДЕНЦИ оператора. Након уноса информација, кликните на дугме "ОК" .
3. Резултат обраде података се приказује на екрану у претходно наведеној ћелији. Као што видите, овог пута резултат је 4682,1 хиљада рубаља. Разлике од резултата обраде података од стране оператора ТЕНДЕНЦИЈА су незнатне, али су доступне. Ово је због чињенице да ови алати користе различите методе израчунавања: метод линеарне зависности и метод експоненцијалне зависности.

Метод 5: ЛИНЕСТ оператер

Оператор ЛИНЕСТ користи метод линеарне апроксимације. Не сме се мешати са методом линеарне зависности коју користи алат ТРЕНДС . Његова синтакса је ово:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Последње две аргументације су необавезне. Са првим двоје смо упознати са претходним методама. Али вероватно сте приметили да у овој функцији нема аргумента који указује на нове вредности. Чињеница је да ова алатка одређује само промјену у висини прихода по јединичном периоду, што је у нашем случају једне године, али укупни износ се рачуна засебно, додајући последњој вриједности стварне добити резултат израчунавања ЛИНЕСТ оператора помноженог са бројем година.

1. Изаберемо ћелију у којој ће се израчунати и покренути Чаробњак за функције. Изаберите назив "ЛИНЕСТ" у категорији "Статистички" и кликните на дугме "ОК" .
2. У пољу "Познате вредности и" отворени прозор аргумента унесите координате колоне "Ентерприсе Профит". У пољу "Познате вредности к" уносимо адресу колоне "Година" . Преостала поља остају празна. Затим кликните на дугме "ОК" .
3. Програм израчунава и приказује вредност линеарног тренда у одабраној ћелији.
4. Сада морамо да сазнамо вредност прогнозиране добити за 2019. Поставите знак "=" у било коју празну ћелију на листу. Кликнемо на ћелију, која садржи стварни износ добити за студијску годину (2016.). Ставите знак "+" . Затим кликните на ћелију која садржи претходно израчунати линеарни тренд. Ставили смо знак "*" . Будући да је између прошле године студијског периода (2016) и године за коју се предвиђа прогноза (2019), период је три године, онда ћемо у ћелију поставити број "3" . Да бисте израчунали, кликните на дугме Ентер .

Као што видимо, очекивани профит, израчунат методом линеарне апроксимације, 2019. године износи 4614,9 хиљада рубаља.

Метод 6: оператор ЛГРФПРИБЛ

Последња алатка коју ћемо размотрити ће бити ЛГРФ . Овај оператор обавља прорачуне на основу експоненцијалног метода апроксимације. Његова синтакса има слиједећу структуру:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Као што видите, сви аргументи у потпуности понављају одговарајуће елементе претходне функције. Алгоритам за израчунавање прогнозе ће се мало променити. Функција ће израчунати експоненцијални тренд, који ће показати колико пута се износ прихода за један период мијења, тј. Годину дана. Мораћемо да нађемо разлику у профиту између последњег стварног периода и првог планираног, помножујући га са бројем планираних периода (3) и додајући резултату збир последњег стварног периода.

1. На листи оператора чаробњака функција изаберите име "ЛГРФИ" . Кликните на дугме "ОК" .
2. Почиње прозор аргумената. У њој уносимо податке тачно као и ЛИНЕСТ функција. Кликом на дугме "ОК" .
3. Резултат експоненцијалног тренда се израчунава и излази на означену ћелију.
4. Ставите "=" у празну ћелију. Отворите заграде и одаберите ћелију која садржи вредност прихода за последњи стварни период. Ставите знак "*" и изаберите ћелију која садржи експоненцијални тренд. Поставили смо знак минус и поново кликните на елемент у којем се налази износ прихода за последњи период. Затворимо носач и возимо знакове "* 3 +" без цитата. Поново кликните на исту ћелију која је изабрана за последњи пут. Да бисте извршили израчунавање, притисните тастер Ентер .

Предвиђени износ добити у 2019. години, који је израчунат методом експоненцијалне апроксимације, износиће 4.639,2 хиљада рубаља, што опет не разликује много од резултата добијених у израчунавању по претходним методама.

Лекција: Остале статистичке функције у Екцелу

Сазнали смо на који начин је могуће направити прогнозе у програму Екцел. Графички, то се може учинити коришћењем линије трендова, а аналитички - користећи бројне уграђене статистичке функције. Као резултат обрађивања идентичних података од ових оператора, могу се јавити различити резултати. Али то није изненађујуће, јер сви користе различите начине обрачуна. Ако је флуктуација мала, онда се све ове опције, које се односе на одређени случај, могу сматрати релативно поузданим.

• ← Транспонујте матрицу у Мицрософт Екцел
• Израчунавање НПВ у Мицрософт Екцелу →