Como comparar duas listas no Excel

O Excel é um programa de processamento de dados eficiente. E um dos métodos de análise da informação é a comparação de duas listas. Se você comparar corretamente duas listas no Excel, organizar esse processo será muito fácil. Basta seguir alguns dos pontos que serão discutidos hoje. A implementação prática deste método depende inteiramente das necessidades da pessoa ou organização em um determinado momento. Portanto, vários casos possíveis devem ser considerados.

Comparando duas listas no Excel

Claro, você pode comparar duas listas manualmente. Mas vai demorar muito. O Excel possui seu próprio kit de ferramentas inteligente que permitirá comparar dados não apenas rapidamente, mas também obter informações que não são tão fáceis de obter com os olhos. Suponha que temos duas colunas com as coordenadas A e B. Alguns valores se repetem nelas.

Formulação do problema

Então, precisamos comparar essas colunas. O procedimento para comparar dois documentos é o seguinte:

1. Se as células exclusivas de cada uma dessas listas forem as mesmas e o número total de células exclusivas for o mesmo e as células forem as mesmas, essas listas poderão ser consideradas iguais. A ordem em que os valores desta lista são empilhados não importa tanto.
2. Podemos falar sobre coincidência parcial de listas se os próprios valores únicos forem os mesmos, mas o número de repetições for diferente. Portanto, essas listas podem conter um número diferente de elementos.
3. O fato de as duas listas não corresponderem é indicado por um conjunto diferente de valores exclusivos.

Todas essas três condições simultaneamente são as condições do nosso problema.

A solução do problema

Vamos gerar dois intervalos dinâmicos para facilitar a comparação de listas. Cada um deles corresponderá a cada uma das listas.

Para comparar duas listas, faça o seguinte:

1. Em uma coluna separada, criamos uma lista de valores exclusivos que são específicos para ambas as listas. Para isso usamos a fórmula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). A fórmula em si deve ser escrita como uma fórmula de matriz.
2. Vamos determinar quantas vezes cada valor exclusivo ocorre na matriz de dados. Aqui estão as fórmulas para fazer isso: =CONTARSE(Lista1,D5) e =CONTARI(Lista2,D5).
3. Se o número de repetições e o número de valores únicos forem iguais em todas as listas incluídas nesses intervalos, a função retornará o valor 0. Isso indica que a correspondência é XNUMX%. Nesse caso, os títulos dessas listas adquirirão um fundo verde.
4. Se todo o conteúdo exclusivo estiver em ambas as listas, será retornado por fórmulas =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) o valor será zero. Se E1 não contiver zero, mas esse valor estiver contido nas células E2 e F2, nesse caso os intervalos serão reconhecidos como correspondentes, mas apenas parcialmente. Nesse caso, os títulos das listas correspondentes ficarão laranjas.
5. E se uma das fórmulas descritas acima retornar um valor diferente de zero, as listas serão completamente não correspondentes.

Esta é a resposta para a questão de como analisar colunas para correspondências usando fórmulas. Como você pode ver, com o uso de funções, você pode implementar praticamente qualquer tarefa que, à primeira vista, não esteja relacionada à matemática.

Teste de exemplo

Em nossa versão da tabela, existem três tipos de listas de cada tipo descrito acima. Tem correspondência parcial e completa, bem como não correspondência.

Para comparar os dados, usamos o intervalo A5:B19, no qual inserimos alternadamente esses pares de listas. Sobre qual será o resultado da comparação, entenderemos pela cor das listas originais. Se forem completamente diferentes, será um fundo vermelho. Se parte dos dados for o mesmo, amarelo. No caso de identidade completa, os títulos correspondentes serão verdes. Como fazer uma cor dependendo de qual é o resultado? Isso requer formatação condicional.

Encontrar diferenças em duas listas de duas maneiras

Vamos descrever mais dois métodos para encontrar diferenças, dependendo se as listas são síncronas ou não.

Opção 1. Listas Síncronas

Esta é uma opção fácil. Suponha que temos essas listas.

Para determinar quantas vezes os valores não convergiram, você pode usar a fórmula: =SOMAPRODUTO(—(A2:A20<>B2:B20)). Se obtivermos 0 como resultado, isso significa que as duas listas são iguais.

Opção 2: listas embaralhadas

Se as listas não forem idênticas na ordem dos objetos que contêm, você precisará aplicar um recurso como formatação condicional e colorir valores duplicados. Ou use a função COUNTIF, usando o qual determinamos quantas vezes um elemento de uma lista ocorre na segunda.

Como comparar 2 colunas linha por linha

Quando comparamos duas colunas, geralmente precisamos comparar informações que estão em linhas diferentes. Para fazer isso, o operador nos ajudará E SE. Vamos ver como isso funciona na prática. Para isso, apresentamos várias situações ilustrativas.

Exemplo. Como comparar 2 colunas para correspondências e diferenças em uma linha

Para analisar se os valores que estão na mesma linha, mas em colunas diferentes, são os mesmos, escrevemos a função IF. A fórmula é inserida em cada linha colocada na coluna auxiliar onde serão exibidos os resultados do processamento de dados. Mas não é necessário prescrevê-lo em cada linha, basta copiá-lo nas células restantes desta coluna ou usar o marcador de preenchimento automático.

Devemos anotar tal fórmula para entender se os valores em ambas as colunas são os mesmos ou não: =SE(A2=B2, “Corresponder”, “”). A lógica desta função é muito simples: ela compara os valores nas células A2 e B2, e se forem iguais, exibe o valor “Coincidir”. Se os dados forem diferentes, ele não retornará nenhum valor. Você também pode verificar as células para ver se há uma correspondência entre elas. Neste caso, a fórmula utilizada é: =SE(A2<>B2, “Não coincide”, “”). O princípio é o mesmo, primeiro é feita a verificação. Se as células atenderem ao critério, o valor "Não corresponde" será exibido.

