0075-Função SUBTOTAL

A função SUBTOTAL permite que flexibilizemos diferentes estatísticas de consolidação sem a necessidade se uso de várias funções.
A sua sintaxe é SUBTOTAL(índice;intervalo_dados).

  • indice: nº que determina qual a estatística de resumo;
  • intervalo_dados: intervalo de células cujas informações serão consolidadas.

Listaremos a seguir os valores de índice e funções correspondentes.

função índice
MÉDIA 1
CONT.NÚM 2
CONT.VALORES 3
MÁXIMO 4
MÍNIMO 5
MULT 6
DESVPAD 7
DESVPADP 8
SOMA 9
VAR 10
VARP 11

Com base nesta capacidade da função de resumir os dados de diferentes formas, vamos utilizá-la para construção de uma estrutura que nos permitirá definir a função de resumo desejada de forma bem simples.

Primeiramente vamos criar uma constante matricial para relacionar as funções e os valores de índice.

Para construirmos uma constante matricial precisamos saber primeiramente que, a exemplo das fórmulas matriciais, ela é identificada pela presença de chaves ({ }). Um ponto (“.”) é utilizado para separar valores em colunas e um ponto-e-vírgula (“;”) é utilizado para separar linhas. Transformando a tabela acima numa constante matricial temos: {“função”.”índice”;”MÉDIA”.1;”CONT.NÚM”.2;”CONT.VALORES”.3;”MÁXIMO”.4;

“MÍNIMO”.5;”MULT”.6;”DESVPAD”.7;”DESVPADP”.8;”SOMA”.9;”VAR”.10;”VARP”.11}

Dica: Se não quiser decorar a regra dos separadores, digite sua tabela normalmente num intervalo de células do Excel, como, por exemplo, A1:B12. Em uma outra célula digite a fórmula =A1:B12. Selecione a fórmula na barra de fórmulas e pressione F9. Pronto. Esta é a sua constante matricial. Copie-a e cole nas fórmulas que precisar.

Com base nesta constante podemos utilizar a função PROCV para obter o valor de índice a partir do nome da função desejada. A fórmula =PROCV(“‘MÉDIA”;{“função”.”índice”;”MÉDIA”.1;”CONT.NÚM”.2;”CONT.VALORES”.3;”MÁXIMO”.4;

“MÍNIMO”.5;”MULT”.6;”DESVPAD”.7;”DESVPADP”.8;”SOMA”.9;”VAR”.10;”VARP”.11};2;FALSO), retornará o valor 1, índice correspondente à função MÈDIA.

Para não termos necessidade de ficar digitando estes nomes de função, podemos associá-los a uma validação de intervalos para que o usuário possa selecionar apenas valores pré-definidos.

Vamos construir o nosso exemplo da seguinte forma:
intervalo_dados: A2:A21
célula_validade: C2
fórmula: =SE(C2=””;””;SUBTOTAL(PROCV(C2;{“MÉDIA”.1;”CONT.NÚM”.2;”CONT.VALORES”.3;”MÁXIMO”.4;”MÍNIMO”.5;

“DESVPAD”.7;”DESVPADP”.8;”SOMA”.9};2;FALSO);A2:A21))
aspecto:

Desta forma, selecionando um dos nomes da lista em C2, podemos obter as diferentes estatísticas de resumo. Isto é particularmente interessante quando criamos relatórios que possam ser dinamicamente modificados pelos usuários dos arquivos.

Veja o arquivo pronto em: Consolidação Flexível

In Excel We Trust

Marcado com: , , , , , , ,
Publicado em Nível: Intermediário
2 comentários em “0075-Função SUBTOTAL
  1. André disse:

    Boa tarde.

    Nao consigo usar a formula, retorna erro, nao consigo nem conclui-la.

    =SE(C2=””;””;SUBTOTAL(PROCV(C2;{“MÉDIA”.1;”CONT.NÚM”.2;”CONT.VALORES”.3;”MÁXIMO”.4;”MÍNIMO”.5;“DESVPAD”.7;”DESVPADP”.8;”SOMA”.9};2;FALSO);A2:A21))

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,963,899

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.476 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: