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

Зміст

• Процедура прогнозування
  • Спосіб 1: лінія тренду
  • Спосіб 2: оператор передбачення
  • Спосіб 3: оператор тенденція
  • Спосіб 4: оператор зростання
  • Спосіб 5: оператор лінійний
  • Спосіб 6: оператор ЛГРФПРИБЛ
• Питання та відповіді

Прогнозування – це дуже важливий елемент практично будь-якої сфери діяльності, починаючи від економіки і закінчуючи інженерією. Існує велика кількість програмного забезпечення, що спеціалізується саме на цьому напрямку. На жаль, далеко не всі користувачі знають, що звичайний табличний процесор 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; відомі_значення_у;відомі значення_х)

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

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

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

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

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

Давайте розберемо нюанси застосування оператора передбачення на конкретному прикладі. Візьмемо всю ту ж таблицю. Нам потрібно буде дізнатися прогноз прибутку на 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» відповідає аргументу «X» попереднього інструменту. Крім того, у тенденція є додатковий аргумент «Константа» , але він не є обов'язковим і використовується тільки при наявності постійних факторів.

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

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

1. Виробляємо позначення осередку для виведення результату і запускаємо Майстер функцій звичайним способом. В категорії »статистичні" знаходимо і виділяємо найменування «тенденція» . Тиснемо на кнопку «OK» .

2. Відкривається вікно аргументів оператора тенденція . У полі «відомі значення y» вже описаним вище способом заносимо координати колонки " прибуток підприємства» . У полі «відомі значення x» вводимо адресу стовпця «рік» . У полі «нові значення x» заносимо посилання на клітинку, де знаходиться номер року, на який потрібно вказати прогноз. У нашому випадку це 2019 рік. Поле «Константа» залишаємо порожнім. Клацаємо по кнопці «OK» .

3. Оператор обробляє дані та виводить результат на екран. Як бачимо, сума прогнозованого прибутку на 2019 рік, розрахована методом лінійної залежності, складе, як і при попередньому методі розрахунку, 4637,8 тис.рублів.

Спосіб 4: оператор зростання

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

=зростання (відомі значенія_у; відомі значенія_х; новие_значенія_х; [конст])

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

1. Виділяємо осередок виведення результату і вже звичним шляхом викликаємо Майстер функцій . У списку статистичних операторів шукаємо пункт «зростання» , виділяємо його і клацаємо по кнопці «OK» .

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

3. Результат обробки даних виводиться на монітор у зазначеній раніше комірці. Як бачимо, на цей раз результат становить 4682,1 тис.рублів. Відмінності від результатів обробки даних оператором тенденція незначні, але вони є. Це пов'язано з тим, що дані інструменти застосовують різні методи розрахунку: метод лінійної залежності і метод експоненціальної залежності.

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

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

=лінійний (відомі значенія_у; відомі значенія_х; новие_значенія_х; [конст]; [статистика])

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

1. Виробляємо виділення комірки, в якій буде проводитися обчислення і запускаємо Майстер функцій. Виділяємо найменування»лінійний" в категорії »статистичні" і тиснемо на кнопку «OK» .

2. У полі «відомі значення y» , що відкрився вікна аргументів, вводимо координати стовпця " прибуток підприємства» . У полі «відомі значення x» вносимо адресу колонки «рік» . Решта поля залишаємо порожніми. Потім тиснемо на кнопку «OK» .

3. Програма розраховує і виводить в обрану комірку значення лінійного тренда.

4. Тепер нам належить з'ясувати величину прогнозованого прибутку на 2019 рік. Встановлюємо знак «=» до будь-якої порожньої комірки на аркуші. Кількома по осередку, в якій міститься фактична величина прибутку за останній досліджуваний рік (2016 г.). Ставимо знак «+» . Далі натискаємо по осередку, в якій міститься розрахований раніше лінійний тренд. Ставимо знак «*» . Так як між останнім роком досліджуваного періоду (2016 г.) і роком на який потрібно зробити прогноз (2019 г.) лежить термін в три роки, то встановлюємо в осередку число «3» . Щоб зробити розрахунок натискаємо на кнопку Enter .

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

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

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

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

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

1. У списку операторів Майстра функцій виділяємо найменування " ЛГРФПРИБЛ» . Робимо клацання по кнопці «OK» .

2. Запускається вікно аргументів. У ньому вносимо дані точно так, як це робили, застосовуючи функцію лінійний . Клацаємо по кнопці «OK» .

3. Результат експоненціальної тенденції підрахований і виведений у позначену клітинку.

4. Ставимо знак «=» до порожньої комірки. Відкриваємо дужки і виділяємо осередок, яка містить значення виручки за останній фактичний період. Ставимо знак «*» і виділяємо клітинку, що містить експоненціальний тренд. Ставимо знак мінус і знову натискаємо по елементу, в якому знаходиться величина виручки за останній період. Закриваємо дужку і вбиваємо символи «*3+» без лапок. Знову натискаємо по тій же клітинці, яку виділяли в останній раз. Для проведення розрахунку тиснемо на кнопку Enter .

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

Урок: інші статистичні функції в Excel

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