LIMPEZA DE DADOS NO EXCEL – IMPORTANDO, EXCLUINDO E PROMOVENDO LINHAS

Nesta primeira parte da sequência vamos apresentar a ferramenta de PowerQuery (ou Obter e Transformar dados no Excel 2016). No Excel 2010 e 2013 você precisará instalá-lo. Siga as instruções que estão nesta página da Microsoft.

As telas deste artigo foram feitas no Excel 2016; no Excel 2013 e 2010 você encontrará no menu PowerQuery que aparecerá depois que ele for instalado.

Você pode fazer limpeza de dados no Excel a partir de qualquer tipo de arquivo: Excel, CSV, Access, etc. Mostraremos neste artigo como fazer a limpeza de dados no Excel a partir de um arquivo XLSX.

O link para baixar o arquivo de exemplo está no final do artigo. Os dados estão assim:

Note que temos 3 linhas em branco, linhas em branco embaixo dos estados e os meses estão na forma de texto e apresentados em colunas, quando deveriam estar em uma coluna para que tenhamos uma base de dados tabular.

Vamos começar a limpeza de dados no Excel importando esses dados para dentro do PowerQuery. Este arquivo deverá ficar fechado e você deverá abrir um outro arquivo em branco, deixando o arquivo com as bases de dados fechado.

PARTE 1 – IMPORTAÇÃO DO ARQUIVO

Em um arquivo em branco, vamos em DADOS > OBTER DADOS > DE ARQUIVO > DA PASTA DE TRABALHO:

Em seguida abrirá uma tela para seleção do arquivo a ser importado. É o arquivo com a base de dados:

Selecione o arquivo da base de dados e clique em IMPORTAR. Aparecerá uma tela chamada NAVEGAÇÃO. à esquerda você verá o nome do arquivo e embaixo dele uma letra “a”, que nada mais é do que o nome da planilha que está sendo usada como base de dados. Se o arquivo tiver mais do que uma planilha ele mostrará naquela posição todas as planilhas (abas) daquele arquivo (clique na imagem para ver maior):

Ao clicar na planilha “a” você verá, à direita, a planilha bagunçada. Como ela precisa ser ajustada, clique no botão EDITAR. A tela que surgir é um editar de consultas (ele também existe no PowerBI). Clique na imagem par ver maior.

Este editor avançado é como um gravador de macros; nele iremos realizar as ações de ajuste da base e ele irá criar as etapas, assim quando a base de dados for atualizada ele irá aplicar exatamente estas etapas à base, eliminando retrabalho.

PARTE 2 – ELIMINAR LINHA EM BRANCO

Precisamos eliminar a primeira linha, já que ela está em branco e não serve para nada.

Vamos clicar em PÁGINA INICIAL > REMOVER LINHAS > REMOVER LINHAS PRINCIPAIS:

Na tela que surgir vamos colocar 1, pois é para remover apenas a 1ª linha:

Dê OK que aquela linha será eliminada:

Veja do lado direito da tela que temos uma tela chamada ETAPAS APLICADAS. Ela mostra todas as ações que fizemos para a limpeza de dados no Excel:

Nesta última etapa “Linhas Principais Removidas” há uma roda dentada à direita. Clique nela que ela mostrará a configuração daquela etapa (no caso, a exclusão de 1 linha). Significa que, se você errar algo, poderá corrigir diretamente na tela de configurações (ou remover a etapa clicando no “x” á esquerda da descrição da etapa.

PARTE 3 – COPIAR NOMES DOS ESTADOS

Na coluna 1 temos os nomes dos estados e embaixo deles valores vazios (null). Mas sabemos que ali nós deveríamos repetir o nome dos estados:

Por exemplo, as linhas 3 e 4 deveriam ter ao invés do null, o nome do estado.

Para ajustar isto, clique na coluna 1 e vá no menu TRANSFORMAR > PREENCHER > PARA BAIXO:

Veja o que ele fará:

(Uma pequena nota: se você já precisou fazer isto “na unha” no Excel, antes deste recurso existir? Eu já…)

PARTE 4 – REMOVER LINHAS COM  A PALAVRA “TOTAL”

Para isto, vamos aplicar um filtro, abra a seta na coluna 1, vá em FILTRO DE TEXTO > NÃO CONTÉM:

E digite “Total” (sem as aspas):

Dê OK que veremos apenas as linhas sem a palavra Total:

PARTE 5 – PROMOVENDO LINHAS

A 1ª linha da base corresponde ao nome das colunas, então precisamos “promovê-la” a nome de coluna.

Vá em PÁGINA INICIAL > USAR PRIMEIRA LINHA COMO CABEÇALHO:

Veja como ficará (clique na imagem para vê-la maior):

[irp posts=”764″ name=”Como importar dados da web para Excel”]

Para finalizarmos esta primeira parte de nossa série sobre limpeza de dados no Excel, falta apenas uma coisa:

PARTE 6 – CONVERTENDO COLUNAS EM LINHAS

As colunas de janeiro até dezembro deveriam ser, na verdade, linhas, já que os meses correspondem a uma única categoria – data.

Vamos selecionar as 12 colunas de meses (clique na imagem par ver maior):

Depois de selecionadas, vá em TRANSFORMAR > TRANSFORMAR COLUNA EM LINHAS:

E veja o resultado:

Agora basta dar um duplo clique no nome da coluna “Atributo” e renomeá-la para algo mais amigável (o mesmo na coluna Valor, se desejar):

No exemplo, renomeamos todas as colunas.

E veja que o editor de consultas armazenou todas as etapas que aplicamos na base:

Vamos retornar ao Excel para ver como ficou. Clique em PÁGINA INICIAL > FECHAR E CARREGAR:

E teremos a base colada no Excel:

E finalizamos a nossa 1ª parte. Na Parte 2 vamos transformar esses meses em data de verdade (já que por enquanto são apenas palavras), vamos formatar a coluna de receitas e fazer aparecer o nome completo do Estado.

Até a 2ª parte!

OUTROS ARTIGOS DE INTERESSE