Limpeza de dados

Uma tabela que parece exata ainda pode dar a resposta errada. A limpeza começa pelo significado de seus registros: o que aconteceu, quem ou o que cada linha descreve e quais campos a análise precisa.

O que significa integridade dos dados

Integridade dos dados significa exatidão, completude, consistência e confiabilidade durante todo o ciclo de vida dos dados. Os valores devem corresponder aos fatos que afirmam registrar. As informações necessárias devem estar presentes, e o mesmo fato deve manter o mesmo significado ao passar de um sistema para outro.

A integridade pode ser comprometida durante a entrada, o armazenamento, a transferência ou a análise. Alguém pode digitar uma quantidade incorretamente. Cópias em dois sistemas podem deixar de coincidir, um problema chamado replicação de dados. Uma transferência interrompida pode carregar metade de um arquivo. Ordenar uma única coluna pode associar cada valor ao pedido errado. Falhas de sistema e alterações não autorizadas também podem danificar registros.

Reconheça o problema antes de corrigi-lo

Dados sujos são incompletos, incorretos, inconsistentes ou irrelevantes para a pergunta. Problemas diferentes exigem ações diferentes.

Um formato organizado não comprova a exatidão. Uma data de entrega pode parecer uma data, mas ainda ser anterior à data do pedido. Um cliente de teste pode ter um registro completo, mas ser irrelevante para as vendas a clientes reais.

Acompanhe o efeito sobre uma resposta

Suponha que o pedido 1003, no valor de 250,00, apareça duas vezes em uma exportação. Contar as linhas adiciona um pedido extra, e somar os valores acrescenta 250,00 indevidamente. As fórmulas podem funcionar perfeitamente enquanto os dados de entrada produzem um resultado falso. Variantes de texto criam outro problema: um gráfico pode dividir as vendas de Lisboa entre rótulos de cidade separados.

Dados limpos são adequados à análise realizada. A ausência de um nome do meio não afeta as vendas por região. A ausência do valor de um pedido afeta o total de vendas. Avalie a completude em relação aos campos necessários para a pergunta e registre as lacunas que permanecerem.

Proteja o ponto de partida

Mantenha uma cópia bruta intacta antes de fazer alterações. Use outro arquivo, uma aba separada ou a tabela original do banco de dados e limpe uma cópia de trabalho. Uma ferramenta como Remover Duplicatas exclui linhas do intervalo selecionado; a cópia bruta oferece uma referência para comparação e uma forma de refazer uma decisão.

Percorra cinco etapas: examine, decida, aplique, documente e verifique. Registre um problema antes de escolher seu tratamento. Registre cada tratamento ao aplicá-lo e depois verifique o resultado em relação aos dados brutos. Dados internos precisam da mesma inspeção que um arquivo externo: importações repetidas e mudanças no significado dos campos também ocorrem dentro das organizações. Comunique problemas recorrentes de entrada ou exportação ao responsável pelos dados para melhorar a próxima entrega.

O perfilamento de dados é uma inspeção sistemática de um conjunto de dados antes de alterá-lo. Ele fornece uma linha de base e uma lista de problemas: o tamanho da tabela, o conteúdo de cada coluna, lacunas, chaves repetidas e valores que precisam de investigação.

Comece pela estrutura da tabela

Identifique o que uma linha significa, como um pedido ou um cliente. Verifique se há uma linha de cabeçalho e um tipo de informação por coluna. Encontre a chave, a coluna ou combinação de colunas que deve identificar um registro. Um ID de pedido repetido precisa de investigação; nomes de cidades repetidos são esperados.

Registre a contagem inicial de linhas. Depois, examine o tipo esperado, a obrigatoriedade e os valores permitidos de cada coluna. Um dicionário de dados ou o responsável pelos dados fornece esses significados. Os valores mínimo e máximo podem revelar uma data fora do período do relatório ou um marcador como 999 em uma coluna de idade.

Examine uma planilha eletrônica

Ordene e filtre a tabela, mantendo as linhas inteiras juntas. Ordene em ordem crescente e decrescente para encontrar células em branco, textos inesperados e valores extremos. As listas suspensas de filtro revelam grafias de categorias. Os filtros do Excel ignoram maiúsculas e minúsculas, então uma única categoria exibida pode ocultar diferenças de capitalização; use EXACT quando for necessário verificar a própria capitalização. A formatação condicional pode destacar chaves repetidas e valores fora de um intervalo.

Para as linhas 2 a 501, verificações úteis incluem:

• =CONT.VALORES(B2:B501) conta células não vazias.
• =CONTAR.VAZIO(B2:B501) conta células em branco, incluindo fórmulas que retornam texto vazio.
• =CONT.NÚM(B2:B501) conta células numéricas.
• =MÍNIMO(B2:B501) e =MÁXIMO(B2:B501) mostram o intervalo numérico.
• =CONT.SE(C2:C501;"Lisbon") conta entradas correspondentes, ignorando maiúsculas e minúsculas.

Se a contagem de células numéricas de uma coluna de valores for menor que a contagem de células não vazias, examine a diferença. Algumas entradas podem ser números em texto; outras podem ser rótulos como “N/A”. Uma célula que contém apenas um espaço também precisa de atenção, embora pareça vazia.

Examine com SQL

Estas consultas leem os dados sem alterá-los:

SELECT COUNT(*) AS rows_total,
       COUNT(amount) AS amounts_present,
       COUNT(*) - COUNT(amount) AS amounts_missing,
       MIN(order_date) AS first_date,
       MAX(order_date) AS last_date
FROM orders;

COUNT(*) conta linhas. COUNT(amount) conta valores que não são NULL. A diferença conta valores ausentes representados por NULL, mas não detecta marcadores em texto.

Para examinar valores de categorias e sua frequência:

SELECT city, COUNT(*) AS n
FROM orders
GROUP BY city
ORDER BY city;

GROUP BY reúne valores iguais; COUNT(*) conta as linhas de cada grupo. NULL forma seu próprio grupo. A igualdade de textos segue as regras de comparação e ordenação de texto do banco de dados. PostgreSQL e SQLite normalmente separam variantes de maiúsculas e minúsculas; configurações comuns do MySQL as agrupam.

Transforme verificações em uma lista de problemas

Para seis valores 40, NULL, 25, 60, NULL e 35, a contagem de linhas é 6 e a contagem de valores presentes é 4: dois valores precisam de atenção. Para as cidades “Lisbon”, “lisbon ”, “Porto”, NULL, “LISBON” e “Porto”, a comparação exata de texto encontra quatro grafias que não são NULL, embora apenas duas cidades estejam representadas.

Registre ambos os resultados, junto com a coluna afetada e a contagem. Examine valores frequentes e extremos em busca de marcadores ocultos, registros de teste e datas como “1900-01-01”. Fazer primeiro o perfilamento de toda a tabela também revela dependências: remover espaços do texto pode alterar a contagem de duplicatas, então a remoção de duplicatas deve esperar até que os campos de comparação sejam padronizados.

A validação de dados verifica se as entradas seguem regras para dados aceitáveis. Uma entrada pode estar presente e ser exibida de forma organizada, mas violar uma regra. Uma quantidade de -3 continua inválida quando as quantidades de pedido devem ser positivas.

Escolha regras a partir do significado dos dados

Leia o dicionário de dados ou pergunte ao responsável pelos dados. Uma reserva de hotel para uso diurno pode permitir zero noites; outro sistema de reservas pode exigir pelo menos uma. Registre o limite real e se o campo é obrigatório.

Uma verificação de formato testa um padrão, não se o fato representado é verdadeiro. Uma verificação básica de e-mail que procura um “@” e texto de cada lado não confirma que uma caixa de correio existe.

Sinalize falhas em uma planilha eletrônica

Coloque um teste em uma coluna auxiliar e preencha-o para baixo. Para uma porcentagem numérica obrigatória em B2, =E(ÉNÚM(B2);B2>=0;B2<=100) verifica tanto o tipo quanto o intervalo. O teste de tipo importa porque uma célula em branco pode se comportar como zero em uma comparação.

Com datas reais em C e D, =SE(D2<C2;"check";"ok") sinaliza uma entrega anterior ao pedido. Verifique separadamente se as datas obrigatórias estão em branco. Para IDs na coluna A, =CONT.SE($A$2:$A$501;A2)>1 sinaliza valores repetidos. Filtre os resultados auxiliares para ver todas as falhas.

Liste falhas com SQL

SELECT * FROM orders
WHERE quantity <= 0 OR quantity IS NULL;

Isso encontra quantidades não positivas e ausentes. Para verificar status:

SELECT * FROM orders
WHERE status NOT IN ('open', 'shipped', 'returned')
   OR status IS NULL;

A verificação explícita de NULL importa. NOT IN sozinho não retorna uma linha com status NULL, pois sua comparação é desconhecida. Uma verificação entre campos como delivery_date < order_date também exige verificações separadas de preenchimento para datas obrigatórias.

Evite novos erros e resolva os antigos

A Validação de Dados do Excel e a Validação de dados do Google Sheets podem oferecer uma lista suspensa, restringir um intervalo numérico ou alertar sobre uma entrada. Tipos e restrições de bancos de dados como NOT NULL e UNIQUE impõem regras relacionadas. Adicionar uma regra não corrige registros antigos; examine-os separadamente. O recurso Circular Dados Inválidos do Excel pode marcar falhas existentes.

Considere as idades de uma pesquisa com adultos: 34, 0, 41, 230 e “quarenta”, com um intervalo de números inteiros de 18 a 120. “quarenta” falha na verificação de tipo; 0 e 230 falham na verificação de intervalo. A palavra tem o significado numérico claro de 40. O valor pretendido de 230 é incerto, então não adivinhe entre 23 e 30. Recupere um valor confiável, marque-o como ausente ou exclua o registro e registre a decisão.

Um valor incomum é diferente de um valor inválido. Um pedido de 500 unidades pode ser real. Valide-o em relação às regras de pedidos e ao registro original antes de decidir que é um erro.

Duas linhas semelhantes não são necessariamente um registro inserido duas vezes. Uma duplicata descreve a mesma entidade ou o mesmo evento do mundo real que outra linha. Comece decidindo o que identifica essa entidade ou esse evento.

Defina a chave e as regras de comparação

Um ID de pedido pode identificar um pedido. Um nome sozinho geralmente é uma chave ruim para clientes, pois pessoas diferentes podem compartilhá-lo. Até duas compras idênticas no mesmo dia podem ser eventos reais separados. Verifique o significado da linha e a chave definida pelo responsável pelos dados antes de excluir linhas semelhantes.

Registros duplicados exatos coincidem em todas as colunas. Registros quase duplicados referem-se à mesma coisa, mas têm pequenas diferenças, como um espaço extra ou um número de telefone alterado. Limpe os campos de comparação antes de procurar. Se o e-mail for a chave acordada, padronize espaços e maiúsculas e minúsculas em uma coluna auxiliar.

Para os cinco e-mails “ana@example.com”, “ben@example.com”, “Ana@Example.com ”, “chen@example.com” e “ben@example.com”, a comparação exata dos dados brutos encontra a entrada repetida de Ben. Remover espaços das extremidades e converter para minúsculas também revela a entrada repetida de Ana. Três chaves de clientes permanecem após a remoção de duas cópias extras.

Sinalize linhas antes de removê-las

Para chaves limpas em B2:B501:

• =CONT.SE($B$2:$B$501;B2)>1 sinaliza todas as linhas cuja chave se repete.
• =CONT.SE($B$2:B2;B2)>1 sinaliza apenas a segunda ocorrência e as seguintes.

O segundo intervalo cresce à medida que você preenche para baixo, então a primeira ocorrência tem contagem de um. CONT.SE ignora maiúsculas e minúsculas; remova primeiro os espaços extras. Examine os grupos sinalizados em busca de informações conflitantes antes de aplicar uma regra de manutenção.

Escolha o que será mantido

Remover Duplicatas do Excel e Remover duplicatas do Google Sheets comparam as colunas selecionadas. Selecionar apenas o e-mail pode descartar uma linha com outro número de telefone. Selecionar todas as colunas mantém esse conflito porque as linhas completas são diferentes. Ambas as ferramentas usam suas próprias regras de correspondência, incluindo comparações que ignoram maiúsculas e minúsculas, então não são adequadas para impor distinções de capitalização caractere por caractere sem uma verificação adicional.

