Baixe a apostila de exemplo
Este tutorial demonstrará como calcular "subtotal se", contando apenas as linhas visíveis com critérios.
Função SUBTOTAL
A função SUBTOTAL pode realizar vários cálculos em uma gama de dados (contagem, soma, média, etc.). Mais importante, ele pode ser usado para calcular apenas linhas visíveis (filtradas). Neste exemplo, usaremos a função para contar (CONT.valores) linhas visíveis, definindo o argumento SUBTOTAL function_num como 3 (uma lista completa de funções possíveis pode ser encontrada aqui.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Observe como os resultados mudam à medida que filtramos as linhas manualmente.
SUBTOTAL SE
Para criar um “Subtotal If”, usaremos uma combinação de SUMPRODUCT, SUBTOTAL, OFFSET, ROW e MIN em uma fórmula de matriz. Usando esta combinação, podemos essencialmente criar uma função genérica “SUBTOTAL IF”. Vejamos um exemplo.
Temos uma lista de membros e seu status de participação para cada evento:
Suponha que sejamos solicitados a contar o número de membros que participaram de um evento dinamicamente conforme filtramos manualmente a lista da seguinte forma:
Para fazer isso, podemos usar esta fórmula:
= SUMPRODUCT ((=) * (SUBTOTAL (3, OFFSET (, LINHA () - MIN (LINHA ()), 0))))
= SUMPRODUCT ((D2: D14 = "Frequentou") * (SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Ao usar o Excel 2022 e anteriores, você deve inserir a fórmula de matriz pressionando CTRL + SHIFT + ENTER para informar ao Excel que você está inserindo uma fórmula de matriz. Você saberá que a fórmula foi inserida corretamente como uma fórmula de matriz quando colchetes aparecerem ao redor da fórmula (veja a imagem acima).
Como funciona a fórmula?
A fórmula funciona multiplicando duas matrizes dentro de SUMPRODUCT, onde a primeira matriz lida com nossos critérios e a segunda matriz filtra apenas para linhas visíveis:
= SUMPRODUCT (*)
A Matriz de Critérios
A matriz de critérios avalia cada linha em nosso intervalo de valores (Status “Atendido” neste exemplo) e gera uma matriz como esta:
=(=)
= (D2: D14 = "Atendido")
Saída:
{VERDADE; FALSO; FALSO; VERDADE; FALSO; TURE; TURE; TURE; FALSO; FALSO; VERDADE; FALSO; VERDADE}
Observe que a saída da primeira matriz em nossa fórmula ignora se a linha está visível ou não, que é onde nossa segunda matriz entra para ajudar.
The Visibility Array
Usando SUBTOTAL para excluir linhas não visíveis em nosso intervalo, podemos gerar nossa matriz de visibilidade. No entanto, SUBTOTAL sozinho retornará um único valor, enquanto SUMPRODUCT está esperando uma matriz de valores. Para contornar isso, usamos OFFSET para passar uma linha por vez. Esta técnica requer alimentar OFFSET com um array que contém um número de cada vez. A segunda matriz tem a seguinte aparência:
= SUBTOTAL (3, OFFSET (, LINHA () - MIN (LINHA ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Saída:
{1;1;0;0;1;1}
Costurando os dois juntos:
= SUMPRODUTO ({VERDADEIRO; VERDADEIRO; FALSO; FALSO; VERDADEIRO; VERDADEIRO} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL SE com múltiplos critérios
Para adicionar vários critérios, basta vários mais critérios juntos no SUMPRODUCT, como:
= SUMPRODUCT ((=) * (=) * (SUBTOTAL (3, OFFSET (, LINHA () - MIN (LINHA ()), 0))))
= SUMPRODUCT ((E2: E14 = "Frequentou") * (B2: B14 = 2019) * (SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
SUBTOTAL SE no Google Sheets
A função SUBTOTAL IF funciona exatamente da mesma forma no Planilhas Google e no Excel:
