Aclaración: no soy un experto en estadística y mi intención en este tutorial no es cubrir todo sobre las medias móviles. Solo pretendo mostrarte 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 sabes 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 o AVERAGE en Excel para hacer esto).
Una media móvil (también llamado 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 las medias móviles.
las medias móviles 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).
Uno de los beneficios de usar las medias móviles es que le brinda la tendencia y suaviza las fluctuaciones hasta cierto punto. Por ejemplo, en caso de que haya un día realmente caluroso, la media móvil de tres días de la temperatura aún garantizaría que el valor promedio se haya suavizado (en lugar de mostrarle un valor realmente alto que podría ser un valor atípico).
Tipos de medias móviles
Hay tres tipos de medias móviles:
- Media móvil simple (SMA)
- Media móvil ponderado (WMA)
- Media móvil exponencial (EMA)
Media móvil simple (SMA)
Esta es la media simple de los puntos de datos en la duración dada.
En nuestro ejemplo de temperatura diaria, cuando simplemente toma una media de los últimos 10 días, se obtiene la media 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 o AVERAGE (esto se explica más adelante en este tutorial).
Media móvil ponderada (WMA)
Digamos que el clima se vuelve más fresco con cada día que pasa y está utilizando una media 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.
Esto se 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 una media 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 medias móviles en Excel.
Calcular la media 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 las media móviles simples.
Se llama Paquete de herramientas de análisis de datos.
Antes de que pueda 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 click 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 la media móvil de los últimos tres intervalos.
A continuación, se muestran los pasos para usar el análisis de datos para calcular una media 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 una media móvil de tres puntos y necesita al menos tres puntos de datos para dar el primer resultado. Entonces, los valores de media 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 medias móviles (SMA, WMA, EMA) usando fórmulas en Excel
También puede calcular las medias móviles utilizando la fórmula PROMEDIO o AVERAGE.
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 la media o 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.
Dado que estamos calculando un media 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 media 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 media 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 media 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.
Con estos pasos anteriores, se agregaría 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 media 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 media 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 grosor de la línea
- La transparencia de la línea
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).
¡Y eso es todo! espero que te haya gustado el tutorial. De ser así, házmelo saber en los comentarios.
Creador de contenido digital y técnico superior universitario (TSU) en comercio internacional. Apasionado por las hojas de cálculo y por compartir contigo las mejores plantillas de excel y recursos.