Quando sua planilha do Excel contém linhas ocultas, dados filtrados ou dados agrupados, use a função SUBTOTAL do Excel. A função SUBTOTAL pode incluir ou excluir valores ocultos nos cálculos. Além de encontrar o total de um grupo de dados, o Excel pode calcular a média, o máximo, o mínimo, o desvio padrão e a variação de seus dados. Veja como inserir subtotais no Excel.
As instruções neste artigo se aplicam ao Excel para Microsoft 365, Excel 2019 e Excel 2016.
Sintaxe da Função SUBTOTAL
Use a função SUBTOTAL no Excel para resumir os valores em uma planilha de diferentes maneiras. É especialmente útil quando sua planilha contém linhas ocultas que você deseja incluir no cálculo.
A sintaxe da função SUBTOTAL é: SUBTOTAL(function_num, ref1, ref2, …)
O argumento function_num é necessário e especifica o tipo de operação matemática a ser usada para o subtotal. A função SUBTOTAL pode adicionar números, calcular o valor médio de números selecionados, encontrar os valores máximo e mínimo em um intervalo, contar o número de valores em um intervalo selecionado e muito mais.
A função SUBTOTAL ignora células que não contêm dados e células com valores não numéricos.
Este argumento é um número e depende se você deseja incluir linhas ocultas no resultado ou excluir linhas ocultas do resultado. Essas linhas podem ser ocultadas manualmente ou ocultadas por um filtro.
Os argumentos function_num incluem:
| Tarefa de Função | function_num | function_num |
|---|---|---|
| (inclui valores ocultos) | (exclui valores ocultos) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| CONTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUTO | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SOMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Os argumentos de referência núm_função de 1 a 11 incluem apenas valores em linhas ocultas ao usar o comando Ocultar para ocultar linhas. Ao usar o comando Filtro, os cálculos SUBTOTAL não incluem resultados de filtro ocultos.
O argumento ref1 é obrigatório. Essas são as células usadas para calcular os resultados do argumento núm_função selecionado. Este argumento pode ser um valor, uma única célula ou um intervalo de células.
Os argumentos ref2, … são opcionais. Estas são células adicionais incluídas no cálculo.
Use a função SUBTOTAL com linhas ocultas
As funções do Excel podem ser inseridas manualmente ou com a ajuda da caixa de diálogo Argumentos da Função. Para ilustrar como inserir a função manualmente usando a barra de fórmulas, o exemplo a seguir usa o argumento COUNT function_num para contar o número de valores em linhas visíveis e em linhas visíveis e ocultas.
Para usar a função SUBTOTAL para contar o número de linhas em uma planilha:
- Comece com uma planilha que contém várias linhas de dados.
- Selecione a célula que conterá a contagem das linhas visíveis.
-
Na barra de funções, digite =SUBTOTAL. Conforme você digita, o Excel sugere uma função. Clique duas vezes na função SUBTOTAL.
Para usar a caixa de diálogo Argumentos da Função para inserir a função SUBTOTAL, vá para Formulas e selecione Math & Trig >SUBTOTAL.
Image -
No menu suspenso que aparece, clique duas vezes no argumento 102 – COUNT function_num.
Image -
Digite uma vírgula (,).
Image -
Na planilha, selecione as células a serem incluídas na fórmula.
Image -
Pressione Enter para ver o resultado na célula selecionada na etapa 2.
Image - Selecione a célula que conterá a contagem das linhas visíveis e ocultas.
-
Na barra de funções, digite =SUBTOTAL. Conforme você digita, o Excel sugere uma função. Clique duas vezes na função SUBTOTAL.
- No menu suspenso que aparece, clique duas vezes no argumento 2 – COUNT function_num e digite uma vírgula (,).
-
Na planilha, selecione as células a serem incluídas na fórmula e pressione Enter.
Image -
Oculta várias linhas de dados. Neste exemplo, as linhas com apenas vendas abaixo de US$ 100.000 foram ocultadas.
Image
Use a função SUBTOTAL com dados filtrados
Usar a função SUBTOTAL em dados filtrados ignora dados em linhas que foram removidas pelo filtro. Cada vez que os critérios do filtro mudam, a função recalcula para mostrar o subtotal das linhas visíveis.
Para usar a função SUBTOTAL para ver as diferenças nos resultados dos cálculos enquanto filtra os dados:
-
Cria fórmulas de SUBTOTAL. Por exemplo, crie fórmulas para determinar os valores subtotais e médios dos dados filtrados.
Não importa se você usa o argumento function_num para linhas visíveis ou ocultas. Ambos os argumentos fornecem o mesmo resultado em dados filtrados.
Image - Selecione qualquer célula no conjunto de dados.
-
Vá para Home e selecione Ordenar e Filtrar > Filtro.
Image -
Use as setas suspensas para filtrar os dados da planilha.
Image -
Observe como os valores mudam cada vez que você seleciona diferentes critérios de filtro.
Image
Use a função SUBTOTAL com dados agrupados
Quando os dados são agrupados, há uma maneira de aplicar a função SUBTOTAL a cada grupo individual e, em seguida, calcular o total geral para todo o conjunto de dados.
- Selecione qualquer célula no conjunto de dados.
-
Selecione Data > Subtotal para abrir a caixa de diálogo Subtotal.
Image - Selecione a seta suspensa Em cada mudança em e escolha o agrupamento para o qual cada subtotal será calculado.
- Selecione a seta suspensa Usar função e escolha um número_função.
- Na lista Adicionar subtotal a, selecione a coluna em que a fórmula será aplicada.
-
Selecione OK.
Image -
Subtotais são inseridos para cada grupo de dados e um total geral é inserido na parte inferior do conjunto de dados.
Image - Para alterar o function_num, destaque qualquer célula no conjunto de dados e selecione Data > Subtotal. Em seguida, faça suas escolhas na caixa de diálogo Subtotal.
