martes, 21 de junio de 2016

El separador espacios como intersección de rangos para búsquedas cruzadas.

Cuando tenemos que recuperar datos de una tabla de valores es muy frecuente utilizar las funciones de búsqueda, como INDICE + COINCIDIR, o BUSCARV o DESREF...
Hoy veremos una curiosidad de la función SUMA que nos permitirá obtener el dato de una Tabla que corresponda a un referencia cruzada.
Tenemos la siguiente tabla:

La función SUMA para búsquedas cruzadas.



Lo que deseamos conseguir es el valor correspondiente a un Mes y un Color concreto.

Para facilitar la búsqueda hemos asignado Nombres definidos a los siguientes rangos de celdas:
ene. =Hoja1!$C$3:$F$3
feb. =Hoja1!$C$4:$F$4
mar. =Hoja1!$C$5:$F$5
abr. =Hoja1!$C$6:$F$6
may. =Hoja1!$C$7:$F$7
jun. =Hoja1!$C$8:$F$8

rojo =Hoja1!$C$3:$C$8
amarillo =Hoja1!$D$3:$D$8
verde =Hoja1!$E$3:$E$8
azul =Hoja1!$F$3:$F$8


Obviamente podríamos optar por la combinación de las funciones INDICE y COINCIDIR:
=INDICE(C3:F8;COINCIDIR("mar.";B3:B8;0);COINCIDIR("verde";C2:F2;0))


También por otra algo más sofisticada con la función SUMAPRODUCTO:
=SUMAPRODUCTO(C3:F8*(C2:F2="verde")*(B3:B8="mar."))


Pero hoy emplearemos la sencilla función SUMA.
Existe una manera de informar los argumentos casi desconocida donde no empleamos los separadores de argumentos (ni : ni ;) y que causa un efecto sorprendente.
Si escribiéramos en una celda cualquiera:
=SUMA(C5:F5 E3:E8)
ejecutando normalmente (no matricial) obtendríamos el valor correspondiente al cruce de ambos rangos!!.

Notemos la forma peculiar de añadir los rangos... sin separadores.
La explicación es que emplear este operador (espacio) trabaja como un Operador de intersección, esto es, devuelve una referencia a las celdas comunes de los dos rangos.


Empleando, y extendiendo la técnica a nuestro ejemplo:



La función empleada en la celda J3 de nuestro ejemplo:
=SUMA(INDIRECTO(H3) INDIRECTO(I3))
nos retorna el valor esperado...


Una última apreciación... funcionaría incluso sin la función suma, únicamente indicando los rangos:
=C5:F5 E3:E8

2 comentarios: