Smoothing Excel chart data with a fixed moving average works when you have experience with the data and how variable it is. Ma, se i dati cambiano o sono nuovi, voi o la direzione potreste voler sperimentare un numero diverso di periodi nella media di lisciatura.
In questo esempio, una formula OFFSET viene usata per creare una media su un intervallo dinamico. Voi digitate il numero di mesi che volete usare per un periodo di lisciatura e la formula AVERAGE e OFFSET calcolano quante celle attraversano la media.
Prima di rendere dinamica la formula AVERAGE, cominciamo a capire come funziona OFFSET. Nella figura seguente, la cella G26 contiene la formula per smussare i dati della riga 20,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET specifica l’intervallo su cui AVERAGE smusserà i dati.
Questa formula è un passo intermedio per aiutarvi a capire come funziona la formula dinamica. La funzione OFFSET restituisce un intervallo su cui AVERAGE agirà. Gli attributi di OFFSET sono,
OFFSET(reference,rows,columns,,)
In questo esempio, l’intervallo che OFFSET calcola inizia da G20 e ha zero offset di riga o colonna. In altre parole, l’angolo in alto a sinistra dell’intervallo che OFFSET calcola è proprio G20. Non sarà spostato o compensato da nessuna riga o colonna. L’intervallo calcolato sarà alto 1 riga e includerà la cella corrente G26 e altre due a sinistra (questo è un -3). Quindi, questo intervallo calcolato sarà G20:E20.
Copia quella formula in G26 a destra e vedrai che fa la media della cella di dati sopra di essa nella riga 20 e le due celle precedenti a sinistra.
Quindi, ecco il trucco che renderà questo un dinamico e medio attraverso un intervallo che sceglierai. Cosa succede se sostituite un riferimento di cella per quel -3 nella formula?
Nella figura sottostante la formula nella cella G26 è stata modificata in modo che la media sia specificata dal numero nella cella E24,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 è la cella verde ombreggiata. Il numero che digitate nella cella E24 determinerà la larghezza delle celle usate per fare la media.
Inserendo un valore in E24 cambierete l’intervallo usato per smussare i dati.
A questo punto potreste usare una rotella o un menu a discesa per dare all’utente una scelta di quanto ampia rendere la media dello smussamento. Il risultato della loro selezione dovrebbe andare nella cella E24.
Scaricare i dati del grafico Excel lisciante con lisciatura fissa o dinamica
C’è un problema con questa disposizione. Se l’utente inserisce un numero più grande in E24 rispetto alle celle a sinistra, OFFSET includerà le celle vuote e l’etichetta. Questo darà una media errata. Se E24 è così grande che OFFSET va fuori dal foglio, allora AVERAGE produce un errore. Cosa fare? Abbiamo bisogno di una soluzione che non si limiti a fermare un errore, come ISERROR, ma che fermi le risposte errate.
Nella prossima figura la formula della cella G26 è stata corretta. La riga 25 ha una serie di numeri che sono i limiti degli intervalli validi a sinistra. La nuova formula nella cella G26 guarda la riga 25 e se il numero nella cella E24 è più grande del limite nella riga 25, allora viene prodotto NA(). Questa nuova formula di correzione degli errori in G26 è,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Quando un grafico Excel fa riferimento a NA() in una cella non traccia nulla. Questo impedisce alla linea di lisciatura di scendere a zero.
Modifica la formula di lisciatura dinamica per evitare errori.
Problemi con i dati di lisciatura e come aggirarli
Ora puoi permettere al tuo utente di provare qualsiasi periodo di lisciatura voglia. Tuttavia, (sembra che ci sia sempre un “tuttavia” o un “ma, in questo caso”) c’è un problema con lo smoothing dei dati. Lo smussamento elimina la volatilità, ma significa anche che non vedrete cambiamenti di direzione, punti di inflessione o nuove tendenze fino a circa metà del periodo di smussamento. Sei dietro la curva.
Il meglio dei due mondi è usare una funzione di smoothing media ponderata. In questo modo si può dare più peso ai dati a breve termine in modo da vedere le tendenze, ma si può smussare sulla base di dati più vecchi per eliminare parte della volatilità. Sembra un buon caso per un altro articolo, Smoothing Excel Chart Data Using a Weighted Average.
Scarica il file di esempio per lo smoothing dei dati del grafico Excel con smoothing mobile o dinamico
Articoli correlati,
- Smoothing Excel Chart Data with Moving Average
- Smoothing Excel Chart Data Using a Weighted Average