Função SUBTOTAL | Como Usar?

outubro 6, 2023 admin

Você conhece a função SUBTOTAL do Excel? Ainda não? Então neste post você vai aprender tudo sobre esta função.

Como o seu próprio nome já diz, a função SUBTOTAL retorna o subtotal de um grupo de valores. Com esta função é possível realizar diferentes operações aritméticas em uma única função como, por exemplo, média, soma, desvio padrão, produto, entre outras.

Acompanhe os detalhes a seguir e veja como usar essa função do Excel por meio de exemplos práticos!

Função SUBTOTAL: Sintaxe e uso básico

A função SUBTOTAL tem a finalidade de executar um determinado cálculo em um intervalo de células, ignorando as células que não devem ser incluídas na função, como linhas ocultas. Basicamente esta função calcula o subtotal de um intervalo de células, tendo como base outra função.

Em outras palavras, a função SUBTOTAL é capaz de realizar diferentes operações matemáticas e lógicas em uma única função. Esta função está presente a partir da versão 2007 do Excel e sua sintaxe é a seguinte:

=SUBTOTAL(núm_função,ref1,[ref2],…])

Onde:

Núm_função: (Argumento obrigatório). Representa o número da função a ser usada para o cálculo do subtotal.
Estes valores, podem ser de 1 a 11 (quando deseja-se incluir linhas ocultas) e 101 a 111 (quando deseja-se ignorar linhas ocultas.
Ref1: (Argumento obrigatório). É o primeiro intervalo de células, onde deseja-se obter o subtotal.
Ref2,…: (Argumento opcional). Demais intervalos de células, onde deseja-se obter o subtotal.

O primeiro argumento da função Núm_função irá representar qual função será usada para obter o subtotal. Na tabela abaixo você poderá conferir o número que representa cada uma delas:

Vale lembrar que você não precisa memorizar o número que corresponde cada função, pois assim que a função SUBTOTAL for inserida em uma célula, estas opções irão aparecer para lhe auxiliar na escolha correta. Confira a imagem abaixo:

Por que usar a função SUBTOTAL?

Uma das vantagens em se usar a função SUBTOTAL é a possibilidade de obter totais de várias operações aritméticas usando uma única função.

Outro ponto positivo da função, é calcular o subtotal de valores que estejam em linhas filtradas. Por exemplo, se você filtrar determinada informação, a função SUBTOTAL irá recalcular o subtotal automaticamente conforme o filtro aplicado.

Uso Básico da Função SUBTOTAL no Excel

Para compreender melhor o uso da função SUBTOTAL, suponha que você tenha uma tabela com as quantidades de algumas frutas e deseja obter os subtotais da quantidade total, a média, valor máximo e mínimo. Veja o exemplo abaixo:

Note na imagem acima que no primeiro argumento, estamos usando a opção onde a função irá incluir as linhas ocultas no cálculo (1 a 11).

Agora, veja como ficaria a fórmula se ocultarmos uma linha da tabela e mudarmos o primeiro argumento da função para ignorar as linhas ocultas (101 a 111):

Observe que na segunda tabela os valores obtidos foram diferentes, pois ocultamos a linha 5 e inserimos na fórmula a opção para que os valores fossem calculados ignorando as linhas ocultas.

Exemplos práticos da função SUBTOTAL

Confira a seguir alguns exemplos práticos do uso da função SUBTOTAL:

Exemplo 1: Calcular a média de valores filtrados

Suponha que você tenha uma lista de produtos com a Data de vendas e valores. Nesta lista você deseja obter a média de vendas dos produtos. Veja a imagem:

No exemplo acima, poderíamos simplesmente usar a função MÉDIA e obter o resultado. Mas se desejarmos filtrar a lista por determinado produto e obter apenas a média de vendas deste produto, não será possível com a função MÉDIA, pois esta função não irá considerar o filtro, ou seja, ela irá mostrar a média de vendas de todos os produtos.

Por outro lado, com a função SUBTOTAL, a média se tornará dinâmica. Deste modo, podemos filtrar o produto e obter apenas a média do produto selecionado. Para isto, basta utilizar a seguinte fórmula:

=SUBTOTAL(101;C2:C18)

=SUBTOTAL(1;C2:C18)

A função SUBTOTAL sempre irá ignorar as linhas filtradas durante o cálculo, então não importa qual código você usar.

Veja abaixo o resultado:

Agora confira o resultado quando filtramos por frutas:

Observe nos exemplos acima que os valores das médias de vendas são calculados conforme os valores filtrados. Agora veja o que aconteceria se usássemos apenas a função média:

Note que ao usar apenas a função média o cálculo não é alterado de acordo com o filtro, ou seja, ele continua calculando a média de todos os produtos.

Exemplo 2: Obter o subtotal de categorias diferentes

Uma das vantagens da função SUBTOTAL é poder obter os totais de categorias diferentes. Para este exemplo, vamos usar uma tabela com as vendas de determinados produtos separados por regiões, veja a imagem abaixo:

Confira o resultado:

Note que o total geral é calculado normalmente, pois a função SUBTOTAL ignora os resultados das demais fórmulas presentes nas células C7, C11 e C15, como se a função SOMA fosse aplicada com 3 intervalos separados.

Exemplo 3: Resumir dados dinamicamente

Suponha que você tenha uma grande quantidade de dados e deseja mostrar os resultados totais de soma, média, máximo e mínimo valor de forma rápida e sem ocupar espaço na planilha. Neste caso, uma alternativa é usar a função SUBTOTAL com a função SE.

Para melhor compreensão, vamos usar a tabela com as mesmas informações do exemplo anterior. Ao final da tabela, vamos criar uma lista suspensa, com as opções de subtotais que queremos obter (Total, Média, Máximo e Mínimo). Veja o exemplo:

Agora na célula ao lado da lista suspensa (B12), insira a seguinte fórmula:

=SE(A12="Total";SUBTOTAL(109;C2:C10);SE(A12="Média";SUBTOTAL(101;C2:C10);SE(A12="Máximo";SUBTOTAL(104;C2:C10);SE(A12="Mínimo";SUBTOTAL(105;C2:C10);""))))

Na fórmula estamos dizendo ao Excel que se o valor presente na célula A12 for igual a Total, então a função SUBTOTAL irá retornar o valor total, agora se o valor da célula A12 for igual a Média, então teremos o resultado da média e assim por diante. Confira o resultado:

Agora basta alterar na lista suspensa a opção deseja, que o resultado irá aparecer conforme a opção que você selecionou. Além disso, quando ocultamos algumas linhas os resultados se alteram, pois usamos no primeiro argumento a opção para ignorar linhas ocultas.

Informações importantes sobre a Função SUBTOTAL no Excel

A função sempre irá ignorar as linhas ocultas quando estas estão em listas filtradas. Mas se forem linhas ocultas, será necessário usar os argumentos de 101 a 111, para que as linhas ocultas sejam ignoradas.
O erro #VALOR! irá retornar se o primeiro argumento da função for um valor diferente de 1 a 11 ou 101 a 111.
O erro #DIV/0! ocorre se o cálculo envolver divisão por zero ou se a função está sendo usada em um intervalo que não contém valores numéricos.
Nas versões do Excel 2010 e posteriores você irá encontrar a função AGREGAR, que é semelhante ao SUBTOTAL e possuí mais opções e funções.

Baixe aqui a planilha com os exemplos apresentados neste tutorial

Confira também as seguintes dicas de Excel:

E então, o que achou do uso da função SUBTOTAL no Excel? Deixe seu comentário abaixo e continue conferindo mais dicas de Excel em nosso site!