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

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

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

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

Спосіб 1: лінія тренду

Одним із найпопулярніших видів графічного прогнозування в Екселі є екстраполяція виконана побудовою лінії тренду.

Спробуємо передбачити суму прибутку підприємства через 3 роки на основі даних за цим показником за попередні 12 років.

1. Будуємо графік залежності на основі табличних даних, що складаються з аргументів та значень функції. Для цього виділяємо табличну область, а потім, перебуваючи у вкладці "Вставка", клацаємо по значку потрібного виду діаграми, що знаходиться в блоці «Діаграми». Потім вибираємо відповідний для конкретної ситуації тип. Найкраще вибрати точкову діаграму. Можна вибрати інший вигляд, але тоді, щоб дані відображалися коректно, доведеться виконати редагування, зокрема прибрати лінію аргументу і вибрати іншу шкалу горизонтальної осі.
2. Тепер нам потрібно збудувати лінію тренду. Робимо клацання правою кнопкою миші за будь-якою з точок діаграми.У контекстному меню, що активувалося, зупиняємо вибір на пункті «Додати лінію тренду».
3. Відкриється вікно форматування лінії тренду. У ньому можна вибрати один із шести видів апроксимації:
   • Лінійна;
   • Логарифмічна;
   • Експонентна;
   • Ступінь;
   • Поліноміальна;
   • Лінійна фільтрація.

Давайте спершу виберемо лінійну апроксимацію.

Спосіб 2: оператор ПЕРЕДСКАЗ

Екстраполяцію для табличних даних можна провести за стандартною функцією Ексель ПЕРЕДСКАЗ. Цей аргумент відноситься до категорії статистичних інструментів і має наступний синтаксис:

«X» - Це аргумент, значення функції для якого потрібно визначити. У нашому випадку як аргумент виступатиме рік, на який слід зробити прогнозування.

"Відомі значення y" - База відомих значень функції. У нашому випадку її ролі виступає величина прибутку за попередні періоди.

"Відомі значення x" - Це аргументи, яким відповідають відомі значення функції. У їхній ролі у нас виступає нумерація років, за які було зібрано інформацію про прибуток попередніх років.

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

При обчисленні даним способом використовують метод лінійної регресії.

Давайте розберемо нюанси застосування оператора ПЕРЕДСКАЗ на конкретному прикладі. Візьмемо ту саму таблицю. Нам потрібно буде дізнатися про прогноз прибутку на 2018 рік.

1. Виділяємо незаповнений осередок на аркуші, куди планується виводити результат обробки. Тиснемо на кнопку "Вставити функцію".
2. Відкривається Майстер функцій. у категорії «Статистичні» виділяємо найменування «ПЕРЕДСКАЗ», а потім клацаємо по кнопці "OK".
3. Запускається вікно аргументів. У полі «X» вказуємо величину аргументу, якого необхідно знайти значення функції. У нашому випадку це 2018 рік. Тому вносимо запис «2018». Але краще вказати цей показник у осередку на аркуші, а в полі «X» просто дати посилання на нього. Це дозволить у майбутньому автоматизувати обчислення та за потреби легко змінювати рік. У полі "Відомі значення y" вказуємо координати стовпця «Прибуток підприємства». Це можна зробити, встановивши курсор у полі, а потім, затиснувши ліву кнопку миші та виділивши відповідний стовпець на аркуші. Аналогічним чином у полі "Відомі значення x" вносимо адресу стовпця «Рік» із даними за минулий період. Після того, як всю інформацію внесено, тиснемо на кнопку "OK".
4. Оператор здійснює розрахунок на підставі введених даних і виводить результат на екран. На 2018 рік планується прибуток у районі 4564,7 тис. карбованців. На основі отриманої таблиці ми можемо побудувати графік за допомогою інструментів створення діаграми, про які йшлося вище.
5. Якщо поміняти рік у осередку, який використовувався для введення аргументу, то відповідно зміниться результат, а також автоматично оновиться графік. Наприклад, за прогнозами у 2019 році сума прибутку становитиме 4637,8 тис. карбованців.

Але не слід забувати, що, як і при побудові лінії тренду, відрізок часу до прогнозованого періоду не повинен перевищувати 30% від усього терміну, за який накопичувалася база даних.

Спосіб 3: оператор ТЕНДЕНЦІЯ

