Como usar a função SUBTOTAL no Microsoft Excel

O que é um subtotal no Excel?

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 que você ocultou.

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_numberargumento permite que você insira uma das 11 funções usando seu número correspondente. As primeiras 11 incluem linhas ocultas, enquanto as segundas 11 as excluem. Então, 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, vamos excluir essas linhas ocultas do nosso total, usando o número 109 como nosso primeiro argumento.

=SUBTOTAL(109,B2:B6)

Depois de obter seus subtotais, você pode usar a função SUBTOTAL mais uma vez para 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 average para incluir as linhas ocultas 3 e 4 com esta fórmula:

=SUBTOTAL(1,C2:C6)

Em seguida, excluiremos as linhas ocultas com esta fórmula:

=SUBTOTAL(101,C2:C6)

Notas sobre a função SUBTOTAL

Aqui estão algumas coisas a serem lembradas ao usar a função SUBTOTAL no Excel:

• A função funciona para colunas ou intervalos verticais, não linhas ou intervalos horizontais.
• Se você tiver subtotais aninhados na área de 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 Subtotal

Outra maneira 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 Contorno.

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

• Em cada mudança em: 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 o nosso exemplo, selecionamos Sum.
• Adicionar subtotal a: Marque a caixa onde deseja que o subtotal seja calculado. Para o nosso exemplo, escolhemos Vendas.

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

Você verá seus dados atualizados para agrupar e subtotalizar as linhas e criar um total geral na parte inferior. Use os botões de mais, menos e número para recolher ou expandir os grupos para facilitar a visualização.

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

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

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

Agora temos nossas linhas agrupadas por aluno com uma nota média para cada uma 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 retornarão ao normal. Porém, aqueles que contém a função SUBTOTAL utilizada pelo Excel ficam para você continuar utilizando ou simplesmente deletar se preferir.

O recurso Subtotal pode se tornar complexo se você planeja usar muitos grupos. No entanto, esses exemplos básicos devem ajudar você a começar se estiver interessado nessa 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 o último, perderá a funcionalidade da tabela.

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