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

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

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

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

• ← Обчислення функції Лапласа в Microsoft Excel
• Видалення рядка в Microsoft Excel →