Aprenda como criar e utilizar a função SUBTOTALSES, subtotal condicional no Excel para trabalhar apenas com registros visíveis após filtros, permitindo cálculos dinâmicos, dashboards inteligentes e análises muito mais precisas.

Índice

1. O Problema dos Filtros no Excel
2. O que é a Função SUBTOTALSES
3. Como Funciona a SUBTOTALSES
4. Código Completo da Função
5. Sintaxe da Função
6. Exemplos Práticos
   • Retornar Dados Filtrados
   • Somar Apenas Valores Visíveis
   • Média dos Registros Filtrados
   • Maior Valor dos Dados Filtrados
   • Contar Registros Visíveis
7. Combinações com Funções Modernas
8. Aplicações em Dashboards
9. Vantagens da SUBTOTALSES
10. Conclusão
11. Perguntas Frequentes (FAQ)

O Problema dos Filtros no Excel

Quem trabalha com tabelas no Excel já percebeu uma limitação bastante comum:

Ao aplicar um filtro, funções como:

• SOMA
• MÉDIA
• MÁXIMO
• MÍNIMO
• CONT.VALORES

continuam considerando linhas ocultas pelo filtro.

Isso pode gerar resultados incorretos em relatórios, dashboards e análises de dados.

Embora a função SUBTOTAL resolva parte desse problema, ela possui algumas limitações importantes:

• Trabalha apenas com uma coluna por vez.
• Não retorna matrizes dinâmicas.
• Não se integra facilmente às novas funções do Excel 365.

Foi justamente para resolver essas limitações que surgiu a função SUBTOTALSES.

O que é a Função SUBTOTALSES?

A função SUBTOTALSES é uma função personalizada criada com LAMBDA que retorna apenas os registros visíveis de uma tabela filtrada.

Em vez de retornar um único cálculo, ela devolve uma matriz dinâmica contendo somente os dados que permanecem visíveis após a aplicação dos filtros.

Isso permite utilizar qualquer função moderna do Excel sobre os dados filtrados.

SUBTOTAL(3;referência)

para verificar se cada linha está visível.

2. Criação de uma Coluna Auxiliar

É criada uma coluna temporária contendo:

• 1 → Linha visível
• 0 → Linha oculta

3. Filtragem dos Registros

A função FILTRO remove automaticamente as linhas ocultas.

4. Retorno dos Dados

A função devolve:

• Todas as colunas
• Ou apenas colunas específicas

dependendo do parâmetro informado.

Código Completo da Função SUBTOTALSES

=LAMBDA(
tabela_entrada;
selecionar_colunas;
LET(
    tabela;tabela_entrada;
    
    colunasub;
    BYROW(
        ÍNDICE(tabela;;1);
        LAMBDA(a;SUBTOTAL(3;a))
    );
    
    junto;
    EMPILHARH(tabela;colunasub);
    
    selecionadas;
    FILTRO(
        junto;
        (ESCOLHERCOLS(
            junto;
            CONT.VALORES(
                ESCOLHERLINS(junto;1)
            )
        )=1)
    );
    
    ESCOLHERCOLS(
        selecionadas;
        SE(
            selecionar_colunas=0;
            SEQUÊNCIA(
                CONT.VALORES(
                    ESCOLHERLINS(selecionadas;1)
                )-1
            );
            selecionar_colunas
        )
    )
))

Sintaxe da Função

=SUBTOTALSES(tabela;colunas)

Argumentos

Argumento	Descrição
tabela	Intervalo ou tabela que será analisada
colunas	Número da coluna desejada ou 0 para retornar todas

Exemplo 1: Retornar Apenas os Dados Filtrados

Suponha uma tabela de vendas:

Cliente	Cidade	Valor
João	Joinville	500
Maria	Curitiba	700
Pedro	Joinville	900

Após filtrar apenas Joinville:

=SUBTOTALSES(Tabela1;0)

Resultado:

Cliente	Cidade	Valor
João	Joinville	500
Pedro	Joinville	900

