El análisis de la MEDIA MÓVIL en Excel proyecta valores en el período de pronósticos, basándose en el valor promedio de la variable calculada durante un número específico de períodos anteriores.
La media móvil nos proporciona información de tendencias que se vería enmascarada por una simple media de todos los datos históricos.
Todos los valores de pronóstico están basados en la siguiente fórmula:
Donde:
- N: Número de períodos anteriores que se incluyen en la media móvil.
- Aj: Valor real en la hora j.
- Fj: Valor pronosticado en la hora j.
Disponemos de ingreso realizados anualmente en nuestra empresa y deseamos calcular la media móvil de ellos. Columna A, ejercicio fiscal y Columna B, ingresos realizados por subvenciones.
| A | B | |
|---|---|---|
| 1 | Año | Ingreso |
| 2 | 1995 | 300 |
| 3 | 1996 | 400 |
| 4 | 1997 | 350 |
| 5 | 1998 | 200 |
| 6 | 1999 | 150 |
| 7 | 2000 | 270 |
| 8 | 2001 | 600 |
| 9 | 2002 | 420 |
| 10 | 2003 | 350 |
| 11 | 2004 | 625 |
| 12 | 2005 | 725 |
| 13 | 2006 | 890 |
| 14 | 2007 | 1100 |
| 15 | 2008 | 870 |
| 16 | 2009 | 950 |
El análisis de datos son herramientas disponibles como complemento. Por lo que debemos instalar y cargar el complemento para que la función quede disponible en Excel.
Ir a: Cinta de opciones Archivo (o en Excel 2007 Botón de Office)
Opciones de Excel. Mostrará el cuadro de diálogo de opciones de Excel.Ir a Categoría: Complementos
Ir a: Administrar Seleccionar: Complementos de Excel Pulsar el botón: Ir. Mostrará el cuadro de diálogo de Complementos.Ir a: Complementos disponibles
Activar herramientas para análisis Clic sobre ella. Requiere que se seleccione al instalar el programa Microsoft Office 2010.Clic Botón: Aceptar. Carga el complemento y cierra la ventana.
Ir a: Cinta de opciones
Ficha: Datos Análisis Análisis de datos 
. Mostrará el cuadro de diálogo de funciones para análisis.
Ir a: Funciones para análisis
Clic sobre Media móvil (herramienta deseada).Clic Botón: Aceptar. Muestra la ventana de estadística descriptiva.
Ir a: Rango de entrada
Clic en su interior Indicar el rango B1:B16. Referencia de datos que contiene la población de valores de los que deseamos una muestra. Microsoft Excel extraerá muestras de la primera columna, luego de la segunda y así sucesivamente.Ir a: Rótulos en la primera fila
Activar la casilla. Ya que la fila 1 contiene el encabezado o rótulo de los datos.Ir a: Intervalo
Escribir 5. Número de valores que desee incluir en la media móvil. El intervalo predeterminado es 3.Ir a: Opciones de salida
Clic sobre rango de salida (Para activarlo) Clic sobre la celda D2. Para indicar donde deseamos que nos muestre el informe estadístico, en la misma hoja y desde la celda D2.Ir a: Crear gráfico
Activar la casilla. Para genera un gráfico de histograma incrustado con la tabla de resultados.Ir a: Error típico
Activar la casilla. Número de valores que desee incluir en la media móvil. El intervalo predeterminado es 3.Clic Botón: Aceptar. Para incluir una columna que contenga valores de error típico en la tabla de resultados.
Verificar como en la Columna D inserta la media móvil, Columna E, calcula el error típico y el gráfico del dato real y del dato pronosticado.
La media móvil la podremos calcular como:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Año | Ingreso | Fórmula | |
| 2 | 1995 | 300 | - | |
| 3 | 1996 | 400 | - | |
| 4 | 1997 | 350 | - | |
| 5 | 1998 | 200 | - | |
| 6 | 1999 | 150 | =(B6+B5+B4+B3+B2)/5 | |
| 7 | 2000 | 270 | =(B7+B6+B5+B4+B3)/5 | |
| 8 | 2001 | 600 | =(B8+B7+B6+B5+B4)/5 | |
| 9 | 2002 | 420 | =(B9+B8+B7+B6+B5)/5 | |
| 10 | 2003 | 350 | =(B10+B9+B8+B7+B6)/5 | |
| 11 | 2004 | 625 | =(B11+B10+B9+B8+B7)/5 | |
| 12 | 2005 | 725 | =(B12+B11+B10+B9+B8)/5 | |
| 13 | 2006 | 890 | =(B13+B12+B11+B10+B9)/5 | |
| 14 | 2007 | 1100 | =(B14+B13+B12+B11+B10)/5 | |
| 15 | 2008 | 870 | =(B15+B14+B13+B12+B11)/5 | |
| 16 | 2009 | 950 | =(B16+B15+B14+B13+B12)/5 |
Excel Avanzado
excelente ayuda, lo busque por muchas partes y solo esta me sirvio
Hola Edwin,
Gracias por seguirnos y por tu comentario, y nos alegramos te sea útil nuestro contenido.
Saludos
excelente y sencillo
Hola.
Creo que tienes que mover un registro más. De esta manera se podría tener una previsión para el año 2010.
Hola Joshua,
Si se incluyen más filas vacías para obtener más años, los valores son erróneos.
Si aplicas en ingreso una escala uniforme, con incrementos de uno en uno, para obtener una línea recta verificarás que los valores en el pronóstico van de 0,5 en 0,5.
Recordar que para obtener pronósticos en Excel 2016, se dispone de la herramienta pronóstico.
Saludos.
Muy bueno, me ha servido un monton! Les consulto como debo hacer para obtener datos de 2010 en adelante??
Hola Diego,
Es lo mismo del ejemplo en la columna A los años deseados: 2010, 2011, etc.
En la columna B, los valores deseados.
En la columna D, copias un celda con fórmula y la pegas en el rango deseado.
Saludos.
Hola, quisiera saber si se puede y como hacer lo en un Windows XP
Hola Andrea,
Windows XP es el sistema operativo, no es la versión de Excel. Con la versión que tengas de Excel (independiente del sistema operativo) 2003, 2007, 2010, 2013, 2016 o 365, lo puedes realizar sin ningún problema.
Saludos.
perdon por la molestia, pero en el libro de David R anderson en sus pronosticos hace la suma de los tres años anteriores los divide y luego el resultado es el año 4 , para hacerlo mas sencillo basicamente 280 seria del año 2000 y no de 1999. Es un error de exel o del libro?
Hola Agustín,
Una cosa es la representación real y otra el pronostico para el año próximo.
Saludos.
Aclaracion: los intervalos en el libro son de 3
Hola Agustin,
Según se indica en el punto 6. Ir a: Intervalo / Escribir 5. Número de valores que desee incluir en la media móvil. El intervalo predeterminado es 3.
Saludos.
Muchas gracias, me ha servido este procedimiento, sencillo y muy rápido. Felicitaciones
Saludos