Como filtrar dados sem incluir a linha total inferior no Excel?
No processamento diário de dados com o Excel, é comum incluir uma linha total na parte inferior do conjunto de dados para resumir valores-chave, como totais, médias, contagens e outras agregações. Contudo, ao utilizar a funcionalidade de filtro do Excel, essa linha total é frequentemente afetada pelo mesmo filtro aplicado aos restantes dados, podendo ficar oculta quando os filtros estão ativos. Em muitos cenários — especialmente em relatórios resumo, demonstrações financeiras ou análises contínuas de dados — é essencial que a linha total permaneça sempre visível na parte inferior dos seus dados, independentemente dos filtros aplicados ao resto da folha de cálculo, conforme ilustrado na captura de ecrã abaixo.
Este artigo orienta-o através de vários métodos práticos para garantir que a linha total inferior permaneça sempre visível durante a filtragem. Cada método adapta-se a diferentes cenários de utilização e preferências pessoais, abrangendo não só funcionalidades nativas do Excel, mas também soluções com fórmulas e abordagens programáticas em VBA — oferecendo-lhe um leque flexível de opções consoante as suas necessidades.
➤ Filtrar dados excluindo a linha total inferior ao criar uma tabela
➤ Filtrar dados excluindo a linha total inferior ao inserir uma fórmula
➤ Filtrar dados excluindo a linha total inferior utilizando código VBA
Filtrar dados excluindo a linha total inferior ao criar uma tabela
A forma mais simples de manter a linha total visível durante a filtragem é converter os seus dados numa Tabela oficial do Excel. As Tabelas do Excel incluem uma função nativa Linha Total, concebida de forma inteligente para permanecer sempre visível na parte inferior dos seus dados — mesmo quando filtra outras linhas. Abaixo, encontra os passos detalhados, cenários comuns e notas relacionadas com este método:
1. Se os seus dados já incluírem uma linha de total calculada manualmente (ou seja, uma linha com fórmulas de total que não estejam integradas numa tabela), elimine-a temporariamente para evitar duplicações.
2. Selecione o intervalo dos seus dados reais (excluindo a linha total criada manualmente). Em seguida, aceda a Inserir > Tabela. Na caixa de diálogo Criar Tabela, certifique-se de que a opção A minha tabela tem cabeçalhos está marcada, caso os seus dados incluam cabeçalhos de coluna. Revise o intervalo de dados para confirmar que todas as linhas estão incluídas.
3. Após clicar em OK, os seus dados serão formatados como uma Tabela do Excel. Com a tabela selecionada, aceda ao separador Estrutura(ou)Estrutura da Tabela em algumas versões). No grupo Opções de Estilo de Tabela, marque a caixa Linha Total. O Excel adicionará automaticamente uma nova linha total na parte inferior.
4. Pode agora usar as setas de filtro em qualquer coluna para filtrar os seus dados conforme necessário. A Linha Total aparecerá sempre como a última linha da tabela, independentemente dos filtros aplicados.
Cenários Aplicáveis: Este método é ideal para conjuntos de dados frequentemente atualizados, partilhados entre equipas ou utilizados em relatórios recorrentes — especialmente quando se beneficiam das funcionalidades incorporadas das tabelas, como linhas alternadas, referências estruturadas e formatação automática.
Vantagens:
- Integração perfeita com as funcionalidades incorporadas do Excel
- Não é necessário atualizar fórmulas quando os dados aumentam ou diminuem
- A linha total permanece dinâmica e é atualizada automaticamente
Limitações:
- fórmulas personalizadas na linha total têm de ser reconfigurados após a conversão para uma Tabela
- Conjuntos de dados grandes podem sofrer ligeiras reduções de desempenho devido à formatação em tempo real
- Certifique-se de eliminar qualquer linha de totais criada manualmente antes de converter para uma Tabela; caso contrário, poderá ver totais duplicados.
- Limpe os filtros antes de adicionar ou remover dados, para garantir uma estrutura de tabela consistente.
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
Filtrar dados excluindo a linha total inferior ao inserir uma fórmula
Se não quiser utilizar uma Tabela do Excel, pode manter uma linha de total manual na parte inferior dos seus dados ao aplicar a fórmula SUBTOTAL. A função SUBTOTAL foi concebida especificamente para funcionar dinamicamente com os filtros do Excel — o seu cálculo reflete apenas as linhas visíveis (filtradas), mas a própria fórmula não é ocultada nem excluída durante a filtragem. Esta abordagem é flexível e funciona perfeitamente com intervalos clássicos.
1. Na primeira célula da linha total pretendida, imediatamente abaixo da sua tabela de dados, introduza a seguinte fórmula (por exemplo, se os seus valores estiverem na coluna B, nas linhas 2 a 13):
=SUBTOTAL(9, B2:B13)Substitua B2:B13 pelo intervalo correspondente à coluna e às linhas reais dos seus dados. O número 9na fórmula especifica a função SOMA; pode utilizar outros números de função conforme necessário (por exemplo,)1 para MÉDIA, 2 para CONTAR, etc.).
2. Prima Enter para confirmar. A linha total apresentará imediatamente a soma calculada com base nas linhas visíveis (não filtradas). Ao aplicar filtros utilizando os botões de filtro incorporados do Excel, a linha total permanecerá visível na parte inferior e mostrará sempre o subtotal atual apenas das linhas visíveis.
SUBTOTALse os seus dados aumentarem ou diminuírem. Esta fórmula não se expandirá automaticamente, a menos que utilize uma função de intervalo dinâmico como DESLOCAMENTOou ÍNDICE.Resumo: A fórmula SUBTOTAL suporta diversas funções agregadas (como MÉDIA, CONTAR, etc.), tornando-a ideal para linhas de resumo em fluxos de trabalho financeiros, de inventário e de relatórios.
Filtrar dados excluindo a linha total inferior utilizando código VBA
Para utilizadores que trabalham com conjuntos de dados maiores ou que pretendem uma abordagem automatizada para garantir que a linha total inferior seja sempre visível — independentemente das definições de filtro —, é possível utilizar VBA (Visual Basic for Applications) para gerir programaticamente a filtragem. Esta solução revela-se particularmente útil em livros onde o intervalo de dados ou a posição da linha total muda dinamicamente, ou sempre que desejar assegurar consistência para todos os utilizadores.
Este exemplo de VBA funciona ao verificar o Última Linha no seu Intervalo de Dados e forçá-lo a permanecer visível, mesmo após aplicar filtros. Garante que a linha total seja sempre apresentada e pode ser reutilizado ao atribuir a macro a um botão.
Passos para utilizar esta solução VBA:
1. No Excel, aceda ao separador Programador. Se não vir este separador, poderá ter de o ativar nas Opções do Excel, marcando a opção «Programador» na secção Personalizar Friso.
2. Clique em Visual Basic para abrir o editor VBA. Na nova janela, clique em Inserir > Módulo e cole o seguinte código no novo módulo:
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. Após introduzir o código, feche o editor VBA. No Excel, prima Alt + F8, selecione AlwaysShowTotalRow e clique em Executar. A macro garante que a última linha (a sua linha de totais) permaneça visível mesmo após aplicar filtros.
• A macro assume que a linha total é a última linha utilizada na coluna A. Pode ajustar a lógica se os seus dados incluírem Linhas em branco ou múltiplos resumos.
• Considere atribuir esta macro a um botão para facilitar a reutilização caso os seus dados mudem frequentemente.
• Se o seu livro contiver várias secções, utilize um intervalo nomeado ou um marcador Especificar coluna para localizar a linha total correta de forma mais fiável.
Vantagens: Automatiza a visibilidade da linha total, independentemente das ações do utilizador ou da filtragem. Ideal para painéis, relatórios ou livros partilhados.
Limitações: Requer um livro com macros ativadas e as devidas permissões para executar macros. Poderá necessitar de personalização para esquemas de folha mais complexos.
Resumo: Esta abordagem VBA é ideal para ambientes automatizados, onde o controlo manual das linhas totais é pouco fiável ou inconveniente. Garante que os dados resumo permaneçam sempre visíveis, independentemente do cenário de utilização.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos