La fel ca multe dintre tutorialele mele Excel, și acesta este inspirat de una dintre întrebările pe care le-am primit de la un prieten. A vrut să calculeze media mobilă în Excel și i-am cerut să o caute online (sau să urmărească un videoclip YouTube despre asta).
Dar apoi, am decis să scriu unul singur (faptul că eram oarecum un tocilar de statistici la facultate a jucat, de asemenea, un rol minor).
Acum, înainte să vă spun cum să calculați media mobilă în Excel, permiteți-mi să vă dau rapid o privire de ansamblu asupra a ceea ce înseamnă media mobilă și ce tipuri de medii mobile există.
În cazul în care doriți să treceți la partea în care vă arăt cum să calculez media mobilă în Excel, faceți clic aici.
Notă: Nu sunt expert în statistici și intenția mea în acest tutorial nu este să acoperi totul despre mediile mobile. Îmi propun doar să vă arăt cum să calculați mediile mobile în Excel (cu o scurtă introducere a ceea ce înseamnă mediile mobile).Ce este o medie mobilă?
Sunt sigur că știți care este valoarea medie.
Dacă am trei zile de date zilnice de temperatură, îmi puteți spune cu ușurință media ultimelor trei zile (indiciu: puteți utiliza funcția MEDIE în Excel pentru a face acest lucru).
O medie mobilă (numită și medie rulantă sau medie de rulare) este atunci când păstrați aceeași perioadă de timp a mediei, dar continuă să se deplaseze pe măsură ce se adaugă date noi.
De exemplu, în ziua 3, dacă îți cer temperatura medie mobilă de 3 zile, îmi vei da valoarea medie a temperaturii zilelor 1, 2 și 3. Și dacă în ziua 4 îți cer temperatura medie mobilă de 3 zile , îmi veți da media Zilei 2, 3 și 4.
Pe măsură ce se adaugă date noi, păstrați aceeași perioadă de timp (3 zile), dar utilizați cele mai recente date pentru a calcula media mobilă.
Media mobilă este puternic utilizată pentru analize tehnice și o mulțime de bănci și analiști bursieri o folosesc zilnic (mai jos este un exemplu pe care l-am obținut de pe site-ul Market Realist).
Tipuri de medii mobile
Există trei tipuri de medii mobile:
- Medie mobilă simplă (SMA)
- Media mobilă ponderată (WMA)
- Media mobilă exponențială (EMA)
Medie mobilă simplă (SMA)
Aceasta este media simplă a punctelor de date în durata dată.
În exemplul nostru zilnic de temperatură, când pur și simplu luați o medie din ultimele 10 zile, aceasta oferă media mobilă simplă de 10 zile.
Acest lucru poate fi realizat prin media punctelor de date în durata dată. În Excel, puteți face acest lucru cu ușurință utilizând funcția MEDIE (acest lucru este tratat mai târziu în acest tutorial).
Media mobilă ponderată (WMA)
Să presupunem că vremea devine mai rece cu fiecare zi care trece și că utilizați o medie mobilă de 10 zile pentru a obține tendința de temperatură.
Temperatura zilei 10 este mai probabil să fie un indicator mai bun al tendinței în comparație cu ziua 1 (deoarece temperatura scade cu fiecare zi care trece).
Deci, suntem mai bine dacă ne bazăm mai mult pe valoarea Zilei 10.
Pentru ca acest lucru să se reflecte în media noastră mobilă, puteți acorda mai multă importanță ultimelor date și mai puțin datelor din trecut. În acest fel, veți obține în continuare tendința, dar cu o mai mare influență a ultimelor date.
Aceasta se numește media mobilă ponderată.
Media mobilă exponențială (EMA)
Media mobilă exponențială este un tip de medie mobilă ponderată în care se acordă mai multă greutate ultimelor date și scade exponențial pentru punctele de date mai vechi.
Se mai numește și media mobilă ponderată exponențială (EWMA)
Diferența dintre WMA și EMA este că, cu WMA, puteți atribui greutăți pe baza oricăror criterii. De exemplu, într-o medie mobilă cu 3 puncte, puteți atribui o vârstă de greutate de 60% ultimului punct de date, 30% punctului central de date și 10% celui mai vechi punct de date.
În EMA, o greutate mai mare este dată celei mai recente valori, iar greutatea continuă să scadă exponențial pentru valorile anterioare.
Destul de prelegere de statistici.
Acum să ne scufundăm și să vedem cum să calculăm mediile mobile în Excel.
Calculul mediei mobile simple (SMA) folosind Instrumentul de analiză a datelor în Excel
Microsoft Excel are deja un instrument încorporat pentru a calcula mediile mobile simple.
Se numește Instrument de analiză a datelor.
Înainte de a putea utiliza instrumentul Analiza datelor, trebuie mai întâi să verificați dacă îl aveți în panglica Excel sau nu. Există șanse mari să faceți câțiva pași pentru a o activa mai întâi.
În cazul în care aveți deja opțiunea Analiză date în fila Date, săriți peste pașii de mai jos și vedeți pașii privind calcularea mediilor mobile.Faceți clic pe fila Date și verificați dacă vedeți opțiunea Analiza datelor sau nu. Dacă nu o vedeți, urmați pașii de mai jos pentru a o face disponibilă în panglică.
- Faceți clic pe fila Fișier
- Faceți clic pe Opțiuni
- În caseta de dialog Opțiuni Excel, faceți clic pe Add-ins
- În partea de jos a casetei de dialog, selectați Suplimente Excel în meniul derulant și apoi faceți clic pe Accesați.
- În caseta de dialog Add-ins care se deschide, bifați opțiunea Analysis Toolpak
- Faceți clic pe OK.
Pașii de mai sus ar activa pachetul de instrumente de analiză a datelor și veți vedea acum această opțiune în fila Date.
Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să calculați media mobilă a ultimelor trei intervale.
Mai jos sunt pașii pentru a utiliza analiza datelor pentru a calcula o medie mobilă simplă:
- Faceți clic pe fila Date
- Faceți clic pe opțiunea Analiza datelor
- În caseta de dialog Analiza datelor, faceți clic pe opțiunea Mișcare mobilă (poate fi necesar să derulați puțin pentru a o atinge).
- Faceți clic pe OK. Aceasta va deschide caseta de dialog „Media mobilă”.
- În domeniul de intrare, selectați datele pentru care doriți să calculați media mobilă (B2: B11 în acest exemplu)
- În opțiunea Interval, introduceți 3 (deoarece calculăm o medie mobilă în trei puncte)
- În intervalul de ieșire, introduceți celula unde doriți rezultatele. În acest exemplu, folosesc C2 ca interval de ieșire
- Faceți clic pe OK
Pașii de mai sus vă vor oferi rezultatul mediu mobil așa cum se arată mai jos.
Rețineți că primele două celule din coloana C au rezultatul ca eroare # N / A. Acest lucru se datorează faptului că este o medie mobilă în trei puncte și are nevoie de cel puțin trei puncte de date pentru a da primul rezultat. Deci, valorile medii mobile reale încep după cel de-al treilea punct de date.
Veți observa, de asemenea, că toate instrumentele de analiză a datelor pe care le-a făcut au aplicat o formulă MEDIE la celule. Deci, dacă doriți să faceți acest lucru manual fără pachetul de instrumente pentru analiza datelor, puteți face acest lucru cu siguranță.
Cu toate acestea, există câteva lucruri care sunt mai ușor de făcut cu instrumentul de analiză a datelor. De exemplu, dacă doriți să obțineți valoarea erorii standard, precum și graficul mediei mobile, tot ce trebuie să faceți este să bifați o casetă și aceasta va face parte din ieșire.
Calculul mediilor mobile (SMA, WMA, EMA) folosind formule în Excel
De asemenea, puteți calcula mediile mobile utilizând formula MEDIE.
De fapt, dacă tot ce aveți nevoie este valoarea medie mobilă (și nu eroarea standard sau graficul), utilizarea unei formule poate fi o opțiune mai bună (și mai rapidă) decât utilizarea Instrumentului de analiză a datelor.
De asemenea, Analiza datelor Toolpak oferă doar media mobilă simplă (SMA), dar dacă doriți să calculați WMA sau EMA, trebuie să vă bazați doar pe formule.
Calculul mediei mobile simple folosind formule
Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să calculați SMA în 3 puncte:
În celula C4, introduceți următoarea formulă:
= MEDIE (B2: B4)
Copiați această formulă pentru toate celulele și vă va oferi SMA pentru fiecare zi.
Amintiți-vă: Când calculați SMA utilizând formule, trebuie să vă asigurați că referințele de pe formulă sunt relative. Aceasta înseamnă că formula poate fi = MEDIE (B2: B4) sau = MEDIE ($ B2: $ B4), dar nu poate fi = MEDIE ($ B $ 2: $ B $ 4) sau = MEDIE (B $ 2: B $ 4 ). Partea numărului rândului de referință trebuie să fie fără semnul dolar. Puteți citi mai multe despre referințe absolute și relative aici.
Deoarece calculăm o medie mobilă simplă în 3 puncte (SMA), primele două celule (pentru primele două zile) sunt goale și începem să folosim formula începând cu a treia zi. Dacă doriți, puteți utiliza primele două valori așa cum este și puteți utiliza valoarea SMA începând cu a treia.
Calculul mediei mobile ponderate folosind formule
Pentru WMA, trebuie să cunoașteți ponderile care ar fi atribuite valorilor.
De exemplu, să presupunem că trebuie să calculați WMA de 3 puncte pentru setul de date de mai jos, unde greutatea de 60% este dată celei mai recente valori, 30% celei dinaintea ei și 10% din cea dinaintea ei.
Pentru aceasta, introduceți următoarea formulă în celula C4 și copiați pentru toate celulele.
= 0,6 * B4 + 0,3 * B3 + 0,1 * B2
Deoarece calculăm o medie mobilă ponderată în 3 puncte (WMA), primele două celule (pentru primele două zile) sunt goale și începem să folosim formula începând cu a treia zi. Dacă doriți, puteți utiliza primele două valori așa cum este și puteți utiliza valoarea WMA începând cu a treia.
Calculul mediei mobile exponențiale utilizând formule
Media mobilă exponențială (EMA) conferă o pondere mai mare celei mai recente valori, iar ponderile continuă să scadă exponențial pentru valorile anterioare.
Mai jos este formula pentru a calcula EMA pentru o medie mobilă în trei puncte:
EMA = [Ultima valoare - Valoarea EMA anterioară] * (2 / N + 1) + EMA anterioară
… Unde N ar fi 3 în acest exemplu (deoarece calculăm un EMA în trei puncte)
Notă: pentru prima valoare EMA (când nu aveți nicio valoare anterioară pentru a calcula EMA), pur și simplu luați valoarea așa cum este și considerați-o valoarea EMA. Puteți folosi această valoare în continuare.Să presupunem că aveți setul de date de mai jos și doriți să calculați EMA în trei perioade:
În celula C2, introduceți aceeași valoare ca în B2. Acest lucru se datorează faptului că nu există o valoare anterioară pentru a calcula EMA.
În celula C3, introduceți formula de mai jos și copiați pentru toate celulele:
= (B3-C2) * (2/4) + C2
În acest exemplu, l-am păstrat simplu și am folosit cea mai recentă valoare și valoarea EMA anterioară pentru a calcula EMA curentă.
Un alt mod popular de a face acest lucru este calculând mai întâi media mobilă simplă și apoi folosind-o în locul celei mai recente valori reale.
Adăugarea unei linii de tendință medii mobile într-un grafic pe coloane
Dacă aveți un set de date și creați o diagramă cu bare folosindu-l, puteți adăuga, de asemenea, linia de tendință medie mobilă cu câteva clicuri.
Să presupunem că aveți un set de date așa cum se arată mai jos:
Mai jos sunt pașii pentru a crea o diagramă cu bare folosind aceste date și adăugând o linie de tendință medie în trei părți la această diagramă:
- Selectați setul de date (inclusiv antetele)
- Faceți clic pe fila Inserare
- În grupul Diagramă, faceți clic pe pictograma „Inserați o coloană sau o diagramă cu bare”.
- Faceți clic pe opțiunea Diagramă coloană grupată. Aceasta va insera graficul în foaia de lucru.
- Cu graficul selectat, faceți clic pe fila Proiectare (această filă apare numai atunci când graficul este selectat)
- În grupul Aspecte diagramă, faceți clic pe „Adăugați element diagramă”.
- Plasați cursorul pe opțiunea „Trendline” și apoi faceți clic pe „More Trendline Options”
- În panoul Formatare linie de trend, selectați opțiunea „Medie mobilă” și setați numărul de perioade.
Asta e! Pașii de mai sus ar adăuga o linie de tendință în mișcare în diagrama de coloane.
În cazul în care doriți să inserați mai multe linii de trend medii mobile (de exemplu, una pentru 2 perioade și una pentru 3 perioade), repetați pașii de la 5 la 8).
Puteți utiliza aceiași pași pentru a insera o linie de tendință medie mobilă și într-o diagramă liniară.
Formatarea liniei de tendință medie mobilă
Spre deosebire de o diagramă liniară obișnuită, o linie de tendință medie mobilă nu permite o mulțime de formatare. De exemplu, dacă doriți să evidențiați un anumit punct de date pe linia de tendință, nu veți putea face acest lucru.
Câteva lucruri pe care le puteți formata în linia de trend includ:
- Culoare a liniei. Puteți utiliza acest lucru pentru a evidenția una dintre liniile de trend făcând ca totul din diagramă să fie deschis la culoare și făcând ca linia de tendință să apară cu o culoare strălucitoare
- grosime a liniei
- transparenţă a liniei
Pentru a formata linia de tendință medie mobilă, faceți clic dreapta pe ea și apoi selectați opțiunea Formatare linie de tendință.
Aceasta va deschide panoul Format Trendline din dreapta. Acest panou conține toate opțiunile de formatare (în diferite secțiuni - Fill & Line, Effects și Trendline Options).
