Технічний аналіз у excel. Ковзне середнє та експоненційне згладжування в MS Excel

Метод ковзної середньої – це статистичний інструмент, за допомогою якого можна вирішувати різного родузавдання. Зокрема, він часто використовується при прогнозуванні. У програмі ExcelДля вирішення цілого ряду завдань також можна застосовувати цей інструмент. Давайте розберемося, як використовується ковзна середня в Екселі.

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

Спосіб 1: Пакет аналізу

Пакет аналізує надбудовою Excel, яка за замовчуванням відключена. Тому насамперед потрібно її включити.

Після цієї дії пакет «Аналіз даних»активовано, і відповідна кнопка з'явилася на стрічці у вкладці «Дані».

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

1. Переходимо у вкладку «Дані»і тиснемо на кнопку «Аналіз даних», яка розміщена на стрічці інструментів у блоці «Аналіз».



2. Відкривається перелік інструментів, які доступні в Пакет аналізу. Вибираємо з них найменування «Слизьке середнє»і тиснемо на кнопку "OK".



3. Запускається вікно введення даних для прогнозування методом ковзної середньої.

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

   В полі "Інтервал"слід вказати інтервал обробки значень методом згладжування. Для початку давайте встановимо значення згладжування у три місяці, а тому вписуємо цифру «3».

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

   Також слід встановити галочку біля параметра «Стандартні похибки».

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

   Після того, як всі налаштування внесені, тиснемо на кнопку "OK".



4. Програма виводить результат обробки.



5. Тепер виконаємо згладжування за період у два місяці, щоб виявити, який результат є коректнішим. Для цього знову запускаємо інструмент «Слизьке середнє» Пакет аналізу.

   В полі "Вхідний інтервал"залишаємо ті ж значення, що й у попередньому випадку.

   В полі "Інтервал"ставимо цифру «2».

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

   Інші налаштування залишаємо колишніми. Після цього тиснемо на кнопку "OK".



6. Після цього програма робить розрахунок і виводить результат на екран. Для того, щоб визначити, яка з двох моделей точніша, нам потрібно порівняти стандартні похибки. Чим менший цей показник, тим вище ймовірність точності отриманого результату. Як бачимо, за всіма значеннями стандартна похибка при розрахунку двомісячної ковзної менше, ніж аналогічний показник за 3 місяці. Таким чином, прогнозованим значенням на грудень можна вважати величину, розраховану методом ковзання за останній період. У разі це значення 990,4 тис. рублів.

Спосіб 2: використання функції СРЗНАЧ

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

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

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

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



2. Активується вікно Майстри функцій. у категорії «Статистичні»шукаємо значення «СРЗНАЧ», виділяємо його та клацаємо по кнопці "OK".



3. Запускається вікно аргументів оператора Відмінник. Синтаксис у нього такий:

   СРЗНАЧ(число1; число2; ...)

   Обов'язковим є лише один аргумент.

   У нашому випадку, у полі «Число1»ми повинні вказати посилання на діапазон, де вказано дохід за два попередні періоди (січень та лютий). Встановлюємо курсор у полі та виділяємо відповідні осередки на аркуші в стовпці "Дохід". Після цього тиснемо на кнопку "OK".



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



5. Отримуємо розрахунок результатів середнього значення за два попередні місяці до кінця року.



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



7. За допомогою маркера заповнення копіюємо формулу в комірки таблиці, розташовані нижче.



8. Отже, значення ми підрахували. Тепер, як і в попередній раз, нам потрібно буде з'ясувати, який вид аналізу якісніший: зі згладжуванням у 2 або 3 місяці. Для цього слід розрахувати середнє квадратичне відхилення та деякі інші показники. Для початку розрахуємо абсолютне відхилення, скориставшись стандартною функцією Excel ABSяка замість позитивних чи негативних чисел повертає їх модуль. Це значення дорівнює різниці між реальним показником виручки за обраний місяць і прогнозованим. Встановлюємо курсор у наступний порожній стовпець у рядок за травень. Викликаємо Майстер функцій.



