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:

1. 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).
2. 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).