Como detectar valores atípicos e erros de digitação em uma planilha

Basta um zero a mais para um pedido de 150 virar 1500 e o total do mês disparar. Erros de digitação raramente saltam aos olhos em uma planilha com centenas de linhas, mas distorcem médias, gráficos e decisões.

Aqui você vai aprender a localizar valores suspeitos com métodos simples e confiáveis: uma primeira olhada com funções básicas, o método do intervalo interquartil, o escore z, a formatação condicional e algumas fórmulas para erros específicos. No final, você verá como evitar que os erros entrem na planilha.

Atípico nem sempre significa erro

Um valor atípico é aquele que fica muito longe dos demais. Pode ser um erro, mas também um dado real: um cliente que fez um pedido enorme ou um mês com vendas excepcionais. Por isso a regra de ouro é marcar, revisar e só então decidir, nunca apagar automaticamente.

Os erros de digitação mais comuns são:

• Zeros a mais ou a menos: 1500 em vez de 150.
• Separador decimal errado: 1250 em vez de 12,50.
• Dígitos trocados: 516 em vez de 156.
• Unidades misturadas: gramas em uma coluna de quilos.
• Valores impossíveis: quantidades negativas, idades de 250 anos ou datas futuras em um registro de vendas.
• Texto em colunas numéricas: “n/d”, “pendente” ou um traço.
• Registros duplicados: a mesma linha digitada duas vezes.

Primeira olhada: mínimo, máximo e mediana

Antes de aplicar métodos mais refinados, calcule três valores na coluna (no exemplo, valores em B2:B200):

=MÍNIMO(B2:B200)

=MÁXIMO(B2:B200)

=MED(B2:B200)

Se a mediana é 140 e o máximo é 14000, você já sabe por onde começar. Compare também a mediana com MÉDIA: quando a média fica bem acima da mediana, alguns poucos valores extremos estão puxando-a para cima.

Outra forma rápida é classificar a coluna do maior para o menor em Dados › Classificar e revisar as primeiras e últimas linhas. Se não quiser perder a ordem original, adicione antes uma coluna com números sequenciais para poder restaurá-la.

O método do intervalo interquartil

É o método mais robusto para planilhas, porque não se deixa influenciar pelos próprios valores extremos. Ele divide os dados em quatro partes e considera atípico o que fica muito longe da metade central.

1. Calcule o primeiro quartil em F1: =QUARTIL.INC($B$2:$B$200;1)
2. Calcule o terceiro quartil em F2: =QUARTIL.INC($B$2:$B$200;3)
3. Calcule o intervalo interquartil em F3: =F2-F1
4. Limite inferior em F4: =F1-1,5*F3
5. Limite superior em F5: =F2+1,5*F3

Depois, em uma coluna auxiliar, marque cada linha que ficar fora desses limites:

=OU(B2<$F$4;B2>$F$5)

Com dados de exemplo, fica assim:

O fator 1,5 é o mais usado. Se quiser marcar só os casos mais extremos, use 3.

O escore z

O escore z indica a quantos desvios padrão cada valor está da média. Em C2:

=(B2-MÉDIA($B$2:$B$200))/DESVPAD.A($B$2:$B$200)

E para marcar os valores distantes:

=ABS(C2)>3

Um valor absoluto maior que 3 é muito raro se os dados seguem uma distribuição normal. Considere duas limitações: os próprios atípicos inflam o desvio padrão e podem se esconder, e com poucos dados (menos de 30, por exemplo) o método é pouco confiável. Nesses casos, o intervalo interquartil funciona melhor.

Destacar valores suspeitos com formatação condicional

Se preferir ver os atípicos sem colunas auxiliares:

1. Selecione B2:B200.
2. Vá em Página Inicial › Formatação Condicional › Nova Regra.
3. Escolha Usar uma fórmula para determinar quais células devem ser formatadas.
4. Digite =OU($B2<$F$4;$B2>$F$5) e escolha uma cor de preenchimento.

Para uma análise rápida sem cálculos, as Regras de Primeiros/Últimos do mesmo menu destacam, por exemplo, os 10% maiores valores. É útil como primeira exploração, embora sempre vá marcar alguma coisa, havendo erros ou não.

Fórmulas para erros de digitação específicos

Texto em uma coluna numérica

A diferença entre as células não vazias e as que contêm números revela quantos textos se infiltraram:

=CONT.VALORES(B2:B200)-CONT.NÚM(B2:B200)

Valores impossíveis

Conte quantidades negativas e datas posteriores a hoje (com as datas na coluna D):

=CONT.SE(B2:B200;"<0")

=CONT.SE(D2:D200;">"&HOJE())

Um zero ou uma vírgula deslocados

Divida cada valor pela mediana. Resultados próximos de 10 ou de 0,1 costumam indicar um zero a mais ou uma casa decimal fora do lugar:

=B2/MED($B$2:$B$200)

Registros repetidos

Para saber se um número de pedido aparece mais de uma vez:

=CONT.SE($A$2:$A$200;A2)>1

Duplicatas e comparação de versões

Linhas digitadas duas vezes estão entre os erros de digitação mais frequentes, e com milhares de registros revisá-las à mão não é prático. Nossa ferramenta gratuita para remover duplicatas elimina as linhas repetidas de um arquivo Excel, CSV ou ODS em segundos. E quando terminar as correções, você pode comparar o arquivo original com o corrigido para revisar exatamente quais células alterou.

Como evitar erros com a validação de dados

A melhor forma de reduzir erros é impedir que entrem. Com a validação de dados, o Excel rejeita valores fora do intervalo que você definir:

1. Selecione a coluna onde as quantidades são digitadas.
2. Vá em Dados › Validação de Dados.
3. Em Permitir, escolha Número inteiro ou Decimal e defina um mínimo e um máximo razoáveis, por exemplo entre 1 e 500.
4. Na guia Alerta de Erro, escreva uma mensagem clara para quem preenche a planilha.

Para campos com valores fixos, como categorias ou status, use a opção Lista: você evita variantes como “Pendente”, “pendente” e “Pend.”. Lembre-se de que a validação não confere os dados que já estavam na planilha nem os colados por cima das células.

Conclusão

Detectar erros de digitação é questão de método: comece por mínimo, máximo e mediana, marque os atípicos com o intervalo interquartil ou o escore z, destaque-os com formatação condicional e use fórmulas específicas para textos, valores impossíveis e duplicatas. Revise cada caso antes de corrigir e, daqui para frente, proteja a entrada de dados com validação.

Perguntas frequentes