Израчунавање покретног просека у Екцелу (једноставно, пондерисано и експоненцијално)

Као и многи моји Екцел водичи, и овај је инспирисан једним од упита које сам добио од пријатеља. Желела је да израчуна покретни просек у Екцелу, а ја сам је замолио да је потражи на интернету (или погледа ИоуТубе видео о томе).

Али онда сам одлучио да га напишем сам (чињеница да сам помало био статистички штребер на факултету такође је играла мању улогу).

Сада, пре него што вам кажем како да израчунате покретни просек у Екцелу, дозволите ми да вам брзо дам преглед шта значи покретни просек и које врсте покретних просека постоје.

У случају да желите да пређете на део где показујем како се израчунава покретни просек у Екцелу, кликните овде.

Напомена: Нисам стручњак за статистику и моја намера у овом водичу није да покријем све о покретним просецима. Желим само да вам покажем како израчунати покретне просеке у Екцелу (са кратким уводом у то шта значе покретни просеци).

Шта је покретни просек?

Сигуран сам да знате која је просечна вредност.

Ако имам податке о дневној температури за три дана, лако можете да ми кажете просек за последња три дана (наговештај: за то можете користити функцију СРЕДЊЕ у Екцелу).

Покретни просек (који се назива и покретни просек или покретни просек) је када временски период просека не мењате, али настављате да се крећете како се додају нови подаци.

На пример, 3. дана, ако вас питам тродневну покретну просечну температуру, даћете ми просечну вредност температуре за 1., 2. и 3. дан. А ако вас 4. дана питам за 3-дневну покретну просечну температуру , даћете ми просек 2., 3. и 4. дана.

Како се додају нови подаци, временски период (3 дана) остаје исти, али за израчунавање покретног просека користите најновије податке.

Покретни просјек се у великој мјери користи за техничке анализе, а многе банке и аналитичари берзе га користе свакодневно (испод је примјер који сам добио са веб странице Маркет Реалист).

Једна од предности коришћења покретних просека је та што вам даје тренд, као и у одређеној мери ублажава флуктуације. На пример, у случају да је дан заиста врућ, тродневни покретни просек температуре и даље ће се побринути да је просечна вредност изглађена (уместо да вам покаже заиста високу вредност која би могла бити изванредна- једно- ван инстанце).

Врсте покретних просека

Постоје три врсте покретних просека:

• Једноставан покретни просек (СМА)
• Пондерисани покретни просек (ВМА)
• Експоненцијални покретни просек (ЕМА)

Једноставни покретни просек (СМА)

Ово је једноставан просек тачака података у датом трајању.

У нашем примеру дневне температуре, када једноставно узмете просек последњих 10 дана, он даје 10-дневни једноставан покретни просек.

То се може постићи усредњавањем тачака података у датом трајању. У Екцелу то можете лако учинити помоћу функције ПРОСЈЕК (ово ће бити обрађено касније у овом водичу).

Пондерисани покретни просек (ВМА)

Рецимо да време постаје све хладније са сваким даном и да користите 10-дневни покретни просек да бисте добили тренд температуре.

Температура 10. дана ће вероватно бити бољи показатељ тренда у поређењу са 1. даном (пошто температура опада сваким даном).

Дакле, боље нам је ако се више ослањамо на вредност 10. дана.

Како би се то одразило на наш покретни просјек, можете дати већу тежину најновијим подацима, а мање прошлим. На овај начин и даље добијате тренд, али са већим утицајем најновијих података.

Ово се назива пондерисани покретни просек.

Експоненцијални покретни просек (ЕМА)

Експоненцијални покретни просек је врста пондерисаног покретног просека где се последњим подацима даје већа тежина и експоненцијално опада за старије тачке података.

Такође се назива и експоненцијално пондерисани покретни просек (ЕВМА)

Разлика између ВМА и ЕМА је у томе што са ВМА можете доделити тежине на основу било ког критеријума. На пример, у покретном просеку од 3 тачке последњој тачки података можете доделити 60% старости, 30% средњој тачки података и 10% најстаријој тачки података.

У ЕМА -и се последњој вредности даје већа тежина, а тежина се константно смањује експоненцијално за раније вредности.

Доста је било предавања о статистици.

Хајдемо сада заронити и видети како израчунати покретне просеке у Екцелу.

Израчунавање једноставног покретног просека (СМА) помоћу алата за анализу података у Екцелу

Мицрософт Екцел већ има уграђен алат за израчунавање једноставних покретних просека.

Зове се Пакет алата за анализу података.

Пре него што будете могли да користите пакет алата за анализу података, прво морате да проверите да ли га имате на Екцел траци или не. Постоји велика шанса да морате да предузмете неколико корака да бисте је прво омогућили.

У случају да већ имате опцију Анализа података на картици Подаци, прескочите доње кораке и погледајте кораке за израчунавање покретних просека.

Кликните на картицу Подаци и проверите да ли видите опцију Анализа података или не. Ако га не видите, следите доле наведене кораке да бисте га учинили доступним на траци.

1. Кликните на картицу Датотека
2. Кликните на Опције
3. У дијалогу Опције програма Екцел кликните на Додаци
4. При дну дијалога изаберите Екцел програмске додатке у падајућем менију, а затим кликните на Иди.
5. У дијалогу Додаци који се отвара, проверите опцију Алатка за анализу опцију
6. Притисните ОК.

Горе наведени кораци омогућили би пакет алата за анализу података и ову опцију ћете сада видети на картици Подаци.

Претпоставимо да имате скуп података као што је приказано испод и желите да израчунате покретни просек последња три интервала.

У наставку су наведени кораци за коришћење анализе података за израчунавање једноставног покретног просека:

1. Кликните на картицу Подаци
2. Кликните на опцију Анализа података
3. У дијалогу Анализа података кликните на опцију Покретни просек (можда ћете морати мало да се померите да бисте дошли до ње).
4. Притисните ОК. Ово ће отворити дијалошки оквир „Покретни просек“.
5. У опсегу уноса изаберите податке за које желите да израчунате покретни просек (Б2: Б11 у овом примеру)
6. У опцију Интервал унесите 3 (јер рачунамо покретни просек у три тачке)
7. У распону излазних података унесите ћелију у коју желите резултате. У овом примеру користим Ц2 као излазни опсег
8. Притисните ОК

Горе наведени кораци дали би вам резултат покретног просека као што је приказано испод.

Имајте на уму да прве две ћелије у колони Ц имају резултат као #Н/А грешка. То је зато што је то покретни просек у три тачке и потребне су му најмање три тачке података да би се дао први резултат. Дакле, стварне вредности покретних просека почињу после треће тачке података па надаље.

Такође ћете приметити да се све што је овај пакет алата за анализу података урадио примењује СРЕДЊУ формулу на ћелије. Дакле, ако то желите да урадите ручно без пакета алата за анализу података, то свакако можете учинити.

Постоји, међутим, неколико ствари које је лакше урадити помоћу алата за анализу података. На пример, ако желите да добијете стандардну вредност грешке, као и графикон покретног просека, све што треба да урадите је да означите поље за потврду и то ће бити део излаза.

Израчунавање покретних просека (СМА, ВМА, ЕМА) помоћу формула у Екцелу

Такође можете израчунати покретне просеке користећи формулу ПРОСЕДЊЕ.

У ствари, ако вам је потребна само покретна просечна вредност (а не стандардна грешка или графикон), употреба формуле може бити боља (и бржа) опција од коришћења пакета алата за анализу података.

Такође, Пакет алата за анализу података даје само једноставни помични просјек (СМА), али ако желите израчунати ВМА или ЕМА, морате се ослонити само на формуле.

Израчунавање једноставног покретног просека помоћу формула

Претпоставимо да имате скуп података као што је приказано испод и желите да израчунате СМА у 3 тачке:

У ћелију Ц4 унесите следећу формулу:

= ПРОСЕЧНО (Б2: Б4)

Копирајте ову формулу за све ћелије и она ће вам дати СМА за сваки дан.

Запамтите: Приликом израчунавања СМА помоћу формула, морате бити сигурни да су референце у формули релативне. То значи да формула може бити = просечна (Б2: Б4) или = просечна ($ Б2: $ Б4), али не може бити = просечна ($ Б $ 2: $ Б $ 4) или = просечна (Б $ 2: Б $ 4) ). Део референци са бројем реда треба да буде без предзнака долара. Више о апсолутним и релативним референцама можете прочитати овде.

Пошто рачунамо Једноставни покретни просек (СМА) у три тачке, прве две ћелије (прва два дана) су празне и формулу почињемо да користимо од трећег дана надаље. Ако желите, можете користити прве две вредности какве јесу, а вредност СМА користити од треће па надаље.

Израчунавање пондерисаног покретног просека помоћу формула

За ВМА морате знати тежине које би биле додељене вредностима.

На пример, претпоставимо да морате израчунати ВМА са 3 тачке за скуп података испод, где се 60% даје последњој вредности, 30% оној пре ње и 10% оној пре ње.

Да бисте то урадили, унесите следећу формулу у ћелију Ц4 и копирајте за све ћелије.

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

Пошто израчунавамо пондерисани покретни просек са 3 тачке (ВМА), прве две ћелије (прва два дана) су празне и формулу почињемо да користимо од трећег дана надаље. Ако желите, можете користити прве две вредности какве јесу, а вредност ВМА користити од треће па надаље.

Израчунавање експоненцијалног покретног просека помоћу формула

Експоненцијални покретни просек (ЕМА) даје већу тежину последњој вредности, а пондери настављају да се експоненцијално смањују за раније вредности.

Испод је формула за израчунавање ЕМА за покретни просек у три тачке:

ЕМА = [Најновија вредност - Претходна ЕМА вредност] * (2 / Н + 1) + Претходна ЕМА

… где би Н било 3 у овом примеру (јер рачунамо ЕМА у три тачке)

Напомена: За прву ЕМА вредност (када немате претходну вредност за израчунавање ЕМА), једноставно узмите вредност онакву каква јесте и сматрајте је ЕМА вредност. Затим можете користити ову вредност напред.

Претпоставимо да имате доњи скуп података и желите да израчунате тропериодну ЕМА:

У ћелију Ц2 унесите исту вредност као у Б2. То је зато што не постоји претходна вредност за израчунавање ЕМА.

У ћелију Ц3 унесите доњу формулу и копирајте за све ћелије:

= (Б3-Ц2)*(2/4)+Ц2

У овом примеру сам поједноставио и користио сам најновију вредност и претходну вредност ЕМА за израчунавање тренутне ЕМА.

Други популаран начин за то је прво израчунавање једноставног покретног просека, а затим га користите уместо стварне најновије вредности.

Додавање покретне просечне линије тренда у колонски графикон

Ако имате скуп података и помоћу њега креирате тракасти графикон, такође можете додати покретну линију тренда са неколико кликова.

Претпоставимо да имате скуп података као што је приказано испод:

У наставку су наведени кораци за креирање тракастог графикона помоћу ових података и додавање троделне покретне просечне линије тренда овом графикону:

1. Изаберите скуп података (укључујући заглавља)
2. Кликните на картицу Уметни
3. У групи Графикон кликните на икону „Уметни колону или тракасти графикон“.
4. Кликните на опцију графикона груписане колоне. Ово ће уметнути графикон у радни лист.
5. Када је графикон изабран, кликните на картицу Дизајн (ова картица се појављује само када је графикон одабран)
6. У групи Распореди графикона кликните на „Додај елемент графикона“.
7. Задржите показивач на опцији „Трендлине“, а затим кликните на „Море Трендлине Оптионс“
8. У окну Обликовање линије тренда изаберите опцију „Покретни просек“ и поставите број тачака.

То је то! Горе наведени кораци додали би покретну линију тренда вашем графикону колона.

У случају да желите да уметнете више од једне линије покретног просека тренда (на пример једну за 2 периода и једну за 3 периода), поновите кораке од 5 до 8).

Исте кораке можете користити за уметање покретне просечне линије тренда у линијски графикон.

Форматирање линије покретног просека

За разлику од обичног линијског графикона, линија трендова покретног просека не дозвољава много обликовања. На пример, ако желите да истакнете одређену тачку података на линији тренда, то нећете моћи да урадите.

Неколико ствари које можете обликовати у тренду су:

• Боја линије. Ово можете користити за истицање једне од линија тренда тако што ћете све на графикону учинити светлом у боји и учинити да линија тренда искаче јарком бојом
• Тхе дебљине линије
• Тхе транспарентност линије

Да бисте форматирали покретну линију тренда, кликните десним тастером миша на њу, а затим изаберите опцију Формат Трендлине.

Ово ће отворити окно Обликовање линије тренда са десне стране. Ово окно као и све опције обликовања (у различитим одељцима - Филл & Лине, Еффецтс и Трендлине Оптионс).