Подобно на много от моите уроци по Excel, и този е вдъхновен от едно от запитванията, които получих от приятел. Тя искаше да изчисли пълзящата средна стойност в Excel и аз я помолих да я потърси онлайн (или да изгледа видеоклип в YouTube за това).
Но след това реших сам да напиша (фактът, че бях донякъде като статистически глупак в колежа също играеше второстепенна роля).
Сега, преди да ви кажа как да изчислявате пълзяща средна в Excel, позволете ми бързо да ви дам преглед на това какво означава пълзяща средна и какви видове пълзящи средни има.
В случай, че искате да преминете към частта, където показвам как се изчислява пълзяща средна в Excel, щракнете тук.
Забележка: Не съм експерт по статистика и намерението ми в този урок не е да обхващам всичко за пълзящите средни. Имам само за цел да ви покажа как да изчислявате пълзящи средни в Excel (с кратко въведение за това какво означават пълзящите средни).Какво е плъзгаща средна?
Сигурен съм, че знаете каква е средната стойност.
Ако имам три дни дневни данни за температурата, можете лесно да ми кажете средната стойност за последните три дни (намек: можете да използвате функцията AVERAGE в Excel, за да направите това).
Плъзгаща средна (наричана още подвижна средна или текуща средна) е, когато запазвате периода на средната стойност същия, но продължава да се движи, когато се добавят нови данни.
Например, на 3-ти ден, ако ви попитам 3-дневната плъзгаща се средна температура, вие ще ми дадете средната температурна стойност на 1, 2 и 3. Ден. И ако на 4-ия ден ви попитам 3-дневната пълзяща средна температура , ще ми дадете средната стойност за Ден 2, 3 и 4.
Когато се добавят нови данни, вие запазвате периода от време (3 дни) същият, но използвате най -новите данни, за да изчислите пълзящата средна.
Плъзгащата средна се използва силно за технически анализ и много банки и анализатори на фондовите пазари я използват ежедневно (по-долу е пример, който получих от сайта Market Realist).
Видове пълзящи средни
Има три вида пълзящи средни:
- Проста пълзяща средна (SMA)
- Претеглена пълзяща средна (WMA)
- Експоненциална пълзяща средна (EMA)
Проста пълзяща средна (SMA)
Това е простата средна стойност на данните в дадената продължителност.
В нашия пример за дневна температура, когато просто вземете средно за последните 10 дни, той дава 10-дневната проста пълзяща средна.
Това може да се постигне чрез усредняване на точките от данни за дадената продължителност. В Excel можете да направите това лесно, като използвате функцията AVERAGE (това е разгледано по -късно в този урок).
Претеглена пълзяща средна (WMA)
Да кажем, че времето става все по-хладно с всеки изминал ден и вие използвате 10-дневна плъзгаща се средна, за да получите температурната тенденция.
Температурата на ден 10 е по-вероятно да бъде по-добър индикатор за тенденцията в сравнение с ден 1 (тъй като температурата намалява с всеки изминал ден).
Така че, ние сме по -добре, ако разчитаме повече на стойността на Ден 10.
За да отразите това в нашата пълзяща средна стойност, можете да придадете по -голяма тежест на най -новите данни и по -малко на минали данни. По този начин все още получавате тенденцията, но с по -голямо влияние на най -новите данни.
Това се нарича претеглена плъзгаща средна.
Експоненциална пълзяща средна (EMA)
Експоненциалната плъзгаща средна е вид претеглена плъзгаща средна, при която най -новите данни се придават по -голяма тежест и намалява експоненциално за по -старите точки от данни.
Нарича се още експоненциално претеглена плъзгаща средна (EWMA)
Разликата между WMA и EMA е, че с WMA можете да задавате тегла въз основа на всякакви критерии. Например при 3-точкова плъзгаща се средна стойност можете да присвоите 60% тегло на най-новата точка на данни, 30% на средната точка на данни и 10% на най-старата точка на данни.
В EMA по -висока тежест се дава на последната стойност и теглото продължава да намалява експоненциално за по -ранните стойности.
Стига лекция по статистика.
Сега нека се потопим и да видим как да изчислим пълзящи средни в Excel.
Изчисляване на проста плъзгаща се средна (SMA) с помощта на Пакет от инструменти за анализ на данни в Excel
Microsoft Excel вече има вграден инструмент за изчисляване на простите пълзящи средни.
Нарича се Пакет от инструменти за анализ на данни.
Преди да можете да използвате инструментариума за анализ на данни, първо трябва да проверите дали го имате в лентата на Excel или не. Има голям шанс да направите няколко стъпки, за да го активирате първо.
В случай, че вече имате опцията Анализ на данни в раздела Данни, пропуснете стъпките по -долу и вижте стъпките за изчисляване на пълзящи средни.Кликнете върху раздела Данни и проверете дали виждате опцията Анализ на данни или не. Ако не го виждате, следвайте стъпките по -долу, за да го направите наличен в лентата.
- Щракнете върху раздела Файл
- Кликнете върху Опции
- В диалоговия прозорец Опции на Excel щракнете върху Добавки
- В долната част на диалоговия прозорец изберете добавките на Excel в падащото меню и след това щракнете върху Напред.
- В диалоговия прозорец Добавки, който се отваря, проверете опцията Пакет инструменти за анализ
- Щракнете върху OK.
Горните стъпки ще активират пакета с инструменти за анализ на данни и сега ще видите тази опция в раздела Данни.
Да предположим, че имате набора от данни, както е показано по -долу и искате да изчислите пълзящата средна стойност на последните три интервала.
По -долу са стъпките за използване на Анализ на данни за изчисляване на обикновена пълзяща средна:
- Щракнете върху раздела Данни
- Кликнете върху опцията Анализ на данни
- В диалоговия прозорец Анализ на данни щракнете върху опцията Подвижна средна (може да се наложи да превъртите малко, за да я достигнете).
- Щракнете върху OK. Това ще отвори диалоговия прозорец „Подвижна средна“.
- В Input Range изберете данните, за които искате да изчислите пълзящата средна (B2: B11 в този пример)
- В опцията Интервал въведете 3 (тъй като изчисляваме три точкова плъзгаща се средна)
- В диапазона Изход въведете клетката, където искате резултатите. В този пример използвам C2 като изходен диапазон
- Щракнете върху OK
Горните стъпки биха ви дали резултат от плъзгащата средна стойност, както е показано по -долу.
Обърнете внимание, че първите две клетки в колона C имат резултат като грешка #N/A. Това е така, защото това е три точкова плъзгаща се средна и се нуждае от поне три точки от данни, за да даде първия резултат. Така че действителните стойности на пълзящата средна стойност започват след третата точка от данните нататък.
Ще забележите също, че всичко, което е направил този набор от инструменти за анализ на данни, е приложено СРЕДНА формула към клетките. Така че, ако искате да направите това ръчно без пакета с инструменти за анализ на данни, със сигурност можете да направите това.
Има обаче няколко неща, които са по -лесни за правене с инструментариума за анализ на данни. Например, ако искате да получите стандартната стойност на грешката, както и диаграмата на пълзящата средна, всичко, което трябва да направите, е да поставите отметка в квадратче и тя ще бъде част от изхода.
Изчисляване на пълзящи средни (SMA, WMA, EMA) с помощта на формули в Excel
Можете също така да изчислите пълзящите средни, като използвате формулата AVERAGE.
Всъщност, ако всичко, от което се нуждаете, е плъзгащата средна стойност (а не стандартната грешка или диаграма), използването на формула може да бъде по -добра (и по -бърза) опция от използването на инструмента за анализ на данни.
Също така, Пакетът от инструменти за анализ на данни дава само простата пълзяща средна (SMA), но ако искате да изчислите WMA или EMA, трябва да разчитате само на формули.
Изчисляване на проста пълзяща средна с помощта на формули
Да предположим, че имате набора от данни, както е показано по-долу, и искате да изчислите 3-точков SMA:
В клетката C4 въведете следната формула:
= СРЕДНА (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% на тази преди нея.
За да направите това, въведете следната формула в клетка C4 и копирайте за всички клетки.
= 0,6*B4+0,3*B3+0,1*B2
Тъй като изчисляваме 3-точкова претеглена подвижна средна (WMA), първите две клетки (за първите два дни) са празни и започваме да използваме формулата от третия ден нататък. Ако искате, можете да използвате първите две стойности както е и да използвате стойността на WMA от третата нататък.
Изчисляване на експоненциална подвижна средна с помощта на формули
Експоненциалната подвижна средна (EMA) дава по -висока тежест на най -новата стойност, а теглата продължават да намаляват експоненциално за по -ранните стойности.
По-долу е формулата за изчисляване на EMA за три точкова плъзгаща се средна:
EMA = [Последна стойност - предишна EMA стойност] * (2 / N + 1) + предишна EMA
… където N би било 3 в този пример (тъй като ние изчисляваме триточкова EMA)
Забележка: За първата EMA стойност (когато нямате предишна стойност за изчисляване на EMA), просто вземете стойността такава, каквато е и я считайте за EMA стойност. След това можете да използвате тази стойност занапред.Да предположим, че имате следния набор от данни и искате да изчислите трипериодната EMA:
В клетка C2 въведете същата стойност като в B2. Това е така, защото няма предишна стойност за изчисляване на EMA.
В клетка C3 въведете формулата по -долу и копирайте за всички клетки:
= (B3-C2)*(2/4)+C2
В този пример съм го опростил и използвах последната стойност и предишната стойност на EMA, за да изчисля текущата EMA.
Друг популярен начин да направите това е като първо изчислите простата пълзяща средна и след това я използвате вместо действителната последна стойност.
Добавяне на подвижна средна линия на тенденция към колонна диаграма
Ако имате набор от данни и създавате лентова диаграма, използвайки го, можете също да добавите линията на тренда с плъзгаща се средна стойност с няколко кликвания.
Да предположим, че имате набор от данни, както е показано по -долу:
По-долу са посочени стъпките за създаване на лентова диаграма с помощта на тези данни и добавяне на тройна линия на трендовата плъзгаща се средна към тази диаграма:
- Изберете набора от данни (включително заглавките)
- Щракнете върху раздела Вмъкване
- В групата Диаграма кликнете върху иконата „Вмъкване на колона или стълбовидна диаграма“.
- Щракнете върху опцията Диаграма на групирана колона. Това ще вмъкне диаграмата в работния лист.
- Когато диаграмата е избрана, щракнете върху раздела Дизайн (този раздел се появява само когато диаграмата е избрана)
- В групата Оформления на диаграми кликнете върху „Добавяне на елемент на диаграма“.
- Задръжте курсора на мишката върху опцията „Trendline“ и след това щракнете върху „More Trendline Options“
- В прозореца Форматиране на тенденция изберете опцията „Плъзгаща средна“ и задайте броя на периодите.
Това е! Горните стъпки биха добавили подвижна линия на тренда към колоновата ви диаграма.
В случай, че искате да вмъкнете повече от една линия на тенденция на движеща се средна (например една за 2 периода и една за 3 периода), повторете стъпките от 5 до 8).
Можете да използвате същите стъпки, за да вмъкнете и линия на тренда с плъзгаща се средна стойност в линейна диаграма.
Форматиране на линията на тренда на плъзгащата средна
За разлика от обикновената линейна диаграма, движещата се средна линия на тренда не позволява много форматиране. Например, ако искате да маркирате конкретна точка от данни на линията на тренда, няма да можете да направите това.
Няколко неща, които можете да форматирате в линията на тренда, включват:
- Цвят от линията. Можете да използвате това, за да подчертаете една от линиите на тенденцията, като направите всичко в диаграмата светло на цвят и направете линията на тенденцията изскачаща с ярък цвят
- The дебелина от линията
- The прозрачност от линията
За да форматирате плъзгащата средна линия на тренда, щракнете с десния бутон върху нея и след това изберете опцията Форматиране на линията на тенденцията.
Това ще отвори прозореца Format Trendline вдясно. Този прозорец като всички опции за форматиране (в различни раздели - Fill & Line, Effects и Trendline Options).
