Como limpar dados no Excel com técnicas e dados modernos

The quality of your data analysis depends on the quality of the data behind it. This is why data cleaning in Excel is so important. Excel data riddled with errors and inconsistencies is a significant issue: 20% of IT and data leaders say poor data quality is one of the most critical challenges they face.

Dados limpos são a base para dados de alta qualidade e insights precisos da análise de dados. Ninguém quer ver erros nos seus resultados ou, pior, que os erros passem despercebidos e influenciem incorretamente as decisões de negócio.

O que é limpeza de dados no Excel?

Data cleaning in Excel is the process of preparing raw data for analysis by removing bad data, organizing the raw data, and filling in the null values. Working with clean data is a prerequisite for getting meaningful and accurate insights from your data.

Data cleansing is just one of the steps in the data preparation process that ensures data is ready for analysis and reporting. Data prep also includes:

• Exploração de dados: descobrir as surpresas que os conjuntos de dados escondem.
• Combinação de dados: combinar vários conjuntos de dados para fazer descobertas novas.
• Criação de perfis de dados: detectar dados de baixa qualidade antes que estraguem seus resultados.
• ETL (Extrair, Transformar, Carregar): agregar dados de diversas fontes.
• Manipulação de dados: fazer o pré-processamento dos dados para os modelos analíticos.

Quais as características dos dados limpos?

• Acurácia: significa que seus dados exibem corretamente os valores reais que representam. Isso garante que as informações exibidas sejam precisas e reflitam o estado real dos dados.
• Consistência: significa que os dados permanecem os mesmos em diferentes conjuntos de dados e períodos de tempo. Os dados limpos contêm consistentemente os mesmos formatos e unidades de medida.
• Integridade: a integridade dos dados refere-se à extensão em que todas as informações necessárias estão presentes em um conjunto de dados. Se seus dados estiverem incompletos e faltam detalhes importantes, isso poderá afetar os resultados. Certifique-se de preencher as lacunas ou levar em conta os valores ausentes na sua análise.
• Consistência: é o grau com que os dados usam a mesma unidade de medida ou um formato e estrutura coerentes. Isso inclui usar as mesmas unidades de medida ou formatar datas e rotulá-las em categorias.
• Validade: significa que os valores dos dados estão dentro da faixa aceitável especificada e estão em conformidade com os padrões esperados. Por exemplo, se uma entrada de idade válida estiver entre 0 e 120, regras de validação e verificações serão aplicadas para garantir que os dados atendam a esse critério. Isso evita que valores discrepantes e entradas incorretas distorçam os resultados.

Techniques for cleaning data in Excel

The vast majority of business analysts have learned how to clean data in Excel. But given the huge number of Excel data cleaning techniques, this is usually an on-going process. To help analysts get their start (or refresh some old tricks), we’ve selected the most common ways for how to clean data in Excel with instructions for each data cleansing technique.

Remover duplicatas

One of the easiest data cleansing techniques in Excel is removing duplicates. Different bits of data are often stored as different datasets, and sometimes in different databases. Duplication occurs when the data is consolidated, even through simply copying and pasting from various sources. Duplicate values can appear without the user’s knowledge so it’s important to take this step for cleaner data. To remove duplicates in Excel, follow these simple instructions.

1. Selecione as células nas quais você deseja verificar se há duplicatas
2. Selecione "Dados" na barra de ferramentas e "Remover Duplicatas", na faixa "Ferramentas de Dados"
3. Em seguida, clique em "OK"

Remover espaços extras

Para remover esses espaços extras difíceis de detectar, os analistas de dados podem usar a função TRIM, que remove espaços iniciais e finais, bem como espaços extras entre palavras. Essa não é apenas uma questão estética. Remover os espaços no Excel é importante para poder exibir esses valores por meio de funções como VLOOKUP. Veja como fazer isso:

1. É possível usar a função TRIM manualmente, além de usá-la em conjunto com a função VLOOKUP. Isso permite que os usuários pesquisem e corrijam quaisquer espaços adicionados enquanto encontram as correspondências que procuram.
2. Sua fórmula VLOOKUP consistiria então no seguinte:
   1. TRIM + o valor da consulta, sem espaços adicionais
   2. O intervalo da consulta
   3. A coluna com o valor a retornar
   4. 0 (para correspondência exata)

Localizar e substituir texto

The find and replace function is useful for several data cleansing techniques. One of them is to standardize values for datasets that may store values in multiple variations. You can use find and replace to tackle them one by one.

1. Selecione a célula ou coluna
2. Vá para a Página Inicial > Localizar e Selecionar
3. Insira um valor na opção Localizar
4. Insira o valor pelo qual deseja substituí-lo
5. Clique em "Substituir Tudo" se desejar aplicar a opção à planilha inteira

Resolver as células em branco é outra maneira de usar o recurso Localizar e Substituir ao limpar dados no Excel. Se não forem resolvidas, serão frequentemente interpretadas como erros ou não serão incluídas na análise final quando originalmente deveriam significar "0".

1. Para selecionar todas as células em branco, os usuários só precisam abrir a caixa de diálogo "Ir para", selecionar "Especial" e, em seguida, "Espaços em branco" ou todos os espaços em branco na sua célula.
2. A partir daí, fica fácil inserir um valor consistente como "0" ou "N/A" pressionando Ctrl e Enter.

Verificar se há erros de digitação e palavras com erros ortográficos

Termos com erros ortográficos podem aparecer em qualquer lugar. Por isso, é uma boa ideia sempre verificar se há erros de ortografia e de digitação nos seus dados.

1. Selecione a célula de dados, coluna ou planilha na qual deseja verificar erros ortográficos
2. Em seguida, vá para a opção "Revisão" e selecione "Verificar Ortografia"
3. O Excel exibe automaticamente a grafia correta na caixa de diálogo. Clique em "Alterar" se concordar com a sugestão

Padronizar formatos

Corrigir a estrutura do formato no Excel é um dos desafios mais comuns de limpeza de dados. Embora a maioria dos bancos de dados contenha dados estruturados, às vezes os formatos são reconhecidos incorretamente ou há incompatibilidades nos formatos dos dados. A formatação no Excel é o processo de alteração de dados em planilhas de um formulário para outro. Existem várias maneiras de padronizar formatos no Excel.

1. Como usar a ferramenta de formatação do Excel
   1. Selecione o intervalo de dados
   2. Clique com o botão direito e escolha "Formatar células"
   3. Ajuste a configuração do formato conforme necessário
2. Como usar o Power Query para descobrir e modificar tipos e formatos de dados
   1. Vá para o Power Query
   2. Selecione uma fonte de dados
   3. Vá para Consulta > Editar função
   4. Selecione uma coluna e procure "Detectar tipo de dados", em "Transformar"
   5. Selecione um tipo ou formato de dados no menu suspenso

Gerenciar células em branco

Não existe uma abordagem única na hora de lidar com células em branco no seu conjunto de dados. No entanto, é útil saber por que as células estão vazias. Aqui estão os métodos mais comuns para lidar com células em branco no Excel:

1. Excluir as células em branco
   1. Selecione o intervalo que você deseja excluir
   2. Clique com o botão direito na seleção
   3. Escolha "Excluir" e selecione a opção de deslocar as células para cima, para a esquerda ou linhas/colunas inteiras
2. Formatação condicional
   1. Selecione "Formatação Condicional", na aba "Página Inicial"
   2. Selecione "Nova Regra"
   3. Selecione um tipo de regra
   4. Escolha como você deseja formatar os espaços em branco
3. Filtrar os dados
   1. Na aba "Página Inicial", selecione "Classificar e Filtrar", em "Edição"
   2. Adicione um filtro às colunas que você deseja filtrar
   3. Clique na seta de dados das colunas selecionadas
   4. Desmarque todas as caixas, exceto a caixa "Vazias"

Dividir e mesclar colunas

Se você tiver conjuntos de dados que precisam ser divididos em colunas separadas, poderá usar o recurso "Texto para Colunas" no Excel.

• Selecione o intervalo de datas dos dados
• Vá para a aba "Dados"
• Clique em "Texto para Colunas"
• Siga as etapas para especificar os delimitadores e dividir os dados

Mesclar dados de várias colunas é outra maneira eficaz de limpar dados no Excel. O método mais simples é usar a função "CONCATENATE" ou o operador E comercial (&).

Distribuir o conteúdo de uma célula em colunas adjacentes

When analysts first start to learn data cleaning in Excel, this is another early question that arises. Analysts don’t normally have absolute control as to how data is recorded, which means that too much data can be recorded in one cell. For example, sensor data arrives with both the date and time combined in a single value, when it is often more helpful to separate the two values into two columns. Here’s how to do it:

1. Selecione a célula, intervalo ou coluna inteira que contém os valores de texto que você deseja dividir
2. Na aba "Dados", no grupo "Ferramentas de Dados", clique em "Texto para Colunas"
3. Siga as instruções do "Assistente de conversão de texto em colunas" para especificar como deseja dividir o texto em colunas separadas

Os desafios da limpeza de dados no Excel

A experiência de limpeza de dados no Excel é conhecida e confiável. Há décadas, os analistas de dados têm limpado com sucesso os dados do Excel para prepará-los para análise.

However, as today’s data grows larger and increasingly complex, Excel is showing its age. Data cleansing works best when analysts are relatively familiar with the data at hand; they know what to search for and what types of errors need addressing. If not, it’s hard to identify invalid numbers or incorrect dates, especially when that data respects certain format standards. And when it comes to assessing data inconsistency between multiple spreadsheets, cleaning data in Excel becomes a complex project all by its own.

Em vez de aprender como limpar dados no Excel, os analistas de dados estão usando cada vez mais novas soluções de preparação de dados que identificam de forma inteligente possíveis transformações e revelam erros visualmente. Soluções desse tipo refletem o futuro do mundo do analytics: a adoção de plataformas fáceis de usar, flexíveis e que permitem interação visual.

Uma alternativa que economiza tempo para limpar dados no Excel

Sem a qualidade adequada dos dados, a precisão da sua análise final será prejudicada ou você poderá chegar a conclusões incorretas.

Embora o uso do Excel para limpar dados às vezes funcione bem para conjuntos de dados simples, novas soluções de preparação de dados oferecem uma abordagem mais inteligente para lidar com dados maiores e menos conhecidos.

Alteryx solves the data cleansing dilemma. It allows users to interactively explore their datasets by clicking on components of the data so that they can discover new and essential ways in which it must be transformed. Instead of a process driven by careful steps like data cleaning in Excel, Designer Cloud will automatically present suggested transformations for users to evaluate and edit. With Designer Cloud, not only are users producing more thoroughly cleansed datasets, but are also achieving it in significantly less time.

Para saber mais sobre como o Designer Cloud funciona e se ele é a melhor alternativa para seu caso de uso, agende agora mesmo uma demonstração gratuita.