Função SUBTOTAL do Excel (Sumário)
- SUBTOTAL no Excel
- Fórmula SUBTOTAL no Excel
- Como usar a função SUBTOTAL no Excel?
SUBTOTAL no Excel
- Uma função subtotal é uma função embutida no Excel que é categorizada em função matemática / trigonométrica
- O subtotal não apenas totalizará os números em um intervalo definido de células, mas ajudará executando diferentes operações aritméticas
- SUBTOTAL é versátil. Você pode usá-lo para calcular a média, resumir, contar várias células e outros cálculos
Definição
Retorna um subtotal em um banco de dados ou lista, ou seja, para descobrir o subtotal de um determinado intervalo de células. Ele executa um cálculo especificado para um determinado conjunto de valores.
Fórmula SUBTOTAL no Excel
A fórmula para a função SUBTOTAL é a seguinte:
SUBTOTAL (número_da_função, ref1, (ref2), …) Onde
Function_num ou código de operação: indica qual função deve ser usada para os cálculos dos subtotais dentro de uma determinada lista ou é um tipo de operação matemática que você deseja executar
Um argumento function_num é aplicado com base nos dois critérios ou métodos abaixo mencionados
- Incluir valores ocultos Quando o argumento function_num estiver entre 1 e 11, a função SUBTOTAL incluirá os valores ocultos.
- Ignorar valores ocultos Quando o argumento function_num estiver entre 101-111, a função SUBTOTAL excluirá ou ignorará os valores ocultos. Ele encontrará valores que apenas linhas invisíveis ignoram valores ocultos no cálculo.
| Fun_num Inclui valores ocultos | Fun_num Exclui valores ocultos | Função | Descrição | |||
| 1 | 101 | MÉDIA | Calcula a média de um intervalo especificado. | |||
| 2 | 102 | CONTAGEM | Contará o número de células que contêm valores numéricos. | |||
| 3 | 103 | COUNTA | Conta as células não vazias no intervalo especificado. | |||
| 4 | 104 | MAX | Ele descobrirá o maior valor no intervalo especificado. | |||
| 5 | 105 | MIN | Ele descobrirá o menor valor no intervalo especificado. | |||
| 6 | 106 | PRODUTOS | Ele calcula o produto das células no intervalo especificado. | |||
| 7 | 107 | DESVPAD | Ele estima o desvio padrão no intervalo especificado. | |||
| 8 | 108 | DESVPADP | Ele calcula o desvio padrão com base em toda a população. | |||
| 9 | 109 | SOMA | Ele calcula a soma do intervalo especificado. | |||
| 10 | 110 | VAR | Ele estima a variação no intervalo especificado. | |||
| 11 | 111 | VARP | Ele estima a variação com base em toda a população. |
ref1, (ref2): uma ou mais referências a células que contêm os valores que o cálculo deve ser realizado para o subtotal.
Como usar a função SUBTOTAL no Excel?
Vamos ver como a função SUBTOTAL funciona no Excel.
Você pode fazer o download deste modelo do Excel da função SUBTOTAL aqui - Modelo do Excel da função SUBTOTALExemplo # 1 - Fórmula SUBTOTAL para valores ocultos usando o filtro automático do Excel ou linhas ocultas por um filtro
No exemplo abaixo mencionado, a tabela contém Dados de vendas trimestrais, em que trimestres individuais na coluna E e dados de vendas na coluna F.
A função soma é aplicada na célula “F16”, ou seja, SUM (F4: F15).
Retorna ou resulta no valor 1184 .
A função SUM retorna o mesmo valor. ou seja, 1184, em vez de 194 para dados do quarto trimestre, se as linhas estiverem ocultas por um filtro (captura de tela abaixo mencionada).
Se eu quiser apenas o valor Soma Correto dos dados do quarto trimestre, preciso usar a função SUBTOTAL.
Antes de aplicar a função SUBTOTAL, Na barra de ferramentas de dados, aplique um filtro para a linha do cabeçalho.
Agora, vamos calcular especificamente os dados de vendas Q1 ou Q2 ou Q3 ou Q4 individualmente para dados filtrados.
Use 109 como um argumento function_num na função SUBTOTAL para resumir os dados. ie = SUBTOTAL (109, C17: C28)
A função SUBTOTAL ignora as linhas ocultas por um filtro e calcula o resultado correto.
Aqui, o valor do subtotal muda dinamicamente de acordo com o filtro; ele encontrará uma soma de valores apenas nas linhas visíveis; linhas ocultas são negligenciadas pela função SUBTOTAL. (Abaixo mencionado, por exemplo)
Q4
Q3
Q2
Q1
Exemplo # 2 - Função SUBTOTAL para linhas ocultas manualmente
Na planilha do Excel, as linhas 15, 16, 19 e 20 foram ocultadas usando a opção de formatação de linha, destacando essas linhas, clicando com o botão direito do mouse e selecionando Ocultar.
Uma função subtotal é usada na célula C23 da planilha, para calcular a soma das células visíveis.
Temos que usar 109 como argumento function_num na função SUBTOTAL para resumir os dados.
ie = SUBTOTAL (109, C14: C21)
Aqui, a função SUBTOTAL ignora manualmente as linhas ocultas e calcula o resultado correto.
Ele encontrará uma soma de valores apenas nas linhas visíveis; linhas ocultas são negligenciadas pela função SUBTOTAL
Exemplo # 3 - Função SUBTOTAL para valor normal
Como todas as células no intervalo de números de vendas trimestrais são visíveis, nenhuma delas é filtrada ou as linhas ocultas, isso inclui todos os valores.
No exemplo abaixo mencionado, várias funções de subtotal são aplicadas.
Na célula, a função SUBTOTAL C14, C15 e C16 é aplicada.
Para calcular o valor SUM, a função número 9 ou 109 é usada em uma função subtotal em que calcula a soma do intervalo especificado, ou seja, = SUBTOTAL (9, C4: C11)
Ele nos fornecerá a soma de todos os valores em C4: C11, desde que nenhum seja filtrado e as linhas ocultas no intervalo especificado.
Para calcular o valor MIN, a função número 5 ou 105 é usada em uma função subtotal em que encontrará o menor valor no intervalo especificado. Em uma célula, C15, uma função SUBTOTAL é aplicada, ou seja, = SUBTOTAL (5, C4: C11)
Ele descobrirá o menor valor no intervalo especificado, ou seja, C4: C11
Para calcular o valor MÁX, a função número 4 ou 104 é usada em uma função subtotal em que encontrará o maior valor no intervalo especificado. Em uma célula, C16, uma função SUBTOTAL é aplicada, ou seja, = SUBTOTAL (4, C4: C11)
Ele descobrirá o maior valor no intervalo especificado, ou seja, C4: C11.
Lembretes sobre a função SUBTOTAL
- Uma função SUBTOTAL pode ser aplicada para encontrar uma soma dos valores filtrados.
- Uma função SUBTOTAL é usada para ignorar valores em linhas ocultas.
- A função _ num valor do argumento não deve ser maior que 11 ou menor que 1, da mesma forma não deve ser maior que 111 ou menor que 101; caso contrário, ele dará # VALOR! erro.
- Uma função SUBTOTAL é aplicável apenas ao valor numérico.
- Se houver subtotais presentes no intervalo SUBTOTAL, eles serão negligenciados, isto é, se alguma fórmula SUBTOTAL já existente estiver presente no intervalo de entrada de outra fórmula SUBTOTAL, esses valores serão negligenciados pela função do subtotal durante o cálculo, para evitar a contagem dupla. evita o erro de contagem dupla.
- Uma função SUBTOTAL pode ser usada para resumir dinamicamente os dados.
- As células em branco e as células que contêm valores não numéricos são ignoradas pela função do subtotal durante o cálculo.
Artigos recomendados
Este foi um guia para SUBTOTAL. Aqui discutimos a Fórmula SUBTOTAL e como usar a Função SUBTOTAL junto com o exemplo do Excel e os modelos do Excel para download. Você também pode examinar essas funções úteis no excel -
- Função SEARCH no Excel
- Função FIND no Excel
- NÃO no Excel
- TRANSPOSE no Excel