Aprenda como usar os recursos subtotal e agrupar em tabela dinâmica no Excel passo-a-passo com download gratuito da planilha exemplo.
1. Introdução Subtotal e agrupamento Tabela Dinâmica Excel
Para facilitar o trabalho com dados e análises o Excel dispõe de diversos recursos. Um destes recursos é o comando subtotal, que figura entre os mais importantes. Através deste comando é possível inserir de forma rápida agrupamentos de dados, incluindo cálculos subtotais em campos, como soma, contagem, média, máximo, mínimo e produto dos valores.
Outro benefício que temos é a possibilidade de realizar a quebra automática de página para cada grupo, o que dá mais praticidade a algumas tarefas, a exemplo da emissão de relatórios de vendas, cobranças, comissões etc., que podem são estratificadas (detalhadas, exibidas, explodidas) por regionais ou vendedores, a citar.
Este recurso, presente na guia Dados (grupo Estrutura de Tópicos), nos permite também organizar os dados em vários níveis que podem ser expandidos ou contraídos (de maneira similar às tabelas dinâmicas).
2. Usando o comando subtotal
A imagem seguinte apresenta os dados que serão usados como exemplo neste artigo. Nosso objetivo é resumir para cada setor o total de salários e a quantidade de funcionários.
Como precisamos analisar por Setor (primeira coluna) devemos antes classificar esta coluna (de A a Z ou de Z a A). Para isso, podemos selecionar qualquer célula deste campo e acessar a guia Dados e, no grupo “Classificar e Filtrar”, escolher algum comando de classificação. Em nosso caso, selecionamos a célula do primeiro setor e clicamos no comando de classificação em ordem alfabética.
Como resultado, os setores estão classificados alfabeticamente.
Com os dados assim organizados, podemos agora aplicar o subtotal. Para isso, com pelo menos uma célula da região de dados selecionada, acessamos a guia Dados e, no grupo Estrutura de Tópicos, clicamos no comando Subtotal.
A janela a seguir surgirá.
Inicialmente vamos obter a quantidade de funcionários por setor. Para isso, na janela exibida, escolhemos para cada alteração em Setor usar a contagem dos nomes de funcionários e resumir abaixo dos dados, conforme imagem a seguir.
Note a disposição final dos dados na imagem a seguir (destacados manualmente e propositalmente em vermelho).
Perceba que a contagem de funcionários de cada setor é exibida abaixo do nome do seu último funcionário. Perceba também que a contagem de funcionários do setor Norte é 11 e temos também a contagem geral, que resulta em 38. Observe também que ao lado esquerdo dos títulos de linhas temos níveis de agrupamentos. Nós podemos clicar para expandir (+) ou contrair (-).
Na próxima imagem note o que ocorre quando clicamos para contrair tudo, ao clicar em todos os (-) do nível 2. Podemos contrair tudo de uma vez clicando diretamente em algum nível, na região em que aponta a seta em vermelho evidenciada na imagem seguinte.
Para voltar a expandir os dados em todos os níveis, podemos clicar em cada (+) ou clicar diretamente no nível 3, que é o maior nível disponível.
Para inserir um subtotal que exiba o valor total de salários de cada setor devemos seguir o mesmo caminho descrito para a contagem realizada.
Na janela de edição do subtotal (imagem anterior), indicamos que para cada alteração em Setor desejamos somar os valores de salários atuais. A imagem seguinte apresenta em vermelho o valor total de salários de forma setorizada.
Abaixo da última ocorrência de cada setor há uma linha com o total de salários, exibido na última coluna. Para o setor Norte, por exemplo, o valor total de salários é R$ 51.294,02, sendo o total geral de salários o valor de R$ 157.664,26.
Para eliminarmos os subtotais devemos nos posicionar com a seleção na região dos dados e seguir o mesmo caminho que indicamos na implementação da contagem de funcionários e na soma de salários. Após isso, clicamos em “Remover todos” (destaque em vermelho na imagem seguinte).
Outra opção interessante que temos à disposição é quebra de página entre os grupos, que pode ser habilitada conforme o destaque em verde na imagem anterior. Esta opção organiza e configura a impressão para separar cada grupo em páginas. Cabe destacar que isto terá o mesmo reflexo em PDFs exportados.
3. Subtotal e Agrupar por Tópicos em Tabela Dinâmica
Nós podemos inserir subtotais automáticos com autotópicos quando os dados já possuem resumos disponibilizados por fórmulas.
Consideremos os dados a seguir. Note que não temos ainda as somas mensais, nem por filiais, nem por categorias e nem por trimestres, bem como os totais gerais.
Nós podemos inserir as somas ao selecionar apenas as células em branco e usar o botão da autosoma. Os dados terão então os totalizadores conforme imagem a seguir.
Na sequência, com os dados devidamente apresentando os totais em vários níveis, podemos simplesmente posicionar a seleção na região dos dados (pelo menos uma célula) e acessar a guia Dados e, no grupo Estrutura de Tópicos, clicar em mais opções do comando “Agrupar” e escolher a opção “Autotópicos”, conforme mostra a imagem a seguir. Não devemos clicar diretamente no comando “Agrupar”, e sim, na setinha para mais opções.
Com isso, os tópicos serão inseridos automaticamente para cada nível. Veja na imagem a seguir que temos 3 níveis para os nossos dados, permitindo: no nível 1, a totalização geral, no nível 2 a totalização por filiais e trimestres e no nível 3 o detalhamento mensal e por categorias.
Veja na imagem a seguir o resultado quando expandimos até o nível 2, tanto na coluna quanto na linha.
Para remover os subtotais devemos nos posicionar na região de dados e, na guia Dados, acessar mais opções do comando “Desagrupar” (no grupo Estrutura de Tópicos), escolhendo ao final a opção “Limpar estrutura de tópicos”.
Download Planilha Subtotal e Agrupar Tabela Dinâmica
Para realizar o download da planilha de cálculo de subtotal e agrupar com tabela dinâmica no Excel clique no botão abaixo.
