Compare duas colunas para encontrar duplicatas no Excel (guia completo)
Identificar valores duplicados em duas colunas no Excel é uma tarefa comum na análise de dados. Isso ajuda a identificar erros de entrada de dados, registros duplicados ou para fins de limpeza de dados. Este artigo irá guiá-lo sobre como identificar duplicatas ou correspondências em duas colunas com eficiência e precisão.
Compare duas colunas para encontrar valores duplicados
- Destaque duplicatas em duas colunas com formatação condicional
- Selecione e destaque duplicatas em duas colunas com uma ferramenta poderosa – Kutools
- Encontre e extraia duplicatas em duas colunas com fórmulas
- Selecione duplicatas em duas colunas com código VBA
Compare duas colunas para encontrar valores duplicados
Para encontrar valores duplicados entre duas colunas, existem vários métodos disponíveis dependendo de suas necessidades, como destacar duplicatas para uma visão geral ou extraí-las para análise aprofundada. Nesta seção, apresentaremos alguns truques rápidos para realizar esta tarefa no Excel.
Destaque duplicatas em duas colunas com formatação condicional
Destacar duplicatas em duas colunas no Excel é uma maneira eficiente de identificar dados repetidos, especialmente em grandes conjuntos de dados onde a revisão manual é impraticável. Neste caso, a Formatação Condicional é um recurso útil para resolver esta tarefa.
Etapa 1: selecione os dados na coluna da qual deseja destacar as duplicatas
Neste exemplo, selecionarei A2:A10, veja a captura de tela:
Etapa 2: aplicar o recurso Formatação Condicional
- Clique Início > Formatação condicional > Nova regra, veja a captura de tela:
- No Nova regra de formatação caixa de diálogo, execute as seguintes operações:
- 2.1 Selecione Use uma fórmula para determinar quais células formatar do Selecione um tipo de regra caixa de listagem;
- 2.2 Digite a seguinte fórmula no Formate os valores onde esta fórmula for verdadeira caixa de texto;
=COUNTIF($B$2:$B$10, A2)>0
- Note: Na fórmula acima, B2: B10 representa a lista de dados com a qual você deseja comparar, A2 é a primeira célula da coluna de onde você deseja destacar as duplicatas. Esta fórmula verifica se o valor na célula A2 é encontrado em qualquer lugar da coluna B. Modifique as referências das células para se adequar aos seus dados.
- 2.3 Em seguida, clique Formato botão.
- No pop-out formatar células caixa de diálogo, especifique uma cor para destacar os itens duplicados. E clique OK.
- Quando ele retorna o Nova regra de formatação caixa de diálogo, clique em OK botão.
Resultado:
Agora, os valores que estão duplicados nas colunas A e B estão destacados na coluna A conforme mostrado a seguir:
- A regra duplicada em Formatação condicional is não diferencia maiúsculas de minúsculas. Portanto, Apple e apple seriam marcados como duplicados.
- Se quiser destacar as duplicatas da coluna B, você só precisa selecionar a coluna B primeiro e, em seguida, aplicar a seguinte fórmula em Formatação condicional:
=COUNTIF($A$2:$A$10, B2)>0
Selecione e destaque duplicatas em duas colunas com uma ferramenta poderosa – Kutools
Às vezes, você pode precisar não apenas destacar, mas também selecionar duplicatas para copiá-las e colá-las em outro local da sua pasta de trabalho. Em tais casos, Kutools for Excel'S Selecionar células iguais e diferentes recurso é uma escolha ideal. Ele pode identificar os valores duplicados ou exclusivos destacando e selecionando as células necessárias.
Clique Kutools > Selecionar > Selecionar células iguais e diferentes, Na Selecionar células iguais e diferentes caixa de diálogo, execute as seguintes operações:
- Selecione os dados de origem e os dados comparados na caixa Encontre valores em e De acordo com o caixas separadamente;
- Selecionar Cada fila sob a Baseado em seção;
- Escolha Mesmos valores do Encontre seção;
- Especifique uma cor de fundo para destacar os valores duplicados sob o Processamento de resultados seção;
- Finalmente, clique em OK botão.
Resultado:
Agora, os valores duplicados nas colunas A e B são destacados e selecionados na coluna A, prontos para serem copiados e colados nas células desejadas. Veja a captura de tela:
- Este recurso suporta distinção entre maiúsculas e minúsculas comparação enquanto marca Maiúsculas e minúsculas caixa de seleção no Selecionar células iguais e diferentes diálogo;
- Se você deseja selecionar as duplicatas da coluna B, basta trocar as duas colunas selecionadas no Encontre valores em e De acordo com o caixas do Selecionar células iguais e diferentes caixa de diálogo;
- Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.
Encontre e extraia duplicatas em duas colunas com fórmula
Para localizar e extrair duplicatas entre duas colunas, você pode usar a fórmula para localizar e extrair as duplicatas.
Copie e cole a seguinte fórmula em uma célula em branco onde deseja colocar o resultado e arraste a alça de preenchimento para baixo na coluna para aplicar esta fórmula a outras células.
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
Note: Na fórmula acima, A2 é a primeira célula da coluna de onde você deseja encontrar as duplicatas; B2: B10 representa a lista de dados com a qual você deseja comparar.
Resultado:
Como você pode ver, se os dados da Coluna A existirem na Coluna B, o valor será exibido; caso contrário, as células ficarão em branco.
Selecione duplicatas em duas colunas com código VBA
Esta seção irá guiá-lo pelas etapas para criar um código VBA que identifica e seleciona valores duplicados entre duas colunas.
Passo 1: Abra o editor do módulo VBA e copie o código
- Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
- Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
- Em seguida, copie e cole o código abaixo no módulo em branco.
Código VBA: Encontre e selecione os valores duplicados entre duas colunasSub Compare() 'Update by Extendoffice Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" On Error Resume Next Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub
Etapa 2: execute este código VBA
- Depois de colar este código, pressione F5 chave para executar este código. Na primeira caixa de prompt, selecione a lista de dados da qual deseja selecionar duplicatas. E então, clique OK.
- Na segunda caixa de prompt, selecione a lista de dados com a qual deseja comparar e clique em OK, veja a captura de tela:
Resultado:
Agora, os valores duplicados das Colunas A e B são selecionados na Coluna A, permitindo preencher as células com cores ou copiá-las e colá-las conforme necessário.
- Este código VBA é maiúsculas e Minúsculas;
- Se você deseja selecionar as duplicatas da coluna B, basta trocar as duas colunas selecionadas ao selecionar o intervalo de dados.
Compare duas colunas para correspondências linha por linha
No Excel, muitas vezes é necessário comparar duas colunas linha por linha para verificar correspondências, o que ajuda em tarefas como verificação de registros ou análise de tendências de dados. O Excel possui diferentes maneiras de fazer isso, desde fórmulas fáceis até recursos especiais, para que você possa escolher a melhor para as necessidades dos seus dados. Vamos dar uma olhada em alguns métodos simples para realizar esse trabalho de maneira eficaz.
Compare duas colunas na mesma linha com fórmulas
As fórmulas do Excel oferecem uma abordagem simples, porém poderosa, para comparação de dados entre colunas. Veja como você pode usá-los. Supondo que você tenha dados na Coluna A e na Coluna B, para verificar se os dados nas duas colunas correspondem, as seguintes fórmulas podem ajudá-lo:
- Usando o operador Igual a (=): a maneira mais simples de comparar duas células
- Função SE: para tornar sua comparação mais informativa
- Função EXATA: uma comparação que diferencia maiúsculas de minúsculas
Usando o operador Igual a (=):
Por favor, insira ou copie a seguinte fórmula, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará TRUE se os valores na mesma linha das Colunas A e B forem idênticos e FALSE se não forem. Veja a captura de tela:
=A2=B2
Função SE:
Se você quiser tornar a comparação mais informativa, você pode usar o Função IF para exibir mensagens personalizadas.
Por favor insira ou copie a fórmula abaixo, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará Match quando os valores forem iguais e No Match quando forem diferentes. Veja a captura de tela:
=IF(A2=B2, "Match", "No Match")
Função EXATA:
Se você precisar de uma comparação com distinção entre maiúsculas e minúsculas, o Função EXATA é o caminho a seguir.
Por favor, insira ou copie a seguinte fórmula, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará Match quando os valores corresponderem exatamente e No Match quando forem diferentes. Veja a captura de tela:
=IF(EXACT(A2,B2), "Match", "No match")
Selecione e destaque correspondências na mesma linha com uma ferramenta útil - Kutools
Se você precisar selecionar e sombrear as correspondências entre duas colunas linha por linha, em vez de obter o resultado em uma coluna separada, o recurso Comparar células do Kutools para Excel será uma excelente opção. Ele permite que você selecione e aplique rapidamente uma cor de preenchimento às células que correspondem ou diferem em valores dentro de cada linha.
Clique Kutools > Comparar células, Na Comparar células caixa de diálogo, execute as seguintes operações:
- Selecione os dados das duas colunas na Encontre valores em e De acordo com o caixas separadamente;
- Selecionar Mesmas células sob a Encontre seção;
- Especifique uma cor de fundo para destacar as correspondências sob o Processamento de resultados seção;
- Finalmente, clique em OK botão.
Resultado:
Agora, as correspondências da mesma linha são destacadas e selecionadas na coluna A, disponibilizando-as para você copiar e colar nas células desejadas. Veja a captura de tela:
- Este recurso suporta distinção entre maiúsculas e minúsculas comparação se você verificar o Maiúsculas e minúsculas opção no Comparar células caixa de diálogo;
- Se você quiser selecionar as correspondências da coluna B, basta trocar as duas colunas selecionadas na Encontre valores em e De acordo com o caixas do Comparar células caixa de diálogo;
- Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.
Compare duas colunas e destaque as correspondências na mesma linha
Comparar duas colunas e destacar correspondências na mesma linha pode ser feito com eficiência usando a Formatação Condicional no Excel. Aqui está um guia para identificar e destacar correspondências de linha:
Etapa 1: selecione o intervalo de dados
Selecione o intervalo de dados em que deseja destacar as correspondências de linha.
Etapa 2: aplicar o recurso Formatação Condicional
- Clique Início > Formatação condicional > Nova regra. No Nova regra de formatação caixa de diálogo, execute as seguintes operações:
- 2.1 Selecione Use uma fórmula para determinar quais células formatar do Selecione um tipo de regra caixa de listagem;
- 2.2 Digite a fórmula abaixo no Formate os valores onde esta fórmula for verdadeira caixa de texto;
=$B2=$A2
- 2.3 Em seguida, clique Formato botão.
- No pop-out formatar células caixa de diálogo, especifique uma cor para destacar os itens duplicados. E clique OK.
- Quando ele retorna o Nova regra de formatação caixa de diálogo, clique em OK botão.
Resultado:
Agora, os valores correspondentes na mesma linha são destacados de uma só vez, veja a captura de tela:
- A fórmula na formatação condicional é não diferencia maiúsculas de minúsculas.
- Se você pretende destacar células com valores diferentes, aplique a seguinte fórmula:
=$B2<>$A2
Compare duas colunas e extraia dados correspondentes
Quando você está lidando com dois conjuntos de dados no Excel e precisa encontrar itens comuns de uma lista na outra, as fórmulas de pesquisa são a solução ideal para recuperar essas correspondências.
No Excel, se você tiver uma lista de frutas na coluna A e seus números de vendas na coluna B, agora deseja combiná-los com uma seleção de frutas na coluna D para encontrar suas vendas correspondentes. Como você poderia retornar os valores relativos da coluna B no Excel?
Aplique qualquer uma das fórmulas a seguir necessárias e, em seguida, arraste a alça de preenchimento para baixo para aplicar esta fórmula às demais células.
- Todas as versões do Excel:
=VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
- Excel 365 e Excel 2021:
=XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
Resultado:
Todos os valores correspondentes serão exibidos se uma correspondência for encontrada, caso contrário o erro #N/A será retornado, veja a captura de tela:
- Com as fórmulas acima, se houver frutas na Coluna D que não correspondam na Coluna A, elas retornarão um erro. Para facilitar a compreensão desses erros, você pode agrupar sua fórmula com a função IFERROR:
- Todas as versões do Excel:
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
- Excel 365 e Excel 2021:
=IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
- Todas as versões do Excel:
- Para aqueles interessados em fórmulas de pesquisa avançadas, Kutools for Excel fornece um conjunto impressionante de fórmulas de pesquisa avançadas que levam a função VLOOKUP tradicional a novos patamares, proporcionando precisão e eficiência incomparáveis em suas tarefas de gerenciamento de dados.
Kutools for Excel possui uma coleção de mais de 300 ferramentas convenientes projetadas para aumentar sua produtividade. Experimente todo o poder com uma avaliação gratuita de 30 dias e eleve suas planilhas hoje mesmo! Get it Now!
Artigos relacionados:
- Encontre e destaque linhas duplicadas em um intervalo
- Às vezes, pode haver alguns registros duplicados no intervalo de dados de uma planilha e agora você deseja localizar ou destacar as linhas duplicadas no intervalo, conforme as capturas de tela a seguir mostradas. Claro que você pode encontrá-los um após um, verificando as linhas. Mas essa não é uma boa escolha se houver centenas de linhas. Aqui, vou falar sobre algumas maneiras úteis para você lidar com essa tarefa.
- Destaque valores duplicados em cores diferentes
- No Excel, podemos destacar facilmente os valores duplicados em uma coluna com uma cor usando a Formatação Condicional, mas, às vezes, precisamos destacar os valores duplicados em cores diferentes para reconhecer as duplicatas de forma rápida e fácil, conforme a captura de tela a seguir. Como você resolveria essa tarefa no Excel?
- Encontre, destaque, filtre, conte, exclua duplicatas no Excel
- No Excel, dados duplicados ocorrem repetidamente quando registramos dados manualmente, copiamos dados de outras fontes ou por outros motivos. Às vezes, as duplicatas são necessárias e úteis. No entanto, às vezes, os valores duplicados levam a erros ou mal-entendidos. Aqui, este artigo apresentará métodos para identificar, destacar, filtrar, contar, excluir duplicatas por fórmulas, regras de formatação condicional, suplementos de terceiros etc. no Excel.
- Remova duplicatas e substitua por células em branco
- Normalmente, quando você aplica o comando Remover Duplicatas no Excel, ele remove todas as linhas duplicadas. Mas, às vezes, você deseja que as células em branco substituam os valores duplicados; nessa situação, o comando Remover Duplicado não funcionará. Este artigo irá guiá-lo para remover duplicatas e substituí-las por células em branco no Excel.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Índice
- Compare duas colunas para encontrar valores duplicados
- Com formatação condicional
- Com uma ferramenta poderosa – Kutools
- Com fórmulas
- Com código VBA
- Compare duas colunas para correspondências linha por linha
- Com fórmulas
- Com uma ferramenta útil - Kutools
- Com formatação condicional
- Compare duas colunas e extraia dados correspondentes
- Artigos Relacionados
- As melhores ferramentas de produtividade para escritório
- Comentários