Excel – Somando apenas as células visíveis no AutoFiltro

Como complemento do último post, que dava a possibilidade de efetuar operações matemáticas em células filtradas através do VBA, adiciono uma alternativa disponibilizada por um colega do fórum INFO.

Existe uma forma mais simples de fazer operações matemáticas em dados filtrados fazendo uso da função SUBTOTAL.

A descrição da Ajuda do Excel fala por si:

A função SUBTOTAL retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, na guia Dados. Assim que a lista de subtotais for criada, você poderá modificá-la editando a função SUBTOTAL.

Sintaxe

SUBTOTAL(núm_função, ref1, ref2, …)

Núm_função é o número de 1 a 11 (incluindo valores ocultos) ou 101 a 111 (ignorando valores ocultos) que especifica qual função usar no cálculo de subtotais dentro de uma lista.

Núm_função
(incluindo valores ocultos)
Núm_função
(ignorando valores ocultos)
Função
1 101 MÉDIA
2 102 CONTA
3 103 CONT.VALORES
4 104 MÁX
5 105 MÍN
6 106 MULT
7 107 DESVPAD
8 108 DESVPADP
9 109 SOMA
10 110 VAR
11 111 VARP

Ref1, ref2 são de 1 a 254 intervalos ou referências cujo subtotal você deseja.

Comentários

  • Se existirem outros subtotais dentro de ref1; ref2,… (ou subtotais aninhados), esses subtotais aninhados serão ignorados para evitar dupla contagem.
  • Para as constantes núm_função de 1 a 11, a função SUBTOTAL inclui os valores de linhas ocultas pelo comando Ocultar Linhas no submenu Ocultar e Exibir do comando Formato no grupo Células, na guia Início). Use essas constantes para subtotalizar números ocultos e não-ocultos em uma lista. Para as constantes núm_função de 101 a 111, a função SUBTOTAL ignora valores de linhas ocultos pelo comando Ocultar Linhas. Use essas constantes para subtotalizar somente números não-ocultos em uma lista.
  • A função SUBTOTAL ignora as linhas não incluídas no resultado de um filtro, independentemente de qual valor de núm_função seja utilizado.
  • A função SUBTOTAL foi projetada para colunas de dados ou intervalos verticais. Ela não foi projetada para linhas de dados nem intervalos horizontais. Por exemplo, quando você subtotaliza um intervalo horizontal usando uma núm_função de 101 ou maior, como SUBTOTAL(109,B2:G2), ocultar uma coluna não afeta o subtotal. Porém, ocultar uma linha em um subtotal de um intervalo vertical afeta o subtotal.
  • Se qualquer uma das referências for uma referência 3D, a função SUBTOTAL retornará o valor de erro #VALOR!.

Exemplo

Portanto, considerando a mesma planilha do post anterior, cuja fórmula mencionada foi:

=Soma_Celulas_Visiveis(C2:C50)

pode ser substituída por:

=SUBTOTAL(9;C2:C50)

A vantagem óbvia é a simplicidade de uso e a dispensa do uso de macros. O post anterior mantém sua utilidade para execução de funções mais complexas em dados não filtrados.

Agradecimentos

Fica meu agradecimento ao usuário Avatar_2010 do fórum INFO de Excel sobre a dica colocada. Valeu garoto!

Bom proveito!