Você aplica um filtro aos seus dados no Excel, mas a função SOMA continua calculando as linhas ocultas. Isso acontece porque a função SOMA padrão inclui todas as células em seu intervalo, independentemente da visibilidade. O Excel fornece uma função dedicada, SUBTOTAL, projetada para realizar cálculos em dados filtrados. Este artigo explica como usar SUBTOTAL para somar apenas células visíveis e esclarece quando usá-la em vez de SOMA.
Principais Conclusões: Somar Dados Filtrados no Excel
- Função SUBTOTAL com 109: Calcula uma soma que ignora automaticamente linhas ocultas por um filtro.
- Botão AutoSoma após filtrar: Insere a função SUBTOTAL automaticamente ao somar uma coluna filtrada.
- Função SOMA: Adiciona todos os números em um intervalo, incluindo aqueles em linhas ocultas pela filtragem.
Por que SOMA Não Funciona em Dados Filtrados
A função SOMA é uma ferramenta aritmética básica. Ela adiciona todos os valores numéricos que você especifica em seu intervalo. Ela não avalia o estado da planilha. Quando você oculta linhas usando um filtro, o Excel apenas altera a propriedade de exibição delas. Os dados nessas linhas continuam fazendo parte da planilha e, portanto, de qualquer intervalo referenciado por SOMA. É por isso que uma fórmula SOMA continua exibindo o total de todos os dados, dando resultados incorretos para sua visualização filtrada. Para realizar cálculos que respondam à filtragem, você precisa de uma função que possa ignorar linhas ocultas.
Passos para Somar Células Visíveis com SUBTOTAL
A função SUBTOTAL é a ferramenta correta para esta tarefa. Ela usa um número de função para especificar o tipo de cálculo e, por padrão, exclui valores em linhas ocultas por um filtro.
Método 1: Usando a Função SUBTOTAL Diretamente
- Selecione a célula para o total
Clique na célula onde deseja que a soma das linhas visíveis apareça, geralmente abaixo dos dados filtrados. - Digite a fórmula SUBTOTAL
Digite =SUBTOTAL( e em seguida insira o número da função. Para uma soma que ignore linhas filtradas, use 109. A fórmula deve ficar assim: =SUBTOTAL(109, - Selecione o intervalo
Com o mouse, selecione o intervalo de células que deseja somar, como B2:B100. A fórmula agora será =SUBTOTAL(109,B2:B100). - Complete a fórmula
Digite um parêntese de fechamento ) e pressione Enter. A célula exibirá uma soma que é atualizada automaticamente quando você altera o filtro.
Método 2: Usando o Botão AutoSoma em uma Coluna Filtrada
- Aplique o filtro
Selecione o intervalo de dados e clique em Dados > Filtro para ativar a filtragem. Use as setas suspensas para filtrar os dados conforme necessário. - Selecione a célula abaixo dos dados visíveis
Clique na célula vazia diretamente abaixo da coluna de números que deseja somar. - Clique no botão AutoSoma
Vá para a guia Página Inicial na faixa de opções. No grupo Edição, clique no botão AutoSoma (o ícone sigma grego Σ). - Confirme a fórmula
O Excel inserirá automaticamente uma função SUBTOTAL, não uma função SOMA, e destacará o intervalo de células visíveis. Pressione Enter para confirmar.
Erros Comuns e Limitações
Usar o Número de Função SUBTOTAL Errado
SUBTOTAL tem dois conjuntos de números de função. Os números 1-11 incluem valores em linhas ocultas. Os números 101-111 ignoram valores em linhas ocultas. Para somar listas filtradas, sempre use 109 ou 9. Usar 9 funcionará, mas é do conjunto mais antigo que inclui linhas ocultas manualmente. Para consistência com filtros, 109 é a melhor escolha.
SUBTOTAL Ignora Outros SUBTOTAL
Uma característica importante da função SUBTOTAL é evitar a dupla contagem. Se o intervalo selecionado contiver outras células com fórmulas SUBTOTAL, essas células são automaticamente excluídas do novo cálculo SUBTOTAL. Isso é intencional e impede que totais incluam outros totais.
Linhas Ocultas Manualmente vs. Linhas Filtradas
A função SUBTOTAL com argumento 109 ignora linhas ocultas por um filtro. No entanto, se você clicar com o botão direito em uma linha e selecionar Ocultar, essa linha fica oculta manualmente. SUBTOTAL com 109 ainda incluirá valores de linhas ocultas manualmente. Apenas os números de função mais antigos (1-11) ignoram linhas ocultas manualmente, mas eles incluem linhas filtradas.
SUBTOTAL vs SOMA: Principais Diferenças
| Item | SUBTOTAL | SOMA |
|---|---|---|
| Uso Principal | Cálculos em listas filtradas ou com subtotais | Adição básica de todos os números em um intervalo |
| Resposta ao Filtro | Ignora automaticamente linhas ocultas por um filtro | Inclui todas as linhas, independentemente do filtro |
| Números de Função | Usa números (ex.: 109 para soma) para especificar o tipo de cálculo | Sem números de função; apenas uma operação |
| Comportamento de Aninhamento | Ignora outras células SUBTOTAL dentro de seu intervalo | Soma todas as células, incluindo outras fórmulas SOMA |
| Ocultação Manual de Linhas | Argumento 109 inclui linhas ocultas manualmente | Inclui linhas ocultas manualmente |
Agora você pode usar a função SUBTOTAL para criar totais precisos para seus relatórios filtrados. Lembre-se de usar o número de função 109 para uma soma que respeite seus filtros ativos. Para sua próxima tarefa, tente usar SUBTOTAL com outros números de função, como 101 para MÉDIA ou 103 para CONTAR.VALORES em dados filtrados. Uma dica avançada útil é nomear seu intervalo de dados como Tabela usando Ctrl+T; isso permite que suas fórmulas SUBTOTAL se expandam automaticamente quando você adicionar novas linhas de dados.