Resumen
Para calcular un promedio móvil o móvil, puede usar una fórmula simple basada en la función PROMEDIO con referencias relativas. En el ejemplo que se muestra, la fórmula en E7 es:
=AVERAGE(C5:C7)
A medida que se copia la fórmula, calcula un promedio móvil de 3 días basado en el valor de ventas del día actual y los dos días anteriores.
A continuación se muestra una opción más flexible basada en la función OFFSET que maneja períodos variables.
Acerca de las medias móviles
Un promedio móvil (también llamado promedio móvil) es un promedio basado en subconjuntos de datos en intervalos dados. El cálculo de un promedio a intervalos específicos suaviza los datos al reducir el impacto de las fluctuaciones aleatorias. Esto hace que sea más fácil ver las tendencias generales, especialmente en un gráfico. Cuanto mayor sea el intervalo utilizado para calcular una media móvil, más suavizado se produce, ya que se incluyen más puntos de datos en cada media calculada.
Explicación
Todas las fórmulas que se muestran en el ejemplo usan la función PROMEDIO con una referencia relativa configurada para cada intervalo específico. El promedio móvil de 3 días en E7 se calcula alimentando PROMEDIO con un rango que incluye el día actual y los dos días anteriores como este:
=AVERAGE(C5:C7) // 3-day average
Los promedios de 5 y 7 días se calculan de la misma manera. En cada caso, el rango proporcionado a PROMEDIO se amplía para incluir el número requerido de días:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Todas las fórmulas usan una referencia relativa para el rango proporcionado a la función PROMEDIO. A medida que las fórmulas se copian en la columna, el rango cambia en cada fila para incluir los valores necesarios para cada promedio.
Cuando los valores se trazan en un gráfico de líneas, el efecto de suavizado es claro:
Datos insuficientes
Si comienza las fórmulas en la primera fila de la tabla, las primeras fórmulas no tendrán suficientes datos para calcular un promedio completo, porque el rango se extenderá por encima de la primera fila de datos:
Esto puede ser un problema o no, según la estructura de la hoja de trabajo y si es importante que todos los promedios se basen en el mismo número de valores. La función PROMEDIO ignorará automáticamente los valores de texto y las celdas vacías, por lo que continuará calculando un promedio con menos valores. Por eso "funciona" en E5 y E6.
Una forma de indicar claramente que hay datos insuficientes es verificar el número de fila actual y abortar con #NA cuando hay menos de n valores. Por ejemplo, para el promedio de 3 días, podría usar:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
La primera parte de la fórmula simplemente genera un número de fila "normalizado", comenzando con 1:
ROW()-ROW($C$5)+1 // relative row number
En la fila 5, el resultado es 1, en la fila 6 el resultado es 2, y así sucesivamente.
Cuando el número de fila actual es menor que 3, la fórmula devuelve # N / A. De lo contrario, la fórmula devuelve una media móvil como antes. Esto imita el comportamiento de la versión Analysis Toolpak de Moving Average, que genera # N / A hasta que se alcanza el primer período completo.
Sin embargo, a medida que aumenta el número de períodos, eventualmente se quedará sin filas por encima de los datos y no podrá ingresar el rango requerido dentro de PROMEDIO. Por ejemplo, no puede configurar un promedio móvil de 7 días con la hoja de trabajo como se muestra, ya que no puede ingresar un rango que se extienda 6 filas por encima de C5.
Periodos variables con OFFSET
Una forma más flexible de calcular una media móvil es con la función OFFSET. OFFSET puede crear un rango dinámico, lo que significa que podemos configurar una fórmula donde el número de períodos es variable. La forma general es:
=AVERAGE(OFFSET(A1,0,0,-n,1))
donde n es el número de períodos a incluir en cada promedio. Como arriba, OFFSET devuelve un rango que se pasa a la función PROMEDIO. A continuación puede ver esta fórmula en acción, donde "n" es el rango con nombre E2. Comenzando en la celda C5, OFFSET construye un rango que se extiende hasta las filas anteriores. Esto se logra usando una altura igual a n negativo. Cuando E5 se cambia a otro número, la media móvil se vuelve a calcular en todas las filas:
La fórmula en E5, copiada, es:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Al igual que la fórmula original anterior, la versión con OFFSET también tendrá el problema de datos insuficientes en las primeras filas, dependiendo de cuántos períodos se den en E5.
En el ejemplo que se muestra, los promedios se calculan correctamente porque la función PROMEDIO ignora automáticamente los valores de texto y las celdas en blanco, y no hay otros valores numéricos por encima de C5. Entonces, mientras que el rango pasado a PROMEDIO en E5 es C1: C5, solo hay un valor para promediar, 100. Sin embargo, a medida que aumentan los períodos, OFFSET continuará creando un rango que se extiende por encima del inicio de los datos, y eventualmente se encontrará con la parte superior de la hoja de trabajo y devuelve un error #REF.
Una solución es "limitar" el tamaño del rango al número de puntos de datos disponibles. Esto se puede hacer usando la función MIN para restringir el número usado para la altura como se ve a continuación:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Esto parece bastante aterrador, pero en realidad es bastante simple. Estamos limitando la altura pasada a OFFSET con la función MIN:
MIN(ROW()-ROW($C$5)+1,n)
Dentro de MIN, el primer valor es un número de fila relativo, calculado con:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
El segundo valor dado a MIN es el número de períodos, n. Cuando el número de fila relativo es menor que n, MIN devuelve el número de fila actual a OFFSET para la altura. Cuando el número de fila es mayor que n, MIN devuelve n. En otras palabras, MIN simplemente devuelve el menor de los dos valores.
Una característica interesante de la opción OFFSET es que n se puede cambiar fácilmente. Si cambiamos n a 7 y graficamos los resultados, obtenemos un gráfico como este:
Nota: Una peculiaridad de las fórmulas DESPLAZADAS anteriores es que no funcionarán en Hojas de cálculo de Google, porque la función DESPLAZAMIENTO en Hojas de cálculo no permitirá un valor negativo para la altura o el ancho. La hoja de cálculo adjunta tiene fórmulas alternativas para las hojas de Google.
