Розрахунок ковзної середньої в Excel (простий, зважений та експоненціальний)

Як і багато мої підручники Excel, цей також натхненний одним із запитів, які я отримав від друга. Вона хотіла обчислити ковзну середню в Excel, і я попросив її пошукати її в Інтернеті (або подивитися відео про це на YouTube).

Але потім я вирішив написати один (той факт, що я був дещо статистичним ботаном у коледжі, також зіграв другорядну роль).

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

Якщо ви хочете перейти до частини, де я показую, як обчислити ковзну середню в Excel, натисніть тут.

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

Що таке ковзаюча середня?

Я впевнений, що ви знаєте, що таке середнє значення.

Якщо у мене є триденні дані про денну температуру, ви можете легко сказати мені середнє значення за останні три дні (підказка: для цього можна скористатися функцією СРЕДНЯ в Excel).

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

Наприклад, на 3 день, якщо я запитаю у вас 3-денну середню температуру ковзання, ви дасте мені середнє значення температури 1, 2 і 3 дня. І якщо на 4 день я запитаю у вас 3-денну середню температуру ковзання , ви дасте мені середнє значення за 2, 3 та 4 день.

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

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

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

Види ковзних середніх

Існує три типи ковзних середніх:

• Просте ковзне середнє (SMA)
• Зважена ковзаюча середня (WMA)
• Експоненціальна ковзна середня (EMA)

Проста ковзаюча середня (SMA)

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

У нашому прикладі денної температури, коли ви просто берете середнє значення за останні 10 днів, це дає 10-денну просту ковзаючу середню.

Це може бути досягнуто шляхом усереднення точок даних за задану тривалість. В Excel це можна легко зробити за допомогою функції СЕРЕДНЯ (це розглянуто далі в цьому підручнику).

Зважена ковзаюча середня (WMA)

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

Температура 10-го дня, швидше за все, буде кращим показником тенденції порівняно з 1-м днем ​​(оскільки температура падає з кожним днем).

Отже, нам буде краще, якщо ми більше будемо покладатися на цінність 10 -го дня.

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

Це називається зваженою ковзною середньою.

Експоненціальна ковзна середня (EMA)

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

Його також називають Експоненціальною зваженою ковзкою середньою (EWMA)

Різниця між WMA та EMA полягає в тому, що за допомогою WMA ви можете призначати ваги на основі будь -яких критеріїв. Наприклад, у 3-бальній ковзній середній ви можете призначити 60% вагового віку для останньої точки даних, 30% для середньої точки даних і 10% для найстарішої точки даних.

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

Досить лекції статистики.

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

Розрахунок простої ковзної середньої (SMA) за допомогою пакета інструментів аналізу даних у Excel

Microsoft Excel вже має вбудований інструмент для розрахунку простих ковзних середніх.

Це називається Пакет інструментів аналізу даних.

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

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

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

1. Перейдіть на вкладку Файл
2. Натисніть Параметри
3. У діалоговому вікні Параметри Excel натисніть Надбудови
4. Унизу діалогового вікна виберіть у спадному меню надбудови Excel, а потім натисніть «Перейти».
5. У діалоговому вікні Надбудови, що відкриється, перевірте параметр Панель інструментів аналізу
6. Натисніть OK.

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

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

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

1. Перейдіть на вкладку Дані
2. Натисніть на опцію Аналіз даних
3. У діалоговому вікні "Аналіз даних" натисніть на параметр "Ковзаюча середня" (можливо, вам доведеться трохи прокрутити сторінку, щоб її досягти).
4. Натисніть OK. Відкриється діалогове вікно "Ковзаюча середня".
5. У діапазоні введення виберіть дані, для яких потрібно обчислити ковзну середню (B2: B11 у цьому прикладі)
6. У опції Інтервал введіть 3 (оскільки ми обчислюємо триточкову ковзну середню)
7. У діапазоні Виведення введіть комірку, у якій потрібно отримати результати. У цьому прикладі я використовую С2 як вихідний діапазон
8. Натисніть OK

Наведені вище кроки дадуть вам результат ковзаючої середньої, як показано нижче.

Зауважте, що перші дві клітинки у стовпці C мають результат як помилку #N/A. Це тому, що це триконтактна ковзаюча середня і для отримання першого результату потрібні щонайменше три точки даних. Отже, фактичні значення ковзної середньої починаються після третьої точки даних і далі.

Ви також помітите, що все, що було зроблено в цьому наборі інструментів аналізу даних, застосовується до клітинок середньої формули. Тому, якщо ви хочете зробити це вручну без набору інструментів «Аналіз даних», ви, звичайно, можете це зробити.

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

Розрахунок ковзних середніх (SMA, WMA, EMA) за допомогою формул у Excel

Ви також можете обчислити ковзаючі середні за формулою СЕРЕДНЯ.

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

Крім того, Пакет інструментів аналізу даних дає лише просту ковзну середню (SMA), але якщо ви хочете обчислити WMA або EMA, вам потрібно покладатися лише на формули.

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

Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити триточкову SMA:

У клітинці С4 введіть таку формулу:

= СЕРЕДНЯ (B2: B4)

Скопіюйте цю формулу для всіх клітинок, і вона надасть вам SMA на кожен день.

Пам’ятайте: під час обчислення SMA за формулами потрібно переконатися, що посилання у формулі є відносними. Це означає, що формула може бути = СЕРЕДНЯ (B2: B4) або = СЕРЕДНЯ ($ B2: $ B4), але вона не може бути = СЕРЕДНЯ ($ B $ 2: $ B $ 4) або = СЕРЕДНЯ (B $ 2: B $ 4) ). Частина номера рядка посилання має бути без знака долара. Детальніше про абсолютні та відносні посилання можна прочитати тут.

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

Обчислення зваженої ковзної середньої за допомогою формул

Для WMA вам потрібно знати ваги, які призначатимуться значенням.

Наприклад, припустимо, що вам потрібно розрахувати 3 -бальний WMA для наведеного нижче набору даних, де 60% ваги надається останньому значенню, 30% - значенню перед ним і 10% - значенню перед ним.

Для цього введіть таку формулу в клітинку С4 і скопіюйте для всіх клітинок.

= 0,6*В4+0,3*В3+0,1*В2

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

Обчислення експоненціальної ковзної середньої за допомогою формул

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

Нижче наведена формула для розрахунку EMA для триточкової ковзної середньої:

EMA = [Останнє значення - попереднє значення EMA] * (2 / N + 1) + Попереднє EMA

… Де N у цьому прикладі буде 3 (оскільки ми обчислюємо триточкову EMA)

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

Припустимо, у вас є набір даних нижче, і ви хочете обчислити триперіодну EMA:

У комірку С2 введіть те саме значення, що і в В2. Це тому, що немає попереднього значення для розрахунку EMA.

У клітинці C3 введіть формулу нижче та скопіюйте для всіх клітинок:

= (B3-C2)*(2/4)+C2

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

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

Додавання лінії ковзної середньої тенденції до стовпчастої діаграми

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

Припустимо, у вас є набір даних, як показано нижче:

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

1. Виберіть набір даних (включаючи заголовки)
2. Перейдіть на вкладку Вставка
3. У групі Діаграма натисніть значок "Вставити стовпець або стовпчасту діаграму".
4. Натисніть на опцію «Кластерна колонна діаграма». Це додасть діаграму до робочого аркуша.
5. Вибравши діаграму, клацніть на вкладці "Дизайн" (ця вкладка з'являється лише тоді, коли діаграму вибрано)
6. У групі Макети діаграм натисніть «Додати елемент діаграми».
7. Наведіть курсор на опцію "Лінія тренда", а потім натисніть "Більше параметрів лінії тренду"
8. На панелі «Формат лінії тренда» виберіть опцію «Ковзаюча середня» та встановіть кількість періодів.

Це воно! Наведені вище кроки додадуть до стовпчастої діаграми рухому лінію тренду.

Якщо ви хочете вставити більше однієї лінії тенденції ковзаючої середньої (наприклад, одну за 2 періоди та одну за 3 періоди), повторіть кроки з 5 по 8).

Ви також можете використовувати ті ж кроки, щоб вставити лінію ковзної середньої тенденції до лінійного діаграму.

Форматування лінії тенденції ковзаючої середньої

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

Кілька речей, які ви можете відформатувати в лінії тренда, включають:

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

Щоб відформатувати лінію тренда ковзної середньої, клацніть по ній правою кнопкою миші та виберіть параметр Формат лінії тренда.

Це відкриє панель «Формат лінії тренда» праворуч. Ця панель містить усі параметри форматування (у різних розділах - Заповнення та лінія, Ефекти та Параметри лінії тренду).