Como usar o preenchimento relâmpago (flash fill) no excel

Planilha de controle de horas de terceiros
Planilha de controle de horas trabalhadas – Terceirizados
7 de janeiro de 2017
Criar manuais automaticamente com gravador de passos 11
Como criar manuais automaticamente com o Gravador de Passos
5 de fevereiro de 2017

Como usar o preenchimento relâmpago (flash fill) no excel

Neste artigo você verá como usar o preenchimento relâmpago (flash fill) no Excel. Este recurso que foi implementado no Excel 2013 pode ser localizado na guia Dados no grupo Ferramenta de dados representado por um ícone com uma tabela e um raio.

Este ícone não poderia estar mais correto, porque ele realmente preenche ,de forma automática e rápida, colunas com informações para as quais poderiam ser necessárias fórmulas gigantes.

Como usar o preenchimento relâmpago (flash fill) no excel

Como funciona o preenchimento relâmpago no Excel?

O preenchimento relâmpago tem por finalidade preencher de forma automática valores á partir de exemplos que você mesmo preencheu. Estes exemplos podem conter células variáveis que estejam na mesma linha da coluna aonde haverá o preenchimento relâmpago e ou textos fixos.

Esta funcionalidade não tem nenhuma ligação com o preenchimento automático, que realiza o preenchimento sequencial á partir de sua lista, como por exemplo 1, 2, 3 ou Domingo, Segunda-feira, Terça-feira, etc.

O Preenchimento relâmpago é capaz de realizar tarefas como extrair textos de várias células e uni-los em outra, extrair partes de datas, mesclar valores fixos e variáveis á partir de células, etc.

Isso torna esta função tão interessante, pois ela reduz muito o uso das famosas funções EXT.TEXTO, ARRUMAR, DIREITA, ESQUERDA e LOCALIZAR, CONCATENAR e do coringa &, muito usadas em situações como estas que costumam dar trabalho.

Exemplo 1 – Extrair nome, sobrenome e data de início e fim de mandatos de lista de presidentes com o Preenchimento relâmpago do Excel

Na lista de dados do gif abaixo temos alguns dos presidentes do Brasil, na coluna ao lado gostaríamos de extrair o nome e o sobrenome de cada um deles.

Preenchendo o primeiro exemplo na coluna e pressionando as teclas CTRL+E ou ainda selecionando na guia Dados a opção Preenchimento Relâmpago, os dados abaixo são automaticamente populados, sem o uso de fórmulas adicionais do Excel.

Como usar o preenchimento relâmpago (flash fill) no excel

Se você tentar fazer, irá perceber que nem todos estão preenchidos corretamente, principalmente o sobrenome.

Isto ocorre ás vezes por conta da complexidade do algoritmo que tem que prever diversas situações.

Para corrigir é bastante simples, basta alterar a célula incorreta para que os casos semelhantes tenham o ajuste aplicado, sem a necessidade nem do uso do CTRL+E, pois ele já entende que a correção é parte do preenchimento relâmpago no Excel.

Exemplo 2 – Formatar e concatenar textos com o uso do preenchimento relâmpago no Excel

Com este recurso é possível extrair e concatenar textos com os dados variáveis, como no exemplo da animação, aonde temos um telefone com DDD sem formatação.

São extraídos os dados do DDD, fone e também uma coluna é formatada com os dados de DDD e telefone devidamente formatados e concatenados com textos fixos da sua formatação como () e -.

Como usar o preenchimento relâmpago (flash fill) no excel

Perceba que na primeira coluna, DDD são preenchidos 3 DDDs como exemplo antes de aplicar o preenchimento relâmpago, isso foi necessário para que o programa reconhecesse o padrão e pudesse repeti-lo para o restante da lista.

Exemplo 3 – Extrair dia, mês, ano, converter data em outros formatos com preenchimento relâmpago no Excel

Outra funcionalidade interessante é o funcionamento com datas. O recurso é capaz de extrair dia, mês e ano por exemplo, dispensando o uso da função DIA, MÊS e ANO do Excel, além disso é capaz de dispor estes dados de outras formas também sem a necessidade do uso de concatenações e funções de texto, como no exemplo abaixo:

Preenchimento relâmpago Excel 3

Como podemos perceber, o recurso não tem maiores dificuldades de extrair o dia, mês e ano automaticamente, mas aqui cabe uma dica, nas colunas em que for usar o preenchimento relâmpago, formate como texto, pois desta forma você conseguirá preencher o mês por exemplo com duas casas decimais, o mesmo se aplicando para outras situações como o mês.

Na conversão do formato AAAAMMDD que temos na situação 2, percebemos que foi necessário preencher 3 exemplos e mesmo assim ainda foi necessária a correção do dia 21/07/2016, pois o preenchimento relâmpago não conseguiu chegar na lógica. Mesmo assim com o uso do recurso foi possível ganhar muito tempo.

Exemplo 4 – Extrair dados diversos de um texto com o preenchimento relâmpago excel

Este recurso é mesmo muito interessante, veja o exemplo abaixo, aonde temos que extrair de um histórico contábil o CPF, a data e o número da nota fiscal.

Estas informações poderiam ser extraídas utilizando funções de texto, ou ainda com o Texto para colunas, mas veja a facilidade de extração destes dados com o preenchimento relâmpago do Excel.

Como usar o preenchimento relâmpago (flash fill) no excel

 Veja que de forma muito simples, sem uso de fórmulas extensas o recurso conseguiu extrair facilmente todos os dados necessários. Muito interessante este uso para extrair dados de lançamentos contábeis e relatórios.

Exemplo 5 – Montar scripts ou concatenar textos e valores variáveis com o preenchimento relâmpago do Excel

Na minha opinião, esta é uma das melhores aplicações do recurso, faço muito uso e já ensinei para a equipe de desenvolvimento da empresa.

Temos a seguinte situação, montar um script SQL no Excel para ajustar a situação de alguns clientes. Para isso é necessário montar o script para cada um dos 5.000 clientes.

Este problema poderia ser solucionado utilizando as funções de texto, não é mesmo? Mas com o uso do preenchimento relâmpago você ganha preciosos minutos. Veja na imagem abaixo a aplicação.

Preenchimento relâmpago Excel 5

Perceba que no update há textos fixos e uso de variáveis, inversão do campo de data, substituição da “,” por “.” no caso do valor e ainda usado o ‘ antes e depois dos vencimentos e do status.

O uso do preenchimento relâmpago neste caso substituiu o uso da fórmula abaixo:

=”UPDATE PARCELAS SET VALOR =”&SUBSTITUIR([@VALOR];”,”;”.”)&”, VENCIMENTO = ‘”&ANO([@DATA])&”-“&DIREITA(“00″&MÊS([@DATA]);2)&”-“&DIREITA(“00″&DIA([@DATA]);2)&”‘, STATUS = ‘”&[@STATUS]&”‘ WHERE CHAVE = “&[@[CHAVE 1]]

Conclusão sobre preenchimento relâmpago

Este recurso no qual vimos a aplicação em vários exemplos acima citados é ainda muito pouco utilizado, mas merece uma atenção especial de nós clientes do Excel, pois com o uso deste recurso podemos reduzir muito o tempo dispendido com soluções complexas e muitas vezes pontuais.

Se quiser fazer o download do exemplo para realizar todos estes testes basta digitar seu nome e e-mail abaixo, ele irá gerar automaticamente um link para o download e enviará um e-mail para você se inscrever em nossa newsletter gratuita. Recebendo assim novos artigos semanalmente sobre Excel.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

8 Comentários

  1. ANDERSON MUNHOZ MADEIRA disse:

    Muito boa esta matéria, de grande utilidade. Parabéns Marcos!

  2. Herbert disse:

    Olá mestre, ótima dica

  3. MIGUELANTUNES disse:

    Excelente e muito útil!
    Grato!

  4. Leandro Pereira disse:

    Obrigado! Extremamente útil para o dia a dia!

  5. Alex Araujo disse:

    Excelente Dica Marcos. Com certeza vou aplicar em muitas tarefas e, mais importante, compartilhares com colegas de trabalho.

  6. Alex Araujo disse:

    Excelente dica Marcos. Com certeza vou aplicar em muitas tarefas e, o mais importante, compartilhar com colegas de trabalho.

  7. Uiliam disse:

    Olá todos bom dia,

    não tenho experiência no Excel e no entanto estou precisando executar uma tarefa seguinte: Na célula “A1” Tenho o seguinte texto: IVETE SANGALO Tempo de Alegria Na célula “A2” Tenho o seguinte texto: MADONNA Don’t Tell Me.

    Gostaria de uma fórmula para separar o nome do cantor e o nome da música em células diferentes.

    Penso que pode ser complexo, más, para quem tem experiência pode ser simples e então estou aqui para pedir ajuda para resolver essa tarefa. Desde já agradeço

  8. Bené disse:

    Parabéns, Marcos. Excelente aula.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.