Postado em 12/09/2017

Se você trabalha com tabelas grandes no Excel e adiciona regularmente dados a elas, por exemplo, sobre alunos de escolas ou funcionários de empresas, então valores duplicados podem aparecer nessas tabelas, ou seja, duplicatas.

Neste artigo veremos como encontrar, selecionar, excluir e contar o número de valores duplicados no Excel.

Encontre e destaque duplicatas em uma tabela usando formatação condicional no Excel. Selecione todo o intervalo de dados na tabela desejada. Na aba “Home”, clique no botão “Formatação Condicional”, selecione “Regras para realçar células” – “Repetição de valores” no menu.

Na próxima janela, selecione na lista suspensa “repetição”, a cor da célula e o texto no qual deseja pintar sobre as duplicatas encontradas. Em seguida, clique em “OK” e o programa procurará duplicatas.

O Excel destacou valores duplicados em uma tabela. Como você pode ver, não são as linhas da tabela que são comparadas, mas as células nas colunas. Portanto, a célula “Sasha V.” está destacada. Pode haver vários desses alunos, mas com sobrenomes diferentes. Agora você pode classificar no Excel por célula e cor do texto e remover valores duplicados encontrados.

Para remover duplicatas no Excel você pode usar os seguintes métodos. Selecione a tabela preenchida, vá até a aba “Dados” e clique no botão “Remover duplicatas”.

Na próxima janela, marque a caixa “Meus dados contêm cabeçalhos” se você selecionou a tabela junto com os cabeçalhos. A seguir, verifique as colunas da tabela nas quais deseja encontrar valores duplicados e clique em OK.

Uma caixa de diálogo aparecerá informando quantos valores duplicados foram encontrados e removidos.

A segunda maneira de remover duplicatas é usar um filtro. Selecione as colunas da tabela necessárias junto com o cabeçalho. Vá até a aba “Dados” e no grupo “Classificação e Filtro” clique no botão “Avançado”.

Na próxima janela, o campo “Intervalo Original” já contém células. Marcamos com um marcador o item “copiar o resultado para outro local” e no campo “Colocar resultado no intervalo” indicamos o endereço de uma célula, que ficará no canto superior esquerdo da nova tabela. Marque a caixa “Somente registros exclusivos” e clique em “OK”.

Uma nova tabela será criada sem linhas com valores duplicados. Se você tiver uma tabela de origem grande, poderá criar uma tabela com registros exclusivos baseados nela em outra planilha do Excel. Para saber mais sobre isso, leia o artigo: filtro no Excel.

Se você precisar encontre e conte o número de valores duplicados no Excel, vamos criar uma tabela dinâmica do Excel para isso. Adicionamos a coluna “Código” à tabela original e preenchemos com “1”: coloque 1, 1 nas duas primeiras células, selecione-as e arraste-as para baixo. Quando forem encontradas duplicatas para linhas, o valor na coluna Código será incrementado em um a cada vez.

Selecione a tabela inteira junto com os cabeçalhos, vá até a aba “Inserir” e clique no botão “Tabela Dinâmica”.

Para saber mais sobre como trabalhar com tabelas dinâmicas no Excel, leia o artigo seguindo o link.

Na próxima janela as células do intervalo já estão indicadas; use um marcador para marcar “Para uma nova planilha” e clique em “OK”.

No lado direito, arraste os três primeiros títulos da tabela para a área “Títulos de linha” e arraste o campo “Código” para a área “Valores”.

Como resultado, obteremos uma tabela dinâmica sem duplicatas, e no campo “Código” haverá números correspondentes aos valores repetidos na tabela de origem - quantas vezes esta linha foi repetida nela.

Por conveniência, vamos selecionar todos os valores na coluna “Valor por código do campo” e classificá-los em ordem decrescente.

Acho que agora você pode encontrar, selecionar, excluir e até contar o número de duplicatas no Excel para todas as linhas da tabela ou apenas para colunas selecionadas.

Encontrar duplicatas no Excel é uma das tarefas mais comuns para qualquer funcionário de escritório. Existem várias maneiras diferentes de resolver isso. Mas com que rapidez você pode encontrar duplicatas no Excel e destacá-las com cores? Para responder a esta pergunta frequente, vejamos um exemplo específico.

Como encontrar valores duplicados no Excel?

Digamos que estamos registrando pedidos recebidos pela empresa via fax e e-mail. Pode surgir uma situação em que o mesmo pedido tenha sido recebido através de dois canais de entrada de informações. Se você registrar o mesmo pedido duas vezes, alguns problemas poderão surgir para a empresa. A seguir consideraremos uma solução usando formatação condicional.

Para evitar pedidos duplicados, você pode usar a formatação condicional para encontrar rapidamente valores duplicados em uma coluna do Excel.

Exemplo de registro diário de pedidos de mercadorias:

Para verificar se o registro de pedidos contém possíveis duplicatas, analisaremos por nomes de clientes – coluna B:

Baixe um exemplo de localização de valores idênticos em uma coluna.

Como você pode ver na imagem com formatação condicional, conseguimos implementar de forma fácil e rápida a pesquisa duplicada no Excel e detectar dados duplicados de células para a tabela de histórico de pedidos.

Exemplo da função CONT.SE e destaque de valores duplicados

O princípio da fórmula para localizar duplicatas usando formatação condicional é simples. A fórmula contém a função =CONT.SE(). Esta função também pode ser usada ao procurar valores idênticos em um intervalo de células. O primeiro argumento da função é o intervalo de dados a ser visualizado. No segundo argumento indicamos o que procuramos. Nosso primeiro argumento tem referências absolutas, pois deve ser imutável. E o segundo argumento, ao contrário, deve mudar para o endereço de cada célula do intervalo visualizado, pois possui um link relativo.

As maneiras mais rápidas e fáceis: encontrar duplicatas nas células.

Após a função existe um operador que compara a quantidade de valores encontrados em um intervalo com o número 1. Ou seja, se houver mais de um valor, então a fórmula retorna o valor TRUE e a formatação condicional é aplicada à célula atual .

Como encontrar e colorir valores repetidos no Excel

Uma lista com grupos de dados destacados em cores certamente parece muito mais legível do que células brancas em colunas com valores de fonte preta. Mesmo um simples destaque colorido de cada segunda linha facilita muito a análise visual dos dados da tabela. Para implementar esta tarefa, o Excel usa uma ferramenta universal - a formatação condicional.

Como destacar células duplicadas no Excel

Às vezes você pode se deparar com uma situação em que precisa destacar grupos de dados, mas devido à estrutura complexa, é impossível definir e indicar claramente ao Excel quais células destacar.

Valores duplicados no Excel

Um exemplo de tal tabela é mostrado na figura abaixo:

Esta tabela está ordenada por cidade (os valores da terceira coluna estão em ordem alfabética). É necessário destacar as linhas de cada segundo grupo de dados de cada cidade. Um grupo de linhas permanece inalterado, o próximo é colorido e assim sucessivamente nesta sequência até o final da tabela. Por esta:

Como resultado, selecionamos linhas inteiras usando formatação condicional e obtemos o efeito mostrado na figura:

Agora trabalhar com uma tabela tão legível é muito mais conveniente. Você pode realizar confortavelmente uma análise visual de todos os indicadores.

Ao trabalhar com o Microsoft Excel, muitas vezes surge uma situação em que é necessário remover linhas duplicadas. Este processo pode tornar-se uma tarefa inútil, monótona e demorada, no entanto, existem várias maneiras de simplificar a tarefa. Hoje veremos vários métodos convenientes para localizar e remover linhas duplicadas no Excel. Tomemos a seguinte tabela de dados como exemplo:

Opção 1: Comando Remover Duplicados no Excel

O Microsoft Excel possui uma ferramenta integrada que permite localizar e remover linhas duplicadas. Vamos começar encontrando strings duplicadas. Para fazer isso, selecione qualquer célula da tabela e, a seguir, selecione a tabela inteira clicando em Ctrl+A.

Vá para a guia Data(Dados) e pressione comando Remover duplicatas(Remover duplicatas) conforme mostrado abaixo.

Uma pequena caixa de diálogo aparecerá Remover duplicatas(Remover duplicatas). Você notará que a primeira linha é automaticamente desmarcada. A razão para isso é a caixa de seleção marcada no item Meus dados têm cabeçalhos(Meus dados contêm cabeçalhos).

No nosso exemplo não há cabeçalhos, pois a tabela começa na 1ª linha. Então, vamos desmarcar a caixa. Feito isso, você notará que toda a tabela está selecionada novamente, e a seção Colunas(Colunas) mudará de duplicar sobre Coluna A, B E COM.

Agora que a tabela inteira está selecionada, clique em OK para remover duplicatas. No nosso caso, todas as linhas com dados duplicados serão excluídas, exceto uma. Todas as informações sobre a exclusão serão exibidas em uma caixa de diálogo pop-up.

Opção 2: filtro avançado

A segunda ferramenta do Excel que você pode usar para localizar e remover duplicatas é Filtro avançado. Este método também se aplica ao Excel 2003. Para aplicar o filtro, você deve selecionar a tabela inteira como antes usando o atalho de teclado Ctrl+A.

Então vá para a aba Dados(Dados), no grupo de comando Classificar e filtrar(Classificar e Filtrar) clique no comando Avançado(Opcional) conforme abaixo. Se você estiver usando o Excel 2003, chame o menu suspenso Dados(Dados), selecione Filtros(Filtros) e depois Filtros Avançados(Filtros avançados).

Agora você precisa marcar a caixa Somente registros exclusivos(Somente entradas exclusivas).

Depois de clicar OK Todas as duplicatas no documento serão excluídas, com exceção de uma entrada. Em nosso exemplo, restam dois registros porque a primeira duplicata foi encontrada na linha 1. Este método determina automaticamente os cabeçalhos da tabela. Se quiser excluir a primeira linha, você terá que excluí-la manualmente. Quando houver cabeçalhos na 1ª linha e nenhuma duplicata, restará apenas uma cópia das duplicatas existentes.

Opção 3: Substituição

Este método é útil quando você precisa encontrar linhas duplicadas em tabelas pequenas. Usaremos a ferramenta Localizar e substituir(Pesquisar e Substituir), integrado a todos os produtos Microsoft Office. Primeiro, você precisa abrir a planilha Excel com a qual planeja trabalhar.

Com a planilha aberta, selecione a célula cujo conteúdo você deseja localizar e substituir e copie-a. Para fazer isso, selecione a célula desejada e pressione o atalho do teclado Ctrl+C.

Depois de copiar a palavra que você precisa encontrar, use a combinação Ctrl+H para abrir a caixa de diálogo Localizar e substituir(Pesquisar e substituir). Cole a palavra copiada do campo Encontrar pressionando Ctrl+V.

Clique no botão Opções(Opções) para abrir uma lista adicional de opções. Marque a caixa ao lado Combine todo o conteúdo da célula(Célula inteira). Isso é necessário porque em algumas células as palavras que você procura são encontradas junto com outras palavras. Se você não selecionar esta opção, poderá excluir inadvertidamente as células que deseja manter. Certifique-se de que todas as outras configurações estejam conforme mostrado na figura abaixo.

Agora você precisa inserir um valor no campo Substituir com(Substituído por). Neste exemplo usaremos o número 1 . Após inserir o valor desejado, pressione Substitua tudo(Substitua tudo).

