Como comparar duas colunas no Excel (para correspondências e diferenças)
Vídeo-Vídeo – Compare duas colunas no Excel para correspondências e diferenças
A única consulta que recebo muito é – ‘como comparar duas colunas no Excel?
Isso pode ser feito de muitas maneiras diferentes, e o método a ser usado dependerá da estrutura de dados e do que o usuário deseja dela.
Por exemplo, você pode querer comparar duas colunas e encontrar ou destacar todos os pontos de dados correspondentes (que estão em ambas as colunas), ou apenas as diferenças (onde um ponto de dados está em uma coluna e não na outra), etc.
Desde que me perguntem tanto sobre isso, decidi escrever este tutorial massivo com a intenção de cobrir a maioria (se não todos) os cenários possíveis.
Se achar útil, passe-o para outros usuários do Excel.
Este tutorial cobre:
Note que as técnicas para comparar colunas mostradas neste tutorial não são as únicas.
Baseado no seu conjunto de dados, você pode precisar alterar ou ajustar o método. Entretanto, os princípios básicos permaneceriam os mesmos.
Se você acha que há algo que pode ser adicionado a este tutorial, me informe na seção de comentários
Comparar duas colunas para comparação exata de linhas
Esta é a forma mais simples de comparação. Neste caso, você precisa fazer uma comparação linha por linha e identificar quais linhas têm os mesmos dados e quais não têm.
Exemplo: Comparar células na mesma linha
Below é um conjunto de dados onde eu preciso verificar se o nome na coluna A é o mesmo na coluna B ou não.
Se houver uma correspondência, preciso do resultado como “VERDADEIRO”, e se não corresponder, então preciso do resultado como “FALSO”.
A fórmula abaixo faria isso:
=A2=B2
Exemplo: Comparar células na mesma linha (usando a fórmula IF)
Se você quiser obter um resultado mais descritivo, você pode usar uma fórmula IF simples para retornar “Match” quando os nomes são os mesmos e “Mismatch” quando os nomes são diferentes.
=IF(A2=B2,"Match","Mismatch")
Nota: Caso queira tornar o caso de comparação sensível, use a seguinte fórmula IF:
=IF(EXACT(A2,B2),"Match","Mismatch")
Com a fórmula acima, ‘IBM’ e ‘ibm’ seriam considerados dois nomes diferentes e a fórmula acima retornaria ‘Mismatch’.
Exemplo: Realce Linhas com dados correspondentes
Se quiser realçar as linhas que têm dados correspondentes (em vez de obter o resultado em uma coluna separada), você pode fazer isso usando Formatação condicional.
Aqui estão os passos para fazer isso:
- Selecione todo o conjunto de dados.
- Clique na guia ‘Home’.
- No grupo Estilos, clique na opção ‘Formatação Condicional’.
- No drop-down, clique em ‘Nova Regra’.
- Na caixa de diálogo ‘Nova Regra de Formatação’, clique em ‘Use a formula to determine which cells to format’.
- No campo da fórmula, insira a fórmula: =$A1=$B1
- Clique no botão Formatar e especifique o formato que deseja aplicar às células correspondentes.
- Clique em OK.
Isto irá destacar todas as células onde os nomes são os mesmos em cada linha.
Comparar Duas Colunas e Destacar Correspondência
Se você quiser comparar duas colunas e destacar os dados correspondentes, você pode usar a funcionalidade duplicada na formatação condicional.
Note que isso é diferente do que vimos ao comparar cada linha. Neste caso, não vamos fazer uma comparação linha por linha.
Exemplo: Comparar Duas Colunas e Dados de Correspondência de Destaques
Muitas vezes, você obterá conjuntos de dados onde houver correspondências, mas estes podem não estar na mesma linha.
Algo como mostrado abaixo:
Note que a lista na coluna A é maior do que a da coluna B. Também há alguns nomes em ambas as listas, mas não na mesma linha (como IBM, Adobe, Walmart).
Se você quiser destacar todos os nomes de empresas correspondentes, você pode fazer isso usando formatação condicional.
Aqui estão os passos para fazer isso:
- Selecione todo o conjunto de dados.
- Clique na guia Home.
- No grupo Estilos, clique na opção ‘Formatação condicional’.
- Passe o cursor sobre a opção Highlight Cell Rules.
- Clique em Duplicate Values.
- Na caixa de diálogo Duplicate Values, certifique-se de que ‘Duplicate’ está selecionado.
- Especifique a formatação.
- Clique OK.
Os passos acima dar-lhe-iam o resultado como mostrado abaixo.
Nota: A regra de formatação condicional de duplicados não é sensível a maiúsculas e minúsculas. Então ‘Apple’ e ‘apple’ são consideradas as mesmas e seriam destacadas como duplicatas.
Exemplo: Compare Two Columns and Highlight Mismatched Data (Comparar duas colunas e destacar dados não combinados)
No caso de querer destacar os nomes que estão presentes numa lista e não na outra, também pode usar a formatação condicional para isto.
- Seleccionar todo o conjunto de dados.
- Clique no separador Home.
- No grupo Styles, clique na opção ‘Conditional Formatting’ (Formatação condicional).
- Passe o cursor sobre a opção Highlight Cell Rules.
- Clique na opção Duplicate Values.
- Na caixa de diálogo Duplicate Values, certifique-se de que ‘Unique’ está selecionado.
- Especifique a formatação.
- Click OK.
Isto dar-lhe-á o resultado como mostrado abaixo. Ele destaca todas as células que têm um nome que não está presente na outra lista.
Comparar duas colunas e encontrar pontos de dados ausentes
Se você quiser identificar se um ponto de dados de uma lista está presente na outra lista, você precisa usar as fórmulas de pesquisa.
Se quiser identificar empresas que estão presentes na coluna A mas não na coluna B,
Para fazer isso, posso usar a seguinte fórmula VLOOKUP.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Esta fórmula usa a função VLOOKUP para verificar se um nome de empresa em A está presente na coluna B ou não. Se estiver presente, retornará esse nome da coluna B, caso contrário retornará um erro #N/A.
Estes nomes que retornam o erro #N/A são os que estão faltando na coluna B.
ISERROR a função TRUE se houver o resultado do VLOOKUP é um erro e FALSO se não for um erro.
Se você quiser obter uma lista de todos os nomes onde não há correspondência, você pode filtrar a coluna de resultados para obter todas as células com TRUE.
Você também pode usar a função MATCH para fazer o mesmo;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Note: Pessoalmente, eu prefiro usar a função Match (ou a combinação de INDEX/MATCH) em vez de VLOOKUP. Acho-a mais flexível e poderosa. Você pode ler a diferença entre Vlookup e Index/Match aqui.
Compare Duas Colunas e Puxe os Dados de Correspondência
Se você tiver dois conjuntos de dados e quiser comparar itens em uma lista com a outra e buscar o ponto de dados de correspondência, você precisa usar as fórmulas de pesquisa.
Exemplo: Puxar os dados correspondentes (Exato)
Por exemplo, na lista abaixo, eu quero ir buscar o valor de avaliação do mercado para a coluna 2. Para fazer isso, preciso procurar esse valor na coluna 1 e depois buscar o valor de avaliação do mercado correspondente.
Below é a fórmula que fará isso:
=VLOOKUP(D2,$A:$B,2,0)
ou
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Exemplo: Puxe os Dados de Correspondência (Parcial)
No caso de você obter um conjunto de dados onde há uma pequena diferença nos nomes nas duas colunas, usando as fórmulas de pesquisa acima não vai funcionar.
Estas fórmulas de pesquisa precisam de uma correspondência exata para dar o resultado certo. Existe uma opção de correspondência aproximada na função VLOOKUP ou MATCH, mas que não pode ser usada aqui.
Suponha que você tenha o conjunto de dados como mostrado abaixo. Note que existem nomes que não estão completos na Coluna 2 (como JPMorgan em vez de JPMorgan Chase e Exxon em vez de ExxonMobil).
Em tal caso, você pode usar uma busca parcial usando caracteres curinga.
A seguinte fórmula dará o resultado certo neste caso:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
ou
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
No exemplo acima, o asterisco (*) é um caractere curinga que pode representar qualquer número de caracteres. Quando o valor de pesquisa é flanqueado com ele em ambos os lados, qualquer valor na Coluna 1 que contenha o valor de pesquisa na Coluna 2 seria considerado como uma correspondência.
Por exemplo, *Exxon* seria uma correspondência para ExxonMobil (pois * pode representar qualquer número de caracteres).
Você também pode gostar das seguintes dicas do Excel & Tutoriais:
- Como comparar duas planilhas do Excel (para diferenças)
- Como destacar células em branco no Excel.
- Luz alta para TODAS as outras planilhas do Excel.
- Filtro Avançado do Excel: Um Guia Completo com Exemplos.
- Linhas de Luz Alta Baseadas em um Valor de Célula no Excel.