Como somar valores sem ou excluir subtotais no Excel?
Ao trabalhar com conjuntos de dados no Excel, é comum organizar e analisar seus números calculando subtotais para diferentes grupos ou categorias. No entanto, depois de adicionar esses subtotais, você pode querer calcular um total geral que exclua esses valores de subtotal, evitando qualquer contagem duplicada no somatório final. O desafio é que, ao somar diretamente todos os números, os subtotais são incluídos no total geral, resultando em uma soma total maior do que o pretendido. Para evitar isso, você precisa encontrar uma maneira de somar os dados originais enquanto exclui as linhas de subtotal. Neste artigo, apresentamos vários métodos práticos para resolver esse problema, para que você possa calcular resultados precisos de forma eficiente em diferentes cenários.
Somar valores sem subtotais com a função SOMA no Excel
Uma abordagem amplamente adotada para calcular subtotais de grupos é inserir a função SOMA dentro ou abaixo de cada grupo relevante. No entanto, quando você deseja calcular o total geral para toda a lista, ignorando essas linhas de subtotal de grupos, muitas vezes é necessário um passo extra. Aqui está um método que você pode usar para alcançar isso:
1. Use a função SOMA em cada grupo para gerar os respectivos subtotais, conforme mostrado nesta captura de tela:
2. Em seguida, para encontrar o total geral sem incluir essas linhas de subtotal, insira a seguinte fórmula em uma célula em branco onde você deseja que o resultado apareça:
=SOMA(B2:B21)/2
Pressione Enter para obter o total correto. Essa fórmula funciona porque assume que você inseriu subtotais com a função SOMA imediatamente após os grupos relevantes, duplicando efetivamente a soma dos dados brutos. Dividir por 2 remove o total duplicado. Observe que este método é mais adequado quando o conjunto de dados está estruturado de forma que a soma dos valores originais e subtotais juntos forme exatamente o dobro da soma dos dados originais.
Se a estrutura dos seus dados for mais complexa ou o número de linhas de subtotal variar, os métodos alternativos descritos abaixo podem oferecer 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 fornece uma maneira integrada de realizar cálculos apenas em linhas filtradas ou visíveis. Isso a torna especialmente útil ao trabalhar com listas que contêm subtotais, pois as fórmulas SUBTOTAL podem ser configuradas para ignorar outros resultados SUBTOTAL dentro do intervalo, evitando assim qualquer contagem duplicada de valores.
Para usar a função SUBTOTAL nesse contexto, siga estas etapas:
1. Insira a função SUBTOTAL para cada grupo da seguinte forma:
=SUBTOTAL(9;B2:B10)
Isso aplica a operação SOMA (número da função 9) ao intervalo especificado e é frequentemente usado como parte do recurso Subtotal do Excel encontrado em Dados > Subtotal.
2. Para o total geral que exclui subtotais internos, insira a seguinte fórmula na célula escolhida:
=SUBTOTAL(9;B2:B21)
Pressione Enter para somar automaticamente os valores dos dados, ignorando quaisquer funções SUBTOTAL aninhadas dentro do intervalo. Essa abordagem é adequada para listas que usam a função SUBTOTAL para resumos de grupos e elimina o risco de somar subtotais várias vezes.
Esteja ciente de que a função SUBTOTAL funciona melhor quando as células de subtotal foram geradas usando SUBTOTAL em vez de SOMA. Além disso, se você filtrar ou ocultar linhas, SUBTOTAL pode ser configurado para somar apenas dados visíveis (não ocultos), o que é útil em situações de relatórios dinâmicos.
Código VBA - Somar valores excluindo linhas de subtotal
Se a estrutura dos seus dados for complexa ou você desejar um método automatizado para somar apenas as linhas de dados brutos (excluindo subtotais), pode utilizar uma macro VBA simples. Essa abordagem permite identificar e somar programaticamente apenas aquelas linhas que atendem aos seus critérios, como com base em formatação específica, presença de fórmulas ou outros atributos distintivos.
Por exemplo, se suas linhas de subtotal forem calculadas com fórmulas (como SOMA ou SUBTOTAL), e suas linhas de dados brutos contiverem apenas valores estáticos, você pode configurar o código VBA para somar apenas aquelas células em uma coluna específica que não contêm fórmulas. Aqui está como você pode fazer isso:
1. Clique Ferramentas de Desenvolvimento > Visual Basic para abrir a janela Microsoft Visual Basic for Applications.
2. Na nova janela, clique 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. Um prompt aparecerá para você selecionar o intervalo que contém seus dados. A macro então somará apenas as células na sua seleção que não contêm fórmulas, ignorando eficazmente as linhas típicas de subtotal.
Filtragem - Use filtro e SUBTOTAL para somar apenas linhas visíveis (não-subtotais)
Se o seu conjunto de dados permitir filtrar linhas de subtotal (por exemplo, há uma etiqueta, palavra-chave ou padrão para identificá-las), você pode usar o recurso Filtro integrado do Excel junto com a função SUBTOTAL para adicionar apenas as linhas de dados visíveis (não-filtradas). Essa técnica é prática para casos em que os subtotais têm um indicador ou etiqueta consistente.
1. Clique em qualquer lugar no seu conjunto de dados, vá até a aba Dados e clique em Filtro para habilitar filtros nas suas colunas.
2. Clique na seta para baixo na sua coluna auxiliar, desmarque "Subtotal" para ocultar as linhas de subtotal e exibir apenas os dados originais.
3. Em uma célula separada, insira a seguinte fórmula para somar apenas as linhas visíveis (filtradas):
=SUBTOTAL(9,B2:B21)A função SUBTOTAL (com o número da função 9 para SOMA) ignorará quaisquer linhas ocultas, retornando o total apenas das linhas atualmente visíveis (isto é, as linhas não-subtotais que você manteve após a filtragem). Essa solução é rápida e dinâmica – se você reaplicar filtros ou expandir seu conjunto de dados, a fórmula será atualizada automaticamente.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins