El suavizado de datos de gráficos de Excel con una media móvil fija funciona cuando se tiene experiencia con los datos y lo variables que son. Pero, si los datos cambian o son nuevos, usted o la dirección pueden querer experimentar con un número diferente de períodos en el promedio de suavizado.
En este ejemplo, se utiliza una fórmula OFFSET para crear un promedio a través de un rango dinámico. Usted escribe el número de meses que desea utilizar para un período de suavizado y el PROMEDIO y el OFFSET calculan cuántas celdas se deben promediar.
Antes de hacer que el PROMEDIO sea dinámico, comencemos por entender cómo funciona el OFFSET. En la siguiente figura, la celda G26 contiene la fórmula para suavizar los datos de la fila 20,
=PROMEDIO(OFFSET(G20,0,0,1,-3))
OFFSET especifica el rango sobre el que el PROMEDIO suavizará los datos.
Esta fórmula es un paso intermedio para ayudarte a entender cómo funciona la fórmula dinámica. La función OFFSET devuelve un rango sobre el que actuará AVERAGE. Los atributos de OFFSET son,
OFFSET(referencia,filas,columnas,,)
En este ejemplo, el rango que OFFSET calcula comienza en G20 y tiene un desplazamiento de fila o columna cero. En otras palabras, la esquina superior izquierda del rango que OFFSET calcula es sólo G20. No será desplazado o compensado por ninguna fila o columna. El rango calculado será de 1 fila e incluirá la celda actual G26 y dos más a la izquierda (eso es un -3). Así, este rango calculado será G20:E20.
Copia esa fórmula en G26 a la derecha y verás que promedia la celda de datos que está por encima de ella en la fila 20 y las dos celdas anteriores a la izquierda.
Así que aquí está el truco que hará que esto sea dinámico y que promedie a través del rango que elijas. ¿Qué pasa si sustituyes una referencia de celda por ese -3 en la fórmula?
En la figura de abajo la fórmula en la celda G26 ha sido modificada para que el sea especificado por el número en la celda E24,
APROVECHAMIENTO(OFFSET(G20,0,0,1,-$E$24))
$E$24 es la celda sombreada en verde. El número que escriba en la celda E24 determinará el ancho de las celdas utilizadas para promediar.
Al introducir un valor en E24 cambiará el rango utilizado para suavizar los datos.
En este punto podría utilizar un spinner o un menú desplegable para dar a su usuario la opción de elegir el ancho del promedio de suavizado. El resultado de su selección debería ir en la celda E24.
Descargar los datos del gráfico de Excel de suavizado con suavizado fijo o dinámico
Hay un problema con esta disposición. Si el usuario introduce un número mayor en E24 que las celdas a la izquierda, entonces OFFSET incluirá las celdas en blanco y la etiqueta. Esto dará un promedio incorrecto. Si E24 es tan grande que OFFSET se sale de la hoja, entonces AVERAGE produce un error. ¿Qué hacer? Necesitamos una solución que no sólo detenga un error, como ISERROR, necesitamos una que detenga las respuestas incorrectas.
En esta siguiente figura la fórmula es la celda G26 ha sido ajustada. La fila 25 tiene una serie de números que son los límites de los rangos válidos a la izquierda. La nueva fórmula en la celda G26 mira a la fila 25 y si el número en la celda E24 es mayor que el límite en la fila 25, entonces se produce NA(). Esta nueva fórmula de corrección de errores en G26 es,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Cuando un gráfico de Excel hace referencia a NA() en una celda no traza nada. Eso evita que la línea de suavizado caiga a cero.
Modifique la fórmula de suavizado dinámico para evitar errores.
Problemas con los datos de suavizado y cómo evitarlos
Ahora puede dejar que su usuario pruebe cualquier período de suavizado que desee. Sin embargo, (parece que siempre hay un «sin embargo» o un «pero, en este caso») hay un problema con los datos de suavizado. El suavizado elimina la volatilidad, pero también significa que no verás los cambios de dirección, los puntos de inflexión o las nuevas tendencias hasta aproximadamente la mitad del periodo de suavizado. Usted está detrás de la curva.
Lo mejor de ambos mundos es utilizar una función de suavizado de media ponderada. De esa manera usted puede dar más peso a los datos a corto plazo para que usted vea las tendencias, pero usted puede suavizar sobre la base de datos más antiguos para eliminar parte de la volatilidad. Parece un buen caso para otro artículo, Suavizar datos de gráficos de Excel utilizando una media ponderada.
Descargue el archivo de ejemplo para suavizar datos de gráficos de Excel con suavizado móvil o dinámico
Artículos relacionados,
- Suavizar datos de gráficos de Excel con media móvil
- Suavizar datos de gráficos de Excel utilizando una media ponderada