Media Móvil a intervalos fijos

Descargar el archivo: MM10.xlsm

Si disponemos de una serie de datos, por ejemplo, 200 y deseamos hacer una media móvil de 10 (MM10) lo que haremos es tomar el promedio de los 10 primeros y luego para obtener el siguiente valor de la media móvil lo que hacemos es eliminar de nuestros cálculos el primero y tomar uno nuevo, y así sucesivamente. Esto en Excel se resuelve de una forma muy sencilla con la función PROMEDIO. Véase el ejemplo siguiente.

Primero generamos aleatorios en la columna B con la expresión:

=ELEGIR(ALEATORIO.ENTRE(1;4);ALEATORIO.ENTRE(1;9);ALEATORIO.ENTRE(10;99);ALEATORIO.ENTRE(100;999);ALEATORIO.ENTRE(1000;1999))

y luego los convertimos en valores con copiar y pegar con pegado especial valores.

En la celda C11 calculamos la primera media móvil de 10 valores (MM10) con la expresión:

=PROMEDIO(B2:B11)

Observe que el rango B2:B11 abarca los 10 primeros valores de la columna B y que no se ponen con dólares, por lo que al ser libres y copiar hacia abajo la fórmula el siguiente valor de la media móvil, situado en la celda C12, hace el promedio del rango B3:B12. Esto supone haber movido hacia abajo el rango una posición, pero siguen siendo 10 valores ya que estamos haciendo una media móvil de 10.

Realizar una media móvil es así de sencillo en Excel, pero ahora nos proponemos otro reto que consiste en tomar la media de los 10 primeros valores de la columna B, y anotarlos por ejemplo en la columna F, y luego tomar los siguientes 10 valores pero sin incluir a los anteriores, simplemente los que van de la posición 11 a la 20, y lo volvemos a anotar en la columna F, y así sucesivamente. Como tenemos 200 datos en la columna B y deseamos tomarlos de 10 en 10 al final tendremos 200/10 = 20 valores anotados en la columna F.

Esto se puede conseguir de tres formas:

• Método 1: de forma manual.
• Método 2: con fórmula
• Método 3: con macro

Método 2

En la colman F se obtiene la media de cada 10 valores de la columna B. Observe que se corresponden los valores obtenidos en la columna F con los marcados en azul.

La fórmula de la celda F2 se copia hacia abajo y es la siguiente.

=PROMEDIO(INDIRECTO("F"&E2*10-8&"C2";0):INDIRECTO("F"&E2*10+1&"C2";0))

Para marcar el color azul hemos utilizado Formato Condicional. La condición se ha aplicado a la celda C11 y luego se ha extendido hacia abajo. La fórmula de la condición es la que nos detecta los múltiplos de 10.

=RESIDUO(A11;10)=0