Inserir subtotais em uma lista de dados em uma planilha Excel
Objetivo: Demonstrar a utilização da função subtotal para realizar funções em listas filtradas de dados no Excel.
A função subtotal executa vários tipos de operações em listas de dados, excetuando dos cálculos os dados filtrados da coluna.
Ele é extremamente útil em dados filtrados, pois se forem utilizadas funções como SOMA, MÉDIA, MÍN, MÁX, CONT.VALORES entre outras, os valores que estão filtrados serão também considerados por estas funções, fazendo com que o resultado não seja o esperado, conforme a situação.
Outra dica é utilizar a função subtotal acima da lista de dados, de forma que os dados filtrados sejam somente abaixo desta função. Desta forma os dados subtotais serão sempre exibidos e não filtrados junto com a lista de dados, e isto também se aplica ás funções padrão, pode ser utilizado em um resumo em outra planilha ou ainda acima dos dados, mas não abaixo de uma lista filtrada.
A utilização desta função é um pouco diferente do que conhecemos, pois ela é uma função que permite o uso de diversas outras. Com a função subtotal você terá as funções SOMA, MÁX, MÍN, CONT.VALORES, entre outras, mudando o parâmetro núm_função, conforme demonstrado abaixo.
Para utilizá-la você tem a sintaxe:
- =SUBTOTAL(núm_função, ref1)
- núm_função -> número da função conforme a tabela abaixo:
- 1->MÉDIA
- 2->CONTA
- 3->CONT.VALORES
- 4->MÁX
- 5->MÍN
- 6->MULT
- 7->DESVPAD
- 8->DESVPADP
- 9->SOMA
- 10->VAR
- 11->VARP
Para este artigo foi utilizada a planilha http://guiadoexcel.com.br/lista-com-filtro-de-dados-automatico-3, ao digitar nas caixas de texto a planilha executará o filtro automaticamente.
No exemplo foram colocadas as funções =SUBTOTAL(1;F3:F9000) para realizar a média das comissões que estão sendo exibidas e também a função =SUBTOTAL(9;H3:H9000) para a soma dos estoques das linhas não ocultas.
Abraço
Marcos Rieper