9. у категорії "Математичні"виділяємо найменування функції «ABS». Тиснемо на кнопку "OK".



10. Запускається вікно аргументів функції ABS. У єдиному полі «Кількість»вказуємо різницю між вмістом осередків у стовпцях "Дохід"і "2 місяці"за травень. Потім тиснемо на кнопку "OK".



11. За допомогою маркера заповнень копіюємо цю формулу у всі рядки таблиці до листопада включно.



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



13. Аналогічну процедуру виконуємо і для того, щоб підрахувати абсолютне відхилення для ковзання за 3 місяці. Спочатку застосовуємо функцію ABS. Тільки цього разу вважаємо різницю між вмістом осередків із фактичним доходом та плановим, розрахованим за методом ковзної середньої за 3 місяці.



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



15. Наступним кроком є ​​підрахунок відносного відхилення. Воно рівне відношенню абсолютного відхилення до фактичного показника. Для того, щоб уникнути негативних значень, ми знову скористаємося тими можливостями, які пропонує оператор ABS. На цей раз за допомогою цієї функції ділимо значення абсолютного відхилення при використанні методу ковзної середньої за 2 місяці на фактичний дохід за вибраний місяць.



16. Але відносне відхилення прийнято відображати у відсотковому вигляді. Тому виділяємо відповідний діапазон на аркуші, переходимо у вкладку «Головна», де в блоці інструментів «Кількість»у спеціальному полі форматування виставляємо процентний формат. Після цього результат підрахунку відносного відхилення відображається у відсотках.



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



18. Після цього вираховуємо середні значення для обох колонок із відносним відхиленням, як і раніше використовуючи для цього функцію Відмінник. Так як для розрахунку як аргументи функції ми беремо відсоткові величини, то додаткову конвертацію робити не потрібно. Оператор на виході видає результат уже у процентному форматі.



19. Тепер ми підійшли до розрахунків середнього квадратичного відхилення. Цей показник дозволить нам безпосередньо порівняти якість розрахунку при використанні згладжування за два та за три місяці. У нашому випадку середнє квадратичне відхилення дорівнюватиме кореню квадратному із суми квадратів різниць фактичної виручки та ковзної середньої, поділеної на кількість місяців. Для того, щоб зробити розрахунок у програмі, ми маємо скористатися цілим рядом функцій, зокрема КОРІНЬ, СУМКВРАЗНі РАХУНОК. Наприклад, для розрахунку середнього квадратичного відхилення при використанні лінії згладжування за два місяці у травні буде в нашому випадку застосовуватись формула наступного виду:

    КОРІНЬ(СУМКВРАЗН(B6:B12;C6:C12)/РАХУНОК(B6:B12))

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



20. Аналогічну операцію з розрахунку середнього квадратичного відхилення виконуємо і для ковзної середньої за 3 місяці.



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



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



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

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

1. Розрахувати коефіцієнти сезонності;
2. Вибрати період для розрахунку середньогозначення;
3. Розрахувати прогноз, тобто. середнє значення помножити на коефіцієнт сезонності;
4. Врахувати додаткові фактори, що значно впливають на продаж;

Розрахувати прогноз за методом ковзноїсередньої дуже просто. Для цього беремо середнє значення, наприклад, середні продажі за останні 3 місяці та множимо на коефіцієнт сезонностідо 3-х місяців – і прогноз на місяць готовий. Аналогічним чином робимо і наступного місяця, тільки до розрахунку вже потрапить попередній прогнозний місяць.

1. Розрахуємо коефіцієнти сезонності для прогнозу за методом ковзної середньої.

