rezumat
Pentru a calcula o medie mobilă sau rulantă, puteți utiliza o formulă simplă bazată pe funcția MEDIE cu referințe relative. În exemplul prezentat, formula din E7 este:
=AVERAGE(C5:C7)
Pe măsură ce formula este copiată, aceasta calculează o medie mobilă de 3 zile pe baza valorii vânzărilor pentru ziua curentă și pentru cele două zile anterioare.
Mai jos este o opțiune mai flexibilă bazată pe funcția OFFSET care gestionează perioade variabile.
Despre medii mobile
O medie mobilă (numită și medie rulantă) este o medie bazată pe subseturi de date la intervale date. Calculul unei medii la intervale specifice netezește datele prin reducerea impactului fluctuațiilor aleatorii. Acest lucru face mai ușor să vedeți tendințele generale, în special într-un grafic. Cu cât este mai mare intervalul utilizat pentru calcularea unei medii mobile, cu atât este mai netedă, deoarece sunt incluse mai multe puncte de date în fiecare medie calculată.
Explicaţie
Formulele prezentate în exemplu folosesc toate funcția MEDIE cu o referință relativă configurată pentru fiecare interval specific. Media mobilă de 3 zile din E7 este calculată prin alimentarea MEDIE a unui interval care include ziua curentă și cele două zile anterioare astfel:
=AVERAGE(C5:C7) // 3-day average
Mediile pentru 5 și 7 zile sunt calculate în același mod. În fiecare caz, intervalul furnizat pentru MEDIE este mărit pentru a include numărul necesar de zile:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Toate formulele utilizează o referință relativă pentru intervalul furnizat funcției MEDII. Pe măsură ce formulele sunt copiate în coloană, intervalul se modifică la fiecare rând pentru a include valorile necesare pentru fiecare medie.
Când valorile sunt reprezentate într-o diagramă liniară, efectul de netezire este clar:
Date insuficiente
Dacă porniți formulele din primul rând al tabelului, primele formule nu vor avea suficiente date pentru a calcula o medie completă, deoarece intervalul se va extinde deasupra primului rând de date:
Aceasta poate fi sau nu o problemă, în funcție de structura foii de lucru și dacă este important ca toate mediile să se bazeze pe același număr de valori. Funcția MEDIE va ignora automat valorile textului și celulele goale, deci va continua să calculeze o medie cu mai puține valori. Acesta este motivul pentru care „funcționează” în E5 și E6.
O modalitate de a indica în mod clar date insuficiente este de a verifica numărul rândului curent și de a anula cu #NA atunci când există mai puțin de n valori. De exemplu, pentru media de 3 zile, puteți utiliza:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Prima parte a formulei generează pur și simplu un număr de rând „normalizat”, începând cu 1:
ROW()-ROW($C$5)+1 // relative row number
În rândul 5, rezultatul este 1, în rândul 6 rezultatul este 2 și așa mai departe.
Când numărul rândului curent este mai mic de 3, formula returnează # N / A. În caz contrar, formula returnează o medie mobilă ca înainte. Aceasta imită comportamentul versiunii de analiză a instrumentului de mișcare mobilă, care generează # N / A până la atingerea primei perioade complete.
Cu toate acestea, odată cu creșterea numărului de perioade, în cele din urmă veți rămâne fără rânduri deasupra datelor și nu veți putea introduce intervalul necesar în MEDIE. De exemplu, nu puteți configura o medie mobilă de 7 zile cu foaia de lucru așa cum se arată, deoarece nu puteți introduce un interval care să se extindă cu 6 rânduri peste C5.
Perioade variabile cu OFFSET
O modalitate mai flexibilă de a calcula o medie mobilă este cu funcția OFFSET. OFFSET poate crea un interval dinamic, ceea ce înseamnă că putem configura o formulă în care numărul de perioade este variabil. Forma generală este:
=AVERAGE(OFFSET(A1,0,0,-n,1))
unde n este numărul de perioade care trebuie incluse în fiecare medie. Ca mai sus, OFFSET returnează un interval care este trecut în funcția MEDIE. Mai jos puteți vedea această formulă în acțiune, unde „n” este intervalul numit E2. Începând de la celula C5, OFFSET construiește un interval care se extinde înapoi la rândurile anterioare. Acest lucru se realizează folosind o înălțime egală cu negativul n. Când E5 este schimbat cu un alt număr, media mobilă recalculează pe toate rândurile:
Formula din E5, copiată în jos, este:
=AVERAGE(OFFSET(C5,0,0,-n,1))
La fel ca formula originală de mai sus, versiunea cu OFFSET va avea, de asemenea, problema datelor insuficiente în primele rânduri, în funcție de câte perioade sunt date în E5.
În exemplul prezentat, mediile se calculează cu succes deoarece funcția MEDIE ignoră automat valorile textului și celulele goale și nu există alte valori numerice peste C5. Deci, în timp ce intervalul trecut în MEDIU în E5 este C1: C5, există o singură valoare la medie, 100. Cu toate acestea, pe măsură ce perioadele cresc, OFFSET va continua să creeze un interval care se extinde deasupra începutului datelor, ajungând în cele din urmă la partea de sus a foii de lucru și returnarea unei erori #REF.
O soluție este de a „limita” dimensiunea intervalului la numărul de puncte de date disponibile. Acest lucru se poate face folosind funcția MIN pentru a restricționa numărul utilizat pentru înălțime, așa cum se vede mai jos:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Acest lucru pare destul de înfricoșător, dar este de fapt destul de simplu. Limităm înălțimea trecută în OFFSET cu funcția MIN:
MIN(ROW()-ROW($C$5)+1,n)
În MIN, prima valoare este un număr de rând relativ, calculat cu:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
A doua valoare dată MIN este numărul de perioade, n. Când numărul de rând relativ este mai mic decât n, MIN returnează numărul rândului curent la OFFSET pentru înălțime. Când numărul rândului este mai mare decât n, MIN returnează n. Cu alte cuvinte, MIN returnează pur și simplu cea mai mică dintre cele două valori.
O caracteristică frumoasă a opțiunii OFFSET este că n poate fi ușor schimbat. Dacă schimbăm n la 7 și trasăm rezultatele, obținem un grafic de genul acesta:
Notă: o ciudățenie cu formulele OFFSET de mai sus este că acestea nu vor funcționa în Foi de calcul Google, deoarece funcția OFFSET din Foi de calcul nu va permite o valoare negativă pentru înălțime sau lățime. Foaia de calcul atașată are formule de soluție pentru foile Google.
