Como remover duplicatas. Encontre e remova duplicatas no Microsoft Excel

As planilhas do MS Excel são usadas para analisar os dados que elas contêm. As informações são analisadas em sua forma original, mas mais frequentemente requerem edição preliminar. Neste artigo, veremos como se livrar de dados redundantes e repetitivos se isso dificultar o processamento de um arquivo.

Encontrando Valores Duplicados

Considere um exemplo. Uma loja de cosméticos recebe regularmente listas de preços de fornecedores em formato excel. Suponha, por conveniência, que os nomes das mercadorias em todas as empresas sejam os mesmos. Para fazer um pedido, queremos primeiro determinar qual das empresas é mais lucrativa para solicitar quais produtos.

Para fazer isso, você precisa encontrar registros idênticos e comparar preços neles. Por enquanto, não removeremos duplicatas no Excel, mas apenas encontraremos os mesmos produtos para análise de custos. Para fazer isso, usaremos a formatação da célula por condição. Selecione a coluna com o nome do produto e abra a barra de ferramentas Estilos na guia Início. O botão "Formatação condicional" abre uma lista suspensa de comandos, dos quais estamos interessados ​​\u200b\u200bno item "Regras de seleção de células".

A regra que precisamos é "Valores duplicados". Na janela que se abre, especifique a cor que será usada para marcar os elementos repetidos do intervalo. Depois de preencher as células com um filtro, você pode selecionar apenas elas, classificar por nome de produto, analisar preços e remover duplicatas no Excel.

Encontrando valores únicos

O método descrito acima também é adequado para selecionar elementos que ocorrem no singular. Para tal escolha, siga os mesmos passos do parágrafo anterior. Abra o painel de comando Estilos na guia Início e clique no botão Formatação Condicional. Na lista de operações, selecione "Regras de seleção de células".

Mas agora na janela de configurações do filtro, você precisa selecionar na lista suspensa o valor não "Duplicar", mas "Único". O programa marcará com a cor selecionada somente aqueles elementos da coluna que ocorrem em uma única instância.

Depois de mesclar várias listas de preços em um arquivo, há muitas entradas duplicadas na tabela que precisam ser removidas. Como a lista de produtos é muito longa, levará muito tempo e esforço para processá-la manualmente. É muito mais conveniente usar a opção que o programa oferece.

Para remover duplicatas no Excel, existe um comando de menu correspondente. Ele está localizado na guia "Dados" no bloco de operações "Trabalhar com dados". Clicar no botão abre uma caixa de diálogo. Se um grupo de células não foi selecionado na planilha antes de chamar o comando, o programa sugere imediatamente a seleção de colunas para as quais você precisa encontrar e remover repetições.

Se você selecionar os campos de uma coluna primeiro, o Excel exibirá uma mensagem de esclarecimento na qual você deverá escolher se deseja usar apenas o intervalo selecionado na operação subsequente ou expandi-lo.

Em seguida, marque as colunas para encontrar duplicatas. A função é conveniente porque permite encontrar correspondências completas e remover linhas duplicadas do Excel, bem como correspondências apenas para campos individuais.

Por exemplo, no nosso caso, podemos buscar repetições apenas por nomes, códigos, tipo e fabricante da mercadoria, sabendo que preços e fornecedores serão diferentes. Esse processamento permitirá que você deixe elementos exclusivos de produtos na lista para compilar sua própria lista de preços ou catálogo.

Outra maneira de remover duplicatas no Excel é selecionar apenas os valores exclusivos do intervalo da tabela selecionada. Vamos mostrar isso com um exemplo. Selecione um grupo de células para o qual deseja remover valores duplicados e abra a guia "Dados" no programa. Aqui encontramos o bloco de comandos "Classificar e filtrar" e selecionar um deles - "Avançado".

Na janela que se abre, insira os parâmetros de seleção de campo. Caso não queira manter a tabela original, selecione a opção "Filtrar a lista no local". Mas se o trabalho com ele não for concluído, é melhor transferir os resultados do filtro para outro local.

Selecionamos um grupo de campos para processamento e marcamos a célula na qual os dados filtrados serão colocados. Para obter apenas entradas exclusivas como resultado, marque a caixa "Apenas entradas exclusivas".

Obtemos como resultado dos 27 registros iniciais apenas 19 sem repetições. Este método pode remover duplicatas no Excel 2003, enquanto o anterior apareceu apenas na versão 2007 do programa.

Fique atento a algumas dicas antes de deletar informações das tabelas. Em primeiro lugar, antes de realizar tais ações, faça uma cópia da tabela e realize operações com ela. Ou deixe-o como backup e faça alterações no original. Caso contrário, você pode perder os dados do arquivo ou alterar o formato da folha.

Se a tabela de origem contiver agrupamento, soma ou você precisar removê-los antes de excluir duplicatas em uma coluna do Excel.

Encontrar itens duplicados não funciona em relatórios de tabela dinâmica.

Além do acima, é possível remover elementos duplicados da coluna usando fórmulas, mas esse método é bastante trabalhoso e não faz sentido nas versões modernas do programa.

Um item de longa data na lista de desejos do Excel era a capacidade de remoção automática linhas duplicadas em uma planilha. E agora, começando com o Excel 2007, os desenvolvedores da Microsoft cumpriram esse desejo.

Na fig. 33.1 mostra um intervalo de dados depois de convertido em uma tabela com o comando Inserir Tabela Tabela. Observe que esta tabela contém algumas linhas duplicadas.

Comece selecionando qualquer célula na tabela. Em seguida, execute o comando Serviço Construtor Remover Duplicatas. O Excel exibirá a caixa de diálogo mostrada na Fig. 33.2. Isso lista todas as colunas na tabela. Marque as caixas de seleção das colunas que deseja incluir na pesquisa duplicada. Na maioria dos casos, todas as colunas devem ser selecionadas - isso significa que uma duplicata será definida como tendo os mesmos dados em todas as colunas.

Caso seus dados não estejam inseridos em forma de tabela, utilize o comando Dados Trabalhar com dados Remover duplicatas para exibir a caixa de diálogo com o mesmo nome.

Deve-se entender que os valores repetidos são determinados pelo valor exibido na célula, e este não é necessariamente o valor armazenado na célula. Suponha que duas células contenham a mesma data: uma data é formatada como 15/05/2010 e a outra como 15 de maio de 2010. Ao remover duplicatas, o Excel assumirá que essas linhas são diferentes.

Clique no botão OK, e o Excel eliminará as linhas duplicadas e exibirá uma mensagem sobre quantas duplicatas foram removidas (Figura 33-3).

Neste artigo, vamos analisar como remover duplicatas no Excel 2007, Excel 2010, Excel 2013 e Excel 2016. Você aprenderá algumas jeitos diferentes Como encontre e remova duplicatas no excel com ou sem primeiras ocorrências, remover linhas duplicadas, Como remover duplicatas em uma coluna como detectar duplicatas absolutas e correspondências parciais. O artigo aborda os seguintes pontos:

Remova linhas duplicadas no Excel com a função Remover Duplicata

Se você estiver usando últimas versões Excel 2007, Excel 2010, Excel 2013 ou Excel 2016, você tem uma vantagem porque essas versões contêm uma função interna para localizar e remover duplicatas - Recurso Remover Duplicatas.

Este recurso permite encontrar e remover duplicatas absolutas (células ou linhas inteiras), bem como entradas parcialmente correspondentes (linhas que têm mesmos valores na coluna ou colunas especificadas). Vamos dar um exemplo de como usar passo a passo Remover a função Duplicatas no Excel.

Observação. já que a função Remover Duplicatas exclui permanentemente registros idênticos, é recomendável criar uma cópia dos dados originais antes de excluir linhas duplicadas.

1. Para começar, selecione o intervalo que deseja remover duplicatas. Para selecionar a tabela inteira, pressione Ctrl+A .
2. Em seguida, vá para a guia "DADOS" --> grupo " Trabalhando com dados” e pressione o botão “ Remover Duplicatas».

Remover Duplicatas no Excel - Função Remover Duplicatas no Excel

1. O " Remover Duplicatas". Selecione as colunas para verificar se há duplicatas e clique em OK.

• Para remover linhas duplicadas que tenham exatamente os mesmos valores em todas as colunas, deixe os checkboxes ao lado de todas as colunas conforme a imagem abaixo.
• Para remover duplicatas parciais com base em uma ou mais colunas-chave, selecione apenas as colunas relevantes. Se sua tabela tiver muitas colunas, é melhor clicar primeiro no botão "Desmarcar" e, em seguida, selecionar as colunas que deseja verificar se há duplicatas.
• Se sua tabela não tiver cabeçalhos, desmarque a opção " Meus dados contêm cabeçalhos' no canto superior direito da caixa de diálogo, que geralmente é selecionado por padrão.

Remover duplicatas no Excel - Selecione a(s) coluna(s) que deseja verificar se há duplicatas

Preparar! Todos linhas duplicadas no intervalo selecionado são removidos e uma mensagem é exibida indicando quantos foram duplicatas removidas registros e quantos valores únicos restam.

Remover duplicatas no Excel - Mensagem sobre quantas duplicatas foram removidas

Remover função duplicada no Excel remove a segunda e todas as instâncias duplicadas subsequentes, deixando todas as linhas únicas e as primeiras instâncias de registros idênticos. Se você quiser remover linhas duplicadas, incluindo as primeiras ocorrências, ou seja, se você deseja excluir todas as células duplicadas. Ou, em outro caso, se houver duas ou mais linhas duplicadas e você quiser manter a primeira e todas as subsequentes remover duplicatas , use uma das seguintes soluções descritas em .

Remova duplicatas copiando entradas exclusivas para outro local

Outra maneira remover duplicatas no excelé dividir valores únicos e copiá-los para outra planilha ou para um intervalo selecionado na planilha atual. Vamos analisar este método.

1. Selecione o intervalo ou a tabela inteira que deseja remover duplicatas .
2. Vá para a guia "DADOS" --> grupo " Classificar e filtrar" e clique no botão "Avançado".

Remover duplicatas no Excel - usando um filtro avançado para remover duplicatas

1. Na caixa de diálogo " Filtro avançado" faça o seguinte:

• Selecione " copie o resultado para outro local».
• Verifique se o intervalo correto é exibido em intervalo original. Este deve ser o intervalo que você selecionou na etapa 1.
• No campo Colocar o resultado no intervalo insira o intervalo que deseja copiar valores únicos(na verdade, basta selecionar a célula superior esquerda do intervalo de destino).
• Selecione Apenas registros únicos

Remover Duplicatas no Excel - Filtro de Duplicatas

1. Finalmente, clique em OK. O Excel removerá duplicatas e copie os valores exclusivos para o novo local especificado:

Remover duplicatas no Excel - Registros exclusivos copiados de outro lugar

Assim você obtém novos dados, com base no especificado, mas com duplicatas excluídas .

Observe que o filtro avançado permite apenas copiar os valores filtrados para outro local na planilha ativa.

Remover linhas duplicadas no Excel com fórmulas e filtro

Outra maneira remover duplicatas no excelé defini-los usando uma fórmula, filtro e remover linhas duplicadas.

A vantagem desta abordagem é a sua versatilidade - permite-lhe encontrar e remover duplicatas em uma coluna ou linhas duplicadas com base em valores em várias colunas. A desvantagem é que você precisará memorizar algumas fórmulas repetitivas.

1. Dependendo da sua tarefa, use uma das fórmulas a seguir para localizar duplicatas.

Fórmulas para encontrar duplicatas em 1 coluna

• Duplicatas, exceto para a 1ª ocorrência:

SE(CONT.SE($A$2:$A2, $A2)>1, "Duplicar", "")

• Duplicatas da 1ª ocorrência:

SE(CONT.SE($A$2:$A$10, $A2)>1, "Duplicado", "Único")

Onde a célula A2 é a primeira e A10 é a última célula do intervalo para localizar duplicatas.

Fórmulas para encontrar linhas duplicadas

• Linhas duplicadas, exceto para a 1ª ocorrência. Ou seja, se houver duas ou mais linhas idênticas no resultado, a primeira delas será marcada como única e todas as subsequentes como duplicadas:

SE(CONT.IFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1,"Linha Duplicada", "Única")

• Linhas duplicadas com a 1ª ocorrência. Nesse caso, se houver duas ou mais strings idênticas no resultado da pesquisa, todas serão marcadas como duplicadas:

SE(CONT.IFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, "Linha Duplicada", "Única")

Onde A, B e C são as colunas a serem verificadas quanto a duplicatas.

Por exemplo, como você pode identificar linhas duplicadas, exceto para a 1ª ocorrência:

Remover Duplicatas no Excel - Fórmula para identificar linhas duplicadas, exceto primeiros casos

1. Selecione qualquer célula em sua tabela e aplique um filtro automático clicando no botão Filtro na guia DADOS ou Classificar e filtrar" --> "Filtro" na guia "PÁGINA INICIAL".

Remover Duplicatas no Excel - Aplicar Filtro às Células (Guia DADOS)

1. Filtro linhas duplicadas clicando na seta no cabeçalho da coluna " duplicatas" e, em seguida, marque a caixa "Duplicar linha".

Remover duplicatas no Excel - Filtro de linha duplicada

1. E finalmente remover linhas duplicadas. Para fazer isso, selecione as linhas filtradas movendo o ponteiro do mouse sobre os números das linhas, clique com o botão direito do mouse e escolha "Excluir linha" no menu contextual. Neste caso, para remover duplicatas você não deve usar a tecla " Delete" do teclado, pois precisamos deletar linhas inteiras, e não apenas o conteúdo das células:

Remover duplicatas no Excel - Filtrar e remover linhas duplicadas

Bem, agora você aprendeu algumas maneiras de como remover duplicatas no excel. E você pode usar um deles, dependendo da sua situação.

Trabalhando com Microsoft Excel muitas vezes há uma situação em que é necessário remover linhas duplicadas. Esse processo pode se tornar um trabalho inútil, monótono e demorado, no entanto, existem várias maneiras de simplificar a tarefa. Hoje vamos dar uma olhada em alguns métodos úteis para localizar e remover linhas duplicadas no Excel. Vamos tomar como exemplo a seguinte tabela de dados:

Opção 1: Remover comando duplicado no Excel

O Microsoft Excel possui uma ferramenta interna que permite localizar e remover linhas duplicadas. Vamos começar procurando por linhas duplicadas. Para fazer isso, selecione qualquer célula na tabela e, em seguida, selecione a tabela inteira clicando em Ctrl+A.

Vá para a guia Data(Dados) e, em seguida, pressione o comando Remover Duplicatas(Remover Duplicatas) conforme mostrado abaixo.

Uma pequena caixa de diálogo aparecerá Remover Duplicatas(Remover duplicados). Você pode notar que a seleção da primeira linha é removida automaticamente. A razão para isso é a caixa de seleção definida no parágrafo Meus dados têm cabeçalhos(Meus dados contêm cabeçalhos).

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

Agora que toda a tabela 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 de exclusão serão exibidas em uma caixa de diálogo pop-up.

Opção 2: filtro avançado

A segunda ferramenta do Excel com a qual você pode encontrar 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.

Em seguida, vá para a guia Dados(Dados), no grupo de comando Classificar e Filtrar(Classificar e Filtrar) clique no comando Avançado(Opcional) como mostrado 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(Apenas entradas únicas).

Depois de pressionar OK todas as duplicatas no documento serão removidas, exceto 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 você deseja excluir a primeira linha, deverá excluí-la manualmente. Quando a 1ª linha tiver cabeçalhos e não duplicatas, apenas uma cópia das repetições existentes permanecerá.

Opção 3: Substituição

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

Com a tabela aberta, selecione a célula cujo conteúdo deseja localizar e substituir e copie-a. Para fazer isso, selecione a célula desejada e pressione o atalho de teclado ctrl+c.

Depois de copiar a palavra que deseja encontrar, use a combinação ctrl+h para abrir uma 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 de Corresponde a todo o conteúdo da célula(Célula inteira). Isso deve ser feito porque em algumas células as palavras de busca 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 correspondem às mostradas na figura abaixo.

Agora você precisa inserir um valor no campo Substituir com(Substituído por). EM este exemplo nós usamos números 1 . Depois de inserir o valor desejado, pressione Substitua tudo(Substitua tudo).

Pode-se ver que todos os valores duplicado nas células da tabela será substituído 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 deixar 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 a uma mantendo pressionada a tecla ctrl.

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

Ao fazer isso, você pode perceber que todas as linhas restantes têm valores exclusivos.

Encontrar e remover manualmente valores duplicados em planilha do Excel, especialmente em documentos grandes, é uma ocupação muito arriscada e inconveniente. É possível que você perca duplicatas verificando as células visualmente. E então, uma quantidade colossal de tempo é gasta na exclusão de cada célula.

Neste artigo, veremos como você pode remover duplicatas no Excel usando várias funções regulares automatizadas.

Conselho! Para a conveniência de aprender como remover linhas, colunas, valores de células duplicados, abra um modelo de rascunho ou uma cópia do projeto em que você está trabalhando no editor para não perder dados valiosos ou violar o formato do original por engano durante a edição.

Método número 1

1. Segurando botão esquerdo mouse, marque com o cursor as bordas da área da tabela (linhas separadas, colunas) onde deseja remover as repetições. Ou selecione o projeto inteiro.

2. No menu do editor, vá para a guia "Dados".

3. No bloco "Trabalhar com dados", clique no botão "Excluir duplicatas".

4. No painel que aparece, selecione as colunas nas quais deseja remover os mesmos valores definindo rótulos. Se houver muitos elementos, use configuração rápida removendo as opções "Selecionar tudo" e "Desmarcar tudo". Clique OK.

5. Após a conclusão da limpeza das linhas e colunas da tabela de duplicatas, aparecerá uma mensagem indicando quantos valores exclusivos permanecem.

Conselho! As repetições imediatamente após a exclusão podem ser restauradas clicando no ícone de seta para a esquerda no canto superior esquerdo do Excel.

Método número 2

1. Clique na tabela a ser editada.

2. Na guia "Dados", no bloco "Classificar e filtrar", clique na subseção "Avançado".

Se você precisar criar uma nova tabela contendo apenas células de origem exclusivas:

1. No painel "Filtro avançado", clique no botão de opção "Copiar resultado para outro local".

2. Clique no botão localizado à direita do campo "Colocar resultado no intervalo".

3. Clique em um espaço de trabalho vazio do Excel onde deseja colocar a tabela filtrada. Após clicar, o código da célula aparecerá no campo. Feche-o e vá para as opções de filtro.

4. Clique na caixa "Apenas registros exclusivos" e clique em "OK".

5. Após a filtragem, uma versão da tabela original sem repetições aparecerá no local especificado.

Para editar um documento sem fazer cópias:

• no painel "Filtro avançado", defina o modo de processamento como "Filtrar a lista no local";
• com um clique do mouse, ative o complemento "Apenas registros exclusivos";
• Clique OK".