Neste artigo, aprenderemos como usar a função SUBTOTAL do Excel.
Obtenha resultados nas linhas filtradas
O maior problema com SUM, AVERAGE, COUNT e outras funções NUMBER é que elas incluem linhas que são ocultadas pelo filtro. Eles não funcionam corretamente quando queremos somar, contar ou fazer qualquer outra tarefa numérica apenas em uma célula visível. Aqui estão os passos na função SUBTOTAL do Excel.
Sintaxe da função SUBTOTAL:
| = SUBTOTAL(número_da_função, ref1, [ref2] …) |
Function_number: O subtotal oferece suporte a várias funções numéricas. Para acessá-los facilmente, ele usa um número de função. Abaixo está a tabela do número da função com as funções correspondentes.
| Número da função para Incluir linhas ocultas manualmente | Número da função para Ignorar linhas ocultas manualmente | Função |
| 1 | 101 | MÉDIA |
| 2 | 102 | CONTAR |
| 3 | 103 | CONT.valor |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUTOS |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SOMA |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Ref1: O intervalo nomeado ou referência de números.
[ref2]: intervalo nomeado opcional ou intervalo de referência para números.
Exemplo :
Tudo isso pode ser confuso de entender. Vamos entender como usar a função usando um exemplo. Aqui temos essa tabela de imóveis. Agora quero adicionar uma linha total, mas ela deve mostrar a soma das células visíveis apenas quando eu aplicar um filtro.
Escreva esta fórmula no final da tabela ou no topo. Sua escolha…
| = SUBTOTAL (9, C2: C16) |
Agora, sempre que você aplicar um filtro, apenas o total de células visíveis será mostrado.
Como você pode ver na imagem acima, a soma da área total das linhas filtradas (1250 + 530 + 590) resulta em 2370. Isso não é impressionante.
COUNT valores nas linhas filtradas
Para este artigo, seremos obrigados a usar a função SUBTOTAL. Agora faremos uma fórmula da função. Aqui, são fornecidos alguns valores em um intervalo e um valor de texto específico como critérios. Precisamos contar os valores onde a fórmula inclui todos os valores que terminam com o texto ou padrão fornecido
Fórmula genérica: -
| = SUBTOTAL (fun_num, intervalo) |
fun_num: número, num correspondente à operação
intervalo: intervalo onde a operação deve ser aplicada.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui temos os dados de importação do pedido. E aplicamos critérios para Região, apenas a região Leste é filtrada.
Encontre aqui a contagem dos valores filtrados. Escolha o argumento certo como fun_num. Fun_num é a operação que você deseja aplicar. Aqui, para contar as células, usamos a operação CONT.valores, num como 3.
Use a fórmula:
| = SUBTOTAL (3, B2: B14) |
Como você pode ver, o total de linhas visíveis é 11. Agora usaremos mais um exemplo para extrair a média das quantidades visíveis ou filtradas.
Use a fórmula:
| = SUBTOTAL (1, C2: C14) |
Como você pode ver, a quantidade média do pedido recebido da região Leste chega a ser aproximadamente 60. Você pode usar diferentes operações como média, contagem, máximo, mínimo, produto, desvio padrão, soma ou variação de acordo com os resultados exigidos nos dados filtrados .
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- Escolha o fun_num certo para obter os resultados desejados.
- A fórmula só funciona com números e texto, ambos
- Operadores como é igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou diferente de () pode ser executado em funções aplicadas apenas a números.
Espero que este artigo sobre como usar a função SUBTOTAL no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel.
COUNTIFS com intervalo de critérios dinâmicos : Contar células dependentes de outros valores de células no Excel.
COUNTIFS Correspondência de dois critérios : Conte as células que correspondem a dois critérios diferentes na lista do Excel.
COUNTIFS com OU para vários critérios : Conte as células com correspondência de vários critérios usando a função OU.
A função COUNTIFS no Excel : Contar células dependentes de outros valores de células.
Como usar Countif em VBA no Microsoft Excel : Conte células usando o código do Visual Basic for Applications.
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel
Artigos populares :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
