Come calcolare la media mobile su Excel

La media mobile può essere calcolata su Excel in diversi modi, sia utilizzando formule specifiche sia con dei componenti aggiuntivi. Nell'esempio di questa guida creeremo una media mobile a tre mesi, utilizzando dei dati fittizi di fatturato che si estendono per 24 mesi.

La media mobile è un indicatore statistico utilizzato prevalentemente nell'analisi dei dati temporali e nelle serie storiche, con l'obiettivo di livellare fluttuazioni a breve termine e evidenziare tendenze a lungo termine. Consiste nel calcolare la media aritmetica di un determinato numero di dati consecutivi. Questa media viene poi "spostata" avanti di un intervallo temporale e si calcola nuovamente, dando origine a una "serie" di medie. Come detto, in questo caso la media mobile sarà considerata su tre mesi, ma puoi modificare le formule come preferisci.

La tabella si chiama semplicemente TBL.

Come calcolare la media mobile su Excel 365

Vediamo come calcolare la media mobile con Excel 365, creando una struttura scalabile. In questo caso la formula sarà:

=PERRIGA(TBL[FATTURATO];
LAMBDA(x;MEDIA(x;SCARTO(x;-1;0);SCARTO(x;-2;0))))

Stiamo dicendo a Excel che deve considerare dei calcoli che interessano tutte le righe della nostra tabella, in particolare la colonna chiamata FATTURATO. Per tutte le celle considerate, faremo la media del valore della cella riga attuale (x), così come della riga precedente, indicata con SCARTO(x;-1;0) e di quella ancora precedente, quindi due righe sopra, indicata con SCARTO(x;-2;0).

Più elegantemente, potremmo anche impostare la formula in modo da creare una funzione LAMBDA:

=LAMBDA(range;
PERRIGA(range;
LAMBDA(x;MEDIA(x;SCARTO(x;-1;0);SCARTO(x;-2;0)))))(TBL[FATTURATO])

In questo caso abbiamo inserito che il range possa essere qualsiasi intervallo sul foglio. Per fare la prova, abbiamo inserito TBL_FATTURATO alla fine - se vuoi provare, ricordati sempre di chiudere tutte le parentesi per poi inserire il tuo intervallo alla fine, altrimenti la funzione non potrà essere accettata da Excel. La nostra funzione LAMBDA sarà esattamente uguale a questa formula, ma senza la parte finale con TBL_FATTURATO.

Per creare una funzione LAMBDA, dobbiamo andare su Formule > Nomi definiti > Definisci nome e decidere il nome da dare alla nostra nuova funzione, in questo caso ROLLING_AVG_3M. Puoi inserire anche una descrizione a corredo, se pensi possa aiutare gli utenti. Nella sezione "Riferito a:" copieremo la nostra formula, che abbiamo testato in precedenza.

Ora abbiamo creato la nostra funzione LAMBDA dedicata. Chiunque apra il foglio potrà calcolare la media mobile semplicemente scrivendo =ROLLING_AVG_3M(range) e inserendo l'intervallo di riferimento.

Chiaramente, se volessi creare una media mobile di 6 mesi o di più, dovresti modificare il numero di dati che sono considerati nella parte della formula con SCARTO(x;....;0), ma il procedimento sarebbe esattamente lo stesso.

Come calcolare la media mobile con Excel 365 modificando i parametri

Vediamo anche come calcolare la media mobile con Excel 365 in maniera più flessibile, modificando i parametri.

=PERRIGA(TBL[FATTURATO];
LAMBDA(x;MEDIA(SCARTO(x;SEQUENZA(S1;;0;-1);0))))

In questo caso stiamo riutilizzando la funzione precedente, ma permettendo all'utente di inserire il numero che preferisce nella cella S1. Questo è un metodo utile se vuoi calcolare la media mobile a 12 mesi, 6 mesi o 3 mesi sempre utilizzando la stessa funzione. Potresti creare una LAMBDA impostando unicamente l'intervallo di riferimento e il valore da inserire, in questo caso S1, per poi avere una funzione utile per tutto il foglio.

Nello specifico, la funzione parte dal punto SCARTO(x;0;0), che è il nostro valore nella stessa riga, per poi muoversi in altezza seguendo le indicazioni in S1. Nell'esempio proposto, l'intervallo prenderà 3 valori perchè in S1 c'è il valore 3. SEQUENZA ci aiuta proprio per questo, dato che avremo, in ordine SCARTO(x;0;0), SCARTO(x;-1;0) e SCARTO(x;-2;0).

Come calcolare la media mobile con tutte le versioni di Excel

Il calcolo della media mobile su Excel può avvenire su tutte le versioni almeno in due modi diversi:

• Inserendo una formula
• Utilizzando i componenti aggiuntivi di Excel

Questa parte potrebbe essere utile se non hai una delle ultime versioni, come ad esempio Excel 365. Vediamo subito il primo caso.

Come calcolare la media mobile su Excel con una formula

Esiste una formula semplice per calcolare la media mobile su Excel in versioni anche piuttosto datate. Consideriamo il caso della media mobile su 3 mesi, inserita nella cella F3:

=MEDIA(D1:D3)

Questa formula prende i tre valori da D1 a D3 e ne restituisce la media. La formula considera solo i casi nei quali ci sono dei valori, quindi come noterai in questo caso la media è uguale al valore in D3 perchè D1 e D2 non presentano valori numerici. Trascinando la formula, questa diventerà =MEDIA(D2:D4), poi =MEDIA(D3:D5) e così via. La media mobile sarà quindi calcolata correttamente perchè prenderà, per ogni riga, solamente tre valori.

Questo non è un metodo particolarmente scalabile, dato che potremmo avere poi nuovi mesi da utilizzare per l'analisi.

Come calcolare la media mobile su Excel senza utilizzare formule

Su Excel possiamo calcolare la media mobile anche senza utilizzare delle formule, sfruttando i componenti aggiuntivi.

Prima di tutto verifica di aver installato, fra i componenti aggiuntivi, gli strumenti di analisi. Devi andare su File > Opzioni > Componenti aggiuntivi > Componenti aggiuntivi di Excel e cliccare poi sul pulsante "Vai...".

Da qui, dovrai cliccare su Strumenti di analisi e poi su OK per aggiungerli al tuo file Excel.

Una volta verificato questo passaggio, clicca su Dati > Analisi > Analisi dati e seleziona Media mobile. In questo modo potrai calcolarla in automatico seguendo un menu contestuale.

A questo punto seleziona il tuo intervallo di partenza, quanti siano i valori da considerare per il calcolo della media mobile e dove questi dati devono essere inseriti sul foglio

A questo punto, la tua media mobile sarà calcolata!

Come hai visto, potremo calcolare la media mobile molto velocemente e senza particolari complessità. Attenzione però, perchè anche stavolta non avremo creato una struttura scalabile - il calcolo sarà one-shot, senza possibilità di modifica una volta ultimato. Ti apparirà un errore nelle prime celle perchè non ci sono abbastanza valori considerati, diversamente da quanto succede utilizzando le formule.