Como somar apenas as células visíveis com base em critérios no Excel?
No Excel, os utilizadores costumam somar células com base em critérios específicos utilizando a função SOMASES. Contudo, ao trabalhar com dados filtrados, a simples aplicação da SOMASES inclui tanto as células visíveis como as ocultas no cálculo, o que frequentemente conduz a resultados incorretos quando se pretende somar apenas as células visíveis (ou seja, não filtradas) que correspondam a determinados critérios, como ilustrado na imagem abaixo.
Somar apenas células visíveis com base num ou mais critérios com uma coluna auxiliar
Somar apenas células visíveis com base num ou mais critérios com uma fórmula
Somar apenas células visíveis com base em critérios utilizando código VBA
É comum, em relatórios diários e fluxos de trabalho de Análise de Dados, a necessidade de agregar com precisão dados em tabelas filtradas — por exemplo, ao calcular montantes de vendas para um determinado produto ou categoria após aplicar filtros. Fazê-lo de forma incorreta pode resultar em totais que incluam dados não pretendidos, pelo que é essencial utilizar técnicas que somem apenas os dados visíveis no seu ecrã.
Este artigo apresenta diversos métodos práticos, adaptados a diferentes cenários e níveis de proficiência, cada um com as suas vantagens e eventuais limitações. Escolha a solução que melhor se ajusta ao tamanho da sua folha de cálculo, à estrutura dos seus dados e aos seus hábitos operacionais. Abaixo encontrará etapas detalhadas para cada abordagem, acompanhadas de explicações sobre possíveis erros e dicas para otimizar o processo de cálculo e obter resultados mais fiáveis.
Somar apenas células visíveis com base num ou mais critérios com uma coluna auxiliar
Uma das abordagens mais intuitivas e estáveis para somar células visíveis com base em critérios específicos consiste em utilizar uma coluna auxiliar que identifique apenas as linhas visíveis e, em seguida, aplicar a função SOMASES com as suas condições pretendidas. Esta solução revela-se particularmente eficaz se o seu conjunto de dados for frequentemente filtrado de diversas formas ou se precisar de configurar cálculos claros e facilmente ajustáveis pelos seus colegas.
Vantagens: Configuração simples; toda a lógica e os cálculos permanecem visíveis na folha de cálculo; ideal para tabelas pequenas a médias; robusta quando é necessário ajustar ou auditar fórmulas.
Limitações: Cria colunas adicionais; poderá exigir atualizações nas fórmulas se a disposição das linhas for alterada; o uso extensivo pode tornar-se incómodo em conjuntos de dados muito grandes.
Por exemplo, para somar apenas os valores dos pedidos do produto «Hoodie» numa Intervalo de Filtro:
1. Introduza ou copie a seguinte fórmula numa coluna em branco junto ao seu conjunto de dados (por exemplo, na célula E2, assumindo que a coluna D contém os seus valores):
Arraste a alça de preenchimento para baixo, de modo a aplicar esta fórmula a todas as linhas do seu intervalo de dados. A fórmula devolverá o valor da coluna D se a linha estiver visível e 0 se estiver oculta pelo filtro.
2. Após gerar os valores auxiliares na coluna E, utilize a função SOMASES para somar apenas os valores visíveis de acordo com os seus critérios. Por exemplo, para somar os valores correspondentes a «Hoodie» na coluna A:
Pode adicionar mais critérios ao expandir os argumentos da função SOMASES no formato de =SOMASES(intervalo_soma; intervalo_critérios1; critérios1; [intervalo_critérios2; critérios2]; [intervalo_critérios3; critérios3]; ...). Verifique sempre os seus intervalos para garantir o alinhamento correto e obter os resultados esperados.
Atenção: Se reorganizar, inserir ou eliminar linhas após configurar as suas fórmulas, verifique novamente se todas as referências ainda correspondem à estrutura dos seus dados. Por vezes, os erros surgem devido a intervalos desalinhados ou ao esquecimento de atualizar as células dos critérios.
Somar apenas células visíveis com base em critérios com uma fórmula
Se preferir uma solução baseada em fórmulas que não exija colunas auxiliares, pode combinar as funções SOMARPRODUTO, SUBTOTAL, DESLOC, LIN e MÍNIMO para somar apenas as células visíveis que correspondam a critérios específicos. Esta abordagem é ideal para utilizadores experientes do Excel, familiarizados com fórmulas matriciais, e revela-se especialmente útil quando pretende manter a sua folha limpa e organizada, sem recorrer a colunas adicionais.
Vantagens: Não requer colunas adicionais na folha de cálculo; é flexível e dinâmica; a fórmula atualiza-se instantaneamente à medida que filtra ou altera os critérios.
Limitações: As fórmulas podem ser complexas de ler ou depurar, especialmente para quem não está familiarizado com funções matriciais; além disso, o desempenho pode diminuir em tabelas muito grandes.
Copie ou introduza a seguinte fórmula numa célula vazia (por exemplo, para somar células visíveis de «Hoodie» em A2:A12, com Valor Atual em D2:D12 e o critério em A17):
Após introduzir a fórmula, prima Enterpara obter o resultado pretendido, conforme ilustrado abaixo:
Atenção: esta abordagem é sensível aos intervalos definidos — intervalos incorretos ou sobrepostos podem causar erros ou resultados inesperados. Teste cenários extremos, especialmente quando a filtragem altera o número ou a posição das linhas visíveis.
Somar apenas células visíveis com base em critérios utilizando código VBA
Para utilizadores avançados, o VBA oferece uma abordagem flexível para somar apenas as células visíveis com base em critérios específicos — especialmente útil em cenários complexos ou com grandes volumes de dados, onde fórmulas convencionais podem enfrentar estrangulamentos de desempenho ou dificuldades em expressar lógica multicritério numa única fórmula. Com VBA, é possível iterar linha a linha nas células visíveis, avaliar as condições definidas e calcular a soma de forma eficiente. Esta solução destaca-se em tarefas repetitivas de relatórios ou na automatização de cálculos resumo.
Vantagens: Permite lidar facilmente com grandes conjuntos de dados, critérios múltiplos ou dinâmicos e lógica complexa; processa rapidamente, mesmo com milhares de linhas; e reduz o risco de erros causados por alterações manuais nas fórmulas.
Limitações: Requer ativação de macros; alguns utilizadores podem não estar familiarizados com VBA ou não ter as permissões adequadas; as alterações exigem acesso ao Editor do VBA. Faça sempre uma cópia de segurança antes de executar código VBA em conjuntos de dados importantes.
1. Para começar, abra o Editor do VBA clicando em Ferramentas de Programador > Visual Basic. Na janela que aparecer, vá a 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 prima)F5) para executar o código. Ser-lhe-á apresentada uma caixa de diálogo a solicitar que selecione o intervalo dos critérios (por exemplo, os nomes dos produtos), o intervalo dos valores a somar e o valor a utilizar como filtro (por exemplo, «Hoodie»). A macro somará apenas as linhas visíveis que correspondam ao critério e apresentará o resultado numa mensagem pop-up.
Dicas práticas: Utilize este código VBA sempre que precisar de recalcular rapidamente as somas após alterar os dados ou os filtros. Pode ainda expandir o código para trabalhar com múltiplos critérios, adicionando mais solicitações de entrada ou condições lógicas.
Resolução de problemas: Certifique-se sempre de que os intervalos selecionados para critérios e valores têm o mesmo número de linhas e correspondem exatamente às colunas dos seus dados filtrados. Se o código apresentar um erro ou não devolver a soma esperada, reveja cuidadosamente as definições do filtro e a seleção ativa.
Sugestões finais: Para análises de dados que exijam cálculos repetidos apenas nas linhas visíveis, guarde esta macro na sua Pasta de Macros Pessoal e acelere os seus relatórios diários. Se não aparecer nenhuma caixa de diálogo, verifique as definições e permissões de segurança das macros.
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