miércoles, 12 de noviembre de 2014

Búsqueda doble sobre cualquier parte de una tabla de Excel.

Aunque parece un tema más que trillado, en el ejercicio de hoy iremos un paso más allá...
Buscaremos un elemento sobre cualquier parte de una tabla o rango de celdas y retornaremos el valor correspondiente a esa fila que deseemos.

...Necesito generar una fórmula similar al buscarv, pero que tenga los siguientes argumentos:
=buscador(A6:F10,"Miranda","nombre")
Es decir, la macro debe analizar toda la base de datos hasta encontrar a "Miranda" y arrojar como resultado su valor equivalente de la columna "nombre":



la idea es, por tanto, clara.. buscaremos en la totalidad de nuestra tabla el valor buscado (que podrá estar en cualquier fila o columna!!), y una vez localizado, obtendremos su equivalente al nombre de campo indicado.

Como se aprecia en la imagen en la celda H6 hemos añadido una Validación de datos tipo Lista en la que desplegaremos el nombre del campo del que queremos obtener el resultado.
Mientras que en I6 escribiremos cualquier valor único (en caso de duplicados fallaría nuestra fórmula) que realmente queremos localizar en cualquier parte de nuestra tabla (rango A6:F10):


La fórmula matricial deseada aparece en J6:
=INDIRECTO(DIRECCION(K.ESIMO.MAYOR(SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0);1);COINCIDIR($H$6;$A$6:$F$6;0)))


Fijémonos de la potencia de esta fórmula.. ya que con ella no necesito indicar en qué columna debe encontrar nuestro valor buscado (celda I6), puesto que lo busca en toda la tabla... para localizar la fila donde se halla.
Posteriormente, aprovechándonos de la Validación de datos en H6 y de la ya conocida función COINCIDIR obtenemos la celda con el resultado final.

La clave de esta matricial reside en el primer argumento de la función DIRECCION:
K.ESIMO.MAYOR(SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0);1)
donde con el condicional SI:
SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0)
obtendríamos una matriz de ceros y un número de fila, para el caso o celda donde se de la coincidencia con el valor buscado. Forzamos que el condicional nos devuelva el número de la fila en cuestión.

Podemos ver, a parte, el resultado de esa matriz:

Búsqueda doble sobre cualquier parte de una tabla de Excel.



Lo siguiente es sencillo.. basta aplicar la función K.ESIMO.MAYOR (xxxx; 1) para conseguir el valor más alto (el número de fila buscado) -también valdría la función MAX-.

Con esto y daría respuesta a la lectora del blog, sin emplear macros...
De todas formas, en una entrada posterior, daré el código para resolver esto con una función VBA personalizada.

6 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Hola Ismael,
    Gracias por la opción! Pero estoy necesitando una codificación. Por el momento he trabajado la siguiente, pero esta "función" necesita de 4 argumentos,
    Function MIBUSCADOR(RANGO As Range, TITULO1, DATO1, TITULO2)
    FILAS = RANGO.Rows.Count
    COLUMNAS = RANGO.Columns.Count
    For Y = 1 To COLUMNAS
    If RANGO.Cells(1, Y) = TITULO1 Then 'filas,columna
    COLUMNADATO = Y
    End If
    Next
    For X = 2 To FILAS
    If RANGO.Cells(X, COLUMNADATO) = DATO1 Then
    FILADATO = X
    End If
    Next
    For Y = 1 To COLUMNAS
    If RANGO.Cells(1, Y) = TITULO2 Then
    COLUMNARESPUESTA = Y
    End If
    Next
    MIBUSCADOR = RANGO.Cells(FILADATO, COLUMNARESPUESTA)
    End Function

    Yo quiero saber si es posible que mi macro busque en la totalidad de la matriz el valor que le digo, en este caso con solo tres argumentos algo asi, =mibuscador(matriz,"Miranda", "nombre") es decir busque el apellido "Miranda" tal cual, y bote el resultado de la columna que le indique, en este ejemplo "nombre".
    Gracias!!!!

    ResponderEliminar
    Respuestas
    1. Hola Gabriela,
      como indico al final del post, intentaré (en cuanto pueda) subir el código al blog.

      Un cordial saludo

      Eliminar
  3. Hola, Ismael. Antes he introducido un comentario pero no sé si lo he hecho bien, porque no aparece (aunque quizá sea porque haces un control previo de comentarios; si es así, como es lógico, no hace falta que publiques o atiendas a éste). En fin, por si acaso, lo vuelvo a intentar, marcando esta vez la casilla Avisarme.
    La cosa es lo siguiente: en el primer argumento de la función SI, ¿para que sirve multiplicar por 1 y hacer la igualdad con 1? Lo he probado sin eso y también funciona. Seguramente hay una razón pero se me escapa.
    En fin, muchas felicidades por tan estupendo blog y gracias por anticipado.

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      correcto, la parte
      SI(($I$6=$A$7:$F$10)*1=1;FILA(A7:F10);0)
      funciona también si *1 e igualarlo a 1, ya que sólo necesitamos un VERDADERO...

      Yo le he multiplicado para que sea más visual la matriz devuelta de ceros y un número de fila...

      Un saludo

      Eliminar
    2. Muchísimas gracias por tu pronta respuesta, Ismael.
      Saludos.

      Eliminar