Muitas vezes, você pode encontrar um conjunto de dados do Excel em que apenas uma célula é preenchida com dados e as células abaixo dela ficam em branco até o próximo valor.
Como mostrado abaixo:
Embora esse formato funcione para algumas pessoas, o problema com esse tipo de dado é que você não pode usá-lo para criar tabelas dinâmicas ou usá-lo em cálculos.
É fácil de corrigir!
Neste tutorial vou mostrar comoPreencha rapidamente as células até o próximo valor de preenchimento no Excel.
Você pode fazer isso facilmente usando técnicas de diálogo especiais Go-To simples, VBA ou Power Query.
Então vamos começar!
Conteúdo
Método 1 – Preencha usando a fórmula Go To Special +
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira preencher os dados nas colunas A e B.
Na coluna B, o objetivo é preencher a “impressora” até a última célula vazia abaixo dela, então quando o “scanner” iniciar, então preencher o “scanner” na célula abaixo até que a célula fique vazia.
As etapas abaixo usam ir para especial para selecionar todas as células em branco e preencher com uma fórmula simples:
- Selecione os dados a serem preenchidos (A1:D21 em nosso exemplo)
- Vá para a guia "Início"
- No grupo de edição, clique no ícone Localizar e Selecionar (isso revelará mais opções no menu suspenso)
- Clique na opção "Ir"
- Na caixa de diálogo Ir para que se abre, clique no botão Especial.Isso abrirá a caixa de diálogo "Ir para especial"
- Na caixa de diálogo Ir para Especial, clique em Branco
- Clique OK
As etapas acima selecionarão todas as células em branco no conjunto de dados (mostrado abaixo).
Entre as células em branco selecionadas, você notará que uma célula é mais brilhante que o restante da seleção.Esta célula é a célula ativa onde queremos inserir a fórmula.
Não se preocupe com a posição da célula na seleção, pois nosso método funcionará em qualquer caso.
Agora, aqui estão as etapas para preencher os dados nas células em branco selecionadas:
- Clique na tecla de sinal de igual (=) em seu teclado.Isto irá inserir um sinal de igual na célula ativa
- Pressione a tecla de seta para cima.Isso inserirá a referência de célula da célula acima da célula ativa.Por exemplo, no nosso caso, B3 é a célula ativa, e quando fazemos esses dois passos, ela insere =B2 na célula
- Mantenha pressionada a tecla Control e pressione Enter
As etapas acima inserirão automaticamente todas as células em branco com os valores acima.
Embora isso possa parecer muitas etapas, assim que você pegar o jeito, poderá preencher rapidamente os dados no Excel em segundos.
Agora, há mais duas coisas que você precisa estar ciente ao usar esse método.
Converter fórmula em valor
A primeira é certificar-se de converter fórmulas em valores (para que você tenha valores estáticos e não estrague quando alterar os dados no futuro).
Alterar formato de data
Se você usar datas em seus dados (como fiz nos meus dados de exemplo), você notará que os valores preenchidos na coluna de datas são números em vez de datas.
Se a saída na coluna de data estiver no formato de data desejado, você estará bem e não precisará fazer mais nada.
Mas se essas datas não estiverem no formato correto, será necessário um ajuste fino.Enquanto você tem o valor correto, você só precisa alterar o formato para que ele apareça como uma data na célula.
Aqui estão os passos para fazer isso:
- Selecione colunas contendo datas
- Clique na guia Início
- No grupo Números, clique no menu suspenso Formato e selecione um formato de data.
Se você precisar fazer esse preenchimento de vez em quando, recomendo esta técnica especial e de fórmula Go-To.
Embora tenha algumas etapas, é simples e fornece resultados no conjunto de dados.
Mas se você precisar fazer isso com frequência, sugiro que consulte o VBA e o método Power Query descrito a seguir
Método 2 - Preencha com 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 a um arquivo uma vez e, em seguida, pode reutilizar facilmente esse código várias vezes na mesma pasta de trabalho ou mesmo em várias pastas de trabalho em seu sistema.
Aqui está o código VBA que percorrerá cada célula na seleção e preencherá todas as células em branco:
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.
No loop For, uso uma condição If-Then para verificar se a célula está vazia.
Se a célula estiver vazia, ela será preenchida com o valor da célula acima, caso contrário, o loop for ignorará a célula e passará para a próxima célula.
Agora que você tem o código VBA, deixe-me mostrar onde colocar esse código no Excel:
- Selecione os dados a serem preenchidos
- Clique na guia do 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ê terá o Project Explorer.Se você não o vir, clique na opção Exibir no menu e clique em Explorador de Projetos
- Se você tiver várias pastas de trabalho do Excel abertas, o Project Explorer exibirá 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 do mouse em qualquer objeto na pasta de trabalho, vá para Inserir e clique em Módulo.Isto irá inserir um novo módulo para esta pasta de trabalho
- Clique duas vezes no "Módulo" que você acabou de inserir nas etapas acima.Ele 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 no 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 executarão o código VBA e seus dados serão preenchidos.
Se você quiser usar esse código VBA novamente no futuro, precisará 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 essa macro à sua Barra de Ferramentas de Acesso Rápido, que está sempre visível e você pode acessar essa macro com apenas um clique (na pasta de trabalho com o código no backend).
Assim, da próxima vez que você tiver dados para preencher, basta fazer uma seleção e clicar no botão macro na Barra de Ferramentas de Acesso Rápido.
Você também pode adicionar essa macro à sua pasta de trabalho de macro pessoal e usá-la em qualquer pasta de trabalho em seu sistema.
Clique aqui para ler mais sobre sua pasta de trabalho de macro pessoal e como adicionar código a ela.
Método 3 - Preencha usando o Power Query
Consulta de energiaPossui funcionalidade integrada que permite preenchê-lo com um único clique.
É recomendado quando você ainda estiver usando o Power Query para transformar dados ou combinar dados de várias planilhas ou várias pastas de trabalho.
Como parte de um fluxo de trabalho existente, você pode preencher rapidamente células em branco usando a opção Preencher no Power Query.
Para usar o Power Query, é recomendável que seus dados estejam no formato de tabela do Excel.Se você não puder converter os dados em uma tabela do Excel, deverá criar um intervalo nomeado para os dados e usar esse intervalo nomeado no Power Query.
Abaixo, tenho um conjunto de dados que foi convertido em uma tabela do Excel.Você pode fazer isso selecionando o conjunto de dados, acessando a guia Inserir e clicando no ícone Tabela.
Aqui estão 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 Obter e Transformar Dados, clique em Da Planilha.Isso abrirá o editor do Power Query.Observe que as células em branco exibirão o valor "null" no Power Query
- No Editor do Power Query, selecione a coluna que você deseja preencher.Para fazer isso, mantenha pressionada a tecla Control e clique no cabeçalho da coluna que deseja selecionar.Em nosso exemplo, serão as colunas de data e produto.
- Clique com o botão direito em qualquer título selecionado
- Vá para a opção Preencher e clique em Baixo.Isto irá preencher todas as células em branco com dados
- Clique na guia Arquivo e, em seguida, clique em Fechar e carregar.Isso irá inserir uma nova planilha com a tabela de resultados na pasta de trabalho
Embora essa abordagem possa parecer um exagero, um dos grandes benefícios de usar o Power Query é que ele permite que você atualize rapidamente os dados resultantes quando os dados originais forem alterados.
Por exemplo, se você adicionar mais registros aos dados originais, não precisará fazer todos os procedimentos acima novamente.Você pode clicar com o botão direito do mouse na tabela resultante e clicar em atualizar.
Embora o método do 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 com células em branco para preencher, é mais conveniente usar o método especial Go To ou o método VBA (descrito acima).
Então você pode usar esses três métodos simples paraPreencha as células em branco até o próximo valor no Excel.
Espero que você tenha achado este tutorial útil.
