Subtotal no Excel – Como inserir, usar e remover

outubro 31, 2019fevereiro 2, 2021 admin 0 comentários

Você sabia que é possível calcular o Subtotal no Excel de maneira fácil e prática? O Excel conta com um recurso de Subtotal para somar, contar ou calcular automaticamente a média de diferentes grupos de células.

Se você trabalha com planilhas grandes, com certeza sabe que muitas vezes elas podem ser difíceis de ser compreendidas. Mas a boa notícia é que com a ferramenta de Subtotal do Excel você pode resumir rapidamente diferentes grupos de dados e criar um esboço para suas planilhas. Se interessou pelo assunto? Então confira os detalhes a seguir:

O que é Subtotal no Excel?

Basicamente o subtotal é a soma de um conjunto de números, que é adicionado a outro(s) conjunto(s) de números para fazer o total geral.

No Excel, o recurso de Subtotal não se limita apenas a mostrar o total de subconjuntos de valores em um conjunto de dados. Ele também possibilita agrupar e resumir seus dados usando SOMA, CONT.NÚM, MÉDIA, MÍNIMO, MÁXIMO e outras funções. Além disso, ele cria uma hierarquia de grupos, conhecida como estrutura de tópicos, que permite exibir ou ocultar os detalhes de cada subtotal ou exibir apenas um resumo dos subtotais e totais gerais.

A imagem abaixo mostra um exemplo de subtotais aplicado a um conjunto de dados.

Como inserir Subtotal no Excel?

Para inserir Subtotais em suas planilhas do Excel, siga os passos abaixo:

1. Organizando os dados de origem

O recurso Subtotal do Excel requer que os dados de origem sejam ajustados em uma ordem adequada e não devem conter linhas em branco. Antes de inserir subtotais, classifique a coluna pela qual deseja agrupar seus dados. A maneira mais fácil de fazer isso é clicar no botão Filtro na guia Dados, clique na seta do filtro e selecione para classificar A a Z ou Z a A:

Dica: Para deletar linhas em branco sem prejudicar seus dados, confira o seguinte artigo: Aprenda Como Deletar linhas vazias no Excel

2. Inserindo subtotais

Com os dados já organizados, selecione qualquer célula em seu conjunto de dados e acesse a guia Dados > Subtotal. Se você quiser adicionar subtotais apenas em determinada parte dos seus dados, selecione o intervalo desejado antes de clicar no botão Subtotal.

3. Definindo as opções do Subtotal

Ao clicar no botão de Subtotal, uma caixa de diálogo será aberta onde você deverá especificar as seguintes opções:

A cada alteração em: escolha a coluna que contém os dados que você deseja agrupar.

Usar função: escolha uma das seguintes funções:

Soma: soma os números.
Contagem: conta células não vazias (isso inserirá fórmulas subtotais com a função COUNTA ).
Média: calcule a média dos números.
Max: retorna o maior valor.
Min: retorna o menor valor.
Produto: calcule o produto das células.
Contar números: conta células que contêm números (isso irá inserir fórmulas subtotais com a função CONT.NÚM ).
DesvPad: calcula o desvio padrão de uma população com base em uma amostra de números.
DesvPadp: retorna o desvio padrão com base em uma população inteira de números.
Var: estima a variação de uma população com base em uma amostra de números.
Varp: estima a variação de uma população com base em uma população inteira de números.

Neste exemplo, agrupamos os dados pela coluna Região e usamos a função SOMA para totalizar números nas colunas Venda e Lucro.

Você também pode selecionar as seguintes opções adicionais:

Substituir subtotais atuais: Para substituir quaisquer subtotais existentes
Quebra de página entre grupos: Para inserir uma quebra automática de página após cada subtotal, selecione a caixa
Resumir abaixo dos dados: Para exibir uma linha de resumo acima da linha de detalhes, limpe a caixa Resumo abaixo dos dados. Para mostrar uma linha de resumo abaixo da linha de detalhes, marque esta caixa de seleção (normalmente selecionada por padrão).

