Media mobilă înseamnă că calculăm media mediilor setului de date pe care le avem, în Excel avem o caracteristică încorporată pentru calcularea mediei mobile care este disponibilă în fila de analiză a datelor din secțiunea de analiză, necesită un interval de intrare și ieșire gama cu intervale ca ieșire, calculele bazate pe simple formule în Excel pentru a calcula media mobilă sunt dificile, dar avem o funcție încorporată în Excel pentru a face acest lucru.
Ce este media mobilă în Excel
Media mobilă este o tehnică utilizată pe scară largă în analiza seriilor de timp care este utilizată pentru a prezice viitorul. Mediile mobile dintr-o serie de timp sunt construite practic luând medii ale diferitelor valori secvențiale ale unei alte date de serie temporală.
Există trei tipuri de medii mobile, și anume medie mobilă simplă, medie mobilă ponderată și medie mobilă exponențială în excel.
# 1 - Media mobilă simplă în Excel
O medie mobilă simplă ajută la calcularea mediei ultimului număr de perioade dintr-o serie de date. Să presupunem că sunt date prețuri de n perioadă. Apoi, media mobilă simplă este dată ca
Medie mobilă simplă = (P1 + P2 + …. + Pn) / n
# 2 - Media mobilă ponderată în Excel
Media mobilă ponderată oferă media ponderată a ultimelor n perioade. Ponderarea scade cu fiecare punct de date din perioada de timp anterioară.
Media mobilă ponderată = (Preț * factor de ponderare) + (Prețul perioadei anterioare * factor de ponderare-1)
# 3 - Media mobilă exponențială în Excel
Este similar cu o medie mobilă simplă care măsoară tendințele pe o perioadă de timp. În timp ce media mobilă simplă calculează o medie a datelor date, media mobilă exponențială acordă mai multă greutate datelor curente.
Media mobilă exponențială = (K x (C - P)) + P
Unde,
- K = constantă de netezire exponențială
- C = preț curent
- P = medie mobilă exponențială a perioadelor anterioare (medie mobilă simplă utilizată pentru calculul primelor perioade)
Cum se calculează media mobilă în Excel?
Mai jos sunt exemplele unei medii mobile în Excel.
Exemplul # 1 - Media mobilă simplă în Excel
Pentru calcularea mediei mobile simple, am luat datele despre vânzări ale unei companii din ianuarie până în decembrie pentru anul 2018. Obiectivul nostru este de a netezi datele și de a cunoaște cifra vânzărilor în ianuarie 2019. Vom folosi aici media mobilă de trei luni.
Pasul 1: media mobilă din ianuarie, februarie și martie este calculată luând suma cifrei vânzărilor lunilor și apoi împărțind-o la 3.
Pasul 2: Selectarea în colțul celulei D5 și apoi doar glisarea și scăderea în jos va da media mobilă pentru perioadele rămase. Aceasta este funcția instrumentului de umplere excel.
Predicția vânzărilor pentru ianuarie 2019 este de 10456,66667.
Pasul 3: Acum, reprezentăm cifra vânzărilor și media mobilă în graficul liniar pentru a înțelege diferența de tendință. Acest lucru se poate face din fila insert. În primul rând, am selectat seria de date și apoi, din secțiunea Diagramele de sub inserție, am folosit graficul liniar.
După crearea graficelor, se poate observa că graficul cu media mobilă este mult mai netezit decât seria de date inițială.
Exemplul # 2 - Medie mobilă simplă prin fila Analiza datelor în Excel
- În fila Date din grupul Analiză, trebuie să facem clic pe Analiza datelor. Următoarea este captura de ecran.
- Din analiza datelor, media mobilă poate fi accesată.
- După ce ați făcut clic pe media mobilă, am selectat cifra de vânzări ca interval de intrare.
- Se face clic pe etichetele din primul rând pentru a face Excel să înțeleagă că primul rând are numele etichetei.
- Intervalul 3 este selectat deoarece vrem o medie mobilă de trei ani.
- Am selectat gama de ieșiri cu adiacență la cifra de vânzări.
- De asemenea, dorim să vedem ieșirea graficului, în care vom putea vedea diferența dintre efectiv și prognozat.
Acest grafic arată diferența dintre media mobilă reală și cea prognozată.
Exemplul # 3 - Media mobilă ponderată în Excel
Folosim media mobilă ponderată pe trei ani, iar formula este dată în captura de ecran.
După utilizarea formulei, am obținut media mobilă pentru o perioadă.
Am obținut media mobilă pentru toate celelalte perioade glisând și plasând valori în următoarele celule.
Prognoza pentru ianuarie 2019, adică 10718,33
Acum am luat graficul liniar pentru a vedea netezirea datelor. Pentru aceasta, am selectat luna noastră datele prognozate și apoi am inserat un grafic liniar.
Acum vom compara datele noastre prognozate cu datele noastre reale. În capturile de ecran de mai jos, putem vedea cu ușurință diferența dintre datele reale și datele prognozate. Graficul din partea de sus reprezintă datele reale, iar graficul de mai jos este media mobilă și datele prognozate. Putem vedea că graficul mediu mobil s-a netezit semnificativ în comparație cu graficul care conține datele reale.
Exemplul # 4 - Media mobilă exponențială în Excel
Formula pentru media mobilă exponențială este S t = α.Y t-1 + (1- α) S t-1 … (1)
Unde,
- Y t-1 = observație efectivă în perioada t-1
- S t-1 = medie mobilă simplă în perioada t-1
- α = factor de netezire și variază între .1 și .3. Cu cât valoarea lui α este mai apropiată de diagramă față de valorile reale și cu cât valoarea α este mai mică, cu atât diagrama va fi mai netedă.
În primul rând, calculăm media mobilă simplă, așa cum se arată mai devreme. După aceea, aplicăm formula dată în ecuația (1). Pentru fixarea valorii α pentru toate valorile următoare, am apăsat F4.
Obținem valorile prin glisare și plasare în următoarele celule.
Acum, vrem să vedem comparația dintre valorile reale, media mobilă simplă și media mobilă exponențială în excel. Am făcut acest lucru făcând o diagramă liniară.
Din captura de ecran de mai sus, putem vedea diferența dintre cifra reală de vânzări, media mobilă simplă și media mobilă exponențială în excel.
Lucruri de reținut despre media mobilă în Excel
- Media mobilă simplă poate fi calculată utilizând o funcție MEDIE în excel
- Media mobilă ajută la netezirea datelor
- Mediile sezoniere sunt adesea denumite indici sezonieri
- Media mobilă exponențială în Excel dă mai multă greutate datelor recente decât media mobilă simplă. Prin urmare, netezirea în cazul mediei mobile exponențiale în excel este mai mult decât cea a mediei mobile simple.
- În afaceri precum bursa, media mobilă îl ajută pe comerciant să identifice mai ușor tendința.
