Como somar valores únicos com base em critérios no Excel?

Ao trabalhar com conjuntos de dados do Excel — como registros de pedidos, registros financeiros ou resultados de pesquisas — você pode frequentemente encontrar a necessidade de calcular a soma de valores únicos de uma coluna com base em filtros ou critérios de outra coluna. Por exemplo, considere uma tabela de dados contendo duas colunas: Nome e Pedido. Se você quiser calcular a soma apenas dos valores únicos de Pedido para cada Nome (ignorando valores repetidos), como pode alcançar isso de forma eficiente no Excel? Essa é uma exigência comum em muitos cenários de negócios ou análise de dados onde simplesmente somar todos os itens correspondentes resultaria em totais inflados devido a duplicatas.

A captura de tela de exemplo abaixo demonstra um cenário típico: dada uma lista de nomes e seus respectivos valores de pedido — incluindo duplicatas — você deseja resumir os dados somando valores únicos de pedido separadamente para cada nome.

Desafios comuns nessa tarefa incluem identificar entradas únicas com base em critérios específicos, garantir que apenas a primeira ocorrência seja contada e prevenir erros manuais que podem surgir ao copiar e colar dados filtrados. Várias abordagens práticas no Excel, incluindo fórmulas de matriz, Kutools e Power Query, podem ajudar a resolver esse problema, cada uma adequada para diferentes cenários de uso.

• Somar valores únicos com base em um ou mais critérios com fórmulas de matriz
• Somar Valores Únicos Com Base em Critérios Usando Kutools para Excel’s Advanced Combine Rows
• Outros Métodos Internos do Excel: Use Tabela Dinâmica para análise de soma única

Somar valores únicos com base em um ou mais critérios com fórmulas de matriz

Uma abordagem eficaz e flexível é usar fórmulas de matriz, que permitem resumir valores únicos que atendem a critérios específicos. Isso funciona especialmente bem quando você deseja que o cálculo seja atualizado automaticamente conforme os dados ou critérios mudam.

Para somar apenas os valores únicos em uma coluna de acordo com o filtro ou condição em outra coluna, você pode aplicar a seguinte fórmula:

1. Em uma célula em branco (por exemplo, E2), insira esta fórmula:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))

Antes de confirmar a fórmula, verifique cuidadosamente se:

• A2:A12: O intervalo contendo os critérios (nomes neste caso).
• D2: A célula onde está localizada sua condição alvo (como um nome específico).
• B2:B12: O intervalo de valores que você deseja somar de forma única.

Você pode ajustar esses intervalos conforme necessário com base na estrutura real de seus dados. Certifique-se de que todos os intervalos tenham o mesmo comprimento para evitar erros na fórmula.

2. Para ativar essa fórmula de matriz, pressione Ctrl + Shift + Enter ao mesmo tempo após digitá-la. Chaves aparecerão ao redor da fórmula, indicando que é uma fórmula de matriz. Em seguida, arraste a alça de preenchimento para baixo para copiar a fórmula para cada valor correspondente na sua coluna de resumo. Dessa forma, cada item obtém automaticamente a soma única correta.

Dica prática: Se você estiver usando o Excel 365 ou o Excel 2021, novas funções de matriz dinâmica como UNIQUE e SUMIFS podem simplificar ainda mais alguns desses cálculos, mas a fórmula acima funciona de maneira confiável em várias versões do Excel.

=SOMASE(A2:B12; ÚNICO(D2); B2:B12)

Lidar com mais critérios: =SOMA(SOMASES(intervalo_soma; intervalo_critério1; ÚNICO(intervalo_critério1); [intervalo_critério2; critério2]; ...)

Precauções:

• Certifique-se de estar usando a entrada de fórmula de matriz (Ctrl + Shift + Enter) se estiver no Excel 2019 ou anterior. Para o Excel 365/2021, um Enter regular pode ser suficiente para fórmulas dinâmicas.
• Se seus intervalos de dados forem especialmente grandes, a abordagem de matriz pode ficar mais lenta, então considere filtrar seus dados primeiro ou usar outros métodos para conjuntos de dados muito grandes.
• Observe cuidadosamente por espaços extras ou consistência no tipo de dado — formatação irregular de texto ou números pode causar erros de não correspondência.

Dicas: Se você precisar somar todos os valores únicos com base em dois critérios, a seguinte extensão da fórmula de matriz pode ser usada:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

Essa fórmula é semelhante em princípio, mas suporta um filtro adicional da coluna B (agora comparado com F2 como uma condição secundária) e soma valores únicos da coluna C. Após inserir essa fórmula na célula de resumo selecionada, use Ctrl + Shift + Enter para confirmá-la e, em seguida, aplique-a a outras linhas de resumo conforme necessário.

Sugestão de resumo: Embora as fórmulas de matriz forneçam resultados precisos para a maioria dos cenários, sempre verifique novamente por duplicatas ocultas (como aquelas com espaços extras ou diferenças de formatação de texto) e certifique-se de que suas áreas de resumo extraem listas filtradas com precisão.

Desbloqueie a Magia do Excel com o Kutools AI

• Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
• Codificação VBA: Escreva e implemente código VBA sem esforço.
• Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
• Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.

Potencialize suas capacidades no Excel com ferramentas alimentadas por IA. Baixe Agora e experimente uma eficiência como nunca antes!

---

---

Somar Valores Únicos Com Base em Critérios Usando Kutools para Excel’s Advanced Combine Rows

O recurso Advanced Combine Rows do Kutools para Excel torna fácil somar apenas valores únicos com base em uma condição específica! Com apenas alguns cliques, ele agrupa inteligentemente seus dados e aplica lógica de resumo personalizada — sem fórmulas, sem complicações, apenas resultados precisos.

Passo 1: Selecione Sua Tabela de Dados

Destaque a tabela completa, incluindo os cabeçalhos.

Passo 2: Vá para Kutools > Texto > Advanced Combine Rows.

Passo 3: Defina a Coluna de Agrupamento

Na caixa de diálogo pop-up, escolha a coluna pela qual você deseja agrupar (por exemplo, Fruta), defina-a como Chave Primária na seção Operação.

Passo 4: Defina o Campo para Soma Única

Selecione a coluna Vendas, defina o cálculo desejado (por exemplo, Soma) na seção Operação.

Dica: Você pode visualizar o resultado combinado na caixa de diálogo.

Passo 5: Clique OK. A tabela agora está agrupada por cliente, cada um com a soma de valores únicos de produtos.

⚡ O Kutools para Excel oferece mais de 300 recursos poderosos para turbinar sua produtividade — e você pode experimentá-los todos gratuitamente por 30 dias!

Outros Métodos Incorporados do Excel: Use Tabela Dinâmica para análise de soma única

O recurso Tabela Dinâmica do Excel oferece outra abordagem interna poderosa para resumir dados com base em critérios. Embora as Tabelas Dinâmicas não somem diretamente valores únicos por padrão, após o Excel 2013, elas suportam o cálculo de Contagem Distinta, o que ajuda a analisar a contagem de entradas únicas para um campo especificado. Embora isso não calcule diretamente a soma de valores únicos, você pode usar Contagem Distinta junto com ajustes manuais ou um campo calculado para facilitar um resumo semelhante.

Vantagens: As Tabelas Dinâmicas não requerem memorização de fórmulas ou codificação VBA e oferecem interfaces altamente flexíveis de arrastar e soltar. Adequadas para relatórios periódicos, análise de grupo, visões rápidas ou ao colaborar entre equipes. No entanto, são melhores para resumos e análises, não para construir fórmulas para cálculos ou automação adicionais.

Aqui está como usar uma Tabela Dinâmica para análise de soma única:

1. Selecione seu intervalo de dados (por exemplo, A1:B12, incluindo cabeçalhos) e vá para Inserir > Tabela Dinâmica. Na caixa de diálogo, selecione se deseja colocar a Tabela Dinâmica em uma nova planilha ou em uma existente.
2. Na Lista de Campos da Tabela Dinâmica, arraste Nome para a área Linhas e Pedido para a área Valores.
3. Para as entradas de Pedido na área Valores, clique na seta suspensa > Configurações de Campo de Valor > defina para Soma (mostra a soma total de pedidos, incluindo duplicatas).

Limitações:

• A função Contagem Distinta só está disponível no Excel 2013 ou posterior; versões anteriores exigem mais trabalho manual.

Em resumo, embora a Tabela Dinâmica seja excelente para resumos interativos e análise, considere combiná-la com abordagens baseadas em fórmulas ou VBA se você precisar de cálculos de soma verdadeiramente únicos.

Mais artigos relacionados: