метод ковзної середньої в Microsoft Excel

Зміст

• Застосування ковзної середньої
  • Спосіб 1: Пакет аналізу
  • Спосіб 2: Використання функції СРЗНАЧ
• Питання та відповіді

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

Застосування ковзної середньої

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

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

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

1. Переміщаємося у вкладку " Файл» . Робимо клацання по пункту «Параметри» .

2. У вікні параметрів слід перейти в розділ " надбудови» . У нижній частині вікна в полі «Управління» повинен бути виставлений параметр «Надбудови Excel» . Клацаємо по кнопці »Перейти" .

3. Ми потрапляємо у вікно надбудов. Встановлюємо галочку біля пункту " Пакет аналізу» і клацаємо по кнопці «OK» .

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

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

Урок: Майстер функцій в Екселі

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