Exemplo 2: Somar Apenas Valores Visíveis

Uma das aplicações mais úteis.

=SOMA(ESCOLHERCOLS(SUBTOTALSES(Tabela1;0);3))

Resultado:

1400

Somente registros visíveis são considerados.

Exemplo 3: Calcular a Média dos Registros Filtrados

=MÉDIA(ESCOLHERCOLS(SUBTOTALSES(Tabela1;0);3))

Resultado:

700

Exemplo 4: Encontrar o Maior Valor

=MÁXIMO(ESCOLHERCOLS(SUBTOTALSES(Tabela1;0);3))

Resultado:

900

Exemplo 5: Contar Registros Visíveis

=CONT.VALORES(
    ESCOLHERCOLS(
        SUBTOTALSES(Tabela1;0);
        1
    )
)

Resultado:

2

Combinando a SUBTOTALSES com Funções Modernas

ORDENAR

=ORDENAR(SUBTOTALSES(Tabela1;0);3;-1)

Ordena apenas os registros visíveis.

ÚNICO

=ÚNICO(SUBTOTALSES(Tabela1;2))

Lista valores únicos considerando apenas linhas filtradas.

FILTRO

=FILTRO(
SUBTOTALSES(Tabela1;0);
ESCOLHERCOLS(SUBTOTALSES(Tabela1;0);3)>500
)

Aplica filtros adicionais sobre os dados já filtrados.

---

CLASSIFICAR

=CLASSIFICAR(SUBTOTALSES(Tabela1;0))

Classifica somente registros visíveis.

Aplicações Práticas da SUBTOTALSES

A função é extremamente útil para:

Dashboards

Criar indicadores que respondem automaticamente aos filtros.

Relatórios Financeiros

Somar receitas, despesas e saldos apenas dos registros exibidos.

Controle de Vendas

Analisar vendedores, regiões e produtos filtrados.

Recursos Humanos

Avaliar apenas colaboradores de determinados setores.

Gestão Escolar

Filtrar turmas e gerar indicadores automáticos.

Controle de Estoque

Monitorar produtos específicos após aplicação de filtros.

Vantagens da Função SUBTOTALSES

✅ Compatível com Excel 365

✅ Funciona com Tabelas Estruturadas

✅ Retorna Matrizes Dinâmicas

✅ Elimina Colunas Auxiliares

✅ Facilita Dashboards Interativos

✅ Compatível com LAMBDA

✅ Compatível com Funções Dinâmicas

✅ Trabalha Apenas com Dados Visíveis

Perguntas Frequentes (FAQ)

A SUBTOTALSES funciona no Excel 2021?

Parcialmente.

Ela depende de funções modernas como:

• LAMBDA
• BYROW
• EMPILHARH
• ESCOLHERCOLS

Por isso funciona melhor no Excel 365 atualizado.

Posso usar com Tabelas Estruturadas?

Sim.

Exemplo:

=SUBTOTALSES(TabelaVendas;0)

Posso retornar apenas uma coluna?

Sim.

Exemplo:

=SUBTOTALSES(TabelaVendas;3)

Retorna somente a terceira coluna.

A função atualiza automaticamente quando altero os filtros?

Sim.

Sempre que um filtro for alterado, a função recalcula automaticamente.

Conclusão

A função SUBTOTALSES é uma excelente evolução da função SUBTOTAL tradicional. Ela permite extrair apenas os registros visíveis de uma tabela filtrada e utilizá-los em qualquer função moderna do Excel.

Para quem trabalha com dashboards, relatórios gerenciais, análises financeiras ou indicadores dinâmicos, essa função pode se tornar uma das ferramentas mais poderosas da sua biblioteca de funções LAMBDA.

Ao combinar SUBTOTALSES com funções como FILTRO, ÚNICO, ORDENAR, SOMA e MÉDIA, você cria soluções muito mais flexíveis, modernas e totalmente adaptáveis aos filtros aplicados pelo usuário.

Download Planilha Subtotalses Excel

Realize o download do arquivo neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.