Conteúdo
Este artigo levará cerca de 10 minutos para ler. Nos próximos 5 minutos, você pode comparar facilmente duas colunas no Excel e descobrir se há duplicatas nelas, excluí-las ou realçá-las em cores. Então, chegou a hora!
O Excel é um aplicativo muito poderoso e muito legal para criar e processar grandes quantidades de dados. Se você tiver várias pastas de trabalho com dados (ou apenas uma tabela enorme), provavelmente desejará comparar 2 colunas, encontrar valores duplicados e fazer algo com eles, por exemplo, excluir, destacar ou limpar o conteúdo . As colunas podem estar na mesma tabela, ser adjacentes ou não adjacentes, podem estar localizadas em 2 folhas diferentes ou mesmo em livros diferentes.
Imagine que temos 2 colunas com nomes de pessoas – 5 nomes por coluna A e 3 nomes em uma coluna B. Você precisa comparar os nomes nessas duas colunas e encontrar duplicatas. Como você entende, estes são dados fictícios, tomados apenas como exemplo. Em tabelas reais, estamos lidando com milhares ou mesmo dezenas de milhares de registros.
Opção A: ambas as colunas estão na mesma folha. Por exemplo, uma coluna A e coluna B.
Opção B: As colunas estão em folhas diferentes. Por exemplo, uma coluna A na folha Sheet2 e coluna A na folha Sheet3.
Excel 2013, 2010 e 2007 têm uma ferramenta interna Remover Duplicados (Remover duplicatas), mas é impotente nessa situação, pois não pode comparar dados em 2 colunas. Além disso, ele só pode remover duplicatas. Não há outras opções, como destacar ou alterar cores. E ponto!
A seguir, mostrarei as possíveis maneiras de comparar duas colunas no Excel, o que permitirá encontrar e remover registros duplicados.
Compare 2 colunas no Excel e encontre entradas duplicadas usando fórmulas
Opção A: ambas as colunas estão na mesma planilha
- Na primeira célula vazia (no nosso exemplo, esta é a célula C1), escrevemos a seguinte fórmula:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
Em nossa fórmula A1 esta é a primeira célula da primeira coluna que vamos comparar. $ B $ 1 и $ B $ 10000 esses são os endereços da primeira e da última células da segunda coluna, com as quais faremos a comparação. Observe as referências absolutas – as letras das colunas e os números das linhas são precedidos por um cifrão ($). Eu uso referências absolutas para que os endereços das células permaneçam os mesmos ao copiar fórmulas.
Se você quiser encontrar duplicatas em uma coluna B, altere as referências para que a fórmula fique assim:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Em vez de "Somente"E"Duplicar» Você pode escrever suas próprias etiquetas, por exemplo, «Não foi encontrado"E"encontrado“, ou deixe apenas “Duplicar' e insira um caractere de espaço em vez do segundo valor. Neste último caso, as células para as quais não foram encontradas duplicatas permanecerão vazias e, acredito, essa representação dos dados é mais conveniente para análises posteriores.
- Agora vamos copiar nossa fórmula para todas as células da coluna C, até a linha inferior, que contém os dados na coluna A. Para fazer isso, mova o ponteiro do mouse para o canto inferior direito da célula C1, o ponteiro terá a forma de uma cruz preta, como mostra a figura abaixo:Clique e mantenha pressionado o botão esquerdo do mouse e arraste a borda do quadro para baixo, destacando todas as células onde deseja inserir a fórmula. Quando todas as células necessárias estiverem selecionadas, solte o botão do mouse:
Clique e mantenha pressionado o botão esquerdo do mouse e arraste a borda do quadro para baixo, destacando todas as células onde deseja inserir a fórmula. Quando todas as células necessárias estiverem selecionadas, solte o botão do mouse:
Dica: Em tabelas grandes, copiar a fórmula será mais rápido se você usar atalhos de teclado. Destacar uma célula C1 e imprensa Ctrl + C (para copiar a fórmula para a área de transferência) e clique em Ctrl + Shift + Fim (para selecionar todas as células não em branco na coluna C) e, finalmente, pressione Ctrl + V (para inserir a fórmula em todas as células selecionadas).
- Ótimo, agora todos os valores duplicados estão marcados como “Duplicar":
Opção B: duas colunas estão em planilhas diferentes (em pastas de trabalho diferentes)
- Na primeira célula da primeira coluna vazia da planilha Sheet2 (no nosso caso é a coluna B) digite a seguinte fórmula:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Aqui Sheet3 é o nome da planilha na qual a 2ª coluna está localizada, e $ A $ 1: $ A $ 10000 são endereços de células do 1º ao último nesta 2ª coluna.
- Copiar a fórmula para todas as células em uma coluna B (igual à opção A).
- Obtemos este resultado:
Processamento de duplicatas encontradas
Ótimo, encontramos entradas na primeira coluna que também estão presentes na segunda coluna. Agora precisamos fazer algo com eles. Passar por todos os registros duplicados em uma tabela manualmente é bastante ineficiente e leva muito tempo. Existem maneiras melhores.
Mostrar apenas linhas duplicadas na coluna A
Se suas colunas não tiverem cabeçalhos, você precisará adicioná-los. Para isso, posicione o cursor sobre o número que representa a primeira linha, e ele se transformará em uma seta preta, conforme mostra a figura abaixo:
Clique com o botão direito do mouse e selecione no menu de contexto Inclusão (Inserir):
Dê nomes às colunas, por exemplo, “Nome"E"Duplicado?» Em seguida, abra a guia Data (Dados) e pressione filtros (Filtro):
Depois disso, clique na pequena seta cinza ao lado de “Duplicado?« para abrir o menu de filtros; desmarque todos os itens nesta lista, exceto Duplicare pressione OK.
Isso é tudo, agora você vê apenas os elementos da coluna А, que são duplicados na coluna В. Existem apenas duas dessas células em nossa tabela de treinamento, mas, como você entende, na prática haverá muito mais delas.
Para exibir todas as linhas de uma coluna novamente А, clique no símbolo de filtro na coluna В, que agora se parece com um funil com uma pequena seta, e selecione Selecionar todos (Selecionar tudo). Ou você pode fazer o mesmo através da Faixa de Opções clicando em Data (Dados) > Selecionar e filtrar (Classificar e filtrar) > Limpar (Limpar) conforme mostrado na captura de tela abaixo:
Altere a cor ou destaque as duplicatas encontradas
Se as notas “Duplicar” não é suficiente para seus propósitos e você deseja marcar células duplicadas com uma cor de fonte diferente, cor de preenchimento ou algum outro método…
Nesse caso, filtre as duplicatas conforme mostrado acima, selecione todas as células filtradas e clique em Ctrl + 1para abrir a caixa de diálogo formatar células (formato de célula). Como exemplo, vamos alterar a cor de preenchimento das células em linhas com duplicatas para amarelo brilhante. Claro, você pode alterar a cor de preenchimento com a ferramenta Preencher guia (Cor de preenchimento) Início (Início), mas a vantagem da caixa de diálogo formatar células (Cell Format) em que você pode configurar todas as opções de formatação ao mesmo tempo.
Agora você definitivamente não perderá nenhuma célula com duplicatas:
Removendo valores duplicados da primeira coluna
Filtre a tabela para que apenas as células com valores duplicados sejam mostradas e selecione essas células.
Se as 2 colunas que você está comparando estiverem em planilhas diferentes, ou seja, em tabelas diferentes, clique com o botão direito do mouse no intervalo selecionado e selecione Excluir linha (Remover linha):
Press OKquando o Excel solicita que você confirme que você realmente deseja excluir toda a linha da planilha e, em seguida, limpe o filtro. Como você pode ver, apenas linhas com valores únicos permanecem:
Se 2 colunas estiverem na mesma planilha, próximos uns dos outros (adjacentes) ou não próximos uns dos outros (não adjacentes), então o processo de remoção de duplicatas será um pouco mais complicado. Não podemos remover a linha inteira com valores duplicados, pois isso também removerá as células da segunda coluna. Então, para deixar apenas entradas únicas em uma coluna А, fazem isto:
- Filtre a tabela para mostrar apenas valores duplicados e selecione essas células. Clique com o botão direito sobre eles e selecione no menu de contexto Conteúdo limpo (conteúdo limpo).
- Limpe o filtro.
- Selecionar todas as células em uma coluna А, a partir da célula A1 todo o caminho até o fundo contendo os dados.
- Clique na Data (Dados) e pressione Classificar de A a Z (Ordenar de A a Z). Na caixa de diálogo que se abre, selecione Continue com a seleção atual (Ordenar dentro da seleção especificada) e clique no botão Preto (Ordenação):
- Exclua a coluna com a fórmula, você não precisará mais dela, a partir de agora você terá apenas valores únicos.
- É isso, agora a coluna А contém apenas dados exclusivos que não estão na coluna В:
Como você pode ver, remover duplicatas de duas colunas no Excel usando fórmulas não é tão difícil.