Clique no botão OK para finalizar. Pronto! Os subtotais aparecerão abaixo de cada grupo de dados e o total geral será inserido no final da tabela.

Ao inserir os subtotais na planilha, eles serão recalculados automaticamente à medida que você edita os dados de origem.

Importante: Caso os subtotais e o total geral não esteja sendo recalculado, certifique-se de definir a sua pasta de trabalho para calcular automaticamente fórmulas. Isso pode ser feito acesso a guia Arquivo > Opções > Fórmulas > Opções de cálculo > Automático.

Inserindo vários subtotais no Excel (subtotais aninhados)

Anteriormente você aprendeu a criar subtotais no Excel porém, saiba que é possível inserir subtotais aninhados, ou seja, vários subtotais agrupados. Confira as etapas a seguir:

1. Classificando os dados por várias colunas

Quando se trata de inserir subtotais aninhados no Excel, é fundamental classificar os dados em todas as colunas que se deseja agrupar seus subtotais. Para fazer isso, basta usar a ferramenta de filtro na guia Dados > Classificar e inserir dois ou mais níveis de classificação:

Como resultado, os valores nas duas primeiras colunas serão classificados em ordem alfabética:

2. Inserindo o primeiro subtotal

Selecione qualquer célula na sua lista de dados e adicione o primeiro nível externo dos subtotais acessando a guia Dados > Subtotal. Como resultado, teremos subtotais de vendas e lucro.

3. Criando subtotais aninhados

Agora, com o subtotal externo criado, acesse novamente a guia Dados > Subtotais para inserir um nível de subtotal interno:

Em “A cada alteração em:”, selecione a segunda coluna pela qual deseja agrupar seus dados.
Na caixa “Usar função”, selecione a função de resumo desejada.
Em “Adicionar subtotal a:“, selecione as colunas para as quais deseja calcular os subtotais. Podem ser as mesmas colunas que os subtotais externos ou colunas diferentes.
Por fim, desmarque a caixa Substituir subtotais atuais. Este é o ponto principal que impede a substituição do nível externo dos subtotais.

Se necessário, repita esta etapa para adicionar mais subtotais aninhados.

Neste exemplo, o nível do subtotal interno agrupará os dados pela coluna Produto e resumirá os valores nas colunas Vendas e Lucro :

Como usar os Subtotais do Excel?

Agora que você já sabe como criar subtotais no Excel para obter resumos em diferentes grupos de dados, as dicas a seguir te ajudarão a usar os Subtotais de maneira fácil e prática:

Exibindo ou ocultando detalhes do Subtotal

Para exibir o resumo dos dados, ou seja, apenas subtotais e totais gerais, clique em um dos símbolos numerados de que aparecem no canto superior esquerdo da sua planilha:

O número 1 exibe apenas os totais gerais.
O último número exibe subtotais e valores individuais.
Os números intermediários mostram agrupamentos. Dependendo do número de subtotais que você inseriu na planilha, pode haver um, dois, três ou mais números intermediários no esquema.

Para exibir ou ocultar linhas de dados para subtotais individuais , use os símbolos (+) e (–).

Como alterar um subtotal no Excel

Se você precisa alterar um subtotal existente, faça o seguinte:

1. Selecione qualquer célula do subtotal.

2. Acesse a guia Dados > Subtotal.

3. Na caixa de diálogo Subtotal, faça as alterações desejadas relacionadas à coluna chave, função de resumo e valores a serem totalizados.

4. Verifique se a caixa Substituir subtotais atuais está selecionada.

5. Clique em OK para finalizar.

Dica: Caso você tenha adicionado vários subtotais no mesmo conjunto de dados, não será possível editá-los. Neste caso, a única maneira é remover todos os subtotais existentes e depois inseri-los novamente.

Como excluir subtotais no Excel

Se você deseja excluir subtotais, siga as etapas abaixo:

1. Selecione qualquer célula no intervalo de subtotais.