Função SUBTOTAL: Fórmula e como usar a função SUBTOTAL no Excel

Qual é a função SUBTOTAL no Excel ? Qual é a fórmula SUBTOTAL no Excel ? Vamos descobrir com LuckyTemplates.com!

O Microsoft Excel possui muitas funções usadas para calcular totais, e SUBTOTAL é uma delas. No entanto, o Subtotal é diferente da função SUM. Não é uma soma normal. Em vez disso, é usado para calcular subtotais em listas ou bancos de dados, calcular médias e muito mais.

Conteúdo principal sobre a função SUBTOTAL

• O que é a função SUBTOTAL e para que é utilizada?
• Fórmula SUBTOTAL no Excel
• Como usar a função SUBTOTAL no Excel
  • 1. Calcule o total de linhas filtradas 
  • 2. Conte as células filtradas que não estão vazias
  • 3. Ignore valores em fórmulas de subtotal aninhadas
• Coisas a considerar antes de usar a função SUBTOTAL no Excel
• Como erro ao executar a função SUBTOTAL no Excel

O que é a função SUBTOTAL e para que é utilizada?

À primeira vista, você pode se perguntar: Qual é o propósito da função SUBTOTAL? Afinal, é fácil encontrar um subtotal de um intervalo de células usando funções existentes como SUMIF ou AVERAGE .

Embora seja verdade que você não precisa necessariamente usar SUBTOTAL, isso torna a análise dos dados direcionados muito mais fácil. Você pode inserir uma fórmula usando SUBTOTAL em um intervalo de células sem afetar o total geral, porque SUBTOTAL ignora outras células que contêm uma fórmula SUBTOTAL.

SUBTOTAL funciona com outros recursos do Excel, como filtragem de células. Se você filtrar uma tabela por um valor, a fórmula SUBTOTAL será atualizada, reconhecerá o filtro e excluirá as células correspondentes. SUBTOTAL também é útil para ignorar valores ocultos, o que outras funções (como SUM) não podem fazer.

Felizmente, você pode usar outras funções como parte de uma fórmula SUBTOTAL. Existem 11 métodos de subtotal diferentes disponíveis, cada um adequado para uma função existente do Excel. Se você deseja uma fórmula SUM que funcione com células filtradas e ignore outras fórmulas SUBTOTAL, é melhor usar SUBTOTAL, embora uma tabela dinâmica possa funcionar da mesma maneira.

Fórmula SUBTOTAL no Excel

A função SUBTOTAL funciona calculando o valor total de um intervalo de células, com base em outra função matemática. SUBTOTAL usa até 11 funções matemáticas diferentes (de MÉDIA a VAR.P) para encontrar o valor total apropriado, enquanto trabalha dentro dos parâmetros da função (ou seja, ignorando outras fórmulas SUBTOTAL).

A função SUBTOTAL tem a sintaxe =SUBTOTAL(function_num, ref1, [ref2],…) . Lá:

• Function_num: Os números de 1 a 11 e 101 a 111 especificam qual função será usada para calcular no SUBTOTAL.
• Ref1, Ref2, ...: 1 ou mais células, ou intervalo de células para calcular subtotais, máximo 254.

Observação:

• A função SUBTOTAL foi projetada para calcular colunas verticais de dados.
• Se os argumentos ref1, ref2,... contiverem a função SUBTOTAL, serão ignorados para evitar dupla contagem.
• Se function_num for de 1 a 11, a função SUBTOTAL calcula incluindo valores ocultos no conjunto de dados porque a linha que contém esse intervalo está oculta. Se function_num for de 101 a 111, a função SUBTOTAL calcula apenas valores não ocultos no conjunto de dados (sem contar valores em linhas ocultas).
• Para áreas de dados ocultadas pelo Filtro, SUBTOTAL será ignorado.

Como usar a função SUBTOTAL no Excel

1. Calcule o total de linhas filtradas 

Para entender melhor esse tipo de soma, consulte o artigo Calcular a soma dos valores da lista filtrada no Excel . Basicamente, a função SUBTOTAL neste caso será:

=SUBTOTAL(9,_intervalo)

pham_range aqui é a área que você deseja somar após filtrar os dados.

2. Conte as células filtradas que não estão vazias

Usaremos SUBTOTAL 3 ou SUBTOTAL 103. Porém, caso haja linhas ocultas, deve-se usar SUBTOTAL 103 para contar com precisão as células visíveis não vazias.

Por exemplo, a tabela de dados acima ocultará 2 linhas 4 e 5. Ao usar SUBTOTAL 3 ou SUBTOTAL 103, produzirá 2 resultados diferentes.

Entramos na fórmula e o Excel exibirá automaticamente o conjunto de funções para você escolher sem precisar lembrar.

O resultado ao usar SUBTOTAL 3 será 3, incluindo células ocultas na linha.

Já o SUBTOTAL 103 exibirá apenas as células não vazias que vemos, ignorando as linhas ocultas.

3. Ignore valores em fórmulas de subtotal aninhadas

