Como usar a função SUBTOTAL no Microsoft Excel

Obter subtotais para grupos de itens relacionados é fácil no Microsoft Excel. Na verdade, você tem duas maneiras de fazer isso. Você pode usar a função SUBTOTAL ou o recurso Subtotal, o que funcionar melhor. Nós lhe mostraremos como.

O que é um subtotal no Excel?

Conforme mencionado, SUBTOTAL no Excel permite agrupar itens iguais ou relacionados em uma lista e usar uma função para calcular os valores. Por exemplo, você pode usá-lo para somar as vendas por mês ou as notas médias por aluno. Você também pode adicionar estoque por produto ou contar o número de contas com vencimento na próxima semana.

Usando a função SUBTOTAL criando uma fórmula ou usando o recurso Subtotal, você pode obter os cálculos necessários em apenas algumas etapas.

Use a função SUBTOTAL do Excel

Você pode criar uma fórmula usando a função SUBTOTAL do Excel com a flexibilidade de incluir ou excluir linhas ocultadas.

A sintaxe da fórmula é

 SUBTOTAL(function_number, reference 1, reference 2,...) 

onde os dois primeiros argumentos são necessários. Você pode usar referências de células adicionais ou intervalos nomeados para os argumentos restantes, conforme necessário.

O

 function_number 

argumento permite inserir uma das 11 funções usando seu número correspondente. Os primeiros 11 incluem linhas ocultas, enquanto os segundos 11 as excluem. Portanto, basta inserir o número da função que você precisa e como deseja tratar as linhas ocultas.

Como exemplo da função SUBTOTAL, somaremos o total de vendas no intervalo de células B2 a B4 usando esta fórmula:

=SUBTOTAL(9,B2:B4)

Para este próximo exemplo, ocultamos as linhas 4 e 5. Usando esta primeira fórmula, podemos obter nossa soma usando o número 9 para o primeiro argumento para incluir os dados ocultos.

=SUBTOTAL(9,B2:B6)

Agora, excluiremos essas linhas ocultas do nosso total, usando o número 109 como nosso primeiro argumento.

=SUBTOTAL(109,B2:B6)

Depois de ter seus subtotais, você pode usar a função SUBTOTAL mais uma vez para obter um total geral na parte inferior. Alternativamente, você pode usar a função SUM para adicionar os subtotais.

Para outro exemplo, usaremos a função média para incluir as linhas ocultas 3 e 4 com esta fórmula:

=SUBTOTAL(1,C2:C6)

A seguir, excluiremos as linhas ocultas com esta fórmula:

=SUBTOTAL(101,C2:C6)

Notas sobre a função SUBTOTAL

Aqui estão algumas coisas que você deve ter em mente ao usar a função SUBTOTAL no Excel:

• A função funciona para colunas ou intervalos verticais, não para linhas ou intervalos horizontais.
• Se você tiver subtotais aninhados na área do argumento de referência, o Excel os ignorará no resultado do subtotal.
• Quando você usa um filtro , os dados filtrados são excluídos do resultado do subtotal.

Use o recurso de subtotal

Outra forma de usar a função SUBTOTAL no Excel é usando o recurso Subtotal. Dessa forma, você pode adicionar cálculos automaticamente e agrupar os itens ao mesmo tempo. O Excel usa a função SUBTOTAL para fazer isso para você. Vejamos alguns exemplos.

Usando o recurso Subtotal para nossas vendas por mês, podemos agrupar os dados por mês e somar cada grupo com um total geral na parte inferior.

Selecione todas as linhas que deseja agrupar e subtotalizar. Vá para a guia Dados e escolha “Subtotal” no menu suspenso Esquema.

Quando a caixa Subtotal for aberta, escolha como deseja adicionar os subtotais.

• A Cada Alteração : Selecione a coluna que deseja usar para o agrupamento. Aqui, escolhemos Mês para que um novo grupo seja criado quando o Mês mudar na planilha.
• Usar Função : Selecione a função que deseja usar, como soma, média, mínimo, máximo ou outra opção. Para nosso exemplo, selecionamos Sum.
• Adicionar subtotal a : marque a caixa onde deseja que o subtotal seja calculado. Para nosso exemplo, escolhemos Vendas.

Opcionalmente, marque as caixas na parte inferior para os itens adicionais que desejar. Clique OK.”

Você verá então sua atualização de dados para agrupar e subtotalizar as linhas e criar um total geral na parte inferior. Use os botões de mais, menos e numérico para recolher ou expandir os grupos e facilitar a visualização.

Como outro exemplo, usaremos as notas dos alunos. Usaremos os recursos Subtotal e Grupo para exibir a nota média de cada aluno. Aqui está o processo.

Selecione as linhas, vá para Dados e escolha “Subtotal” no menu suspenso Esboço.

Na caixa Subtotal, escolheremos Aluno na lista suspensa de alterações e Média na lista de funções. Em seguida, marcaremos a caixa Nota. Clique OK.”

Agora temos nossas linhas agrupadas por aluno com uma nota média para cada um e uma média geral na parte inferior. Novamente, você pode usar os botões à esquerda para recolher e expandir os grupos.

Se você decidir desagrupar as linhas depois de usar o recurso Subtotal, as linhas voltarão ao normal. Porém, aqueles que contém a função SUBTOTAL utilizada pelo Excel permanecem para você continuar utilizando ou simplesmente excluir se preferir.

O recurso Subtotal pode se tornar complexo se você planeja usar muitos grupos. No entanto, estes exemplos básicos devem ajudá-lo a começar se estiver interessado nesta função.

Uma coisa a observar é que você não pode adicionar um subtotal de tabela do Excel com esse recurso. Se você tiver seus dados em uma tabela, poderá inserir a fórmula do Excel para SUBTOTAL conforme descrito anteriormente ou converter sua tabela em um intervalo de células para usar o recurso. Se você escolher a última opção, perderá a funcionalidade da tabela.

Agora que você já sabe como inserir subtotais no Excel, confira como remover linhas duplicadas .