Netezirea datelor din graficul Excel cu o medie mobilă fixă funcționează atunci când aveți experiență cu datele și cât de variabile sunt acestea. Dar, dacă datele se schimbă sau dacă datele sale sunt noi, este posibil ca dumneavoastră sau conducerea să doriți să experimentați cu un număr diferit de perioade în media de netezire.
În acest exemplu, se utilizează o formulă OFFSET pentru a crea o medie pe un interval dinamic. Introduceți numărul de luni pe care doriți să le utilizați pentru o perioadă de netezire, iar AVERAGE și OFFSET calculează câte celule trebuie să se calculeze media.
Înainte de a face AVERAGE dinamică, să începem prin a înțelege cum funcționează OFFSET. În figura următoare, celula G26 conține formula de netezire a datelor din rândul 20,
=AVERAGE(OFFSET(G20,0,0,0,1,-3))
OFFSET specifică intervalul pe care AVERAGE va netezi datele.
Această formulă este un pas intermediar pentru a vă ajuta să înțelegeți cum funcționează formula dinamică. Funcția OFFSET returnează un interval asupra căruia AVERAGE va acționa. Atributele lui OFFSET sunt,
OFFSET(referință,rânduri,coloane,,)
În acest exemplu, intervalul pe care OFFSET îl calculează începe la G20 și are un decalaj de zero rânduri sau coloane. Cu alte cuvinte, colțul din stânga sus al intervalului calculat de OFFSET este doar G20. Acesta nu va fi deplasat sau decalat cu niciun rând sau coloană. Intervalul calculat va fi de 1 rând în sus și va include celula curentă G26 și încă două la stânga (adică un -3). Deci, acest interval calculat va fi G20:E20.
Copiați acea formulă din G26 în dreapta și veți vedea că face media celulei de date de deasupra ei din rândul 20 și a celor două celule anterioare din stânga.
Deci, iată trucul care va face ca aceasta să fie o dinamică și o medie pe un interval pe care îl alegeți. Ce se întâmplă dacă înlocuiți o referință de celulă pentru acel -3 din formulă?
În figura de mai jos, formula din celula G26 a fost modificată astfel încât este specificată de numărul din celula E24,
AVERAGE(OFFSET(G20,0,0,0,1,-$E$24))
$E$24 este celula umbrită în verde. Numărul pe care îl introduceți în celula E24 va determina lățimea celulelor folosite pentru a face media.
Introducerea unei valori în E24 va schimba intervalul folosit pentru a netezi datele.
În acest punct ați putea folosi un rotitor sau un meniu derulant pentru a oferi utilizatorului posibilitatea de a alege cât de largă să facă media de netezire. Rezultatul selecției lor ar trebui să meargă în celula E24.
Încărcați datele de netezire a graficului Excel Chart cu netezire fixă sau dinamică
Există o problemă cu acest aranjament. Dacă utilizatorul introduce un număr mai mare în E24 decât există celule în stânga, atunci OFFSET va include celulele goale și eticheta. Acest lucru va da o medie incorectă. Dacă E24 este atât de mare încât OFFSET iese din foaie, atunci AVERAGE produce o eroare. Ce trebuie făcut? Avem nevoie de o soluție care să nu oprească doar o eroare, cum ar fi ISERROR, avem nevoie de una care să oprească răspunsurile incorecte.
În figura următoare, formula este celula G26 a fost ajustată. Rândul 25 are în stânga o serie de numere care sunt limitele pentru intervalele valide. Noua formulă din celula G26 se uită la rândul 25 și dacă numărul din celula E24 este mai mare decât limita din rândul 25, atunci se produce NA(). Această nouă formulă de corectare a erorilor din G26 este,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Când o diagramă Excel face referință la NA() într-o celulă, aceasta nu trasează nimic. Acest lucru împiedică linia de netezire să coboare la zero.
Modificați formula de netezire dinamică pentru a preveni erorile.
Probleme cu datele de netezire și cum să le ocoliți
Acum puteți lăsa utilizatorul să încerce orice perioadă de netezire dorește. Cu toate acestea, (se pare că întotdeauna există un „totuși” sau un „dar, în acest caz”) există o problemă cu netezirea datelor. Netezirea elimină volatilitatea, dar înseamnă, de asemenea, că nu veți vedea schimbări de direcție, puncte de inflexiune sau noi tendințe până la aproximativ jumătate din perioada de netezire. Sunteți în spatele curbei.
Cel mai bun din ambele lumi este să folosiți o funcție de netezire a mediei ponderate. În acest fel, puteți da mai multă greutate datelor pe termen scurt, astfel încât să vedeți tendințele, dar puteți netezi pe baza datelor mai vechi pentru a elimina o parte din volatilitate. Sună ca un caz bun pentru un alt articol, Smoothing Excel Chart Data Using a Weighted Average.
Download the example file for smoothing Excel chart data with moving or dynamic smoothing
Articole corelate,
- Smoothing Excel Chart Data with Moving Average
- Smoothing Excel Chart Data Using a Weighted Average