Fórmula SUBTOTAL SE – Excel e Google Planilhas
Este tutorial vai demonstrar como calcular o “subtotal se” executando a função SUBTOTAL somente em células que atendam a determinados critérios.
Função SUBTOTAL
A função SUBTOTAL é usada para realizar vários cálculos em um intervalo de dados (contagem, soma, média, etc.). Se você não estiver familiarizado com a função, talvez esteja se perguntando por que não usar as funções CONT.NÚM, SOMA ou MÉDIA… Há dois bons motivos:
- Você pode criar uma tabela que liste as opções SUBTOTAIS (1,2,3,4, etc.) e copiar uma única fórmula para criar dados resumidos. (Isso pode economizar muito tempo se você estiver tentando calcular o SE SUBTOTAL, como demonstramos neste artigo).
- A função SUBTOTAL pode ser usada para calcular somente as linhas visíveis (filtradas).
Vamos nos concentrar na segunda implementação da função SUBTOTAL.
Neste exemplo, usaremos a função para contar (CONT.NÚM) as linhas visíveis, definindo o primeiro argumento núm_função da função SUBTOTAL 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 manualmente as linhas.
SUBTOTAL SE
Para criar um “Subtotal Se”, usaremos uma combinação de SOMARPRODUTO, SUBTOTAL, DESLOC, LIN e MÍNIMO em uma fórmula de matriz. Usando essa combinação, podemos essencialmente criar uma função genérica “SUBTOTAL SE”. Vamos ver um exemplo.
Temos uma lista de membros e seu status de participação em cada evento:
Suponhamos que sejamos solicitados a contar o número de membros que participaram de um evento dinamicamente. Fazemos isto filtrando manualmente a lista da seguinte forma:
Para fazer isso, podemos usar esta fórmula:
=SOMARPRODUTO((<intervalo de valores>=<critério>)*(SUBTOTAL(3;DESLOC(<1a célula no intervalo>;LIN(<intervalo de valores>)-MÍNIMO(LIN(<intervalo de valores>));0))))=SOMARPRODUTO((D2:D14="Presente")*(SUBTOTAL(3;DESLOC(D2;LIN(D2:D14)-MÍNIMO(LIN(D2:D14));0))))Ao usar o Excel 2019 e versões anteriores, você deve inserir a fórmula de matriz pressionando CTRL + SHIFT + ENTER para informar ao Excel que está inserindo uma fórmula de matriz. Você saberá que a fórmula foi inserida corretamente como uma fórmula de matriz quando aparecerem colchetes ao redor da fórmula (veja figura acima).
Como a fórmula funciona?
A fórmula funciona multiplicando duas matrizes dentro de SOMARPRODUTO, em que a primeira matriz lida com nossos critérios e a segunda matriz filtra apenas as linhas visíveis:
=SOMARPRODUTO(<matriz de critérios>*<matriz de visibilidade>)A Matriz de Critérios
A matriz de critérios avalia cada linha em nosso intervalo de valores (status “Presente”, neste exemplo) e gera uma matriz como esta:
=(<intervalo de valores>=<critério>)=(D2:D14="Presente")Resultado:
{VERDADEIRO; FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO}Observe que a saída na primeira matriz em nossa fórmula ignora se a linha está visível ou não, e é aí que nossa segunda matriz entra para ajudar.
A Matriz de Visibilidade
Usando SUBTOTAL para excluir as linhas não visíveis em nosso intervalo, podemos gerar nossa matriz de visibilidade. No entanto, o SUBTOTAL sozinho retornará um único valor, enquanto o SOMARPRODUTO está esperando uma matriz de valores. Para contornar isso, usamos o DESLOC para passar uma linha de cada vez. Essa técnica requer a alimentação do DESLOC com uma matriz que contenha um número de cada vez. A segunda matriz tem a seguinte aparência:
=SUBTOTAL(3;DESLOC(<1a célula no intervalo>;LIN(<intervalo de valores>)-MÍNIMO(LIN(<intervalo de valores>));0))=SUBTOTAL(3;DESLOC(D2;LIN(D2:D14)-MÍNIMO(LIN(D2:D14));0))Resultado:{1;1;0;0;1;1}Juntar os dois:=SOMARPRODUTO({VERDADEIRO; VERDADEIRO; FALSO; FALSO; VERDADEIRO; VERDADEIRO} * {1; 1; 0; 0; 1; 1})= 4
SUBTOTAL SE com Vários Critérios
Para adicionar vários critérios, basta juntar mais critérios dentro do SUMPRODUCT da seguinte forma:
=SOMARPRODUTO((<intervalo de valores 1>=<critério 1>)*(<intervalo de valores 2>=<critério 2>)*(SUBTOTAL(3;DESLOC(<1a célula no intervalo>;LIN(<intervalo de valores>)-MÍNIMO(LIN(<intervalo de valores>));0))))=SOMARPRODUTO((E2:E14="Presente")*(B2:B14=2019)*(SUBTOTAL(3;DESLOC(E2;LIN(E2:E14)-MÍNIMO(LIN(E2:E14));0))))
SUBTOTAL SE no Google Planilhas
A função SUBTOTAL SE funciona exatamente da mesma forma no Google Planilhas e no Excel. Exceto pelo fato de que, ao usar CTRL + SHIFT + ENTER para inserir a fórmula da matriz, o Planilhas Google adiciona a função ARRAYFORMULA à fórmula (você também pode adicionar essa função manualmente).
