Inserir subtotais em uma lista de dados em uma planilha Excel

Fórmula matricial com duas condições Excel
Fórmula matricial com duas condições Excel
20 de junho de 2012
Painel de Acompanhamento de Projetos - Excel
Painel de Acompanhamento de Projetos – Excel
30 de junho de 2012

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
  • ref1-> Selecionar o intervalo de células que se quer filtrar. Deve ser sempre uma lista horizontal sem utilizar várias colunas.

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.

GUT PPT

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/

2 Comentários

  1. Volnei disse:

    Marcos, tudo bem ?!
    Estou “tentando” adaptar este código em uma planilha de produtos. A dificuldade é que os códigos dos produtos (coluna A) são numeros e a consulta eu gostaria de fazer como ocorre para texto. Ou seja, conforme é digitado o código, ou parte dele, o filtro já vai ocorrendo.

    Segue alguns códigos que utilizo:

    64801002
    64802002
    64801000
    64802000
    64803000
    54801002
    54802002
    54801000
    54802000
    54801001
    54802001
    54801005
    54802005
    54801004
    54802004

    Nestes codigos a logica é o seguinte:
    1º digito = origem do produto
    2º, 3º e 4º digitos = familia do produto
    5º digito = voltagem/tensão
    6º, 7º e 8º digitos = versão/modelo
    com isto, se eu digitar uma sequencia correspondente a familia do produto, irá me retornar ignorando a voltagem, modelo e a origem.

    Consegue me ajudar !!??

    Desde ja agradeço.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.