Búsqueda de referencias cruzadas en Excel.

Tiempo atrás planteé un ejercicio de cómo emplear la herramienta Suma condicional, en la que ser realizaba una suma de datos referenciado al cumplimiento de varias condiciones; en realidad era una Búsqueda encubierta de datos sobre una tabla.
En esta ocasión nos plantea un lector el siguiente problema:

...Tengo una lista con los pagos a realizar cada mes. En columnas tengo entre otros (y por simplificar) 'empresa' 'banco' y los distintos meses 'ene', 'feb'…,'nov' y 'dic'. Intento tener una formula que me resuelva, lo siguiente: empresa (lista desplegable) banco (lista desplegable) mes (lista desplegable) = resultado...

digamos que tenemos la siguiente estructura de datos:

Para obtener un resultado a esta situación expondremos dos escenarios posibles:

• Los elementos del campo 'Empresa' son únicos, es decir, no se pueden repetir.


• Los elementos del campo 'Empresa' son repetibles

Previo a cualquiera de ambos trabajos definimos los siguientes nombres en cada una de las hojas de trabajo:

abr_09 ='con repetecion'!$G$2:$G$9 ago_09 ='con repetecion'!$K$2:$K$9 Banco ='con repetecion'!$B$2:$B$9 dic_09 ='con repetecion'!$O$2:$O$9 Empresa ='con repetecion'!$A$2:$A$9 ene_09 ='con repetecion'!$D$2:$D$9 feb_09 ='con repetecion'!$E$2:$E$9 jul_09 ='con repetecion'!$J$2:$J$9 jun_09 ='con repetecion'!$I$2:$I$9 mar_09 ='con repetecion'!$F$2:$F$9 may_09 ='con repetecion'!$H$2:$H$9 nov_09 ='con repetecion'!$N$2:$N$9 oct_09 ='con repetecion'!$M$2:$M$9 orden ='con repetecion'!$C$2:$C$9 sep_09 ='con repetecion'!$L$2:$L$9

abr_09 ='sin repetir'!$G$2:$G$9 ago_09 ='sin repetir'!$K$2:$K$9 Banco ='sin repetir'!$B$2:$B$9 dic_09 ='sin repetir'!$O$2:$O$9 Empresa ='sin repetir'!$A$2:$A$9 ene_09 ='sin repetir'!$D$2:$D$9 feb_09 ='sin repetir'!$E$2:$E$9 jul_09 ='sin repetir'!$J$2:$J$9 jun_09 ='sin repetir'!$I$2:$I$9 mar_09 ='sin repetir'!$F$2:$F$9 may_09 ='sin repetir'!$H$2:$H$9 nov_09 ='sin repetir'!$N$2:$N$9 oct_09 ='sin repetir'!$M$2:$M$9 orden ='sin repetir'!$C$2:$C$9 sep_09 ='sin repetir'!$L$2:$L$9

Adicionalmente para semi-automatizar las búsquedas insertaremos tres celdas validadas, cada una de ellas vinculadas a un rango de elementos de nuestra tabla de datos.

Para resolver el primero de los casos, i.e., que los elementos del campo 'Empresa' sean únicos, podremos plantear una 'Suma condicional', obviamente en su forma matricial:
{=SUMA(SI(Empresa=$Q$2;SI(Banco=$R$2;BUSCARH($S$2;$D$1:$O$9;1+COINCIDIR($Q$2;Empresa);0))))}
con esta función SUMA, aprovechándonos de que los cruces entre estas tres variables son únicos, obtenemos el valor buscado. Hemos indicado con los dos primeros SI la coincidencia en el rango 'Empresa' y con el rango 'Banco' de los elementos a buscar, para determinar el últimos criterio de búsqueda he empleado la función BUSCARH
BUSCARH($S$2;$D$1:$O$9;1+COINCIDIR($Q$2;Empresa);0)
en la que buscamos, previa coincidencia de las dos condiciones anteriores, el 'mes' requerido dentro de la matriz $D$1:$O$9 (datos correspondientes exclusivamente a los meses del año), con esta operación localizaré el importe del mes concreto; sólo queda entonces hacerlo coincidir con la fila adecuada, para lo que incluimos como Indicador de fila la función
1+COINCIDIR($Q$2;Empresa);0)
con la que resulta el número de fila que corresponde a la empresa buscada. Fijémonos que encuentra un único valor de la empresa buscada en el rango 'Empresa'.
Por ejemplo, si queremos buscar el importe en euros que corresponda a la Empresa6, con el Banco2 del mes de sep-09, obtendremos:

haz click en la imagen

Para resolver el segundo de los casos, es decir, que los elementos del campo 'Empresa' puedan ser repetidos, podremos plantear una 'Búsqueda condicionada'; se realizará en dos partes:
Una en la que obtengo el valor de orden en la tabla de aquel elemento buscado que cumpla simultaneamente las condiciones de coincidencia de 'Empresa' y 'Banco', mediante la siguiente fórmula matricial:
{=SUMA(SI(Empresa=$Q$2;SI(Banco=$R$2;orden)))}
La siguiente parte emplearé el resultado de orden obtenido con la función anterior, y empleando la función directa INDICE:
=INDICE($A$2:$O$9;R5;COINCIDIR($S$2;$A$1:$O$1;))
con la que busco en el rango de datos $A$2:$O$9 el número de fila obtenido con la anterior fórmula matricial y el número de columna lo obtengo con la función COINCIDIR, igual que hice en el apartado anterior.

haz click en la imagen

Adjunto fichero para su análisis.

Triple busqueda-cr...

Hosted by eSnips