A função soma ao filtrar dados é muito importante para usuários do Microsoft Excel . O artigo resumirá para você como filtrar e calcular totais no Excel usando o Subtotal.

Embora seja útil saber a soma de uma coluna da planilha, você pode querer saber a soma de grupos diferentes. A função subtotal no Excel é uma maneira prática de calcular valores menores, separados do total final. É relativamente fácil de usar em planilhas, ao mesmo tempo que permite organizar seus dados.
A função subtotal no Excel é uma fórmula que calcula um intervalo de valores com base em uma operação especificada. Ele aparece como =SUBTOTAL(núm_da_função, ref1, [ref2],...), onde núm_da_função se refere à operação que você deseja que a fórmula execute. ref1, [ref2],... são os valores que você deseja que o Excel inclua no cálculo. Um dos usos mais comuns da função subtotal é somar uma coluna, mas ela também pode executar as seguintes operações:
- Médio
- Contar
- Contar tudo
- Valor máximo
- Valor mínimo
- Multiplicação
- Desvio padrão da população
- Desvio padrão da amostra
- Variância da população
- Variância amostral
O tutorial foi feito no Excel 2019, porém você ainda pode aplicar esse cálculo em outras versões do Excel, como Excel 2007, Excel 2010, Excel 2013, Excel 2016, porque o tutorial usa a função Subtotal do Excel.
Calcular o valor total de uma lista filtrada usando a função Subtotal no Excel
Suponha que temos uma tabela de dados como a seguinte, com filtros já criados.
STT |
Tipo de produto |
Nome do produto |
Quantidade |
Preço unitário |
Montante total |
1 |
Livro |
Exercícios de matemática 3ª série |
2 |
10000 |
20000 |
2 |
Estojo de lápis |
Caixa de lápis de tecido TL |
2 |
30000 |
60000 |
3 |
Lápis |
Lápis de parágrafo TL |
5 |
15000 |
75000 |
4 |
Livro |
Matemática 3ª série |
3 |
18000 |
54000 |
5 |
Livro |
Inglês 3º ano |
3 |
19000 |
57000 |

Após filtrar a lista de produtos pertencentes à categoria livros, temos a seguinte planilha:

O requisito é calcular o custo total dos livros. Se você usar a função SUM() na tabela de dados filtrados acima, o resultado obtido não será o preço total dos livros, mas o preço total de todos os produtos.
Neste caso, usamos a função SUBTOTAL da seguinte forma, na célula E13, você digita:
=SUBTOTAL(9,H7:H11)
Em que 9 é o valor do argumento correspondente à função a ser usada, aqui queremos calcular a soma, então a função a ser usada é SUM, você pode ver na tabela abaixo, H7:H11 é o intervalo para calcular a soma.
O resultado retornou que o custo total dos livros é 131.000.

Sobre a função SUBTOTAL()
A função SUBTOTAL() examinará toda a lista de valores na coluna D e calculará apenas os valores que satisfazem o filtro. Você pode olhar a imagem acima e imaginar que é porque declaramos o argumento 9. No entanto, esse argumento informa ao Excel que queremos calcular a SOMA dos valores referenciados. A tabela a seguir lista os argumentos aceitos:
Incluindo valores ocultos |
Ignorar valores ocultos |
Mandíbula |
1 |
101 |
MÉDIA() |
2 |
102 |
CONTAR() |
3 |
103 |
CONT.valores() |
4 |
104 |
MÁXIMO() |
5 |
105 |
MÍNIMO() |
6 |
106 |
PRODUTO() |
7 |
107 |
DESVPAD() |
8 |
108 |
DESVPAD() |
9 |
109 |
SOMA() |
10 |
110 |
VAR() |
11 |
111 |
VARP() |
Depois de olhar a tabela acima, você pode estar se perguntando qual é a diferença entre 9 e 109. Quando usamos o argumento 9, a função SUBTOTAL() somará todos os valores ocultos. Quando usamos o argumento 109, a função SUBTOTAL() ignorará valores ocultos. Precisamos distinguir claramente entre valores ocultos e valores que são excluídos porque não satisfazem o filtro . É possível ocultar uma linha clicando com o botão direito do mouse na ordem das linhas e selecionando Ocultar . Isso é completamente diferente de linhas que não são exibidas porque não satisfazem o filtro.
A função SUBTOTAL() também é usada para executar muitas outras tarefas úteis. Você pode consultar o tutorial sobre a função SUBTOTAL do Quantrimang.com.
Veja também: