Sommario
Per calcolare una media mobile o mobile è possibile utilizzare una semplice formula basata sulla funzione MEDIA con relativi riferimenti. Nell'esempio mostrato, la formula in E7 è:
=AVERAGE(C5:C7)
Quando la formula viene copiata, calcola una media mobile di 3 giorni in base al valore delle vendite per il giorno corrente e i due giorni precedenti.
Di seguito è riportata un'opzione più flessibile basata sulla funzione OFFSET che gestisce periodi variabili.
Informazioni sulle medie mobili
Una media mobile (chiamata anche media mobile) è una media basata su sottoinsiemi di dati a determinati intervalli. Il calcolo di una media a intervalli specifici appiana i dati riducendo l'impatto delle fluttuazioni casuali. Ciò semplifica la visualizzazione delle tendenze generali, soprattutto in un grafico. Maggiore è l'intervallo utilizzato per calcolare una media mobile, maggiore sarà l'attenuazione, poiché più punti dati sono inclusi in ciascuna media calcolata.
Spiegazione
Le formule riportate nell'esempio utilizzano tutte la funzione MEDIA con un relativo riferimento impostato per ogni specifico intervallo. La media mobile di 3 giorni in E7 viene calcolata alimentando MEDIA un intervallo che include il giorno corrente ei due giorni precedenti in questo modo:
=AVERAGE(C5:C7) // 3-day average
Le medie di 5 e 7 giorni vengono calcolate allo stesso modo. In ogni caso, l'intervallo fornito a MEDIA viene ampliato per includere il numero di giorni richiesto:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Tutte le formule utilizzano un riferimento relativo per l'intervallo fornito alla funzione MEDIA. Man mano che le formule vengono copiate nella colonna, l'intervallo cambia in ogni riga per includere i valori necessari per ciascuna media.
Quando i valori vengono tracciati in un grafico a linee, l'effetto di smussatura è chiaro:
Dati insufficienti
Se inizi le formule nella prima riga della tabella, le prime poche formule non avranno dati sufficienti per calcolare una media completa, perché l'intervallo si estenderà sopra la prima riga di dati:
Questo può o meno essere un problema, a seconda della struttura del foglio di lavoro e se è importante che tutte le medie siano basate sullo stesso numero di valori. La funzione MEDIA ignorerà automaticamente i valori di testo e le celle vuote, quindi continuerà a calcolare una media con meno valori. Questo è il motivo per cui "funziona" in E5 e E6.
Un modo per indicare chiaramente dati insufficienti è controllare il numero di riga corrente e interrompere con #NA quando sono presenti meno di n valori. Ad esempio, per la media di 3 giorni, potresti utilizzare:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
La prima parte della formula genera semplicemente un numero di riga "normalizzato", che inizia con 1:
ROW()-ROW($C$5)+1 // relative row number
Nella riga 5, il risultato è 1, nella riga 6 il risultato è 2 e così via.
Quando il numero di riga corrente è inferiore a 3, la formula restituisce # N / D. Altrimenti, la formula restituisce una media mobile come prima. Questo imita il comportamento della versione degli strumenti di analisi di Moving Average, che restituisce # N / A fino al raggiungimento del primo periodo completo.
Tuttavia, con l'aumentare del numero di periodi, finirai le righe sopra i dati e non sarai in grado di inserire l'intervallo richiesto all'interno di MEDIA. Ad esempio, non è possibile impostare una media mobile di 7 giorni con il foglio di lavoro come mostrato, poiché non è possibile immettere un intervallo che si estende di 6 righe sopra C5.
Periodi variabili con OFFSET
Un modo più flessibile per calcolare una media mobile è con la funzione OFFSET. OFFSET può creare un intervallo dinamico, il che significa che possiamo impostare una formula in cui il numero di periodi è variabile. La forma generale è:
=AVERAGE(OFFSET(A1,0,0,-n,1))
dove n è il numero di periodi da includere in ciascuna media. Come sopra, OFFSET restituisce un intervallo che viene passato alla funzione MEDIA. Di seguito puoi vedere questa formula in azione, dove "n" è l'intervallo denominato E2. A partire dalla cella C5, OFFSET costruisce un intervallo che si estende fino alle righe precedenti. Ciò si ottiene utilizzando un'altezza pari al negativo n. Quando E5 viene modificato in un altro numero, la media mobile viene ricalcolata su tutte le righe:
La formula in E5, copiata in basso, è:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Come la formula originale sopra, anche la versione con OFFSET avrà il problema di dati insufficienti nelle prime righe, a seconda di quanti periodi sono indicati in E5.
Nell'esempio mostrato, le medie vengono calcolate correttamente perché la funzione MEDIA ignora automaticamente i valori di testo e le celle vuote e non ci sono altri valori numerici sopra C5. Quindi, mentre l'intervallo passato in MEDIA in E5 è C1: C5, c'è solo un valore per la media, 100. Tuttavia, con l'aumentare dei periodi, OFFSET continuerà a creare un intervallo che si estende sopra l'inizio dei dati, fino a raggiungere all'inizio del foglio di lavoro e restituisce un errore #REF.
Una soluzione è "limitare" la dimensione dell'intervallo al numero di punti dati disponibili. Questo può essere fatto usando la funzione MIN per limitare il numero usato per l'altezza come mostrato di seguito:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Questo sembra piuttosto spaventoso, ma in realtà è abbastanza semplice. Stiamo limitando l'altezza passata in OFFSET con la funzione MIN:
MIN(ROW()-ROW($C$5)+1,n)
All'interno di MIN, il primo valore è un numero di riga relativo, calcolato con:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Il secondo valore dato a MIN è il numero di periodi, n. Quando il numero di riga relativo è minore di n, MIN restituisce il numero di riga corrente a OFFSET per l'altezza. Quando il numero di riga è maggiore di n, MIN restituisce n. In altre parole, MIN restituisce semplicemente il più piccolo dei due valori.
Una caratteristica interessante dell'opzione OFFSET è che n può essere facilmente modificato. Se cambiamo n in 7 e tracciamo i risultati, otteniamo un grafico come questo:
Nota: una stranezza con le formule OFFSET sopra è che non funzioneranno in Fogli Google, perché la funzione OFFSET in Fogli non consentirà un valore negativo per altezza o larghezza. Il foglio di lavoro allegato contiene formule alternative per i fogli Google.
