Como filtrar dados excluindo a linha de total inferior no Excel?
No processamento diário de dados com o Excel, é comum calcular uma linha de total na parte inferior do seu conjunto de dados para resumir números-chave, como totais, médias, contagens e outras agregações. No entanto, ao usar o recurso de filtro do Excel, a linha de total geralmente está sujeita à mesma filtragem que o restante dos seus dados, o que significa que ela pode ser ocultada quando os filtros são aplicados. Em muitos cenários — especialmente em relatórios de resumo, demonstrações financeiras ou análises de dados contínuas — você pode precisar que a linha de total sempre permaneça visível na parte inferior dos seus dados, independentemente dos filtros aplicados ao restante da sua planilha, conforme demonstrado na captura de tela abaixo.
Este artigo irá guiá-lo através de vários métodos práticos para garantir que a linha de total inferior sempre permaneça visível durante a filtragem. Cada método é adequado para diferentes cenários de uso e preferências do usuário. O artigo não abrange apenas os recursos internos do Excel, mas também inclui abordagens por fórmulas e soluções programáticas VBA, fornecendo uma variedade de opções dependendo das suas necessidades.
➤ Filtrar dados excluindo a linha de total inferior criando uma tabela
➤ Filtrar dados excluindo a linha de total inferior inserindo uma fórmula
➤ Filtrar dados excluindo a linha de total inferior usando código VBA
Filtrar dados excluindo a linha de total inferior criando uma tabela
A maneira mais simples de manter sua linha de total visível enquanto filtra é converter seus dados em uma Tabela Oficial do Excel. As tabelas do Excel possuem uma função nativa de Linha de Total que foi projetada para permanecer visível na parte inferior dos seus dados mesmo enquanto você filtra outras linhas. Abaixo estão as etapas detalhadas, cenários comuns e observações para este método:
1. Se seus dados já incluem uma linha de total calculada manualmente (ou seja, uma linha com fórmulas de total não incorporadas em uma tabela), exclua esta linha temporariamente para evitar duplicação.
2. Selecione o intervalo dos seus dados reais (excluindo a linha de total criada manualmente). Em seguida, vá para Inserir > Tabela. Na caixa de diálogo Criar Tabela, certifique-se de que Minha tabela tem cabeçalhos está marcada se seus dados incluem cabeçalhos de coluna. Revise o intervalo de dados para confirmar que todas as suas linhas estão incluídas.
3. Após clicar em OK, seus dados serão formatados como uma Tabela do Excel. Enquanto a tabela estiver selecionada, vá para a guia Design (ou Design da Tabela em algumas versões). No grupo Opções de Estilo da Tabela, marque a caixa Linha de Total. O Excel adicionará automaticamente uma nova linha de total na parte inferior.
4. Agora você pode usar as setas de filtro em qualquer coluna para filtrar seus dados conforme necessário. A Linha de Total sempre aparecerá como a última linha na tabela, independentemente dos filtros que você aplicar.
Cenários Aplicáveis: Este método é ideal para conjuntos de dados que são atualizados frequentemente, compartilhados entre equipes ou usados em relatórios recorrentes — especialmente quando você se beneficia de recursos embutidos de tabela, como linhas listradas, referências estruturadas e formatação automática.
Vantagens:
- Integração perfeita com recursos integrados do Excel
- Não é necessário atualizar fórmulas quando os dados se expandem ou diminuem
- A linha de total permanece dinâmica e é automaticamente atualizada
Limitações:
- Fórmulas personalizadas na linha de total precisam ser reconfiguradas após a conversão para uma Tabela
- Conjuntos de dados grandes podem sofrer pequenas desacelerações devido à formatação em tempo real
- Certifique-se de excluir qualquer linha de total criada manualmente antes de converter para uma Tabela, caso contrário, você poderá ver totais duplicados.
- Limpe os filtros antes de adicionar ou remover dados para manter a estrutura da tabela consistente.
Desbloqueie a Magia do Excel com o Kutools AI
- Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
- Codificação VBA: Escreva e implemente código VBA sem esforço.
- Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
- Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.
Filtrar dados excluindo a linha de total inferior inserindo uma fórmula
Se você não deseja usar uma Tabela do Excel, pode manter uma linha de total manual na parte inferior dos seus dados aplicando a fórmula SUBTOTAL. A função SUBTOTAL foi especificamente projetada para funcionar dinamicamente com os filtros do Excel — seu cálculo refletirá apenas as linhas visíveis (filtradas), mas a própria fórmula não será ocultada ou excluída ao filtrar. Essa abordagem é flexível e funciona bem com intervalos de dados clássicos.
1. Na primeira célula da sua linha de total pretendida, diretamente abaixo da tabela de dados, insira a seguinte fórmula (por exemplo, se seus valores estiverem na coluna B, da linha 2 até 13):
=SUBTOTAL(9, B2:B13)Substitua B2:B13 pelo intervalo correspondente à coluna e linhas reais dos seus dados. O número 9 na fórmula especifica a função SOMA; você pode usar diferentes números de função conforme necessário (por exemplo, 1 para MÉDIA, 2 para CONTAR, etc.).
2. Pressione Enter para confirmar. A linha de total imediatamente exibirá a soma calculada com base nas linhas visíveis (não filtradas). Quando você aplicar filtros usando os botões de filtro embutidos do Excel, a linha de total permanecerá visível na parte inferior e sempre mostrará o subtotal atual apenas para as linhas visíveis.
SUBTOTAL se seus dados aumentarem ou diminuírem. Esta fórmula não se expandirá automaticamente, a menos que você use uma função de intervalo dinâmico como OFFSET ou INDEX.Resumo: A fórmula SUBTOTAL suporta uma variedade de funções agregadas (como MÉDIA, CONTAR, etc.), tornando-a flexível para linhas de resumo usadas em finanças, inventário e fluxos de trabalho de relatórios.
Filtrar dados excluindo a linha de total inferior usando código VBA
Para usuários que trabalham com conjuntos de dados maiores, ou aqueles que buscam uma abordagem automatizada para garantir que a linha de total inferior seja sempre exibida (e nunca oculta, independentemente das configurações de filtro), você pode usar VBA (Visual Basic for Applications) para gerenciar a filtragem de forma programática. Essa solução é especialmente útil em pastas de trabalho onde o intervalo de dados ou o posicionamento da linha de total muda dinamicamente, ou quando você deseja impor consistência para todos os usuários.
Este exemplo de VBA funciona verificando a última linha no intervalo de dados e forçando-a a permanecer visível, mesmo após a aplicação de filtros. Ele garante que a linha de total seja sempre exibida e pode ser reutilizado atribuindo a macro a um botão.
Etapas para usar esta solução VBA:
1. No Excel, vá para a guia Desenvolvedor. Se você não visualiza essa guia, talvez precise habilitá-la nas Opções do Excel marcando "Desenvolvedor" na seção Personalizar Faixa de Opções.
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. Depois de inserir o código, feche o editor VBA. No Excel, pressione Alt + F8, selecione AlwaysShowTotalRow e clique em Executar. A macro garantirá que a última linha (sua linha de total) seja visível mesmo após a aplicação de filtros.
• A macro assume que a linha de total é a última linha usada na coluna A. Você pode ajustar a lógica se seus dados incluem linhas em branco ou múltiplos resumos.
• Considere atribuir essa macro a um botão para fácil reutilização se seus dados mudarem frequentemente.
• Se sua pasta de trabalho contém várias seções, use um intervalo nomeado ou um marcador específico de coluna para localizar a linha de total correta de forma mais confiável.
Vantagens: Automatiza a visibilidade da linha de total, independentemente das ações do usuário ou da filtragem. Ideal para dashboards, relatórios ou pastas de trabalho compartilhadas.
Limitações: Requer uma pasta de trabalho habilitada para macros e permissões apropriadas de macros. Pode precisar de personalização para layouts de planilha mais complexos.
Resumo: Essa abordagem VBA é recomendada para ambientes automatizados onde o controle manual das linhas de total é pouco confiável ou inconveniente. Ela ajuda a manter a visibilidade dos dados de resumo em todos os casos de uso.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins