Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função SUBTOTAL do Excel no Excel para calcular estatísticas resumidas.
Visão geral da função SUBTOTAL
A função SUBTOTAL Calcula uma estatística de resumo para uma série de dados. As estatísticas disponíveis incluem, mas não estão limitadas a média, desvio padrão, contagem, mínimo e máximo. Veja a lista completa abaixo na seção de entradas de função:
Para usar a função SUBTOTAL de planilha do Excel, selecione uma célula e digite:
(Observe como as entradas da fórmula aparecem)
Sintaxe e entradas da função SUBTOTAL:
| 1 | = SUBTOTAL (núm_função, REF1) |
function_num - Um número que representa a operação a ser executada.
REF1 - Intervalos ou referências contendo dados para calcular.
Qual é a função SUBTOTAL?
O SUBTOTAL é uma das funções exclusivas das planilhas porque ele pode dizer a diferença entre células ocultas e células não ocultas. Isso pode ser bastante útil ao lidar com intervalos filtrados ou quando você precisa configurar cálculos com base em diferentes seleções do usuário. Uma vez que ele também sabe ignorar outras funções SUBTOTAL de seus cálculos, também podemos usá-lo em grandes dados resumidos sem medo de contagem dupla.
Resumo básico com SUBTOTAL
Digamos que você tenha uma tabela de vendas de produtos classificadas e queira criar totais para cada produto, bem como criar um total geral. Você pode usar uma Tabela Dinâmica ou inserir algumas fórmulas. Considere este layout:
Eu coloquei algumas funções SUBTOTAL nas células B5 e B8 que parecem
| 1 | = SUBTOTAL (9, B2: B4) |
A partir da sintaxe, você pode usar uma variedade de números para o primeiro argumento. Em nosso caso específico, estamos usando 9 para indicar que queremos fazer uma soma.
Vamos nos concentrar na célula B9. Ele tem essa fórmula, que inclui todo o intervalo de dados da coluna B, mas não inclui os outros subtotais.
| 1 | = SUBTOTAL (9, B2: B8) |
NOTA: Se você não quiser escrever todas as fórmulas de resumo sozinho, pode ir para a faixa de dados e usar o assistente Esboço - Subtotal. Ele irá inserir linhas automaticamente e colocar as fórmulas para você.
Diferença nos primeiros argumentos
No primeiro exemplo, usamos um 9 para indicar que queríamos fazer uma soma. A diferença entre usar 9 e 109 seria como queremos que a função trate as linhas ocultas. Se você usar as designações 1XX, a função não incluirá linhas que foram ocultadas ou filtradas manualmente.
Aqui está a nossa mesa de antes. Mudamos as funções para que possamos ver a diferença entre os argumentos 9 e 109. Com todos visíveis, os resultados são os mesmos.
Se aplicarmos um filtro para filtrar o valor de 6 na coluna B, as duas funções permanecerão as mesmas.
Se ocultarmos manualmente as linhas, vemos a diferença. A função 109 foi capaz de ignorar a linha oculta, enquanto a função 9 não.
Alterar operação matemática com SUBTOTAL
Talvez você queira, às vezes, dar ao usuário a capacidade de alterar o tipo de cálculo executado. Por exemplo, eles querem obter a soma ou a média. Visto que SUBTOTAL controla a operação matemática por um número de argumento, você pode escrever isso em uma única fórmula. Aqui está nossa configuração:
Criamos uma lista suspensa em D2 onde o usuário pode selecionar "Soma" ou "Média". A fórmula em E2 é:
| 1 | = SUBTOTAL (IF (D2 = "Média", 1, IF (D2 = "Soma", 9)), B2: B4) |
Aqui, a função IF vai determinar qual argumento numérico dar ao SUBTOTAL. Se A5 for “Média”, então produzirá 1 e SUBTOTAL dará a média de B2: B4. Ou, se A5 é igual a “Soma”, então o IF produz um 9 e obtemos um resultado diferente.
Você pode expandir esse recurso usando uma tabela de pesquisa para listar ainda mais tipos de operações que deseja executar. Sua tabela de pesquisa pode ser parecida com esta
Então, você pode alterar a fórmula em E2 para ser
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Fórmulas condicionais com SUBTOTAL
Embora SUBTOTAL tenha muitas operações que pode fazer, ele não pode verificar os critérios por conta própria. No entanto, podemos usá-lo em uma coluna auxiliar para realizar esta operação. Quando você tem uma coluna de dados que sabe sempre Se houver alguns dados nele, você poderá usar a capacidade do SUBTOTAL para detectar linhas ocultas.
Aqui está a tabela com a qual trabalharemos neste exemplo. Eventualmente, gostaríamos de poder somar os valores de “Apple”, mas também deixar o usuário filtrar a coluna Qty.
Primeiro, crie uma coluna auxiliar que abrigará a função SUBTOTAL. Em C2, a fórmula é:
| 1 | = SUBTOTAL (103, A2) |
Lembre-se de que 103 significa que queremos fazer uma CONT.valor. Eu recomendo usar COUNTA porque você pode ter sua célula de referência A2 ser preenchida com qualquer números ou texto. Agora você terá uma tabela parecida com esta:
Isso não parece útil no início, porque todos os valores são apenas 1. No entanto, se ocultarmos a linha 3, aquele "1" em C3 mudará para 0 porque está apontando para uma linha oculta. Embora seja impossível ter uma imagem mostrando o valor da célula oculta específica, você pode verificar ocultando a linha e, em seguida, escrevendo uma fórmula básica como esta para verificar.
| 1 | = C3 |
Agora que temos uma coluna que mudará de valor dependendo se está oculta ou não, estamos prontos para escrever a equação final. Nossos SUMIFS serão parecidos com este
Nesta fórmula, vamos apenas somar os valores da coluna B quando a coluna A for igual a “Apple”, e o valor na coluna C é 1 (ou seja, a linha não está oculta). Digamos que nosso usuário queira filtrar 600, porque parece anormalmente alto. Podemos ver que nossa fórmula dá o resultado correto.
Com essa capacidade, você pode aplicar uma verificação a COUNTIFS, SUMIFS ou mesmo a SUMPRODUCT. Você adiciona a capacidade de permitir que seus usuários controlem alguns fatiadores de mesa e está pronto para criar um painel incrível.
SUBTOTAL no Planilhas Google
A função SUBTOTAL funciona exatamente da mesma forma no Planilhas Google e no Excel:
SUBTOTAL Exemplos em VBA
Você também pode usar a função SUBTOTAL em VBA. Modelo:application.worksheetfunction.subtotal (function_num, reh1)
Executando as seguintes instruções VBA
| 1234567891011121314151617 | Intervalo ("C7") = Application.WorksheetFunction.Subtotal (1, Intervalo ("C2: C5"))Intervalo ("C8") = Application.WorksheetFunction.Subtotal (2, Intervalo ("C2: C5"))Intervalo ("C9") = Application.WorksheetFunction.Subtotal (4, Intervalo ("C2: C5"))Intervalo ("C10") = Application.WorksheetFunction.Subtotal (5, Intervalo ("C2: C5"))Intervalo ("C11") = Application.WorksheetFunction.Subtotal (9, Intervalo ("C2: CE5"))Intervalo ("D7") = Application.WorksheetFunction.Subtotal (1, Intervalo ("D2: D5"))Range ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Intervalo ("D10") = Application.WorksheetFunction.Subtotal (5, Intervalo ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Intervalo ("E7") = Application.WorksheetFunction.Subtotal (1, Intervalo ("E2: E5"))Intervalo ("E8") = Application.WorksheetFunction.Subtotal (2, Intervalo ("E2: E5"))Intervalo ("E9") = Application.WorksheetFunction.Subtotal (4, Intervalo ("E2: E5"))Intervalo ("E10") = Application.WorksheetFunction.Subtotal (5, Intervalo ("E2: E5"))Intervalo ("E11") = Application.WorksheetFunction.Subtotal (9, Intervalo ("E2: E5")) |
irá produzir os seguintes resultados
Para os argumentos da função (núm_da_função, etc.), você pode inseri-los diretamente na função ou definir as variáveis a serem usadas.
Voltar para a lista de todas as funções no Excel
