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 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.

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 -.

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:

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.

 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.

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.

Baixe a planilha

Abraço

Marcos Rieper

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: