A função SUBTOTAL é bastante útil para fazer cálculos de intervalos desconsiderando as linhas ocultas, ou seja, considerar somente as linhas que estão no contexto de filtro da tabela. Muito útil, porém com limitações, uma vez que ela permite o uso limitado de 11 funções de agregações que escolhemos conforme a lista que o intelisense nos sugere ao montar a fórmula com esta função.
Já vi algumas vezes em fóruns a dúvida de como fazer um cálculo condicional com o intervalo filtrado, ou seja, aplicar funções como SOMASE, CONT.SE, SOMASES, CONT.SES, MÉDIASE e demais funções com terminações SE’s. Neste caso não conseguimos aplicar o SUBTOTAL, pois estes tipos de funções não estão dentro da lista do primeiro argumento do SUBTOTAL.
Pra contornar esta situação, quero sugerir uma solução, que talvez não seja a mais usual devido a necessidade de fazer uso de uma coluna auxiliar, mas que funciona e pode resolver rapidamente este tipo de necessidade.
Vamos a um exemplo:
Como realizamos cálculos considerando somente as linhas visíveis? Cálculos como a soma dos valores negativos, ou contar quantas maçãs aparecem, etc…
A solução é bastante simples, e usamos a função SUBTOTAL, não para gerar a resposta final, mas como função numa coluna auxiliar que servirá de argumento nas fórmulas condicionais. Podemos usar a função para trazer o próprio valor da linha, ou trazer um parâmetro. Para efeito de didática, eu vou ilustrar com um parâmetro “S” para linha visível e “N” para linhas ocultas. Veja a fórmula na imagem abaixo:
CURIOSIDADE: Nem sempre necessitamos fazer um teste lógico no primeiro argumento da função SE. Qualquer valor numérico diferente de 0 é considerado VERDADEIRO na função. Por isso que nem me preocupei em fazer um teste lógico na fórmula.
Visualmente nunca conseguiremos ver o “N”, mas acredite, ao filtrar a tabela, todas as linhas ocultas ficarão com o parâmetro “N” na coluna D. Isso porque o 1º argumento definido como 109 representa a SOMA ignorando os valores ocultos (Mais informações sobre a função SUBTOTAL).
Com esta coluna auxiliar, podemos desenvolver normalmente as fórmulas com as funções da família …SE(s) usando esta coluna como um dos critérios sendo igual a “S”.
Veja exemplos na imagem:
Uma outra alternativa seria usar a função AGREGAR alternativamente à SUBTOTAL, pois ela também tem a possibilidade de ignorar valores ocultos da planilha (Veja mais info…)
Baixe a planilha com este exemplo aqui: ExcelManiacos – Subtotal com condicional
