Neste artigo você aprenderá como fazer de uma forma simples um De Para no Excel de modo que os valores sejam substituídos por outros conforme tabelas com outras informações.
Como preparar a estrutura da Tabela
Imagine a situação em que você recebe uma base de dados de um cliente e precisa importar estes dados para o seu sistema.
Porém, o sistema do cliente envia estes dados de uma forma diferente do que está no seu sistema, e isso será recorrente.
Digamos que ao invés de estar preenchido RADIO está vindo Radio, e a o invés de estar preenchido coaxial está vindo CABO.
Este problema impediria a sua importação dos dados e o seu gerente pediu que fossem tratados os dados antes da importação, fazendo então um DE PARA no Excel.
Como podemos fazer isso dado que as palavras podem acabar mudando no sistema do seu cliente ou ainda aumentarem as palavras?
Uma das formas de resolver este problema é fazer uso do Excel e do Power Query.
Digamos que a sua tabela de origem dos dados seja a seguinte:
Esta é a tabela de dados recebida do nosso cliente, ela já está em formato de tabela no Excel, ALT+T+A para converter uma lista em tabela.
E nós precisamos que algumas palavras das colunas Comunicação, Conexão e Tecnologia sejam subsituídas.
Tabelas De Para Excel
Par que as palavras que desejamos sejam substituídas por outras, precisamos primeiro criar as tabelas De Para.
Acima temos as tabelas DE PARA em Excel para substituir as palavras na planilha.
Veja que são sempre duas colunas, De e Para e separadas em 3 tabelas, pois são três colunas com a mesma necessidade.
Importante ressaltar que estas tabelas podem ter seus dados alterados, incluídas novas linhas ou excluídas, sem nenhum problema para o processo que faremos.
Veja como iremos tratar das questões para substituir os dados das colunas no Excel de forma automática.
Power Query com Tabelas
Um dos recursos mais interessantes do Excel é o Power Query, que é uma das ferramentas de ETL que o Excel possui.
Com ela podemos automatizar processos que antes eram feitos com fórmulas ou com VBA.
Para fazer o De para e criarmos uma tabela com os dados tratados faça o seguinte.
Clique sobre a tabela com os dados do cliente e clique na guia Dados->De Folha ou De Tabela.
O Excel irá abrir o Power Query com estes dados.
No Power Query clique em Arquivo->Fechar e Carregar->Fechar e Carregar para…., selecione a opção Apenas Criar Conexão.
O Excel irá criar uma conexão com esta tabela.
Repita a operação para as tabelas De Para.
Uma vez as tabelas no Power Query nós iremos agora editar o carregamento da tabela com os dados substituídos conforme as tabelas.
Se não estiver aberto o Power Query clique em Dados->Consultas e Conexões e clique com o botão direito sobre a primeira consulta e no botão Editar.
A tabela estará conforme abaixo.
Faça as mudanças de tipos no cabeçalho conforme a necessidade.
Depois clique em Página Inicial->Mesclar Consultas.
Selecione a primeira coluna Comunicação e na lista de tabelas selecione tComunicacao, que é a tabela correspondente a este campo.
Marque então a coluna De da tComunicacao e clique em OK.
O Excel irá carregar os dados desta tabela mesclando com a tabela principal.
Clicando no botão de setas irão ser exibidos os campos da tabela, para poder escolher os campos que deseja expandir.
Neste caso selecione a coluna Para, porque ela tem o retorno que desejamos substituir na coluna Comunicação.
Então clique em Ok para abrir a coluna.
Clique na guia Adicionar Coluna -> Coluna Condicional.
Nela selecione as seguintes opções:
Clique em OK e veja que se a nova coluna tComunicacao.para estiver diferente de null, então, será retornada esta coluna, senão, será retornada a coluna atual, pois não há substituição para esta palavra.
Veja que agora temos uma nova coluna chamada Comunicação 2, pode colocar o nome que quiser.
Temos agora duas colunas Comunicação e Comunicação 2, a primeira com a informação antiga e a segunda com o De para da tabela Excel.
Clique nas colunas que deseja manter com o botão Direito e clique em Remover outras colunas.
Veja então que o Power Query terá apenas as colunas que precisa e poderá carregar estes dados para o Excel.
Repita esta operação para todas as outras duas tabelas.
Por fim, para carregar a tabela tratada clique em Página Inicial->Fechar e Carregar.
Uma vez no Excel, crie uma nova planilha e clique com o botão direito sobre a consulta que deseja carregar e selecione Tabela.
Por fim tenha os dados De Para do Excel na sua tabela totalmente tratada.
Atualizar a Tabela De Para Excel
Para atualizar a tabela basta clicar em Dados->Atualizar Tudo ou então sobre a tabela final com o botão direito e selecionar a opção Atualizar Dados.
Download Planilha De Para Excel
Realize o download da planilha de exemplo deste artigo clicando no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
