Como sumar los ultimos n valores de un rango con celdas vacías

martes, marzo 18, 2014

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?
La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

tabla de datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))
tabla de datos

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango


El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente


En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula



4 comments:

Anónimo,  03 abril, 2014 19:26  

buenas tardes, necesito encontrar en una tabla cuyas filas van cambiando de orden, por lo que usualmente utilizo la funcion coincidir, tanto para las filas coo par alas columnas. el problmea me surge cuando en las columnas (meses del año, con inicio mes actual y ultima fila diciembre año +2) resulta que ya no tengo que buscar un mes en concreto de cada año, sino que tengo que buscar el vaor del mes de diciembre, y si no hay valor, buscar el mes anterior, y asi hasta que encuentre un mes del año en el que si haya valor (que siempre habra alguno con valor) .Sabria hacerlo con lo que cuentas aqui si siempre fuese la misma fila, pero al tener que buscar la fila que corresponda tambien, no se como formularlo.
muchas gracias!

Jorge Dunkelman 03 abril, 2014 20:24  

Hola, bastante difícil entender el problema sin ver el cuaderno. Te sugiero que me mandes el cuadderno o un ejemplo (el mail figura en el enlace Ayuda, en la parte superior de la plantilla).
En términos generales y tomando en cuenta lo poco que logro figurarme a partir de tu descripción, usaría un rango dinámico basado en la columna de los valores.

Anónimo,  15 abril, 2014 00:18  

Buenas tardes Jorge,
Por que cuando apreto las teclas control-shift-enter no me sale la formula dinamica entre corchetes?
Saludos, Ivan Castilla

Jorge Dunkelman 15 abril, 2014 09:42  

Iván, se trata de una fórmula matricial. ¿Estás apretando Ctrl-Shuft-Enter simultáneamente?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP