Assistir ao vídeo - Preencher no Excel
Muitas vezes, você pode encontrar um conjunto de dados onde apenas uma célula é preenchida com dados e as células abaixo dele ficam em branco até o próximo valor.
Algo conforme mostrado abaixo:
Embora esse formato funcione para algumas pessoas, o problema com esse tipo de dados é que você não pode usá-lo para criar tabelas dinâmicas ou usá-lo em cálculos.
E isso tem uma solução fácil!
Neste tutorial, vou mostrar como rapidamente preencha as células no Excel até o próximo valor preenchido.
Você pode fazer isso facilmente usando uma técnica simples de caixa de diálogo especial Go-To, VBA ou Power Query.
Então vamos começar!
Método 1 - Preencher usando Go To Special + Formula
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja preencher os dados nas colunas A e B.
Na coluna B, o objetivo é preencher a ‘Impressora’ até a última célula vazia abaixo dela e, em seguida, quando o ‘Scanner’ iniciar, preencha o ‘Scanner’ nas células abaixo até que as células estejam vazias.
Abaixo das etapas a serem usadas, vá para o especial para selecionar todas as células em branco e preencher usando uma fórmula simples:
- Selecione os dados que deseja preencher (A1: D21 em nosso exemplo)
- Vá para a guia ‘Página inicial’
- No grupo de edição, clique no ícone ‘Localizar e selecionar’ (isso mostrará mais algumas opções no menu suspenso)
- Clique na opção ‘Ir para’
- Na caixa de diálogo Go-To que se abre, clique no botão Special. Isso abrirá a caixa de diálogo ‘Ir para o especial’
- Na caixa de diálogo Go To Special, clique em Blanks
- Clique OK
As etapas acima selecionariam todas as células em branco no conjunto de dados (conforme mostrado abaixo).
Nas células em branco selecionadas, você notaria que uma célula é mais clara que o resto da seleção. Esta célula é a célula ativa onde vamos inserir uma fórmula.
Não se preocupe com a posição da célula na seleção, pois nosso método funcionaria em qualquer caso.
Agora, abaixo estão as etapas para preencher os dados nas células em branco selecionadas:
- Pressione a tecla igual a (=) em seu teclado. Isto irá inserir um sinal de igual na célula ativa
- Pressione a tecla de seta para cima. Isso irá inserir a referência de célula da célula acima da célula ativa. Por exemplo, em nosso caso, B3 é a célula ativa e quando fazemos essas duas etapas, ele insere = B2 na célula
- Segure a tecla Control e pressione a tecla Enter
As etapas acima inserem automaticamente todas as células em branco com o valor acima delas.
Embora isso possa parecer muitas etapas, quando você pegar o jeito desse método, poderá preencher rapidamente os dados no Excel em alguns segundos.
Agora, há mais duas coisas que você precisa cuidar ao usar esse método.
Converter fórmulas em valores
O primeiro é certificar-se de que você converte fórmulas em valores (para que você tenha valores estáticos e as coisas não bagunçam no caso de você alterar os dados no futuro).
Abaixo está um vídeo que mostra como converter fórmulas em valores rapidamente no Excel.
Mudar o formato da data
Caso você esteja usando datas em seus dados (como estou nos dados de exemplo), você notaria que os valores preenchidos na coluna de data são números e não datas.
Se a saída na coluna de data estiver no formato de data desejado, você está bem e não precisa fazer mais nada.
Mas se não estiverem no formato de data correto, você precisará fazer um pequeno ajuste. Embora você tenha os valores corretos, você só precisa alterar o formato para que seja exibido como uma data na célula.
Abaixo estão as etapas para fazer isso:
- Selecione a coluna que contém as datas
- Clique na guia Home
- No grupo Número, clique no menu suspenso Formato e selecione o formato de data.
Se você precisar fazer esse preenchimento de vez em quando, recomendo que use esta técnica especial e de fórmula Go-To.
Embora tenha algumas etapas, é simples e fornece o resultado ali mesmo em seu conjunto de dados.
Mas no caso de você ter que fazer isso com frequência, sugiro que você dê uma olhada nos métodos VBA e Power Query abordados a seguir
Método 2 - Preencher usando um código VBA simples
Você pode usar um código de macro VBA muito simples para preencher rapidamente as células no Excel até o último valor vazio.
Você só precisa adicionar o código VBA ao arquivo uma vez e, em seguida, pode reutilizar facilmente o código várias vezes na mesma pasta de trabalho ou até mesmo em várias pastas de trabalho em seu sistema.
Abaixo está o código VBA que percorrerá cada célula da seleção e preencherá todas as células que estiverem em branco:
'Este código VBA é criado por Sumit Bansal de TrumpExcel.com Sub FillDown () para cada célula na seleção If cell = "" Then cell.FillDown End If Next End Sub
O código acima usa um loop For para percorrer cada célula na seleção.
Dentro do loop For, usei uma condição If-Then que verifica se a célula está vazia ou não.
Caso a célula esteja vazia, ela é preenchida com o valor da célula acima e, caso não esteja vazia, o loop for simplesmente ignora a célula e passa para a próxima.
Agora que você tem o código VBA, deixe-me mostrar onde colocar este código no Excel:
- Selecione os dados que deseja preencher
- Clique na guia Desenvolvedor
- Clique no ícone do Visual Basic (ou você pode usar o atalho de teclado ALT + F11). Isso abrirá o Editor do Visual Basic no Excel
- No Editor do Visual Basic, à esquerda, você teria o Explorador de Projetos. Caso não o veja, clique na opção ‘Exibir’ no menu e, em seguida, clique em Explorador de Projetos
- Caso você tenha várias pastas de trabalho do Excel abertas, o Project Explorer mostrará todos os nomes das pastas de trabalho. Localize o nome da pasta de trabalho onde você tem os dados.
- Clique com o botão direito em qualquer um dos objetos da pasta de trabalho, vá para Inserir e clique em Módulo. Isso irá inserir um novo módulo para essa pasta de trabalho
- Clique duas vezes no ‘Módulo’ que acabou de inserir na etapa acima. Irá abrir a janela de código para esse módulo
- Copie e cole o código VBA na janela de código
- Coloque o cursor em qualquer lugar do código e execute o código da macro clicando no botão verde na barra de ferramentas ou usando o atalho de teclado F5
As etapas acima executariam o código VBA e seus dados seriam preenchidos.
Se quiser usar este código VBA novamente no futuro, você precisa salvar este arquivo como uma pasta de trabalho do Excel habilitada para macro (com uma extensão .XLSM)
Outra coisa que você pode fazer é adicionar esta macro à sua Barra de Ferramentas de Acesso Rápido, que está sempre visível e você terá acesso a esta macro com um único clique (na pasta de trabalho onde você tem o código no backend).
Portanto, da próxima vez que você tiver dados que precise preencher, tudo o que você precisa fazer é fazer a seleção e clicar no botão de macro na Barra de Ferramentas de Acesso Rápido.
Você também pode adicionar essa macro à pasta de trabalho macro pessoal e, em seguida, usá-la em qualquer pasta de trabalho em seu sistema.
Clique aqui para ler mais sobre pastas de trabalho de macro pessoais e como adicionar código a elas.
Método 3 - Preencher usando o Power Query
O Power Query tem uma funcionalidade integrada que permite preencher com um único clique.
O uso do Power Query é recomendado quando você o está usando de qualquer maneira para transformar seus dados ou combinar dados de várias planilhas ou pastas de trabalho.
Como parte de seu fluxo de trabalho existente, você pode usar a opção de preenchimento no Power Query para preencher rapidamente as células em branco.
Para usar o Power Query, é recomendável que seus dados estejam em formato de tabela do Excel. Se você não pode converter seus dados em uma tabela do Excel, você terá que criar um intervalo nomeado para os dados e, em seguida, usar esse intervalo nomeado no Power Query.
Abaixo, tenho o conjunto de dados que já converti em uma tabela do Excel. Você pode fazer isso selecionando o conjunto de dados, indo para a guia Inserir e clicando no ícone Tabela.
Abaixo as etapas para usar o Power Query para preencher os dados até o próximo valor:
- Selecione qualquer célula no conjunto de dados
- Clique na guia Dados
- No grupo Get & Transform Data, clique em ‘From Sheet’. Isso abrirá o editor do Power Query. Observe que as células em branco mostrariam o valor ‘null’ no Power Query
- No editor do Power Query, selecione as colunas que deseja preencher. Para fazer isso, segure a tecla Control e clique no cabeçalho das colunas que você deseja selecionar. Em nosso exemplo, seriam as colunas Data e Produto.
- Clique com o botão direito em qualquer um dos cabeçalhos selecionados
- Vá para a opção ‘Preencher’ e clique em ‘Abaixo’. Isso preencheria os dados em todas as células em branco
- Clique na guia Arquivo e em ‘Fechar e carregar’. Isso irá inserir uma nova planilha na pasta de trabalho com a tabela resultante
Embora esse método possa soar um pouco exagerado, um grande benefício de usar o Power Query é que ele permite que você atualize rapidamente os dados resultantes, caso seus dados originais sejam alterados.
Por exemplo, se você adicionar mais registros aos dados originais, não precisará fazer tudo acima novamente. Você pode simplesmente clicar com o botão direito do mouse na tabela resultante e clicar em Atualizar.
Embora o método Power Query funcione bem, é melhor usá-lo quando você já estiver usando o Power Query em seu fluxo de trabalho.
Se você tiver apenas um conjunto de dados onde deseja preencher as células em branco, usar o método especial Go To ou o método VBA (abordado acima) seria mais conveniente.
Então, essas são três maneiras simples que você pode usar para preencha as células em branco até o próximo valor no Excel.
Espero que você tenha achado este tutorial útil.
