lunes, 19 de diciembre de 2011

Búsqueda matricial triple sobre listados de Excel.

En otro foro en el que participo (cuando puedo), respondí una cuestión que me pareció interesante. Se trataba de averiguar si los registros de una Tabla coincidian con alguno de una segunda Tabla, situada en otra Hoja de cálculo de nuestro Excel:

...necesito realizar una búsqueda en dos diferentes hojas por ejemplo en hoja1 tengo 3 columnas con numero de parte cantidad y localidad y en hoja2 tengo las mismas 3 columnas con diferentes datos, necesito que si coinciden las tres columnas se ponga ok o algún valor en la columna 4 ...


Partiremos de dos listados de datos en hojas distintas, para cumplir con los requisitos de la consulta:

Búsqueda matricial triple sobre listados de Excel.


Se trata, por tanto, de identificar cuáles de los registros de la tabla de la Hoja1 coinciden (en los tres datos) con algún registro de la Tabla de datos de la Hoja2; que como vemos serán el primero y último de la lista...
El trabajo será construir una matriz o un rango sobre el que comparar nuestros tres valores. Expondré en primer lugar la función que nos resolverá la cuestión, para luego desgranarla y ver su funcionamiento. Nuestra función matricial (Ctrl+Mayusc+Enter) para cada registro de la Tabla de la Hoja1 será:
{=SI(ESERROR(BUSCARV(A2&B2&C2;campo1&campo2&campo3;1;0));"";"ok")}

Búsqueda matricial triple sobre listados de Excel.


Nuestra labor comenzó definiendo o asignando nombres a las columnas de datos de la tabla de datos de la Hoja2, en concreto serían:
campo1 =Hoja2!$A$2:$A$5
campo2 =Hoja2!$B$2:$B$5
campo3 =Hoja2!$C$2:$C$5
Este paso es muy importante, ya que además de la facilidad para crear nuestra matriz de búsqueda, nos permitirá trabajar entre hojas diferentes.
Con los tres nombres definidos ('campo1', 'campo2' y 'campo3') podemos empezar a detallar nuestra función matricial anidada. La parte más 'profunda',y por otro lado la clave, de nuestra función es
BUSCARV(A2&B2&C2;campo1&campo2&campo3;1;0)
mediante el concatenado de los tres nombres hemos obtenido un rango único sobre el que buscar verticalmente los valores buscados (A2&B2&C2), como hemos creado un rango virtual de sólo una columna, es como si tuvieramos el siguiente rango en la columna D:

Búsqueda matricial triple sobre listados de Excel.


la función BUSCARV efectuará la búsqueda y devolvería el valor de dicha columan, sólo cuando encuentre la coincidencia exacta, en caso de no hallar coincidencia devolverá un error tipo #N/A; con lo que llegamos al siguiente nivel de la función.
Con la función ESERROR evaluamos si la búsqueda ha tenido éxito o no; si obtenemos VERDADERO significará que no se ha encontrado ninguna coincidencia.
Finalmente, como deseamos obtener un texto 'oK' para aquellos registros coincidentes entre tablas, anidamos los dos niveles anteriores de nuestra función en un condicional SI, que dirije nuestro trabajo a una celda vacía en caso de no coincidencia (esto es,que la función ESERROR sea FALSO) o a un 'oK' si fuera VERDADERO

5 comentarios:

  1. Hola Excelforo
    Ante todo las gracias por esos correos tan buenos que siempre vamos aprendiendo poco a poco, he estado probando la formula de las coincidencias y me da “Error de Formula”, quería preguntarle si las columnas con nombres campo1, campo2 y campo3 pueden ser normales o deben estar integradas a una Tabla Dinámica.

    Un Saludo Cordial
    Lázaro.

    ResponderEliminar
  2. Hola Lázaro,
    no es necesario que estén 'integrados' en una tabla de datos (en el ejemplo no existen tablas dinámicas), es suficiente con que existan y hayan sido creados normalmente...
    Si te falla, quizá sea por que no lo ejecutaste matricialmente???
    Slds

    ResponderEliminar
  3. Hola ExcelForo
    El error lo tenía en que había colocado un & de mas en la formula, he aumentado los campos y cantidad de columnas y me los acepta sin problema, estupenda formula que me guardare a buen recaudo, solo quería preguntarle si no es mucho pedir, que función cumplen el 1;0 en la formula, lo único que me falta es probarla para cientos de miles de filas, ya que al ser matricial pudiera ejecutarse más lenta, pero de momento me aporta mucho.
    Un Cordial Saludo
    Lázaro

    ResponderEliminar
  4. Lázaro,
    el tercer y cuarto argumento de la función BUSCARV nos dicen en qué columna de la matriz de búsqueda encontraremos el valor a devolver, en este caso, sólo tenemos una columna en la matriz generada, y el cuarto argumento (será VERDADERO =1 ó FALSO =0) sirve para indicar si deseamos una coincidencia exacta o no.
    Slds

    ResponderEliminar
  5. ExcelForo
    Mire usted, otra cosilla más que aprendo, muchas gracias una vez más.

    Saludos
    Lázaro

    ResponderEliminar