Visual Dicas

terça-feira, 7 de outubro de 2014

Visual Dicas

Como inserir SUBTOTAIS automáticos em uma planilha do Excel

Posted: 06 Oct 2014 01:59 PM PDT

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

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].

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].

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.

Passo 2: Acrescentar os SUBTOTAIS

Selecione as linhas da planilha onde você acrescentará os subtotais.

Clique na guia [Dados].

Clique no botão [Subtotal].

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].

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.

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.

Clicando no botão [1] (um) você terá o Total Geral.

Clicando em [2] (dois) você terá todos os subtotais demonstrados

Clicando em [3] (três) você terá todos os dados da sua tabela abertos, ou seja, tabela completa.

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

Passo 2: Acrescentar os SUBTOTAIS

Clique na guia [Dados] e no botão [Subtotal].

Na janela Subtotais defina com atenção:

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.

Depois de definidos os parâmetros, clique no botão [OK].

Pronto, agora a nossa planilha está completa com os dois objetivos: Soma de valores e contagem de itens por fornecedor.

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

Passo 2: Abrir janela SUBTOTAIS

Clique na guia [Dados] e no botão [Subtotal].

Clique no botão [Remover todos].

Pronto, sua planilha estará novamente na forma original, mas ainda organizada alfabeticamente por fornecedores.

Para voltar à planilha original, selecione as linhas de dados da planilha e em Dados, clique no botão [Classificar].

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.