Як створити робочий лист прогнозу в Excel 2016

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

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

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

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

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

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

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

• Інтервал довіри дозволяє вибрати новий ступінь довіри, який Excel використовує для встановлення лінії нижньої та верхньої межі довіри в прогнозі під час використання лінійної діаграми за замовчуванням.

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

• Включити прогнозну статистику дозволяє Excel включити таблицю показників точності прогнозу та коефіцієнтів згладжування в результуючу робочу таблицю прогнозу.

• Діапазон часової шкали t змінює діапазон комірок, що містить значення дат у ваших історичних даних (за замовчуванням Excel вибирає всі ці значення, які він визначає в поточній таблиці робочого аркуша).

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

• Заповнення відсутніх точок Використання, щоб Excel автоматично заповнював будь-які відсутні точки даних, які він знаходить у таблиці робочого аркуша на діаграмі прогнозу за допомогою інтерполяції або нулів.

• Aggregate Duplicates Використання t змінює статистичну функцію.

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

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

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

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