Como Encontrar e Remover Duplicatas no Excel
Valores duplicados no Excel podem ser irritantes, mas, felizmente, existem vários métodos para encontrá-los e removê-los. Recomendamos fazer uma cópia de backup da sua planilha do Excel antes de remover as duplicatas. Vamos ver como contar, encontrar e remover valores duplicados no Excel.
Índice
- Use o Botão Remover Duplicatas
- Encontrar Dados Duplicados Usando Formatação Condicional
- Remover Duplicatas Usando Filtro Condicional
- Encontrar Duplicatas no Excel Com uma Fórmula
- Contar o Número de Duplicatas Usando uma Fórmula
- Remover Valores Duplicados Usando uma Fórmula
- Remover Dados Duplicados Usando Filtros Avançados
- Remover Duplicatas do Excel Usando Power Query
- Usando uma Tabela Dinâmica
1. Use o Botão Remover Duplicatas
O método mais rápido para encontrar e remover duplicatas no Excel é usar o botão “Remover Duplicatas” do Excel. Este método permite que você procure duplicatas com base nos dados em uma ou mais colunas. Ele remove linhas inteiras quando duplicatas são encontradas.
Clique em qualquer célula com dados na sua planilha. Isso não precisa ser a célula, coluna ou linha na qual você gostaria de remover duplicatas.
Selecione a guia “Dados” e clique no botão “Remover Duplicatas” na barra de ferramentas.
Marque a(s) coluna(s) nas quais você deseja remover duplicatas. Todas as colunas são marcadas por padrão. Além disso, se suas colunas não tiverem cabeçalhos, desmarque a caixa “Meus dados têm cabeçalhos” para que sua primeira linha seja incluída. Clique em “OK” após selecionar as colunas desejadas.
O Excel lhe dá uma mensagem informando quantas linhas foram excluídas e quantas linhas permanecem. Este método remove a linha inteira, não apenas o valor. Pressione “OK” para aceitar os resultados e retornar à sua planilha.
Se você não gostar dos resultados, pressione Ctrl + Z para retornar os valores excluídos para sua planilha.
2. Encontrar Dados Duplicados Usando Formatação Condicional
Se você prefere revisar duplicatas no Excel antes de excluir qualquer coisa, tente usar a formatação condicional. Isso encontra duplicatas no Excel, mas não as exclui.
Selecione a(s) coluna(s) ou a planilha inteira onde você deseja procurar valores duplicados.
Abra a guia “Página Inicial” e clique no botão “Formatação Condicional”.
Selecione “Regras de Realce de Célula” e escolha “Valores Duplicados”.
Escolha a cor na qual você deseja que os valores duplicados apareçam. Você também pode mudar o “Tipo de Regra” de “Valores Duplicados” para “Valores Únicos” se preferir ver os valores únicos destacados. Clique em “Concluído” quando terminar.
Revise as células destacadas e exclua as duplicatas que você não deseja. No meu exemplo, eu só quero excluir linhas onde cada valor é duplicado.
3. Remover Duplicatas Usando Filtro Condicional
Se você quiser facilitar a remoção de duplicatas no Excel após usar a formatação condicional, use um filtro do Excel baseado na cor da célula para exibir apenas duplicatas ou valores únicos. Com esta opção, você removerá a(s) linha(s) inteira(s) manualmente ou apenas as células duplicadas.
Configure a formatação condicional com base na seção anterior.
Abra a guia “Página Inicial”, selecione o botão “Classificar e Filtrar” e escolha “Filtro”.
Setas suspensas aparecerão no cabeçalho de cada coluna. Clique na seta e selecione “Filtrar por Cor”. Selecione a cor das células duplicadas para visualizar apenas aquelas. Ou, selecione “Sem preenchimento” para selecionar apenas os valores únicos.
Observe que nenhuma duplicata (incluindo o valor original) é exibida quando você escolhe “Sem preenchimento”. Se você ainda quiser ver todos os valores, selecione “Filtrar por Cor” em vez disso. Isso coloca as células coloridas ou as células sem preenchimento no topo da lista.
Para o meu exemplo, estou classificando pela cor das células duplicadas. Isso permite que você revise as duplicatas e exclua quaisquer linhas ou valores que você não deseja manter. Se você optar por mostrar apenas os valores únicos, pode copiar ou mover todos os dados visíveis para uma nova planilha. Isso deixará apenas as duplicatas para trás.
Quando você terminar a edição dos dados, clique novamente na seta suspensa e escolha “Limpar Filtro”.
Se você quiser remover as setas suspensas, selecione “Classificar e filtrar” na barra de ferramentas e escolha “Filtro”. Isso limpa todos os filtros.
Dica: você pode fazer muito mais com filtros do Excel. Por exemplo, você pode eliminar células em branco ou remover espaços extras de uma célula.
4. Encontrar Duplicatas no Excel Com uma Fórmula
As fórmulas do Excel podem realizar quase qualquer tarefa em uma planilha, incluindo encontrar duplicatas. Existem várias fórmulas diferentes que você pode usar com base no que deseja ver. Todas elas são baseadas em funções COUNT. Essas fórmulas não removem duplicatas. Você deve fazer isso manualmente uma vez que elas sejam encontradas. Não importa qual fórmula você use, o processo é o mesmo:
Crie uma nova coluna na mesma planilha. Eu rotulei a minha como “Duplicatas”.
Selecione a primeira célula vazia na sua nova coluna. Insira sua fórmula desejada (veja as várias fórmulas abaixo dos passos) na barra de função/fórmula ou na própria célula.
Se você quiser estender a fórmula para outras células na coluna, clique e segure o pequeno quadrado no canto inferior direito da célula que contém a fórmula. Puxe para baixo para preencher quantas células você quiser.
Agora que você sabe como inserir a fórmula, estas são as melhores opções para usar:
=COUNTIF(Range, Criteria) >1– O intervalo é sua coluna, e o critério é a célula mais alta. Isso funciona bem para encontrar duplicatas em uma única coluna. Por exemplo, para ver duplicatas com base apenas na coluna D, você usaria=COUNTIF(D:D, D2) >1. Um resultado de “Verdadeiro” significa duplicado, enquanto “Falso” significa único.
- Se você não quiser que a referência da célula mude enquanto você a arrasta, use referências de célula absolutas em vez disso. Por exemplo, eu usaria
=COUNTIF($D$2:$D$105, $D2) >1para verificar toda a coluna D. - Se você quiser mostrar algo diferente de Verdadeiro ou Falso, coloque o COUNTIF dentro de uma função IF:
=IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicado", " "). Isso mostrará “Duplicado” para duplicatas e uma célula em branco para valores únicos. Você pode facilmente usar quaisquer palavras que desejar, como “Duplicado” e “Único”. - Se você quiser comparar várias colunas ao mesmo tempo, precisará usar referências de célula absolutas e uma declaração IF, como:
=IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicado", "Único")Esta fórmula mostrará “Duplicado” apenas se os valores em D, E e F forem os mesmos. Se qualquer um dos três for único em uma linha, “Único” será exibido em vez disso. - Se você quiser mostrar apenas os valores duplicados e não a primeira ocorrência do valor, tente isso em vez disso:
=IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicado", "Único")Isso mostrará “Duplicado” apenas para a segunda ou mais ocorrências.
5. Contar o Número de Duplicatas Usando uma Fórmula
Você pode usar a fórmula acima sem o texto > 1 para contar o número de duplicatas em uma coluna. As duas fórmulas se tornarão =COUNTIF($D$2:$D$105, $D2) e =COUNTIF(D:D, D2). Insira a nova coluna para que ela exiba quantas vezes cada item aparece nos dados.
Um valor de “1” significa que é um valor único. Qualquer outra coisa equivale a duplicatas.
6. Remover Valores Duplicados Usando uma Fórmula
Uma vez que você tenha encontrado os valores duplicados (Seção 4) ou a contagem de duplicatas (Seção 5), use o método de filtro para remover duplicatas e reter valores únicos.
Clique em qualquer lugar na coluna com seus valores ou contagem duplicados. Vá para “Guia Inicial -> Classificar & Filtrar -> Filtro” para habilitar a caixa de filtro suspensa no cabeçalho da coluna.
Clique na seta suspensa na sua coluna de duplicatas.
Marque o(s) valor(es) que você deseja manter, desmarque o que você não deseja e clique em “Aplicar”. Oculte todas as duplicatas selecionando apenas “Único”, “Falso” ou qualquer que seja seu rótulo para valores únicos para o método de valores duplicados, e “1” para Contagem de duplicatas. Visualize apenas as duplicatas desmarcando os identificadores únicos.
Selecione as linhas visíveis pressionando Ctrl + C. Se, por qualquer motivo, isso selecionar todas as linhas, incluindo linhas ocultas, use o atalho Alt + ; em vez disso.
Copie os valores únicos ou duplicados (o que você escolher mostrar) para outra planilha. Exclua esses valores da planilha original.
Vá para “Classificar & Filtrar -> Filtro.” Clicar em “Filtro” removerá o filtro e mostrará todas as duplicatas ou valores únicos restantes.
A partir daqui, passe pelas duplicatas e remova as células ou linhas que você não deseja mais. Lembre-se, a ocorrência original é listada com os valores duplicados.
7. Remover Dados Duplicados Usando Filtros Avançados
Até agora, usamos apenas filtros básicos, mas você também pode encontrar e remover duplicatas no Excel usando filtros avançados.
Selecione a coluna que você deseja filtrar. Abra a guia “Dados” e clique em “Avançado.”
Na caixa “Filtro Avançado”, escolha “Filtrar a lista, no local.” Isso oculta as duplicatas no mesmo conjunto de dados. Mais tarde, você pode copiar e colar manualmente os valores únicos para um lugar diferente na mesma planilha ou para uma planilha separada.
Se você ainda não selecionou sua(s) coluna(s), selecione-as. Elas aparecerão automaticamente no campo “Intervalo da Lista”. Deixe o “Intervalo de Critérios” em branco.
Marque a caixa ao lado de “Registros únicos apenas” e clique em “OK.”
Isso mostrará valores únicos em seus dados. Use o atalho Alt + ; para selecionar linhas visíveis, apenas se você quiser realizar alguma ação sobre elas, como copiar para outro local.
Por outro lado, selecione “Copiar para outro local” na caixa de Filtro Avançado se você quiser que o Excel copie automaticamente os valores únicos para um lugar diferente na mesma planilha.
Selecione primeiro o “Intervalo da Lista”. Você pode manter o “Intervalo de Critérios” em branco.
Clique uma vez no campo “Copiar para” e selecione as linhas na sua planilha onde você deseja copiar os dados únicos.
Certifique-se de que a caixa ao lado de “Registros únicos apenas” esteja marcada.
Este método oculta a linha duplicada inteira, não apenas os valores. Ele também oculta a ocorrência original do valor duplicado, não apenas as versões duplicadas. Você vai querer visualizar as duplicatas para extrair os valores originais.
8. Remover Duplicatas do Excel Usando Power Query
O Power Query pode remover valores duplicados no Excel, como mostrado abaixo. Este método remove a linha duplicada inteira.
Abra a guia “Dados” e selecione “Da Tabela/Intervalo.”
Todos os seus dados devem ser selecionados automaticamente. Se não, insira o intervalo de toda a planilha na caixa “Criar Tabela”. Clique em “OK” quando terminar.
O editor “Power Query” será aberto. Selecione as colunas e clique com o botão direito no cabeçalho da coluna selecionada. Escolha “Remover duplicatas” no menu. Isso remove todas as duplicatas no editor Power Query. Neste ferramenta, a ocorrência original permanece na lista.
Se você quiser remover duplicatas de toda a tabela, clique no botão “Tabela” no canto superior esquerdo e escolha “Manter duplicatas” para mostrar apenas as entradas duplicadas e remover o resto.
Isso mostra todas as duplicatas, incluindo as entradas originais. Para remover as extras e deixar apenas as entradas originais, clique novamente em “Tabela” e selecione “Remover duplicatas.”
Clique em “Fechar e Carregar” na parte superior para abrir a tabela em uma nova guia da mesma pasta de trabalho. Apenas os resultados da sua consulta serão carregados. Isso não remove nada da planilha original.
9. Usando uma Tabela Dinâmica
Use Tabelas Dinâmicas para exibir apenas os valores únicos em seus dados, removendo assim as entradas duplicadas. Isso não remove realmente nenhuma linha ou valor dos seus dados originais; apenas mostra os valores únicos.
Abra a guia “Inserir” e selecione “Tabela Dinâmica.” Selecione “Da Tabela/Intervalo.”
Selecione a tabela ou intervalo onde você deseja ocultar valores duplicados. Todo o seu conjunto de valores deve ser selecionado por padrão. Se não, pressione Ctrl + A para selecionar tudo. Ou, use o mouse para selecionar um intervalo personalizado.
Selecione se a Tabela Dinâmica deve ser colocada na mesma planilha ou em uma nova planilha. Se for uma planilha existente, insira ou selecione a célula que você deseja usar na caixa “Localização”. Clique em “OK.”
Na barra lateral da Tabela Dinâmica, marque ou arraste as colunas das quais você deseja extrair valores únicos para a seção “Linhas”.
Você precisará formatar a Tabela Dinâmica para mostrá-la em uma forma tabular. Para isso, vá para a guia “Design” e execute os seguintes passos:
Selecione “Layout do Relatório -> Mostrar em Forma Tabular.”
Selecione “Subtotais -> Não mostrar subtotais.”
Selecione “Layout do Relatório -> Repetir Todos os Rótulos dos Itens.”
Selecione “Totais Gerais -> Desligar para Linhas e Colunas.”
Você obterá uma Tabela Dinâmica com valores únicos em forma tabular.
Com tantas maneiras de encontrar e remover duplicatas no Excel, não há razão para fazê-lo manualmente novamente. Escolha seu método favorito e siga em frente. Enquanto você economiza tempo com problemas de duplicatas, experimente essas dicas e truques do Microsoft Excel para economizar ainda mais tempo. Além disso, aprenda como limpar ainda mais seus dados no Excel.
Crédito da imagem: Pixabay. Todas as capturas de tela por Crystal Crowder.
