Инструменти за прогнозиране в Microsoft Excel

Прогнозирането е много важен елемент в почти всяка област на дейност, от икономиката до инженеринга. Има голямо количество софтуер, който се специализира точно в тази посока. За съжаление, не всички потребители знаят, че обикновената електронна таблица в Excel има в арсенала си инструменти за прогнозиране, които с тяхната ефективност не са много по-ниски от професионалните програми. Нека разберем какви са тези инструменти и как да се направи прогноза на практика.

Прогнозна процедура

Целта на всяка прогноза е да се идентифицира текущата тенденция и да се определи очакваният резултат по отношение на изследвания обект в определен момент от време в бъдеще.

Метод 1: Тенденция

Един от най-популярните видове графично прогнозиране в Excel е екстраполацията, извършена чрез изграждането на линия на тенденциите.

Нека се опитаме да предвидим печалбата на предприятието за 3 години въз основа на данни за този показател за предходните 12 години.

1. Начертаваме зависимостта въз основа на таблични данни, състоящи се от аргументи и стойности на функцията. За да направите това, изберете областта на таблицата и след това, докато в раздела "Вмъкване" кликнете върху иконата на желания вид диаграма, който е в блока "Диаграми" . След това изберете подходящия тип за конкретна ситуация. Най-добре е да изберете растерна графика. Можете да изберете различен изглед, но за да се покажат данните правилно, ще трябва да извършите редактиране, по-специално да премахнете линията на аргументите и да изберете друга скала за хоризонталната ос.
2. Сега трябва да изградим тренд линия. Кликнете с десния бутон върху някоя от точките на диаграмата. В активираното контекстно меню изберете "Add trend line" .
3. Прозорецът за форматиране на линията на тенденцията се отваря. Той може да избере един от шестте типа сближаване:
   • Линейни ;
   • Логаритмична ;
   • Експоненциална ;
   • Степен на мощност ;
   • Полином ;
   • Линейно филтриране .

   Първо да изберем линейно сближаване.

   В полето "Прогнози" в полето "Препращане към" задайте номер "3.0" , тъй като трябва да изготвим прогноза за три години напред. Освен това можете да проверите настройките "Показване на уравнението в диаграмата" и "Поставете стойността на точността на сближаване (R ^ 2) на диаграмата" . Последният индикатор отразява качеството на тенденцията. След като направите настройките, кликнете върху бутона "Затваряне" .

4. Тенденцията е изградена и върху нея можем да определим приблизителната сума на печалбата за три години. Както виждаме, по това време тя трябва да надхвърля 4500 000 рубли. Коефициентът R2 , както вече бе споменато по-горе, отразява качеството на линията на тенденциите. В нашия случай R2 е 0.89 . Колкото по-висок е коефициентът, толкова по-голяма е надеждността на линията. Максималната му стойност може да бъде равна на 1 . Общоприето е, че с коефициент над 0.85 тенденцията е надеждна.
5. Ако не сте доволни от нивото на надеждност, можете да се върнете към прозореца на формата на тренда и да изберете друг вид приближение. Можете да опитате всички налични опции, за да намерите най-точните.

   Следва да се отбележи, че ефективна прогноза, използваща екстраполация през тренд линията, може да бъде, ако прогнозният период не надвишава 30% от анализираната база от периоди. Това означава, че при анализиране на период от 12 години не можем да направим ефективна прогноза за повече от 3-4 години. Но дори и в този случай, тя ще бъде сравнително надеждна, ако през това време няма да има непреодолима сила или напротив, изключително благоприятни обстоятелства, които не са били в предишни периоди.

Урокът: Как да се изгради тренд линия в Excel

Метод 2: Предоставяне на оператор

Екстраполатацията за табличните данни може да се извърши чрез стандартната функция Exxel PRESCASE . Този аргумент принадлежи към категорията на статистическите инструменти и има следния синтаксис:

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

"X" е аргумент, чиято функционална стойност трябва да бъде определена. В нашия случай годината, в която трябва да се извърши прогнозирането, ще бъде аргумент.

"Известните стойности на y" са в основата на известни стойности на функцията. В нашия случай ролята на печалбата за предишни периоди е в ролята й.

"Известните стойности на x" са аргументи, на които съответстват познатите стойности на функцията. В тяхната роля ние сме преброени от години, за които се събира информация за печалбите от предишни години.

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

При изчисляване на този метод се използва методът на линейна регресия.

Да разгледаме нюансите на използването на оператора PRESCASE за конкретен пример. Вземете същата маса. Ще трябва да разберем прогнозата за печалбата за 2018 г.