Também é possível usar a seguinte fórmula no campo de fórmula para exibir tanto “Corresponder” se os valores forem iguais, quanto “Não corresponder” se forem diferentes: =SE(A2=B2; “Corresponde”, “Não corresponde”). Você também pode usar o operador de desigualdade em vez do operador de igualdade. Apenas a ordem dos valores que serão exibidos neste caso será um pouco diferente: =SE(A2<>B2, “Não coincide”, “Coincide”). Após usar a primeira versão da fórmula, o resultado será o seguinte.

Essa variação da fórmula não diferencia maiúsculas de minúsculas. Portanto, se os valores em uma coluna diferem dos outros apenas por serem escritos em letras maiúsculas, o programa não notará essa diferença. Para fazer a comparação com distinção entre maiúsculas e minúsculas, você precisa usar a função nos critérios EXATO. Os demais argumentos permanecem inalterados: =SE(EXATO(A2,B2), “Correspondência”, “Único”).

Como comparar várias colunas para correspondências em uma linha

É possível analisar os valores nas listas de acordo com todo um conjunto de critérios:

1. Encontre aquelas linhas que têm os mesmos valores em todos os lugares.
2. Encontre as linhas onde há correspondências em apenas duas listas.

Vejamos alguns exemplos de como proceder em cada um desses casos.

Exemplo. Como encontrar correspondências em uma linha em várias colunas de uma tabela

Suponha que temos uma série de colunas que contêm as informações de que precisamos. Estamos diante da tarefa de determinar aquelas linhas em que os valores são os mesmos. Para fazer isso, você precisa usar a seguinte fórmula: =SE(E(A2=B2,A2=C2), “corresponder”, ” “).

Se houver muitas colunas na tabela, você só precisa usá-lo junto com a função IF operador COUNTIF: =SE(CONT.SE($A2:$C2,$A2)=3;”correspondência”;” “). O número usado nesta fórmula indica o número de colunas a serem verificadas. Se for diferente, você precisará escrever o máximo que for verdade para sua situação.

Exemplo. Como encontrar correspondências em uma linha em quaisquer 2 colunas de uma tabela

Digamos que precisamos verificar se os valores em uma linha correspondem em duas colunas daqueles da tabela. Para fazer isso, você precisa usar a função como condição OR, onde alternadamente escreva a igualdade de cada uma das colunas com a outra. Aqui está um exemplo.

Usamos esta fórmula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Pode haver uma situação em que haja muitas colunas na tabela. Nesse caso, a fórmula será enorme e pode levar muito tempo para selecionar todas as combinações necessárias. Para resolver este problema, você precisa usar a função COUNTIF: =SE(CONT.SE(B2:D2,A2)+CONT.SE(C2:D2,B2)+(C2=D2)=0; “String exclusiva”; “String não exclusiva”)

Vemos que no total temos duas funções COUNTIF. Com o primeiro, determinamos alternadamente quantas colunas têm semelhança com A2 e, com o segundo, verificamos o número de semelhanças com o valor de B2. Se, como resultado do cálculo por esta fórmula, obtivermos um valor zero, isso indica que todas as linhas desta coluna são únicas, se mais, há semelhanças. Portanto, se como resultado do cálculo por duas fórmulas e somando os resultados finais obtivermos um valor zero, o valor do texto “String única” será retornado, se esse número for maior, está escrito que essa string não é única.

Como comparar 2 colunas no Excel para correspondências

Agora vamos dar um exemplo. Digamos que temos uma tabela com duas colunas. Você precisa verificar se eles combinam. Para fazer isso, você precisa aplicar a fórmula, onde a função também será usada IF, e o operador COUNTIF: =SE(CONT.SE($B:$B,$A5)=0, “Nenhuma correspondência na coluna B”, “Há correspondências na coluna B”)

Não é necessária nenhuma ação adicional. Após calcular o resultado por esta fórmula, obtemos se o valor do terceiro argumento da função IF fósforos. Se não houver nenhum, então o conteúdo do segundo argumento.

Como comparar 2 colunas no Excel para correspondências e destacar com cor

Para facilitar a identificação visual das colunas correspondentes, você pode realçá-las com uma cor. Para fazer isso, você precisa usar a função “Formatação Condicional”. Vamos ver na prática.

Localizando e destacando correspondências por cor em várias colunas

Para determinar as correspondências e destacá-las, primeiro você deve selecionar o intervalo de dados em que a verificação será realizada e, em seguida, abrir o item “Formatação Condicional” na guia “Início”. Lá, selecione “Valores Duplicados” como a regra de seleção de células.

Depois disso, uma nova caixa de diálogo aparecerá, na qual na lista pop-up à esquerda encontramos a opção “Repetir” e na lista à direita selecionamos a cor que será usada para a seleção. Após clicarmos no botão “OK”, o plano de fundo de todas as células com semelhanças será selecionado. Depois é só comparar as colunas a olho nu.

Localizando e destacando linhas correspondentes

A técnica para verificar se as strings correspondem é um pouco diferente. Primeiro, precisamos criar uma coluna adicional, e nela usaremos os valores combinados usando o operador &. Para fazer isso, você precisa escrever uma fórmula da forma: =A2&B2&C2&D2.

Selecionamos a coluna que foi criada e contém os valores combinados. Em seguida, executamos a mesma sequência de ações descrita acima para as colunas. Linhas duplicadas serão destacadas na cor que você especificar.

Vemos que não há nada difícil em procurar repetições. O Excel contém todas as ferramentas necessárias para isso. É importante apenas praticar antes de colocar todo esse conhecimento em prática.