Як створити робочі таблиці прогнозу в Excel 2019

Функція аркуша прогнозу в Excel 2019 дозволяє дуже легко перетворити аркуш, що містить історичні фінансові дані, у чудовий візуальний робочий аркуш прогнозу. Все, що вам потрібно зробити, це відкрити робочий аркуш Excel зі своїми історичними даними , розмістити курсор комірки в одній із його клітинок , а потім натиснути кнопку Прогнозний аркуш на вкладці «Дані» стрічки (Alt+AFC).

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

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

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

Окрім вибору типу діаграми, яка буде використовуватися на аркуші прогнозу, ви також можете керувати датою закінчення діаграми прогнозу. Ви можете зробити це, вибравши нову дату в текстовому полі «Завершення прогнозу», яке з’явиться під попереднім переглядом вбудованої лінійної або стовпчастої діаграми. Наприклад, на зображенні нижче таблиця історичних даних містить річні продажі за 2000–2015 роки, і Excel автоматично використовує ці дані для продовження дати завершення прогнозу з чотирьох років до 2019 року. Якщо ви хочете розширити прогноз за межами цього кінця дату, ви просто введіть новий рік у текстове поле Forecast End (або виберіть його за допомогою кнопок обертання).

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

Під текстовим полем «Завершення прогнозу» у діалоговому вікні «Створити робочий аркуш прогнозу» ви знайдете кнопку «Параметри», натиснувши на яку, діалогове вікно розгортається, щоб включити такі параметри:

• Початок прогнозу дозволяє вибрати дату початку в історичних даних пізніше, ніж дата, яку Excel автоматично вибирає (це перша дата в таблиці робочого аркуша).
• Інтервал довіри дозволяє вибрати новий ступінь довіри, який Excel використовує для встановлення лінії нижньої та верхньої межі довіри в прогнозі під час використання лінійної діаграми за замовчуванням.
• Сезонно змінюється з параметра «Визначати автоматично» до «Встановити вручну», де ви можете ввести або вибрати нове значення, що вказує кількість точок у діапазоні значень у вашій таблиці даних, які є частиною повторюваного сезонного шаблону. Коли Excel не може автоматично виявити сезонність у даних вашого робочого аркуша, з’являється попередження, яке пропонує вам вибрати кнопку «Встановити вручну» (і залишити значення за замовчуванням рівним нулю), щоб отримати кращі результати в прогнозі.
• Включити прогнозну статистику дозволяє Excel включати таблицю показників точності прогнозу та коефіцієнтів згладжування в результуючу робочу таблицю прогнозу.
• Діапазон часової шкали змінює діапазон комірок, що містить значення дат у ваших історичних даних (за замовчуванням Excel вибирає всі ці значення, які він визначає в поточній таблиці робочого аркуша).
• Діапазон значень змінює діапазон комірок, що містить фінансові значення у ваших історичних даних (за замовчуванням Excel вибирає всі ці значення, які він визначає в поточній таблиці робочого аркуша).
• Заповнення відсутніх точок Використання, щоб Excel автоматично заповнював будь-які відсутні точки даних, які він знаходить у таблиці робочого аркуша на діаграмі прогнозу за допомогою інтерполяції або нулів.
• Використання Aggregate Duplicates змінює статистичну функцію.

Після вибору всіх потрібних параметрів у діалоговому вікні «Створити робочий аркуш прогнозу», вам потрібно лише натиснути кнопку «Створити», щоб Excel створив новий робочий аркуш прогнозу. Цей новий аркуш містить відформатовану таблицю даних, яка поєднує ваші історичні дані з прогнозними значеннями, а також вбудовану лінійну або стовпчасту діаграму (залежно від вашого вибору), що відображає тенденцію як історичних, так і прогнозних значень.

На зображенні вище показано абсолютно новий робочий аркуш прогнозу, створений за допомогою функції аркуша прогнозів Excel з використанням вихідних історичних даних про продажі, показаних вище після того, як кнопку параметра «Встановити вручну» було вибрано з нульовим значенням і встановлено прапорець «Включити прогнозну статистику», перш ніж натиснути кнопку «Створити» у вікні. Діалогове вікно створення робочого аркуша прогнозу. Як бачите, Excel розмістив цей новий робочий аркуш прогнозу (з назвою Лист1) перед аркушем, що містить історичні дані (з назвою Річний продаж).

Зауважте, що для відображення всіх значень прогнозу в новій відформатованій таблиці даних на зображенні вище, вбудовану лінійну діаграму було переміщено та змінено так, щоб вона добре помістилася в нижньому правому куті, перш ніж зробити знімок екрана. Якщо потрібно, ви можете перемістити цю діаграму на власний аркуш діаграми, перш ніж зберегти нову таблицю прогнозу та діаграму як частину книги «Історія та прогноз продажів».