Для цього розраховуємо коефіцієнти сезонності очищені від зростання, як описано у статті «Як розрахувати коефіцієнти сезонності, очищені від зростання?» . Потім визначаємо коефіцієнти сезонності до попередніх періодів, до 1 місяця, до 2-го місяця, до 3-го місяця і т.д. залежно від цього, який період беремо середнє значення для прогнозування продажів. Наприклад, розрахуємо місячні коефіцієнти сезонності (див. вкладений файл лист "Розрахунок коефіцієнтів")

до 1 місяця:

• коефіцієнт січня - відношення січневого коефіцієнта сезонності очищеного від зростання до грудневого;

  лютого – лютневого коефіцієнта до січневого;

  березня – березень до лютого;

до 2-х місяців:

• для січня - ставлення січневого коефіцієнта сезонності до середнього значення грудня та листопада

  для лютого - лютий ділимо на середнє значення коефіцієнтів січня та грудня

  для березня - березень до середнього лютневого та січневого коефіцієнтів

до 3-х місяців:

• для визначення січневого коефіцієнта сезонності до 3-х місяців січневий коефіцієнт сезонності, очищений від зростання, ділимо на середнє значення коефіцієнтів сезонності, очищених від зростання, за грудень, листопад, жовтень;

  для лютого - коефіцієнт лютого ділимо на середнє значення коефіцієнтів листопада, грудня та січня;

  Для березня - ставлення березня до середнього значення коефіцієнтів сезонності очищених від зростання грудня, січня та лютого;

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

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

Для цього робимо прогноз для останнього та передостаннього періодів, дані за який нам відомі, трьома чи більше способами для визначення відповідного періоду розрахунку середньої(Див. вкладений файл лист «Вибір періоду»). І дивимося, який із варіантів робить більш точний прогноз:

1. Розрахуємо прогноз продажів за методом ковзної середньої до 1-го місяця:

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

1. Розрахуємо прогноз продажів за методом ковзної середньої до 2-ум місяців:

Грудень = середній обсяг продажів за жовтень та листопад помножимо на грудневий коефіцієнт сезонності до 2-х місяців.

1. Розраховуємо прогноз за методом ковзної середньої до 3-х місяців:

Грудень = середній обсяг продажів за вересень, жовтень та листопад помножимо на грудневий коефіцієнт сезонності до 3-х місяців.

Наразі ми розрахували прогноз трьома способами на грудень. Аналогічно розрахуємо на листопад.

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

3. Розрахуємо прогноз продажів за методом ковзної середньої.

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

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

Дотримуючись цієї логіки, ми продовжуємо розрахунок прогнозу до кінця року. Розрахунок прогнозу продажів на рік готовий.

4. Додаткові чинники, які варто врахувати під час розрахунку прогнозу продажів.

Для підвищення точності прогнозу важливо:

1. З минулих періодів відняти фактори, які значно вплинули на обсяг продажу, але в прогнозних місяцях не повторюватимуться(Акції зі стимулювання збуту, разове відвантаження великого нерегулярного клієнта, виведення з великої роздрібної мережі тощо).
2. До прогнозованих місяців додати фактори, які значно вплинуть на продаж - початок роботи з великими мережами, проведення великих акцій зі стимулювання збуту, виведення нових товарів, рекламні компанії тощо.

Точних вам прогнозів!

Програма Forecast4AC PRO розрахує прогноз за методом ковзної середньої одночасно більш ніж для 1000 часових рядіводним натисканням клавіші, значно заощадивши ваш час, одним з 4-х способів:

До середнього за два попередні періоди

До середнього за три попередні періоди

До середнього за 4 попередні періоди

Подвійна середня до 3 та 4 попередніх періодів

Приєднуйтесь до нас!

Завантажуйте безкоштовні програмидля прогнозування та бізнес-аналізу:

• Novo Forecast Lite- автоматичний розрахунок прогнозув Excel.
• 4analytics - ABC-XYZ-аналізта аналіз викидів у Excel.
• Qlik Sense Desktop та QlikViewPersonal Edition - BI-системи для аналізу та візуалізації даних.

Тестуйте можливості платних рішень:

• Novo Forecast PRO- прогнозування Excel для великих масивів даних.

У бізнесі, як і в будь-якій іншій діяльності людина, хоче знати, а що буде далі. Навіть важко уявити багатство того щасливця, який зі 100% точністю міг би вгадувати майбутнє. Але, на жаль (або, на щастя) дар передбачення зустрічається вкрай рідко. А… намагатися хоча б у загальних рисахуявити майбутню бізнес ситуацію підприємець просто зобов'язаний.

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

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

• Обсяги продажів
• Розмір та ємність ринку
• Об'єми виробництва
• Обсяги імпорту
• Динаміка цін
• та ін.

Для прогнозування, яке ми розглядаємо в даному пості, раджу дотримуватися наступного простого алгоритму:

1. Збір вторинної інформаціїз проблеми(бажано як кількісної, і якісної). Так, наприклад, якщо Ви прогнозуєте розмір свого ринку, потрібно зібрати статистичну інформацію щодо ринку (обсяги виробництва, імпорту, динаміку цін, обсяги продажу та ін.) так і тенденції, проблеми чи можливості ринку. Якщо ви прогнозуєте обсяг продажу, тоді вам потрібні дані про продаж за період. Для прогнозування чим більше історичних даних ви розглянете, тим краще. Бажано прогнозування доповнити аналізом факторів, що впливають на прогнозоване явище (можна SWOT, PEST аналіз або будь-який інший). Це дозволить розуміти логіку розвитку, і ви зможете таким чином перевіряти правдоподібність тієї чи іншої моделі тренду.

2. Далі бажано перевірити кількісні дані. Для цього потрібно порівняти значення тих самих показників, але отриманих з різних джерел. Якщо все сходитися, можна «заганяти» дані в Excel. Також дані повинні відповідати таким вимогам:

• Базова лінія включає результати спостережень - починаючи з ранніх і закінчуючи останніми.
• Усі періоди базової лінії мають однакову тривалість. Не слід змішувати дані, наприклад, за день із середніми триденними показниками.
• Спостереження фіксуються в той самий момент кожного тимчасового періоду. Наприклад трафік замірятись повинен в один і той же час.
• Перепустка даних не допускається. Перепустка навіть одного результату спостережень небажана при прогнозуванні» тому, якщо у ваших спостереженнях відсутні результати за незначний відрізок часу, постарайтеся заповнити їх хоча б приблизними даними.

3. Перевіривши дані, можна застосовувати різні методики прогнозування. Почати я хотів би з самого простого методу – МЕТОДУ КОВЗНОГО СЕРЕДНЬОГО

МЕТОД КОВЗНОГО СЕРЕДНЬОГО

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

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

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

Отже, як це робити вExcel

1. Припустимо, що у Вас є обсяги місячних продажів за останні 29 місяців. І ви хочете визначити, який обсяг продажів буде у 30 місяці. Але, якщо чесно, зовсім не обов'язково при розрахунку прогнозних значень оперувати 30 історичними значеннями, адже цей метод використовуватиме для розрахунку середнього лише кілька останніх місяців. Тому для розрахунку достатньо лише кілька минулих місяців.

2. Наводимо цю таблицю як зрозумілий Excel, тобто. щоб усі значення були в одному ряді.

3. Далі вводимо формулу розрахунку середнього за попереднім трьом (чотирьом, п'яти? як самі оберіть) значенням (див. в). Найзручніше все-таки використовуватиме розрахунку останні 3 значення, т.к. якщо враховувати більше, дані будуть занадто середні, якщо менше - не будуть точними.

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

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

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

Інструмент "Ковзне середнє" можна викликати в діалоговому вікні команди "Аналіз даних" з меню "Сервіс".

За допомогою інструменту ковзної середньої складаю прогноз економічних показників таблиці 1.1 (табл. 3.1).

Таблиця3 .1 ― Оцінка тенденції поведінки показників досліджуваного динамічного ряду методом ковзного середнього

Примітка - Джерело: .

З даних таблиці строю графік ковзної середньої.

Рисунок 3.1 – Ковзне середнє

Примітка - Джерело: .

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

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

1. Складання лінійних прогнозів засобами Excel

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

1. Використання функції лінійн для створення моделі тренду

Функція робочого листа ЛІНІЙН допомагає визначити характер лінійного зв'язку між результатами спостережень та часом їх фіксації та дати їй математичний опис, найкращим чиномапроксимує вихідні дані. Для побудови моделі вона використовує рівняння виду y = mx + b, де y - досліджуваний показник; x = t - тимчасовий тренд; b, ​​m - параметри рівняння, що характеризують відповідно y-перетин і нахил лінії тренду. Розрахунок параметрів моделі Лінейн виробляють на основі методу найменших квадратів.

Викликувати функцію ЛІНІЙН можна у діалоговому вікні «Майстер функцій» (категорія «Статистичні»), розташованому на панелі інструментів «Стандартні».

Таблиця 3.2 ― Розрахунок та оцінка лінійної моделі тренду за допомогою функції ЛІНІЙН

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

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

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

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

При згладжуванні часового ряду ковзними середніми у розрахунках беруть участь усі рівні ряду. Чим ширший інтервал згладжування, тим паче плавним виходить тренд. Згладжений ряд коротший за початковий на (n–1) спостережень, де n – величина інтервалу згладжування.

При великих значеннях n коливання згладженого ряду значно знижується. Одночасно помітно скорочується кількість спостережень, що створює труднощі.

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

Цей методвикористовується при короткостроковому прогнозуванні. Його робоча формула:

Приклад застосування методу ковзної середньої розробки прогнозу

Завдання . Є дані, що характеризують рівень безробіття у регіоні, %

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

Рішення методом ковзної середньої

Для розрахунку прогнозного значення методом ковзної середньої необхідно:

1. Визначити величину інтервалу згладжування, наприклад, рівну 3 (n = 3).

2. Розрахувати ковзну середню для перших трьох періодів
m лютий = (Уянв + Уфев + У березень) / 3 = (2,99 +2,66 +2,63) / 3 = 2,76
Отримане значення заносимо до таблиці у середину взятого періоду.
Далі розраховуємо m наступних трьох періодів лютий, березень, квітень.
m березень = (Уфев + Умарт + Уапр) / 3 = (2,66 +2,63 +2,56) / 3 = 2,62
Далі за аналогією розраховуємо m для кожних трьох періодів, що стоять поруч, і результати заносимо в таблицю.

3. Розрахувавши ковзну середню для всіх періодів, будуємо прогноз на листопад за формулою:

де t + 1 – прогнозний період; t - період, що передує прогнозному періоду (рік, місяць і т.д.); Уt+1 – прогнозований показник; mt-1 - ковзна середня за два періоди до прогнозного; n – кількість рівнів, що входять до інтервалу згладжування; Уt - Фактичне значення досліджуваного явища за попередній період; Уt-1 - фактичне значення досліджуваного явища за два періоди, що передують прогнозному.

У листопад = 1,57 + 1/3 (1,42 - 1,56) = 1,57 - 0,05 = 1,52
Визначаємо ковзну середню m для жовтня.
m = (1,56 +1,42 +1,52) / 3 = 1,5
Будуємо прогноз на грудень.
У грудень = 1,5 + 1/3 (1,52 - 1,42) = 1,53
Визначаємо ковзну середню m для листопада.
m = (1,42 +1,52 +1,53) / 3 = 1,49
Будуємо прогноз на січень.
У січень = 1,49 + 1/3 (1,53 - 1,52) = 1,49
Заносимо отриманий результат таблицю.

Розраховуємо середню відносну помилкуза формулою:

ε = 9,01/8 = 1,13% точність прогнозувисока.

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