Skip to content

Como usar a função de subtotal do Excel

8 de maio de 2021

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:

  1. Comece com uma planilha que contém várias linhas de dados.

  2. Selecione a célula que conterá a contagem das linhas visíveis.

  3. 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.

  4. No menu suspenso que aparece, clique duas vezes no 102 – COUNT argumento function_num.

    Use o argumento COUNT function_num com a função SUBTOTAL

  5. Digite uma vírgula (,)

    Complete o argumento function_num na função SUBTOTAL do Excel

  6. Na planilha, selecione as células a serem incluídas na fórmula.

    Adicione as células a serem incluídas na fórmula como o argumento ref1 da função SUBTOTAL

  7. Aperte Entrar para ver o resultado na célula selecionada na etapa 2.

    A função SUBTOTAL completa usando o argumento COUNT

  8. Selecione a célula que conterá a contagem das linhas visíveis e ocultas.

  9. Na barra de funções, digite = SUBTOTAL. Conforme você digita, o Excel sugere uma função. Clique duas vezes no SUBTOTAL função.

  10. No menu suspenso que aparece, clique duas vezes no 2 – COUNT argumento function_num e, em seguida, digite uma vírgula (,)

  11. Na planilha, selecione as células a serem incluídas na fórmula e pressione Entrar.

    Use a função SUBTOTAL para contar as linhas visíveis e ocultas

  12. Oculte várias linhas de dados. Neste exemplo, as linhas com apenas vendas abaixo de $ 100.000 foram ocultadas.

    A função SUBTOTAL concluída para contar os valores visíveis e ocultos

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:

  1. 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.

    Use a função SUBTOTAL em dados filtrados

  2. Selecione qualquer célula no conjunto de dados.

  3. Vamos para Casa, então selecione Classificar e filtrar > Filtro.

    Filtre os dados em uma planilha do Excel para ver o resultado da função SUBTOTAL

  4. Use as setas suspensas para filtrar os dados da planilha.

    Como filtrar dados em uma planilha do Excel

  5. Observe como os valores mudam cada vez que você seleciona diferentes critérios de filtro.

    Resultados da função SUBTOTAL em dados filtrados em uma planilha do Excel

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.

  1. Selecione qualquer célula no conjunto de dados.

  2. Selecione Dados > Subtotal para abrir o Subtotal caixa de diálogo.

    Use o comando Subtotal para criar subtotais e totais gerais para dados agrupados no Excel

  3. Selecione os A cada mudança em seta suspensa e escolha o agrupamento para o qual cada subtotal será calculado.

  4. Selecione os Função de uso seta suspensa e escolha um function_num.

  5. No Adicionar subtotal a lista, selecione a coluna em que a fórmula será aplicada.

  6. Selecione OK.

    Defina os critérios para o comando do subtotal em uma planilha do Excel

  7. Os subtotais são inseridos para cada grupo de dados e um total geral é inserido na parte inferior do conjunto de dados.

    Subtotal e um total geral inserido em dados agrupados em uma planilha do Excel

  8. 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

  • Empresários discutindo dados em um laptop

    Como classificar seus dados relacionados no Excel com tabelas

  • Um cronômetro de areia e um calendário sobre uma mesa.

    Encontre frações de um ano entre datas com ‘YEARFRAC’ do Excel

  • Imagem de um computador com uma planilha na tela

    Arredondando os números para cima nas planilhas do Google

  • Encontre o tipo de dados em uma célula com a função TYPE do Excel

    Use a função TYPE do Excel para verificar o tipo de dados em uma célula

  • Código binário

    Como usar valores booleanos (valores lógicos) no Excel

  • Dados em uma planilha do Excel

    Encontre o maior número negativo ou positivo no Excel

  • Folha do Google aberta no desktop macOS

    Como usar a função CONT.SE do Planilhas Google

  • Ilustração de uma pessoa usando o Excel para combinar as funções de arredondamento e soma

    Como combinar as funções ROUND e SUM no Excel

  • Pessoa gerenciando planilha no laptop

    Função Excel WORKDAY: Encontrar datas de início e término do projeto

  • Exemplo de inserção de uma função IF-THEN no Excel 2016.

    Como usar a função IF-THEN no Excel

  • Pessoa em um quadro-negro com contagem

    Como contar células em branco ou vazias em planilhas do Google

  • Usar a função ROUNDUP no Excel economiza tempo.

    Como criar um banco de dados no Excel

  • DATEVALUE Exemplos de função Excel

    Como usar a função DATEVALUE do Excel

  • Empresário usando laptop em reunião de escritório

    Como criar uma fórmula de pesquisa do Excel com vários critérios

  • Pessoa multiplicando números no Excel

    Como multiplicar números em planilhas do Google

  • Analytics redondo ilustração com gráficos.

    Como usar a função AVERAGE das planilhas do Google

Lifewire

Siga-nos

  • Facebook

  • Sobre nós
  • Anunciar
  • Política de Privacidade
  • Política de Cookies
  • Carreiras
  • Diretrizes editoriais
  • Contato
  • Termos de uso
  • Privacidade da UE
  • Aviso de privacidade da Califórnia



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)