Cálculos con los últimos n valores de una serie en Excel
lunes, enero 09, 2012
Un compañero de trabajo quería calcular el promedio de los últimos 12 valores de una serie. En su caso, el precio promedio de una serie de productos
Para calcular el promedio de los últimos doce meses usaremos esta fórmula
=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-12,0,12,1))
A medida que agreguemos (o quitemos) valores, la fórmula se ajustará automáticamente.
Sobre el funcionamiento de la función DESREF ya hemos escrito en este blog. El “truco” aquí es que usamos CONTAR para encontrar la última celda no vacía (suponemos que contienen números) y luego “retrocedemos” 12 puntos atrás.
En nuestro ejemplo
CONTAR(B5:B160)-12
da 2 (14-12) lo que nos “lleva” a B7; luego nos extendemos hasta B18 usando 12 como argumento en DESREF.
Un detalle a tomar en cuenta es que el rango de valores debe ser continuo (sin celdas vacías entre dos o más valores); en caso contrario el resultado será incorrecto.
Podemos modificar la fórmula de manera que la cantidad de valores a considerar en el cálculo se determine de forma dinámica
Usamos el valor en la celda B1 como argumento para determinar la cantidad de meses a tomar en cuenta
=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-B1,0,B1,1))
Apéndice: en esta nota muestro como resaltar las celdas comprendidas en el cálculo con formato condicional
6 comments:
Hola jorge:
Por favor como se puede hacer en formato condicional colorear las celdas de los números que se esta calculando los promedios indicados en el ejemplo.
Muchas gracias un placer saludarlo.
No estoy seguro de haber entendido. ¿Se trata de colorear las celdas del rango evaluado(B7:B18 en nuestro ejemplo)?
A sí es Jorge ¿Se trata de colorear las celdas o los números del rango evaluado(B7:B18 en su ejemplo)? que tiene como título.
Cálculos con los últimos n valores de una serie.
Atentamente: Winny
OK!,
1 - Seleccionamos el rango a evaluar (B5:B160 en nuestro ejemplo)
2 - en formato condicional-nueva regla-fórmula ponemos ésta
Y(FILA(C5)>=CONTAR($C$5:$C$160)+4-$C$1+1,FILA(C5)<=CONTAR($C$5:$C$160)+4)
poner atención a los $ en la fórmula.
3 - elegimos el fondo de nuestro agreado
4 - Listo
Cómo funciona y otras alternativas las explicaré en una nota.
Hola Buenas noche Jorge..
vengo siguiendo tu blog y no he encontrado conveniente molestarte para realizarte una pregunta ya que me hace muy explicito a lo que escribes.. pero en esta ocasión me surgió una duda....
en la funcion DESREF(B5,CONTAR(B5:B160)-12,0,12,1)y en referencia al link explicando la funcion DESREF me queda claro hasta este punto: -12,0..y vuelves a escribir 12... segun en tu link los ultimos 2 argumentos se refieren a "argumentos de rango" ...
la pregunta es: el # 12 (en este caso)se repite SIEMPRE dentro de los argumentos de celda como argumentos para rango .. y si es asi .. por que
o como lo definirias el numero 12 en este caso...
espero pues tu comentario...
saludos desde MX D.F
Marco,
DESREF permite calcular dos tipos de rangos a partir de una celda determinada (el primer argumento de la función):
# - la dirección de una celda específica, definiendo con los dos siguientes argumentos a que distancia se encuentra de la celda del primer argumento (en nuestro ejemplo calculamos cuantas filas con CONTAR)
# - la dirección de un rango de celdas continuas, con el segundo par de argumentos,
Como explico en la nota,CONTAR(B5:B160)-12 calcula donde empieza el rango (12 celdas antes la última celda de la lista) y el segundo 12 nos lleva al final de la lista.
Publicar un comentario