Cómo buscar coincidencias de datos en Excel

Entre las innumerables funciones de Microsoft Excel, existe una que permite comparar dos listas de datos, identificar las coincidencias entre esas listas y detectar además qué elementos se encuentran solo en una de las listas. Esto resulta muy útil para comparar registros financieros o comprobar si un nombre específico se encuentra en una base de datos. Puedes usar la función COINCIDIR para identificar y marcar los registros coincidentes y los no coincidentes, o usar la función CONTAR.SI para establecer un formato condicional. Los siguientes pasos te mostrarán cómo usar estas funciones para buscar coincidencias entre datos.

Método 1

Método 1 de 2:

Identificar registros a través de la función COINCIDIR

Descargar el PDF

Excel puede trabajar con varias planillas de cálculo dentro de un mismo libro o con múltiples libros, pero verás que es más fácil comparar las listas si copias toda la información en una sola planilla.
2
Proporciónale un identificador único a cada elemento de la lista. Si las dos listas no comparten un método de identificación común, probablemente tendrás que agregar una columna adicional en cada lista de datos que identifique ese elemento en Excel. Así podrás determinar si ese elemento de una lista dada está relacionado con un elemento de la otra lista. Las características de este identificador dependerán del tipo de datos que vayas a comparar. Necesitarás un identificador para cada lista de columnas.
- Si tienes datos financieros asociados a un período dado (por ejemplo, registros de impuestos), el identificador podría ser la descripción del activo, la fecha en la que se adquirió el activo o ambas. En algunos casos, las entradas se pueden identificar con un código numérico. Sin embargo, si no usas el mismo sistema para ambas listas, este identificador puede crear coincidencias donde no las hay o ignorar coincidencias que realmente existen.
- En algunos casos, puedes tomar elementos de una lista y combinarlos con elementos de otra lista para crear un identificador, por ejemplo, la descripción de un activo físico y el año en el que se adquirió. Para crear tal identificador, hay que concatenar (agregar, combinar) los datos de dos o más celdas usando el símbolo ampersand (&). Para combinar la descripción de un elemento en la celda F3 con una fecha en la celda G3, separadas por un espacio, tendrías que ingresar la fórmula '=F3& " "&G3'. Si quisieras incluir solo el año en el identificador (porque una lista usa fechas completas y la otra usa solo años), tendrías que incluir la función AÑO ingresando '=F3&" "&AÑO(G3)' en la celda E3 (no incluyas las comillas simples; en este caso solo se usan para indicar un ejemplo).
- Una vez que hayas creado la fórmula, puedes copiarla en todas las otras celdas de la columna del identificador. Para hacerlo, selecciona la celda donde está la fórmula y arrastra el pequeño cuadro de relleno por las demás celdas de la columna donde quieras copiar la fórmula. Al soltar el botón del ratón, cada celda sobre la cual hayas arrastrado el cuadro se completará automáticamente con la fórmula y tendrá las referencias de celdas actualizadas con las celdas que correspondan a esa fila.
3
Estandariza los datos cuando sea posible. Si bien la mente reconoce que "S.A." y "Sociedad Anónima" significan lo mismo, Excel no lo sabrá a menos que reformatees una de las dos expresiones. Del mismo modo, puedes pensar que los valores $11 950 y $11 999,95 son lo suficientemente cercanos como para considerar que hay una coincidencia, pero Excel no lo verá de ese modo a menos que se lo indiques.
- Puedes ocuparte de las abreviaturas, como por ejemplo "Co" para "Compañía" u "Org" para "Organización" usando la función IZQUIERDA para truncar los caracteres que sobran. Pero hay otras abreviaturas que no se pueden truncar, como por ejemplo "S.R.L." para "Sociedad de Responsabilidad Limitada". En ese caso deberás establecer un estilo de entrada de datos y usar un programa que busque y corrija los formatos incorrectos.
- En las cadenas de números también se puede aplicar la función IZQUIERDA. Por ejemplo, si tienes códigos postales con formato "ZIP+4" que incluyen un sufijo después del número y códigos postales simples, utiliza IZQUIERDA para reconocer y buscar la coincidencia solo en la primera parte del código. Para hacer que Excel reconozca valores numéricos cercanos, pero no iguales, puedes usar la función REDONDEAR. Con esta función puedes hacer que los valores cercanos se redondeen al mismo número para que al compararlos hay coincidencia entre ellos.
- Los espacios que estén de más, por ejemplo, si hay dos espacios entre palabras en vez de solo uno, se pueden eliminar a través de la función RECORTAR.
4
Crea columnas para la fórmula de comparación. Así como tuviste que crear columnas para los identificadores de las listas, ahora tendrás que crear columnas para la fórmula que va a hacer la comparación. Necesitarás una columna por cada lista.
- Etiqueta estas columnas con un título que diga "¿Registro faltante?" o algo parecido.
5
Ingresa la fórmula de comparación en cada celda. Para hacer la fórmula de comparación debes usar la función COINCIDIR anidada dentro de otra función de Excel llamada ESNOD.
- La fórmula debe quedar parecida a "=ESNOD(COINCIDIR(G3;$L$3:$L$14;FALSO))", donde se toma una celda de la columna de identificadores de la primera lista y se compara con cada uno de los identificadores de la segunda lista para ver si hay coincidencia con alguno. Si no hay coincidencia, significa que falta ese registro y la palabra "VERDADERO" aparecerá en la celda. Si hay coincidencia, significa que el registro está y aparecerá la palabra "FALSO" (cuando vayas a escribir la fórmula no incluyas las comillas de los extremos).
- Puedes copiar la fórmula en las celdas restantes de la columna de la misma forma que copiaste la fórmula de los identificadores. En este caso, solo cambia la referencia de celdas para el identificador. Los signos de pesos delante de las referencias de fila y columna de la primera y última celda de la lista de identificadores de la segunda celda sirven para transformar las referencias en "referencias absolutas".
- Puedes copiar la fórmula de comparación de la primera lista en la primera celda de la columna de la segunda lista. En ese caso, tendrás que editar las referencias de celdas reemplazando "G3" por la referencia de la primera celda de identificadores de la segunda lista y "$L$3:$L$14" por la primera y última celda de identificadores de la segunda lista (deja los signos de pesos y los dos puntos tal como están). Ahora puedes copiar esta fórmula editada en el resto de las celdas de la columna de comparación de la segunda lista.
6
Ordena las listas de modo que sea más fácil ver los datos que no coinciden. Si las listas son grandes, es mejor ordenarlas poniendo juntos todos los datos que no coinciden. Las instrucciones de las subactividades que se explican a continuación sirven para convertir las fórmulas en valores y así evitar errores al recalcular. Si las listas son largas, esto reducirá también el tiempo de recálculo.
- Arrastra el ratón sobre todas las celdas de la lista para seleccionarlas.
- Selecciona "Copiar" en el menú "Edición" (Excel 2003) o en el grupo "Portapapeles" de la cinta de opciones (Excel 2007 o 2010).
- Selecciona "Pegado especial" en el menú "Edición" (Excel 2003) o en el botón desplegable "Pegar" del grupo "Portapapeles" (Excel 2007 o 2010) de la pestaña "Inicio" en la cinta de opciones.
- Selecciona "Valores" en la lista "Pegar como" del cuadro de diálogo "Pegado especial". Luego haz clic en "Aceptar" para cerrar el cuadro.
- Selecciona "Ordenar" en el menú "Datos" (Excel 2003) o en el grupo "Ordenar y filtrar" de la pestaña "Datos" en la cinta de opciones (Excel 2007 o 2010).
- Selecciona "Fila de encabezamiento" en la lista "Mi rango de datos tiene" del cuadro de diálogo "Ordenar por". Selecciona "¿Registro faltante?" (o el nombre que hayas elegido como encabezado para la columna de comparación) y haz clic en "Aceptar".
- Repite estos pasos con la otra lista.
Tal como se explicó anteriormente, Excel está diseñado para buscar coincidencias exactas en los datos, a menos que lo configures para que busque aproximaciones. La "no coincidencia" puede deberse a un simple error de transposición de letras o dígitos. También puede ser algo que requiera una verificación independiente como, por ejemplo, comprobar los datos para ver si los activos de la lista debían reportarse en primer lugar.
Anuncio

Método 2

Método 2 de 2:

Dar formato condicional a través de la función CONTAR.SI

Descargar el PDF

Si quieres resaltar registros solamente en una lista, lo mejor es resaltar los registros únicos de esa lista, es decir, aquellos que no coincidan con los registros de la otra lista. Si quieres resaltar registros de las dos listas, es mejor resaltar aquellos que sí tengan coincidencias con la otra lista. Para los fines de este ejemplo, se asumirá que la primera lista ocupa las celdas G3 a G14 y la segunda ocupa las celdas L3 a L14.
Si quieres resaltar elementos en las dos listas, tendrás que seleccionar las listas de a una por vez y aplicar la fórmula de comparación (que se indica en el siguiente paso) en cada una.
4
Aplica la fórmula de comparación correspondiente. Para hacerlo, tendrás que acceder al cuadro "Formato condicional" de tu versión de Excel. En Excel 2003, ábrelo seleccionado "Formato condicional" en el menú "Formato". En Excel 2007 y 2010, haz clic en el botón "Formato condicional" del grupo "Estilos" de la pestaña "Inicio" en la cinta de opciones. Selecciona "Fórmula" como tipo de regla e ingresa la fórmula en el campo "Editar una descripción de regla".
- Si quieres resaltar los registros únicos de la primera lista, la fórmula sería "=CONTAR.SI($L$3:$L$14;G3=0)" con el rango de celdas de la segunda lista expresados en valores absolutos y la referencia a la primera celda de la primera lista como un valor relativo (no incluyas las comillas en la fórmula).
- Si quieres resaltar los registros únicos de la segunda lista, la fórmula sería "=CONTAR.SI($G$3:$G$14;L3=0)" con el rango de celdas de la primera lista expresados en valores absolutos y la referencia a la primera celda de la segunda lista como un valor relativo (no incluyas las comillas en la fórmula).
- Si quieres resaltar los registros de una lista que se encuentran en la otra lista, necesitarás dos fórmulas, una para la primera lista y otra para la segunda. La fórmula para la primera lista es "=CONTAR.SI($L$3:$L$14;G3>0)", mientras que la fórmula para la segunda lista es "=CONTAR.SI($G$3:$G$14;L3>0)". Tal como se explicó anteriormente, hay que seleccionar la primera lista para aplicarle su fórmula y luego seleccionar la segunda para aplicarle su fórmula.
- Aplica el formato que quieras para resaltar los registros que quieras marcar. Haz clic en "Aceptar" para cerrar el cuadro de diálogo.
Anuncio

Consejos

En vez de usar una referencia de celda con la función CONTAR.SI del método de formato condicional, puedes ingresar un valor. Ese valor se buscará en las listas y se resaltarán todas las instancias que se encuentren.
Para simplificar las fórmulas de comparación, puedes crear un nombre para cada lista, por ejemplo "Lista1" y "Lista2". Luego, cuando escribas la fórmula, los nombres de las listas se pueden usar para sustituir los rangos de celdas absolutos de los ejemplos anteriores.

Anuncio