Fórmula SUBTOTAL no Excel (Sumário)
- Fórmula SUBTOTAL no Excel
- Como usar a fórmula SUBTOTAL no Excel?
Fórmula SUBTOTAL no Excel
Às vezes, precisamos encontrar o subtotal de uma categoria a partir de um grande conjunto de dados que possui várias categorias. Nesse tipo de situação, a função subtotal nos ajudará a encontrar a categoria total de maneira sábia. Não apenas o subtotal, podemos calcular a média, a contagem e o máximo e muito mais.
Definição
A função que retorna o subtotal de uma lista ou banco de dados pode ser definida como função do subtotal. Subtotal é uma função especial em comparação com outras funções do Excel, porque outras funções do Excel executam apenas uma operação específica, mas a especialidade da função subtotal é que ela realiza não apenas o cálculo do subtotal, mas também várias aritméticas e operações lógicas dependem do número da função.
Sintaxe da fórmula SUBTOTAL
Argumentos:
Função num : refere-se ao tipo de operação matemática que vamos executar em um intervalo especificado de dados.
Ref1, Ref 2: Refere-se ao intervalo de células.
Há um número múltiplo de números de funções disponíveis, mas não há problema, não precisamos lembrar de todos esses números de funções porque, ao usar a fórmula SUBTOTAL, o excel mostrará automaticamente a lista de números de funções disponíveis como abaixo.
Você deve estar se perguntando por que existem dois números de função para a mesma função.
- Os números de função 1 - 11 devem ser usados quando queremos ignorar as células filtradas e considerar as células ocultas manualmente.
- Os números de função 101 - 111 devem ser usados quando queremos ignorar todas as células ocultas filtradas e manualmente ocultas.
Veremos alguns exemplos para entender como a função subtotal funcionará para diferentes números de função.
Como usar a fórmula SUBTOTAL no Excel?
Fórmula SUBTOTAL no Excel é muito simples e fácil. Vamos entender como usar a fórmula SUBTOTAL no Excel com alguns exemplos.
Você pode fazer o download deste modelo SUBTOTAL de fórmula Excel aqui - Modelo SUBTOTAL de fórmula ExcelExemplo 1
Considere uma tabela pequena com dados de diferentes categorias, como abaixo.
Se você observar a tabela acima, temos categoria de produto, categoria de cores e quantidade.
Aplique a fórmula SUBTOTAL como abaixo com a função número 9.
Depois de aplicar esta fórmula, o resultado é como mostrado abaixo.
Aplique a fórmula SUBTOTAL como abaixo com o número de função 109.
Após a aplicação da SUBTOTAL Formula, o resultado é mostrado abaixo.
Na fórmula, usamos as funções número 9 e 109 para executar SUM em duas colunas diferentes. C2: C9 é o intervalo de dados que estamos realizando cálculos.
Agora, a soma total é 385 para ambas as fórmulas. Oculte algumas linhas e observe os resultados para ambas as fórmulas.
Após a aplicação, o resultado da Fórmula SUBTOTAL é mostrado abaixo.
As linhas de 50 a 60 estão ocultas; portanto, os resultados do número da função 109 foram alterados para 255 porque não consideram os dados ocultos manualmente, enquanto o número total da função 9 permanece o mesmo, o que significa que os dados serão considerados, mesmo que ocultemos os dados manualmente. .
Aplique filtro aos dados e filtre apenas uma cor; aqui é selecionada a Cor preta.
Após a aplicação, o resultado da Fórmula SUBTOTAL é mostrado abaixo.
Aplique a fórmula SUBTOTAL como abaixo com o número da função 109.
Após a aplicação, o resultado da Fórmula SUBTOTAL é mostrado abaixo.
Se observarmos a captura de tela acima, os números das funções não consideraram a quantidade de dados filtrados. Depois de filtrar os dados e ocultar linhas, as duas fórmulas fornecerão os mesmos resultados que as duas fórmulas não considerarão dados não visíveis.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Aplique a fórmula SUBTOTAL como abaixo com o número da função 109.
Depois de aplicar esta fórmula, o resultado é como mostrado abaixo.
Exemplo 2
Até agora, vimos a operação SUM, agora executaremos a operação MÉDIA com o mesmo intervalo de dados. 1 e 101 são os números de função para executar a média. Percorra a tabela acima para obter os detalhes dos números das funções.
Após a aplicação, o resultado da Fórmula SUBTOTAL é mostrado abaixo.
Para a próxima célula, aplique a Fórmula SUBTOTAL novamente com o número de função 109.
Depois de aplicar a fórmula, o resultado é como mostrado abaixo.
Com a mesma fórmula SUBTOTAL, também podemos calcular a média com a única alteração no número da função.
Após a aplicação, o resultado da Fórmula SUBTOTAL é mostrado abaixo.
Para a próxima célula, aplique a Fórmula SUBTOTAL novamente com o Número da Função 101.
Depois de aplicar a Fórmula SUBTOTAL, o resultado mostrado abaixo.
Oculte algumas linhas, agora observe as alterações na média. A média com a função número 1 permanece a mesma como estática, considerando também a quantidade oculta. A média com o número de função 101 foi alterada porque ignora os dados ocultos manualmente. Assim, você pode tentar o restante dos números de função e verificar a funcionalidade de cada um, pois o conceito básico é o mesmo e eles também funcionarão da mesma maneira.
Exemplo 3
O subtotal tem mais uma vantagem que ele não considerará se houver subtotais disponíveis no intervalo de dados. Veremos um exemplo para entender melhor. Fórmulas de entrada para executar operações SUM com produtos como A, B, C e D. Não é obrigatório que seja com produtos, você também pode fazer por cores.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Use a fórmula SUBTOTAL novamente para verificar a soma de B.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Use a fórmula SUBTOTAL novamente para verificar a soma de C.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Use a fórmula SUBTOTAL novamente para verificar a soma de D.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Use a fórmula SUBTOTAL novamente para a próxima célula.
Depois de aplicar a fórmula SUBTOTAL, o resultado é como mostrado abaixo.
Use a fórmula SUBTOTAL novamente para a próxima célula.
Depois de aplicar a Fórmula SUBTOTAL, o resultado mostrado abaixo.
Se observarmos as capturas de tela acima, há soma por subproduto e, durante a execução da soma total, incluímos os totais por categoria na soma total (células de 36 a 48), mas não consideramos os subtotais (células de 44 a 47 ) Portanto, podemos calcular os subtotais entre o intervalo de dados sem afetar o valor total da soma.
Espero que você entenda como usar o subtotal e o uso dele.
Lembretes sobre a fórmula SUBTOTAL no Excel
- Ao fornecer o número da função, não forneça o número que está fora do intervalo de números da função, pois não é predefinido pelo Excel, receberemos uma mensagem de erro. Devemos sempre fornecer o número entre 1 e 11 e 101 e 111, caso contrário, a mensagem de erro #value será exibida.
- Ao usar a operação de divisão, lembre-se de que nenhum número deve ser divisível por zero. Isso significa que x / 0 é um formato incorreto.
- Se você aplicar o subtotal a dados horizontais de A1: F1 e ocultar qualquer coluna não afetará o subtotal.
- Ao aplicar o subtotal, se alguma célula não tiver dados ou dados não numéricos, a função ignorará essas células.
- Use os números das funções compreendendo a funcionalidade e use, caso contrário, poderá não obter os resultados corretos conforme o esperado.
Artigos recomendados
Este foi um guia para a Fórmula SUBTOTAL no Excel. Aqui discutimos como usar a fórmula SUBTOTAL no Excel, juntamente com exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -
- Como usar a fórmula IRR do Excel?
- TRIM esquerdo no Excel (fórmula, exemplos)
- Tutorial VLOOKUP no Excel
- Fórmula ISNUMBER do Excel