Como Agrupar Dados Excel -Estrutura de Tópicos Subtotal

Neste artigo você aprenderá como agrupar dados no Excel usando a Estrutura de Tópicos e Subtotal.

1. Agrupar Dados no Excel

No Excel podemos lançar mão de diversos recursos que facilitam as nossas análises. Um destes recursos é o agrupamento de dados direto na fonte de dados, podendo incluir também subtotais.

Este recurso, possível de se aplicar através da guia Dados, no grupo Estrutura de Tópicos, nos permite organizar os dados em vários níveis que podem ser expandidos ou contraídos (de maneira similar às tabelas dinâmicas).

2. Agrupar Dados com subtotal

Os dados a seguir serão utilizados como exemplo para o agrupamento, que visa resumir para cada setor o total de salários e a quantidade de funcionários.

Como-agrupar-dados-no-Excel-Estrutura-de-Topicos-1

Nós desejamos inserir análises de acordo com cada Setor (primeira coluna). Para isso, faz-se necessário que classifiquemos esta coluna (de A a Z ou de Z a A). Para isso, podemos selecionar alguma 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 agrupar dados no Excel - Estrutura de Tópicos 2

Note a seguir que os setores estão agora classificados de A a Z (ordem alfabética).

Como agrupar dados no Excel - Estrutura de Tópicos 3

Com os dados desta maneira, podemos agora aplicar o recurso. 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.

Como agrupar dados no Excel - Estrutura de Tópicos 4

A janela a seguir surgirá.

Como agrupar dados no Excel - Estrutura de Tópicos 5

Inicialmente vamos obter a quantidade de funcionários de cada setor. Para isso, na janela que surgiu, escolhemos para cada alteração em Setor usar a contagem dos nomes de funcionários e resumir abaixo dos dados, conforme imagem a seguir.

Como agrupar dados no Excel - Estrutura de Tópicos 6

Note como ficarão os dados na imagem a seguir (destacados manualmente e propositalmente em vermelho).

Como agrupar dados no Excel - Estrutura de Tópicos 7

Perceba que a contagem de funcionários de cada setor é exibida abaixo do nome do último funcionário de cada setor. Note que a contagem de funcionários do setor Sudeste é 6 e temos também a contagem geral, que resulta em 38. Perceba também que ao lado esquerdo dos títulos de linhas temos níveis de agrupamentos. Nós podemos clicar para expandir (+) ou contrair (-).

Como agrupar dados no Excel - Estrutura de Tópicos 8

Na imagem a seguir, 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 aonde aponta a seta em vermelho na imagem a seguir.

Como agrupar dados no Excel - Estrutura de Tópicos 9

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.

Como agrupar dados no Excel - Estrutura de Tópicos 10

Para inserir um subtotal que exiba o total de salários de cada setor devemos seguir o mesmo caminho que empregamos para a contagem até então realizada.

Como agrupar dados no Excel - Estrutura de Tópicos 11

Na janela de implementação (imagem anterior), indicamos que para cada alteração em Setor desejamos somar os valores de salários atuais. Note o resultado final na imagem a seguir e em vermelho o total de salários de cada Setor.

Como agrupar dados no Excel - Estrutura de Tópicos 12

Note que abaixo da última ocorrência de cada setor há uma linha com o total de salários exibido na última coluna. Para o setor Sudeste, por exemplo, o total de salários é R$ 25.412,63 e o total geral de salários é R$ 157.664,26.

Para removermos os agrupamentos 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”.

Como agrupar dados no Excel - Estrutura de Tópicos 13

3. Agrupamento com autotópicos

Quando os dados já possuem resumos disponibilizados por fórmulas nós podemos nos aproveitar disto e inserir agrupamentos automáticos com autotópicos. 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.

Como agrupar dados no Excel - Estrutura de Tópicos 14

Nós podemos inserir as somas ao selecionar apenas as células em branco e empregar o botão da autosoma. Os dados terão então os totalizadores conforme imagem a seguir.

Como agrupar dados no Excel - Estrutura de Tópicos 15

Após isso, 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.

Como agrupar dados no Excel - Estrutura de Tópicos 16

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.

Como agrupar dados no Excel - Estrutura de Tópicos 17

Veja na imagem a seguir o resultado quando expandimos até o nível 2, tanto na coluna quanto na linha.

Como agrupar dados no Excel - Estrutura de Tópicos 18

Para remover os autotópicos nos posicionamos na região de dados e na guia Dados acessamos mais opções do comando Desagrupar (no grupo Estrutura de Tópicos), escolhendo ao final a opção Limpar estrutura de tópicos.

Como agrupar dados no Excel - Estrutura de Tópicos 19

Download da Planilha Agrupar Dados no Excel

Realize o download da planilha de exemplo de Agrupar Dados no Excel deste artigo neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha