Cómo comparar dos listas en Excel

Excel es un programa de procesamiento de datos eficiente. Y uno de los métodos de análisis de la información es la comparación de dos listas. Si comparas correctamente dos listas en Excel, organizar este proceso será muy fácil. Basta con seguir algunos de los puntos que se discutirán hoy. La implementación práctica de este método depende enteramente de las necesidades de la persona u organización en un momento particular. Por lo tanto, se deben considerar varios casos posibles.

Comparando dos listas en Excel

Por supuesto, puede comparar dos listas manualmente. Pero llevará mucho tiempo. Excel tiene su propio conjunto de herramientas inteligentes que le permitirán comparar datos no solo rápidamente, sino también obtener información que no es tan fácil de obtener a simple vista. Supongamos que tenemos dos columnas con coordenadas A y B. En ellas se repiten algunos valores.

Formulación del problema

Así que tenemos que comparar estas columnas. El procedimiento para comparar dos documentos es el siguiente:

1. Si las celdas únicas de cada una de estas listas son las mismas, y el número total de celdas únicas es el mismo, y las celdas son las mismas, entonces estas listas pueden considerarse iguales. El orden en que se apilan los valores de esta lista no importa tanto.
2. Podemos hablar de coincidencia parcial de listas si los valores únicos son los mismos, pero el número de repeticiones es diferente. Por lo tanto, dichas listas pueden contener un número diferente de elementos.
3. El hecho de que las dos listas no coincidan se indica mediante un conjunto diferente de valores únicos.

Todas estas tres condiciones son simultáneamente las condiciones de nuestro problema.

La solución del problema

Generemos dos rangos dinámicos para que sea más fácil comparar listas. Cada uno de ellos se corresponderá con cada una de las listas.

Para comparar dos listas, haga lo siguiente:

1. En una columna separada, creamos una lista de valores únicos que son específicos para ambas listas. Para ello utilizamos la fórmula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). La fórmula en sí debe escribirse como una fórmula matricial.
2. Determinemos cuántas veces ocurre cada valor único en la matriz de datos. Aquí están las fórmulas para hacer esto: =CONTAR.SI(Lista1,D5) y =CONTAR.I(Lista2,D5).
3. Si tanto el número de repeticiones como el número de valores únicos son iguales en todas las listas que se incluyen en estos rangos, entonces la función devuelve el valor 0. Esto indica que la coincidencia es un XNUMX%. En este caso, los encabezamientos de estas listas adquirirán un fondo verde.
4. Si todo el contenido único está en ambas listas, lo devuelven las fórmulas =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) el valor será cero. Si E1 no contiene cero, pero dicho valor está contenido en las celdas E2 y F2, en este caso los rangos se reconocerán como coincidentes, pero solo parcialmente. En este caso, los encabezados de las listas correspondientes se volverán de color naranja.
5. Y si una de las fórmulas descritas anteriormente devuelve un valor distinto de cero, las listas no coincidirán en absoluto.

Esta es la respuesta a la pregunta de cómo analizar columnas en busca de coincidencias usando fórmulas. Como puedes ver, con el uso de funciones, puedes implementar casi cualquier tarea que, a simple vista, no esté relacionada con las matemáticas.

Pruebas de ejemplo

En nuestra versión de la tabla, hay tres tipos de listas de cada tipo descrito anteriormente. Tiene coincidencia parcial y total, así como no coincidencia.

Para comparar datos, usamos el rango A5:B19, en el que insertamos alternativamente estos pares de listas. Sobre cuál será el resultado de la comparación, lo entenderemos por el color de las listas originales. Si son completamente diferentes, será un fondo rojo. Si parte de los datos es el mismo, entonces amarillo. En el caso de identidad completa, los epígrafes correspondientes serán de color verde. ¿Cómo hacer un color dependiendo de cuál sea el resultado? Esto requiere formato condicional.

Encontrar diferencias en dos listas de dos maneras

