Aprenda a usar a função SUBTOTAL do Excel para realizar cálculos automáticos em dados filtrados. Guia completo com exemplos práticos, fórmulas prontas e dicas para análise de planilhas. Domine essa função essencial!
A função SUBTOTAL é uma das ferramentas mais poderosas e subestimadas do Microsoft Excel. Diferente de funções comuns como SOMA ou MÉDIA, a SUBTOTAL foi projetada especificamente para trabalhar com dados filtrados, ignorando automaticamente linhas ocultas e fornecendo resultados precisos mesmo quando você aplica filtros à sua planilha.
Se você já teve problemas ao somar valores em uma tabela filtrada e percebeu que o resultado incluía dados ocultos, a função SUBTOTAL é a solução perfeita. Neste guia completo, você aprenderá como usar essa função de forma eficiente, com exemplos práticos que podem ser aplicados imediatamente no seu trabalho diário.
Índice
- O Que É a Função SUBTOTAL?
- Tabela de Códigos da Função SUBTOTAL
- Exemplos Práticos com Tabelas Prontas
- Combinando SUBTOTAL com Filtros Automáticos
- Fórmulas Combinadas Úteis
- Vantagens da Função SUBTOTAL
- Desvantagens da Função SUBTOTAL
- Dicas Profissionais
- Conclusão
- Perguntas Frequentes
- 1. Qual a diferença entre SUBTOTAL e SOMA?
- 2. Quando devo usar código 9 versus código 109?
- 3. Posso usar SUBTOTAL com critérios como SOMASE?
- 4. Por que minha fórmula SUBTOTAL não atualiza quando filtro os dados?
- 5. Posso usar múltiplas funções SUBTOTAL na mesma planilha?
- 6. A função SUBTOTAL funciona com Tabelas Dinâmicas?
- 7. Como faço para mostrar "0" em vez de erro quando não há dados visíveis?
- 8. A SUBTOTAL funciona com dados não contíguos?
O Que É a Função SUBTOTAL?
A função SUBTOTAL executa cálculos específicos (como soma, média, contagem) em um intervalo de células, com a capacidade especial de ignorar linhas ocultas por filtros. Ela aceita 11 diferentes operações matemáticas e estatísticas. E aqui, vamos utilizar a função SUBTOTAL para Calcular Dados Filtrados em uma planilha!
Sintaxe:
=SUBTOTAL(núm_função; ref1; [ref2]; ...)Parâmetros:
- núm_função: número de 1 a 11 (ou 101 a 111) que especifica qual função usar
- ref1, ref2...: intervalos de células para calcular
Tabela de Códigos da Função SUBTOTAL
| Código (inclui ocultas manualmente) | Código (ignora todas ocultas) | Função Equivalente | Descrição |
|---|---|---|---|
| 1 | 101 | MÉDIA | Calcula a média aritmética |
| 2 | 102 | CONT.NÚM | Conta células com números |
| 3 | 103 | CONT.VALORES | Conta células não vazias |
| 4 | 104 | MÁXIMO | Retorna o maior valor |
| 5 | 105 | MÍNIMO | Retorna o menor valor |
| 6 | 106 | MULT | Multiplica os valores |
| 7 | 107 | DESVPAD | Desvio padrão (amostra) |
| 8 | 108 | DESVPADP | Desvio padrão (população) |
| 9 | 109 | SOMA | Soma os valores |
| 10 | 110 | VAR | Variância (amostra) |
| 11 | 111 | VARP | Variância (população) |
Importante: Códigos de 1-11 ignoram apenas linhas ocultas por filtros. Códigos de 101-111 ignoram TODAS as linhas ocultas (filtros e ocultação manual). Cada código pode ser inserido na função SUBTOTAL para calcular dados filtrados em diferentes situações.
Exemplos Práticos com Tabelas Prontas
Exemplo 1: Vendas por Região
Você pode copiar esta tabela e colar em sua planilha de Excel, na célula A1 e exclua a linha com as letras de cabeçalhos A, B, C e D.
Tabela de Dados:
| A | B | C | D |
|---|---|---|---|
| Vendedor | Região | Produto | Valor |
| João | Sul | Notebook | 45900,00 |
| Maria | Leste | Mouse | 2400,00 |
| Pedro | Oeste | Teclado | 6900,00 |
| Ana | Norte | Monitor | 38000,00 |
| Carlos | Sul | Notebook | 50000,00 |
| Julia | Norte | Mouse | 2000,00 |
| Roberto | Leste | Monitor | 15000,00 |
| Fernanda | Norte | Teclado | 7000,00 |
Fórmulas Práticas:
=SUBTOTAL(9;D2:D9)Esta fórmula soma todos os valores da coluna D. Quando você filtrar por região "Sul", o resultado será automaticamente atualizado para mostrar apenas a soma das vendas do Sul.
=SUBTOTAL(109;D2:D9)Mesma função soma, mas ignora também linhas ocultas manualmente (código 109) na função SUBTOTAL para calcular dados filtrados.
=SUBTOTAL(1;D2:D9)Calcula a média dos valores na coluna D, considerando apenas linhas visíveis após filtros.
=SUBTOTAL(2;D2:D9)Conta quantas células contêm números na coluna D (apenas linhas visíveis).
Exemplo 2: Controle de Estoque
Tabela de Dados:
| A | B | C | D |
|---|---|---|---|
| Código | Produto | Categoria | Quantidade |
| 001 | Arroz 5kg | Alimentos | 150 |
| 002 | Feijão 1kg | Alimentos | 200 |
| 003 | Detergente | Limpeza | 80 |
| 004 | Sabão em Pó | Limpeza | 95 |
| 005 | Macarrão | Alimentos | 180 |
| 006 | Desinfetante | Limpeza | 65 |
Fórmulas para Análise:
=SUBTOTAL(9;D2:D7)Soma total de itens em estoque (atualiza automaticamente ao filtrar por categoria).
=SUBTOTAL(4;D2:D7)Mostra a quantidade máxima em estoque entre os produtos visíveis na função SUBTOTAL para calcular dados filtrados.
=SUBTOTAL(5;D2:D7)Mostra a quantidade mínima em estoque entre os produtos visíveis.
="Total de produtos: " & SUBTOTAL(3;A2:A7)Conta quantos produtos estão visíveis após aplicar filtros.
Exemplo 3: Análise de Desempenho de Funcionários
Tabela de Dados:
| A | B | C | D |
|---|---|---|---|
| Nome | Departamento | Vendas | Meta Atingida |
| Lucas | Vendas | 45000 | Sim |
| Carla | Marketing | 0 | Não |
| Bruno | Vendas | 52000 | Sim |
| Paula | TI | 0 | Não |
| Ricardo | Vendas | 38000 | Não |
| Juliana | Vendas | 61000 | Sim |
Fórmulas Avançadas:
=SUBTOTAL(9;C2:C7)Soma total de vendas (filtre por "Departamento = Vendas" para ver apenas vendas desse setor).
=SUBTOTAL(1;C2:C7)Média de vendas considerando apenas linhas visíveis, na função SUBTOTAL para calcular dados filtrados.
=SUBTOTAL(102;D2:D7)Conta quantos funcionários estão visíveis (útil após filtrar por departamento).
Combinando SUBTOTAL com Filtros Automáticos
Passo a passo para usar com filtros:
- Selecione sua tabela de dados
- Vá em Dados > Filtro (ou pressione Ctrl+Shift+L)
- Insira a fórmula SUBTOTAL abaixo da tabela
- Aplique filtros nas colunas desejadas
- Observe os resultados atualizarem automaticamente
Exemplo prático:
=SUBTOTAL(9;D2:D100)Coloque esta fórmula na célula D101. Ao filtrar qualquer coluna da sua tabela, a soma será recalculada automaticamente considerando apenas as linhas visíveis.
Fórmulas Combinadas Úteis
Soma com condição de filtro:
=SUBTOTAL(9;D2:D50)Média de valores filtrados:
="Média: " & TEXTO(SUBTOTAL(1;D2:D50);"R$ #.##0,00")Percentual sobre o total:
=SUBTOTAL(9;D2:D50)/SOMA(D2:D50)*100&"%"Contagem de itens visíveis vs total:
=SUBTOTAL(103;A2:A50) & " de " & CONT.VALORES(A2:A50) & " itens visíveis"Vantagens da Função SUBTOTAL
Atualização Automática: Os cálculos são recalculados automaticamente quando você aplica ou remove filtros, eliminando erros manuais.
Versatilidade: Uma única função substitui 11 funções diferentes (SOMA, MÉDIA, MÁXIMO, etc.), simplificando suas fórmulas. É a melhor maneira na função SUBTOTAL para calcular dados filtrados.
Compatibilidade com Filtros: Funciona perfeitamente com filtros automáticos do Excel, ignorando linhas ocultas sem necessidade de configurações adicionais.
Aninhamento Seguro: Você pode usar várias funções SUBTOTAL na mesma planilha sem que uma interfira no cálculo da outra (evita contagem dupla).
Flexibilidade: Os códigos 101-111 permitem controle adicional sobre como linhas ocultas manualmente devem ser tratadas.
Dashboards Dinâmicos: Ideal para criar painéis e relatórios que se atualizam conforme o usuário interage com os filtros.
Economia de Tempo: Elimina a necessidade de criar múltiplas fórmulas complexas com SE e E/OU para filtrar dados.
Desvantagens da Função SUBTOTAL
Curva de Aprendizado: Requer memorização dos códigos numéricos ou consulta frequente à tabela de referência.
Limitação de Funções: Oferece apenas 11 operações predefinidas; não é possível criar cálculos personalizados complexos.
Não Funciona com Todas as Ferramentas: Não interage com Tabelas Dinâmicas da mesma forma que interage com filtros automáticos.
Código Críptico: Fórmulas como =SUBTOTAL(109;A1:A50) são menos intuitivas do que =SOMA(A1:A50) para usuários iniciantes.
Referências Absolutas: Ao copiar fórmulas, é necessário cuidado extra com referências de células para manter a funcionalidade.
Incompatibilidade Parcial: Versões muito antigas do Excel podem não suportar os códigos 101-111.
Confusão com Ocultação Manual: A diferença entre códigos 1-11 e 101-111 pode gerar confusão sobre quais linhas serão ignoradas.
Dicas Profissionais
Use nomes de intervalo: Em vez de =SUBTOTAL(9;D2:D100), defina um nome como "Vendas" e use =SUBTOTAL(9;Vendas) para facilitar a leitura.
Combine com formatação condicional: Destaque células com valores acima da média calculada pelo SUBTOTAL.
Crie modelos reutilizáveis: Salve planilhas com SUBTOTAL configurado como templates para relatórios recorrentes.
Documente seus códigos: Adicione um comentário na célula explicando qual código você usou e por quê.
Conclusão
A função SUBTOTAL é uma ferramenta essencial para qualquer pessoa que trabalha com análise de dados no Excel. Sua capacidade de calcular automaticamente considerando apenas dados visíveis após filtros a torna indispensável para relatórios dinâmicos, dashboards e análises exploratórias.
Embora exija um pequeno investimento de tempo para memorizar os códigos das funções, os benefícios em termos de produtividade e precisão compensam amplamente esse esforço inicial. Ao dominar a SUBTOTAL, você estará equipado para criar planilhas mais inteligentes e responsivas, que se adaptam automaticamente às necessidades de análise.
Comece aplicando os exemplos práticos apresentados neste guia em suas próprias planilhas e experimente diferentes combinações de códigos e intervalos. Com a prática, a função SUBTOTAL se tornará uma aliada natural no seu dia a dia com o Excel.
Perguntas Frequentes
1. Qual a diferença entre SUBTOTAL e SOMA?
A função SOMA calcula o total de todas as células em um intervalo, incluindo linhas ocultas por filtros. Já a SUBTOTAL com código 9 ou 109 soma apenas as células visíveis após aplicação de filtros. Isso torna a SUBTOTAL ideal para trabalhar com dados filtrados, pois ela atualiza automaticamente o resultado conforme você mostra ou oculta linhas.
2. Quando devo usar código 9 versus código 109?
Use código 9 quando quiser somar apenas linhas visíveis após filtros automáticos, mas incluir linhas que você ocultou manualmente. Use código 109 quando quiser ignorar TODAS as linhas ocultas, independentemente de terem sido ocultadas por filtros ou manualmente. A regra geral: códigos 1-11 ignoram apenas filtros; códigos 101-111 ignoram tudo.
3. Posso usar SUBTOTAL com critérios como SOMASE?
A função SUBTOTAL não possui parâmetros para critérios condicionais como SOMASE. Para obter funcionalidade similar, você precisa primeiro aplicar filtros automáticos na sua tabela para mostrar apenas as linhas que atendem seus critérios, e então usar SUBTOTAL para calcular. Alternativamente, você pode combinar SUBTOTAL com fórmulas de matriz ou usar a função SOMA.SE.S em conjunto com técnicas avançadas.
4. Por que minha fórmula SUBTOTAL não atualiza quando filtro os dados?
Verifique se você aplicou Filtros Automáticos corretamente (Dados > Filtro). A SUBTOTAL só responde a linhas ocultas por filtros ou ocultação manual, não a outros métodos. Certifique-se também de que o intervalo da fórmula inclui todas as células que você deseja calcular e que você está usando o código correto (9 para soma, 1 para média, etc.).
5. Posso usar múltiplas funções SUBTOTAL na mesma planilha?
Sim, você pode usar quantas funções SUBTOTAL precisar na mesma planilha. Uma grande vantagem da SUBTOTAL é que ela não conta outras células SUBTOTAL nos seus cálculos, evitando contagem dupla. Isso permite criar estruturas de relatórios complexas com subtotais parciais e totais gerais sem interferência entre as fórmulas.
6. A função SUBTOTAL funciona com Tabelas Dinâmicas?
A função SUBTOTAL é mais adequada para uso com filtros automáticos em intervalos de dados regulares. Para Tabelas Dinâmicas, o Excel já possui suas próprias funções de agregação integradas que funcionam melhor nesse contexto. No entanto, você pode usar SUBTOTAL em campos calculados dentro de Tabelas Dinâmicas se necessário, mas geralmente não é a abordagem recomendada.
7. Como faço para mostrar "0" em vez de erro quando não há dados visíveis?
Você pode usar a função SE.ERRO para tratar situações onde todos os dados estão ocultos: =SE.ERRO(SUBTOTAL(9;D2:D50);0). Isso retornará 0 quando não houver células visíveis para calcular, em vez de mostrar um valor de erro. Você também pode usar SE.ERRO(SUBTOTAL(9;D2:D50);"Sem dados") para exibir uma mensagem personalizada.
8. A SUBTOTAL funciona com dados não contíguos?
Sim, você pode usar múltiplos intervalos separados por ponto e vírgula: =SUBTOTAL(9;A2:A10;C2:C10;E2:E10). Isso permite calcular valores de colunas diferentes ou intervalos não consecutivos, mantendo a funcionalidade de ignorar linhas ocultas. É útil quando sua estrutura de dados tem colunas intercaladas que você deseja incluir no cálculo.