1. Изберете празна клетка в листа, където искате да отпечатате резултата от обработката. Кликнете върху бутона "Вмъкване на функция" .
2. Ще се отвори съветникът за функциите . В категорията "Статистика" изберете името "PRESCASE" и след това кликнете върху бутона "OK" .
3. Прозорецът с аргументи започва. В полето "X" посочваме стойността на аргумента, на който искаме да намерим стойността на функцията. В нашия случай това е 2018 г. Затова въвеждаме вписването "2018" . Но е по-добре да посочите този индикатор в клетката на листа, а в полето "X" просто дайте връзка към него. Това ще позволи в бъдеще да се автоматизират изчисленията и, ако е необходимо, лесно да се промени годината.

   В полето "Известни стойности на y" посочваме координатите на колоната "Enterprise profit" . Това може да стане чрез задаване на курсора в полето и след това задържане на левия бутон на мишката и избор на съответната колона на листа.

   По същия начин в полето "Известни стойности x" въвеждаме адреса на колоната "Година" с данните за миналия период.

   След като въведете цялата информация, кликнете върху бутона "OK" .

4. Операторът изчислява въз основа на въведените данни и показва резултата на екрана. За 2018 г. се планира печалба от около 4564.7 хил. Рубли. Въз основа на резултантната таблица можем да графикираме графиката, като използваме описаните по-горе инструменти за диаграми.
5. Ако промените годината в клетката, използвана за въвеждане на аргумента, резултатът ще се промени съответно и графиката ще се актуализира автоматично. Например, според прогнозите през 2019 г., сумата на печалбата ще бъде 4637.8 хил. Рубли.

Но не забравяйте, че както при изграждането на трендовата линия, интервалът от време до прогнозния период не трябва да надвишава 30% от целия период, за който е натрупана базата данни.

Урокът: Екстраполация в Excel

Метод 3: Тенденции на оператора

За прогнозиране можете да използвате още една функция - ТЕНДЕНЦИИ . Той принадлежи и към категорията на статистическите оператори. Синтаксисът й много наподобява синтаксиса на инструмента PRESCASE и изглежда така:

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

Както можете да видите, аргументите "Известните стойности на y" и "Известните стойности на x" напълно съответстват на подобни елементи на оператора PRESCASE , а аргументът "Нови стойности x" съответства на аргумента "X" на предишния инструмент. Освен това TRENDS има допълнителен аргумент "Constant" , но не е задължителен и се използва само ако има постоянни фактори.

Този оператор се използва най-ефективно в присъствието на линейна зависимост на функцията.

Нека видим как този инструмент ще работи с един и същ набор от данни. За да се сравни получените резултати, ще определим прогнозната точка през 2019 г.

1. Определяме клетката за изхода на резултата и изпълняваме функционалният помощник по обичайния начин. В категорията "Статистически" намираме и избираме името "ТЕНДЕНЦИИ" . Кликнете върху бутона "OK" .
2. Отваря аргумента за оператор на TENDENCY. В полето "Известни стойности на y" въвеждаме координатите на колоната "Enterprise Profit " по начина, описан по-горе. В полето "Известни стойности x" въведете адреса на колоната "Година" . В полето "Новите стойности x" въведете справка за клетката, където се намира номерът на годината, към който искате да посочите прогнозата. В нашия случай това е 2019 г. Полето "Постоянно" остава празно. Кликваме върху бутона "OK" .
3. Операторът обработва данните и показва резултата на екрана. Както можете да видите, сумата от прогнозната печалба за 2019 г., изчислена по метода на линейната зависимост, ще направи, както и при предишния метод на изчисление, 4637,8 хил. Рубли.

Метод 4: Операторът GROWTH

Друга функция, с която можете да правите прогнози в Excel, е операторът GROWTH. Той също така се позовава на статистическата група от инструменти, но, за разлика от предишните, методът се прилага не чрез метода на линейна зависимост, а чрез експоненциалния метод. Синтаксисът на този инструмент изглежда така:

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

Както можете да видите, аргументите на тази функция точно повтарят аргументите на оператора на TENDENCY , така че няма да ги обсъждаме за втори път, но ще се обърнем незабавно към прилагането на този инструмент на практика.

1. Изберете изходната изходна клетка и извикайте функционалният помощник по обичайния начин. В списъка на статистическите оператори потърсете елемента "GROWTH" , изберете го и кликнете върху бутона "OK" .
2. Прозорецът с аргументи на горната функция е активиран. Въвеждаме данните в полетата на този прозорец по същия начин, както ги въведехме в прозореца с аргументи на оператора на TENDENCY. След като въведете информацията, кликнете върху бутона "OK" .
3. Резултатът от обработката на данните се показва на монитора в клетката, посочена по-горе. Както можете да видите, този път резултатът е 4682.1 хил. Рубли. Различията от резултатите от обработката на данните от оператора TENDENCY са незначителни, но те са налични. Това се дължи на факта, че тези инструменти използват различни методи за изчисление: метод на линейна зависимост и метод на експоненциална зависимост.

