Realizar coincidencias aproximadas y búsquedas difusas en Excel

Esta publicación explora las funciones de búsqueda de Excel, coincidencias aproximadas, búsquedas aproximadas y coincidencias exactas. Las funciones de búsqueda integradas en Excel, como BUSCARV, son sorprendentes. Cuando se implementan de la manera correcta para proyectos especiales o en libros de trabajo de uso recurrente, pueden ahorrar mucho tiempo. La función BUSCARV por sí sola me ha ahorrado innumerables horas en mis libros de uso recurrente. Sin embargo, la función BUSCARV, similar a otras funciones de búsqueda de Excel, como BUSCARH y COINCIDIR, está diseñada para realizar una coincidencia exacta o una búsqueda de rango. Ambos son bastante diferentes de una coincidencia aproximada o una búsqueda difusa. Esta publicación analiza los detalles de estas ideas y demuestra cómo realizar una búsqueda difusa en Excel 2010 y versiones posteriores.

*** PUBLICACIÓN DE BLOG ACTUALIZADA: FUZZY MATCH ESTÁ DISPONIBLE EN POWER QUERY ***

Comprender las funciones de búsqueda integradas

Las funciones de búsqueda integradas de Excel, como BUSCARV, BUSCARH y COINCIDIR, funcionan con una lógica de búsqueda similar. Para simplificar esta publicación, usaremos solo uno como ejemplo. Dado que la función BUSCARV es probablemente la función de búsqueda más utilizada y más familiar, la usaremos mientras exploramos estas ideas.

La idea básica de una función de búsqueda de Excel es buscar un valor en una lista. Por ejemplo, podríamos pedirle a Excel que busque “Compañía ABC” en una lista de nombres de clientes. Esa es la idea básica, pero las aplicaciones de funciones de búsqueda son numerosas y las implementaciones pueden llegar a ser bastante sofisticadas y poderosas.

Para esta publicación, me gustaría dividir las tareas que realiza una función de búsqueda en dos pasos. Llamaré al paso uno el partido y al paso dos la devolución . En el primer paso, la coincidencia, Excel debe encontrar el valor coincidente. Le dice a Excel el valor que debe buscar, como “Compañía ABC”, y le dice a Excel dónde buscar, por ejemplo, en un rango de celdas. Le está pidiendo a Excel que encuentre el valor de búsqueda en el rango de búsqueda.

El segundo paso, el retorno, es el resultado de la función. Es decir, qué valor debe devolver la función a la celda. Algunas funciones de búsqueda, como la función COINCIDIR, le indican a Excel que devuelva el número de posición. Otras funciones de búsqueda, como la función BUSCARV, le indican a Excel que devuelva un valor relacionado. Entonces, según la función de búsqueda que seleccione y los valores de los argumentos de la función que ingrese, Excel sabe qué devolver una vez que encuentra su coincidencia. ¿Hasta ahora, todo bien?

Hagamos un ejemplo rápido en este punto.

Me gustaría encontrar un nombre de cliente específico “Compañía ABC” en una lista de clientes y, si lo encuentro, me gustaría que Excel devuelva la identificación del cliente que se encuentra en la siguiente columna.

Usaría una función BUSCARV y le pediría que busque “Compañía ABC” en la tabla de clientes y devuelva la identificación. Suponiendo que el nombre del cliente se ingresó en C7 y los clientes se almacenaron en una tabla denominada Tabla1, la siguiente función funcionaría:

=BUSCARV(C7, Tabla1, 2, FALSO)

Dónde :

• C7 es el valor a encontrar
• Tabla1 es el rango de búsqueda
• 2 es la columna que tiene el valor que deseamos devolver
• FALSO significa que no estamos realizando una búsqueda de rango

Esta función se ingresa en C8 en la captura de pantalla siguiente.

Como puede ver, el ID AC100 se devolvió exitosamente a la celda de fórmula C8. Y esa, amigo mío, es la idea básica de la función BUSCARV. Encuentre un valor (la coincidencia) y calcule el resultado (el retorno).

Es importante tener en cuenta que el valor de búsqueda, la cadena de texto “Compañía ABC”, debe encontrarse en el rango de búsqueda. Excepto mayúsculas y minúsculas, los dos valores deben coincidir exactamente. “Compañía ABC” no coincidiría con “Compañía ABC, Inc.”, “Compañía ABC” o “Compañía ABC”. Sin espacios iniciales ni finales, ni abreviaturas ni caracteres adicionales. Deben ser iguales. A esto se le llama coincidencia exacta. Si el valor no es el mismo, la función no lo coincidirá y obtendrá un error, como se muestra en la captura de pantalla siguiente.

Ahora que hemos cubierto los conceptos básicos, es hora de explorar el cuarto argumento de BUSCARV.

La verdad sobre el cuarto argumento de BUSCARV

El cuarto argumento de la función BUSCARV se llama oficialmente: range_lookup. Es un argumento booleano, lo que significa que puede pasarle un valor VERDADERO o FALSO, o cualquier otra representación de VERDADERO o FALSO. Lo que tiende a engañar a los usuarios de Excel es la descripción que Microsoft utilizó para estas opciones. Excel describe el valor VERDADERO como “Coincidencia aproximada” y FALSO como “Coincidencia exacta”. Una descripción más clara habría sido algo como VERDADERO “Estás haciendo una búsqueda de rango” y FALSO “No estás haciendo una búsqueda de rango” pero, en cualquier caso, las descripciones son las que son.

Cuando selecciona VERDADERO (Coincidencia aproximada), no le pide a Excel que haga coincidir valores que son aproximadamente iguales entre sí. La descripción Coincidencia aproximada tendería a implicar que la función coincidiría con “Compañía ABC” y “Compañía ABC, Inc.” ya que son aproximadamente el mismo nombre. En algunos casos y en algunos conjuntos de datos, esta idea funcionaría. Pero esta idea no funciona en todos los casos y, por lo tanto, no se puede confiar en ella en nuestros libros de trabajo. Por ejemplo, en la captura de pantalla siguiente, la función no encontró una coincidencia entre “ABC Company” y “ABC Company, Inc.” como lo demuestra la identificación incorrecta devuelta en C8:

Sin embargo, en la siguiente captura de pantalla, la función encontró una coincidencia entre “ABC Company” y “ABC Co”, como lo demuestra la identificación esperada devuelta a C8:

La forma en que la función realmente funciona cuando se selecciona VERDADERO es la siguiente: recorre la lista fila por fila y finalmente se detiene en la fila que es menor que el valor y donde la siguiente fila es mayor que el valor. Es por eso que el rango de búsqueda debe ordenarse en orden ascendente para que la función devuelva un resultado preciso cuando el cuarto argumento es VERDADERO.

Esta idea puede resultar confusa cuando se piensa en cadenas de texto, pero tiene más sentido cuando se piensa en números. Por ejemplo, al intentar encontrar la tasa de comisión correcta en función del valor de las ventas. En este caso, desea realizar una búsqueda de rango. Quiere buscar un valor dentro de un rango. Esto se ilustra en la captura de pantalla siguiente.

La función recorre fila por fila tratando de determinar en qué fila detenerse. Continúa hacia abajo hasta que encuentra una fila que es mayor que el valor de búsqueda y luego se detiene en la fila anterior. Se detiene en la fila que es menor que el valor y donde la siguiente fila es mayor que el valor de búsqueda. Esto es bastante fácil de entender cuando se piensa en números, pero puede ser más difícil de visualizar cuando se piensa en cadenas de texto. Sin embargo, la clave para comprender el argumento de esta función es darse cuenta de que la lógica es idéntica cuando se opera con cadenas de texto y números. Esta es la razón por la que “ABC Company” no coincide con “ABC Company, Inc.”, porque “ABC Company Inc.” es mayor que la empresa ABC. Esta es la razón por la que “ABC Company” coincidirá con “ABC Co”, porque “ABC Co” es menor que “ABC Company”. Como puede ver, esto no es lo que tenemos en mente cuando pensamos en la coincidencia aproximada.

¿Qué es una búsqueda aproximada, también conocida como coincidencia aproximada?

Para nosotros, una coincidencia aproximada significa que dos cadenas de texto que son aproximadamente iguales, pero no necesariamente idénticas, deben coincidir. Por ejemplo, “Compañía ABC” debe coincidir con “Compañía ABC, Inc.”, “Compañía ABC” y “Compañía ABC”. Pensamos en una coincidencia aproximada como algo confusa, donde algunos de los personajes coinciden pero no todos.

La idea de una búsqueda difusa es que los valores no coinciden claramente, no son idénticos. Pero si es probable que coincidan, existe la probabilidad de que coincidan. Probablemente representen la misma entidad subyacente.

Ahora que nos damos cuenta de que la función BUSCARV realmente no realiza una lógica de coincidencia aproximada, al menos no de la forma que queremos, ¿qué hacemos?

Complemento

Cuando choques contra una pared, rodéala. Dado que las funciones de búsqueda integradas no realizan lógica difusa al realizar la coincidencia, nos topamos con una limitación incorporada de Excel. Microsoft ha ofrecido una forma de solucionar esta limitación ofreciendo un complemento gratuito.

Microsoft ofrece un complemento gratuito que permite a Excel realizar búsquedas difusas. Se llama “Complemento de búsqueda difusa para Excel” y está disponible en el momento de escribir esta publicación en el siguiente enlace:

http://www.microsoft.com/en-us/download/details.aspx?id=15011

Una vez instalado, este complemento realiza búsquedas difusas. No cambia el comportamiento de ninguna de las funciones de búsqueda integradas. No permite que las funciones BUSCARV realicen búsquedas difusas. Es un complemento que básicamente procesa dos listas y calcula la probabilidad de una coincidencia.

Usted especifica las dos tablas y, dentro de cada tabla, las columnas a inspeccionar. Básicamente, defines el paso uno : el partido . Luego define el paso dos identificando qué columnas de las tablas deben incluirse en el resultado. También puede especificar el umbral de probabilidad. Presiona Ir y el complemento realiza su trabajo y luego genera la tabla resultante comenzando en la celda activa. Básicamente genera un informe estático basado en la configuración que seleccione.

Aquí hay una captura de pantalla del resultado, que muestra que coincidió correctamente con “ABC Company” y “ABC Company, Inc.” en el mismo conjunto de datos que provocó que fallara nuestra función BUSCARV.

Para obtener más información sobre el complemento de búsqueda difusa y más detalles sobre cómo usarlo, visite el enlace de Microsoft anterior. El complemento viene con instrucciones, un archivo Excel de muestra y un archivo pdf con los antecedentes y la lógica que utiliza para hacer su magia. También viene con una licencia, por lo que querrás asegurarte de leer los términos de la licencia en el documento LicenseTerms.rtf incluido con la descarga.

Hay algunas ciencias de la computación y matemáticas extremadamente interesantes trabajando detrás de escena, incluida la similitud de Jaccard, la tokenización de registros y las transformaciones. Matemáticas bastante pesadas ahí. ¡Gracias Microsoft Research por este complemento!