Como muchos de mis tutoriales de Excel, este también está inspirado en una de las consultas que recibí de un amigo. Quería calcular el promedio móvil en Excel y le pedí que lo buscara en línea (o que mirara un video de YouTube al respecto).
Pero luego, decidí escribir uno yo mismo (el hecho de que yo fuera un nerd de las estadísticas en la universidad también jugó un papel menor).
Ahora, antes de decirle cómo calcular el promedio móvil en Excel, permítame darle rápidamente una descripción general de lo que significa el promedio móvil y qué tipos de promedios móviles existen.
En caso de que quiera saltar a la parte donde muestro cómo calcular el promedio móvil en Excel, haga clic aquí.
Nota: no soy un experto en estadísticas y mi intención en este tutorial no es cubrir todo sobre las medias móviles. Solo pretendo mostrarle cómo calcular las medias móviles en Excel (con una breve introducción de lo que significan las medias móviles).¿Qué es una media móvil?
Estoy seguro de que sabe lo que es un valor medio.
Si tengo tres días de datos de temperatura diaria, puede decirme fácilmente el promedio de los últimos tres días (pista: puede usar la función PROMEDIO en Excel para hacer esto).
Un promedio móvil (también llamado promedio móvil o promedio móvil) es cuando mantiene el mismo período de tiempo del promedio, pero sigue moviéndose a medida que se agregan nuevos datos.
Por ejemplo, el día 3, si le pregunto la temperatura media móvil de 3 días, me dará el valor de temperatura media de los días 1, 2 y 3. Y si el día 4 le pregunto la temperatura media móvil de 3 días , me darás el promedio de los días 2, 3 y 4.
A medida que se agregan nuevos datos, mantiene el mismo período de tiempo (3 días) pero usa los datos más recientes para calcular el promedio móvil.
El promedio móvil se usa mucho para el análisis técnico y muchos bancos y analistas del mercado de valores lo usan a diario (a continuación se muestra un ejemplo que obtuve del sitio Market Realist).
Tipos de medias móviles
Hay tres tipos de medias móviles:
- Media móvil simple (SMA)
- Media móvil ponderada (WMA)
- Media móvil exponencial (EMA)
Media móvil simple (SMA)
Este es el promedio simple de los puntos de datos en la duración dada.
En nuestro ejemplo de temperatura diaria, cuando simplemente toma un promedio de los últimos 10 días, se obtiene el promedio móvil simple de 10 días.
Esto se puede lograr promediando los puntos de datos en la duración dada. En Excel, puede hacer esto fácilmente usando la función PROMEDIO (esto se explica más adelante en este tutorial).
Media móvil ponderada (WMA)
Supongamos que el clima es más fresco con cada día que pasa y está utilizando un promedio móvil de 10 días para obtener la tendencia de la temperatura.
Es más probable que la temperatura del día 10 sea un mejor indicador de la tendencia en comparación con el día 1 (ya que la temperatura desciende con cada día que pasa).
Por lo tanto, estaremos mejor si confiamos más en el valor del día 10.
Para que esto se refleje en nuestra media móvil, puede dar más peso a los datos más recientes y menos a los datos anteriores. De esta manera, aún obtiene la tendencia, pero con más influencia de los datos más recientes.
A esto se le llama media móvil ponderada.
Media móvil exponencial (EMA)
La media móvil exponencial es un tipo de media móvil ponderada en la que se da más peso a los datos más recientes y disminuye exponencialmente para los puntos de datos más antiguos.
También se llama Media Móvil Exponencial Ponderada (EWMA).
La diferencia entre WMA y EMA es que con WMA, puede asignar pesos según cualquier criterio. Por ejemplo, en un promedio móvil de 3 puntos, puede asignar una edad de peso del 60% al último punto de datos, 30% al punto de datos intermedio y 10% al punto de datos más antiguo.
En EMA, se le da un mayor peso al último valor y el peso sigue disminuyendo exponencialmente para los valores anteriores.
Basta de conferencia de estadística.
Ahora, profundicemos y veamos cómo calcular promedios móviles en Excel.
Calcular el promedio móvil simple (SMA) usando el paquete de herramientas de análisis de datos en Excel
Microsoft Excel ya tiene una herramienta incorporada para calcular los promedios móviles simples.
Se llama Paquete de herramientas de análisis de datos.
Antes de poder usar el paquete de herramientas de análisis de datos, primero debe verificar si lo tiene en la cinta de Excel o no. Es muy probable que deba seguir algunos pasos para habilitarlo primero.
En caso de que ya tenga la opción Análisis de datos en la pestaña Datos, omita los pasos a continuación y vea los pasos para calcular las medias móviles.Haga clic en la pestaña Datos y verifique si ve la opción Análisis de datos o no. Si no lo ve, siga los pasos a continuación para que esté disponible en la cinta.
- Haga clic en la pestaña Archivo
- Haga clic en Opciones
- En el cuadro de diálogo Opciones de Excel, haga clic en Complementos
- En la parte inferior del cuadro de diálogo, seleccione Complementos de Excel en el menú desplegable y luego haga clic en Ir.
- En el cuadro de diálogo Complementos que se abre, marque la opción Paquete de herramientas de análisis
- Haga clic en Aceptar.
Los pasos anteriores habilitarían el Paquete de herramientas de análisis de datos y ahora verá esta opción en la pestaña Datos.
Suponga que tiene el conjunto de datos como se muestra a continuación y desea calcular el promedio móvil de los últimos tres intervalos.
A continuación se muestran los pasos para usar el análisis de datos para calcular un promedio móvil simple:
- Haga clic en la pestaña Datos
- Haga clic en la opción Análisis de datos
- En el cuadro de diálogo Análisis de datos, haga clic en la opción Media móvil (es posible que deba desplazarse un poco para llegar a ella).
- Haga clic en Aceptar. Esto abrirá el cuadro de diálogo "Media móvil".
- En el rango de entrada, seleccione los datos para los que desea calcular la media móvil (B2: B11 en este ejemplo)
- En la opción Intervalo, ingrese 3 (ya que estamos calculando un promedio móvil de tres puntos)
- En el Rango de salida, ingrese la celda donde desea obtener los resultados. En este ejemplo, estoy usando C2 como rango de salida
- Haga clic en Aceptar
Los pasos anteriores le darían el resultado de la media móvil como se muestra a continuación.
Tenga en cuenta que las dos primeras celdas de la columna C tienen el resultado de un error # N / A. Esto se debe a que es un promedio móvil de tres puntos y necesita al menos tres puntos de datos para dar el primer resultado. Entonces, los valores de promedio móvil reales comienzan después del tercer punto de datos en adelante.
También notará que todo lo que ha hecho este paquete de herramientas de análisis de datos es aplicar una fórmula PROMEDIO a las celdas. Entonces, si desea hacer esto manualmente sin el paquete de herramientas de análisis de datos, ciertamente puede hacerlo.
Sin embargo, hay algunas cosas que son más fáciles de hacer con el paquete de herramientas de análisis de datos. Por ejemplo, si desea obtener el valor de error estándar, así como el gráfico de la media móvil, todo lo que necesita hacer es marcar una casilla y será parte de la salida.
Calcular promedios móviles (SMA, WMA, EMA) usando fórmulas en Excel
También puede calcular las medias móviles utilizando la fórmula PROMEDIO.
De hecho, si todo lo que necesita es el valor de la media móvil (y no el error estándar o el gráfico), usar una fórmula puede ser una opción mejor (y más rápida) que usar el Paquete de herramientas de análisis de datos.
Además, el paquete de herramientas de análisis de datos solo proporciona el promedio móvil simple (SMA), pero si desea calcular WMA o EMA, debe confiar solo en fórmulas.
Calcular la media móvil simple usando fórmulas
Suponga que tiene el conjunto de datos como se muestra a continuación y desea calcular la SMA de 3 puntos:
En la celda C4, ingrese la siguiente fórmula:
= PROMEDIO (B2: B4)
Copie esta fórmula para todas las celdas y le dará la SMA para cada día.
Recuerde: al calcular la SMA con fórmulas, debe asegurarse de que las referencias en la fórmula sean relativas. Esto significa que la fórmula puede ser = PROMEDIO (B2: B4) o = PROMEDIO ($ B2: $ B4), pero no puede ser = PROMEDIO ($ B $ 2: $ B $ 4) o = PROMEDIO (B $ 2: B $ 4 ). La parte del número de fila de la referencia debe estar sin el signo de dólar. Puede leer más sobre referencias absolutas y relativas aquí.
Como estamos calculando un promedio móvil simple (SMA) de 3 puntos, las dos primeras celdas (para los dos primeros días) están vacías y comenzamos a usar la fórmula a partir del tercer día en adelante. Si lo desea, puede usar los dos primeros valores tal cual y usar el valor SMA del tercero en adelante.
Calcular la media móvil ponderada mediante fórmulas
Para WMA, necesita conocer los pesos que se asignarían a los valores.
Por ejemplo, suponga que necesita calcular el WMA de 3 puntos para el siguiente conjunto de datos, donde el 60% del peso se asigna al último valor, el 30% al anterior y el 10% al anterior.
Para hacer esto, ingrese la siguiente fórmula en la celda C4 y cópiela para todas las celdas.
= 0,6 * B4 + 0,3 * B3 + 0,1 * B2
Dado que estamos calculando un promedio móvil ponderado de 3 puntos (WMA), las dos primeras celdas (para los dos primeros días) están vacías y comenzamos a usar la fórmula a partir del tercer día en adelante. Si lo desea, puede usar los dos primeros valores tal cual y usar el valor WMA del tercero en adelante.
Calcular la media móvil exponencial mediante fórmulas
La media móvil exponencial (EMA) otorga un mayor peso al último valor y los pesos siguen disminuyendo exponencialmente para los valores anteriores.
A continuación se muestra la fórmula para calcular la EMA para una media móvil de tres puntos:
EMA = [Valor más reciente - Valor de EMA anterior] * (2 / N + 1) + EMA anterior
… donde N sería 3 en este ejemplo (ya que estamos calculando una EMA de tres puntos)
Nota: Para el primer valor de la EMA (cuando no tenga ningún valor anterior para calcular la EMA), simplemente tome el valor como está y considérelo como el valor de la EMA. A continuación, puede utilizar este valor en el futuro.Suponga que tiene el siguiente conjunto de datos y desea calcular la EMA de tres períodos:
En la celda C2, ingrese el mismo valor que en B2. Esto se debe a que no existe un valor previo para calcular la EMA.
En la celda C3, ingrese la siguiente fórmula y cópiela para todas las celdas:
= (B3-C2) * (2/4) + C2
En este ejemplo, lo he mantenido simple y he utilizado el último valor y el valor de EMA anterior para calcular el EMA actual.
Otra forma popular de hacer esto es calculando primero el promedio móvil simple y luego usándolo en lugar del último valor real.
Agregar una línea de tendencia de media móvil a un gráfico de columnas
Si tiene un conjunto de datos y está creando un gráfico de barras con él, también puede agregar la línea de tendencia de la media móvil con unos pocos clics.
Suponga que tiene un conjunto de datos como se muestra a continuación:
A continuación se muestran los pasos para crear un gráfico de barras con estos datos y agregar una línea de tendencia de promedio móvil de tres partes a este gráfico:
- Seleccione el conjunto de datos (incluidos los encabezados)
- Haga clic en la pestaña Insertar
- En el grupo Gráfico, haga clic en el icono "Insertar columna o gráfico de barras".
- Haga clic en la opción Gráfico de columnas agrupadas. Esto insertará el gráfico en la hoja de trabajo.
- Con el gráfico seleccionado, haga clic en la pestaña Diseño (esta pestaña solo aparece cuando se selecciona el gráfico)
- En el grupo Diseños de gráficos, haga clic en "Agregar elemento de gráfico".
- Coloque el cursor sobre la opción "Línea de tendencia" y luego haga clic en "Más opciones de línea de tendencia"
- En el panel Formato de línea de tendencia, seleccione la opción "Media móvil" y establezca el número de períodos.
¡Eso es! Los pasos anteriores agregarían una línea de tendencia móvil a su gráfico de columnas.
En caso de que desee insertar más de una línea de tendencia de media móvil (por ejemplo, una para 2 períodos y otra para 3 períodos), repita los pasos del 5 al 8).
También puede utilizar los mismos pasos para insertar una línea de tendencia de promedio móvil en un gráfico de líneas.
Formateo de la línea de tendencia de la media móvil
A diferencia de un gráfico de líneas normal, una línea de tendencia de promedio móvil no permite mucho formato. Por ejemplo, si desea resaltar un punto de datos específico en la línea de tendencia, no podrá hacerlo.
Algunas cosas que puede formatear en la línea de tendencia incluyen:
- Color de la línea. Puede usar esto para resaltar una de las líneas de tendencia haciendo que todo en el gráfico sea de color claro y haciendo que la línea de tendencia aparezca con un color brillante.
- El espesor de la linea
- El transparencia de la linea
Para formatear la línea de tendencia de la media móvil, haga clic derecho sobre ella y luego seleccione la opción Formatear línea de tendencia.
Esto abrirá el panel Formato de línea de tendencia a la derecha. Este panel tiene todas las opciones de formato (en diferentes secciones: relleno y línea, efectos y opciones de línea de tendencia).
