Valores Duplicados no Excel – Como Impedir o Cadastro de Dados Repetidos
Na publicação de hoje vamos falar um pouco mais sobre um método para que você nunca mais precise se preocupar com valores duplicados no Excel!
Para evitar valores duplicados no Excel, use a Validação de Dados (guia Dados > Validação de Dados > Personalizado) com uma fórmula baseada em CONT.SES que conta quantas vezes o valor aparece na coluna e bloqueia o cadastro quando passa de 1. Para encontrar duplicados que já estão na planilha, use Página Inicial > Formatação Condicional > Realçar Regras das Células > Valores Duplicados ou a fórmula =CONT.SES(A:A;A2) numa coluna auxiliar.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: Para bloquear cadastro repetido, o instrutor usa Validação de Dados no modo Personalizado com a fórmula CONT.SE: ela conta quantas vezes o valor digitado aparece na coluna e só aceita se o resultado for menor ou igual a 1. Na aba Alerta de Erro dá para trocar a mensagem padrão do Excel por um aviso claro.
Neste vídeo (10 min):
- 1:00 - Por que importa: um funcionário lançado duas vezes distorce soma de salários, média e contagem, o erro nasce de várias pessoas usando a mesma planilha.
- 2:00 - Selecione a coluna inteira e vá em guia Dados > Validação de Dados, a ferramenta que limita o que pode ser digitado.
- 3:00 - Entre os tipos (número inteiro, decimal, lista, data, hora…), escolha Personalizado, é o que aceita uma regra escrita como fórmula.
- 4:00 - A fórmula é o
CONT.SE: o intervalo é a coluna toda e o critério é a primeira célula dela (a regra se estende sozinha para as demais). - 5:30 - Feche a regra com
<=1: o nome pode aparecer no máximo uma vez, vale o “menor ou igual” porque a célula também pode estar vazia. - 6:30 - Testando: ao repetir um nome já cadastrado, o Excel recusa a entrada e exibe o aviso padrão de restrição de validação.
- 7:30 - A mensagem padrão confunde quem não é de Excel. Volte em Validação de Dados e use a aba Alerta de Erro.
- 8:00 - Preencha o título (o cabeçalho da caixa) e a mensagem com um texto direto, do tipo “esse funcionário já foi incluído”.
Trechos do vídeo:
- O critério da fórmula aponta apenas a primeira célula da coluna de propósito: a validação replica a regra célula a célula, comparando sempre o valor que está sendo digitado com a coluna inteira.
- A escolha do <=1 em vez de =1 cobre o estado inicial: a célula em branco também precisa passar na regra, senão a planilha trava antes mesmo de alguém digitar.
- Personalizar o alerta é o passo que faz a regra funcionar na prática. O aviso padrão (“este valor não corresponde às restrições de validação de dados”) não diz a quem está preenchendo o que fazer.
Para baixar a planilha utilizada nesta publicação, clique aqui!
Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:
Como evitar o cadastro de Valores Duplicados no Excel?
Isso é uma forma que podemos criar dentro do Excel para impedir que em uma determinada tabela não sejam cadastrados itens repetidos. Isso é muito importante, pois em diversas situações precisamos de dados únicos e ao cadastrar mais de um pode gerar um problema posteriormente.
Quando Bloquear Esses Valores?
Esse procedimento será utilizado sempre que o usuário precisar de apenas um único dado dentro de sua tabela, seja por nome, produto, área, etc. Contanto que precise de apenas um único valor essa forma servirá para evitar o cadastro de algum dado que já esteja na planilha.
Isso é importante principalmente quando a quantidade de dados é muito grande e fica inviável a verificação manual desses dados para saber se realmente é um dado único ou não.
Como Evitar Esse Cadastro?
Para iniciar vamos analisar a tabela inicial que temos com os dados em que vamos evitar que mais informações sejam cadastradas de forma duplicada.
O primeiro passo é selecionar a coluna A, pois é nela que queremos impedir o cadastro de valores duplicados, ou seja, dentro da empresa não é possível ter duas pessoas com o mesmo nome, pois isso iria gerar problemas com pagamento e outros cálculos dentro da empresa.
Feito isso vamos até a guia Dados e vamos selecionar a opção Validação de Dados, que permite ao usuário restringir valores que serão inseridos dentro desse intervalo selecionado.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Ao selecionar essa opção uma janela será aberta para podermos escolher como vamos restringir esses dados.
Na parte de Permitir é possível escolher qual o tipo de dado será liberado para que o usuário insira nas células selecionadas. Como queremos algo que não tem na lista, vamos selecionar a opção Personalizado, para podermos inserir uma fórmula manualmente.
A fórmula que vamos utilizar é para que o Excel possa contar quantas vezes cada valor de célula aparece dentro da coluna A, ou seja, estamos verificando se aquela informação aparece no máximo uma única vez, caso contrário o Excel não irá permitir que o cadastro dessa informação seja feito.
Para verificar se está realmente funcionando vamos escrever um nome que já temos dentro dessa coluna para analisar a resposta do Excel.
Nesse caso estamos inserindo o nome da Thaís Azevedo Moura, já cadastrado na linha 145, desta forma o Excel retorna um erro informando que essa informação está fora do limite que colocamos, ou seja, a contagem desse nome passou de 1.
Esse erro para algumas pessoas pode parecer estranho e confuso, então para facilitar o entendimento, podemos alterar essa mensagem para explicar melhor ao usuário o que aconteceu.
Para isso basta selecionar novamente a coluna A e voltar a validação de dados, no entanto, vamos entrar na guia Alerta de Erro.
Nesta parte é possível colocar um título para a caixa de erro e mostrar um texto para explicar o motivo do erro.
Então ao tentar inserir novamente uma informação que já temos dentro da coluna A o Excel irá mostrar a nova mensagem para explicar o motivo do erro, fazendo com que o usuário saiba que aquele funcionário já está cadastrado e não há necessidade de cadastrá-lo novamente.
Método visual
Podemos destacar valores repetidos com a formatação condicional apenas colorindo as células.
-> Selecione a lista onde estão os valores
-> Vá em página inicial
-> Formatação condicional
-> Regra de realce das células
-> Valores duplicados
Feito isso, vai aparecer uma janela onde você irá selecionar “duplicados” e a cor do preenchimento que você deseja, caso o valor esteja repetido, clique em ok e está pronto, esta é mais uma forma de fazer…
Procurando valores com a função CONT.SES
Neste exemplo vamos colocar a função CONT.SES na coluna ao lado para contar quantas vezes o número se repete.
Fazendo assim podemos facilmente verificar quando o valor apareceu duas ou mais vezes, podemos inclusive colocar um filtro e deixar apenas os repetidos para depois excluí-los.
No primeiro argumento da célula selecione a coluna onde temos os valores e no segundo a primeira célula onde temos valor na coluna, feito isso arraste a função para as demais células.
=CONT.SES(A:A;A2)
Esta forma de fazer é bom para quando queremos conferir os valores antes de excluir, porém, se não existir dúvidas a respeito de querer excluir qualquer valor duplicado, podemos apenas selecionar a coluna A -> Ir a guia Dados -> e selecionar o ícone “remover duplicatas”.
Dessa forma todas as duplicatas são excluídas imediatamente.
Como excluir valores repetidos em 2 colunas?
Para fazer isso primeiro você deve concatenar (juntar) os valores em uma coluna que vai servir de apoio.
=CONCAT(A2:B2)
Feito isso, arraste a função para todas as células abaixo, agora temos em uma única coluna produto e marca.
Agora podemos usar a coluna ProdutoMarca que criamos para identificar os valores repetidos da mesma forma que fizemos nos exemplos acima, usando a formatação condicional e o CONT.SES:
=CONT.SES(C:C;C2)
Também podemos usar o ícone de formatação condicional, dessa vez selecionando as colunas A e B.
Deixe marcado ambas as colunas para excluir apenas os produtos que se repetem em ambas.
Você pode fazer esse mesmo tratamento de valores duplicados no excel em 3, 4 ou mais colunas.
Perguntas frequentes sobre valores duplicados no Excel
1. Qual fórmula identifica valores duplicados no Excel?
A função CONT.SES é a mais usada: =CONT.SES(intervalo;célula) conta quantas vezes um valor aparece dentro do intervalo. Quando o resultado é maior que 1, o valor está duplicado. Você pode combiná-la com a Formatação Condicional para colorir automaticamente as células repetidas.
2. Como impedir que um valor duplicado seja cadastrado no Excel?
Selecione a coluna, vá em Dados > Validação de Dados > Personalizado e insira uma fórmula baseada em CONT.SES que limite a contagem a 1. Assim, o Excel bloqueia a digitação de um valor que já existe na coluna e pode até exibir uma mensagem de erro personalizada.
3. Como remover valores duplicados automaticamente no Excel?
Selecione a coluna (ou colunas), abra a guia Dados e clique em Remover Duplicatas. O Excel elimina todas as ocorrências repetidas, mantendo apenas a primeira, sem precisar de nenhuma fórmula.
4. Como encontrar duplicados considerando duas colunas ao mesmo tempo?
Concatene os valores das duas colunas com a função CONCAT numa coluna auxiliar e aplique o CONT.SES ou a Formatação Condicional sobre essa nova coluna. Assim, só é contado como duplicado quando as informações das duas colunas juntas se repetem.
5. Qual a diferença entre Formatação Condicional e Remover Duplicatas?
A Formatação Condicional apenas destaca visualmente os valores repetidos, sem apagar nada, o que é útil para conferir antes de decidir o que fazer. Já a opção Remover Duplicatas exclui definitivamente as linhas repetidas da planilha.
6. É possível personalizar a mensagem de erro ao tentar cadastrar um valor duplicado?
Sim. Na janela de Validação de Dados, abra a guia Alerta de Erro e escreva um título e uma mensagem personalizados. Assim, o usuário entende exatamente por que aquele cadastro foi bloqueado.
Conclusão – Valores Duplicados no Excel
Nesta aula foi possível aprender uma forma de evitar que dados repetidos sejam incluídos em uma lista, desta forma é possível evitar problemas de cálculos entre outros problemas por conta de uma informação duplicada.
Você é do Rio de Janeiro ou de Niterói? Já conhece nosso curso de excel presencial que oferecemos nessas localidades? Clique para saber mais!
Hashtag Treinamentos
Para acessar outras publicações de Excel Básico, clique aqui!
