¿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
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)))
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
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.
ResponderBorrarmuchas gracias!
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).
ResponderBorrarEn 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.
Buenas tardes Jorge,
ResponderBorrarPor que cuando apreto las teclas control-shift-enter no me sale la formula dinamica entre corchetes?
Saludos, Ivan Castilla
Iván, se trata de una fórmula matricial. ¿Estás apretando Ctrl-Shuft-Enter simultáneamente?
ResponderBorrarsi tengo un rango de números y quiero sumar los n primeros, como se haría ?
ResponderBorrarHay varias maneras; la más común es usar un rango dinámico (fijate en este post).
ResponderBorrarSUponiendo que los valores están en el rango A1:A10 y quieres sumar los primeros 3 valores, podrías usar esta fórmula
=SUMA($A$1:INDICE(A$1:A$10,3))
Para que la fórmula sea más dinámica podrías poner el número de filas a sumar en la celda C1, por ejemplo, y reescribir la fórmula de esta manera
=SUMA($A$1:INDICE(A$1:A$10,C1))
muchas gracias, tu aporte fue de gran ayuda :)
Borrar