jueves, 18 de enero de 2018

Doble búsqueda con cálculo condicionado

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:



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.



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));"")



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...

4 comentarios:

  1. uenos días

    Estoy creando una formula cual al escribir un numero llamado RTN en una celda me devulve un nombre luego de una consulta web, por ejemplo pongo el numero 08019002275485 en la casilla RTN y me devuelve los resultados del cual necesito el nombre "EMPRESA DE CONSULTORIA EN INGIENERIA CONSULTING S DE R L"y asi va cambiando segun el numero RTN que se ponga en la celda

    La web es http://enlacertn.sar.gob.hn

    El codigo vba es el siguiente

    Function Registro(RTN As String)
    Dim IE As Object
    Dim Respuesta As String

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://enlacertn.sar.gob.hn"
    IE.Visible = True
    While IE.busy
    DoEvents
    Wend
    IE.document.All.Item("txtCriterio").Value = RTN
    IE.document.All("btnBuscar").Click
    Application.Wait (Now + TimeValue("0:00:03"))

    Do Until IE.ReadyState = 4
    DoEvents
    Loop
    Respuesta = IE.document.getElementById("LblNombre").innertext
    Registro = Respuesta
    IE.Quit
    End Function

    necesito extraer el elemento el resultado del Id=LblNombre pero me devulve un error #valor la funcion

    Agradeceria mucho la ayuda y explicacion

    Saludos,

    ResponderEliminar
    Respuestas
    1. Hola,
      'rascar' una web siempre es complicado...¿te has asegurado que en la web, su html está identificado por Id y no por otra propiedad?...
      A veces los códigos web son javascript o similares y no son operativos con este tipo de macros...
      En otros casos simplemente son web protegidas...

      En principio no se ve nada raro en tu código que haga suponer que no funcionará

      slds

      Eliminar
  2. Jesucristo te bendiga Ismael esto era lo que estaba buscando.

    ResponderEliminar
    Respuestas
    1. Muchas gracias!
      me alegro encontraras lo que necesitabas

      UN saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.