Limpeza de dados com funções de texto
Os dados que você obtém de diferentes fontes podem não estar em um formato pronto para análise. Neste capítulo, você entenderá como preparar seus dados que estão na forma de texto para análise.
Inicialmente, você precisa limpar os dados. A limpeza de dados inclui a remoção de caracteres indesejados do texto. Em seguida, você precisa estruturar os dados na forma necessária para uma análise posterior. Você pode fazer o mesmo por -
- Encontrar os padrões de texto necessários com as funções de texto.
- Extraindo valores de dados de texto.
- Formatando dados com funções de texto.
- Executando operações de dados com as funções de texto.
Removendo caracteres indesejados do texto
Quando você importa dados de outro aplicativo, eles podem conter caracteres não imprimíveis e / ou espaços em excesso. Os espaços excedentes podem ser -
- espaços principais, e / ou
- espaços extras entre as palavras.
Se você classificar ou analisar esses dados, obterá resultados errados.
Considere o seguinte exemplo -
Estes são os dados brutos que você obteve nas informações do produto contendo a ID do produto, a descrição do produto e o preço. O personagem “|” separa o campo em cada linha.
Quando você importa esses dados para a planilha do Excel, eles têm a seguinte aparência -
Como você observa, todos os dados estão em uma única coluna. Você precisa estruturar esses dados para realizar a análise de dados. No entanto, inicialmente você precisa limpar os dados.
Você precisa remover quaisquer caracteres não imprimíveis e espaços em excesso que possam estar presentes nos dados. Você pode usar a função CLEAN e a função TRIM para este propósito.
| S.No. | Descrição da função |
|---|---|
| 1 | CLEAN Remove todos os caracteres não imprimíveis do texto |
| 2 | TRIM Remove os espaços do texto |
- Selecione as células C3 - C11.
- Digite = TRIM (CLEAN (B3)) e pressione CTRL + Enter.
A fórmula é preenchida nas células C3 - C11.
O resultado será como mostrado abaixo -
Encontrar os padrões de texto necessários com as funções de texto
Para estruturar seus dados, pode ser necessário fazer certa correspondência de padrão de texto com base na qual você pode extrair os valores de dados. Algumas das funções de texto que são úteis para esse fim são -
| S.No. | Descrição da função |
|---|---|
| 1 | EXACT Verifica se dois valores de texto são idênticos |
| 2 | FIND Encontra um valor de texto dentro de outro (diferencia maiúsculas de minúsculas) |
| 3 - | SEARCH Encontra um valor de texto dentro de outro (não diferencia maiúsculas de minúsculas) |
Extração de valores de dados de texto
Você precisa extrair os dados necessários do texto para estruturá-lo. No exemplo acima, digamos, você precisa colocar os dados em três colunas - ProductID, Product_Description e Price.
Você pode extrair dados de uma das seguintes maneiras -
- Extraindo valores de dados com o Assistente de conversão de texto em colunas
- Extração de valores de dados com funções de texto
- Extração de valores de dados com Flash Fill
Extraindo valores de dados com o Assistente de conversão de texto em colunas
Você pode usar o Convert Text to Columns Wizard para extrair valores de dados em colunas do Excel se seus campos forem -
- Delimitado por um personagem, ou
- Alinhado em colunas com espaços entre cada campo.
No exemplo acima, os campos são delimitados pelo caractere “|”. Portanto, você pode usar oConvert Text to Columns Mago.
Selecione os dados.
Copie e cole os valores no mesmo lugar. De outra forma,Convert Text to Columns recebe as funções em vez dos próprios dados como entrada.
Selecione os dados.
Clique em Text to Columns no Data Tools grupo sob Data Guia na faixa de opções.
Step 1 - Assistente de conversão de texto em colunas - a etapa 1 de 3 aparece.
- Selecione Delimitado.
- Clique em Avançar.
Step 2 - Assistente de conversão de texto em colunas - a etapa 2 de 3 aparece.
Debaixo Delimiters, selecione Other.
Na caixa ao lado de Other, digite o caractere |
Clique Next.
Step 3 - Assistente de conversão de texto em colunas - a etapa 3 de 3 aparece.
Nesta tela, você pode selecionar cada coluna de seus dados no assistente e definir o formato dessa coluna.
Para Destination, selecione a célula D3.
Você pode clicar Advanced, E definir Decimal Separator e Thousands Separator no Advanced Text Import Settings caixa de diálogo que aparece.
Clique Finish.
Seus dados, que são convertidos em colunas, aparecem nas três colunas - D, E e F.
- Nomeie os cabeçalhos da coluna como ProductID, Product_Description e Price.
Extração de valores de dados com funções de texto
Suponha que os campos em seus dados não sejam delimitados por um caractere nem alinhados em colunas com espaços entre cada campo, você pode usar funções de texto para extrair valores de dados. Mesmo no caso de os campos serem delimitados, você ainda pode usar funções de texto para extrair dados.
Algumas das funções de texto que são úteis para este propósito são -
| S.No. | Descrição da função |
|---|---|
| 1 | LEFT Retorna os caracteres mais à esquerda de um valor de texto |
| 2 | RIGHT Retorna os caracteres mais à direita de um valor de texto |
| 3 - | MID Retorna um número específico de caracteres de uma string de texto começando na posição que você especificar |
| 4 | LEN Retorna o número de caracteres em uma string de texto |
Você também pode combinar duas ou mais dessas funções de texto de acordo com os dados disponíveis, para extrair os valores de dados necessários. Por exemplo, usando uma combinação das funções LEFT, RIGHT e VALUE ou usando uma combinação das funções FIND, LEFT, LEN e MID.
No exemplo acima,
Todos os personagens deixaram para o primeiro | dê o nome ProductID.
Todos os personagens à direita para o segundo | dê o nome de Preço.
Todos os personagens que se encontram entre os primeiros | e segundo | dê o nome Product_Description.
Cada | tem um espaço antes e depois.
Observando essas informações, você pode extrair os valores dos dados com as seguintes etapas -
Encontre a posição do primeiro | -First | Position
Você pode usar a função FIND
Encontre a posição do segundo | -Second | Position
Você pode usar a função FIND novamente
Começando a (First | Position - 2) Os caracteres do texto fornecem ProductID
Você pode usar a função LEFT
(First | Position + 2) para (Second | Position - 2) Caracteres do texto fornecem Product_Description
Você pode usar a função MID
(Second | Position + 2) para Caracteres Finais do Texto dar Preço
Você pode usar a função RIGHT
O resultado será como mostrado abaixo -
Você pode observar que os valores na coluna de preço são valores de texto. Para realizar cálculos nesses valores, você deve formatar as células correspondentes. Você pode consultar a seção fornecida abaixo para entender a formatação do texto.
Extração de valores de dados com Flash Fill
Usando Excel Flash Fillé outra maneira de extrair valores de dados do texto. No entanto, isso funciona apenas quando o Excel é capaz de encontrar um padrão nos dados.
Step 1 - Crie três colunas para ProductID, Product_Description e Price ao lado dos dados.
Step 2 - Copie e cole os valores de C3, D3 e E3 de B3.
Step 3 - Selecione a célula C3 e clique Flash Fill no Data Tools grupo no Dataaba. Todos os valores para ProductID são preenchidos.
Step 4- Repita as etapas fornecidas acima para Descrição_do_produto e Preço. Os dados são preenchidos.
Formatando dados com funções de texto
O Excel possui várias funções de texto integradas que você pode usar para formatar dados que contêm texto. Isso inclui -
Functions that format the Text as per your need -
| S.No. | Descrição da função |
|---|---|
| 1 | LOWER Converte o texto em minúsculas |
| S.No. | Descrição da função |
|---|---|
| 1 | UPPER Converte o texto em maiúsculas |
| 2 | PROPER Capitaliza a primeira letra de cada palavra de um valor de texto |
Functions that convert and/or format the Numbers as Text -
| S.No. | Descrição da função |
|---|---|
| 1 | DOLLAR Converte um número em texto, usando o formato de moeda $ (dólar) |
| 2 | FIXED Formata um número como texto com um número fixo de decimais |
| 3 - | TEXT Formata um número e o converte em texto |
Functions that convert the Text to Numbers -
| S.No. | Descrição da função |
|---|---|
| 1 | VALUE Converte um argumento de texto em um número |
Executing Data Operations with the Text Functions
Você pode ter que realizar certas operações de texto em seus dados. Por exemplo, se os IDs de login dos funcionários forem alterados para um novo formato em uma organização, com base na alteração do formato, substituições de texto podem ter que ser feitas.
As seguintes funções de texto ajudam você a realizar operações de texto em seus dados contendo texto -
| S.No. | Descrição da função |
|---|---|
| 1 | REPLACE Substitui caracteres dentro do texto |
| 2 | SUBSTITUTE Substitui o texto novo pelo texto antigo em uma string de texto |
| 3 - | CONCATENATE Une vários itens de texto em um item de texto |
| 4 | CONCAT Combina o texto de vários intervalos e / ou strings, mas não fornece o delimitador ou argumentos IgnoreEmpty. |
| 5 | TEXTJOIN Combina o texto de vários intervalos e / ou strings e inclui um delimitador que você especifica entre cada valor de texto que será combinado. Se o delimitador for uma string de texto vazia, esta função concatenará efetivamente os intervalos. |
| 6 | REPT Repete o texto um determinado número de vezes |