O Excel mantém a primeira ocorrência. Se o registro mais recente deve ser mantido, ordene primeiro as linhas completas pela data de atualização, com a mais recente primeiro. Resolva explicitamente registros empatados ou conflitantes. Uma regra documentada pode manter o registro confirmado mais recentemente ou unir campos verificados. Registre tanto as colunas escolhidas quanto o número removido.

=ÚNICO(A2:C501) retorna linhas distintas em um novo intervalo nas versões atuais do Excel e no Google Sheets. Ela preserva a origem e é útil para visualizar um resultado previamente.

Encontre chaves repetidas com SQL

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY cria um grupo para cada e-mail; HAVING mantém grupos cuja contagem é maior que um. WHERE filtra linhas individuais antes do agrupamento. Execute a verificação em chaves padronizadas, usando uma tabela limpa ou agrupando pela expressão de limpeza.

SELECT DISTINCT email, name FROM customers; retorna cada combinação de e-mail e nome uma vez. Isso não garante e-mails únicos: duas linhas com um e-mail e nomes diferentes permanecem. Verifique a chave acordada após a deduplicação.

Importações repetidas e exportações sobrepostas frequentemente criam duplicatas. Registre a causa com o responsável pelos dados para que uma correção na tabela de hoje também melhore a próxima exportação.

Uma lacuna pode estar visível como um campo em branco ou oculta por trás de um valor que parece plausível. Descubra como a fonte registra informações ausentes antes de contá-las. Um zero registrado pode ser real, enquanto “unknown” pode ser um marcador.

Diferencie campos em branco, texto e NULL

Em uma planilha eletrônica, uma célula vazia, uma célula que contém um espaço e uma fórmula que retorna "" podem parecer iguais. ÉCÉL.VAZIA é verdadeiro apenas para uma célula realmente vazia. Um espaço é texto. CONTAR.VAZIO também conta fórmulas que retornam texto vazio, mas não conta uma célula que contém um espaço. Para espaços comuns, =ARRUMAR(B2)="" detecta tanto entradas vazias quanto entradas compostas apenas de espaços.

SOMA, MÉDIA e CONT.NÚM em planilhas eletrônicas ignoram células vazias e texto em um intervalo referenciado. Elas incluem o zero numérico. Para os valores 48, 52, um campo em branco, 45, um campo em branco, 55, 50 e 46, os valores conhecidos somam 296. MÉDIA divide por seis, resultando em cerca de 49,33. Substituir os campos em branco por zero muda o denominador para oito e a média para 37.

Em SQL, NULL marca um valor desconhecido ou ausente. Zero é um número conhecido. A cadeia de caracteres vazia '' é texto sem caracteres em PostgreSQL, MySQL, SQL Server e SQLite. Campos em branco importados podem aparecer em qualquer uma das duas formas, então verifique a representação armazenada:

SELECT * FROM orders
WHERE city IS NULL OR TRIM(city) = '';

Use IS NULL e IS NOT NULL. amount = NULL não encontra valores ausentes: a comparação com NULL produz um resultado desconhecido, que WHERE não mantém.

Conheça o denominador

COUNT(*) conta todas as linhas. COUNT(amount), SUM, AVG, MIN e MAX ignoram valores NULL. Para 40, NULL, 25, 60, NULL e 35, há seis linhas, mas quatro valores conhecidos. SUM é 160 e AVG é 40 porque divide 160 por quatro.

Essas funções não conseguem identificar um marcador numérico como -1 ou 999. Confirme os significados dos marcadores com o responsável pelos dados e depois conte-os separadamente ou padronize-os como ausentes. Examine as frequências de códigos incomuns, além das contagens de células em branco.

Procure padrões nas lacunas

Conte as entradas ausentes por coluna e como proporção das linhas. Dois valores ausentes em oito correspondem a 25%. Depois, examine se as lacunas se concentram por loja, fonte ou mês. Um campo ausente restrito a uma fonte de exportação pode apontar para um problema de importação que pode ser corrigido.

O motivo da ausência importa:

• Ausência completamente ao acaso: um evento sem relação com o valor, como uma falha aleatória ao salvar, causa a lacuna.
• Ausência ao acaso: a chance de uma lacuna depende de informações registradas, como clientes mais velhos deixarem de preencher um campo on-line opcional com mais frequência.
• Ausência não ao acaso: a chance depende do próprio valor ausente, como pessoas de renda muito alta deixarem a renda em branco.

No caso da renda, a média entre as pessoas que responderam pode ser menor que a média de todas as pessoas. Preencher lacunas com um número não elimina esse problema de seleção. Informe o que está ausente e quem os valores restantes descrevem antes de interpretar uma média.

Valores ausentes exigem uma decisão ligada à pergunta que está sendo respondida. Um cliente sem idade registrada ainda pode contribuir para o total de vendas. Um pedido sem valor não pode contribuir com um valor conhecido para a receita.

Escolha uma ação

Tente recuperar primeiro. Consulte uma nota fiscal, o formulário original ou o sistema oficial de registro. Um total de linha calculado a partir de uma quantidade e de um preço unitário verificados é uma informação recuperada. Um valor estimado a partir de outros clientes é uma estimativa, chamada de imputação.

Preserve o significado de um número

Preencher uma categoria com “Desconhecido” mantém a linha nas contagens e deixa a lacuna visível. Preencher um campo numérico com zero só se justifica quando a ausência realmente significa zero. Uma contagem de devoluções ausente pode significar zero se o processo de origem confirmar essa convenção; um valor de venda desconhecido não tem esse significado.

Para os valores conhecidos 48, 52, 45, 55, 50 e 46, a soma é 296 e a média é cerca de 49,33. Com dois campos em branco convertidos em zero, a soma continua 296, mas a média cai para 37. As linhas adicionais passam a contar como observações de zero.

O preenchimento com média ou mediana também altera os dados. A mediana dos seis valores conhecidos é 49. Preencher ambas as lacunas com 49 adiciona 98, fazendo a média das oito entradas ser 49,25. Esses dois valores adicionados não foram observados. Valores centrais repetidos fazem os dados parecerem menos dispersos, então mantenha um sinalizador que os identifique. Para valores muito assimétricos, como rendas, a mediana frequentemente descreve um valor típico melhor que a média.

Aplique a regra de forma visível

Em SQL, COALESCE(city, 'Unknown') retorna o primeiro argumento que não é NULL. Ele preenche cidades NULL, mas deixa uma cadeia de caracteres vazia inalterada. NULLIF(city, 'N/A') transforma exatamente esse marcador em NULL. Combinar padronização e preenchimento torna o tratamento explícito:

COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city

Uma coluna auxiliar de categoria em uma planilha eletrônica pode usar =SE(ARRUMAR(B2)="";"Unknown";B2). Um sinalizador separado pode usar =ARRUMAR(B2)="" para que a condição original de ausência permaneça visível após o preenchimento.

Ajuste as exclusões à análise

Para a receita total de 1.000 pedidos com três valores ausentes, procure primeiro as notas fiscais. Se a recuperação falhar, informe que o total exclui 3 de 1.000 pedidos, ou 0,3%. Não descarte uma linha porque um campo não usado está em branco.

Se 400 de 1.000 idades de clientes estão ausentes, preenchê-las com a média transformaria 40% da coluna em estimativas. Uma análise das 600 idades conhecidas precisa de uma declaração clara sobre sua cobertura. Verifique por que essas idades estão ausentes: excluir linhas também pode distorcer os resultados quando esses clientes diferem dos demais. Mantenha o tratamento consistente e documente qualquer alteração posterior.

A limpeza de texto torna comparáveis valores com o mesmo significado. Comece listando os valores distintos e suas contagens. Isso revela se uma coluna precisa de ajustes de espaços, padronização de maiúsculas e minúsculas ou mapeamento de várias grafias para um rótulo.

Remova espaços e caracteres ocultos

No Excel e no Google Sheets, =ARRUMAR(A2) remove espaços nas duas extremidades e reduz espaços internos repetidos a um. Ela transforma “ Ana Silva ” em “Ana Silva”. =TIRAR(A2) remove caracteres não imprimíveis, como quebras de linha.

Um espaço não separável copiado de uma página da web pode permanecer após TRIM. Primeiro, substitua o caractere 160 por um espaço comum:

=ARRUMAR(SUBSTITUIR(A2;CARACT(160);" "))

Leia funções aninhadas de dentro para fora: SUBSTITUIR é executada primeiro e TRIM atua sobre seu resultado. Examine exemplos limpos, pois remover uma quebra de linha pode unir palavras que deveriam permanecer separadas.

Em SQL, TRIM(city) remove espaços nas extremidades, mas deixa espaços internos repetidos. Portanto, TRIM(' Ana Silva ') mantém três espaços entre os nomes. LTRIM altera a extremidade esquerda e RTRIM, a direita. Escolha uma expressão de limpeza adequada aos caracteres encontrados.

Padronize maiúsculas e minúsculas de forma deliberada

UPPER e LOWER funcionam nas duas ferramentas. Letras minúsculas podem ser uma convenção útil para e-mails; maiúsculas podem ser adequadas a códigos de país. PROPER em uma planilha eletrônica coloca iniciais de palavras em maiúsculas, mas não determina a grafia correta de um nome: “mcdonald” torna-se “Mcdonald” e “PT123” torna-se “Pt123”. Revise nomes e mantenha códigos na capitalização pretendida.

As comparações de texto seguem as configurações da ferramenta. CONT.SE, PROCV e filtros do Excel ignoram maiúsculas e minúsculas. O PostgreSQL normalmente trata “Lisbon” e “LISBON” como valores de texto diferentes. =EXATO(A2;B2) em uma planilha verifica textos idênticos, incluindo maiúsculas e minúsculas. Padronize explicitamente quando a mesma categoria precisar funcionar de modo consistente entre ferramentas, preservando distinções significativas de capitalização em identificadores que distinguem maiúsculas e minúsculas.

Mapeie valores inteiros para uma categoria

Remover espaços das extremidades e alterar maiúsculas e minúsculas não corrige grafias diferentes. Uma tabela de mapeamento pode associar cada variante a um rótulo padrão. Busque o valor limpo completo com correspondência exata e examine os valores sem correspondência.

Em SQL, CASE pode expressar o mesmo mapeamento:

CASE
  WHEN UPPER(TRIM(country)) IN
       ('NL', 'NETHERLANDS', 'THE NETHERLANDS', 'HOLLAND')
    THEN 'Netherlands'
  ELSE TRIM(country)
END AS country

ELSE preserva os valores não mapeados para revisão. Conte as categorias restantes após o mapeamento; não presuma que as primeiras linhas representam todas as grafias.

Evite danos causados por substituições

SUBSTITUIR, Localizar e Substituir e REPLACE em SQL podem substituir texto dentro de um valor mais longo. Substituir “NY” em todos os lugares altera “Store NYC, NY” para “Store New YorkC, New York”. Para rótulos de categorias, busque uma correspondência com a célula inteira ou use uma tabela de mapeamento. Para endereços e nomes, examine o alcance pretendido de cada substituição.

Para as cidades “Lisbon”, “lisbon ”, “Porto”, um valor ausente, “LISBON” e “Porto”, UPPER(TRIM(city)) produz LISBON três vezes e PORTO duas vezes, com uma lacuna. Analise a coluna limpa e depois use-a para verificações de correspondência e duplicatas. Manter a coluna auxiliar enquanto agrupa a cidade bruta preservaria o problema original.

Um valor de texto combinado pode ocultar informações necessárias para ordenar ou encontrar correspondências. Coloque cada parte necessária em sua própria coluna, preservando o original até que a divisão seja verificada.

Divida em um separador confiável

Um delimitador marca o limite entre partes. Uma vírgula em “Silva, Ana” separa o sobrenome do nome. Texto para Colunas do Excel e Dividir texto em colunas do Google Sheets permitem escolher esse separador. Insira colunas vazias à direita antes de dividir para que o resultado não sobrescreva outros campos.

O Google Sheets pode usar =SPLIT(A2;","); versões atuais do Excel podem usar =DIVIDIRTEXTO(A2;","). Remova os espaços das extremidades das partes retornadas se houver um espaço após a vírgula. Verifique linhas sem delimitador ou com mais de um delimitador antes de aplicar uma regra à coluna inteira.

Não divida todo nome em cada espaço. “Ana Maria Silva” tem mais partes que um nome de duas palavras, e um espaço sozinho não determina quais palavras pertencem ao nome ou ao sobrenome. Use um formato de origem definido ou mantenha um campo de nome completo quando a estrutura for incerta.

Extraia por posição quando o formato for fixo

Para o código de produto “PT-LIS-0042”, o país ocupa as posições 1–2, a localização as posições 4–6 e o código final as posições 8–11.

A contagem de posições começa em um. Preserve o código extraído como texto para manter seus zeros iniciais. Posições fixas só funcionam quando o formato do campo é consistente.

Para texto de comprimento variável, localize o delimitador. Em “Silva, Ana”, PROCURAR(",";A2) retorna 6. =ESQUERDA(A2;PROCURAR(",";A2)-1) extrai os cinco caracteres antes da vírgula. A subtração exclui a própria vírgula. FIND distingue maiúsculas e minúsculas; SEARCH as ignora ao procurar letras.

Use a função SQL equivalente

SUBSTRING(code, 4, 3) extrai o código de localização do meio em PostgreSQL; alguns bancos de dados usam SUBSTR. As funções de comprimento de caracteres também variam: PostgreSQL e SQLite usam LENGTH, MySQL usa CHAR_LENGTH e SQL Server usa LEN, que ignora espaços no final. LENGTH em MySQL conta bytes, então pode diferir da contagem de caracteres para textos como “é”.

Uma verificação em PostgreSQL WHERE LENGTH(code) <> 11 encontra códigos com comprimento incorreto. Uma verificação de comprimento por si só não determina se as letras, os algarismos e os hífens estão nas posições corretas.

Combine partes com um separador claro

=A2&" "&B2 ou =CONCATENAR(A2;" ";B2) une dois campos de planilha eletrônica com um espaço. =UNIRTEXTO(" ";VERDADEIRO;A2:C2) une um intervalo ignorando células vazias. CONCAT no Excel aceita vários argumentos; CONCAT no Google Sheets aceita exatamente dois.

Em PostgreSQL, first_name || ' ' || last_name concatena texto. Com esse operador, uma parte NULL torna o resultado NULL. CONCAT trata NULL de forma diferente entre dialetos: MySQL propaga NULL, enquanto PostgreSQL e SQL Server o ignoram. Trate partes opcionais de forma deliberada com COALESCE e verifique os separadores resultantes.

Uma exibição consistente só é útil quando os valores têm o mesmo significado. Datas precisam de uma ordem conhecida, números precisam de separadores conhecidos e medidas precisam de uma unidade comum antes do início dos cálculos.

Estabeleça a convenção da fonte

O texto “03/04/2025” pode significar 4 de março ou 3 de abril. Uma data como “25/03/2025” estabelece a ordem com o dia primeiro para essa entrada, mas um arquivo combinado pode conter vários formatos. Confirme a convenção com o responsável pelos dados ou nas configurações de exportação e verifique se há fontes misturadas antes de converter entradas ambíguas.

Use a ordem de data ISO 8601, AAAA-MM-DD, para uma troca clara: “2025-03-04” indica 4 de março. Datas em texto ISO uniformes também são ordenadas cronologicamente. Use o relógio de 24 horas para horários, como “14:30”, e registre o fuso horário quando eventos abrangerem vários fusos.

Separe valores armazenados de sua exibição

Planilhas eletrônicas armazenam datas reais como números de série. No sistema de datas padrão do Excel e no Google Sheets, 1º de janeiro de 2025 é 45658 e 4 de março de 2025 é 45720. Formatar essa data numérica como AAAA-MM-DD altera sua aparência; não altera o dia registrado.

Texto que parece uma data é diferente. Aplicar um formato de data ao texto não cria um valor de data real. Verifique o tipo e converta o texto usando o formato de origem confirmado antes de formatá-lo. O alinhamento pode ser uma pista, mas verificações de tipo e o comportamento em cálculos fornecem evidências mais fortes, pois o alinhamento pode ser alterado manualmente.

A formatação numérica funciona da mesma forma. Um valor armazenado de 0,125 pode ser exibido como 0,13 com duas casas decimais, enquanto os cálculos ainda usam 0,125. =ARRED(A2;2) altera o resultado numérico para duas casas decimais. Mantenha a precisão completa durante a limpeza, a menos que a definição do campo exija um valor arredondado: três terços arredondados separadamente para 0,33 somam 0,99.

Leia corretamente os separadores numéricos

“1,234.5” e “1.234,5” expressam o mesmo número segundo convenções diferentes. Importar com a configuração regional errada pode alterar a magnitude ou deixar um valor como texto. Compare vários valores importados com a fonte, incluindo valores com separadores decimais e de agrupamento.

No Excel, =NUMBERVALUE("1.234,5",",",".") especifica a vírgula decimal e o ponto de agrupamento e retorna 1.234,5. Configure corretamente a região de importação em outras ferramentas. Não exclua todas as vírgulas sem verificar sua função: elas podem separar milhares ou marcar a parte decimal.

Converta unidades antes de combinar valores

Uma coluna de peso que contém “2.5 kg”, “750 g” e “1.2 kg” mistura rótulos e unidades. Extraia o número e a unidade, converta gramas em quilogramas e armazene 2,5; 0,75 e 1,2 em uma coluna numérica weight_kg.

Somar as partes numéricas brutas resulta em 753,7, que não tem uma unidade consistente. Mantenha uma unidade no cabeçalho da coluna e números nas células. Uma coluna monetária também precisa de uma moeda única para que totais ou médias tenham um significado comum; registre qualquer regra de conversão usada.

Após a conversão, examine as datas e medidas mínimas e máximas. Verifique datas ambíguas individuais em relação à fonte, mesmo quando estiverem dentro do período permitido: trocar mês e dia pode produzir uma data válida, mas incorreta.

Um tipo de dado informa ao software como tratar um valor. Quantidades numéricas permitem operações aritméticas, datas permitem ordenação e cálculo de diferenças entre datas, texto armazena rótulos e identificadores e campos booleanos contêm TRUE ou FALSE. Importações frequentemente transformam números ou datas pretendidos em texto.

Diagnostique o tipo antes de alterá-lo

Texto numérico pode ser alinhado à esquerda por padrão, mostrar um aviso do Excel ou ser ordenado com “10” antes de “9”. Verifique a coluna inteira com CONT.NÚM e CONT.VALORES. Em um intervalo referenciado que contém o número 120, o texto “80” e o número 45, SOMA fornece 165 e CONT.NÚM fornece 2. O total pretendido é 245, então o erro de tipo está afetando o cálculo.

Alterar o formato de exibição para Número não converte texto existente. Crie uma coluna auxiliar, faça a conversão nela e compare o resultado com a entrada bruta.

Converta texto em planilhas eletrônicas

=VALOR(A2) interpreta texto numérico usando a configuração regional da planilha. =DATA.VALOR(A2) interpreta datas em texto usando as convenções de data reconhecidas. Confirme primeiro os separadores e a ordem da data.

Para um valor conhecido com o dia primeiro, “04.03.2025”, construa uma data a partir de suas partes. Chamadas explícitas de VALOR tornam as entradas numéricas tanto no Excel quanto no Google Sheets:

=DATA(VALOR(DIREITA(A2;4));VALOR(EXT.TEXTO(A2;4;2));VALOR(ESQUERDA(A2;2)))

RIGHT retorna o texto do ano “2025”, MID retorna o texto do mês “03” e LEFT retorna o texto do dia “04”. VALUE converte cada parte; DATE constrói 4 de março de 2025. Valide as partes primeiro: DATE pode deslocar um dia ou mês impossível para uma data posterior em vez de informar um erro.

Para um valor como “ 1250 kg”, remova o rótulo de unidade conhecido e os espaços antes da conversão: =VALOR(ARRUMAR(SUBSTITUIR(A2;"kg";""))). Não remova letras indiscriminadamente de todos os campos, pois elas podem ter significado ou indicar uma entrada inválida. =SEERRO(VALOR(A2);"check") mantém conversões malsucedidas visíveis para revisão.

Mantenha identificadores como texto

O código “00427” identifica algo; não é uma quantidade a somar. A conversão em número resulta em 427 e perde os zeros iniciais. Números de telefone, muitos códigos postais e identificadores de conta também pertencem a colunas de texto. Defina o tipo antes de importar. O Excel preserva apenas 15 algarismos significativos no armazenamento numérico, então um identificador de 16 algarismos pode perder informação antes que você perceba. Formatá-lo como texto depois não recupera os algarismos perdidos.

Converta com SQL e examine as falhas

Em bancos de dados como PostgreSQL, CAST(amount_text AS DECIMAL(10,2)) interpreta texto numérico e retorna um decimal com duas casas. CAST(quantity_text AS INTEGER) solicita um número inteiro. Texto ISO pode ser convertido para DATE em muitos dialetos; outros formatos de data precisam de uma função de interpretação correspondente, como TO_DATE no PostgreSQL, STR_TO_DATE no MySQL ou PARSE_DATE no BigQuery.

Texto inválido frequentemente interrompe uma consulta CAST. TRY_CAST do SQL Server e SAFE_CAST do BigQuery retornam NULL em conversões comuns malsucedidas. Conte essas falhas separadamente dos valores já ausentes. Por exemplo, no SQL Server:

SELECT amount_text FROM sales
WHERE TRY_CAST(amount_text AS DECIMAL(10,2)) IS NULL
  AND amount_text IS NOT NULL;

SQLite pode converter “12abc” em 12 e “abc” em 0 sem aviso. Uma consulta bem-sucedida, portanto, não é evidência suficiente de uma conversão correta. Verifique as cadeias de caracteres originais, os valores convertidos, as contagens e os intervalos antes de aceitar a coluna limpa.

Um fluxo de trabalho em planilha eletrônica torna cada alteração visível e mantém evidências suficientes para repeti-la. Use uma aba de dados brutos, uma aba de trabalho com colunas auxiliares e uma tabela limpa verificada.

Prepare e ordene o trabalho

Mantenha os dados brutos intactos. Congele a linha de cabeçalho e use uma tabela ou filtros para selecionar o intervalo completo de dados. Ao ordenar, mova as linhas inteiras juntas para que nomes, IDs e valores permaneçam ligados.

Uma sequência prática é:

1. Faça o perfilamento da tabela e escreva uma lista de problemas.
2. Corrija cabeçalhos e divida campos que contêm várias partes necessárias.
3. Remova espaços extras, padronize maiúsculas e minúsculas e mapeie variantes de categorias.
4. Converta tipos e unifique formatos de datas, números e unidades.
5. Trate valores ausentes, preservando sinalizadores quando necessário.
6. Remova duplicatas usando a chave e a regra de manutenção acordadas.
7. Valide, concilie e faça verificações pontuais do resultado.

Essa ordem trata as dependências. Uma verificação de duplicatas em chaves brutas pode não identificar espaços extras. Um teste de intervalo em texto numérico pode não testar os valores pretendidos. Registre o efeito após cada etapa para que uma divergência posterior possa ser rastreada até sua causa.

Crie e examine colunas auxiliares

Se B2 contém “ ana SILVA”, =PRI.MAIÚSCULA(ARRUMAR(B2)) retorna “Ana Silva”. Preencha a fórmula por toda a tabela e compare os nomes brutos e limpos. Revise grafias que PROPER não consegue inferir corretamente.

Uma fórmula auxiliar como =MAIÚSCULA(ARRUMAR(SUBSTITUIR(B2;CARACT(160);" "))) funciona de dentro para fora: substitui espaços não separáveis, remove espaços comuns extras e depois altera maiúsculas e minúsculas. Use cabeçalhos auxiliares descritivos como city_clean para que fórmulas posteriores apontem para a coluna pretendida.

Filtre os resultados e verifique valores no início, no meio e no fim do intervalo. Uma fórmula copiada apenas por parte da tabela deixa uma coluna com conteúdos misturados. Procure erros e também células em branco.

Mapeie categorias com busca exata

Mantenha uma tabela de mapeamento com duas colunas: variante e rótulo padrão. Com uma variante preparada em B2, =PROCV(B2;Map!$A$2:$B$50;2;FALSO) encontra o rótulo correspondente. As referências absolutas mantêm o intervalo de mapeamento fixo ao preencher para baixo. FALSE solicita correspondência exata; a correspondência aproximada pode retornar uma categoria plausível, mas incorreta, para um valor não listado.

PROCX pode usar intervalos separados de busca e resultado. Seu padrão é a correspondência exata. Uma variante sem correspondência deve permanecer visível como erro para revisão. Adicione ou corrija seu mapeamento em vez de converter toda falha de busca em uma categoria familiar.

