Conteúdo
Linhas e colunas vazias podem ser um problema em tabelas em muitos casos. As funções padrão para classificar, filtrar, resumir, criar tabelas dinâmicas, etc. percebem linhas e colunas vazias como uma quebra de tabela, sem pegar os dados localizados mais atrás delas. Se houver muitas dessas lacunas, removê-las manualmente pode ser muito caro e não funcionará para remover tudo de uma vez "a granel" usando filtragem, porque o filtro também "tropeçará" nos intervalos.
Vejamos várias maneiras de resolver esse problema.
Método 1. Procure células vazias
Esta pode não ser a mais conveniente, mas definitivamente a maneira mais fácil é digna de menção.
Suponha que estamos lidando com uma tabela contendo muitas linhas e colunas vazias dentro (destacada para maior clareza):
Suponha que temos certeza de que a primeira coluna de nossa tabela (coluna B) sempre contém o nome de uma cidade. As células vazias nesta coluna serão um sinal de linhas vazias desnecessárias. Para removê-los rapidamente, faça o seguinte:
- Selecionar intervalo com cidades (B2:B26)
- Pressione a tecla F5 e depois pressione Destaques (Ir para Especial) ou selecione na guia Home — Localizar e Selecionar — Selecione um grupo de células (Início — Localizar e selecionar — Ir para especial).
- Na janela que se abre, selecione a opção Células vazias (Em branco) e imprensa OK – todas as células vazias na primeira coluna da nossa tabela devem ser selecionadas.
- Agora selecione na guia INÍCIO Command Excluir – Excluir linhas da planilha (Excluir — Excluir linhas) ou pressione o atalho de teclado Ctrl+menos – e nossa tarefa está resolvida.
Claro, você pode se livrar de colunas vazias exatamente da mesma maneira, usando o cabeçalho da tabela como base.
Método 2: pesquisar linhas vazias
Como você já deve ter percebido, o método anterior só funcionará se nossos dados contiverem necessariamente linhas e colunas totalmente preenchidas, que podem ser enganchadas ao procurar células vazias. Mas e se não houver essa confiança e os dados também puderem conter células vazias?
Dê uma olhada na tabela a seguir, por exemplo, para esse caso:
Aqui a abordagem será um pouco mais complicada:
- Digite na célula A2 a função CONTAGEM (CONTAGEM), que calculará o número de células preenchidas na linha à direita e copiará esta fórmula para toda a tabela:
- Selecione a célula A2 e ative o filtro com o comando Dados - Filtro (Dados - Filtro) ou atalho de teclado Ctrl+Shift+L.
- Vamos filtrar os zeros pela coluna calculada, ou seja, todas as linhas onde não há dados.
- Resta selecionar as linhas filtradas e excluí-las com o comando Home — Excluir -' Excluir linhas da planilha (Início — Excluir — Excluir linhas) ou atalho de teclado Ctrl+menos.
- Desligamos o filtro e obtemos nossos dados sem linhas vazias.
Infelizmente, esse truque não pode mais ser feito com colunas – o Excel ainda não aprendeu a filtrar por colunas.
Método 3. Macro para remover todas as linhas e colunas vazias de uma planilha
Você também pode usar uma macro simples para automatizar essa tarefa. Pressione o atalho de teclado outro+F11 ou selecione na guia revelador - Visual básico (Desenvolvedor — Editor do Visual Basic). Se guias revelador não está visível, você pode habilitá-lo através Arquivo – Opções – Configuração da fita (Arquivo — Opções — Personalizar Faixa de Opções).
Na janela do editor Visual Basic que se abre, selecione o comando de menu Inserir - Módulo e no módulo vazio que aparece, copie e cole as seguintes linhas:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Não é nada Então Set rng = Linhas(r) Else Set rng = União(rng, Linhas(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Então se rng não é nada, então Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
Feche o editor e retorne ao Excel.
Agora aperte combinação outro+F8 ou botão Macros aba revelador. A janela que se abre listará todas as macros atualmente disponíveis para execução, incluindo a macro que você acabou de criar. Excluir Vazio. Selecione-o e clique no botão Execute (corre) – todas as linhas e colunas vazias da planilha serão excluídas instantaneamente.
Método 4: consulta de energia
Outra forma de resolver nosso problema e um cenário muito comum é remover linhas e colunas vazias no Power Query.
Primeiro, vamos carregar nossa tabela no Editor de Consultas do Power Query. Você pode convertê-lo em um dinâmico “inteligente” com o atalho de teclado Ctrl+T ou apenas selecionar nosso intervalo de dados e dar um nome (por exemplo Data) na barra de fórmulas, convertendo para nomeado:
Agora usamos o comando Data – Get data – From table/range (Data – Get Data – From table/range) e carregamos tudo no Power Query:
Então tudo é simples:
- Excluímos linhas vazias com o comando Home – Reduzir linhas – Excluir linhas – Excluir linhas vazias (Home – Remover Linhas – Remover linhas vazias).
- Clique com o botão direito do mouse no cabeçalho da primeira coluna Cidade e selecione o comando Unpivot Other Columns no menu de contexto. Nossa mesa será, como é tecnicamente chamada corretamente, normalizado – convertido em três colunas: cidade, mês e valor da intersecção da cidade e mês da tabela original. A peculiaridade dessa operação no Power Query é que ela pula células vazias nos dados de origem, que é o que precisamos:
- Agora realizamos a operação inversa – transformamos a tabela resultante em uma tabela bidimensional para devolvê-la à sua forma original. Selecione a coluna com meses e na guia Transformação escolha um time Coluna Pivot (Transformar - Coluna Dinâmica). Na janela que se abre, como coluna de valores, selecione o último (Value), e nas opções avançadas – a operação Não agregue (Não agregue):
- Resta carregar o resultado de volta para o Excel com o comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…)
- O que é uma macro, como funciona, onde copiar o texto de uma macro, como executar uma macro?
- Preenchendo todas as células vazias da lista com os valores das células pai
- Removendo todas as células vazias de um determinado intervalo
- Removendo todas as linhas vazias em uma planilha com o complemento PLEX