Como somar valores no Excel excluindo os subtotais?
Ao trabalhar com conjuntos de dados no Excel, é habitual organizar e analisar os seus números calculando subtotais para diferentes grupos ou categorias. No entanto, depois de adicionar esses subtotais, poderá precisar de calcular um total geral que os exclua, evitando assim contagens duplicadas na soma final. O desafio surge porque, ao somar diretamente todos os valores, os subtotais são incluídos no total global, originando uma soma final superior à pretendida. Para contornar esta situação, é essencial encontrar uma forma de somar apenas os dados originais, ignorando as linhas de subtotal. Neste artigo, apresentamos vários métodos práticos para resolver este problema, permitindo-lhe obter resultados precisos de forma eficiente em diversos cenários.
Somar valores sem subtotais com a função SOMA no Excel
Uma abordagem amplamente adotada para calcular subtotais de grupo consiste em inserir a função SOMA dentro ou abaixo de cada grupo relevante. Contudo, quando pretende obter o total geral de toda a lista ignorando essas linhas de subtotal de grupo, é frequentemente necessário um passo adicional. Eis um método que pode utilizar para alcançar este objetivo:
1. Utilize a função SOMA em cada grupo para obter os respetivos subtotais, conforme ilustrado nesta captura de ecrã:
2. Em seguida, para obter o total geral sem incluir essas linhas de subtotal, introduza a seguinte fórmula numa célula vazia onde pretender que o resultado apareça:
=SOMA(B2:B21)/2
Prima Enter para obter o total correto. Esta fórmula funciona porque parte do pressuposto de que inseriu subtotais com a função SOMA imediatamente após os grupos relevantes, duplicando efetivamente a soma dos dados brutos. Ao dividir por 2, elimina-se o total duplicado. Note que este método é mais adequado quando a estrutura do conjunto de dados é tal que a soma dos valores originais e dos subtotais em conjunto corresponde exatamente ao dobro da soma dos dados originais.
Se a estrutura dos seus dados for mais complexa ou se o número de linhas de subtotal variar, os métodos alternativos descritos abaixo oferecem uma solução mais precisa e flexível.
Somar valores sem subtotais com a função SUBTOTAL no Excel
A função SUBTOTAL no Excel oferece uma forma integrada de realizar cálculos apenas nas linhas filtradas ou visíveis, tornando-a especialmente útil ao trabalhar com listas que contêm subtotais. Isso porque as fórmulas SUBTOTAL podem ser configuradas para ignorar outros resultados SUBTOTAL dentro do intervalo, evitando assim contagens duplicadas de valores.
Para utilizar a função SUBTOTAL neste contexto, siga estes passos:
1. Introduza a função SUBTOTAL para cada grupo da seguinte forma:
=SUBTOTAL(9;B2:B10)
Isto aplica a operação SOMA (número da função 9) ao intervalo limitado e é frequentemente utilizado como parte da funcionalidade integrada Dados > Subtotal do Excel.
2. Para obter o total geral, excluindo os subtotais internos, introduza a seguinte fórmula na célula pretendida:
=SUBTOTAL(9;B2:B21)
Prima Enter para somar automaticamente os valores dos dados, ignorando quaisquer funções SUBTOTAL aninhadas no intervalo. Esta abordagem é ideal para listas que utilizam a função SUBTOTAL para resumos por grupo, eliminando o risco de somar subtotais múltiplas vezes.
Tenha em atenção que a função SUBTOTAL oferece melhores resultados quando as células de subtotal são geradas com SUBTOTAL, e não com SOMA. Além disso, ao filtrar ou ocultar linhas, o SUBTOTAL pode ser configurado para somar apenas os dados visíveis — uma vantagem essencial em relatórios dinâmicos.
Código VBA – Somar valores excluindo linhas de subtotal
Se a estrutura dos seus dados for complexa ou se pretender um método automatizado para somar apenas as linhas de dados brutos — excluindo subtotais —, pode recorrer a uma macro VBA simples. Esta abordagem permite-lhe identificar e somar programaticamente apenas as linhas que cumprem os seus critérios, como formatação específica, presença de fórmulas ou outros atributos distintivos.
Por exemplo, se as suas linhas de subtotal forem calculadas com fórmulas (como SOMA ou SUBTOTAL) e as linhas de dados brutos contiverem apenas valores estáticos, pode configurar o código VBA para somar exclusivamente as células de uma coluna específica que não contenham fórmulas. Eis como fazê-lo:
1. Clique em Ferramentas de Programador > Visual Basic para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Na nova janela, clique em Inserir > Módulo e cole o seguinte código no módulo:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Clique no botão 
Executar para executar o código. Irá aparecer uma mensagem a solicitar que selecione o intervalo que contém os seus dados. A macro somará, então, apenas as células da sua seleção que não contêm fórmulas, ignorando eficazmente as linhas típicas de subtotal.
Filtragem – Utilizar filtro e SUBTOTAL para somar apenas as linhas visíveis (não subtotais)
Se o seu conjunto de dados permitir filtrar as linhas de subtotal — por exemplo, se estas tiverem uma etiqueta, palavra-chave ou padrão que as identifique de forma consistente —, pode combinar a funcionalidade de Filtro integrada do Excel com a função SUBTOTAL para somar apenas as linhas de dados visíveis (não filtradas). Esta abordagem revela-se particularmente útil quando os subtotais incluem um indicador ou etiqueta uniforme.
1. Clique em qualquer ponto do seu conjunto de dados, aceda ao separador Dados e clique em Filtro para ativar filtros nas suas colunas.
2. Clique na seta suspensa da sua coluna auxiliar, desmarque «Subtotal» para ocultar as linhas de subtotal e exibir apenas os dados originais.
3. Numa célula separada, introduza a seguinte fórmula para somar apenas as linhas visíveis (filtradas).
=SUBTOTAL(9,B2:B21)A função SUBTOTAL (com o número de função 9 para SOMA) ignora quaisquer linhas ocultas, devolvendo o total apenas das linhas atualmente visíveis — ou seja, as linhas não subtotal que permaneceram após a aplicação do filtro. Esta solução é rápida e dinâmica: ao reaplicar filtros ou expandir o seu conjunto de dados, a fórmula atualiza-se automaticamente.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos