Como somar valores únicos com base em critérios no Excel?
Ao trabalhar com conjuntos de dados no Excel — como registos de encomendas, registos financeiros ou resultados de inquéritos — surgirá frequentemente a necessidade de calcular a soma dos valores únicos numa coluna com base em filtros ou critérios definidos noutra coluna. Por exemplo, imagine uma tabela com duas colunas: Nome e Encomenda. Se pretender somar apenas os valores únicos de Encomenda para cada Nome (ignorando duplicados), como o poderá fazer de forma eficiente no Excel? Trata-se de um requisito comum em diversos cenários empresariais ou de análise de dados, onde uma simples soma de todos os itens correspondentes originaria resultados inflacionados devido à presença de valores repetidos.
A captura de ecrã de exemplo abaixo ilustra um cenário típico: dada uma lista de nomes e os respetivos valores de encomenda — incluindo duplicados — pretende-se resumir os dados somando os valores de encomenda únicos para cada nome separadamente.
Os desafios comuns nesta tarefa incluem identificar entradas únicas com base em critérios específicos, garantir que apenas a primeira ocorrência seja contabilizada e evitar erros manuais decorrentes da cópia e colagem de dados filtrados. Diversas abordagens práticas no Excel — como fórmulas de matriz, Kutools e Power Query — oferecem soluções eficazes para este problema, cada uma adaptada a cenários de utilização distintos.
- Somar valores únicos com base num ou mais critérios com fórmulas de matriz
- Somar Valores Únicos Com Base em Critérios Utilizando o Mesclar Linhas Avançado do Kutools para Excel
- Outros Métodos Incorporados do Excel: Utilize o Tabela Dinâmica para análise de soma única
<h4"> Somar valores únicos com base num ou mais critérios com fórmulas de matriz
Uma abordagem eficaz e flexível consiste em utilizar fórmulas de matriz, que lhe permitem resumir valores únicos com base em critérios específicos. Esta solução revela-se particularmente vantajosa quando pretende que o cálculo seja atualizado automaticamente sempre que os dados ou os critérios forem alterados.
Para somar apenas os valores únicos numa coluna de acordo com o filtro ou condição noutra coluna, pode aplicar a seguinte fórmula:
1. Numa célula vazia (por exemplo,)E2), introduza 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 que contém os critérios (neste caso, nomes).
- D2: A célula onde se encontra a sua condição-alvo (por exemplo, um nome específico).
- B2:B12: O intervalo de valores que pretende somar de forma única.
Pode ajustar estes intervalos conforme necessário, de acordo com a estrutura real dos seus dados. Certifique-se de que todos os intervalos têm o mesmo comprimento para evitar erros na fórmula.
2. Para ativar esta fórmula de matriz, prima Ctrl + Shift + Enter simultaneamente após a introduzir. Aparecerão chavetas em redor da fórmula, indicando que se trata de 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 — assim, cada item obtém automaticamente a soma única correta.
Dica prática: Se estiver a utilizar o Excel 365 ou o Excel 2021, as novas funções de matriz dinâmica, como ÚNICO e SOMASES, podem simplificar ainda mais alguns destes cálculos — mas a fórmula acima funciona de forma fiável em muitas versões do Excel.
=SOMASE(A2:B12; ÚNICO(D2); B2:B12)
Lidar com mais critérios: =SOMA(SOMASES(intervalo_soma; intervalo_critérios1; ÚNICO(intervalo_critérios1); [intervalo_critérios2; critério2]; ...)
Precauções:
- Certifique-se de introduzir a fórmula como fórmula de matriz ()Ctrl + Shift + Enter) se estiver a utilizar o Excel 2019 ou versões anteriores. No Excel 365/2021, basta premir Enter normalmente para fórmulas dinâmicas.
- Se os seus intervalos forem particularmente grandes, a abordagem com matrizes poderá tornar-se mais lenta; por isso, considere filtrar previamente os seus dados ou utilizar outros métodos para conjuntos de dados muito extensos.
- Atenção especial a espaços extra e à consistência dos tipos de dados — formatações irregulares de texto ou números podem causar erros de não correspondência.
Dicas: Se precisar de somar todos os valores únicos com base em dois critérios, pode utilizar a seguinte extensão da fórmula de matriz:
=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))Esta fórmula segue um princípio semelhante, mas inclui um filtro adicional na coluna B(agora comparada com)F2 como condição secundária) e soma os valores únicos da coluna C. Após introduzir esta fórmula na sua célula de resumo selecionada, utilize Ctrl + Shift + Enter para confirmá-la e, em seguida, aplique-a às restantes linhas de resumo conforme necessário.
Sugestão de resumo: Embora as fórmulas de matriz forneçam resultados precisos na maioria dos cenários, verifique sempre a existência de duplicados ocultos — como os que contêm espaços extra ou diferenças na formatação do texto — e certifique-se de que as suas áreas de resumo obtêm dados de listas filtradas com rigor.
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
Somar Valores Únicos Com Base em Critérios Utilizando o Mesclar Linhas Avançado do Kutools para Excel
Kutools para Excel’s Mesclar Linhas Avançado torna extremamente fácil somar apenas valores únicos com base numa condição específica! Com apenas alguns cliques, agrupa os seus dados de forma inteligente e aplica uma lógica personalizada de resumo — sem fórmulas, sem complicações, apenas resultados precisos.
Passo 1: Selecione a Sua Tabela de Dados
Destaque toda a tabela, incluindo os cabeçalhos.
Passo 2: Aceda a Kutools > Conteúdo > Mesclar Linhas Avançado.
Passo 3: Defina a Coluna de Agrupamento
Na caixa de diálogo que surge, escolha a coluna pela qual pretende agrupar (por exemplo, Fruta) e defina-a como Chave Primária na secção Operação.
Passo 4: Defina o Campo para Soma Única
Selecione a coluna Vendas e defina o tipo de cálculo pretendido (por exemplo, Soma) na secção Operação.
Dica: Pode pré-visualizar o resultado combinado diretamente na caixa de diálogo.
Passo 5: Clique em OK. A tabela encontra-se agora agrupada por cliente, com a soma dos montantes únicos de cada produto.
Outros métodos incorporados no Excel: Utilize Tabela Dinâmica para análise de soma única
A funcionalidade Tabela Dinâmica do Excel oferece uma abordagem incorporada e poderosa para resumir dados com base em critérios. Embora as Tabelas Dinâmicas não somem diretamente valores únicos por predefinição, a partir do Excel 2013 suportam o cálculo de Contagem Distinta, que lhe permite analisar a contagem de entradas únicas num campo especificado. Apesar de isto não calcular diretamente a soma de valores únicos, pode utilizar a Contagem Distinta em conjunto com ajustes manuais ou um campo calculado para obter um resumo semelhante.
Vantagens: As Tabelas Dinâmicas dispensam a memorização de fórmulas e programação em VBA, oferecendo interfaces altamente flexíveis com funcionalidade de arrastar e largar. São ideais para relatórios periódicos, análises por grupo, visões gerais rápidas ou trabalho em equipa. Contudo, revelam-se mais adequadas para resumos e análises do que para criar fórmulas destinadas a cálculos posteriores ou automatizações.
Eis como utilizar uma Tabela Dinâmica para análise de soma única:
- Selecione o seu intervalo de dados (por exemplo,)A1:B12, incluindo cabeçalhos) e vá a Inserir > Tabela Dinâmica. Na caixa de diálogo, escolha se pretende colocar a Tabela Dinâmica numa nova folha ou numa folha existente.
- Na Lista de Campos da Tabela Dinâmica, arraste Nome para a área Linhas e Encomenda para a área Valores.
- Para as entradas Encomenda na área Valores, clique na seta suspensa > Campo de Valor Configurações de Campo > e defina como Soma (mostra a soma total das encomendas, incluindo duplicados).
Limitações:
- A função Contagem Distinta está disponível apenas no Excel 2013 ou versões posteriores; versões anteriores exigem trabalho manual adicional.
Em resumo, embora as Tabelas Dinâmicas sejam excelentes para resumos e análises interativas, considere combiná-las com abordagens baseadas em fórmulas ou VBA se precisar de cálculos reais de soma única.
Mais artigos relacionados:
- Somar Múltiplas Colunas Com Base num Único Critério no Excel
- No Excel, poderá frequentemente precisar de somar várias colunas com base num único critério. Por exemplo, tenho um intervalo de dados como o apresentado na seguinte captura de ecrã e pretendo obter o total dos valores da KTE nos três meses — jan., fev. e mar.
- ProcurarValor e Somar Correspondências em Linhas ou Colunas no Excel
- As funções PROCV e SOMA permitem-lhe encontrar rapidamente o critério especificado e somar os respetivos valores em simultâneo. Neste artigo, mostramos-lhe dois métodos para procurar e somar o primeiro valor correspondente ou todos os valores correspondentes em linhas ou colunas no Excel.
- Somar Valores Com Base no Mês e Ano no Excel
- Se tiver um intervalo de dados em que a coluna A contém datas e a coluna B apresenta o número de encomendas, poderá agora precisar de somar esses valores com base no mês e ano de outra coluna. Neste caso, pretendo calcular o total de encomendas de janeiro de 2016 para obter o resultado seguinte. Neste artigo, explico alguns truques práticos para resolver esta tarefa no Excel.
- Somar Valores Com Base em Critérios de Texto no Excel
- No Excel, já alguma vez precisou de somar valores com base num critério textual noutra coluna? Por exemplo, tenho um intervalo de dados na folha de cálculo, como mostra a seguinte captura de ecrã, e quero somar todos os números da coluna B cujas células correspondentes na coluna A contenham “KTE”.
- Somar Valores Com Base na Seleção de Lista suspensa no Excel
- Conforme ilustra a captura de ecrã abaixo, tem uma tabela com uma coluna **Categoria** e uma coluna **Montante**, e criou uma validação de dados do tipo **Lista suspensa** que inclui todas as categorias. Ao selecionar qualquer categoria na lista suspensa, pretende somar automaticamente todos os valores correspondentes na coluna B e apresentar o resultado numa célula específica. Por exemplo, ao escolher a categoria **CC** na lista suspensa, deseja somar os valores das células **B5** e **B8**, obtendo o total **40 + 70 = 110**. Como pode alcançar este objetivo? O método descrito neste artigo pode ajudá-lo.
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