Describamos dos métodos más para encontrar diferencias, dependiendo de si las listas son sincrónicas o no.

Opción 1. Listas sincrónicas

Esta es una opción fácil. Supongamos que tenemos tales listas.

Para determinar cuántas veces los valores no convergieron, puede usar la fórmula: =SUMAPRODUCTO(—(A2:A20<>B2:B20)). Si obtuvimos como resultado 0, significa que las dos listas son iguales.

Opción 2: Listas barajadas

Si las listas no son idénticas en el orden de los objetos que contienen, debe aplicar una característica como formato condicional y colorear valores duplicados. O usa la función COUNTIF, mediante el cual determinamos cuántas veces aparece un elemento de una lista en la segunda.

Cómo comparar 2 columnas fila por fila

Cuando comparamos dos columnas, a menudo necesitamos comparar información que está en diferentes filas. Para ello, el operador nos ayudará SI. Echemos un vistazo a cómo funciona en la práctica. Para ello, presentamos varias situaciones ilustrativas.

Ejemplo. Cómo comparar 2 columnas para coincidencias y diferencias en una fila

Para analizar si los valores que están en la misma fila pero en diferentes columnas son iguales, escribimos la función IF. La fórmula se inserta en cada fila colocada en la columna auxiliar donde se mostrarán los resultados del procesamiento de datos. Pero no es necesario prescribirlo en cada fila, solo cópielo en las celdas restantes de esta columna o use el marcador de autocompletar.

Deberíamos escribir una fórmula de este tipo para comprender si los valores en ambas columnas son iguales o no: =SI(A2=B2, “Coincidencia”, “”). La lógica de esta función es muy simple: compara los valores de las celdas A2 y B2, y si son iguales, muestra el valor “Coincidir”. Si los datos son diferentes, no devuelve ningún valor. También puede verificar las celdas para ver si hay una coincidencia entre ellas. En este caso, la fórmula utilizada es: =SI(A2<>B2, “No coinciden”, “”). El principio es el mismo, primero se realiza la comprobación. Si resulta que las celdas cumplen el criterio, se muestra el valor "No coincide".

También es posible usar la siguiente fórmula en el campo de fórmula para mostrar tanto "Coincidencia" si los valores son iguales, como "No coincidencia" si son diferentes: =SI(A2=B2; “Coincidencia”, “No coincidencia”). También puede usar el operador de desigualdad en lugar del operador de igualdad. Solo el orden de los valores que se mostrarán en este caso será ligeramente diferente: =SI(A2<>B2, “No coinciden”, “Coinciden”). Después de usar la primera versión de la fórmula, el resultado será el siguiente.

Esta variación de la fórmula no distingue entre mayúsculas y minúsculas. Por lo tanto, si los valores en una columna difieren de los demás solo en que están escritos en mayúsculas, el programa no notará esta diferencia. Para que la comparación distinga entre mayúsculas y minúsculas, debe usar la función en los criterios EXACT. El resto de los argumentos se dejan sin cambios: =SI(EXACTO(A2,B2), “Coincidencia”, “Único”).

Cómo comparar múltiples columnas para coincidencias en una fila

Es posible analizar los valores de las listas según toda una serie de criterios:

1. Encuentra esas filas que tienen los mismos valores en todas partes.
2. Encuentre esas filas donde hay coincidencias en solo dos listas.

Veamos algunos ejemplos de cómo proceder en cada uno de estos casos.

Ejemplo. Cómo encontrar coincidencias en una fila en varias columnas de una tabla

Supongamos que tenemos una serie de columnas que contienen la información que necesitamos. Nos encontramos ante la tarea de determinar aquellas filas en las que los valores son iguales. Para hacer esto, debe usar la siguiente fórmula: =SI(Y(A2=B2,A2=C2), “coincidencia”, ” “).

Si hay demasiadas columnas en la tabla, solo necesita usarlas junto con la función IF operador COUNTIF: =SI(CONTAR.SI($A2:$C2,$A2)=3;”coincidencia”;” “). El número utilizado en esta fórmula indica el número de columnas a verificar. Si difiere, entonces debe escribir todo lo que sea cierto para su situación.

Ejemplo. Cómo encontrar coincidencias en una fila en cualquiera de las 2 columnas de una tabla

Digamos que necesitamos verificar si los valores en una fila coinciden en dos columnas de los de la tabla. Para hacer esto, necesita usar la función como una condición OR, donde alternativamente se escribe la igualdad de cada una de las columnas con la otra. Aquí hay un ejemplo.

Usamos esta fórmula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Puede haber una situación en la que haya muchas columnas en la tabla. En este caso, la fórmula será enorme y puede llevar mucho tiempo seleccionar todas las combinaciones necesarias. Para resolver este problema, necesita usar la función COUNTIF: =SI(CONTAR.SI(B2:D2,A2)+CONTAR.SI(C2:D2,B2)+(C2=D2)=0; “Cadena única”; “Cadena no única”)

Vemos que en total tenemos dos funciones COUNTIF. Con el primero, determinamos alternativamente cuántas columnas tienen una similitud con A2, y con el segundo, verificamos el número de similitudes con el valor de B2. Si, como resultado de calcular con esta fórmula, obtenemos un valor cero, esto indica que todas las filas de esta columna son únicas, si hay más, hay similitudes. Por lo tanto, si como resultado del cálculo por dos fórmulas y sumando los resultados finales obtenemos un valor cero, entonces se devuelve el valor de texto "Cadena única", si este número es mayor, se escribe que esta cadena no es única.

Cómo comparar 2 columnas en Excel para coincidencias

Ahora tomemos un ejemplo. Digamos que tenemos una tabla con dos columnas. Tienes que comprobar si coinciden. Para hacer esto, debe aplicar la fórmula, donde también se usará la función IF, y el operador COUNTIF: =SI(CONTAR.SI($B:$B,$A5)=0, “No hay coincidencias en la columna B”, “Hay coincidencias en la columna B”)

No se requiere ninguna acción adicional. Después de calcular el resultado por esta fórmula, obtenemos si el valor del tercer argumento de la función IF partidos. Si no hay ninguno, entonces el contenido del segundo argumento.

Cómo comparar 2 columnas en Excel para coincidencias y resaltar con color

Para facilitar la identificación visual de las columnas coincidentes, puede resaltarlas con un color. Para hacer esto, debe usar la función "Formato condicional". Veamos en la práctica.

Encontrar y resaltar coincidencias por color en múltiples columnas

Para determinar las coincidencias y resaltarlas, primero debe seleccionar el rango de datos en el que se realizará la verificación y luego abrir el elemento "Formato condicional" en la pestaña "Inicio". Allí, seleccione "Valores duplicados" como la regla de selección de celdas.

Después de eso, aparecerá un nuevo cuadro de diálogo, en el que en la lista emergente de la izquierda encontramos la opción "Repetir", y en la lista de la derecha seleccionamos el color que se utilizará para la selección. Después de hacer clic en el botón "Aceptar", se seleccionará el fondo de todas las celdas con similitudes. Luego simplemente compare las columnas a simple vista.

Encontrar y resaltar líneas coincidentes

La técnica para verificar si las cadenas coinciden es ligeramente diferente. Primero, necesitamos crear una columna adicional, y allí usaremos los valores combinados usando el operador &. Para hacer esto, debe escribir una fórmula de la forma: =A2 y B2 y C2 y D2.

Seleccionamos la columna que se creó y contiene los valores combinados. A continuación, realizamos la misma secuencia de acciones que se describe anteriormente para las columnas. Las líneas duplicadas se resaltarán en el color que especifique.

Vemos que no hay nada difícil en buscar repeticiones. Excel contiene todas las herramientas necesarias para esto. Es importante practicar antes de poner todo este conocimiento en práctica.