Прогнозування в Excel

Прогноз може основуватись на інтуїції, ясно баченні, ворожбі тощо. В Excel прогноз основується тільки на статистичних методах, які використовують показники попередніх періодів. Дані методи базуються на тому, що спочатку вивчається стратегія процесу за минулий період, а далі на її основі будується прогноз. Цю стратегію ще називають базовою лінією даних.

Тобто базова лінія є типовими результатами спостережень, які проводились протягом тривалого часу. Точність прогнозу залежить від вимог до базової лінії:

· починати побудову базової лінії потрібно з результатів самих ранніх спостережень і закінчувати останніми;

· всі часові періоди спостережень повинні бути однакові. Не слід змішувати дані за різні періоди. Наприклад, дані за день з середніми показниками за декілька днів.

· спостереження повинні фіксуватися в один і той же момент кожного часового періоду. Наприклад, при складанні базової лінії на основі добових (щоденних) спостережень фіксувати дані в один і той же час;

· пропущення даних не допускається. Тому, якщо відсутні дані спостережень за незначний період, доцільно буде їх доповнити наближеними даними.

Методи прогнозування враховують характер протікання процесів і значення випадкової величини часового ряду. Якщо варіація середніх значень незначна, для прогнозу на короткі інтервали часу використовують метод ковзного середнього. Якщо перші значення мають меншу значимість для прогнозу, а наступні значення мають більшу значимість для прогнозу, то використовують метод експоненціального згладжування. Дані методи розглянуті у першому питанні.

Для прогнозування також використовуються статистичні функції «Тенденция» та «Рост», які відносяться до функції регресійного аналізу.

Функція «Тенденция» апроксимує прямою лінією, а функція «Рост» експонентою, за методом найменших квадратів, масив відомих значень на сітці :

Параметри функцій однакові (для обох функцій): «Тенденция» (значення ; старі значення ; нові значення для прогнозу; логічна константа: якщо «1» - обчислюється, якщо «0» - = 0).

Алгоритм використання функцій:

1) ввести початкові дані (у стовпчик):

· значення сітки

· значення (результати вимірювань, спостережень тощо)

2) при необхідності отримати згладжені значення , виконати як формулу масиву:

· виділити діапазон розміром ;

· «майстер функцій» - вибрати «Тенденция» або «Рост»;

· вказати перших два параметра;

· [Ctrl + Shift + Enter];

3) для отримання прогнозу на період (тобто ) можна використовувати такі способи:

Спосіб 1: за допомогою формули масиву:

· виділити новий діапазон, розмір якого визначається кількістю нових значень (тобто );

· функція «Тенденция» або «Рост»;

· в полі третього параметра додати діапазон нових значень ;

· [Ctrl + Shift + Enter].

· Спосіб 2: автозаповнення:

· виділити комірку;

· функція «Тенденция» або «Рост»;

· додати третій параметр посилання до комірки зі значенням ;

· «ОК»;

· маркером автозаповнення скопіювати формулу на необхідну кількість комірок.

Особливість; якщо для прогнозу використовуються тільки початкові (експериментальні, вимірювані) дані, то діапазони та зафіксувати абсолютними посиланнями (наприклад, $A$1:$A$20 та $B$1:$B$20).

Функція «ПРЕДСКАЗ» - обчислює значення для заданого значення (або для масиву значень ), тобто функція передбачає (завбачає або провіщує) значення змінної . В основі – алгоритм МНК, за яким будується регресійне рівняння для визначеня значень .

Синтаксис функції:

ПРЕДСКАЗ () значення ; значення ).

Якщо обчислюється масив значень , то функцію використовують як формулу масиву, тобто спочатку виділяють діапазон комірок відповідно кількості значень масиву ; перший параметр функції вводять як діапазон і завершують операцію комбінацією клавіш [Ctrl + Shift + Enter].