Olá, você já ouviu falar da função SUBTOTAL no Excel? Então leia este artigo e veja a importância desta função em seu dia a dia. Você já tentou usar a Função SOMA em tabelas com filtros? Você percebeu que não funciona. Para isso utilizamos a função SUBTOTAL no Excel. Este artigo vem de encontro com uma dúvida de uma colega de trabalho que estava tentando fazer uma SOMA em uma tabela com dados filtrados.
Descrição
Retorna um subtotal em uma lista ou em um banco de dados.
Sintaxe
SUBTOTAL(núm_função,ref1,[ref2],...])
A sintaxe da função SUBTOTAL tem os seguintes argumentos:
| Núm_função (inclui valores ocultos) |
Núm_função (ignora valores ocultos) |
Função |
| 1 | 101 | MÉDIA |
| 2 | 102 | CONTA |
| 3 | 103 | CONT.VALORES |
| 4 | 104 | MÁXIMO |
| 5 | 105 | MÍNIMO |
| 6 | 106 | MULT |
| 7 | 107 | DESVPAD |
| 8 | 108 | DESVPADP |
| 9 | 109 | SOMA |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
- Use 101,102…para filtrar células ocultas na função.
- Ref1 Obrigatório. O primeiro intervalo nomeado ou referência cujo subtotal você deseja.
- Ref2,…Opcional. Intervalos nomeados ou referências de 2 a 254 cujo subtotal você deseja.
Descrição
A função SUBTOTAL no Excel é excelente para utilizar em Tabelas com Filtros.
Com esta função podemos Somar, Encontrar o Máximo, Mínimo, Média de uma Tabela mesmo utilizando os filtros.
Exemplo
Podem perceber que utilizando a função SOMA e SUBTOTAL no exemplo acima o resultado é igual. Isto porque ainda não utilizamos nenhum filtro na tabela.
Agora vou filtrar somente a região Nordeste e veja o resultado.
Perceba que na Fórmula SOMA o resultado ficou em 890.360,00 e na Fórmula SUBTOTAL somou somente o Faturamento da Região Nordeste. Isto ocorre porque na Função SOMA o Excel não respeita os filtros, e no SUBTOTAL a soma é feito somente os valores que foram filtrados.
Neste exemplo foi utilizado o número da função 9, agora vou mostrar um exemplo utilizando a função de número 109 para não calcular células ocultas.
No exemplo acima não utilizei o filtro da Tabela, veja que a linha 13 a 24 estão ocultas. Eu selecionei as linhas e ocultei. Com a função 109 do SUBTOTAL ignora as células ocultas somando somente as células visíveis.
E você, já sabia utilizar o SUBTOTAL? Usa atualmente? Este artigo foi útil? Deixe seus comentários.
Você pode baixar a planilha com exemplo abaixo.
[file_download style=”2″][download title=”SUBTOTAL%20no%20Excel” icon=”style1-Xls-64×64.png” file=”http://minhasplanilhas.com.br/wp-content/uploads/2015/11/subtotal_excel.xlsx” package=”” level=”” new_window=”Y”]Arquivo%20de%20exemplo%20utilizado%20no%20artigo%20SUBTOTAL%20no%20Excel.[/download][/file_download]
Grande Abraço
Muito obrigada! sempre acho aqui tudo o que preciso em excel! =)
Oi Kely, que bom saber disso. Me motiva a criar cada vez mais conteúdos. Muito obrigado pelo feedback. Grande Abraço.
Muito obrigada! Isso me ajudou muito mesmo! Finalmente entendi tudo nesse quesito em Excel. Obrigada amigo!
Que bom Cinthia. Sucesso.
Parabens pelo site. Muito didatico e encontrei a resposta para minha duvida rapidamente. Obrigado
Fico Feliz Juliano. Gosto muito quando recebo feedbacks que ajude as pessoas. Sucesso.
=SUBTOTAL(109;A4:A100)
Gostaria de sempre contar a partir da celula A4 até A100
Como faço para ao inserir linha não mude a formula
Olá Elio. Você precisa usar a referência absoluta. =SUBTOTAL(109;$A$4:$A$100). Insira o cifrão antes e depois da Letra, ou clique com o mouse no intervalo e aperte F4.
Parabéns, ótima explicação. Continue assim!
Obrigado Eduarda. Grande Abraço.
Muito obrigado, pela ajuda com função subtotal. Excelente explicação.
Muito bom.