Come molti dei miei tutorial di Excel, anche questo è ispirato da una delle domande che ho ricevuto da un amico. Voleva calcolare la media mobile in Excel e le ho chiesto di cercarla online (o di guardare un video su YouTube a riguardo).
Ma poi, ho deciso di scriverne uno io stesso (anche il fatto che fossi un po' un nerd delle statistiche al college ha giocato un ruolo minore).
Ora, prima di dirti come calcolare la media mobile in Excel, lascia che ti dia rapidamente una panoramica di cosa significa media mobile e quali tipi di medie mobili ci sono.
Nel caso in cui desideri passare alla parte in cui mostro come calcolare la media mobile in Excel, fai clic qui.
Nota: non sono un esperto di statistiche e il mio intento in questo tutorial non è quello di coprire tutto ciò che riguarda le medie mobili. Ho solo lo scopo di mostrarti come calcolare le medie mobili in Excel (con una breve introduzione di cosa significano le medie mobili).Che cos'è una media mobile?
Sono sicuro che sai qual è un valore medio.
Se ho tre giorni di dati sulla temperatura giornaliera, puoi facilmente dirmi la media degli ultimi tre giorni (suggerimento: puoi usare la funzione MEDIA in Excel per farlo).
Una media mobile (chiamata anche media mobile o media mobile) è quando si mantiene lo stesso periodo di tempo della media, ma continua a muoversi man mano che vengono aggiunti nuovi dati.
Ad esempio, il giorno 3, se ti chiedo la temperatura media mobile di 3 giorni, mi darai il valore della temperatura media dei giorni 1, 2 e 3. E se il giorno 4 ti chiedo la temperatura media mobile di 3 giorni , mi darai la media dei giorni 2, 3 e 4.
Man mano che vengono aggiunti nuovi dati, mantieni lo stesso periodo di tempo (3 giorni) ma utilizzi i dati più recenti per calcolare la media mobile.
La media mobile è molto utilizzata per l'analisi tecnica e molte banche e analisti di borsa la usano quotidianamente (di seguito è riportato un esempio che ho preso dal sito Market Realist).
Tipi di medie mobili
Esistono tre tipi di medie mobili:
- Media mobile semplice (SMA)
- Media mobile ponderata (WMA)
- Media mobile esponenziale (EMA)
Media mobile semplice (SMA)
Questa è la media semplice dei punti dati nella durata data.
Nel nostro esempio di temperatura giornaliera, quando prendi semplicemente una media degli ultimi 10 giorni, viene fornita la media mobile semplice di 10 giorni.
Ciò può essere ottenuto calcolando la media dei punti dati nella durata indicata. In Excel, puoi farlo facilmente usando la funzione MEDIA (questo è trattato più avanti in questo tutorial).
Media mobile ponderata (WMA)
Supponiamo che il clima si stia facendo più fresco ogni giorno che passa e che tu stia utilizzando una media mobile di 10 giorni per ottenere l'andamento della temperatura.
È più probabile che la temperatura del giorno 10 sia un indicatore migliore della tendenza rispetto al giorno 1 (poiché la temperatura diminuisce ogni giorno che passa).
Quindi, stiamo meglio se ci affidiamo di più al valore del Day 10.
Per fare in modo che ciò si rifletta nella nostra media mobile, puoi dare più peso ai dati più recenti e meno ai dati passati. In questo modo, ottieni ancora la tendenza, ma con una maggiore influenza dei dati più recenti.
Questa è chiamata media mobile ponderata.
Media mobile esponenziale (EMA)
La media mobile esponenziale è un tipo di media mobile ponderata in cui viene dato più peso ai dati più recenti e diminuisce esponenzialmente per i punti dati più vecchi.
È anche chiamata media mobile ponderata esponenziale (EWMA)
La differenza tra WMA ed EMA è che con WMA puoi assegnare pesi in base a qualsiasi criterio. Ad esempio, in una media mobile a 3 punti, puoi assegnare un'età di ponderazione del 60% al punto dati più recente, il 30% al punto dati centrale e il 10% al punto dati più vecchio.
In EMA, viene dato un peso maggiore all'ultimo valore e il peso continua a diminuire esponenzialmente per i valori precedenti.
Basta con le lezioni di statistica.
Ora tuffiamoci e vediamo come calcolare le medie mobili in Excel.
Calcolo della media mobile semplice (SMA) utilizzando Data Analysis Toolpak in Excel
Microsoft Excel ha già uno strumento integrato per calcolare le medie mobili semplici.
Si chiama Pacchetto di strumenti per l'analisi dei dati.
Prima di poter utilizzare il toolpak di analisi dei dati, devi prima verificare se lo hai nella barra multifunzione di Excel o meno. È molto probabile che tu debba eseguire alcuni passaggi per abilitarlo prima.
Se hai già l'opzione Analisi dati nella scheda Dati, salta i passaggi seguenti e guarda i passaggi sul calcolo delle medie mobili.Fai clic sulla scheda Dati e controlla se vedi l'opzione Analisi dati o meno. Se non lo vedi, segui i passaggi seguenti per renderlo disponibile nella barra multifunzione.
- Fare clic sulla scheda File
- Fare clic su Opzioni
- Nella finestra di dialogo Opzioni di Excel, fare clic su Componenti aggiuntivi
- Nella parte inferiore della finestra di dialogo, seleziona Componenti aggiuntivi di Excel nel menu a discesa, quindi fai clic su Vai.
- Nella finestra di dialogo dei componenti aggiuntivi che si apre, seleziona l'opzione Strumenti di analisi
- Fare clic su OK.
I passaggi precedenti abiliterebbero il pacchetto di strumenti per l'analisi dei dati e ora vedrai questa opzione nella scheda Dati.
Supponiamo di avere il set di dati come mostrato di seguito e di voler calcolare la media mobile degli ultimi tre intervalli.
Di seguito sono riportati i passaggi per utilizzare l'analisi dei dati per calcolare una media mobile semplice:
- Fare clic sulla scheda Dati
- Fare clic sull'opzione Analisi dati
- Nella finestra di dialogo Analisi dati, fare clic sull'opzione Media mobile (potrebbe essere necessario scorrere un po' per raggiungerla).
- Fare clic su OK. Si aprirà la finestra di dialogo "Media mobile".
- In Input Range, seleziona i dati per i quali vuoi calcolare la media mobile (B2:B11 in questo esempio)
- Nell'opzione Intervallo, inserisci 3 (dato che stiamo calcolando una media mobile a tre punti)
- Nell'intervallo di output, inserisci la cella in cui desideri i risultati. In questo esempio, sto usando C2 come intervallo di output
- Fare clic su OK
I passaggi precedenti ti darebbero il risultato della media mobile come mostrato di seguito.
Si noti che le prime due celle nella colonna C hanno come risultato l'errore #N/D. Questo perché è una media mobile a tre punti e ha bisogno di almeno tre punti dati per dare il primo risultato. Quindi i valori medi mobili effettivi iniziano dopo il terzo punto dati in poi.
Noterai anche che tutto ciò che questo toolpak di analisi dei dati ha fatto è stato applicare una formula MEDIA alle celle. Quindi, se vuoi farlo manualmente senza il pacchetto di strumenti di analisi dei dati, puoi certamente farlo.
Ci sono, tuttavia, alcune cose che sono più facili da fare con il toolpak di analisi dei dati. Ad esempio, se vuoi ottenere il valore dell'errore standard e il grafico della media mobile, tutto ciò che devi fare è selezionare una casella e farà parte dell'output.
Calcolo delle medie mobili (SMA, WMA, EMA) utilizzando le formule in Excel
Puoi anche calcolare le medie mobili utilizzando la formula MEDIA.
In effetti, se tutto ciò di cui hai bisogno è il valore della media mobile (e non l'errore standard o il grafico), l'uso di una formula può essere un'opzione migliore (e più veloce) rispetto all'utilizzo di Data Analysis Toolpak.
Inoltre, Data analysis Toolpak fornisce solo la Simple Moving Average (SMA), ma se vuoi calcolare WMA o EMA, devi fare affidamento solo sulle formule.
Calcolo della media mobile semplice utilizzando le formule
Supponiamo di avere il set di dati come mostrato di seguito e di voler calcolare la SMA a 3 punti:
Nella cella C4, inserisci la seguente formula:
=MEDIA(B2:B4)
Copia questa formula per tutte le celle e ti darà la SMA per ogni giorno.
Ricorda: quando si calcola la SMA utilizzando le formule, è necessario assicurarsi che i riferimenti sulla formula siano relativi. Ciò significa che la formula può essere =MEDIA(B2:B4) o =MEDIA($B2:$B4), ma non può essere =MEDIA($B$2:$B$4) o =MEDIA(B$2:B$4 ). La parte del numero di riga del riferimento deve essere senza il simbolo del dollaro. Puoi leggere di più sui riferimenti assoluti e relativi qui.
Poiché stiamo calcolando una media mobile semplice (SMA) a 3 punti, le prime due celle (per i primi due giorni) sono vuote e iniziamo a utilizzare la formula dal terzo giorno in poi. Se lo desideri, puoi utilizzare i primi due valori così come sono e utilizzare il valore SMA dal terzo in poi.
Calcolo della media mobile ponderata utilizzando le formule
Per WMA, è necessario conoscere i pesi che verrebbero assegnati ai valori.
Ad esempio, supponiamo di dover calcolare il WMA a 3 punti per il set di dati sottostante, dove il peso del 60% è dato all'ultimo valore, il 30% a quello precedente e il 10% a quello precedente.
Per fare ciò, inserisci la seguente formula nella cella C4 e copia per tutte le celle.
=0,6*B4+0,3*B3+0,1*B2
Poiché stiamo calcolando una media mobile ponderata a 3 punti (WMA), le prime due celle (per i primi due giorni) sono vuote e iniziamo a utilizzare la formula dal terzo giorno in poi. Se lo desideri, puoi utilizzare i primi due valori così come sono e utilizzare il valore WMA dal terzo in poi.
Calcolo della media mobile esponenziale usando le formule
La media mobile esponenziale (EMA) dà un peso maggiore all'ultimo valore e i pesi continuano a diminuire in modo esponenziale per i valori precedenti.
Di seguito è riportata la formula per calcolare l'EMA per una media mobile a tre punti:
EMA = [Ultimo valore - Valore EMA precedente] * (2 / N+1) + EMA precedente
… dove N sarebbe 3 in questo esempio (dato che stiamo calcolando un EMA a tre punti)
Nota: per il primo valore EMA (quando non hai alcun valore precedente per calcolare EMA), prendi semplicemente il valore così com'è e consideralo il valore EMA. È quindi possibile utilizzare questo valore in futuro.Supponiamo di avere il set di dati seguente e di voler calcolare l'EMA a tre periodi:
Nella cella C2, inserisci lo stesso valore di B2. Questo perché non esiste un valore precedente per calcolare l'EMA.
Nella cella C3, inserisci la formula seguente e copia per tutte le celle:
=(LA3-DO2)*(2/4)+DO2
In questo esempio, l'ho mantenuto semplice e ho utilizzato l'ultimo valore e il precedente valore EMA per calcolare l'EMA corrente.
Un altro modo popolare per farlo è calcolare prima la media mobile semplice e poi usarla al posto dell'ultimo valore effettivo.
Aggiunta di una linea di tendenza della media mobile a un grafico a colonne
Se disponi di un set di dati e stai creando un grafico a barre utilizzandolo, puoi anche aggiungere la linea di tendenza della media mobile con pochi clic.
Supponiamo di avere un set di dati come mostrato di seguito:
Di seguito sono riportati i passaggi per creare un grafico a barre utilizzando questi dati e aggiungendo una linea di tendenza media mobile in tre parti a questo grafico:
- Seleziona il set di dati (incluse le intestazioni)
- Fare clic sulla scheda Inserisci
- Nel gruppo Grafico, fai clic sull'icona "Inserisci colonna o grafico a barre".
- Fare clic sull'opzione Istogramma raggruppato. Questo inserirà il grafico nel foglio di lavoro.
- Con il grafico selezionato, fai clic sulla scheda Design (questa scheda viene visualizzata solo quando il grafico è selezionato)
- Nel gruppo Layout grafico, fai clic su "Aggiungi elemento grafico".
- Passa il cursore sull'opzione "Linea di tendenza" e quindi fai clic su "Altre opzioni di linea di tendenza"
- Nel riquadro Formato linea di tendenza, seleziona l'opzione "Media mobile" e imposta il numero di periodi.
Questo è tutto! I passaggi precedenti aggiungerebbero una linea di tendenza in movimento al tuo istogramma.
Nel caso in cui si desideri inserire più di una trendline a media mobile (ad esempio una per 2 periodi e una per 3 periodi), ripetere i passaggi da 5 a 8).
È possibile utilizzare gli stessi passaggi anche per inserire una linea di tendenza media mobile in un grafico a linee.
Formattazione della linea di tendenza della media mobile
A differenza di un normale grafico a linee, una linea di tendenza media mobile non consente molta formattazione. Ad esempio, se desideri evidenziare un punto dati specifico sulla linea di tendenza, non sarai in grado di farlo.
Alcune cose che puoi formattare nella linea di tendenza includono:
- Colore della linea. Puoi usarlo per evidenziare una delle linee di tendenza rendendo tutto chiaro nel grafico e facendo apparire la linea di tendenza con un colore brillante
- Il spessore della linea
- Il trasparenza della linea
Per formattare la linea di tendenza della media mobile, fare clic con il pulsante destro del mouse su di essa e quindi selezionare l'opzione Formato linea di tendenza.
Questo aprirà il riquadro Formato Trendline a destra. Questo riquadro come tutte le opzioni di formattazione (in diverse sezioni - Riempimento e linea, Effetti e Opzioni linea di tendenza).
