Função SUBTOTAL no Excel: Diferença entre 9 e 109 + Exemplos
A função SUBTOTAL no Excel calcula somas, médias e contagens que se atualizam quando você filtra uma lista. Ela é especialmente útil em relatórios de vendas, pois permite ver o total apenas dos registros visíveis, sem alterar a fórmula a cada filtro.
Compatibilidade: Excel para Microsoft 365, Excel 2024, 2021, 2019 e 2016, incluindo versões compatíveis para Mac. Os exemplos usam a configuração regional brasileira, com ponto e vírgula entre argumentos.
Sintaxe da função SUBTOTAL
=SUBTOTAL(núm_função;ref1;[ref2];...)O argumento núm_função define o cálculo (por exemplo, 9 para soma, 1 para média ou 3 para contagem de células preenchidas). ref1 indica o intervalo; você pode acrescentar outras referências. O comportamento diante de linhas ocultas depende do código escolhido.
Qual é a diferença entre SUBTOTAL 9 e 109?
Ambos ignoram linhas removidas por filtros. A diferença aparece quando você oculta linhas manualmente pelo menu do Excel: 9 continua incluindo essas linhas; 109 as exclui. Portanto, 109 costuma ser a escolha mais intuitiva para mostrar somente o que permanece visível.
=SUBTOTAL(9;C2:C9)=SUBTOTAL(109;C2:C9)Tabela de códigos mais usados
| Operação | Inclui linhas ocultas manualmente | Ignora linhas ocultas manualmente |
|---|---|---|
| Média | 1 | 101 |
| Contagem de números | 2 | 102 |
| Contagem de células não vazias | 3 | 103 |
| Máximo | 4 | 104 |
| Mínimo | 5 | 105 |
| Soma | 9 | 109 |
Os códigos 1 a 11 incluem linhas ocultas manualmente, enquanto os códigos 101 a 111 as ignoram. Linhas excluídas por um filtro são ignoradas nos dois grupos. Os códigos acima cobrem os cálculos mais comuns em relatórios.
Base de dados para praticar
Crie uma planilha com os cabeçalhos Pedido, Região, Valor e Status nas células A1:D1. Preencha as linhas 2 a 9 como na tabela. Digite os valores da coluna C como números e aplique formato Moeda se desejar.
| Linha | A — Pedido | B — Região | C — Valor (R$) | D — Status |
|---|---|---|---|---|
| 2 | 101 | Sul | 120,00 | Concluído |
| 3 | 102 | Norte | 80,00 | Pendente |
| 4 | 103 | Sul | 200,00 | Concluído |
| 5 | 104 | Norte | 150,00 | Concluído |
| 6 | 105 | Sul | 50,00 | Pendente |
| 7 | 106 | Norte | 100,00 | Concluído |
| 8 | 107 | Sul | 70,00 | Concluído |
| 9 | 108 | Norte | 130,00 | Pendente |
Sem filtros e sem linhas ocultas, a soma dos oito valores é R$ 900,00. Esse é o ponto de partida para conferir os resultados.
Exemplo 1: somar apenas as linhas filtradas
Na célula C11, digite:
=SUBTOTAL(109;C2:C9)Selecione A1:D9 e ative Dados → Filtro (ou Ctrl+Shift+L). Na coluna Região, mantenha apenas Sul. O total passa de R$ 900,00 para R$ 440,00 (120 + 200 + 50 + 70). Ao limpar o filtro, volta a R$ 900,00.
Exemplo 2: aplicar dois filtros simultâneos
Mantenha Região = Sul e filtre Status = Concluído. A mesma fórmula =SUBTOTAL(109;C2:C9) retorna R$ 390,00 (120 + 200 + 70). Nenhuma mudança na fórmula é necessária.
Exemplo 3: ocultar uma linha manualmente
Limpe todos os filtros e oculte manualmente a linha 4, correspondente ao pedido 103 de R$ 200,00. Compare:
=SUBTOTAL(9;C2:C9)R$ 900,00: inclui a linha 4, embora esteja oculta manualmente.
=SUBTOTAL(109;C2:C9)R$ 700,00: ignora a linha 4. Exiba novamente a linha antes de continuar os exercícios.
Exemplo 4: contar registros visíveis
=SUBTOTAL(103;A2:A9)O código 103 conta células não vazias, ignorando linhas ocultas manualmente e filtradas. Sem filtros, o resultado é 8 pedidos; ao filtrar Região = Sul, passa para 4 pedidos. Como a coluna A contém números, o código 102 também funcionaria nesse exemplo.
Exemplo 5: média dos valores visíveis
=SUBTOTAL(101;C2:C9)Sem filtros, a média é R$ 112,50 (900 ÷ 8). Com Região = Sul, a média é R$ 110,00 (440 ÷ 4). O código 101 também ignora linhas ocultas manualmente.
SUBTOTAL, SOMA e SOMASES: quando usar?
- SOMA: soma todos os valores numéricos do intervalo, mesmo os que foram ocultados por um filtro.
- SUBTOTAL: responde aos filtros ativos e pode ignorar linhas ocultas manualmente. É adequado para indicadores de listas filtráveis.
- SOMASES: calcula por critérios escritos na fórmula, como região e status, sem depender de um filtro ativo. Veja o tutorial de SOMASES.
Erros comuns e limitações
- Usar o código errado: 9 e 109 fazem soma, mas só 109 ignora linhas ocultas manualmente.
- Esperar que SUBTOTAL aplique critérios sozinho: para calcular “Sul” sem filtrar a lista, use SOMASES ou outra função com critérios.
- Somar linhas horizontais: SUBTOTAL foi projetada principalmente para intervalos verticais; ocultar colunas não equivale a ocultar linhas.
- Confundir a função com o comando Subtotal: o comando na guia Dados insere agrupamentos e fórmulas; a função SUBTOTAL pode ser digitada diretamente.
- Subtotal dentro de subtotal: subtotais aninhados na referência são ignorados para evitar contagem em duplicidade.
Exercícios para praticar
Use a mesma base A1:D9. Em cada exercício, volte a exibir todas as linhas e limpe os filtros antes de começar.
- 1. Filtre apenas a região Norte. Qual é o total com
=SUBTOTAL(109;C2:C9)? - 2. Filtre Região = Sul e Status = Concluído. Qual total aparece?
- 3. Limpe os filtros, oculte manualmente a linha 4 e compare o resultado dos códigos 9 e 109.
- 4. Filtre Região = Sul. Qual é a média dos valores visíveis com o código 101?
- 5. Filtre Região = Norte. Qual é o maior valor visível usando o código 104?
Gabarito dos exercícios
Exercício 1 — Total Norte
=SUBTOTAL(109;C2:C9)R$ 460,00: 80 + 150 + 100 + 130.
Exercício 2 — Sul e Concluído
=SUBTOTAL(109;C2:C9)R$ 390,00: 120 + 200 + 70.
Exercício 3 — Linha oculta manualmente
Com a linha 4 oculta, =SUBTOTAL(9;C2:C9) retorna R$ 900,00 e =SUBTOTAL(109;C2:C9) retorna R$ 700,00.
Exercício 4 — Média Sul
=SUBTOTAL(101;C2:C9)R$ 110,00: média de 120, 200, 50 e 70.
Exercício 5 — Maior valor Norte
=SUBTOTAL(104;C2:C9)R$ 150,00: maior valor dos pedidos da região Norte.
Próximos passos
Explore o guia de fórmulas e funções do Excel para estudar outras funções de análise, como SOMA, MÉDIA, SOMASES e SOMARPRODUTO.
Referência técnica: documentação oficial da função SUBTOTAL da Microsoft.
0 Comentários