Como usar a função SUBTOTAL do Excel -

Resumo

A função SUBTOTAL do Excel retorna um resultado agregado para os valores fornecidos. SUBTOTAL pode retornar SUM, AVERAGE, COUNT, MAX e outros (consulte a tabela a seguir), e a função SUBTOTAL pode incluir ou excluir valores em linhas ocultas.

Objetivo

Obtenha um subtotal em uma lista ou banco de dados

Valor de retorno

Um número que representa um tipo específico de subtotal

Sintaxe

= SUBTOTAL (núm_função, ref1, (ref2), …)

Argumentos

  • function_num - um número que especifica qual função usar no cálculo de subtotais em uma lista. Veja a tabela abaixo para uma lista completa.
  • ref1 - um intervalo nomeado ou referência ao subtotal.
  • ref2 - (opcional) Um intervalo nomeado ou referência ao subtotal.

Versão

Excel 2003

Notas de uso

Use a função SUBTOTAL para obter um subtotal em uma lista ou banco de dados. Apesar do nome, SUBTOTAL tem a capacidade de executar uma variedade de funções matemáticas, incluindo AVERAGE, COUNT, MAX e muitas outras (veja a tabela abaixo para a lista completa). Por padrão, SUBTOTAL exclui valores em linhas ocultas por um filtro, o que torna SUBTOTAL muito útil em tabelas do Excel.

A função SUBTOTAL ignora automaticamente outras fórmulas SUBTOTAL que existem nas referências para evitar a contagem dupla.

Exemplos

Abaixo estão alguns exemplos de SUBTOTAL configurados para SUM, COUNT e AVERAGE os valores em um intervalo. Observe que a única diferença é o valor usado para o argumento function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Cálculos disponíveis

O comportamento SUBTOTAL é controlado pelo argumento function_num , que é fornecido como um valor numérico. São 11 funções disponíveis, cada uma com duas opções, conforme tabela a seguir. Observe que os valores estão "emparelhados" (por exemplo, 1-101, 2-102, 3-103 e assim por diante). Isso está relacionado à forma como SUBTOTAL lida com linhas ocultas manualmente. Quando function_num está entre 1-11, SUBTOTAL inclui células que foram ocultadas manualmente. Quando function_num está entre 101-111, SUBTOTAL exclui valores em linhas que foram ocultadas manualmente.

Função Incluir oculto Ignorar oculto
MÉDIA 1 101
CONTAGEM 2 102
CONT.valor 3 103
MAX 4 104
MIN 5 105
PRODUTOS 6 106
STDEV 7 107
STDEVP 8 108
SOMA 9 109
VAR 10 110
VARP 11 111

Nota: SUBTOTAL sempre ignora os valores nas células que estão ocultas com um filtro. Os valores nas linhas que foram "filtradas" nunca são incluídos, independentemente de function_num .

SUBTOTAL em tabelas Excel

A função SUBTOTAL é usada quando você exibe uma linha Total em uma Tabela do Excel. O Excel insere a função SUBTOTAL automaticamente e você pode usar um menu suspenso para mudar o comportamento e mostrar máx., Mín., Média, etc. A razão pela qual o Excel usa SUBTOTAL para cálculos na linha Total de uma Tabela do Excel é porque SUBTOTAL exclui automaticamente as linhas oculto pelos controles de filtro na parte superior da tabela. Ou seja, conforme você filtra linhas em uma tabela com uma linha Total, você verá os cálculos serem atualizados automaticamente para respeitar o filtro.

SUBTOTAL com contornos

O Excel possui um recurso de subtotal que insere automaticamente fórmulas SUBTOTAL nos dados classificados. Você pode encontrar esse recurso em Dados> Esboço> Subtotal. As fórmulas SUBTOTAL inseridas desta forma usam os números de função padrão 1-11. Isso permite que os resultados do subtotal permaneçam visíveis, mesmo quando as linhas são ocultadas e exibidas quando o contorno é recolhido e expandido.

Observação: embora o recurso Esboço seja uma maneira "fácil" de inserir subtotais em um conjunto de dados, uma Tabela Dinâmica é uma maneira melhor e mais flexível de analisar dados. Além disso, uma Tabela Dinâmica separará os dados da apresentação dos dados, o que é uma prática recomendada.

Notas

  • Quando function_num está entre 1-11, SUBTOTAL inclui valores que estão ocultos
  • Quando function_num está entre 101-111, SUBTOTAL exclui valores que estão ocultos
  • Em listas filtradas, SUBTOTAL sempre ignora valores em linhas ocultas, independentemente de function_num .
  • SUBTOTAL ignora outras fórmulas SUBTOTAL que existem nas referências para evitar a contagem dupla.
  • SUBTOTAL é projetado para trabalhar com valores de dados verticais organizados verticalmente. Em intervalos horizontais, os valores em colunas ocultas são sempre incluídos.

Vídeos relacionados

Como adicionar uma linha de totais a uma tabela Neste vídeo, veremos como adicionar e configurar uma linha de totais a uma tabela do Excel. A linha total pode mostrar automaticamente cálculos de resumo abaixo de uma tabela. Como contar itens em uma lista filtrada Neste vídeo, mostramos como adicionar uma mensagem no topo de uma lista filtrada que exibe o total de itens visíveis na lista. Fórmulas para consultar uma tabela Como as tabelas oferecem suporte a referências estruturadas, você pode aprender muito sobre uma tabela com fórmulas simples. Neste vídeo, veremos algumas fórmulas que você pode usar para consultar uma tabela.

Artigos interessantes...