Como comparar dúas listas en Excel

Excel é un programa de procesamento de datos eficiente. E un dos métodos de análise da información é a comparación de dúas listas. Se comparas correctamente dúas listas en Excel, organizar este proceso será moi sinxelo. Basta con seguir algúns dos puntos que se tratarán hoxe. A implementación práctica deste método depende enteiramente das necesidades da persoa ou organización nun momento particular. Polo tanto, hai que considerar varios casos posibles.

Comparando dúas listas en Excel

Por suposto, podes comparar dúas listas manualmente. Pero vai levar moito tempo. Excel ten o seu propio conxunto de ferramentas intelixente que che permitirá comparar datos non só rapidamente, senón tamén obter información que non é tan fácil de conseguir cos teus ollos. Supoñamos que temos dúas columnas coas coordenadas A e B. Algúns valores repítense nelas.

Formulación do problema

Polo tanto, necesitamos comparar estas columnas. O procedemento para comparar dous documentos é o seguinte:

1. Se as celas únicas de cada unha destas listas son iguais e o número total de celas únicas é o mesmo e as celas son iguais, entón estas listas pódense considerar iguais. A orde na que se apilan os valores desta lista non importa tanto.
2. Podemos falar de coincidencia parcial de listas se os valores únicos son os mesmos, pero o número de repeticións é diferente. Polo tanto, tales listas poden conter un número diferente de elementos.
3. O feito de que as dúas listas non coincidan indícase por un conxunto diferente de valores únicos.

Todas estas tres condicións á vez son as condicións do noso problema.

A solución do problema

Imos xerar dous rangos dinámicos para que sexa máis fácil comparar listas. Cada unha delas corresponderá a cada unha das listas.

Para comparar dúas listas, faga o seguinte:

1. Nunha columna separada, creamos unha lista de valores únicos que son específicos para ambas listas. Para iso usamos a fórmula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). A propia fórmula debe escribirse como unha fórmula matricial.
2. Determinemos cantas veces aparece cada valor único na matriz de datos. Aquí están as fórmulas para facelo: =CONTARSE(Lista1,D5) e =CONTARSE(Lista2,D5).
3. Se tanto o número de repeticións como o número de valores únicos son os mesmos en todas as listas que se inclúen nestes intervalos, entón a función devolve o valor 0. Isto indica que a coincidencia é XNUMX%. Neste caso, os títulos destas listas adquirirán un fondo verde.
4. Se todo o contido único está en ambas as listas, entón devolverase mediante fórmulas =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) o valor será cero. Se E1 non contén cero, pero tal valor está contido nas celas E2 e F2, entón neste caso os intervalos recoñeceranse como coincidentes, pero só parcialmente. Neste caso, os encabezados das listas correspondentes pasarán a laranxa.
5. E se unha das fórmulas descritas anteriormente devolve un valor distinto de cero, as listas non coincidirán completamente.

Esta é a resposta á pregunta de como analizar as columnas das coincidencias mediante fórmulas. Como podes ver, co uso das funcións, pódese implementar case calquera tarefa que, a primeira vista, non estea relacionada coas matemáticas.

Exemplo de proba

Na nosa versión da táboa, hai tres tipos de listas de cada tipo descrito anteriormente. Ten coincidencias parcial e completamente, así como non coincidentes.

Para comparar datos, utilizamos o rango A5:B19, no que inserimos alternativamente estes pares de listas. Sobre cal será o resultado da comparación, entenderémolo pola cor das listas orixinais. Se son completamente diferentes, entón será un fondo vermello. Se parte dos datos é a mesma, entón o amarelo. No caso de identidade completa, os epígrafes correspondentes serán de cor verde. Como facer unha cor dependendo de cal sexa o resultado? Isto require un formato condicional.

Atopar diferenzas en dúas listas de dúas formas

Imos describir dous métodos máis para atopar diferenzas, dependendo de se as listas son sincrónicas ou non.

Opción 1. Listas síncronas

Esta é unha opción sinxela. Supoñamos que temos listas deste tipo.

Para determinar cantas veces non converxeron os valores, pode usar a fórmula: =PRODUTO SUMA(—(A2:A20<>B2:B20)). Se obtivemos 0 como resultado, isto significa que as dúas listas son iguais.

Opción 2: Listas mesturadas

Se as listas non son idénticas na orde dos obxectos que conteñen, cómpre aplicar unha función como o formato condicional e colorear os valores duplicados. Ou use a función COUNTIF, co cal determinamos cantas veces aparece un elemento dunha lista na segunda.

Como comparar 2 columnas fila por fila

Cando comparamos dúas columnas, moitas veces necesitamos comparar información que está en filas diferentes. Para iso, o operador axudaranos SI. Vexamos como funciona na práctica. Para iso, presentamos varias situacións ilustrativas.

Exemplo. Como comparar 2 columnas para coincidencias e diferenzas nunha fila

Para analizar se os valores que están na mesma fila pero diferentes columnas son iguais, escribimos a función IF. A fórmula insírese en cada fila situada na columna auxiliar onde se mostrarán os resultados do procesamento de datos. Pero non é necesario prescribilo en cada fila, só cópiao nas celas restantes desta columna ou utiliza o marcador de autocompletar.

Deberíamos escribir esa fórmula para comprender se os valores de ambas columnas son iguais ou non: =SI(A2=B2, “Coincidir”, “”). A lóxica desta función é moi sinxela: compara os valores das celas A2 e B2 e, se son iguais, mostra o valor "Coincide". Se os datos son diferentes, non devolve ningún valor. Tamén pode comprobar as celas para ver se hai coincidencia entre elas. Neste caso, a fórmula utilizada é: =SI(A2<>B2, “Non coincide”, “”). O principio é o mesmo, primeiro realízase a comprobación. Se resulta que as celas cumpren o criterio, móstrase o valor "Non coincide".

