Como usar a função SUBTOTAL no Microsoft Excel
Veja o subtotal dos valores com esta função simples do Excel.
Principais conclusões
Para usar a função SUBTOTAL, crie uma fórmula usando a sintaxe: SUBTOTAL(número_função, referência 1, referência 2,...). Você também pode usar o recurso Subtotal selecionando suas linhas e clicando na guia “Dados”. No menu suspenso Estrutura de tópicos, selecione “Subtotal”.
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.
Esse
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.
Function | Include Hidden Rows | Exclude Hidden Rows |
AVERAGE | 1 | 101 |
COUNT | 2 | 102 |
COUNTA | 3 | 103 |
MAX | 4 | 104 |
MIN | 5 | 105 |
PRODUCT | 6 | 106 |
STDEV | 7 | 107 |
STDEVP | 8 | 108 |
SUM | 9 | 109 |
VAR | 10 | 110 |
VARP | 11 | 111 |
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 Esboço.
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 a atualização dos seus 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 Esquema.
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ê sabe inserir subtotais no Excel, veja como remover linhas duplicadas.
Mastering Excel Functions | ||
Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF ·FILTER ·FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND· RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL ·SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP· YEAR | |
Types | Basic · Budgeting· Data Entry· Logical · Text · Time and Date | |
Explained | Copying Formulas· Evaluating Formulas· Finding Functions · Fixing Formula Errors· Functions vs Formulas · Comparing Lookup Functions· Locking Formulas · Structuring Formulas · Translating Formulas |