Neste artigo
Quando a 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, máximo, mínimo, desvio padrão e variância 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 maneiras diferentes. É especialmente útil quando sua planilha contém linhas ocultas que você deseja incluir no cálculo. A sintaxe da função SUBTOTAL é: SUBTOTAL (núm_função, ref1, ref2, …)
O function_num argumento é obrigató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 dos 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) | |
MÉDIA | 1 | 101 |
CONTAR | 2 | 102 |
CONT.valor | 3 | 103 |
MAX | 4 | 104 |
MIN | 5 | 105 |
PRODUTOS | 6 | 106 |
STDEV | 7 | 107 |
STDEVP | 8 | 108 |
SOMA | 9 | 109 |
VAR | 10 | 110 |
VARP | 11 | 111 |
Os argumentos de referência de function_num 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 ref1 o argumento é obrigatório. Estas são as células usadas para calcular os resultados do argumento function_num selecionado. Este argumento pode ser um valor, uma única célula ou um intervalo de células. O ref2, … os argumentos são opcionais. Estas são células adicionais que são 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 nas linhas visíveis e nas 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 no SUBTOTAL função. Para usar a caixa de diálogo Argumentos da função para inserir a função SUBTOTAL, vá para Fórmulas e selecione Matemática e Trig > SUBTOTAL.
-
No menu suspenso que aparece, clique duas vezes no 102 – COUNT argumento function_num.
-
Digite uma vírgula (,)
-
Na planilha, selecione as células a serem incluídas na fórmula.
-
Aperte Entrar para ver o resultado na célula selecionada na etapa 2.
-
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 no SUBTOTAL função.
-
No menu suspenso que aparece, clique duas vezes no 2 – COUNT argumento function_num e, em seguida, digite uma vírgula (,)
-
Na planilha, selecione as células a serem incluídas na fórmula e pressione Entrar.
-
Oculte várias linhas de dados. Neste exemplo, as linhas com apenas vendas abaixo de $ 100.000 foram ocultadas.
Use a função SUBTOTAL com dados filtrados
Usar a função SUBTOTAL nos dados filtrados ignora os dados nas linhas que foram removidas pelo filtro. Cada vez que os critérios de filtro mudam, a função recalcula para mostrar o subtotal para as linhas visíveis. Para usar a função SUBTOTAL para ver as diferenças nos resultados dos cálculos ao filtrar os dados:
-
Crie fórmulas SUBTOTAL. Por exemplo, crie fórmulas para determinar o subtotal e os valores 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.
-
Selecione qualquer célula no conjunto de dados.
-
Vamos para Casa, então selecione Classificar e filtrar > Filtro.
-
Use as setas suspensas para filtrar os dados da planilha.
-
Observe como os valores mudam cada vez que você seleciona diferentes critérios de filtro.
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 Dados > Subtotal para abrir o Subtotal caixa de diálogo.
-
Selecione os A cada mudança em seta suspensa e escolha o agrupamento para o qual cada subtotal será calculado.
-
Selecione os Função de uso seta suspensa e escolha um function_num.
-
No Adicionar subtotal a lista, selecione a coluna em que a fórmula será aplicada.
-
Selecione OK.
-
Os subtotais são inseridos para cada grupo de dados e um total geral é inserido na parte inferior do conjunto de dados.
-
Para alterar o function_num, realce qualquer célula no conjunto de dados e selecione Dados > Subtotal. Então, faça suas escolhas no Subtotal caixa de diálogo.
Mais da Lifewire
-
Como classificar seus dados relacionados no Excel com tabelas
-
Encontre frações de um ano entre datas com ‘YEARFRAC’ do Excel
-
Arredondando os números para cima nas planilhas do Google
-
Use a função TYPE do Excel para verificar o tipo de dados em uma célula
-
Como usar valores booleanos (valores lógicos) no Excel
-
Encontre o maior número negativo ou positivo no Excel
-
Como usar a função CONT.SE do Planilhas Google
-
Como combinar as funções ROUND e SUM no Excel
-
Função Excel WORKDAY: Encontrar datas de início e término do projeto
-
Como usar a função IF-THEN no Excel
-
Como contar células em branco ou vazias em planilhas do Google
-
Como criar um banco de dados no Excel
-
Como usar a função DATEVALUE do Excel
-
Como criar uma fórmula de pesquisa do Excel com vários critérios
-
Como multiplicar números em planilhas do Google
-
Como usar a função AVERAGE das planilhas do Google
Seus direitos de privacidade
A Lifewire e nossos parceiros terceirizados usam cookies e processam dados pessoais como identificadores exclusivos com base em seu consentimento para armazenar e / ou acessar informações em um dispositivo, exibir anúncios personalizados e para medição de conteúdo, percepção do público e desenvolvimento de produtos. Para alterar ou retirar suas opções de consentimento para Lifewire.com, incluindo seu direito de contestar quando um interesse legítimo é usado, clique abaixo. A qualquer momento, você pode atualizar suas configurações por meio do link “Privacidade da UE” na parte inferior de qualquer página. Essas escolhas serão sinalizadas globalmente para nossos parceiros e não afetarão os dados de navegação. Lista de parceiros (fornecedores)
Nós e nossos parceiros processamos dados para:
Digitalize ativamente as características do dispositivo para identificação. Use dados precisos de geolocalização. Armazene e / ou acesse informações em um dispositivo. Selecione o conteúdo personalizado. Crie um perfil de conteúdo personalizado. Avalie o desempenho do anúncio. Selecione anúncios básicos. Crie um perfil de anúncios personalizados. Selecione anúncios personalizados. Aplique pesquisas de mercado para gerar insights sobre o público. Avalie o desempenho do conteúdo. Desenvolva e aprimore produtos. Lista de parceiros (fornecedores)