Nem sempre é fácil localizar duplicatas em uma mesa, especialmente quando ela é grande. Mas o Excel tem muitas funções para localizá-los, mostrá-los, ocultá-los, contá-los e excluí-los se necessário.
Embora seja normal que algumas de suas tabelas tenham dados duplicados em uma coluna específica - se você estiver no comércio, felizmente tem o direito de vender a mesma referência de produto na sexta-feira e na terça e quinta-feira seguintes.! -, é igualmente normal querer, em um momento ou outro, manter apenas uma lista de dados únicos, ou acumular para essas duplicatas, ou mesmo realizar essas duas tarefas ao mesmo tempo.
Uma simples ordenação na coluna direita de uma tabela do Excel já é suficiente para identificar valores iguais ou semelhantes, sejam rótulos de texto ou datas, por exemplo. Mas o Excel nasceu em 1985, ao longo das versões, ele também se acumulou! Hoje oferece muitas funções, por vezes redundantes ou com funcionalidades subtis, que podem ser encontradas em diferentes menus, para o ajudar a tratar estas duplicatas como quiser: realçar, ocultar, combinar ou calcular a média, ou mesmo eliminá-las.
O layout e os nomes das opções diferem um pouco entre as versões do Excel para Windows, Mac e para a web, mas funcionam de forma idêntica.
Como localizar duplicatas em uma ou mais colunas no Excel?
Existem várias maneiras de localizar todos os valores duplicados em uma ou mais colunas de uma só vez, sem realmente removê-los.
- Clique em qualquer célula da sua tabela e, para selecionar todos os dados nela, pressione a combinação de teclas Ctrl + A no PC ou Cmd + A no Mac. Se a primeira linha de cabeçalho (Aluno, Assunto) faz ou não parte da seleção é irrelevante para a função que você vai iniciar aqui. Esteja ciente, no entanto, que pressionando novamente Ctrl + A, a seleção se estenderia a esses títulos de coluna (se esses cabeçalhos ainda não estiverem selecionados), e que uma nova pressão sobre Ctrl + A selecionaria a folha inteira.
- Você também pode selecionar apenas uma coluna para se interessar apenas por duplicatas nesta coluna: clique no primeiro valor (por exemplo em Peter em nosso exemplo) e pressione as três teclas Ctrl + Shift + Seta para baixo, no PC ou Mac ou no Excel para a Web. Você também pode clicar, na parte superior, no A da coluna A.
- Na aba Página inicial Clique em Formatação condicional> Regras de realce de células> Valores duplicados.
- Na primeira lista suspensa, selecione duplicar (o outro critério possível é: uniques) e, na segunda lista, escolha uma formatação de célula, por exemplo Preenchimento vermelho claro ou especifique outro clicando em Tamanho personalizado…
- Se você escolheu um formato personalizado, o Formato de Célula permite-lhe escolher a formatação de duplicados usando os separadores Número, fonte, borda et O preenchimento. Clique por exemplo no botão Padrões e texturas, você pode escolher duas cores da paleta para criar um gradiente.
- Pressione várias vezes OK para validar suas escolhas, células duplicadas são formatadas conforme você especificou para facilitar a identificação.
- Esta formatação não substitui a formatação antiga de suas células, ela desaparecerá quando você selecionar sua tabela (Ctrl + A ou Cmd + A) e que, na guia Página inicial , você vai lançar Formatação condicional> Limpar regras das células selecionadas. Para evitar ter que selecionar toda a mesa, você também pode clicar em Apague as réguas de toda a folha.
- Se um valor, por exemplo Emma, aparecer duas vezes na coluna Aluno, o Excel marca esse valor duas vezes como Emma. Especificamos isso porque em outras funções que veremos mais tarde, o Excel deixa intacta a primeira ocorrência de Emma, mas atua apenas nas seguintes, as duplicatas.
- Mesmo que várias colunas sejam selecionadas (como em nosso exemplo), esta função do Excel procura por duplicatas em cada coluna, independentemente das outras. O Excel, portanto, não pesquisa na tabela todas as linhas onde o par Emma + Histórico aparece, ele formata Emma se o primeiro nome aparecer várias vezes na coluna do Aluno e formata o Histórico se esta legenda aparecer várias vezes na coluna Assunto. Você também pode executar o comando na primeira coluna, depois, na segunda, obterá os mesmos resultados que iniciá-lo imediatamente nas duas colunas da tabela.
- O Excel não leva em consideração o caso (maiúsculas): toto, Toto, ToTo e TotO são mencionados como duplicatas.
- A planilha também não leva em consideração o formato: se três células armazenam a data 11/11/2021, todas serão mencionadas como duplicatas, mesmo que, devido ao seu formato de exibição, a primeira exiba 11/11, a segunda 11/11 e o terceiro 11/20/XNUMX.
Como filtrar dados para mostrar duplicatas no Excel?
Os filtros são muito úteis para exibir apenas certas linhas em uma tabela. O Excel oculta as linhas que não correspondem aos seus critérios, mas não remove nenhum dado - se você estiver usando uma planilha compatível com o Excel, certamente receberá uma função de filtro. Com os filtros, você pode, por exemplo, exibir em uma lista de produtos apenas aqueles cuja marca é Sony - o que é o mesmo que localizar todas as cópias da Sony. E, com a mesma facilidade, crie filtragem multicritério em várias colunas, como: Marca = Sony e Categoria = Televisão.
- Os filtros do Excel são reconhecíveis pela pequena seta suspensa que aparece à direita de cada cabeçalho de coluna, para classificar ou filtrar seus dados de acordo com os critérios que você especificar. É essencial que cada uma de suas colunas comece com um cabeçalho (um título de coluna).
- Para ativar os filtros, um primeiro método consiste em clicar em uma célula não vazia de seu intervalo de células e, na guia Página inicial , para clicar em Classificar e filtrar, então abaixo em Filtro. O atalho do teclado é Ctrl + Maj + L no macOS e Windows (este ícone Filtro também está disponível na guia Dados) Pequenas setas suspensas aparecem na primeira linha contendo os títulos.
- Outro método possível de ativação de filtros, mais específico do Excel e que oferece mais possibilidades (você verá um uso deste mais tarde para calcular a soma das duplicatas): crie uma "tabela" a partir dos seus dados. Comece clicando em um de seus dados. Sob a guia Página inicial , agora clique em Formatar como uma tabela, selecione uma formatação entre as propostas ...
- … O Excel seleciona a tabela inteira e marca se ela tem cabeçalhos ou não. Se suas colunas não tiverem um, ele adiciona alguns inserindo uma linha acima dela e os chama de Coluna 1, Coluna 2, etc. Mas é preferível nomear você mesmo suas colunas (Data, Cliente, Serviço, etc.). Clique em OK, a tabela é formatada e os filtros são ativados automaticamente.
- Qualquer que seja o método para os filtros ativados, clique em um seta suspensa um título de coluna para descobrir as opções de classificação e filtragem. A apresentação, embora um pouco diferente no Mac, oferece a mesma riqueza.
- Se você deseja ver apenas, por exemplo, as linhas correspondentes ao cliente Dupond, a maneira mais rápida é primeiro desmarcar a caixa (Selecionar tudo) - que, portanto, desmarcará tudo - e, em seguida, marcará apenas a caixa Dupond (O Excel considera Dupond e dupond iguais).
- Apenas as linhas correspondentes a este cliente permanecem exibidas: você verá, portanto, as duplicatas. As outras linhas estão simplesmente ocultas, você pode ver à esquerda, em nossa ilustração, os números das linhas indicam: 1, 2, 4, 6, 11 (enquanto as linhas 3, 5, 7, 8, 9, 10 estão ocultas) .
- Você pode adicionar critérios de filtro nas outras colunas. Para uma pesquisa multicritério, por exemplo, para reter apenas o artigo do blog do serviço do cliente Dupond, clique, além disso, na seta suspensa Serviço para verificar apenas esta legenda. Como alternativa, você pode solicitar que a célula contenha (ou não contenha) o texto que você especificar, etc.
- Para deletar um filtro em uma coluna, clique no botão suspenso e depois em Filtro limpo.
- Para desativar permanentemente os filtros e remover as pequenas setas suspensas: na guia Página inicial Clique em Classificar e filtrare, em seguida, Filtro.
Como filtrar dados para ocultar duplicatas no Excel?
Os filtros permitem que você exiba apenas duplicatas em uma planilha. Mas você pode usar os filtros avançados do Excel para, ao contrário, ocultá-los sem remover os dados duplicados. Observe que esse recurso está disponível apenas nas versões do Excel para Windows e Mac, e não na versão para web.
- Em seu intervalo de dados, selecione apenas a coluna que provavelmente terá duplicatas. Clique por exemplo no seu primeiro valor e pressione as três teclas Ctrl + Shift + Seta para baixo.
- Clique, na parte superior, na guia Dadose, em seguida, avançado, na seção Classificar e filtrar.
- Na janela Filtro avançado, certifique-se de que apenas a coluna com as duplicatas seja selecionada.
- Marque a caixa Extração duplicada e deixe o campo em branco Área de critérios.
- Se você quiser Filtre a lista na hora, apenas a primeira aparição de um elemento duplicado permanece exibida, as linhas de sua tabela contendo duplicatas são ocultadas. Você pode ver isso à esquerda, olhando para os números das linhas: em nosso exemplo, vamos da linha 3 para a linha 6.
- Para exibir novamente as linhas ocultas, você pode: na guia Dadosclique em Apagar...
- ... ou, na guia Página inicial clique em Classificar e filtrare, em seguida, Apagar.
- Uma dica para PC: se você realmente deseja deletar essas células ocultas, clique na aba Arquivo> Informações> bouton Verifique se não há problemas, choix Inspecione o documento> bouton inspecionar… Role a lista de problemas inspecionados e, na seção Linhas e colunas ocultas, pressione o botão Apagar tudo. Observe que esta ação não pode ser desfeita!
- Se você preferir Copiar para outro local (isso só é possível na planilha ativa), clique em uma célula vazia, abaixo nós escolhemos H1... Depois de checar Extração duplicada e apertou o botão OK, os elementos únicos são copiados de H1 à H6. Sem mexer na sua tabela original, esta coluna poderia ser usada para calcular o valor gerado por cada cliente, mostraremos a fórmula abaixo.
Como remover duplicatas no Excel?
Quer remover permanentemente duplicatas de uma planilha? Se for uma tabela com várias colunas, toda a linha da tabela onde a duplicata aparece será removida. Se seus dados se resumirem a uma coluna, você obterá a lista de dados exclusivos. O Excel oferece uma função especial para isso. Seu método: ele mantém o primeiro elemento e remove todas as duplicatas que então detecta, sem se preocupar com a relevância de manter a primeira em vez da última, e sem, por exemplo, realizar qualquer acumulação (aprenda aqui como acumular duplicatas). Dependendo dos seus dados, uma classificação prévia pode, portanto, ser útil ...
- Mesmo que seja obviamente possível cancelar a ação (Ctrl + Z no PC ou Cmd + Z no Mac), é melhor trabalhar em uma cópia dos seus dados ou até mesmo duplicar a planilha antes de realizar a exclusão. Um pequeno clique com o botão direito na guia da planilha irá ajudá-lo ...
- Embora não seja obrigatório, é melhor se suas colunas tiverem uma linha de cabeçalho.
- Clique uma única célula, qualquer, da sua mesa.
- Na aba DadosClique em Remover duplicatas.
- Se você transformou seus dados em uma "tabela" do Excel por meio da guia Página inicial > Formatar como uma tabela, esta opção Remover duplicatas também pode ser encontrado na guia Criação de mesa no PC, ou Quadro no Mac, ou criação no Excel para a web. Esta guia só fica visível quando você seleciona uma das células da tabela.
- O Excel seleciona a tabela inteira e lista os cabeçalhos das colunas que marcou.
- Fique tranquilo: quer você marque ou desmarque tal ou qual caixa, o Excel não corre o risco de excluir um valor em uma coluna sem afetar os outros na mesma linha, o que criaria uma mudança como uma camisa mal abotoada! Se ele tiver que deletar uma duplicata, em qualquer caso, ele irá deletar uma linha completa intervalo de dados (as células fora de sua tabela não são afetadas).
- Se você deixar todos os nomes das colunas marcados, o Excel irá considerar como duplicatas apenas as linhas onde aparecem, em nosso exemplo: ambas na mesma data, mas além do mesmo nome de cliente, mas também o mesmo tipo de serviço e, finalmente, a mesma quantidade . Se algum dos valores diferir nas linhas abaixo, eles não serão considerados uma duplicata.
- Mais provavelmente, portanto, você desejará marcar apenas algumas colunas. Verificando, por exemplo, apenas a coluna Cliente (ilustração abaixo): cada nome de cliente aparecerá apenas uma vez em sua tabela (3 Dupond são excluídos, 1 Durand e 1 Matthieu).
- Verificando por exemplo Cliente et benefício (ilustração abaixo): apenas as linhas tendo de uma só vez o mesmo nome et o mesmo serviço é considerado duplicado. Em nosso exemplo, três linhas se comportaram como Serviço ao cliente Valores Dupond / Blog Post, as duas últimas linhas duplicadas são removidas.
- Acima, consideramos o caso em que você deseja remover linhas duplicadas diretamente de sua tabela. Mas você pode, por exemplo, copiar e colar uma única coluna da tabela, por exemplo, copiar células B1: B11 da coluna Cliente, para colá-los na célula F1. E pedindo a exclusão de duplicatas em F1: F11 ...
- … Você obterá a lista de seus clientes exclusivos. Esta coluna pode ser utilizada para calcular o faturamento gerado por cada cliente, como veremos a seguir.
Como calcular a soma das duplicatas na planilha do excel?
Saber que existem duplicados é bom. Exclua-os para manter apenas os elementos únicos, por que não? Mas na maioria das vezes, você vai querer fazer cálculos nessas duplicatas primeiro, pelo menos obter o total, mesmo que isso signifique removê-las! Veja como fazer isso, dependendo do método escolhido para detectar valores duplicados.
Calcule o total de valores filtrados
Se você acabou de filtrar seus dados, uma pequena fórmula será suficiente para calcular o total de células filtradas e, portanto, o total geral se todas as células forem exibidas.
- Em um intervalo de células filtradas pela guia Home> Classificar e filtrar> Filtrar, a função SOUS.TOTAL O Excel permite calcular a soma das células exibidas (ou a média, ou o número de células, etc., dependendo dos parâmetros que você especificar).
- No exemplo abaixo, inserindo na célula G1 Eu o formulei:
= SUBTOTAL (109; $ D: $ D)
para calcular o total da coluna D, obtemos o total geral, uma vez que nenhuma coluna é filtrada aqui. O valor 109 significa: realiza a soma, ignorando as células ocultas.
- Se você não quiser levar em consideração toda a coluna D, mas apenas as células onde seus dados estão armazenados, a fórmula pode ser escrita:
= SUBTOTAL (109; D2: D11) - Se filtrarmos a coluna Cliente para reter apenas o cliente Smith, a função SUBTOTAL - que sempre exclui os valores filtrados de seus cálculos - informa o total dos dados exibidos, portanto, do cliente Smith.
Calcule o total de uma tabela Excel
Se você transformou seu intervalo de células em uma tabela do Excel por meio da guia Home> Formatar como uma mesa, você não precisa escrever uma fórmula, o Excel gerencia isso para você.
- Clique em qualquer célula da tabela: uma guia verde Criação de mesa no PC (ou Quadro no Mac, ou criação no Excel para a Web) é adicionado às guias já presentes (Arquivo, Página inicial, Inserir, etc.) na parte superior da tela.
- Clique nesta aba Criação de mesa então, abaixo, em Linha total. O Excel adiciona uma linha total na parte inferior da tabela, para colunas que contêm números.
- Se você filtrar seus dados, o total indica a soma apenas dos valores exibidos. Uma seta suspensa permite escolher a média ou outra função, em vez da soma.
Calcule o total de cada duplicata
Sem tocar em seus dados iniciais, você criou uma coluna livre de duplicatas para manter apenas elementos únicos? Se for, por exemplo, uma lista de clientes, uma pequena fórmula será suficiente para calcular o faturamento gerado por cada um desses clientes.
- Em nosso exemplo, a coluna G inclui a lista de clientes exclusivos. A fórmula escrita em H2 permite calcular o volume de negócios gerado pelo cliente cadastrado no celular G2, portanto Dupond:
= SUM.SI ($ B: $ B; G2; $ D: $ D)
- Com esta fórmula, o Excel compara o conteúdo da célula G2 (Dupond) em cada valor da coluna B. Se houver equivalência, ele acumula o valor escrito na mesma linha, mas na coluna D. Por exemplo, se comparar Smith com o conteúdo da célula B5, que também inclui Dupond, adiciona à sua acumulação o valor inserido em D5.
- Copie esta fórmula nas células H3 à H6 conhecer o faturamento gerado por cada cliente. A fórmula copiada para a célula H3 torna-se por exemplo:
= SUM.SI ($ B: $ B; G3; $ D: $ D)
Insira subtotais automáticos nas tabelas
Última solução possível que oferecemos: subtotais. O Excel para Windows e para Mac (mas não para a Web) pode, de fato, inserir automaticamente linhas de subtotais e total geral para acumular seus dados. A planilha pode calcular a soma, ou a média, ou o mínimo, o máximo, o desvio padrão, etc. Com algumas restrições para saber:
- Os subtotais não são suportados em "tabelas do Excel". Quando você clica em uma célula em seu intervalo de dados, se uma guia verde Criação de mesa é exibido na parte superior da tela (é chamado Quadro no Mac, e criação no Excel para a web), você deve primeiro clicar abaixo no ícone Converter para intervalo para que você possa pedir ao Excel para inserir subtotais.
- Assim que você clicar no ícone Subtotal, O Excel esvazia seu histórico de desfazer. Portanto, você não poderá mais cancelar um pedido antigo digitando Ctrl + Z, mas ainda é possível fechar o arquivo e reabri-lo para voltar ao último registro da pasta de trabalho do Excel. Trabalhe em uma cópia do seu arquivo se estiver fazendo experiências.
- Inserir subtotais ativa o Modo plano, o que pode impressionar inicialmente os iniciantes. Não há razão !
Veja como adicionar subtotais às colunas numéricas.
- Comece classificando seus dados para que as duplicatas sejam agrupadas uma após a outra. Se for uma classificação em uma coluna, clique em qualquer valor desta coluna (em uma única célula apenas) e, em seguida, na guia Dados, clique por exemplo no ícone AZ para um tipo simples e ascendente. Se você precisar classificar em duas ou mais colunas, ou especificar outros critérios de classificação, clique no ícone. Trier.
- No exemplo abaixo, nosso array, previamente classificado na coluna Data, agora está classificado na coluna Cliente, em ordem alfabética, clicando no ícone AZ.
- Depois que seus dados estiverem classificados, selecione qualquer célula em sua tabela (mas apenas uma célula) e, ainda na guia Dados, clique no ícone Subtotal.
- A janela de opções do subtotal é exibida. O Excel sugere a realização de um subtotal A cada mudança de data. Desenrole esta lista e especifique em seu lugar A cada mudança de cliente.
- A lista Use a função espectáculos soma. Clique nele se preferir outra função (média, mínimo, máximo, desvio padrão, número de valores, etc.).
- Em seguida, verifique os itens numéricos (pode ser uma coluna de valores faturados ou uma coluna de horas indicando a duração de cada serviço, por exemplo) sobre os quais deve ser efetuada a acumulação.
- Se você preferir que a linha de total geral apareça no topo da tabela ao invés do final da lista, e o subtotal de cada cliente apareça acima em vez de abaixo de suas linhas de detalhes, desmarque a caixa Resumo sob os dados.
- Observe a presença do botão Apagar tudo, para remover as linhas de subtotal e desativar o mundo Plan.
- Aqui está nossa tabela com, para nossa coluna Valor, os subtotais em negrito inseridos pelo Excel (Total Bertrand, Total Dupond, etc.), e o total geral no final da lista. De sua parte, se sua tabela possui várias colunas que merecem um subtotal (colunas de números, durações, etc.), marque várias.
- À esquerda, se você clicar no nível 2 do plano, você exibe apenas as linhas correspondentes aos subtotais e ao total geral.
- Se você realizar cálculos por hora e descobrir que os totais por hora estão errados porque sempre voltam às 24:00:00 (21h + 5h = 2h em vez de 26h!), Selecione as células que contêm seus dados por hora, clique com o botão direito do mouse e escolha Formato de Célula no menu de contexto. Sob a guia Nome, clique com o botão esquerdo em Tempo e, na lista Tipo, clique no exemplo de formato 37: 30: 55.
- O Excel agora exibe os totais por hora corretos! Quer criar um formato de hora personalizado? Cerque o [h] representando as horas para solicitar que o Excel exiba tempos superiores a 24 horas: [h]: mm: ss em vez de h: mm: ss
Como proibir a entrada de duplicatas em uma coluna do Excel?
Para evitar que o mesmo valor seja inserido mais de uma vez em uma coluna, tudo o que você precisa fazer é usar a função de validação de dados do Excel.
- Selecione a coluna inteira ou intervalo de células onde você precisará inserir seus dados, abaixo da coluna B.
- Na aba DadosClique em Validação de dados.
- Na lista autorização, selecione personnalisé.
- Na área de entrada Fórmulas, uma vez que a célula B1 está atualmente selecionada, digite a fórmula:
= NB.SI (B: B; B1) = 1
- Adapte esta fórmula: se por exemplo a célula C3 for selecionada, digite = NB.SI (C: C; C3) = 1
- Nesta mesma janela de diálogo de Validação de dados, clique na guia Alerta de erro.
- Na área Título, insira uma mensagem curta, aqui Duplicar.
- Na área Mensagem de erro, seja mais explícito.
- Na área Estilo : a escolha Parar proibir a entrada de duplicados; escolhas Renúncia ou informações exibir sua mensagem, mas deixe o usuário inserir uma duplicata, se desejar.
- Neste exemplo, quando você pressiona a tecla entrada, o estilo Parar proíbe entrar no Dupond duas vezes, mesmo em letras minúsculas, e propõe Tente novamente.
- Para obter mais informações sobre validação de dados e ajuda de entrada, em particular usando listas suspensas, consulte nossa ficha prática Crie listas suspensas no Excel.
Conteúdo Identificar duplicatas em colunas Filtrar dados para exibir duplicatas Filtrar dados para ocultar duplicatas Remover duplicatas no Excel Calcular a soma de duplicatas Proibir a entrada de ...
