O desafio de hoje é o seguinte: você tem uma tabela e precisa aplicar alguns cálculos de soma, média e contagem. Porém, quando você aplica um filtro nessa tabela, os cálculos ainda continuam considerando os valores ocultos pelo filtro. E agora, como somar apenas dados filtrados no Google Planilhas?
De forma direta e com um exercício prático, vamos te mostrar como fazer operações e agregações apenas com os valores que estão visíveis. Além de soma, média e contagem, também funcionará para obter valores mínimos, máximo, desvio padrão e outras agregações.
Função SUBTOTAL
Nesse exemplo, temos a seguinte tabela de dados:
Perceba que abaixo do campo de busca, existem as métricas Ticket Médio e Faturamento. Para calcular essas métricas, que são baseadas puramente na coluna E (valor), vamos utilizar a SUBTOTAL – uma função muito poderosa do Google Planilhas e Excel.
Faça uma cópia da Planilha
Essa é a sintaxe da função SUBTOTAL:
=SUBTOTAL(código_da_funcao, intervalo1, [intervalo2, ...])Onde o argumento mais importante dessa função é o código da função. Ele é que vai definir como os cálculos serão feitos, que no nosso caso, precisaremos que considere somente os valores visíveis.
Para isso, todo o código deverá ter o prefixo 10 e mais algum outro número de 1 a 11. Com isso, o ticket médio usando somente os valores filtrados da coluna E foi calculado usando:
=SUBTOTAL(101;E10:E32)Enquanto o faturamento total:
=SUBTOTAL(109;E10:E32)Ou seja, o que mudou foi somente o código da função de 101 para 109. Aqui abaixo deixaremos todos os códigos possíveis para você utilizar na função SUBTOTAL no Google Planilhas.
Códigos da função SUBTOTAL
Escolha a operação desejada e para considerar somente os dados visíveis use o código da função da última coluna da tabela abaixo.
| Operação | Código da função para todos os valores | Código da função apenas para valores filtrados |
|---|---|---|
| MÉDIA | 1 | 101 |
| CONT.NÚM | 2 | 102 |
| CONT.VALORES | 3 | 103 |
| MÁXIMO | 4 | 104 |
| MÍNIMO | 5 | 105 |
| MULT | 6 | 106 |
| DESVPAD | 7 | 107 |
| DESVPADP | 8 | 108 |
| SOMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Ou seja, se quiséssemos fazer uma contagem no lugar de uma média ou soma, a função seria:
=SUBTOTAL(103;E10:E32) // usando código 103 para representar a agregação de CONT.VALORESComo somar apenas dados filtrados com critério ou filtro
Em outro cenário, suponhamos que além de vencer a questão de considerar somente os valores visíveis, você também precise aplicar uma condição ao SUBTOTAL.
Considerando a mesma tabela de dados, além de aplicar o SUBTOTAL, queremos agora que considere somente os dados onde a coluna Status (G) seja igual a Enviado.
Para realizar uma SOMA:
=SOMA(FILTER(E10:E32; (G10:G32="Enviado") * MAP(E10:E32; LAMBDA(r; SUBTOTAL(3; r)))))MÉDIA:
=MÉDIA(FILTER(E10:E32; (G10:G32="Enviado") * MAP(E10:E32; LAMBDA(r; SUBTOTAL(3; r)))))MÁXIMO:
=MÁXIMO(FILTER(E10:E32; (G10:G32="Enviado") * MAP(E10:E32; LAMBDA(r; SUBTOTAL(3; r)))))MÍNIMO:
=MÍNIMO(FILTER(E10:E32; (G10:G32="Enviado") * MAP(E10:E32; LAMBDA(r; SUBTOTAL(3; r)))))CONT.VALORES:
=CONT.VALORES(FILTER(E10:E32; (G10:G32="Enviado") * MAP(E10:E32; LAMBDA(r; SUBTOTAL(3; r)))))Em todos os casos, alteramos somente a função inicial. Para adicionar mais de um critério, siga o fluxo normal da função FILTER – já que ela pode receber inúmeras condições. Adicionalmente, utilizamos a função MAP e LAMBDA.
Esperamos que tenha gostado de aprender mais sobre agregações apenas com os dados visíveis no Google Planilhas. Caso tenha ficado com alguma dúvida, use a nossa seção de comentários.
Participe também da Comunidados, nosso grupo 100% gratuito no WhatsApp.