Tamén é posible usar a seguinte fórmula no campo da fórmula para mostrar tanto "Coincidir" se os valores son iguais, como "Non coincidir" se son diferentes: =IF(A2=B2; "Coincidir", "Non coincidir"). Tamén pode usar o operador de desigualdade en lugar do operador de igualdade. Só a orde dos valores que se mostrarán neste caso será lixeiramente diferente: =SI(A2<>B2, “Non coincide”, “Coincide”). Despois de usar a primeira versión da fórmula, o resultado será o seguinte.

Esta variación da fórmula non distingue entre maiúsculas e minúsculas. Polo tanto, se os valores dunha columna difiren doutras só porque están escritos en maiúsculas, entón o programa non notará esta diferenza. Para facer que a comparación distinga entre maiúsculas e minúsculas, cómpre utilizar a función nos criterios EXACT. O resto dos argumentos quedan sen cambios: =SI(EXACTO(A2,B2), “Coincidir”, “Único”).

Como comparar varias columnas para coincidencias nunha fila

É posible analizar os valores nas listas segundo un conxunto de criterios:

1. Busca aquelas filas que teñen os mesmos valores en todas partes.
2. Busca aquelas filas nas que hai coincidencias en só dúas listas.

Vexamos algúns exemplos de como proceder en cada un destes casos.

Exemplo. Como atopar coincidencias nunha fila en varias columnas dunha táboa

Supoñamos que temos unha serie de columnas que conteñen a información que necesitamos. Estamos ante a tarefa de determinar aquelas filas nas que os valores son os mesmos. Para iso, cómpre usar a seguinte fórmula: =SE(AND(A2=B2;A2=C2), "coincidir", " ").

Se hai demasiadas columnas na táboa, só tes que usalo xunto coa función IF operador COUNTIF: =SE(CONTARSE($A2:$C2,$A2)=3;”coincidir”;” “). O número empregado nesta fórmula indica o número de columnas a comprobar. Se é diferente, entón tes que escribir tanto como sexa certo para a túa situación.

Exemplo. Como atopar coincidencias nunha fila en 2 columnas calquera dunha táboa

Digamos que necesitamos comprobar se os valores dunha fila coinciden en dúas columnas cos da táboa. Para iso, cómpre usar a función como condición OR, onde se escribe alternativamente a igualdade de cada unha das columnas coa outra. Aquí tes un exemplo.

Usamos esta fórmula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Pode haber unha situación na que hai moitas columnas na táboa. Neste caso, a fórmula será enorme e pode levar moito tempo seleccionar todas as combinacións necesarias. Para resolver este problema, cómpre usar a función COUNTIF: =SE(CONTARSE(B2:D2,A2)+CONTARSE(C2:D2,B2)+(C2=D2)=0; “Cadea única”; “Cadea non única”)

Vemos que en total temos dúas funcións COUNTIF. Coa primeira determinamos alternativamente cantas columnas teñen semellanza con A2, e coa segunda comprobamos o número de semellanzas co valor de B2. Se, como resultado do cálculo mediante esta fórmula, obtemos un valor cero, isto indica que todas as filas desta columna son únicas, se hai máis, hai semellanzas. Polo tanto, se como resultado de calcular por dúas fórmulas e engadir os resultados finais obtemos un valor cero, entón devólvese o valor de texto "Cadea única", se este número é maior, escríbese que esta cadea non é única.

Como comparar 2 columnas en Excel para as coincidencias

Agora imos poñer un exemplo. Digamos que temos unha táboa con dúas columnas. Debes comprobar se coinciden. Para iso, cómpre aplicar a fórmula, onde tamén se utilizará a función IF, e o operador COUNTIF: =SE(CONTARSE($B:$B,$A5)=0, "Non hai coincidencias na columna B", "Hai coincidencias na columna B")

Non é necesaria ningunha acción adicional. Despois de calcular o resultado mediante esta fórmula, obtemos se o valor do terceiro argumento da función IF partidos. Se non hai ningún, entón o contido do segundo argumento.

Como comparar 2 columnas en Excel para as coincidencias e resaltar con cor

Para facilitar a identificación visual das columnas coincidentes, pode resaltalas cunha cor. Para iso, cómpre utilizar a función "Formato condicional". Vexamos na práctica.

Busca e destaca coincidencias por cor en varias columnas

Para determinar as coincidencias e destacalas, primeiro debes seleccionar o intervalo de datos no que se realizará a comprobación e, a continuación, abrir o elemento "Formato condicional" na pestana "Inicio". Alí, seleccione "Valores duplicados" como regra de selección de celas.

Despois diso, aparecerá un novo cadro de diálogo, no que na lista emerxente esquerda atopamos a opción “Repetir”, e na lista da dereita seleccionamos a cor que se utilizará para a selección. Despois de facer clic no botón "Aceptar", seleccionarase o fondo de todas as celas con semellanzas. A continuación, compara as columnas a ollo.

Buscar e destacar liñas coincidentes

A técnica para comprobar se as cadeas coinciden é lixeiramente diferente. En primeiro lugar, necesitamos crear unha columna adicional, e alí usaremos os valores combinados usando o operador &. Para iso, cómpre escribir unha fórmula da forma: =A2&B2&C2&D2.

Seleccionamos a columna que se creou e contén os valores combinados. A continuación, realizamos a mesma secuencia de accións que se describe anteriormente para as columnas. As liñas duplicadas resaltaranse na cor que especifique.

Vemos que non hai nada difícil buscar repeticións. Excel contén todas as ferramentas necesarias para iso. É importante practicar antes de poñer en práctica todos estes coñecementos.