PROMOÇÃO - Magazine Luiza - NÃO PERCA!

Publicidade:

Encontre no blog...

06/10/2014

Como inserir SUBTOTAIS automáticos em uma planilha do Excel

Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas

Subtotais nos dados do Excel 2010 / 2013

Nada melhor do que o Excel para exibir relatórios gerenciais para análise e tomada de decisão. Existem várias formas de se preparar uma planilha para este fim.
A ferramenta do Excel que iremos apresentar neste artigo ajudará você a criar relatórios gerenciais. Utilizamos a opção “Subtotal” que efetua totalizações automáticas nos dados da planilha oferecendo ao leitor uma melhor visualização das informações.
Obs.: Utilizamos para este tutorial o Excel 2010, mas você poderá usar o Excel 2007 ou o Excel 2013, pois seus comandos são similares.
Veja a seguir como isso pode ser feito…

Planilha de exemplo

Primeiro, vamos criar uma planilha de exemplo no Excel, como mostra a figura abaixo:
Obs.: Se você quiser, pode fazer o download da planilha no link: Download Planilha
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas

Inserindo Subtotais

A) Objetivo: Queremos saber a somatória do VALOR TOTAL e do VALOR SOBRA para cada FORNECEDOR da tabela.
Passo 1:  Classificar os dados em ordem crescente por FORNECEDOR.
Selecione as linhas que contêm os dados da tabela. Clique na guia [Dados].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Em Dados, clique no botão [Classificar].
Na janela Classificar, mantenha marcado a opção Meus dados contêm cabeçalho, pois selecionamos a linha do cabeçalho também.
No campo [Classificar por] selecione FORNECEDOR. Mantenha os demais campos como aparece na imagem. Clique no botão [OK].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas



Observe que a planilha ficará classificada por grupos de fornecedores e na ordem alfabética crescente, pois definimos o campo [Ordem] de A a Z.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Passo 2: Acrescentar os SUBTOTAIS
Selecione as linhas da planilha onde você acrescentará os subtotais.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clique na guia [Dados].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clique no botão [Subtotal].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Na janela Subtotais defina com atenção:
a) A cada alteração em: Selecione FORNECEDOR – significa que a ferramenta subtotais executará a função por fornecedor, que no nosso caso será por grupo de fornecedores, já que ordenamos desta forma.
b) Usar função: Selecione SOMA – significa que a ferramenta subtotais fará a soma dos itens da lista de dados (fornecedores).
c) Adicionar subtotal a: Marque as colunas VALOR TOTAL e VALOR SOBRA – significa que serão calculados os subtotais (soma) por fornecedor nas colunas marcadas.
d) Substituir subtotais atuais: Marque esta opção se desejar refazer os subtotais em uma nova organização, substituindo os anteriores.
e) Quebra de página entre grupos: Marque esta opção para facilitar a impressão em grandes tabelas, onde o Excel irá “quebrar a página” imprimindo a sequência em outra folha.(para evitar desperdício de papel, recomendo manter desmarcada esta opção).
f) Resumir abaixo dos dados: Marque esta opção para que o Excel coloque os valores dos subtotais na linha abaixo dos dados e o Total Geral no final da tabela. Se desmarcar esta opção os subtotais ficaram acima dos dados e o Total Geral no início da tabela.
Depois de definidos os parâmetros, clique no botão [OK].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Obs.: Principais funções que podem ser utilizadas na ferramenta “subtotais”
  • Soma: Soma dos valores de uma lista (função padrão para dados numéricos).
  • Contagem: O número de itens de uma lista (função padrão para dados não numéricos).
  • Média: Média dos valores de uma lista.
  • Max: Maior valor de uma lista.
  • Min: Menor valor de uma lista.
Pronto, veja a seguir a imagem da planilha do nosso exemplo. Observe que obtivemos o nosso objetivo, ou seja, subtotais (soma) por fornecedores do Valor Total e do Valor de Sobra.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas

Utilizando os cursores (visualização)

Observe que no canto esquerdo superior no início da sua tabela que existe alguns números (em formato de botão) que vão de 1 a 3. Isto significa que você tem três níveis de subtotais.
Obs.: Os cursores não apareceram na impressão da planilha
Para visualizar cada um deles, basta clicar nos números indicados.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clicando no botão [1] (um) você terá o Total Geral.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clicando em [2] (dois) você terá todos os subtotais demonstrados
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clicando em [3] (três) você terá todos os dados da sua tabela abertos, ou seja, tabela completa.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas

Acrescentando subtotais

B) Objetivo: Queremos saber, além da somatória dos valores, o número de itens por fornecedor.
Passo 1 – Selecionar toda planilha de dados
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Passo 2: Acrescentar os SUBTOTAIS
Clique na guia [Dados] e no botão [Subtotal].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Na janela Subtotais defina com atenção:
a) A cada alteração em: Selecione FORNECEDOR – significa que a ferramenta subtotais executará a função por fornecedor, que no nosso caso será por grupo de fornecedores, já que ordenamos alfabeticamente nesta forma.
b) Usar função: Selecione CONTAGEM – significa que a ferramenta subtotais fará a contagem dos itens da lista de dados por grupo de fornecedores.
c) Adicionar subtotal a: Marque a coluna UN. – significa que serão calculados os subtotais (contagem) por fornecedor na colunas marcada.
d) Substituir subtotais atuais: Desmarque esta opção para acrescentar o novo subtotal sem refazer ou substituir os valores anteriores.
e) Quebra de página entre grupos: Marque esta opção para facilitar a impressão em grandes tabelas, onde o Excel irá “quebrar a página” imprimindo a sequência em outra folha.(para evitar desperdício de papel, recomendo manter desmarcada esta opção).
Depois de definidos os parâmetros, clique no botão [OK].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Pronto, agora a nossa planilha está completa com os dois objetivos: Soma de valores e contagem de itens por fornecedor.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Depois de montada a planilha, você poderá realçar as linhas com os subtotais preenchendo com cores diferenciadas.

Remover os subtotais

Para remover os subtotais e deixar a planilha de forma inicial, siga os passos abaixo:
Passo 1 – Selecionar toda planilha de dados
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Passo 2: Abrir janela SUBTOTAIS
Clique na guia [Dados] e no botão [Subtotal].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Clique no botão [Remover todos].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Pronto, sua planilha estará novamente na forma original, mas ainda organizada alfabeticamente por fornecedores.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Para voltar à planilha original, selecione as linhas de dados da planilha e em Dados, clique no botão [Classificar].
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Na janela Classificar, selecione ITEM em “Coluna e Do Menor para o Maior” em Ordem. Clique no botão [OK]. Desta forma você irá ordenar a planilha por item, do menor para o maior número.
Obs.: Mantenha marcada a opção [Meus dados contêm cabeçalhos] se você incluiu a linha de cabeçalhos na seleção, como fizemos no nosso exemplo.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Agora a planilha estará no formato original.
Como inserir SUBTOTAIS automáticos em uma planilha do Excel - Visual Dicas
Bons trabalhos…
byalf

1 comentários:

Cintia Ribeiro disse...

Não estou conseguindo colocar subtotais, fiz algo na tabela que quando vou em Dados, não consigo clicar no Subtotal e fazer as definições que quero. Quando clico no titulo ou outras partes de cima da tabela vai, mas onde eu quero não. O que eu faço??

Postar um comentário

Gostou do Post? Então espalhe!

Quem está curtindo...

Encontre no blog...

Gostou? Então espalhe!

Twitter Delicious Facebook Digg Stumbleupon Favorites More