Por exemplo, calcularemos a média do total de quilogramas de tecido nos armazéns A1 e A2.

A fórmula média para o armazém A2= SUBTOTAL(1,C2:C4) e dá o resultado 19.

A fórmula média para o armazém A1 = SUBTOTAL(1,C5:C7) tem o resultado 38.

Porém, ao calcular a média do número total de tecidos em 2 armazéns, os resultados médios nos 2 armazéns serão ignorados. Temos a fórmula =SUBTOTAL(1,C2:C9) e o resultado removerá automaticamente o resultado médio calculado anteriormente.

Coisas a considerar antes de usar a função SUBTOTAL no Excel

Embora SUBTOTAL tenha suas vantagens, há algumas coisas que você deve considerar antes de começar a usar esta função, incluindo as seguintes:

• Conforme mencionado, usar 1-11 para o argumento function_num garantirá que SUBTOTAL inclua valores ocultos, enquanto 101-111 os ignora.
• Usar um valor diferente de 1-11 ou 101-111 fará com que o Excel retorne o erro #VALUE!. Isso também acontecerá com referências de células 3D (onde a mesma célula, em várias planilhas, é referenciada em um intervalo).
• Quando intervalos de células horizontais como A1:D1 são usados, os valores ocultos são incluídos automaticamente (independentemente do valor do argumento function_num). Esta é uma limitação da função SUBTOTAL e não pode ser superada.
• Usar SUBTOTAL em dados filtrados garantirá que os valores ocultos sejam sempre ignorados, independentemente do argumento function_num usado.
• SUBTOTAL pode ser usado como uma função aninhada como parte de outras funções, incluindo fórmulas que contêm uma função SE.
• Se outra fórmula SUBTOTAL estiver dentro do intervalo de dados ref1, SUBTOTAL a ignora e a exclui do cálculo geral.
• O Excel permite que até 254 intervalos de células sejam usados ​​em uma fórmula SUBTOTAL, embora apenas 1 intervalo de células seja necessário para que a fórmula funcione.

Como erro ao executar a função SUBTOTAL no Excel

Ao calcular a função SUBTOTAL no Excel, muitas vezes você encontrará alguns erros básicos:

• #VALUE!: O identificador da função não está no intervalo 1-11 ou 101-111 ou possui uma referência 3D.
• #DIV/0!: Ocorre quando uma soma específica deve ser dividida por 0 (por exemplo, calculando a média ou desvio padrão de um intervalo de células que não contém valores numéricos).
• #NOME? O nome da função SUBTOTAL está escrito incorretamente.

Algumas dicas sobre como usar a função SUBTOTAL no Microsoft Excel que você talvez não conheça

Dica 1: digamos que você queira ter certeza de que todas as cores de camisetas estão disponíveis em todos os tamanhos em qualquer depósito. Você pode seguir estas etapas:

Etapa 1: clique em Subtotal . Lembre-se de que esta etapa consiste em adicionar mais critérios aos dados do subtotal existente.

Etapa 2: Selecione CONTAGEM no menu suspenso e Tamanho no campo “ Adicionar campo de subtotal a ”. Em seguida, desmarque Substituir subtotais atuais . Ao clicar em OK , você receberá a seguinte tabela de dados:

Isso ajuda a garantir que você tenha um número suficiente de dimensões diferentes e possa classificar os dados de uma forma que não exija operações repetidas.

Dica 2 : é sempre útil classificar os dados por coluna.

Dica 3: ajuda a incluir um rótulo na primeira linha.

Dica 4: Caso queira resumir os dados, desmarque a caixa “ Resumo abaixo dos dados ao inserir o Subtotal ”. - Resumo dos dados abaixo ao inserir a função SUBTOTAL.

Resumindo, o que é a função Subtotal do Excel?

Como o nome sugere, a função SUBTOTAL no Excel é usada para calcular subtotais. A função Excel SUBTOTAL retorna resultados agregados para os valores fornecidos. SUBTOTAL pode calcular soma, média, contagem, máximo… Este Excel também pode incluir ou excluir valores em linhas ocultas.

• Objetivo: Calcular o subtotal em uma lista ou banco de dados.
• Valor de retorno: um número que representa um tipo de subtotal.
• Argumento:
  • function_num- Um número que especifica qual função usar para calcular subtotais em uma lista.
  • ref1- Um intervalo nomeado ou referência a um subtotal.
  • ref2- [opcional] Um intervalo nomeado ou referência a um subtotal.

Usar a função SUBTOTAL pode ajudá-lo a analisar rapidamente um conjunto de dados sem depender de tabelas dinâmicas mais complexas. Se tiver dúvidas, você também pode usar o recurso Subtotal, encontrado na guia Dados da faixa de opções, para criar automaticamente uma fórmula SUBTOTAL que pode ser editada ou copiada em outro lugar.

Ver mais:

• Função SUMPRODUCT no Excel: Calcule o produto total dos valores correspondentes
• Como combinar 2 colunas Nome Completo e Nome no Excel sem perder conteúdo