Finalize e documente

Após verificar as colunas auxiliares, copie seus resultados e use Colar Especial > Valores no Excel ou Colar especial > Somente valores no Google Sheets para criar a tabela limpa. Teste se ela é independente antes de excluir colunas de origem: fórmulas que ainda fazem referência a células excluídas podem retornar #REF!.

Ferramentas integradas como Remover Duplicatas, Localizar e Substituir e Texto para Colunas podem alterar muitas células de uma vez. Registre suas opções, o intervalo selecionado e a contagem de itens afetados enquanto a informação estiver visível. Fórmulas auxiliares mostram sua lógica diretamente; mantenha essa aba de trabalho junto com o registro.

As fórmulas em inglês aqui usam vírgulas. O idioma e a configuração regional da planilha podem alterar nomes de funções e separadores; por exemplo, o Excel em holandês usa SPATIES.WISSEN para TRIM, e configurações com vírgula decimal frequentemente usam ponto e vírgula entre argumentos.

O Excel permite 1.048.576 linhas e 16.384 colunas por planilha; o Google Sheets limita as células por arquivo. O desempenho pode se tornar um problema muito antes de um limite fixo. Dados atualizados com frequência ou já presentes em um banco de dados frequentemente se ajustam melhor a um fluxo SQL salvo do que a edições manuais repetidas em planilhas.

Uma consulta de limpeza em SQL pode retornar dados utilizáveis sem alterar a tabela bruta. Cada expressão torna uma regra explícita, e um nome alternativo identifica a saída limpa.

Construa o resultado uma coluna por vez

A consulta a seguir é adequada para PostgreSQL quando o texto de valores e datas já passou por verificações de conversão e a quantidade é numérica:

SELECT DISTINCT
  order_id,
  LOWER(TRIM(email)) AS email,
  COALESCE(NULLIF(UPPER(TRIM(city)), ''), 'UNKNOWN') AS city,
  CAST(amount AS DECIMAL(10,2)) AS amount,
  CAST(order_date AS DATE) AS order_date
FROM raw_orders
WHERE order_id IS NOT NULL
  AND quantity > 0;

Os e-mails têm espaços removidos das extremidades e são convertidos para minúsculas. Para a cidade, TRIM remove espaços das extremidades, UPPER padroniza maiúsculas e minúsculas, NULLIF transforma texto vazio em NULL e COALESCE fornece “UNKNOWN”. As conversões retornam valores numéricos e datas. WHERE exclui IDs ausentes e quantidades não positivas ou NULL. DISTINCT remove linhas repetidas do resultado selecionado.

Faça o perfilamento da saída após cada expressão adicionada. Uma consulta complexa pode ser executada com sucesso mesmo quando um mapeamento ou uma conversão está incorreto.

Entenda a ordem de filtragem

A ordem lógica útil é FROM, WHERE, SELECT, DISTINCT, ORDER BY. WHERE vê as colunas de origem antes que as expressões SELECT produzam seus nomes alternativos. Um nome alternativo não torna uma coluna bruta limpa dentro da cláusula WHERE da mesma consulta.

Para filtrar uma cidade bruta de forma consistente, use WHERE UPPER(TRIM(city)) = 'LISBON' ou salve o resultado limpo e filtre esse resultado em uma consulta posterior. Filtrar a cidade bruta com city = 'LISBON' pode deixar variantes de fora, mesmo quando SELECT limpa a cidade exibida.

CASE verifica condições em ordem e retorna o primeiro resultado correspondente:

CASE
  WHEN LOWER(TRIM(status)) IN ('shipped', 'sent') THEN 'shipped'
  WHEN status IS NULL THEN 'unknown'
  ELSE LOWER(TRIM(status))
END AS status

Sem ELSE, um valor sem correspondência torna-se NULL. Mantenha status não mapeados visíveis até que seu significado seja esclarecido.

Escolha entre filtrar e sinalizar

WHERE remove uma linha do resultado. Um sinalizador mantém a linha e marca um problema, por exemplo CASE WHEN delivery_date < order_date THEN 1 ELSE 0 END AS date_issue. Verifique separadamente se as datas obrigatórias são NULL.

Conte explicitamente as linhas excluídas:

SELECT COUNT(*) FROM raw_orders
WHERE order_id IS NULL
   OR quantity <= 0
   OR quantity IS NULL;

Isso considera todos os motivos pelos quais o filtro anterior rejeita uma linha, incluindo quantidades ausentes. Após DISTINCT, verifique a chave de negócio com GROUP BY e HAVING. Linhas que compartilham um ID, mas contêm valores diferentes, permanecem após DISTINCT e ainda precisam ser resolvidas.

Salve o método e escolha o dialeto adequado

CREATE VIEW clean_orders AS SELECT ... salva uma consulta que lê os dados atuais da fonte toda vez que a visão é usada. CREATE TABLE clean_orders AS SELECT ... cria um resultado armazenado em muitos dialetos; SQL Server comumente usa SELECT INTO. Mantenha a consulta e os comentários para poder reconstruir esse resultado quando as entradas mudarem.

Nomes e comportamento das funções variam entre sistemas SQL. Verifique no dialeto como interpretar datas, medir o comprimento de caracteres e fazer conversões seguras. UPDATE e DELETE alteram registros armazenados: visualize a mesma condição WHERE com SELECT e tenha uma cópia de segurança antes de aplicar qualquer um deles. Sem WHERE, eles afetam todas as linhas. A limpeza por SELECT é uma opção padrão útil para analistas com acesso somente de leitura.

Uma tabela limpa precisa de uma explicação de como foi produzida. A documentação permite que outra pessoa rastreie um valor comunicado, avalie uma decisão e execute novamente o trabalho na próxima entrega.

Escreva uma entrada útil no registro de alterações

Um registro de alterações é um registro datado de mudanças. Cada entrada deve identificar:

• A data e o autor.
• O conjunto de dados e a coluna ou intervalo afetado.
• O problema e sua dimensão original.
• A ação, incluindo fórmula, consulta ou opções da ferramenta.
• O número de linhas ou valores afetados.
• O motivo e qualquer fonte usada para confirmar uma correção.

Por exemplo: “2025-03-10, A. Silva, orders.city: espaços removidos das extremidades de 412 valores e valores convertidos para maiúsculas com UPPER(TRIM(city)); 9 grafias reduzidas a 4 cidades; necessário para vendas por cidade.”

Essa entrada explica o método e sua finalidade. “Cidades limpas” não identifica a regra de comparação, o escopo ou a contagem. Registre as entradas durante o trabalho para que a mensagem da ferramenta sobre linhas afetadas e o motivo de uma decisão ainda estejam disponíveis.

Registre decisões e operações

Uma fórmula não explica por que era adequada. Para duplicatas, indique as colunas de chave e qual registro foi mantido. Para valores ausentes, diga se foram recuperados, excluídos, preenchidos ou deixados ausentes. Se você preencheu valores, registre a regra de estimativa e a coluna de sinalização. Para uma correção, identifique a nota fiscal ou o registro de origem que forneceu o valor real.

Registre também linhas sinalizadas, mas mantidas. Quem revisa precisa distinguir um problema conhecido que você manteve visível de um problema que a limpeza deixou passar. Se um tratamento mudar depois, adicione uma entrada datada explicando a nova decisão.

Mantenha o método e seu histórico

Notas em células de planilhas eletrônicas podem explicar uma correção, enquanto uma aba de registro de alterações contém a sequência completa. O histórico de versões do Google Sheets pode restaurar estados anteriores do arquivo. O Histórico de Versões do Excel está disponível para arquivos armazenados no OneDrive ou no SharePoint. O histórico ajuda a recuperar um arquivo, mas um registro separado explica a intenção das alterações.

Comentários SQL podem explicar uma regra ao lado de sua expressão. Use -- para uma linha ou /* ... */ para um comentário mais longo. Mantenha versões de consultas em um controle de versão ou em um armazenamento com histórico de versões. Uma pasta compartilhada comum torna a consulta atual acessível, mas não mantém versões anteriores.

Torne possível executar novamente

Um processo é reprodutível quando a mesma entrada bruta e as mesmas regras produzem a mesma saída limpa. Consultas salvas e fórmulas auxiliares mantidas mostram suas regras. Uma edição manual de célula precisa do ID do registro, do valor antigo, do valor novo e do motivo para que alguém possa repeti-la com exatidão.

Mantenha juntos a cópia bruta, as etapas de transformação, as tabelas de mapeamento e o registro. Se uma regra depender de uma ordem de manutenção, como preservar o registro mais recente de um cliente, salve também a regra de ordenação ou seleção. Reproduzir um resultado exige as decisões e as expressões do software.

Compartilhe o registro e um breve resumo da limpeza com as pessoas que usam os dados. Quem interpreta os resultados precisa saber que pedidos de teste foram removidos ou que faltam idades para parte da base de clientes. Comunique erros recorrentes na origem ao responsável pelos dados para que ele possa corrigir o formulário, a importação ou a exportação que os cria.

A verificação pergunta se a limpeza corrigiu os problemas sem criar outros. Ela também verifica se os dados restantes ainda respondem à pergunta original. Use testes e comparações em colunas inteiras e depois examine registros individuais.

Concilie as contagens de linhas passo a passo

Comece com a contagem de linhas brutas. Subtraia as linhas removidas em cada etapa, contando cada remoção uma vez. Para 1.250 linhas brutas, 37 cópias duplicadas removidas e 12 linhas inválidas excluídas posteriormente:

A contagem limpa esperada é 1.201. Uma contagem diferente precisa de explicação antes da divulgação. Verifique a etapa em que a contagem divergiu pela primeira vez. Um filtro pode ter ocultado linhas de uma operação na planilha, ou um intervalo selecionado pode ter omitido parte da tabela.

Se uma linha tiver vários problemas, não a subtraia várias vezes. Use a contagem real de linhas antes e depois de cada operação. Mantenha contagens distintas para linhas removidas e células corrigidas: alterar dez células de cidade não remove dez linhas.

Repita o perfilamento e as regras

Execute as verificações originais na saída limpa. Compare cada resultado com a decisão registrada:

• As contagens de ausentes correspondem aos valores deixados ausentes, recuperados ou preenchidos.
• As listas de categorias têm as grafias padrão pretendidas.
• Os valores mínimos e máximos numéricos e de datas são válidos.
• A chave não tem duplicatas não resolvidas.
• As verificações de tipo, preenchimento obrigatório, intervalo e consistência não têm falhas sem explicação.

Uma contagem zero de células em branco após o preenchimento não significa que nenhum valor estava ausente. Verifique também os sinalizadores. Uma chave de negócio repetida pode permanecer após DISTINCT quando um campo selecionado é diferente, então teste essa chave diretamente.

Concilie valores e examine registros

Quando nenhum valor foi corrigido ou preenchido, o total limpo deve ser igual ao total bruto menos os valores das linhas removidas. Um total bruto de 152.400 menos 3.100 em pedidos duplicados removidos deve deixar 149.300. Se valores foram corrigidos ou preenchidos, inclua essas alterações separadamente na conciliação.

Compare registros de várias partes da tabela com os dados brutos e, quando possível, com a fonte original. Examine especialmente as linhas afetadas por etapas arriscadas: substituições, divisão de texto, interpretação e conversão de tipo. Examine nomes em busca de substituições abrangentes demais, colunas em busca de resultados de divisão deslocados, identificadores em busca de zeros perdidos e datas em busca de trocas de dia e mês. Uma data plausível ainda pode ser a data errada.

Verifique a cobertura da pergunta

Confirme se os dados limpos ainda abrangem o período, as regiões, as lojas, os produtos e os campos necessários. Excluir todos os pedidos de uma loja porque suas datas usam outro formato altera a população analisada. Corrija a regra de interpretação quando possível e informe qualquer cobertura que continue ausente.

Escreva um breve relatório indicando a fonte e o período, problemas e contagens, ações realizadas, exclusões ou preenchimentos, resultados da verificação e limitações restantes. Por exemplo: “A idade está ausente para 12% dos clientes; os resultados por idade descrevem os outros 88%”. Um resultado deve ser interpretado considerando essa cobertura.

Execute novamente a limpeza e a verificação quando novos dados brutos chegarem. Regras salvas tornam o processo repetível, mas novas categorias, formatos alterados e novos padrões de valores ausentes ainda precisam de revisão.