Ковзне середнє та експоненційне згладжування в MS Excel. Метод ковзної середньої в Microsoft Excel

Мета роботи : Придбати навички вирішення задач частотного аналізу за допомогою функції робочого листа аналізу MS Excel.

Коротка теорія

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

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

Як масив даних може бути одновимірний або двовимірний масив (наприклад, A4: D15).

Синтаксис: ЧАСТОТА (масив_даних; масив_кишень)

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

Завдання 1

За допомогою функціїЧастота для вибірки безлічі сум замовлень () введіть у діапазон підрахуйте, скільки значень потрапляють у задані інтервали значень. Наприклад, від 0 до 1000, від 1001 до 1500, від 1501 до 2000, від 2001 до 2500, понад 2500.

Порядок дій:

1. На робочому аркуші MS Excel введіть дані про обсяги замовлень у 20 філіях фірми за вересень у вигляді таблиці, фрагмент якої показано на малюнку.

1. У вільний діапазон клітин (стовпець) введіть верхні межі інтервалів (Наприклад, D 2 = 1000, D 3 = 1500, D 4 = 2000, D 5 = 2500).
2. Виділіть блок осередків стовпця, суміжного зі стовпцем інтервалів ( E 2: E 21). Для того, щоб підрахувати кількість значень, що перевищують нижню межу інтервалу, виділяється діапазон на одну комірку більше, ніж діапазон інтервалів.
3. В діапазоні E 2: E 6 введіть формулу ( =ЧАСТОТА( E 2: E 15; J 2: J 6)).

Для цього скористайтеся майстром функцій (Вставка/Функція). У категорії «Статистичні» виберіть зі списку опцію «Частота». У діалоговому вікні ЧАСТИНА заповніть поля масиву вибірки та масиву інтервалів (рисунок 1).Не виходячи з вікна діалогунатисніть комбінацію клавіш< Ctrl / Shift / Enter > для розрахунку елементів масиву

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

1. Побудуйте діаграму за результатами.
2. Збережіть файл.

Завдання 2

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

При використанні інструмента аналізу даних у діалоговому вікні у поліВхідний інтервалвведіть вихідний інтервал, за яким будується гістограма, у поліІнтервал кишень- Діапазон зі значеннями верхніх меж інтервалів. Гістограма будується на новому або поточному робочому аркуші.

Частина 2

Вирішення задач прогнозування в середовищі MS Excel. Метод ковзного середнього

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

Коротка теорія

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

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

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

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

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

Якщо для прогнозу найбільш значущими є останні результатиспостережень, використовують метод експоненціального згладжування. У методі експоненційного згладжування кожне значення бере участь у формуванні прогнозованих значень зі змінною вагою, яка зменшується в міру «старіння» даних. В інструменті аналізу MS Excel « Експонентне згладжування»ваговий коефіцієнт, або параметр згладжування, визначається параметромФактор згасання. Зазвичай для часових рядів в економічних завданнях величину параметра згладжування задають в інтервалі від 01 до 03. Початкове розрахункове значення у процедуріЕкспонентне згладжуванняпакету Аналізу MS Excel приймається рівним рівню першого члена низки. Метод забезпечує гарне узгодження вихідних та розрахункових даних для перших значень ряду. Якщо кінцеві обчислені значення значно відрізняються від відповідних вихідних, доцільно змінити величину параметра згладжування. Оцінити величини розбіжностей можна з урахуванням стандартних похибок і графіка, які пакет Аналізу дозволяє вивести разом із розрахунковими значеннями ряду.

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

Завдання до лабораторної роботи(частина 2)

Завдання 1 : Обчислити прогнозоване значення величини обсягу продукції (послуг) підприємства методом ковзного середнього.

Порядок виконання завдання:

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

Для обчислень скористаємось способом прямого введення формули. Щоб отримати трирічне ковзне середнє обсягу виконаних послуг для нашого прикладу, введемо в осередок B 5 формулу для обчислення = СРЗНАЧ( A 2: A 4). Скопіюємо формулу в інтервал B6: B11.

Рисунок 1 | Обчислення простого ковзного середнього

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

Малюнок 2 | Графік тенденції зміни показника обсягу послуг, отриманої методом простого ковзного середнього

Іншим способом рішення є використання для визначення ковзного цілогоПакет аналізу . Пакет аналізу є надбудовою MS Excel (Виберіть пункт менюСервіс / Надбудовита встановіть прапорецьПакет аналізу).

Порядок дій

1. Виконати командуСервіс/Аналіз данихта вибрати зі списку інструментів аналізуКовзне середнє.
2. У діалоговому вікні вкажіть параметри для обчислення ковзного середнього:

• Як вхідний інтервал виділіть блок осередків, що містить дані про обсяг послуг.
• Вкажіть Інтервал- 3 (за замовчуванням використовується 3), в якості вихідного інтервалу будь-яку комірку робочого листа (просто клацніть на комірці робочого листа, з якої повинні виводитися результати);

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

Проаналізуйте використовувані розрахункові формули та отримані результати.

Аналогічно обчисліть п'ятирічні прості ковзні середні. Порівняйте результати згладжування двох варіантів розрахунку.

Завдання 2: Обчислити прогнозоване значення величини обсягу продукції (послуг) підприємства шляхом експоненційного згладжування.

Порядок дій:

