Calcula la mitjana mòbil a Excel (simple, exponencial i ponderada)
Mitjana mòbil significa que calculem la mitjana de les mitjanes del conjunt de dades que tenim, a Excel tenim una característica incorporada per al càlcul de la mitjana mòbil que està disponible a la pestanya d’anàlisi de dades de la secció d’anàlisi. amb intervals com a sortida, els càlculs basats en meres fórmules en Excel per calcular la mitjana mòbil són difícils, però tenim una funció incorporada en Excel per fer-ho.
Què és la mitjana mòbil a Excel
La mitjana mòbil és una tècnica àmpliament utilitzada en l'anàlisi de sèries temporals que s'utilitza per predir el futur. Les mitjanes mòbils d'una sèrie temporal es construeixen bàsicament prenent mitjanes de diversos valors seqüencials d'altres dades de sèries temporals.
Hi ha tres tipus de mitjanes mòbils: mitjana mòbil simple, mitjana mòbil ponderada i mitjana mòbil exponencial a Excel.
# 1: mitjana mòbil simple a Excel
Una mitjana mòbil senzilla ajuda a calcular la mitjana de l’últim nombre de períodes d’una sèrie de dades. Suposem que es donen preus de n període. A continuació, es dóna la mitjana mòbil simple com
Mitjana mòbil simple = [P1 + P2 + …………. + Pn] / n
# 2: mitjana mòbil ponderada a Excel
La mitjana mòbil ponderada proporciona la mitjana ponderada dels darrers n períodes. La ponderació disminueix amb cada punt de dades del període de temps anterior.
Mitjana mòbil ponderada = (Preu * factor de ponderació) + (Preu del període anterior * factor de ponderació-1)
# 3: mitjana mòbil exponencial a Excel
És similar a la mitjana mòbil simple que mesura les tendències durant un període de temps. Tot i que la mitjana mòbil simple calcula una mitjana de dades donades, la mitjana mòbil exponencial dóna més pes a les dades actuals.
Mitjana mòbil exponencial = (K x (C - P)) + P
On,
- K = constant de suavitzat exponencial
- C= preu actual
- Pàg= mitjana mòbil exponencial de períodes anteriors (mitjana mòbil simple utilitzada per al càlcul dels primers períodes)
Com es calcula la mitjana mòbil a Excel?
A continuació es mostra l'exemple de mitjana mòbil a Excel.
Podeu descarregar aquesta plantilla Excel mitja mòbil aquí - Plantilla Excel mitja mòbilExemple 1: mitjana mòbil simple a Excel
Per calcular la mitjana mòbil simple, hem pres dades de vendes d’una empresa de gener a desembre de l’any 2018. El nostre objectiu és suavitzar les dades i conèixer la xifra de vendes al gener de 2019. Utilitzarem aquí una mitjana mòbil de tres mesos.
Pas 1:La mitjana mòbil de gener, febrer i març es calcula prenent la suma de la xifra de vendes dels mesos i dividint-la per 3.
Pas 2:Seleccionar a la cantonada de la cel·la D5 i després només arrossegar i deixar anar donarà la mitjana mòbil dels períodes restants. Aquesta és la funció d'eina de farciment d'Excel.
La predicció de vendes per al gener de 2019 és de 10456,66667.
Pas 3:Ara representem la xifra de vendes i la mitjana mòbil al gràfic lineal per entendre la diferència de tendència. Això es pot fer des de la pestanya d'inserció. Primer hem seleccionat les sèries de dades i, a continuació, a la secció Gràfics que hi ha a sota d’inserció, hem utilitzat el gràfic lineal.
Després de crear els gràfics, es pot veure que el gràfic amb la mitjana mòbil està molt més suavitzat que la sèrie de dades original.
Exemple 2: mitjana mòbil senzilla mitjançant la pestanya Anàlisi de dades a Excel
- A la pestanya Dades del grup Anàlisi, hem de fer clic a Anàlisi de dades. A continuació es mostra la captura de pantalla.
- Des de l’anàlisi de dades es pot accedir a la mitjana mòbil.
- Després de fer clic a la mitjana mòbil, hem seleccionat la xifra de vendes com a rang d'entrada.
- Es fa clic a les etiquetes de la primera fila per fer entendre a Excel que la primera fila té el nom de l’etiqueta.
- L'interval 3 es selecciona com perquè volem tres anys de mitjana mòbil.
- Hem seleccionat l'interval de sortida ajustat a la xifra de vendes.
- També volem veure la sortida del gràfic en què podrem veure la diferència entre el real i el previst.
Aquest gràfic mostra la diferència entre la mitjana mòbil real i la prevista.
Exemple 3: mitjana mòbil ponderada a Excel
Utilitzem la mitjana mòbil ponderada de tres anys i la fórmula es dóna a la captura de pantalla.
Després d’utilitzar la fórmula, vam obtenir la mitjana mòbil durant un període.
Hem obtingut la mitjana mòbil de la resta de períodes arrossegant i deixant anar els valors de les cel·les següents.
La previsió per al gener de 2019, és a dir, 10718,33
Ara vam agafar el gràfic lineal per veure la suavització de les dades. Per a això, hem seleccionat el mes les dades previstes i després hem inserit un gràfic lineal.
Ara compararem les nostres dades previstes amb les nostres dades reals. A les captures de pantalla següents podem veure fàcilment la diferència entre les dades reals i les dades previstes. El gràfic superior és les dades reals i el gràfic següent és la mitjana mòbil i les dades previstes. Podem veure que el gràfic mitjà mòbil s’ha suavitzat significativament en comparació amb el gràfic que conté les dades reals.
Exemple # 4: mitjana mòbil exponencial a Excel
La fórmula per a la mitjana mòbil exponencial ésSt= α.Yt-1+ (1- α) St-1……(1)
On,
- Yt-1 = observació real al període t-1
- St-1= mitjana mòbil simple en el període t-1
- α = factor de suavitzat i varia entre .1 i .3. Com més gran sigui el valor de α, el gràfic sigui més proper als valors reals i disminueixi el valor de α, més suau serà el gràfic.
En primer lloc, calculem la mitjana mòbil simple com es mostra anteriorment. Després d’això, apliquem la fórmula donada a l’equació (1). Per fixar el valor α de tots els valors següents, hem premut F4.
Obtenim els valors arrossegant i deixant anar a les cel·les següents.
Ara volem veure la comparació entre els valors reals, la mitjana mòbil simple i la mitjana mòbil exponencial a Excel. Ho hem fet fent un gràfic lineal.
A la captura de pantalla anterior, podem veure la diferència entre la xifra real de vendes, la mitjana mòbil simple i la mitjana mòbil exponencial a Excel.
Coses que cal recordar sobre la mitjana mòbil a Excel
- La mitjana mòbil simple es pot calcular mitjançant una funció MITJANA en excel
- La mitjana mòbil ajuda a suavitzar les dades
- Les mitjanes estacionals se solen anomenar índex estacional
- La mitjana mòbil exponencial a Excel dóna més pes a les dades recents que la mitjana mòbil simple. Per tant, la suavització en cas de la mitjana mòbil exponencial a Excel és més que la de la mitjana mòbil simple.
- En empreses com una borsa, la mitjana mòbil ajuda el comerciant a identificar més fàcilment la tendència.