Для прогнозування можна використати ще одну функцію – ТЕНДЕНЦІЯ. Вона також належить до категорії статистичних операторів. Її синтаксис багато в чому нагадує синтаксис інструменту ПЕРЕДСКАЗ і виглядає так:

= ТЕНДЕНЦІЯ (Відомі значення_y; відомі значення_x; нові_значення_x; [конст])

Як бачимо, аргументи "Відомі значення y" і "Відомі значення x" повністю відповідають аналогічним елементам оператора ПЕРЕДСКАЗ, а аргумент "Нові значення x" відповідає аргументу «X» попередній інструмент. Крім того, у ТЕНДЕНЦІЯ є додатковий аргумент «Константа»але він не є обов'язковим і використовується тільки за наявності постійних факторів.

Цей оператор найефективніше використовується за наявності лінійної залежності функції.

Подивимося, як цей інструмент працюватиме все з тим самим масивом даних. Щоб порівняти отримані результати, точкою прогнозування визначимо 2019 рік.

1. Виробляємо позначку осередку для виведення результату та запускаємо Майстер функцій звичайним способом. у категорії «Статистичні» знаходимо та виділяємо найменування «ТЕНДЕНЦІЯ». Тиснемо на кнопку "OK".
2. Відкриється вікно аргументів оператора ТЕНДЕНЦІЯ. У полі "Відомі значення y" вже описаним вище способом заносимо координати колонки «Прибуток підприємства». У полі "Відомі значення x" вводимо адресу стовпця «Рік». У полі "Нові значення x" заносимо посилання на комірку, де знаходиться номер року, на який потрібно вказати прогноз. У нашому випадку це 2019 рік. Поле «Константа» залишаємо порожнім. Клацаємо по кнопці "OK".
3. Оператор обробляє дані та виводить результат на екран. Як бачимо, сума прогнозованого прибутку на 2019 рік, розрахована методом лінійної залежності, становитиме, як і за попереднього методу розрахунку, 4637,8 тис. грн. карбованців.

Спосіб 4: оператор РОСТ

Ще однією функцією, за допомогою якої можна проводити прогнозування в Екселі, є оператор РОСТ.Він також належить до статистичної групи інструментів, але, на відміну попередніх, при розрахунку застосовує не метод лінійної залежності, а експоненціальної. Синтаксис цього інструмента має такий вигляд:

= РОСТ (Відомі значення_y; відомі значення_x; нові_значення_x; [конст])

Як бачимо, аргументи цієї функції точно повторюють аргументи оператора ТЕНДЕНЦІЯТак що вдруге на їх описі зупинятися не будемо, а відразу перейдемо до застосування цього інструменту на практиці.

1. Виділяємо комірку виведення результату і вже звичним шляхом викликаємо Майстер функцій. У списку статистичних операторів шукаємо пункт «ЗРОСТАННЯ», виділяємо його та клацаємо по кнопці "OK".
2. Відбувається активація вікна аргументів зазначеної функції. Вводимо в поля цього вікна дані повністю аналогічно до того, як ми їх вводили у вікні аргументів оператора ТЕНДЕНЦІЯ. Після того, як інформацію внесено, тиснемо на кнопку "OK".
3. Результат обробки даних виводиться на монітор у зазначеному раніше осередку. Як бачимо, цього разу результат становить 4682,1 тис. рублів. Відмінності від результатів обробки даних оператором ТЕНДЕНЦІЯ незначні, але вони є. Це з тим, що ці інструменти застосовують різні методи розрахунку: метод лінійної залежності і метод експоненційної залежності.

Спосіб 5: оператор Лінейн

Оператор Лінейн при обчисленні використовує метод лінійного наближення. Його не варто плутати з методом лінійної залежності, використовуваним інструментом ТЕНДЕНЦІЯ. Його синтаксис має такий вигляд:

=ЛІНЕЙН(Відомі значення_y;відомі значення_x; нові_значення_x;[конст];[статистика])

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

