SUBTOTAL no Excel: totais que ignoram subtotais e filtros

=SUBTOTAL(9;C2:C8) soma os números de C2:C8, como a SOMA (SUM em inglês), com duas diferenças: ela ignora qualquer outra fórmula SUBTOTAL dentro do intervalo e ignora as linhas ocultas por um filtro. O primeiro argumento, 9, diz qual cálculo fazer. A função tem o mesmo nome em inglês; a tabela mostra as fórmulas em inglês, com vírgulas, e também aceita a forma em português, com ponto e vírgula.

FórmulasRestaurar

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SUBTOTAL(9;C2:C7)

O total geral em C8 cobre a coluna inteira, linhas de subtotal incluídas, e ainda mostra 445: a SUBTOTAL deixa C4 e C7 de fora porque elas têm fórmulas SUBTOTAL. F2 faz o mesmo com a SOMA e mostra 890, cada venda contada duas vezes. Com SUBTOTAL em todas as linhas de total, você pode acrescentar ou mover grupos sem reescrever o total geral.

Números de função da SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], ...)

Quando você digita =SUBTOTAL(, o Excel mostra essa lista, então não precisa decorar. 9 e 109 (SOMA), 1 (MÉDIA) e 103 (contar linhas visíveis) são os mais usados.

FórmulasRestaurar

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SUBTOTAL(1;C2:C7)

Nada está oculto aqui, então cada linha é igual à função comum: uma média de 88.33, 6 linhas, um máximo de 200, um mínimo de 30 e uma soma de 530. A diferença só aparece quando há linhas ocultas, que é o assunto da próxima seção.

SUBTOTAL 9 ou 109, e linhas filtradas

Ligue um filtro em Dados > Filtro (Ctrl+Shift+L, Cmd+Shift+F no Mac) e escolha North na lista suspensa de Region. As linhas das outras regiões ficam ocultas:

• =SOMA(C2:C7) continua somando as seis linhas.
• =SUBTOTAL(9;C2:C7) e =SUBTOTAL(109;C2:C7) somam só as linhas visíveis de North.
• =SUBTOTAL(103;A2:A7) conta as linhas que ficaram na tela: 2, o mesmo número de "2 de 6 registros localizados" na barra de status.

As duas famílias só diferem nas linhas que você oculta à mão (selecione as linhas, botão direito > Ocultar). O 9 continua somando essas linhas; o 109 não. Se o total precisa sempre bater com o que está na tela, use 109. Se você oculta linhas só para arrumar a visualização e ainda quer que elas entrem no total, use 9.

A SUBTOTAL só trabalha com linhas. Colunas ocultas sempre entram, então =SUBTOTAL(109;B2:G2) ao longo de uma linha também soma as colunas ocultas.

O jeito mais rápido de ter uma SUBTOTAL é o botão AutoSoma com um filtro ligado: o Excel escreve =SUBTOTAL(9;...) no lugar da SOMA. Dados > Subtotal vai além: em uma lista ordenada por uma coluna, ele insere uma linha de total abaixo de cada grupo e um total geral, todos com SUBTOTAL, mais os botões de estrutura de tópicos para recolher os grupos.

AGREGAR: uma SUBTOTAL que ignora erros

Se uma célula do intervalo tem um erro, a SOMA e a SUBTOTAL retornam esse erro. A AGREGAR (AGGREGATE em inglês, Excel 2010 e posterior) é uma SUBTOTAL com um argumento extra de opções; a opção 6 ignora valores de erro.

FórmulasRestaurar

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =AGREGAR(9;6;C2:C7)

C3 tem #N/A, então F2 também mostra #N/A (no Excel em português, #N/D; a tabela mostra os nomes de erro em inglês). F3 o ignora e soma os outros cinco: 450. O primeiro argumento usa os mesmos números da SUBTOTAL (9 é SOMA, 4 é MÁXIMO). Outras opções: 5 ignora linhas ocultas, 7 ignora linhas ocultas e erros, 3 ignora linhas ocultas, erros e fórmulas SUBTOTAL e AGREGAR aninhadas. Troque C3 por um número e F2 mostra o mesmo total que F3.

Prática: um total geral sobre subtotais

FórmulasRestaurar

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: A lista tem um subtotal abaixo de cada região. Coloque em C9 um total geral que cubra C2:C8 sem contar duas vezes as linhas de subtotal.

VerificarVer resposta

Por que um total com SUBTOTAL ainda está errado

• Os totais dos grupos usam SOMA. A SUBTOTAL ignora outras fórmulas SUBTOTAL dentro do intervalo, não fórmulas SOMA. Um total de grupo escrito como =SOMA(C2:C3) é contado de novo. Troque todas as linhas de total por SUBTOTAL.
• As linhas foram ocultas à mão e o número da função é 9. Use 109.
• Os dados estão em colunas, não em linhas. Colunas ocultas nunca são ignoradas.
• Você precisa de uma condição, não de um filtro. A SUBTOTAL acompanha o que o filtro oculta. Para totalizar North sem filtrar, use a SOMASE. Para um resumo de todos os grupos de uma vez, uma tabela dinâmica faz isso sem linhas de total nos dados.