Como agrupar linhas e colunas com contornos e subtotais no Excel
Se você for um analista de dados profissional, provavelmente lidará com várias planilhas em pastas de trabalho do Excel. Existem milhares de colunas e linhas em um arquivo Excel, dificultando a extração de dados significativos.
No entanto, os recursos do Excel como esboço, dados de grupo e subtotais em uma lista de dados são essenciais para analisar dados de negócios e apresentar relatórios impressionantes para o público ou cliente.
Como formatar seus dados para agrupar colunas no Excel
Antes de prosseguir para criar uma visualização de dados impressionante usando a função de subtotal do Excel e o grupo automático do Excel, você precisa reestruturar os dados brutos.
Para adicionar um subtotal aos dados do Excel, primeiro, você precisará classificar a lista de dados. Em seguida, você pode usar a função Classificação do Excel da seguinte maneira:
- Clique em qualquer cabeçalho de coluna que você deseja classificar.
- Na faixa de opções da guia Início , procure por Edição .
- Agora, clique em Classificar e filtrar e escolha Classificar de A a Z , Classificar de Z a A ou Classificação Personalizada , como achar adequado.
Quando quiser agrupar colunas no Excel, certifique-se de que a planilha contenha conjuntos de dados com cabeçalhos de coluna exclusivos, sem colunas ou linhas vazias e subtotais para cada subconjunto de linha. Após a reestruturação, seus dados devem ser semelhantes aos mostrados nesta imagem:
Você pode baixar esta planilha gratuita do Excel para praticar a função Contornos e subtotais antes de trabalhar com dados da vida real.
Como adicionar subtotal em conjuntos de dados do Excel
A função de subtotal do Excel permite calcular totais gerais e subtotais para uma coluna em uma lista de dados. Você não precisa formatar milhares de colunas e linhas, pois o Excel irá reestruturar a lista de dados automaticamente.
A função Subtotal do Excel agrupa automaticamente os dados, e você poderá usar funções como CONTAGEM, SOMA, MÉDIA, Máx., Mín., Produto, Números de contagem, Desvio padrão, etc. Para adicionar subtotais à planilha do Excel, siga estas etapas:
- Classifique os dados da planilha de acordo com o título da coluna para a qual você deseja adicionar subtotais.
- Na planilha de exemplo atual, a coluna Categoria é a base para a classificação dos dados.
- Agora, selecione qualquer célula dentro do intervalo de dados.
- Na Faixa de Opções , clique na guia Dados .
- Na seção Esboço da guia Dados , clique em Subtotal .
- O Excel selecionará todo o conjunto de dados na planilha e você verá a caixa de diálogo Subtotal .
7. Na caixa de diálogo Subtotal , escolha a coluna desejada clicando no menu suspenso em A cada alteração em .
8. Agora, você precisa definir o campo de função Usar , selecionando uma opção no campo suspenso. Você pode escolher qualquer coisa entre SUM , AVERAGE , Max , Min , etc.
9. O Excel colocará o subtotal calculado na célula que você escolher em Adicionar subtotal a .
10. Clique em Ok e o Excel adicionará automaticamente os subtotais junto com os contornos .
Os grupos automatizados delineados mostrarão subtotais em cada grupo, de acordo com sua seleção de título de coluna. Por exemplo, neste tutorial, as categorias de itens alimentares como Barras , Biscoitos , Biscoitos e Lanches são grupos separados. O preço total de cada grupo está disponível na coluna Preço total (USD) .
Como agrupar no Excel para visualizar dados aninhados
O Excel permite que você crie um esboço que consiste em até oito níveis. É uma ferramenta freqüentemente usada quando você precisa resumir uma longa lista de dados como um grupo de cabeçalhos individuais. O contorno mais à esquerda ou o contorno externo começa com o Número 1 (Um) , e conforme você vai para a direita, pode subir até o Número 8 (oito) .
Você pode visualizar rapidamente os dados aninhados como um grupo ou dados resumidos ou até mesmo revelar os detalhes de cada grupo usando as linhas de grupo do Excel ou a ferramenta de contorno. O Excel permite que você crie o contorno de colunas, o contorno de linhas e o contorno de linhas e colunas.
Grupo automático do Excel para criar um esboço
Quando você precisa organizar listas de dados aninhadas menores, você pode usar a ferramenta Contorno para criar grupos automáticos. Você pode tentar estas etapas:
- Clique em qualquer célula dos dados da linha que você precisa agrupar.
- Agora, vá para a guia Dados na Faixa de Opções do Excel.
- Na seção Esboço , você encontrará o menu suspenso Grupo .
- Clique em Grupo e selecione Contorno automático .
Você notará que a função de grupo organizou as linhas perfeitamente. Você também verá as barras de contorno no lado esquerdo, representando várias estruturas de nível de dados.
Você pode explorar várias opções de visualização interagindo com o botão Níveis , como Número 1 (Um), Número 2 (Dois), Número 3 (Três), etc.
1. Se você selecionar o nível mais baixo, Número 1 (Um) , verá os dados mínimos na planilha. No exemplo atual, é o custo total geral de todos os itens alimentares.
2. Ao clicar no próximo nível, você verá apenas as linhas de subtotal junto com a linha de Total geral . Você não verá outras listas de dados detalhadas na planilha do Excel.
3. Agora você pode selecionar o nível mais alto ou o Número 3 (Três) nos dados de exemplo atuais para expandir todas as linhas e colunas junto com a visualização delineada.
Você também pode usar as etapas acima para agrupar colunas no Excel. Formate os conjuntos de dados de acordo para que o comando Auto Outline do Excel possa reconhecer os subtotais.
Comando de grupo do Excel para agrupar linhas manualmente
Quando a planilha contém conjuntos de dados complexos, como mais de um nível de informação, você precisa usar o comando Grupo para agrupamento manual.
- Selecione um subconjunto de dados que deseja incluir em um grupo e clique no comando Grupo na guia Dados da Faixa de Opções do Excel.
- Para criar outro grupo com outro subconjunto de dados, selecione as linhas e clique no comando Grupo ou use esta tecla de atalho Shift + Alt + Seta para a direita .
3. Se você deseja criar um grupo aninhado, escolha todas as linhas de detalhes dentro da linha de resumo relacionada e use o atalho Shift + Alt + Seta para a direita .
Você pode ocultar linhas detalhadas usando a opção de recolher linhas do Excel: o sinal de menos (-) nos contornos para simplificar dados aninhados complexos. Ao recolher uma barra de contorno, o sinal de menos (-) muda para o sinal de mais (+) para mostrar as linhas posteriormente.
Além disso você pode usar os botões de mostrar detalhes e Ocultar detalhes sobre a seção de destaques do guia dados na fita de Excel.
Existem estilos embutidos para grupos e contornos na planilha do Excel. Para aplicar Estilos , clique no ícone do iniciador da caixa de diálogo (uma pequena seta suspensa) na seção Contornos da guia Dados .
Você verá uma caixa de diálogo Configurações . Clique na caixa de seleção Estilos automáticos e, em seguida, clique no botão Aplicar estilos .
Aplique um toque profissional aos seus relatórios do Excel
Com este tutorial do Excel sobre subtotais e contornos do Excel (grupos), você será capaz de organizar dados complexos em uma tabela fácil de entender. Usando planilhas do Excel, você pode visualizar dados aninhados de várias perspectivas para extrair inteligência de dados valiosa.
