Aprender Excel
PT
Define como números, datas e fórmulas aparecem, para que tudo funcione como no seu próprio Excel.

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
  1. Sintaxe do SUBTOTAL
  2. Números de função
  3. Experimente você mesmo
  4. Somar dados filtrados
  5. Contar linhas visíveis
  6. Sem contar duas vezes
  7. Totais por grupo com o botão Subtotal
  8. Erros comuns
  9. Continue aprendendo
  10. Perguntas frequentes

Sintaxe do SUBTOTAL

=SUBTOTAL(núm_função; ref1; [ref2]; …)
ArgumentoO que significa
núm_funçãoUm 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álculoInclui linhas ocultas à mãoIgnora linhas ocultas à mão
MÉDIA1101
CONT.NÚM (números)2102
CONT.VALORES (não vazias)3103
MÁXIMO4104
MÍNIMO5105
MULT6106
DESVPAD7107
DESVPADP8108
SOMA9109
VAR10110
VARP11111

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

ABC
1RegiãoVendedorVendas
2NorteAna1.200
3SulBruno800
4NorteCarlos950
5LesteDiana450
6SulAna700
7Total4.100
=SUBTOTAL(109; C2:C6)

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)

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.