Blog

Postado em em 3 de abril de 2020

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!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

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.

Base de dados
Base de dados

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.

Ferramenta de Validação de Dados - Valores duplicados no excel
Ferramenta de Validação de Dados

Ao selecionar essa opção uma janela será aberta para podermos escolher como vamos restringir esses dados.

Janela de Validação de Dados
Janela de Validação de 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.

Fórmula personalizada para evitar dados duplicados
Fórmula personalizada para evitar dados duplicados

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.

Erro ao inserir um dado duplicado - Valores duplicados no excel
Erro ao inserir um dado duplicado

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.

Alteração da mensagem de erro
Alteração da mensagem 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.

Nova mensagem de erro modificada pelo usuário - Valores duplicados no excel
Nova mensagem de erro modificada pelo usuário

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…

duplicados
duplicados

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)

CONT.SES
CONT.SES

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”.

remover duplicatas
remover duplicatas

Dessa forma todas as duplicatas são excluídas imediatamente.

Como excluir valores repetidos em 2 colunas?

valores repetidos em duas colunas
valores repetidos em duas colunas

Para fazer isso primeiro você deve concatenar (juntar) os valores em uma coluna que vai servir de apoio.

=CONCAT(A2:B2)

Valores duplicados no excel
concatenar

Feito isso, arraste a função para todas as células abaixo, agora temos em uma única coluna produto e marca.

coluna com valores concatenados
coluna com valores concatenados

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)

Valores duplicados no excel
formatação condicional e CONT.SES

Também podemos usar o ícone de formatação condicional, dessa vez selecionando as colunas A e B.

Valores duplicados no excel
remover duplicatas

Deixe marcado ambas as colunas para excluir apenas os produtos que se repetem em ambas.

selecionar ambas as colunas
selecionar ambas as colunas

Você pode fazer esse mesmo tratamento de valores duplicados no excel em 3, 4 ou mais colunas.

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!


Quer aprender mais sobre Excel com um minicurso básico gratuito?