Como comparar colunas com valores diferentes no no excel

Índice

Assistir ao vídeo - Compare duas colunas no Excel para correspondências e diferenças

A única consulta que recebo com frequência é - ‘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.

Já que sou muito questionado sobre isso, decidi escrever este tutorial massivo com a intenção de cobrir a maioria (senão todos) os cenários possíveis.

Se você achar isso útil, passe-o para outros usuários do Excel.

Observe que as técnicas para comparar colunas mostradas neste tutorial não são as únicas.

Com base no seu conjunto de dados, pode ser necessário alterar ou ajustar o método. No entanto, os princípios básicos permaneceriam os mesmos.

Se você acha que há algo que pode ser adicionado a este tutorial, deixe-me saber na seção de comentários

Compare duas colunas para correspondência exata de linha

Esta é a forma mais simples de comparação. Nesse caso, você precisa fazer uma comparação linha por linha e identificar quais linhas têm os mesmos dados e quais não.

Exemplo: comparar células na mesma linha

Abaixo está um conjunto de dados onde 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, 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 quiser obter um resultado mais descritivo, você pode usar uma fórmula IF simples para retornar “Match” quando os nomes são iguais e “Mismatch” quando os nomes são diferentes.

= SE (A2 = B2, "Correspondência", "Incompatibilidade")

Observação: caso você queira fazer a comparação com distinção entre maiúsculas e minúsculas, use a seguinte fórmula SE:

= SE (EXATO (A2, B2), "Correspondência", "Incompatibilidade")

Com a fórmula acima, ‘IBM’ e ‘ibm’ seriam considerados dois nomes diferentes e a fórmula acima retornaria ‘Mismatch’.

Exemplo: Destacar linhas com dados correspondentes

Se quiser destacar as linhas que possuem dados correspondentes (em vez de obter o resultado em uma coluna separada), você pode fazer isso usando a Formatação Condicional.

Aqui estão as etapas para fazer isso:

1. Selecione todo o conjunto de dados.
2. Clique na guia ‘Página inicial’.
3. No grupo Estilos, clique na opção ‘Formatação condicional’.
4. No menu suspenso, clique em ‘Nova regra’.
5. Na caixa de diálogo ‘Nova regra de formatação’, clique em ‘Usar uma fórmula para determinar quais células formatar’.
6. No campo de fórmula, insira a fórmula: = $ A1 = $ B1
7. Clique no botão Formato e especifique o formato que deseja aplicar às células correspondentes.
8. Clique OK.

Isso irá destacar todas as células onde os nomes são os mesmos em cada linha.

Comparar duas colunas e destacar correspondências

Se você deseja comparar duas colunas e realçar os dados correspondentes, pode usar a funcionalidade duplicada na formatação condicional.

Observe que isso é diferente do que vimos ao comparar cada linha. Nesse caso, não faremos uma comparação linha por linha.

Exemplo: compare duas colunas e realce os dados correspondentes

Freqüentemente, você obterá conjuntos de dados onde há correspondências, mas eles podem não estar na mesma linha.

Algo conforme mostrado abaixo:

Observe que a lista na coluna A é maior do que a da B. Além disso, alguns nomes estão em ambas as listas, mas não na mesma linha (como IBM, Adobe, Walmart).

Se quiser destacar todos os nomes de empresas correspondentes, você pode fazer isso usando a formatação condicional.

Aqui estão as etapas para fazer isso:

1. Selecione todo o conjunto de dados.
2. Clique na guia Página inicial.
3. No grupo Estilos, clique na opção ‘Formatação condicional’.
4. Passe o cursor sobre a opção Destacar Regras de Célula.
5. Clique em Duplicate Values.
6. Na caixa de diálogo Valores Duplicados, certifique-se de que ‘Duplicar’ esteja selecionado.
7. Especifique a formatação.
8. Clique OK.

As etapas acima fornecerão o resultado mostrado abaixo.

Nota: A regra de duplicação da Formatação Condicional não diferencia maiúsculas de minúsculas. Portanto, ‘Apple’ e ‘apple’ são considerados iguais e seriam destacados como duplicatas.

Exemplo: comparar duas colunas e destacar dados incompatíveis

Caso queira destacar os nomes que estão presentes em uma lista e não na outra, você pode usar a formatação condicional para isso também.

1. Selecione todo o conjunto de dados.
2. Clique na guia Página inicial.
3. No grupo Estilos, clique na opção ‘Formatação condicional’.
4. Passe o cursor sobre a opção Destacar Regras de Célula.
5. Clique em Duplicate Values.
6. Na caixa de diálogo Valores Duplicados, certifique-se de que ‘Único’ esteja selecionado.
7. Especifique a formatação.
8. Clique OK.

Isso lhe dará o resultado conforme mostrado abaixo. Ele destaca todas as células que possuem um nome que não está presente na outra lista.

Compare duas colunas e encontre pontos de dados ausentes

Se você deseja identificar se um ponto de dados de uma lista está presente na outra lista, você precisa usar as fórmulas de pesquisa.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja identificar as 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 (PROCV (A2, $ B $ 2: $ B $ 10,1,0))

Esta fórmula usa a função PROCV para verificar se um nome de empresa em A está presente na coluna B ou não. Se estiver presente, ele retornará aquele nome da coluna B, caso contrário, retornará um erro # N / A.

Esses nomes que retornam o erro # N / A são os que estão faltando na coluna B.

A função ISERROR retornaria TRUE se o resultado VLOOKUP fosse um erro e FALSE se não fosse um erro.

Se você deseja obter uma lista de todos os nomes onde não há correspondência, você pode filtrar a coluna de resultado para obter todas as células com TRUE.

Você também pode usar a função MATCH para fazer o mesmo;

= NÃO (ISNUMBER (CORRESPONDÊNCIA (A2, $ B $ 2: $ B $ 10,0)))

Observação: pessoalmente, prefiro usar a função Match (ou a combinação de INDEX / MATCH) em vez de VLOOKUP. Acho que é mais flexível e poderoso. Você pode ler a diferença entre Vlookup e Index / Match aqui.

Compare duas colunas e extraia os dados correspondentes

Se você tiver dois conjuntos de dados e quiser comparar os itens de uma lista com a outra e buscar o ponto de dados correspondente, precisará usar as fórmulas de pesquisa.

Exemplo: extraia os dados correspondentes (exato)

Por exemplo, na lista abaixo, desejo buscar o valor de avaliação de mercado para a coluna 2. Para fazer isso, preciso pesquisar esse valor na coluna 1 e, em seguida, buscar o valor de avaliação de mercado correspondente.

Abaixo está a fórmula que fará isso:

= PROCV (D2, $ A $ 2: $ B $ 14,2,0)

ou

= ÍNDICE ($ A $ 2: $ B $ 14, CORRESPONDÊNCIA (D2, $ A $ 2: $ A $ 14,0), 2)

Exemplo: extraia os dados correspondentes (parcial)

No caso de você obter um conjunto de dados onde há uma pequena diferença nos nomes nas duas colunas, usar as fórmulas de pesquisa mostradas acima não funcionará.

Essas fórmulas de pesquisa precisam de uma correspondência exata para fornecer o resultado correto. Há uma opção de correspondência aproximada na função VLOOKUP ou MATCH, mas não pode ser usada aqui.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo. Observe que há nomes que não estão completos na coluna 2 (como JPMorgan em vez de JPMorgan Chase e Exxon em vez de ExxonMobil).

Nesse caso, você pode usar uma pesquisa parcial usando caracteres curinga.

A fórmula a seguir fornecerá o resultado correto neste caso:

= PROCV ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)

ou

= ÍNDICE ($ A $ 2: $ B $ 14, CORRESPONDÊNCIA ("*" & D2 & "*", $ A $ 2: $ A $ 14,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 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 e tutoriais do Excel:

• Como comparar duas planilhas do Excel (para diferenças)
• Como destacar células em branco no Excel.
• Destaque TODAS AS OUTRAS LINHAS no Excel.
• Filtro avançado do Excel: um guia completo com exemplos.
• Realce linhas com base em um valor de célula no Excel.