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

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

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

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

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

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

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

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

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

   У блоці налаштувань «Прогноз» в поле «Вперед на" встановлюємо число «3,0», так як нам потрібно скласти прогноз на три роки вперед. Крім того, можна встановити галочки біля налаштувань «Показувати рівняння на діаграмі» і «Помістити на діаграмі величину достовірності апроксимації (R ^ 2)». Останній показник відображає якість лінії тренда. Після того, як налаштування зроблені, тиснемо на кнопку «Закрити».

4. Лінія тренда побудована і по ній ми можемо визначити приблизну величину прибутку через три роки. Як бачимо, на той час вона повинна перевалити за 4500 тис. Рублів. Коефіцієнт R2, як уже було сказано вище, відображає якість лінії тренда. У нашому випадку величина R2 становить 0,89. Чим вище коефіцієнт, тим вище вірогідність лінії. Максимальна величина його може бути рівною 1. Прийнято вважати, що при коефіцієнті понад 0,85 лінія тренда є достовірною.
5. Якщо ж вас не влаштовує рівень достовірності, то можна повернутися в вікно формату лінії тренда і вибрати будь-який інший тип апроксимації. Можна перепробувати всі доступні варіанти, щоб знайти найбільш точний.

   Потрібно зауважити, що ефективним прогноз за допомогою екстраполяції через лінію тренда може бути, якщо період прогнозування не перевищує 30% від аналізованої бази періодів. Тобто, при аналізі періоду в 12 років ми не можемо скласти ефективний прогноз більш ніж на 3-4 роки. Але навіть в цьому випадку він буде відносно достовірним, якщо за цей час не буде ніяких форс-мажорів або навпаки надзвичайно сприятливих обставин, яких не було в попередніх періодах.

урок: Як побудувати лінію тренда в Excel

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

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

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

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

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

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

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

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

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

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

   В поле «Відомі значення y» вказуємо координати стовпчика «Прибуток підприємства». Це можна зробити, встановивши курсор в поле, а потім, затиснувши ліву кнопку миші і виділивши відповідний стовпець на аркуші.

   Аналогічним чином в поле «Відомі значення x» вносимо адресу стовпця «Рік» з даними за минулий період.

   Після того, як вся інформація внесена, тиснемо на кнопку «OK».

4. Оператор проводить розрахунок на підставі введених даних і виводить результат на екран. На 2018 рік планується прибуток в районі 4564,7 тис. Рублів. На основі отриманої таблиці ми можемо побудувати графік за допомогою інструментів створення діаграми, про які йшла мова вище.
5. Якщо поміняти рік в осередку, яка використовувалася для введення аргументу, то відповідно зміниться результат, а також автоматично оновиться графік. Наприклад, за прогнозами в 2019 році сума прибутку складе 4637,8 тис. Рублів.

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

урок: Екстраполяція в Excel

Спосіб 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

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

• ← Транспонування матриці в програмі Microsoft Excel
• Обчислення NPV в Microsoft Excel →