Como somar apenas as células visíveis com base em critérios no Excel?
No Excel, os usuários geralmente podem somar células com base em critérios específicos usando a função SOMASES. No entanto, ao lidar com dados filtrados, simplesmente aplicar o SOMASES incluirá tanto as células visíveis quanto as ocultas no cálculo. Isso frequentemente leva a resultados incorretos se você precisar somar apenas as células visíveis (ou seja, não filtradas) que correspondem a determinados critérios, como mostrado na captura de tela abaixo.
Somar apenas células visíveis com base em um ou mais critérios usando uma coluna auxiliar
Somar apenas células visíveis com base em um ou mais critérios usando uma fórmula
Somar apenas células visíveis com base em critérios usando código VBA
É uma necessidade comum em fluxos de trabalho de relatórios diários e análise de dados agregar dados com precisão em tabelas filtradas, como ao calcular valores de vendas para um produto ou categoria específica após aplicar alguns filtros. Fazer isso incorretamente pode resultar em totais que incluem dados que você não pretendia, por isso é importante usar técnicas que somem apenas os dados visíveis que você vê na sua tela.
Este artigo introduz vários métodos práticos adequados para diferentes cenários e níveis de proficiência, cada um com suas vantagens e possíveis limitações. Você pode selecionar uma solução que melhor se adapte ao tamanho da sua planilha, estrutura de dados e hábitos operacionais. As etapas detalhadas para cada solução são fornecidas abaixo, juntamente com explicações de possíveis erros e maneiras de otimizar o processo de cálculo para resultados mais confiáveis.
Somar apenas células visíveis com base em um ou mais critérios usando uma coluna auxiliar
Uma das abordagens mais intuitivas e estáveis para somar células visíveis com base em critérios específicos é usar uma coluna auxiliar que retorna valores apenas para linhas visíveis, e depois aproveitar a função SOMASES com suas condições desejadas. Isso é especialmente eficaz se seu conjunto de dados for filtrado frequentemente de várias maneiras ou se você precisar configurar cálculos que colegas possam entender ou modificar facilmente.
Vantagens: Fácil de configurar; toda a lógica e cálculos permanecem visíveis na planilha; ideal para tabelas pequenas e médias; robusto ao ajustar ou auditar fórmulas.
Limitações: Cria colunas adicionais; pode ser necessário atualizar fórmulas se o layout das linhas mudar; uso extensivo pode se tornar complicado em conjuntos de dados muito grandes.
Por exemplo, para somar apenas os valores dos pedidos do produto "Hoodie" em um intervalo filtrado:
1. Insira ou copie a seguinte fórmula em uma coluna em branco ao lado do seu conjunto de dados (por exemplo, na célula E2, assumindo que D é sua coluna de valores):
Arraste a alça de preenchimento para baixo para preencher essa fórmula em todas as linhas do intervalo de dados. Essa fórmula retornará o valor da coluna D se a linha estiver visível e 0 se a linha estiver oculta por meio de filtro.
2. Após gerar os valores auxiliares na coluna E, use uma função SOMASES para somar apenas os valores visíveis com base nos seus critérios. Por exemplo, para somar para "Hoodie" na coluna A:
Você pode adicionar mais critérios estendendo os argumentos de SOMASES no formato =SOMASES(intervalo_soma, intervalo_critérios1, critério1, [intervalo_critérios2, critério2], [intervalo_critérios3, critério3], ...). Sempre verifique seus intervalos para garantir o alinhamento correto e os resultados esperados.
Atenção: Se você reorganizar, inserir ou excluir linhas após configurar suas fórmulas, verifique novamente para garantir que todas as referências ainda correspondam à sua estrutura de dados. Às vezes, erros podem ocorrer devido a intervalos desalinhados ou esquecimento de atualizar suas células de critérios.
Somar apenas células visíveis com base em critérios usando uma fórmula
Se você prefere uma solução baseada em fórmulas que não requer a adição de colunas auxiliares, pode usar uma combinação das funções SOMARPRODUTO, SUBTOTAL, DESLOC, LIN e MÍNIMO para somar células visíveis de acordo com critérios específicos. Essa abordagem é ideal para usuários experientes do Excel familiarizados com fórmulas matriciais e é particularmente útil quando você deseja manter sua planilha organizada sem colunas extras.
Vantagens: Não é necessário ter colunas extras na planilha; flexível e dinâmico; a fórmula é atualizada instantaneamente ao filtrar ou alterar os critérios.
Limitações: As fórmulas podem ser complexas de ler ou depurar, especialmente para aqueles não familiarizados com funções matriciais; o desempenho pode diminuir em tabelas muito grandes.
Copie ou insira a seguinte fórmula em uma célula em branco (por exemplo, para somar células visíveis para "Hoodie" em A2:A12, com os valores reais em D2:D12 e os critérios em A17):
Após inserir a fórmula, pressione Enter para obter o resultado desejado, conforme mostrado abaixo:
Atenção: Essa abordagem é sensível aos intervalos especificados — intervalos incompatíveis ou sobrepostos podem causar erros ou resultados inesperados. Teste casos extremos, especialmente quando a filtragem altera o número ou posição das linhas visíveis.
Somar apenas células visíveis com base em critérios usando código VBA
Para usuários avançados, usar VBA oferece uma maneira flexível de somar apenas células visíveis com base em critérios específicos, especialmente ao lidar com cenários complexos ou grandes conjuntos de dados onde fórmulas padrão podem sofrer com gargalos de desempenho ou onde a contagem de critérios inclui lógica multi-condicional difícil de expressar em uma única fórmula. O VBA pode iterar através de cada linha visível, testar suas condições e calcular a soma de forma eficiente. Isso é particularmente adequado para tarefas de relatório repetidas ou ao automatizar cálculos de resumo.
Vantagens: Pode lidar facilmente com grandes conjuntos de dados, múltiplos ou critérios dinâmicos e lógica complexa; o processo é executado rapidamente mesmo com milhares de linhas; reduz o risco de erros devido a mudanças manuais nas fórmulas.
Limitações: Requer a habilitação de macros; alguns usuários podem não estar familiarizados com VBA ou ter permissões adequadas; as alterações exigem acesso ao Editor de Macro. Sempre faça um backup antes de executar o VBA em conjuntos de dados importantes.
1. Para começar, abra o Editor de VBA clicando em Ferramentas de Desenvolvedor > Visual Basic. Na janela que aparece, vá para Inserir > Módulo e cole o seguinte código no novo módulo:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Clique no 
botão "Executar" (ou pressione F5) para executar o código. Um diálogo solicitará que você selecione o intervalo de critérios (como seus nomes de produtos), o intervalo de valores a ser somado e qual valor você deseja como filtro (por exemplo, "Hoodie"). A macro somará apenas aquelas linhas visíveis onde seus critérios forem atendidos e mostrará o resultado em uma mensagem pop-up.
Dicas práticas: Use este código VBA quando você precisa recalcular suas somas frequentemente após alterar seus dados ou filtros. Você pode expandir ainda mais o código VBA para funcionar com múltiplos critérios adicionando mais prompts de entrada ou condições lógicas.
Solução de problemas: Sempre certifique-se de que os intervalos selecionados para critérios e valores tenham o mesmo número de linhas e pertençam às mesmas colunas dos seus dados filtrados. Se o código relatar um erro ou não retornar a soma esperada, verifique novamente suas configurações de filtro e seleção ativa.
Sugestões de resumo: Para análises de dados que exigem cálculos repetidos considerando apenas células visíveis, salvar esta macro no seu Pasta de Trabalho de Macros Pessoal pode acelerar seus relatórios diários. Se uma caixa de diálogo não aparecer, verifique suas configurações de macro e permissões de segurança.
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