Алатки за предвидување во Microsoft Excel

Прогнозирањето е многу важен елемент на речиси секое поле на активност, од економија до инженерство. Има голем број на софтвер кој е специјализиран во оваа област. За жал, не сите корисници знаат дека вообичаениот Excel табеларен процесор ги има во своите алатки арсенал за предвидување, кои не се многу инфериорни во однос на професионалните програми во нивната ефикасност. Ајде да откриеме што се овие алатки и како да се направи прогноза во пракса.

Процедура за предвидување

Целта на какво било предвидување е да се идентификува тековниот тренд и да се утврди очекуваниот резултат во однос на студираниот предмет во одредено време во иднина.

Метод 1: линија на трендови

Еден од најпопуларните видови на графичко предвидување во Excel е екстраполацијата со изградба на тренд линија.

Ајде да се обидеме да ја предвидиме висината на добивката на претпријатието за 3 години врз основа на податоците за овој индикатор за претходните 12 години.

1. Ние градиме графичка зависност заснована врз табеларни податоци што се состојат од аргументи и вредности на функција. За да го направите ова, изберете ја областа на табелата, а потоа, наоѓајќи се во картичката Вметнетекликнете на иконата од посакуваниот тип на табела, која се наоѓа во блокот Графикони. Потоа го избираме типот погоден за одредена ситуација. Најдобро е да изберете табела за распрснување. Можете да изберете друг поглед, но потоа, така што податоците се прикажани правилно, ќе мора да извршите уредување, особено, да ја отстраните линијата на аргументот и да изберете друга скала на хоризонталната оска.



2. Сега треба да градиме тренд линија. Десен-клик на која било од точките од дијаграмот. Во активираното мени за контекст, запрете го изборот на артикалот Додадете линија на тренд.



3. Се отвора прозорецот за форматирање на трендовите. Во него можете да изберете еден од шесте типа на приближување:
   • Линеарно;
   • Логаритамски;
   • Експоненцијален;
   • Моќност;
   • Полином;
   • Линеарно филтрирање.

   Да започнеме со избирање на линеарно приближување.

   Во блокот за поставки „Прогноза“ на полето "Напред кон" поставете го бројот "3,0", бидејќи треба да направиме прогноза за три години однапред. Покрај тоа, можете да го проверите полето до поставките. "Покажи равенка во дијаграмот" и "Поставете ја вредноста на довербата за приближување (R ^ 2) на дијаграмот". Последниот индикатор го прикажува квалитетот на тренд-линијата. Откако ќе ги направите поставките, кликнете на копчето Затвори.



4. Трендската линија е изградена и од неа можеме да го одредиме приближниот износ на профит за три години. Како што гледаме, до тоа време треба да биде над 4500 илјади рубли. Коефициент R2Како што споменавме погоре, го прикажува квалитетот на тренд-линијата. Во нашиот случај, вредноста R2 го сочинува 0,89. Колку е поголем коефициентот, толку е поголема сигурноста на линијата. Неговата максимална вредност може да биде еднаква 1. Општо е прифатено дека со коефициент погоре 0,85 трендот е сигурен.



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

   

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

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

Метод 2: операторот FOREKAST

Екстраполација за табеларни податоци може да се направи преку стандардната функција на Excel ПРЕДИКЦИЈА. Овој аргумент спаѓа во категоријата статистички алатки и ја има следната синтакса:

= PREDICT (X; познати_y_values; познати_x_values)

„Х“ е аргумент за кој треба да се утврди вредноста на функцијата. Во нашиот случај, аргументот ќе биде година за која треба да се направи предвидување.

Познати y вредности - база на познати вредности на функцијата. Во нашиот случај, нејзината улога ја игра износот на профитот за претходните периоди.

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

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

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

Ајде да погледнеме во нијансите на користење на операторот ПРЕДИКЦИЈА на конкретен пример. Земете ја целата маса. Forecastе треба да ја знаеме прогнозата за профит за 2018 година.

1. Изберете празна ќелија на листот, каде што планирате да го прикажете резултатот од обработката. Кликнете на копчето "Вметни функција".



2. Се отвора Волшебник на карактеристики. Во категоријата „Статистика“ изберете го името „ПРЕДИКЦИЈА“и потоа кликнете на копчето „ОК“.



3. Прозорецот со аргументи започнува. На полето „Х“ означете ја вредноста на аргументот на кој сакате да ја пронајдете вредноста на функцијата. Во нашиот случај, ова е 2018 година. Затоа, ние пишуваме "2018". Но, подобро е да се означи овој индикатор во ќелија на листот и во полето „Х“ само дај линк до тоа. Ова ќе овозможи во иднина да ги автоматизираат пресметките и, доколку е потребно, лесно да ја променат годината.

   На полето Познати y вредности наведете ги координатите на колоната "Добивка на претпријатието". Ова може да се направи со ставање на покажувачот во полето, а потоа држење на левото копче на глувчето и истакнување на соодветната колона на листот.

   Слично на ова поле Познати x вредности внесете ја адресата на колоната „Година“ со податоци за изминатиот период.

   Откако ќе ги внесете сите информации, кликнете на копчето „ОК“.



4. Операторот пресметува врз основа на внесените податоци и го прикажува резултатот на екранот. За 2018 година се планира да се профитира во регионот од 4.564,7 илјади рубли. Врз основа на табелата што произлегува, можеме да изградиме график со помош на алатките за мапирање дискутирани погоре.



5. Ако ја промените годината во ќелијата што беше користена за да го внесете аргументот, резултатот соодветно ќе се промени и распоредот автоматски ќе се ажурира. На пример, според предвидувањата во 2019 година, износот на профитот ќе биде 4637,8 илјади рубли.

Но, не заборавајте дека, како и со изградбата на тренд-линијата, периодот пред предвидувачкиот период не треба да надминува 30% од целиот период за кој беше акумулирана базата на податоци.

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

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

За предвидување, можете да користите друга функција - ТРЕНД. Исто така спаѓа во категоријата статистички оператори. Неговата синтакса е многу како синтакса на алатки ПРЕДИКЦИЈА и изгледа вака:

= TREND (Познати вредности_y; познати вредности_x; new_values_x; [const])

Како што можете да видите, аргументите Познати y вредности и Познати x вредности целосно одговараат на слични елементи на операторот ПРЕДИКЦИЈА, и аргументот "Нови вредности x" аргумент на натпревари „Х“ претходна алатка. Покрај тоа, ТРЕНД има дополнителен аргумент "Постојан", но тоа е по избор и се користи само ако има постојани фактори.

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

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

1. Ние ја назначуваме ќелијата да го прикаже резултатот и да работи Волшебник на карактеристики на вообичаен начин. Во категоријата „Статистика“ најдете и означете го името „ТРЕНД“. Кликнете на копчето „ОК“.



2. Отворен е прозорецот за аргументи на операторот ТРЕНД. На полето Познати y вредности со методот опишан погоре ги внесуваме координатите на колоната "Добивка на претпријатието". На полето Познати x вредности внесете ја адресата на колоната „Година“. На полето "Нови вредности x" ја внесуваме врската до ќелијата каде се наоѓа бројот на годината за која треба да се наведе прогнозата. Во нашиот случај, ова е 2019 година. Полето "Постојан" оставете го празно. Кликнете на копчето „ОК“.



3. Операторот ги обработува податоците и го прикажува резултатот на екранот. Како што можете да видите, износот на проектираната добивка за 2019 година, пресметано со методот на линеарна зависност, ќе биде, како и во претходниот метод на пресметка, 4637,8 илјади рубли.

Метод 4: Оператор за раст

Друга функција што може да се користи за предвидување во Excel е операторот на раст. Припаѓа и на статистичката група на алатки, но, за разлика од претходните, при нејзиното пресметување, тој не го користи методот на линеарна зависност, туку експоненцијалниот. Синтаксата на оваа алатка е како што следува:

= GROWTH (Познати вредности_y; познати вредности_x; new_values_x; [const])

Како што можете да видите, аргументите на оваа функција точно ги повторуваат аргументите на операторот ТРЕНД, така што нема да се задржиме на нивниот опис втор пат, туку веднаш да продолжиме со практична примена на оваа алатка.

1. Ние ја избираме ќелијата за излез на резултатот и ја нарекуваме на вообичаен начин Волшебник на карактеристики. Во списокот на статистички оператори, побарајте ја предметот РОСТ, изберете го и кликнете на копчето „ОК“.



2. Активиран е прозорецот со аргументи од горенаведената функција. Внесете ги податоците во полињата на овој прозорец на ист начин како што ги внесовме во прозорецот за аргументи на операторот ТРЕНД. Откако ќе ги внесете информациите, кликнете на копчето „ОК“.



3. Резултатот од обработката на податоците се прикажува на мониторот во претходно посочената ќелија. Како што можете да видите, овој пат резултатот е 4682,1 илјади рубли. Разлики од резултатите од обработката на операторот ТРЕНД незначителни, но тие се достапни. Ова се должи на фактот дека овие алатки користат различни методи за пресметување: методот на линеарна зависност и методот на експоненцијална зависност.

Метод 5: ЛИНЕАР оператор

Оператор ЛИНИЈА во пресметката се користи линеарниот метод за приближување. Не треба да се меша со методот на линеарна зависност што ја користи алатката. ТРЕНД. Нејзината синтакса е како што следува:

= LINE (Познати вредности_y; познати вредности_х; нови_важни_х; [констант]; [статистика])

Последните два аргумента се по избор. Со првите две, запознаени сме со претходните методи. Но, веројатно забележавте дека не постои аргумент во оваа функција што укажува на нови вредности. Факт е дека оваа алатка ја одредува само промената на приходите по единица период, што во нашиот случај е еднаква на една година, но ние треба да го пресметаме вкупниот резултат одделно, додавајќи го резултатот од пресметката на операторот до последната реална вредност на профитот ЛИНИЈАпати повеќе од годините.

1. Ние ја избираме ќелијата во која ќе се изврши пресметката и ќе ја извршиме Функцијата Волшебник. Изберете го името ЛИНЕИН во категорија „Статистика“ и кликнете на копчето „ОК“.



2. На полето Познати y вредности, отворениот прозорец на аргументи, внесете ги координатите на колоната "Добивка на претпријатието". На полето Познати x вредности внесете ја адресата на колоната „Година“. Останатите полиња се оставени празни. Потоа кликнете на копчето „ОК“.



3. Програмата ја пресметува и прикажува линеарната вредност на трендот во избраната ќелија.



4. Сега мора да ја дознаеме големината на проектираната добивка за 2019 година. Поставете го знакот "=" до која било празна ќелија на листот. Ние кликнуваме на ќелијата што содржи реален износ на добивка за последната студирана година (2016 година). Ставивме знак "+". Следно, кликнете на ќелијата што го содржи претходно пресметаниот линеарен тренд. Ставивме знак "*". Бидејќи помеѓу последната година од студискиот период (2016 година) и годината за која сакате да направите прогноза (2019), лежи период од три години, го поставивме бројот во ќелијата "3". За да направите пресметка, кликнете на копчето Внесете.

Како што можете да видите, проектираната маргина на профит пресметана со линеарниот метод за приближување во 2019 година ќе изнесува 4,614,9 илјади рубли.

Метод 6: LGRFPPRIBLE оператор

Последната алатка што ќе ја разгледаме ќе биде LGRFPPRIBLE. Овој оператор извршува пресметки врз основа на методот на експоненцијално приближување. Неговата синтакса има следнава структура:

= LGRFPRIBLE (Познати вредности_y; познати вредности_x; new_values_x; [const]; [статистика])

Како што можете да видите, сите аргументи целосно ги повторуваат соодветните елементи од претходната функција. Алгоритмот за пресметка на предвидување ќе се промени малку. Функцијата го пресметува експоненцијалниот тренд, кој покажува колку пати износот на приходот ќе се промени за еден период, односно за една година. Willе треба да ја најдеме разликата во профитот помеѓу последниот реален период и првиот планиран, да го помножиме со бројот на планирани периоди (3) и додадете го резултатот збирот од последниот вистински период.

1. Во списокот на оператори на Волшебникот за функции, изберете го името LGRFPPRIBL. Кликнете на копчето „ОК“.



2. Прозорецот со аргументи започнува. Во него, ги внесуваме податоците точно како што направивме, користејќи ја функцијата ЛИНИЈА. Кликнете на копчето „ОК“.



3. Резултатот од експоненцијалниот тренд се пресметува и прикажува во одредена ќелија.



4. Ставивме знак "=" во празна ќелија. Отворете ги држачите и одберете ја ќелијата што ја содржи вредноста на приходот за последниот вистински период. Ставивме знак "*" и изберете ја ќелијата што содржи експоненцијален тренд. Ставиме знак минус и повторно кликнуваме на елементот во кој се наоѓа вредноста на приходите за последниот период. Затворете ја заградата и возете во знаците "*3+" без понуди Повторно, кликнете на истата ќелија која беше избрана за последен пат. За извршување на пресметката, кликнете на копчето Внесете.

Проектираната сума на профит во 2019 година, која беше пресметана со методот на експоненцијално приближување, ќе изнесува 4,639,2 илјади рубли, што повторно не се разликува многу од резултатите добиени во претходната пресметка.

Лекција: Други статистички функции во Excel

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