Como comparar duas planilhas do Excel para duplicatas: 5 maneiras rápidas

Ao trabalhar com grandes conjuntos de dados no Excel, é comum ter várias planilhas contendo dados semelhantes. Comparar essas planilhas para registros duplicados pode ser um grande desafio, especialmente quando os dados estão espalhados por várias colunas e linhas.

Os métodos mais comuns para encontrar duplicatas em duas planilhas do Excel são:

1. Funções VLOOKUP, COUNTIF ou EXACT

2. Formatação condicional

3. Consulta avançada

4. Ferramentas externas e suplementos

5. Verificações visuais de duplicatas

Este artigo descreve passo a passo esses cinco métodos para identificar e lidar com duplicatas em várias planilhas. No final, você verá como é fácil gerenciar valores duplicados.

Vamos começar!

Índice

• Exemplos de planilhas e dados
• 1. Usando as funções VLOOKUP, COUNTIF ou EXACT para encontrar duplicatas
  • A. Como você usa a função VLOOKUP para localizar duplicatas em duas planilhas?
    • E se você estiver lidando com pastas de trabalho diferentes?
  • B. Como você usa a função CONT.SE para encontrar duplicatas em planilhas?
  • C. Como você usa a função EXACT para encontrar duplicatas em planilhas?
• 2. Como usar formatação condicional para linhas duplicadas
  • Como usar o gerenciador de regras de formatação condicional
• 3. Como usar o Power Query para localizar duplicatas em planilhas
• 4. Ferramentas e suplementos para identificar duplicatas em planilhas
• 5. Como verificar visualmente se há duplicatas em duas folhas
• 3 dicas para preparar suas planilhas do Excel
• Como lidar com erros e inconsistências
• Pensamentos finais

Exemplos de planilhas e dados

Para seguir os exemplos deste artigo, crie uma nova pasta de trabalho do Excel com esses dados na coluna A da primeira planilha:

• Maçã

• Laranja

• Pera

• Morango

Coloque esses dados na coluna A da segunda folha:

• Pera

• Morango

• Maçã

• Abacaxi

Suas planilhas ficam assim:

Depois de configurar suas planilhas, agora podemos examinar as diferentes maneiras de encontrar duplicatas em duas planilhas, começando com as funções VLOOKUP, COUNTIF e EXACT.

Vamos!

1. Usando as funções VLOOKUP, COUNTIF ou EXACT para encontrar duplicatas

O Excel tem três funções internas que podem facilitar a localização de duplicatas: VLOOKUP , COUNTIF e EXACT .

Essas funções são projetadas para ajudá-lo a encontrar, contar e comparar dados em suas planilhas, tornando-as ferramentas ideais para encontrar entradas duplicadas.

Cada função atende a um propósito exclusivo e entender como usá-las pode simplificar significativamente seu processo de análise de dados.

Nas seções a seguir, exploraremos como usar as funções VLOOKUP, COUNTIF e EXACT para identificar efetivamente duplicatas em suas planilhas do Excel.

A. Como você usa a função VLOOKUP para localizar duplicatas em duas planilhas?

VLOOKUP significa pesquisa vertical. Você usa esta função para localizar valores duplicados entre duas colunas. Esta é a sintaxe:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : O valor que você deseja pesquisar na primeira coluna do table_array.

• table_array : O intervalo de células que contém os dados que você deseja pesquisar.

• col_index_num : O número da coluna no table_array do qual você deseja retornar o valor.

• range_lookup : Opcional. É VERDADEIRO (correspondência aproximada) ou FALSO (correspondência exata). O padrão é VERDADEIRO.

Para usar a função em duas planilhas em um arquivo do Excel, você precisa saber como fazer referência a uma planilha separada em uma fórmula. Para fazer isso, digite o nome da planilha seguido do ponto de exclamação (!) e sua célula ou intervalo de células.

Por exemplo, isso faz referência às células A2 a A5 na planilha 2 da mesma pasta de trabalho: Planilha 2!$A$2:$A$5.

Para usar a função VLOOKUP em sua planilha de amostra, siga estas etapas:

1. Selecione a célula B2 para exibir o primeiro resultado da comparação.

2. Digite esta fórmula: =PROCV(A2,Planilha2!$A$2:$A$5, 1, FALSO).

3. Pressione Enter para exibir o resultado da comparação.

4. Preencha a fórmula para comparar os valores do restante das linhas na primeira planilha.

Os resultados serão como a imagem abaixo:

Você pode organizar a exibição mostrando uma mensagem amigável em vez de um erro quando uma duplicata não é encontrada.

Por exemplo, esta fórmula exibirá “Sim” ou “Não” para valores encontrados e não encontrados, respectivamente:

=SE(PROCV(A2, Folha 2!$A$2:$A$5, 1, FALSO)), “Não”, “Sim”)

Há mais exemplos dessa função em nosso artigo sobre como usar VLOOKUP para comparar duas colunas .

E se você estiver lidando com pastas de trabalho diferentes?

Se suas planilhas estiverem em pastas de trabalho separadas, o uso da função é o mesmo com dois arquivos do Excel. No entanto, referenciar a segunda planilha é um pouco mais complexo.

Você precisa:

• Coloque o nome da pasta de trabalho do Excel entre colchetes

• Siga com o nome da planilha

• Coloque a pasta de trabalho e a planilha entre aspas.

Por exemplo, se as células estiverem em uma planilha chamada Sheet2 em uma pasta de trabalho chamada “WB 2.xlsx”, o formato ficará assim:

'[WB 2.xlsx]Planilha2'!$A$2:$A$5

Antes de inserir a fórmula, feche a segunda pasta de trabalho. Caso contrário, você receberá um erro.

B. Como você usa a função CONT.SE para encontrar duplicatas em planilhas?

A função CONT.SE no Excel é usada para contar o número de células dentro de um intervalo especificado que atendem a um determinado critério.

Para comparar várias planilhas, você pode contar o número de células na segunda planilha que corresponde a uma célula na primeira planilha.

Esta é a sintaxe da função:

=CONT.SE(intervalo, critérios)

• Intervalo : o intervalo de células que você deseja contar com base nos critérios especificados.

• Critérios : a condição que deve ser atendida para que uma célula seja contada.

Para usar a função com os dados de amostra, siga estas etapas:

1. Selecione a célula B2 para exibir o primeiro resultado da comparação.

2. Digite esta fórmula: =CONT.SE(Folha2!$A$2:$A$5, A2)

3. Pressione Enter para exibir o resultado da comparação.

4. Preencha a fórmula para comparar os valores do restante das linhas na primeira planilha.

A função encontrará uma correspondência para algumas das células e nenhuma para outras. A célula de comparação exibe a contagem. Aqui está o resultado dos dados de amostra:

A função CONT.SE pode ser usada para outras tarefas úteis, como contar células não em branco no Excel .

C. Como você usa a função EXACT para encontrar duplicatas em planilhas?

A função EXATO no Excel também pode ser usada para procurar duplicatas nas mesmas células em duas planilhas diferentes do Excel. A sintaxe é:

=EXATO(texto1, texto2)

• text1 é a primeira string de texto que você deseja comparar.

• text2 é a segunda string de texto que você deseja comparar.

Siga esses passos:

1. Selecione a célula B2.

2. Digite a fórmula =EXATO(A2, Folha2!A2)

3. Pressione Enter para exibir o resultado da comparação. A fórmula retornará TRUE se ambos os valores forem idênticos ou FALSE caso contrário.

4. Preencha a fórmula para comparar os valores do restante das linhas na primeira planilha.

Observe que esse método não procura duplicatas em um intervalo de células. Em vez disso, você está apenas procurando correspondências com base na mesma célula em uma planilha diferente.

Isso pode ser útil com dados ordenados em que você espera apenas algumas exceções.

As funções VLOOKUP, COUNTIF e EXACT são úteis para encontrar duplicatas, mas o Excel é um programa versátil e existem outras opções. Na próxima seção, veremos como você pode usar a formatação condicional para identificar duplicatas em duas planilhas.

2. Como usar formatação condicional para linhas duplicadas

Nesta seção, você aprenderá como usar a formatação condicional para localizar e realçar linhas duplicadas em duas planilhas do Excel.

Para criar uma regra de formatação condicional, siga estas etapas:

1. Selecione o intervalo de células que contém os dados (A2:A5 neste caso).

2. Clique na guia "Página inicial" na faixa de opções do Excel.

3. Clique em “Formatação condicional” no grupo “Estilos”.

4. Escolha "Nova regra" no menu suspenso.

A próxima tarefa é fornecer uma fórmula para sua regra usar. Siga esses passos:

1. Escolha “Usar uma fórmula para determinar quais células formatar” na caixa de diálogo

2. Insira a seguinte fórmula: =CONT.SE(Folha2!$A$2:$A$5, A2) > 0

Por fim, você aplica a formatação de sua preferência para células duplicadas.

1. Clique no botão Formatar para abrir a caixa de diálogo Formatar células.

2. Escolha um formato, por exemplo, preencha as duplicatas com uma cor de fundo amarela.

3. Clique OK.

Seus dados duplicados agora estão destacados em amarelo.

Como usar o gerenciador de regras de formatação condicional

Depois de criar a regra de formatação condicional, você pode gerenciá-la usando o Gerenciador de regras de formatação condicional.

Para acessar o gerenciador:

1. Vá para a guia Início.

2. Clique em Formatação Condicional.

3. Escolha “Gerenciar regras”.

Você verá uma lista de todas as regras de formatação condicional aplicadas à planilha selecionada. Você pode editar, excluir ou alterar a ordem das regras selecionando a regra e clicando nos botões apropriados.

Para aplicar a mesma regra à outra folha, siga estes passos:

1. Selecione o intervalo que deseja comparar na segunda planilha.

2. Vá para o Gerenciador de Regras de Formatação Condicional.

3. Selecione a regra, clique em Duplicar regra e, em seguida, clique em Editar regra.

4. Substitua “Sheet2” pelo nome da primeira planilha a ser comparada.

Agora que você aplicou a regra de formatação condicional a ambas as planilhas, as duplicatas serão realçadas de acordo com a formatação escolhida.

Certifique-se de ajustar o intervalo e as referências de célula nas fórmulas conforme necessário para cobrir todos os dados que deseja comparar.

A formatação condicional pode parecer um pouco primitiva. Se você deseja um controle mais preciso, o Power Query pode ser a resposta! Na próxima seção, abordamos como você pode usar o Power Query para localizar duplicatas.

3. Como usar o Power Query para localizar duplicatas em planilhas

Power Query é uma ferramenta de transformação e preparação de dados no Microsoft Excel. Identificar os mesmos valores é apenas uma das muitas tarefas de análise que você pode realizar com a ferramenta.

Para fazer isso, você deve primeiro importar os dados das duas planilhas em tabelas separadas. Siga estas etapas em cada folha:

1. Clique com o botão direito do mouse no intervalo de células.

2. Escolha “Obter dados da tabela/intervalo”.

3. Altere o nome da tabela para algo apropriado.

Depois de importar as duas planilhas, a primeira tarefa é mesclar os dados:

1. Vá para a guia Dados.

2. Clique em “Obter dados”.

3. Selecione “Combinar Consultas”.

4. Escolha “Mesclar” e selecione as duas tabelas.

5. Clique nas duas colunas principais.

6. Escolha “Inner” como o “Join Kind” e clique em OK.

O Editor do Power Query será aberto com os dados combinados de ambas as tabelas em sua planilha do Excel. Você verá duas colunas, uma de cada tabela. Como você está interessado apenas nos valores duplicados, pode remover a segunda coluna.

Você pode clicar em “Fechar e carregar” no Editor do Power Query para carregar as duplicatas em uma nova planilha.

Para explorar mais aspectos desse poderoso recurso, siga os exemplos em nosso artigo sobre como usar o Power Query no Excel .

O Excel também possui ferramentas e suplementos de terceiros que adicionam a capacidade de encontrar duplicatas perfeitamente, então vamos dar uma olhada em algumas dessas ferramentas na próxima seção.

4. Ferramentas e suplementos para identificar duplicatas em planilhas

Ferramentas externas e suplementos podem oferecer funcionalidades avançadas que podem não estar disponíveis em recursos nativos do Excel. Essas ferramentas podem agilizar ainda mais o processo de comparação de duplicatas em planilhas.

O Spreadsheet Compare é uma ferramenta da Microsoft que permite comparar duas pastas de trabalho lado a lado, destacando as diferenças e identificando facilmente as duplicatas. Você pode baixá- lo no site da Microsoft.

Existem vários suplementos que você pode instalar para automatizar o processo de localização de duplicatas. Um exemplo é “ Removedor de Duplicatas ”. Para instalar um suplemento:

1. Vá para a guia Inserir.

2. Clique em “Obter suplemento”.

3. Pesquise por "Duplicar".

4. Clique em “Adicionar” na ferramenta de sua escolha.

5. Como verificar visualmente se há duplicatas em duas folhas

Se tudo mais falhar, use seus olhos! A caixa de diálogo Organizar janelas no Excel permite visualizar várias planilhas ou pastas de trabalho lado a lado.

Embora não encontre duplicatas diretamente, pode ajudá-lo a comparar dados visualmente em planilhas ou pastas de trabalho para identificar duplicatas. Siga esses passos:

1. Clique na guia "Exibir" na faixa de opções do Excel.

2. Clique em “Organizar tudo” no grupo “Janela”.

3. Escolha uma opção de disposição, por exemplo, “Vertical” ou “Horizontal”.

Isso exibirá as duas folhas lado a lado ou uma acima da outra. Agora você pode comparar manualmente os dados em cada planilha para identificar duplicatas.

Você precisa percorrer os dados e inspecionar visualmente cada valor para encontrar correspondências.

Observe que esse método não é eficiente para grandes conjuntos de dados, pois requer comparação manual. O uso dos outros métodos deste artigo será mais eficaz para localizar duplicatas em conjuntos de dados maiores.

E esse é o último de nossos métodos comuns para encontrar valores duplicados em planilhas do Excel! Na próxima seção, daremos algumas dicas para preparar suas planilhas.

3 dicas para preparar suas planilhas do Excel

Antes de começar a comparar várias planilhas, verifique se as colunas e linhas de seus conjuntos de dados estão alinhadas corretamente.

Certifique-se de que ambas as planilhas do Excel tenham a mesma estrutura e os mesmos nomes de cabeçalho. Se necessário, você pode reorganizar as colunas em ambas as planilhas para que correspondam entre si.

Aqui estão três sugestões para garantir comparações precisas:

1. Organize seus dados na mesma ordem em ambas as planilhas. Isso facilita o funcionamento eficaz das funções do Excel.

2. Normalize seus dados usando formatação, letras maiúsculas e tipos de dados consistentes. Isso evitará entradas incompatíveis devido a pequenas diferenças.

3. Remova linhas ou colunas em branco desnecessárias, pois elas podem interferir no processo de comparação.

Você pode avaliar quanta duplicação você tem em um conjunto de dados contando os valores exclusivos. Este vídeo mostra vários métodos:

Como lidar com erros e inconsistências

Inconsistências em seus dados podem afetar o processo de comparação. Aqui estão quatro dicas para resolver inconsistências:

1. Verifique se há discrepâncias nos tipos de dados, como misturar texto e valores numéricos na mesma coluna.

2. Certifique-se de que a formatação consistente seja usada para datas, números e outros tipos de dados.

3. Examine seus dados em busca de entradas ausentes ou incorretas e atualize, se necessário.

4. Padronize abreviações ou convenções de nomenclatura inconsistentes em seus conjuntos de dados.

Pensamentos finais

Encontrar duplicatas em duas planilhas do Excel é uma tarefa essencial para gerenciamento e análise de dados, garantindo integridade e precisão dos dados. O Excel oferece várias técnicas para identificar duplicatas, cada uma com suas próprias vantagens e limitações.

A escolha do método depende das necessidades do usuário, do tamanho e complexidade do conjunto de dados e do resultado desejado. Para conjuntos de dados menores e comparações diretas, usar VLOOKUP , COUNTIF ou formatação condicional pode ser suficiente.

Para conjuntos de dados maiores ou transformações de dados mais complexas, o Power Query é uma ferramenta poderosa e flexível que pode lidar com uma ampla variedade de tarefas de preparação de dados, incluindo a localização de duplicatas.

Para finalizar, comparar planilhas do Excel em busca de duplicatas é uma habilidade super útil para se ter em sua caixa de ferramentas. Com os truques deste artigo, você pode identificar essas duplicatas incômodas e manter seus dados completamente limpos.

Confie em nós, à medida que você melhorar nisso, você passará rapidamente por suas tarefas de dados e impressionará todos ao seu redor!