Метод 5: LINEST оператор

Операторът LINEST използва метода на линейно сближаване. Не трябва да се бърка с метода на линейната зависимост, използван от инструмента ТЕНДЕНЦИИ . Синтаксисът му е следният:

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

Последните два аргумента са незадължителни. С първите две сме запознати с предишните методи. Но вероятно сте забелязали, че в тази функция няма аргумент, който да сочи към нови ценности. Фактът е, че този инструмент определя само промяната в размера на прихода за единица период, което в нашия случай е една година, но сумата се изчислява отделно, като към последната действителна стойност на печалбата се прибавя резултатът от изчислението на LINEST оператора, умножен по броя на годините.

1. Избираме клетката, в която ще се извърши изчислението, и изпълняваме помощника за функциите. Изберете името "LINEST" в категорията "Статистически" и кликнете върху бутона "OK" .
2. В полето "Известни стойности на y" , отворения прозорец на аргументите въведете координатите на колоната "Enterprise Profit . " В полето "Известни стойности x" въвеждаме адреса на колоната "Година" . Останалите полета остават празни. След това кликнете върху бутона "OK" .
3. Програмата изчислява и показва стойността на линейната тенденция в избраната клетка.
4. Сега трябва да разберем стойността на прогнозната печалба за 2019 г. Задайте знака "=" на всяка празна клетка на листа. Кликваме върху клетката, която съдържа реалната сума на печалбата за последната изучавана година (2016 г.). Поставете знака "+" . След това кликнете върху клетката, която съдържа предварително изчислената линейна тенденция. Поставяме знака "*" . Тъй като между последната година от периода на изследване (2016 г.) и годината, за която ще се направи прогнозата (2019), периодът е три години, след това в клетката се поставя числото "3" . За да направите изчисление, кликнете върху бутона Enter .

Както можем да видим, прогнозната печалба, изчислена по метода на линейно сближаване, през 2019 г. ще бъде 4614.9 хил. Рубли.

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

Последният инструмент, който ще разгледаме, ще бъде LGRF . Този оператор извършва изчисления въз основа на метода на експоненциално сближаване. Синтаксисът му има следната структура:

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

Както можете да видите, всички аргументи напълно повтарят съответните елементи от предишната функция. Алгоритъмът за изчисляване на прогнозата ще се промени леко. Функцията ще изчисли експоненциалната тенденция, която ще покаже колко пъти ще се промени размерът на приходите за един период, т.е. за една година. Ще трябва да намерим разликата в печалбата между последния и първия планиран период, като го умножим по броя на планираните периоди (3) и добавим към резултата сумата от последния действителен период.

1. В списъка с оператори на Wizards of functions , изберете името "LGRFI" . Кликнете върху бутона "OK" .
2. Прозорецът с аргументи започва. В него въвеждаме данните точно както направихме с функцията LINEST . Кликваме върху бутона "OK" .
3. Резултатът от експоненциалната тенденция се изчислява и се извежда към определената клетка.
4. Поставете "=" в празната клетка. Отворете скобите и изберете клетката, която съдържа стойността на приходите за последния действителен период. Поставете знака "*" и изберете клетката, съдържаща експоненциалната тенденция. Поставяме знак минус и отново кликваме върху елемента, в който се намира размерът на приходите за последния период. Затваряме скобата и караме символите "* 3 +" без кавички. Отново кликнете върху същата клетка, която бе избрана за последен път. За да извършите изчислението, натиснете бутона Enter .

Прогнозната сума на печалбата през 2019 г., изчислена по метода на експоненциално сближаване, ще възлезе на 4 639,2 хил. Рубли, което отново не се различава значително от резултатите, получени при изчисляването по предишни методи.

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

Разбрахме по какъв начин е възможно да се правят прогнози в програмата Excel. Графично, това може да се направи чрез използването на тренд линията и аналитично - като се използват редица вградени статистически функции. В резултат на обработката на идентични данни от тези оператори могат да възникнат различни резултати. Но това не е изненадващо, тъй като те всички използват различни методи за изчисление. Ако флуктуацията е малка, тогава всички тези опции, приложими за конкретен случай, могат да се считат за относително надеждни.

• ← Прехвърлете матрицата в Microsoft Excel
• Изчисляване на NPV в Microsoft Excel →