Como comparar duas colunas no Excel e encontrar correspondências e diferenças

Você tem duas listas no Excel, por exemplo os códigos de produto do estoque e os que aparecem no catálogo on-line, e precisa saber quais estão nas duas, quais faltam em uma e quais sobram. Conferir no olho funciona com dez linhas; com quinhentas, é fácil deixar algo passar.

Aqui estão os métodos mais úteis para comparar duas colunas, da comparação linha a linha até a extração automática das diferenças, com fórmulas que você pode copiar do jeito que estão.

Os dados de exemplo

Vamos usar esta planilha: na coluna A, os códigos do estoque; na B, os do catálogo on-line. Os cabeçalhos estão na linha 1 e os dados vão da linha 2 à 7.

Antes de escolher um método, faça uma pergunta: a posição importa? Se cada linha representa a mesma coisa nas duas colunas (por exemplo, o preço antigo e o novo de um produto), compare linha a linha. Se são duas listas independentes em ordens diferentes, você precisa procurar cada valor na outra coluna.

Comparar linha a linha

O jeito mais simples é digitar uma igualdade em C2 e copiá-la para baixo:

=A2=B2

Ela retorna VERDADEIRO ou FALSO. Se preferir um texto mais claro:

=SE(A2=B2;"Igual";"Diferente")

Com os dados de exemplo, só as linhas 2 e 4 aparecem como “Igual”. Repare que P-102 e P-104 estão nas duas colunas, mas em linhas diferentes, então esse método os marca como diferentes. Por isso ele só serve quando as linhas estão alinhadas.

Diferenciar maiúsculas e minúsculas

O operador = não distingue “abc” de “ABC”. Se eles precisarem contar como diferentes, use EXATO:

=SE(EXATO(A2;B2);"Igual";"Diferente")

Saber se um valor está na outra coluna

Quando a ordem não importa, a ferramenta principal é CONT.SE. Digite em C2:

=SE(CONT.SE($B$2:$B$7;A2)>0;"Está no catálogo";"Falta no catálogo")

A fórmula conta quantas vezes A2 aparece no intervalo B2:B7. Se for mais de zero, o código está lá. Os cifrões $ fixam o intervalo para que ele não se desloque ao copiar a fórmula. Resultado: P-101, P-102, P-103 e P-104 estão no catálogo; P-105 e P-106 faltam.

Para o caminho inverso (o que está no catálogo, mas não no estoque), digite em D2 e copie para baixo:

=SE(CONT.SE($A$2:$A$7;B2)=0;"Não está no estoque";"")

Assim você descobre P-107 e P-108.

Alternativa com CORRESP

Se você prefere funções de pesquisa, esta fórmula dá o mesmo resultado:

=SE(ÉNÚM(CORRESP(A2;$B$2:$B$7;0));"Sim";"Não")

CORRESP retorna a posição do valor ou #N/D se não o encontrar, e ÉNÚM transforma isso em VERDADEIRO ou FALSO. Se você também quiser trazer um dado da outra lista, como o preço publicado em uma coluna C do catálogo, use PROCV ou, no Excel 365 e 2021, PROCX.

Destacar correspondências e diferenças com formatação condicional

Se você prefere ver cores em vez de uma coluna auxiliar, a formatação condicional é ideal.

Marcar os valores que estão nas duas colunas

1. Selecione A2:B7.
2. Vá em Página Inicial › Formatação Condicional › Realçar Regras das Células › Valores Duplicados.
3. Escolha a cor e clique em OK.

O Excel colore todo valor que aparece mais de uma vez na seleção. Atenção: se uma mesma coluna tiver repetidos, eles também serão marcados, mesmo que não estejam na outra.

Marcar só o que falta na outra coluna

1. Selecione A2:A7, começando por A2.
2. Vá em Página Inicial › Formatação Condicional › Nova Regra.
3. Escolha Usar uma fórmula para determinar quais células devem ser formatadas.
4. Digite a fórmula abaixo, clique em Formatar, escolha um preenchimento e confirme.

=CONT.SE($B$2:$B$7;A2)=0

Só P-105 e P-106 serão coloridos. Repita o processo em B2:B7 com =CONT.SE($A$2:$A$7;B2)=0 para marcar P-107 e P-108.

Extrair as diferenças em uma lista separada

No Excel 365 e no Excel 2021, a função FILTRO retorna diretamente a lista de valores que atendem a uma condição. Para obter os códigos do estoque que não estão no catálogo, digite em uma célula vazia:

=FILTRO(A2:A7;CONT.SE(B2:B7;A2:A7)=0;"Nenhum")

O resultado se espalha para baixo com P-105 e P-106. Para as correspondências, mude a condição:

=FILTRO(A2:A7;CONT.SE(B2:B7;A2:A7)>0;"Nenhum")

Se os dados crescerem, transforme o intervalo em tabela em Inserir › Tabela ou amplie os intervalos. Evite incluir linhas vazias: CONT.SE também compara células em branco e pode acrescentar resultados vazios. Em versões anteriores, use a coluna auxiliar com CONT.SE e aplique um filtro nela em Dados › Filtro.

Erros comuns ao comparar colunas

• Espaços invisíveis. “P-101 ” com um espaço no final não é igual a “P-101”. Limpe antes com =ARRUMAR(A2) em uma coluna auxiliar.
• Números armazenados como texto. 101 como número e “101” como texto não coincidem. Se aparecer um triângulo verde no canto da célula, converta com a opção Converter em Número.
• Curingas nos dados. CONT.SE interpreta * e ? como curingas. Se seus códigos tiverem esses caracteres, use CORRESP com uma igualdade ou EXATO.
• Esquecer os cifrões. Sem referências absolutas, o intervalo se desloca ao copiar a fórmula e os resultados deixam de ser confiáveis.
• Duplicados dentro da mesma lista. Eles distorcem as contagens e a regra de valores duplicados. Se uma coluna tiver repetidos, elimine-os primeiro com nossa ferramenta para remover duplicados.

Quando você precisa comparar arquivos inteiros

As fórmulas funcionam muito bem com duas colunas da mesma planilha. Mas se você tem duas versões de um arquivo, por exemplo a lista de preços do mês anterior e a atual, com muitas colunas e linhas, montar fórmulas para cada campo fica cansativo. Nesse caso, você pode usar nossa ferramenta gratuita para comparar arquivos Excel: você envia o arquivo original e o modificado e recebe um novo arquivo com as alterações entre os dois, sem escrever nenhuma fórmula.

Conclusão

Para comparar duas colunas, primeiro decida se a posição importa. Se as linhas estão alinhadas, basta =A2=B2 ou SE. Se são listas independentes, CONT.SE mostra o que está nas duas e o que falta, a formatação condicional deixa isso visível e FILTRO entrega as diferenças em uma lista limpa. E antes de tudo, confira espaços, tipos de dados e duplicados: eles causam a maioria dos resultados estranhos.

Perguntas frequentes