Resolveremos hoy un problema interesante de búsqueda múltiple con el matiz que se condiciona el cálculo a devolver.
Veamos el planteamiento para entender algo mejor el problema:
haz clic en la imagen
La idea es obtener, según la matriz de información en B3:E6, y para cada registro de la tabla principal en H3:J18 la fecha de vencimiento correspondiente a las condiciones de B3:E6.
Por ejemplo, el primer registro corresponde a
-la Categoría: cat1
-el Concepto: conc1
al que se le aplicará un vencimiento de 15 días;
en nuestro ejemplo habrá que sumar a 28/06/2017 + 15 días con resultado: 13/07/2017.
En otros registros, se sumarán meses naturales o años completos...
Esto es, el cálculo variará según el tipo de intervalo (día, mes, año).
Esta doble búsqueda la gestionaremos con las funciones INDICE y COINCIDIR pero matricialmente ejecutadas... lo que nos permitirá recuperar del rango B3:E6 por un lado las uds y por otro el tipo (día, mes o año).
Veamos nuestras formulaciones, en K3 insertamos (recuerda validar presionando Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($E$3:$E$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));"")
la clave del asunto es que concatenando elementos de Categoría + Concepto podremos recuperar el valor deseado...
Luego podremos copiar la fórmula al resto del rango: K4:K18.
haz clic en la imagen
De forma similar para el siguiente dato en L3 insertamos (validar con Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($D$3:$D$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));0)
y copiamos al resto del rango L4:L18.
Con los valores recuperados de 'Tipo' y 'Uds' ya estamos en disposición de calcular el vencimiento de cada registro...
En M3:
=SI.ERROR(ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3));"")
haz clic en la imagen
Lo interesante de esta fórmula ELEGIR es que a partir del 'Tipo' recuperado con la matricial anterior,
nos permite seleccionar qué cálculo realizar:
ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3))
la función COINCIDIR sobre la constante matricial {"día";"mes";"año"} devuelve un número índice 1,2 ó 3, que tomaremos como indicador de posición para optar por un cálculo u otro.
Si la coincidencia del tipo es 1, i.e., el 'Tipo' es día, entonces calcularemos: J3+L3 (fecha + Uds)
Si la coincidencia del tipo es 2, i.e., el 'Tipo' es mes, entonces calcularemos: FECHA.MES(J3;L3) (sumamos x meses a la fecha)
Si la coincidencia del tipo es 3, i.e., el 'Tipo' es año, entonces calcularemos: FECHA.MES(J3;12*L3) (sumamos x años a la fecha)
Con la función SI.ERROR gestionamos en todos los casos el error en las búsquedas...
Veamos el planteamiento para entender algo mejor el problema:
haz clic en la imagen
La idea es obtener, según la matriz de información en B3:E6, y para cada registro de la tabla principal en H3:J18 la fecha de vencimiento correspondiente a las condiciones de B3:E6.
Por ejemplo, el primer registro corresponde a
-la Categoría: cat1
-el Concepto: conc1
al que se le aplicará un vencimiento de 15 días;
en nuestro ejemplo habrá que sumar a 28/06/2017 + 15 días con resultado: 13/07/2017.
En otros registros, se sumarán meses naturales o años completos...
Esto es, el cálculo variará según el tipo de intervalo (día, mes, año).
Esta doble búsqueda la gestionaremos con las funciones INDICE y COINCIDIR pero matricialmente ejecutadas... lo que nos permitirá recuperar del rango B3:E6 por un lado las uds y por otro el tipo (día, mes o año).
Veamos nuestras formulaciones, en K3 insertamos (recuerda validar presionando Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($E$3:$E$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));"")
la clave del asunto es que concatenando elementos de Categoría + Concepto podremos recuperar el valor deseado...
Luego podremos copiar la fórmula al resto del rango: K4:K18.
haz clic en la imagen
De forma similar para el siguiente dato en L3 insertamos (validar con Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($D$3:$D$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));0)
y copiamos al resto del rango L4:L18.
Con los valores recuperados de 'Tipo' y 'Uds' ya estamos en disposición de calcular el vencimiento de cada registro...
En M3:
=SI.ERROR(ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3));"")
haz clic en la imagen
Lo interesante de esta fórmula ELEGIR es que a partir del 'Tipo' recuperado con la matricial anterior,
nos permite seleccionar qué cálculo realizar:
ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3))
la función COINCIDIR sobre la constante matricial {"día";"mes";"año"} devuelve un número índice 1,2 ó 3, que tomaremos como indicador de posición para optar por un cálculo u otro.
Si la coincidencia del tipo es 1, i.e., el 'Tipo' es día, entonces calcularemos: J3+L3 (fecha + Uds)
Si la coincidencia del tipo es 2, i.e., el 'Tipo' es mes, entonces calcularemos: FECHA.MES(J3;L3) (sumamos x meses a la fecha)
Si la coincidencia del tipo es 3, i.e., el 'Tipo' es año, entonces calcularemos: FECHA.MES(J3;12*L3) (sumamos x años a la fecha)
Con la función SI.ERROR gestionamos en todos los casos el error en las búsquedas...
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
