Створення прогнозу в програмі Excel 2016 для Windows

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

У цій статті йдеться про те , як обчислюється прогноз і які параметри можна змінити.

Створити та зберегти

Створення прогнозу

  1. Введіть на аркуші два ряди даних, що відповідають один одному:
    • ряд, що містить дані про дату або час для часової шкали;
    • ряд із відповідними значеннями.

      Ці значення буде спрогнозовано для майбутніх дат.

    ПРИМІТКА : Інтервали між точками даних на часовій шкалі мають бути узгоджені. Наприклад, можна використовувати місячні інтервали зі значеннями для 1-го числа кожного місяця, річні або числові інтервали. У ряді на часовій шкалі може не бути до 30 % точок даних або міститися кілька значень для однієї позначки часу. Прогноз однаково буде точний. Проте якщо підсумовувати дані перед побудовою прогнозу, результати прогнозу будуть точнішими.

  2. Виділіть обидва ряди даних.

    ПОРАДА : Якщо виділити клітинку в одному з рядів, програма Excel автоматично виділить решту даних.

  3. Послідовно виберіть Дані > Аркуш прогнозу.

    Рисунок на кнопці

  4. У вікні Створення аркуша прогнозу виберіть лінійчату або стовпчасту діаграму для візуального представлення прогнозу.

    Знімок екрана: діалогове вікно "Створення аркуша прогнозу" зі згорнутим розділом "Параметри"

  5. У полі Завершення прогнозу виберіть дату завершення та натисніть кнопку Створити.

    Якщо потрібно змінити будь-які додаткові настройки, клацніть елемент Параметри. Нижче наведено корисну інформацію про кожен параметр.

Початок прогнозу.    Виберіть дату початку прогнозування. Якщо вибрати дату, що передує останній даті в даних за минулий період, для прогнозу буде використано лише дані до дати початку (такий прогноз називається ретроспективним).

ПОРАДИ : Прогноз, що починається перед останньою датою в даних за минулий період, дає змогу оцінити точність прогнозування, порівнявши прогнозований ряд із фактичними даними. Проте якщо почати прогноз зарано, створений прогноз не обов’язково збігатиметься з тим, який можна отримати, використавши всі дані за минулий період. Якщо використати всі минулі дані, прогноз буде точніший.

Якщо дані сезонні, радимо починати прогноз із дати, яка передує останній даті в даних за минулий період.

Довірчий інтервал.    Установіть або зніміть прапорець Довірчий інтервал, щоб відобразити або приховати цей інтервал. Довірчий інтервал – це діапазон навколо кожного прогнозованого значення, у який відповідно до прогнозу має потрапити 95 % майбутніх точок (у випадку нормального розподілу). За допомогою довірчого інтервалу можна краще оцінити точність прогнозу. Менший інтервал свідчить про більшу достовірність прогнозу для конкретної точки. Довірчий рівень за замовчуванням становить 95 %, але це значення можна змінити за допомогою стрілок вгору або вниз.

Сезонний фактор.    Сезонний фактор – це число, яке позначає довжину періоду сезонних змін (кількість точок у цьому періоді). Це значення визначається автоматично. Наприклад, у річному циклі продажів кожна точка відповідає місяцю, тому сезонність дорівнює 12. Автоматично встановлене значення можна замінити. Для цього потрібно вибрати параметр Установити вручну, а потім вибрати число.

ПРИМІТКА : Настроюючи сезонність уручну, використовуйте дані за період, що охоплює не менше 2 циклів. Якщо циклів менше 2, програма Excel не може визначити сезонні компоненти. Якщо сезонність недостатньо значуща, щоб її міг виявити алгоритм, для прогнозу використовуватиметься лінійне наближення.

Діапазон часової шкали.    Тут можна змінити діапазон, який використовуватиметься для часової шкали. Цей діапазон має відповідати значенню в полі Діапазон значень.

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

Заповнення відсутніх точок.    Якщо які-небудь точки відсутні, у програмі Excel використовується інтерполяція, тобто замість значень відсутніх точок використовується зважене середнє значення сусідніх точок (якщо відсутні менше 30 % точок). Щоб замість значень відсутніх точок використовувалися нулі, виберіть у списку значення Нулі.

Агрегація повторів.    Якщо дані містять кілька значень з однаковою позначкою часу, програма Excel використає середні значення. Щоб використовувати інший метод обчислення, наприклад Медіана, виберіть його в списку.

Включити статистичні дані прогнозування   . Установіть цей прапорець, щоб додати до нового аркуша статистичні відомості. Таблицю статистики буде створено за допомогою функції FORECAST.ETS.STAT. Вона міститиме такі міри, як коефіцієнти згладжування (альфа, бета, гама) і показники похибок (MASE, SMAPE, MAE, RMSE).

Формули, які використовуються для прогнозування даних

Якщо для прогнозу використовується формула, вона повертає таблицю з даними за минулий період і прогнозованими даними, а також діаграму. Прогноз дає змогу передбачити майбутні значення на основі наявних даних (з урахуванням часового компонента) за допомогою версії AAA алгоритму експоненційного згладжування (ETS).

Таблиця може містити зазначені нижче стовпці, три з яких обчислювані:

  • стовпець моментів часу в минулому (ряд даних, що змінюється в часі);
  • стовпець значень у моменти часу в минулому (ряд даних із відповідними значеннями);
  • стовпець прогнозованих значень (обчислюються за допомогою функції FORECAST.ETS);
  • два стовпці, що представляють довірчий інтервал (обчислюються за допомогою функціїFORECAST.ETS.CONFINT). Ці стовпці відображаються лише тоді, коли у вікні в розділі Параметривстановлено прапорець Довірчий інтервал.

Прокоментуйте першими

Залишити коментар

Ваш e-mail не буде публікуватись.

*