fbpx

LIMPEZA DE DADOS NO EXCEL

PARTE 1 - IMPORTANTO, REMOVENDO E PROMOVENDO LINHAS

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!

[the_ad_group id=”486″]

Licença Creative Commons

OUTROS ARTIGOS DE INTERESSE

quatro motivos para voce parar de usar o procv

quatro motivos para voce parar de usar o procv

Já são 40 anos de idade O Excel 1.0 foi criado em 1982. São quase 40 anos de existência. Apesar de ter começado minha carreira usando o Excel 5.0 tenho certeza de que a função PROCV existia nela (pelo menos no 5.0 tinha!). Então estamos hoje, no séc. XXI obcecados em...

ler mais
Como Criar um Gráfico de Waterfall no Excel

Como Criar um Gráfico de Waterfall no Excel

O que é ? O gráfico de tornou-se popular através da empresa de consultoria McKinsey no final do séc XX em apresentações para seus clientes. Sua utilidade é apresentar de forma visual a decomposição de um valor principal até o resultado final. Significa que em toda a...

ler mais
Como Criar Bullet Chart no Excel

Como Criar Bullet Chart no Excel

O que é um Bullet Chart O Bullet Chart foi criado por Stephen Few por volta de 2005 com o objetivo de ser a solução PROFISISONAL para as aberrações chamadas de Gráficos de Velocímetro. O Bullet Chart e capaz de apresentar de forma mais clara valores e suas relações...

ler mais
Siga-Me

Nossas Redes Sociais

Facebook
Linkedin
Instagram
Youtube