1. Виробляємо виділення осередку, в якому буде проводитись обчислення та запускаємо Майстер функцій. Виділяємо найменування «ЛІНЕЙН» у категорії «Статистичні» і тиснемо на кнопку "OK".
2. У полі "Відомі значення y", вікна аргументів, вводимо координати стовпця «Прибуток підприємства». У полі "Відомі значення x" вносимо адресу колонки «Рік». Інші поля залишаємо порожніми. Потім тиснемо на кнопку "OK".
3. Програма розраховує та виводить у вибрану комірку значення лінійного тренду.
4. Тепер ми маємо з'ясувати величину прогнозованого прибутку на 2019 рік. Встановлюємо знак «=» у будь-яку порожню комірку на аркуші. Клікаємо по осередку, в якому міститься фактична величина прибутку за останній рік, що вивчається (2016 р.). Ставимо знак «+». Далі клацаємо по осередку, в якому міститься розрахований раніше лінійний тренд. Ставимо знак «*». Оскільки між останнім роком періоду, що вивчається (2016 р.) і роком на який потрібно зробити прогноз (2019 р.), лежить термін у три роки, то встановлюємо в осередку число «3». Щоб зробити розрахунок, клацаємо по кнопці Enter.

Як бачимо, прогнозована величина прибутку, розрахована методом лінійного наближення, у 2019 році становитиме 4614,9 тис. карбованців.

Спосіб 6: оператор ЛДРФПРИБЛ

Останній інструмент, який ми розглянемо, буде ЛДРФПРИБЛ. Цей оператор здійснює розрахунки на основі методу експоненційного наближення. Його синтаксис має таку структуру:

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

Як бачимо, всі аргументи повторюють відповідні елементи попередньої функції. Алгоритм розрахунку прогнозу трохи зміниться. Функція розрахує експоненційний тренд, який покаже, скільки разів зміниться сума виручки за період, тобто, за рік. Нам потрібно буде знайти різницю у прибутку між останнім фактичним періодом та першим плановим, помножити її на число планових періодів (3) та додати до результату суму останнього фактичного періоду.

1. У списку операторів Майстра функцій виділяємо найменування «ЛДРФПРИБЛ». Робимо клацання по кнопці "OK".
2. Запускається вікно аргументів. У ньому вносимо дані точно так, як це робили, застосовуючи функцію Лінейн. Клацаємо по кнопці "OK".
3. Результат експоненційного тренду підрахований і виведений у зазначений осередок.
4. Ставимо знак «=» у порожній осередок. Відкриваємо дужки та виділяємо комірку, яка містить значення виручки за останній фактичний період. Ставимо знак «*» і виділяємо комірку, що містить експоненційний тренд. Ставимо знак мінус і знову натискаємо на елемент, в якому знаходиться величина виручки за останній період. Закриваємо дужку та вбиваємо символи «*3+» без лапок. Знову клацаємо по тому ж осередку, який виділяли востаннє. Для проведення розрахунку тиснемо на кнопку Enter.

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

Ми з'ясували, як можна зробити прогнозування в програмі Ексель. Графічним шляхом це можна зробити через застосування лінії тренду, а аналітичним – використовуючи низку вбудованих статистичних функцій. В результаті обробки ідентичних даних цими операторами може вийти різний результат. Але це не дивно, оскільки всі використовують різні методи розрахунку. Якщо коливання невелике, всі ці варіанти, застосовні до конкретного випадку, вважатимуться щодо достовірними.

Як прогнозувати в Excel на основі історичних даних (4 відповідні методи)

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

Завантажити Практичний посібник

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

Прогноз на основі історичних даних.

Що таке прогнозування?

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

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

Однак ви не можете прогнозувати зі 100% точністю, незалежно від того, скільки у вас даних про минуле та сьогодення та наскільки ідеально ви визначили сезонність. Тому, перш ніж прийняти остаточне рішення, необхідно перевіряти ще раз результати і врахувати інші фактори.

4 методи прогнозування в Excel на основі історичних даних

У цій статті ми взяли дані про ціни на сиру нафту (Petroleum) з Веб-сайт Світового банку , за останні 10 років (з квітня 2012 року до березня 2022 року). На наступному малюнку список представлений частково.

1. використовуйте кнопку "Аркуш прогнозу" в Excel 2016, 2019, 2021 і 365

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

📌 Крок 1: Упорядкувати дані за допомогою тимчасових рядів та відповідних значень

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

📌 Крок 2: Створення робочого листа прогнозу

• Тепер перейдіть до Вкладка даних Потім натисніть на Прогнозний лист кнопка з Група прогнозування .

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

• Тепер виберіть тип графіка з вікна.
• Ви також можете вибрати дата закінчення прогнозу.

• Нарешті натисніть кнопку Створити кнопки. Ви закінчили!

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

Налаштування графіка прогнозу:

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

1. Тип діаграми

Тут є дві опції: Створити стовпчасту діаграму та Створити лінійну діаграму. Використовуйте будь-яку з них, яка здається вам більш зручною.

2. кінець прогнозу

Встановіть час, коли ви хочете завершити прогноз.

3. Прогноз Старт

Встановіть дату початку прогнозу за допомогою цього.

4. довірчий інтервал

Його значення за промовчанням дорівнює 95%. Чим воно менше, тим більша впевненість у прогнозованих значеннях. Ви можете відзначити або не відзначити цей прапорець, залежно від необхідності відображення рівня точності вашого прогнозу.

5. сезонність

Excel намагається виявити сезонність у ваших історичних даних, якщо ви оберете ' Визначити автоматично '. Ви також можете встановити його вручну, задавши відповідне значення.

6. Тимчасовий діапазон

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

7. Діапазон значень

Ви можете редагувати цей діапазон аналогічним чином.

8. заповнення відсутніх точок за допомогою

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

9. агрегування дублікатів за допомогою

Виберіть відповідний метод розрахунку (Average, Median, Min, Max, Sum, CountA), якщо у вас є кілька значень в одній мітці.

10. Включити статистику прогнозів

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

2. Використання функцій Excel для прогнозування з урахуванням попередніх даних

Ви також можете використовувати функції Excel, такі як ПРОГНОЗ, ТЕНДЕНЦІЯ, і РОСТ, для прогнозування з урахуванням попередніх рекордів. Давайте подивимося на них по черзі.

2.1 Використання функції FORECAST

MS Excel 2016 замінює Функція прогнозування з Функція FORECAST.LINEAR Тому ми будемо використовувати більш оновлені (для майбутніх проблем сумісності з Excel).

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

=FORECAST.LINEAR(x, knows_ys, known_xs)

Ось, x позначає кінцеву дату, відомі_хс позначає тимчасову шкалу, а відомі_люди позначає відомі значення.

📌 Кроки:

• По-перше, вставте наступну формулу в осередок C17 .

=FORECAST.LINEAR(B17,C5:C16,B5:B16)

Докладніше: Функція FORECAST в Excel (з іншими функціями прогнозування)

2.2 Застосування функції TREND

MS Excel також допомагає в Функція TREND для прогнозування з урахуванням історичних даних. Ця функція застосовує метод найменших квадратів для передбачення майбутніх значень.

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

=TREND(known_ys, [known_xs], [new_xs], [const])

У разі наступного набору даних введіть наведену нижче формулу у формулу клітина C125 для отримання прогнозних значень для Квітень, травень та червень 2022 року . Потім натиснути ENTER.

=TREND(C5:C124,B5:B124,B125:B127)

2.3 Використання функції Зрілість

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

Таким чином, щоб отримати прогнозні значення для Квітень, травень та червень 2022 року , вставте наступну формулу в клітина C125 . Потім натиснути ENTER .

= GROWTH (C5: C124, B5: B124, B125: B127)

Читати далі: Як спрогнозувати темпи зростання в Excel (2 методи)

Подібні читання

• Як прогнозувати продаж в Excel (5 простих способів)
• Прогноз темпів зростання продажів в Excel (6 методів)
• Як прогнозувати виторг в Excel (6 простих методів)

3. використання інструментів ковзного середнього та експоненційного згладжування

У статистиці використовуються два типи ковзних середніх: прості та експоненційні ковзні середні. Ми також можемо використовувати їх для передбачення майбутніх значень.

3.1 Використання простої ковзної середньої

Щоб застосувати техніку ковзного середнього, давайте додамо новий стовпець з ім'ям ' Ковзна середня '.

Тепер виконайте наведені нижче дії.

📌 Кроки:

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

• Сайт Ковзна середня з'явиться вікно.
• Тепер виберіть Вхідний діапазон як C5: C20 , поклав Інтервал як 3, Вихідний діапазон як D5:D20 і відзначити Виведення діаграми прапорець.
• Після цього натисніть Добре.

Ви можете побачити прогнозне значення на квітень 2022 року у осередок D20 .

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

3.2 Застосування експонентного згладжування

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

📌 Кроки:

• Спочатку зайдіть у Вкладка даних >> натисніть на кнопку Кнопка аналізу даних >> вибрати Експонентне згладжування зі списку.
• Потім натисніть OK .

З'явиться вікно Експонентне згладжування.

• Тепер встановити Вхідний діапазон як C5: C20 (або відповідно до ваших даних), Коефіцієнт демпфування як 0,3, і Вихідний діапазон як D5:D20 ; відзначити Виведення діаграми прапорець.
• Потім натисніть кнопку OK кнопки.

Після натискання кнопки OK ви отримаєте результат у форматі c елл D20 .

Подивіться на наступний графік, щоб зрозуміти прогноз наочно.

4. Застосування інструменту Fill Handle для прогнозування на основі історичних даних

Якщо ваші дані слідують лінійна тенденція (збільшується або зменшується), ви можете застосувати функцію Наповнювальна рукоятка інструмент для отримання швидкий прогноз. Ось наші дані та відповідний графік.

Це свідчить, що дані мають лінійну тенденцію.

Припустимо, що ми хочемо спрогнозувати для Квітень, травень та червень 2022 року Виконайте такі швидкі дії.

📌 Кроки:

• Спочатку виберіть значення C5:C16 та наведіть курсор миші у правий нижній кут осередок C16 З'явиться інструмент "Ручка заливки".
• Тепер перетягніть його до c елл C19 .

Наступний графік наочно показує результати. Помітно, що Excel ігнорує нерегулярні значення (наприклад, різкий стрибок Mar-22) і розглядає значення, які є регулярнішими.

Наскільки точно можна прогнозувати Excel?

У голові може виникнути питання: " Наскільки точними є методи прогнозування в Excel? "Наприклад, коли ми працювали над цією статтею, почалася війна між Україною та Росією, і ціни на сиру нафту зненацька зросли набагато більше, ніж очікувалося.

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

Тут ми маємо дані за період з квітня 2012 р. до березня 2022 р. Якщо ми зробимо прогноз останні кілька місяців і порівняємо результати з відомими значеннями, то дізнаємося, наскільки твердо ми можемо покладатися на нього.

Читати далі: Як розрахувати відсоток точності прогнозу в Excel (4 простих методи)

Висновок

У цій статті ми розглянули 4 методи прогнозування Excel на основі історичних даних. Якщо у вас виникли питання щодо них, будь ласка, повідомте нам поле для коментарів.Для отримання більшої кількості подібних статей відвідайте наш блог ExcelWIKI .

Hugh West

Х'ю Вест - досвідчений тренер та аналітик Excel з більш ніж 10-річним досвідом роботи в галузі. Він має ступінь бакалавра в галузі бухгалтерського обліку та фінансів та ступінь магістра ділового адміністрування. Х'ю пристрасно любить викладати і розробив унікальний підхід до навчання, яке легко слідувати і який легко зрозуміти. Його експертні знання Excel допомогли тисячам студентів та фахівців по всьому світу покращити свої навички та досягти успіху у своїй кар'єрі. У своєму блозі Х'ю ділиться своїми знаннями з усім світом, пропонуючи безкоштовні навчальні посібники з Excel та онлайн-навчання, щоб допомогти окремим особам та компаніям повністю розкрити свій потенціал.

Методи та формули прогнозування в Excel

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

Переваги прогнозування

Прогнозування може допомогти вам приймати правильні рішення та заощаджувати гроші. Ось один приклад.

Час – це гроші. Місце – це гроші. Тому ви хочете використовувати всі доступні засоби, щоб зменшити свої запаси - звичайно, не відчуваючи дефіциту.

Як? За допомогою прогнозування!

Як зробити все просто: мітки, коментарі, імена файлів

З часом, у міру накопичення даних, ви все більше і більше можете заплутатися та робити помилки.Рішення? Не заважайте: правильне використання міток, коментарів і правильне найменування файлів може заощадити вам багато неприємностей.

• Завжди помічайте ваші стовпці. Використовуйте перший рядок кожного стовпця, щоб описати дані, які він містить.
• Різні дані, різні стовпці. Не поміщайте різні числа (наприклад, ваші витрати та продажі) в той самий стовпець. Імовірність плутанини дуже висока, і це ускладнює обчислення та обробку даних.
• Дайте кожному файлу зрозуміле ім'я. Це потребує невеликого зусилля та прискорює процес. Це робить файли, що легко ідентифікуються візуально і спрощує їх пошук за допомогою функції пошуку в Windows.
• Використайте коментарі.

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

Просто клацніть правою кнопкою миші на комірці, до якої потрібно додати коментар, а потім виберіть «Вставити коментар».

Ви можете використовувати їх:

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

Отримайте розширені прогнози продажів за допомогою нашого веб-додатку для прогнозування запасів. Lokad спеціалізується на оптимізації запасів через прогнозування попиту. Зміст цього підручника – і багато іншого – є вбудованими функціями нашого інструменту прогнозування.

Початок роботи: простий приклад прогнозування з використанням трендів

Перегляд ваших даних

Тепер зробимо наш перший прогноз. У цьому розділі ми будемо використовувати цей файл: Example1.xls.Щоб повторити кроки самостійно, можна завантажити файл. Ці дані є лише прикладом.

Наші дані: У першому стовпці дані про одиничні витрати на аналогічні продукти (поодинока вартість відображає якість продукту). У другому стовпці дані, скільки було продано.

Що ми хочемо дізнатися: Якщо ми продаємо ще один продукт з якістю, що відповідає вартості $150/одиниця, скільки одиниць ми можемо очікувати продати?

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

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

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

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

Щоб отримати уявлення про точну форму зв'язку, ми клацаємо правою кнопкою миші на графіку та вибираємо опцію “Лінія тренду”.

Тепер нам потрібно вибрати зв'язок, який, здається, "підходить" (тобто найкраще описує) наші дані. Тут знову ми використовуємо наші очі: у цьому випадку крапки майже знаходяться на прямій лінії, тому ми використовуємо налаштування "лінійне". Згодом ми будемо використовувати інші – складніші, але часто більш реалістичні – налаштування, такі як “експоненційна”.

Тепер наша лінія тренду відображається на графіку.Клацнувши правою кнопкою миші, ми можемо відобразити точну форму зв'язку: y = 102.4x – 191.64.

Розуміння: Кількість проданих одиниць = 102.4 рази одинична вартість – 191.64.

Таким чином, якщо ми вирішимо виробляти за вартістю одиниці $150, ми можемо очікувати на продаж 102.4 * 150 - 191.64 = 15168 одиниць.

Ми тільки-но успішно завершили наш перший прогноз.

Однак будьте обережні: програмне забезпечення завжди здатне знайти зв'язок між двома стовпцями, навіть якщо цей зв'язок насправді дуже слабкий! Тому потрібна перевірка на стійкість. Ось як ви швидко це робите:

• Спочатку завжди подивіться на графік. Якщо ви виявите, що точки знаходяться близько до лінії тренду, як у нашому прикладі вище, є більша ймовірність того, що зв'язок стійкий. Однак, якщо точки здаються розташованими практично випадково і загалом досить далеко від лінії тренду, тоді вам слід бути обережними: кореляція слабка, і оціненого зв'язку не слід довіряти сліпо.

• Після перегляду графіка можна використовувати функцію CORREL. У нашому прикладі функція буде виглядати так: CORREL (A2: A83, B2: B83). Якщо результат близький до 0, то низька кореляція, і висновок такий: просто немає реального тренду. Якщо результат наближається до 1, то кореляція сильна. Останнє корисно, оскільки воно збільшує пояснювальну силу знайденого зв'язку.

Є більш тонкі способи переконатися, що висока кореляція; ми повернемося до цього пізніше.

Звичайно, ці останні кроки можна автоматизувати: вам не потрібно записувати зв'язок та використовувати калькулятор для обчислень. Вам потрібний Analysis Toolpak!

Прогнозування за допомогою Analysis Toolpak

Перш ніж продовжити, переконайтеся, що встановлений Excel ATP (Analysis Toolpak).Щоб отримати додаткові відомості, див. Установка Analysis Toolpak.

На жаль, такі ідеальні дані про продаж з таким гарним, простим лінійним зв'язком досить рідкісні в реальному житті. Давайте подивимося, що Excel пропонує більш складних ситуацій з більш складними даними.

Подальший розвиток: приклад експоненційної апроксимації

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

Ось як виконати експоненційну апроксимацію:

1. Погляньте на ваші дані. Намалюйте простий графік і подивіться на нього. Якщо вони слідують експоненційній еволюції, вони мають виглядати так:

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

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

1. На щастя, ви також можете зробити все це безпосередньо, використовуючи Analysis Toolpak: помістіть всі ваші дані в порожній аркуш Excel і перейдіть до Інструменти => Аналіз даних

Установка Analysis Toolpak (ATP)

ATP - це надбудова, що постачається з Microsoft Excel, але вона не завжди встановлюється за умовчанням. Щоб встановити її, можна вчинити так:

1. Переконайтеся, що ви маєте диск Office. Excel може вимагати вставлення диска для встановлення файлів ATP.
2. Відкрийте аркуш Excel, перейдіть до меню “Інструменти”, а потім виберіть “Надбудови”. Встановіть прапорець у першому вікні, позначеному «2. Analysis Toolpak».
3. Вставте диск Office, якщо програма попросить вас це зробити.
4. Ось і все! Зверніть увагу, що меню “Інструменти” тепер включає набагато більше функцій, включаючи опцію “Аналіз даних”. Саме її ми використовуватимемо найчастіше.

Використання Analysis Toolpak (ATP)

… у лінійному середовищі

Тепер повернемось до нашого лінійного прикладу. Якщо ваші дані "виглядають" добре (див. вище), ви можете використовувати ATP для отримання прямої оцінки функціональної форми, не вдаючись до процесу "трендової лінії".

Відкрийте аркуш даних, потім відкрийте меню “Інструменти” та виберіть “Аналіз даних”. З'явиться вікно, в якому запитають, який аналіз ви хочете виконати. Виберіть “регресію” для лінійних налаштувань.

Тепер вам потрібно вказати Excel два аргументи: "діапазон Y" та "діапазон X". Діапазон Y вказує, що ви хочете оцінити (наприклад, ваші продажі), а діапазон X містить дані, які, на вашу думку, можуть пояснити ваші продажі (тут - вартість одиниці). У нашому прикладі (див. example1.xls) наші дані про продаж знаходяться в стовпці B, від рядка 3 до рядка 90, тому вам потрібно вказати «$B$3:$B$90» як діапазон Y та «$A$3:$ A$90» як діапазон X. Коли ви закінчите, натисніть «ок».

З'являється новий лист, що містить "результати регресії".

Найважливіший результат міститься у стовпці "Коефіцієнти" внизу аркуша. Перехоплення - це константа, а коефіцієнт "X змінної" - це коефіцієнт X (в даному випадку, вартість одиниці).Таким чином, ми отримуємо ту ж саму рівняння, яку ми отримали за допомогою функції “трендової лінії”. Продажу = Перехоплення + Коефіцієнт X * вартість одиниці Продаж = -126 + 100 * вартість одиниці

На цьому аркуші також міститься корисне число, яке дає вам інформацію про те, наскільки хороша ваша оцінка: "R Square". Якщо воно близько до 1, то ваша оцінка хороша, що означає, що знайдене вами рівняння є досить добрим уявленням ваших даних. Якщо воно близько до 0, то оцінка не є хорошою, і вам слід, ймовірно, спробувати інший вид припасування (див. експоненційне припасування нижче).

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

… з використанням експоненційного припасування

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

Запустіть інструментарій аналізу, як завжди: відкрийте свій аркуш даних, потім відкрийте меню інструменти і виберіть Аналіз даних. З'явиться вікно, де буде запропоновано вибрати вид аналізу, який ви хочете виконати.

У нашому випадку з експоненційним припасуванням, ми хочемо вибрати "експоненційну".

Зауважте, що Excel просить вас тільки про один діапазон введення. Виберіть стовпець, який містить дані, які потрібно прогнозувати (тобто вартість одиниці), і виберіть “коефіцієнт згладжування”.

Як я можу дізнатися, яку модель вибрати?

Зверніть увагу, що вам не потрібно пробувати кожен метод оцінки, щоб знайти той, який найкраще підходить для вас. Це можна зробити тільки за допомогою автоматизації, оскільки є така велика кількість методів. Якщо ви хочете, щоб усі моделі були протестовані на ваших даних, ви можете розглянути можливість надсилання їх до Lokad. У нас є потужна комп'ютерна система, яка “тестує” всі моделі та обирає лише ті, які найкраще підходять для даних вашого бізнесу.