1. На аркуші MS Excel створіть список, що містить дані про кількість співробітників фірми за останні 10 років. Дані введіть довільно, але так, щоб простежувалася тенденція.
2. Проведіть згладжування часового ряду за допомогою експоненційної середньої з параметрами згладжування 0,1 і потім 0,3. За результатами розрахунків побудуйте графік та визначте, який із отриманих часових рядів має більш гладкий характер.

Скористайтеся командоюСервіс/Аналіз данихта виберіть зі списку інструментів аналізуЕкспонентне згладжування.Вкажіть параметри для обчислення ковзного середнього:

• Як вхідний інтервал виділіть блок осередків, що містить дані про чисельність.
• Вкажіть Фактор згасання. В якості вихідного інтервалу будь-яку комірку робочого листа.
• Задайте виведення графіка та стандартних похибок.

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

Частина 3

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

Коротка теорія

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

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

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

Excel може автоматично проводити лінії тренду різних типів безпосередньо на діаграмі. Обчислення можна проводити двома способами:

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

Перший спосіб

Лінійне наближення

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

Експонентне наближення

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

Другий спосіб

У MS Excel вбудовано статистичні функції робочого листа.

ТЕНДЕНЦІЯ() - Повертає значення відповідно до лінійної апроксимації за методом найменших квадратів.

ЗРІСТ() - Повертає значення відповідно до експоненційного тренду.

Використання цих функцій ще один спосіб обчислення регресійного аналізу.

Формат

ТЕНДЕНЦІЯ (зв_знач_Y; зв_знач_X; новий_знач_X; константа)

Функція РОСТ повертає значення відповідно до експоненційного тренду.

Завдання до лабораторної роботи (частина 3)

Завдання 1:

Розрахуйте лінійний та експоненційний прогноз на один рік та на наступні три періоди (до 2011 року) за допомогою маркера заповнення.

Завдання 2:

Розрахуйте лінійний та експоненційний прогноз на один рік і потім на наступні три періоди за допомогою функцій робочого листа ТЕНДЕНЦІЯ та РОСТ. Для розрахунку інтервального прогнозу після заповнення параметрів діалогового вікна функції та не виходячи з нього натисніть комбінацію клавіш Ctrl/Shift/Enter.

У рядку формул робочого листа має з'явитися формула для розрахунку елементів масиву, наприклад,

( = ТЕНДЕНЦІЯ (B 3: G 3; B 2: G 2; B 2: H 2))

Визначте, яка модель є найточнішою.

Побудуйте графіки та лінії тренду для першого та другого завдання.

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 одиниць товару. Але зверніть увагу, що якщо тенденція падіння постійна, як у нашому прикладі, розрахунок прогнозу за середньою буде трохи завищеним, або буде «відставати» від реальних значень.

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

Метод ковзної середньої – це статистичний інструмент, за допомогою якого можна вирішувати різного родузавдання. Зокрема, він часто використовується при прогнозуванні. У програмі 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 тис. рублів. Як бачимо, це значення повністю збігається з тим, яке ми отримали, розраховуючи за допомогою інструментів Пакет аналізу.

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

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

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

Даний метод в Excel застосовується через використання функції пакета аналізу і безпосередньо через саму вбудовану функцію, яка отримала назву СРЗНАЧ.

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

1. Пакет аналізу в стандартному наборі функцій немає, тому його необхідно включити. Робиться це через параметри документа - "Файл" - "Параметри" - "Надбудови". Внизу діалогового вікна є вкладка Надбудови. Саме вона нам і потрібна.

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

2. Щоб зрозуміти, яким чином працює метод ковзної середньої, спробуємо отримати дані за 12 місяців на основі тих, які ми вже отримали за 11 минулих – зробимо прогноз. Заповнюємо вихідні значення таблиці.

3. У раніше доданому функціоналі «Аналіз даних» на робочій панелі з параметрів надбудов документа, вибираємо «Змінну середню» функцію і натискаємо «Ок».

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

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

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

1. Якщо пакет аналізу робить практично всі операції автоматизованими, то використання функції СРЗНАЧ вимагає застосування кількох стандартних функцій Excel. Використовуємо ті самі вихідні дані з 11 місяців. Вставимо функцію.

2. У діалоговому вікні Майстра функцій перейдемо у вкладку «Статистичні» і виберемо нашу функцію «СРЗНАЧ».

3. Функція «СРЗНАЧ» має дуже простий синтаксис – «=СРЗНАЧ(число1;число2;число3;...). Вкажемо в аргументі «число 1» діапазон за «Січень» та «Лютий».

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

5. Проведемо цю операцію, але з різницею в період за 3 місяці.

6. Але які дані у нашому випадку вірні, на основі двох місяців чи трьох? Для отримання правильної відповіді застосуємо розрахунок абсолютного відхилення, середнього квадратичного та ще кількох інших показників. За повне відхилення відповідає функція «ABS».

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

7. Маркером заповнення заповнимо стовпець та розрахуємо «СРЗАНЧ» за весь час.

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

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

Всі дані уявимо у відсотках.

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

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

Пропишемо нашу функцію «КОРІНЬ(СУМКВРАЗН(B6:B12;C6:C12)/РАХУНОК(B6:B12))», заповнимо стовпці маркерами заповнення і знайдемо середнє значення за отриманими даними.

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