lunes, 28 de octubre de 2013

Promedio Móvil de x Meses

"Tengo un histórico con la cifra de ventas mensual y me gustaría poder calcular el promedio de ventas de los últimos x meses hasta la fecha de hoy".

No problemo. Lo resolveremos haciendo uso de las funciones SI.ERROR, PROMEDIO, DESREF, COINCIDIR, BUSCARV y HOY. Supongamos que tenemos la siguiente tabla con la fecha y su cifra de ventas. Preparamos además la entrada de datos, es decir, el número variable de meses para calcular el promedio:
Empezamos calculando la fecha actual. Para ello nos ponemos en la celda C4 y escribimos la fórmula  =HOY()
Seleccionamos el rango E3:E32 y le damos el nombre Datomes (como siempre lo podemos crear haciendo clic en el Cuadro de nombres, a la izquierda de la barra de fórmulas, escribiendo dicho nombre y pulsando Enter). Nos situamos ahora en C6 y escribimos la fórmula:
=SI.ERROR(PROMEDIO(DESREF(F2;COINCIDIR(BUSCARV(C4;Datomes;1);
Datomes);;-C2;));"No disponible")

Con la función BUSCARV localizamos la fecha actual en nuestra tabla de ventas. Al anidarla dentro de la función COINCIDIR, obtendremos el número de fila en el que se encuentra dicha fecha. A su vez, COINCIDIR se encuentra anidada dentro de la función DESREF y es el argumento de fila, esto es, partiendo de la referencia de celda F2 tiene que contar tantas filas como devuelva COINCIDIR(BUSCARV(C4;Datomes;1). Ya tenemos la fila referente al mes corriente, ahora nos queda indicarle desde qué mes queremos realizar el cálculo del promedio, es decir, de los últimos 12 meses, de los últimos 6 meses, etcétera. Esto lo resolvemos con el argumento alto de la función DESREF. En concreto tendrá que retroceder desde la fila de la fecha actual hasta el número de meses que le indiquemos en la celda de entrada C2, y por ello le ponemos signo negativo a dicha referencia (-C2).
Le he añadido la función SI.ERROR para que si indicamos un número de meses demasiado elevado no aparezca el error #¡REF! sino que aparezca un texto un poco más estético del tipo "No disponible":


Se me olvidaba... Para conseguir que el texto del rótulo de la media(B6) sea variable, es decir, que cambie en función del número de meses que escribamos en C2, tenemos que poner la siguiente fórmula en B6:  ="Media "&C2&" meses:"

4 comentarios:

  1. Buenos días,
    Muy bueno todas las ayudas pero no encuentro solución a un problema.
    Pongo un ejemplo. Tengo tiendas por todo el territorio donde vendo fruta, este territorio esta dividido en distribuidores que tienen varias tiendas, algunos, 3 otros 4, otros 5 y otros 6. Quiero sacar la media de venta por distribuidor, tienda y tipo de fruta y realizarlo con Tablas dinámicas. No todos los distribuidores venden de todo tipo de fruta

    ResponderEliminar
  2. Muchas gracias felipe. Por favor, mándame tu mail a Kiko.rial@gmail.com y te mando un ejemplo resuelto. Gracias

    ResponderEliminar
  3. Buenas noches Kiko, tengo una fórmula con la cual calculo el promedio del último trimestre y necesito que esta se me actualice automáticamente conforme voy ingresando datos.
    Por favor requiero esta ayuda con urgencia.

    Saludos y muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Carlos. En principio excel actualiza los cálculos cada vez que incorporas nuevos datos. Si no es así, comprueba en Opciones/Fórmulas, en el primer apartado, Opciones de Cálculo, que tienes marcado AUTOMÁTICO.

      Eliminar