Neste artigo você aprenderá como identificar dados repetidos no Excel, como encontrar os dados repetidos de várias formas e com download da planilha de exemplo gratuita.
1. Introdução
Detectar dados repetidos pode ter várias utilidades, especialmente para conferência de resultados, análises e relatórios. Ao passo que erros de cálculos podem ser originados por dados não íntegros, duplicidades podem resultar em divergências indesejadas.
Imagine uma soma em que algumas dimensões se repetem. Com certeza o total estará errado, podendo provocar distorções nas demonstrações e análises, o que pode acarretar algo mais prejudicial.
Tomemos como base os dados abaixo:
Pergunta: qual UF está repetida? A resposta certamente não será encontrada visualmente. Pelo menos não tão rapidamente. Além do mais, o total está superestimado, visto que há UF com o valor repetido, influenciando a soma.
Encontrar com facilidade dados repetidos no Excel é o objetivo elencado neste artigo. A seguir serão apresentados alguns métodos úteis.
2. Métodos para evidenciar dados repetidos
2.1 Usando formatação condicional
Este método é de breve execução. Inicialmente devemos selecionar o intervalo de dados em que desejamos evidenciar as repetições (em nosso caso de B3 a B29).
Em seguida, acessamos a guia Página Inicial e o grupo Estilos. Encontramos Formatação Condicional > Regras de Realce de Células > Valores Duplicados.
Em seguida, uma formatação condicional padrão será exibidas nos dados repetidos.
A imagem anterior já denuncia com formatação de preenchimento vermelho claro e texto escuro que a UF repetida é PA. Necessariamente não precisamos acatar esta formatação. Podemos trocar as cores de preenchimento, fundo, inserir bordas etc. Inclusive, como já sabemos agora a UF repetida, podemos cancelar (vide imagem) a formatação condicional, visto que ela ainda é uma prévia (não confirmada em Ok).
Visto que podem haver vários UFs repetidas e, além disso, a repetição de uma mesma UF pode ocorrer mais de 2 vezes, após usar a formatação condicional, o autofiltro facilita a obtenção separada destes dados, através do filtro por cor (de fundo ou fonte).
2.2 Usando fórmulas
a) Com o auxílio da classificação de dados
P.S: das alternativas aqui apresentadas, apenas a infracitada depende da classificação.
Se os dados estiverem classificados a inspeção visual ficará facilitada porque os dados repetidos ficarão agrupados, conforme a imagem seguinte.
Note que todas as ocorrências da UF PA estão disponibilizadas em sequência. Contudo, nem sempre a inspeção visual permite agilidade da detecção, especialmente para dados volumosos. Em complemento, após a classificação, podemos criar uma coluna auxiliar nos dados para verificar se itens consecutivos são iguais.
A partir então do 2º registro, criamos uma fórmula que compara a UF deste registro com a UF do registro anterior. Em nosso caso, comparamos a UF de B4 (AL) com a UF de B3 (AC).
Esta fórmula deixa uma pergunta no ar: “A UF de B3 é a mesma de B4?”.
A resposta foi FALSO, o que indica que não são iguais, conforme esperado. Agora tudo que precisamos é replicar a fórmula criada na coluna auxiliar, até o último registro.
Realizado o descrito, teremos o resultado booleano VERDADEIRO em uma das ocorrências de repetição, indicando o que desejamos.
A imagem seguinte mostra casos com repetições múltiplas, com vários resultados VERDADEIRO.
Conforme a imagem, temos as UFs ES e PA com 2 ocorrências além de SC com 3 ocorrências.
b) Sem o auxílio da classificação de dados
A repetição é definida pela dupla ocorrência ou até mais. Ou seja: se pudermos contar os itens, caso a contagem resulte em 2 ou mais, teremos dados repetidos.
A solução aqui usa a simples função CONT.SE: ela conta no intervalo de todas as UFs o número de ocorrência de cada UF. Usamos também uma coluna auxiliar, escrevendo no 1º registro uma fórmula que conta (ao longo de todas as UFs) a UF lá presente.
A fórmula inserida para contar a 1ª UF foi: =CONT.SE($B$3:$B$32;B3). Para o 1º registro, cuja UF é TO, a contagem resultou em 1.
Ao replicarmos a fórmula ao longo da coluna auxiliar criada, teremos as contagens das UFs de cada linha.
Nitidamente as contagens acima de 1 representam os dados desejados, em que há repetição. Para obter separadamente apenas os itens com repetição, podemos usar o autofiltro, selecionando valores acima de 1 na coluna auxiliar:
P.S: essa contagem pode ser igualmente obtida no Power Query com a agrupamento e realizando contagem.
Download Planilha Dados Repetidos no Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
