Início › Funções do Excel › SUBTOTAL
Função SUBTOTAL no Excel: somar células filtradas e visíveis
O SUBTOTAL calcula uma soma, uma contagem, uma média ou outra estatística de um intervalo, mas ignora as linhas ocultas por um filtro. Filtre sua lista por uma região e o total se atualiza para mostrar só essa região. No Excel em inglês, a função também se chama SUBTOTAL.
Neste guia
Sintaxe do SUBTOTAL
=SUBTOTAL(núm_função; ref1; [ref2]; …)| Argumento | O que significa |
|---|---|
| núm_função | Um número que diz ao SUBTOTAL qual cálculo fazer, como 9 para SOMA ou 109 para SOMA sem as linhas ocultas. Veja a tabela abaixo. |
| ref1; ref2; … | O intervalo ou os intervalos sobre os quais o cálculo é feito. |
Números de função
| Cálculo | Inclui linhas ocultas à mão | Ignora linhas ocultas à mão |
|---|---|---|
| MÉDIA | 1 | 101 |
| CONT.NÚM (números) | 2 | 102 |
| CONT.VALORES (não vazias) | 3 | 103 |
| MÁXIMO | 4 | 104 |
| MÍNIMO | 5 | 105 |
| MULT | 6 | 106 |
| DESVPAD | 7 | 107 |
| DESVPADP | 8 | 108 |
| SOMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
1–11 versus 101–111: as duas versões ignoram as linhas ocultas por um filtro. A diferença está nas linhas que você mesmo oculta (botão direito › Ocultar): 1–11 continuam incluindo essas linhas e 101–111 as deixam de fora. Na dúvida, use 101–111: sempre calculam só o que você vê.
Pratique SUBTOTAL: experimente você mesmo
Ler é uma coisa, digitar é outra. Monte a fórmula abaixo passo a passo: as dicas dizem o que digitar, e você pode clicar nas células ou arrastar sobre elas para adicioná-las, igual ao Excel.
Tarefa: Some os valores com SUBTOTAL, em B7: =SUBTOTAL(9;B2:B6)
Exemplo: somar uma lista filtrada
| A | B | C | |
|---|---|---|---|
| 1 | Região | Vendedor | Vendas |
| 2 | Norte | Ana | 1.200 |
| 3 | Sul | Bruno | 800 |
| 4 | Norte | Carlos | 950 |
| 5 | Leste | Diana | 450 |
| 6 | Sul | Ana | 700 |
| 7 | Total | 4.100 |
Sem filtro, C7 mostra 4.100, igual à SOMA. Agora filtre a coluna A por "Norte" (Dados › Filtro). As linhas 3, 5 e 6 ficam ocultas e o total muda para 2.150: só as linhas visíveis são somadas. Uma =SOMA(C2:C6) normal continuaria mostrando 4.100.
Contar ou tirar a média das linhas visíveis
- Contar as linhas visíveis:
=SUBTOTAL(103; A2:A6)conta as células visíveis não vazias. Depois de filtrar por Norte, retorna 2. - Média do que está visível:
=SUBTOTAL(101; C2:C6)retorna 1.075 para o Norte. - Maior valor visível:
=SUBTOTAL(104; C2:C6)retorna 1.200 para o Norte.
Dica: coloque as fórmulas SUBTOTAL acima da lista em vez de abaixo. Assim elas continuam à vista quando a lista cresce.
O SUBTOTAL ignora outros subtotais
Se um intervalo contém células que também usam o SUBTOTAL, essas células são puladas. Assim você pode colocar subtotais por grupo e um total geral sobre a coluna inteira sem contar nada duas vezes. É exatamente o que faz o comando Dados › Subtotal do Excel: ele insere fórmulas SUBTOTAL para cada grupo e uma para o total geral. A linha de totais de uma tabela do Excel (Ctrl+Alt+T e depois Design da Tabela › Linha de Totais) também usa o SUBTOTAL.
Totais por grupo com o botão Subtotal
O Excel também tem um botão Subtotal que insere essas fórmulas para você. Classifique a lista pela coluna que quer agrupar, por exemplo Região. Depois escolha Dados > Subtotal, ponha A cada alteração em em Região e Usar função em Soma, e marque as colunas a somar. O Excel coloca um total abaixo de cada grupo e um total geral no fim, com os botões 1, 2 e 3 à esquerda para recolher e expandir os grupos. Para desfazer tudo: Dados > Subtotal > Remover Todos.
Numa tabela formatada o botão fica cinza: converta-a antes num intervalo normal (Design da Tabela > Converter em Intervalo).
Erros comuns
- O total não muda quando você oculta linhas à mão. Você usou 1–11. Troque para 101–111.
- Colunas ocultas continuam incluídas. O SUBTOTAL só ignora linhas ocultas. Ele não pula colunas ocultas em um intervalo horizontal.
- #VALOR! O número de função não está entre 1–11 ou 101–111.
- Você também precisa ignorar erros. Use o AGREGAR, que funciona como o SUBTOTAL mas também pode pular valores de erro:
=AGREGAR(9; 7; C2:C6)
Continue aprendendo
Perguntas frequentes
Qual é a diferença entre SUBTOTAL 9 e 109?
Os dois somam só as linhas visíveis depois de filtrar. O 109 também pula as linhas que você ocultou à mão; o 9 continua incluindo essas linhas.
Como somo só as células visíveis no Excel?
Use =SUBTOTAL(109; intervalo). Ele ignora as linhas ocultas por um filtro e as ocultas à mão.
Por que usar o SUBTOTAL em vez da SOMA?
A SOMA sempre soma todas as células do intervalo. O SUBTOTAL respeita os filtros e pula outros subtotais, então dá o total certo do que estiver visível a cada momento.
O que é o AGREGAR?
O AGREGAR é uma versão mais poderosa do SUBTOTAL, disponível desde o Excel 2010. Ele oferece mais cálculos e também pode ignorar valores de erro.