Você pode notar que todos os valores duplicado nas células da tabela serão substituídas por 1 . Usamos o valor 1 , por ser pequeno e se destacar no texto. Agora você pode identificar visualmente as linhas que possuem valores duplicados.

Para manter uma das duplicatas, basta colar o texto original de volta na linha que foi substituída. No nosso caso, iremos restaurar os valores da 1ª linha da tabela.

Depois de identificar as linhas com conteúdo repetido, selecione-as uma por uma enquanto mantém pressionado o botão Ctrl.

Após selecionar todas as linhas que precisam ser excluídas, clique com o botão direito no cabeçalho de qualquer uma das linhas selecionadas e no menu de contexto clique Excluir(Excluir). Não pressione a tecla Excluir no teclado, pois neste caso será apagado apenas o conteúdo das células, e não a linha inteira.

Feito isso, você poderá notar que todas as linhas restantes possuem valores únicos.

Uma dúvida comum é como localizar e remover duplicatas no Excel. Suponha que você baixou um relatório mensal do seu sistema contábil, mas no final precisa entender quais contrapartes geralmente interagiram com a empresa nesse período - faça uma lista de contrapartes sem repetição. Como selecionar valores únicos?

É possível remover valores duplicados, triplicados, etc. no Excel em várias colunas?

É possível e muito simples. Existe uma função especial para isso. Pré-selecione o intervalo onde deseja remover duplicatas. Na faixa de opções, vá para Dados - Remover duplicatas (veja a imagem no início do artigo).

Selecionando a primeira coluna

É importante entender que se você selecionar apenas a primeira coluna, todos os dados nas colunas não selecionadas serão excluídos se não forem exclusivos.

Muito confortavelmente!

2. Como selecionar todas as duplicatas no Excel?

Você já ouviu falar? Isso vai ajudar aqui também! Selecione a coluna na qual deseja marcar duplicatas, selecione no menu Principal - Formatação Condicional - Regras para destacar células - Valores duplicados...

Na janela Valores repetidos que se abre, selecione quais células selecionar (únicas ou repetitivas), bem como o formato de seleção, entre os sugeridos, ou crie um formato personalizado. O formato padrão será preenchimento vermelho e texto vermelho.

Clique em OK se não quiser alterar a formatação. Agora todos os dados das condições selecionadas serão coloridos.

Observo que a ferramenta é aplicada apenas à coluna selecionada (!).

A propósito, se você precisa ver os únicos, na janela à esquerda selecione - único.

3. Valores únicos usando tabelas dinâmicas

Para ser sincero, uma vez não suspeitei da existência da capacidade de “remover duplicatas” e usei tabelas dinâmicas. Como eu fiz isso? Selecione a tabela na qual você precisa encontrar valores únicos - Inserir -

As funções de tabela automática permitem comparar valores e eliminar duplicatas. É importante notar imediatamente que valores numéricos idênticos com formatos de células diferentes são percebidos como diferentes no Excel. Vejamos esta regra usando um exemplo específico ao remover duplicatas.

Como remover duplicatas no Excel

A figura abaixo mostra uma tabela com valores duplicados. A linha 3 contém o mesmo valor da linha 6. E o valor da linha 4 = linha 7. As células com números em linhas duplicadas têm os mesmos valores e formatos diferentes. Eles têm um número diferente de casas decimais. Vamos realizar 2 operações diferentes para remover duplicatas.

Elimine duplicatas com base nos valores da coluna de texto:

As linhas 6 e 7 são reconhecidas como duplicadas e removidas da tabela. Se você não desabilitar a verificação de colunas na etapa 2, nenhuma linha será excluída, pois para o Excel todos os números da coluna “Preço” são considerados diferentes.



Maneiras alternativas de remover duplicatas

O Excel possui outras ferramentas para trabalhar com valores duplicados. Por exemplo:

Cada ferramenta tem suas próprias vantagens e desvantagens. Mas a maneira mais eficaz de remover duplicatas é uma tabela (conforme descrito acima). Lá, todo o processo ocorre em etapas com máximo controle de dados. Isso permite obter um resultado de alta qualidade.

Nos arquivos Excel atuais, as duplicatas são onipresentes. Por exemplo, ao criar uma tabela composta a partir de outras tabelas, você pode encontrar valores duplicados nela ou dois usuários diferentes inseriram os mesmos dados em um arquivo compartilhado, o que levou a duplicações, etc. As duplicatas podem ocorrer em uma coluna, em várias colunas ou até mesmo na planilha inteira. O Microsoft Excel fornece diversas ferramentas para localizar, destacar e, opcionalmente, remover valores duplicados. Abaixo estão as técnicas básicas para identificar duplicatas no Excel.

1. Removendo valores duplicados no Excel (2007+)

Digamos que você tenha uma tabela de três colunas que contém registros idênticos e precise se livrar deles. Selecione a área da tabela da qual deseja remover valores duplicados. Você pode selecionar uma ou mais colunas ou a tabela inteira. Vá para a guia Dados, vá para o grupo Ferramentas de Dados e clique no botão Remover Duplicados.

Se cada coluna da tabela tiver um cabeçalho, marque a caixa de seleção Meus dados contêm cabeçalhos. Também colocamos marcadores opostos às colunas nas quais precisamos procurar duplicatas.

Clique em OK, a caixa de diálogo será fechada e as linhas contendo duplicatas serão excluídas.

Esta função foi projetada para excluir registros que duplicam completamente as linhas da tabela. Caso você não tenha selecionado todas as colunas para identificar duplicatas, as linhas com valores duplicados também serão removidas.

2. Usando um filtro avançado para remover duplicatas

Selecione qualquer célula da tabela, vá para a guia Dados, para o grupo Classificação e Filtro e clique no botão Avançado.

Na caixa de diálogo Filtro avançado que aparece, você precisa definir a opção para copiar o resultado para outro local, no campo Intervalo de origem, especifique o intervalo no qual a tabela está localizada, na caixa Colocar resultado no intervalo, especifique o topo célula esquerda da futura tabela filtrada e marque a caixa de seleção Somente valores exclusivos. Clique OK.

No local indicado para colocação dos resultados do filtro avançado será criada outra tabela, mas com dados filtrados por valores únicos.

3. Destaque valores duplicados usando formatação condicional no Excel (2007+)

Selecione a tabela na qual deseja detectar valores duplicados. Vá para a guia Início para o grupo Estilos, selecione Formatação Condicional -> Regras para destacar células -> Repetir valores.

Na caixa de diálogo Valores Duplicados que aparece, você deve selecionar um formato para destacar duplicatas. Eu tenho a cor padrão definida para preenchimento vermelho claro e cor de texto vermelho escuro. Observe que, neste caso, o Excel não comparará toda a linha da tabela quanto à exclusividade, mas apenas a célula da coluna; portanto, se você tiver valores duplicados em apenas uma coluna, o Excel também os formatará. No exemplo, você pode ver como o Excel preencheu algumas células da terceira coluna com nomes, embora toda a linha desta célula da tabela seja única.

4. Usando tabelas dinâmicas para determinar valores repetidos

Vamos utilizar a tabela com três colunas que já conhecemos e adicionar uma quarta, chamada Contador, e preenchê-la com unidades (1). Selecione a tabela inteira e vá até a aba Inserir no grupo Tabelas, clique no botão Tabela Dinâmica.

Crie uma tabela dinâmica. No campo Nome da Linha colocamos as três primeiras colunas, no campo Valores colocamos uma coluna com contador. Na tabela dinâmica criada, os registros com valor maior que um serão duplicados, o próprio valor indicará a quantidade de valores duplicados. Para maior clareza, você pode classificar a tabela pela coluna Contagem para agrupar duplicatas.