KutoolsforOffice — Uma solução, cinco ferramentas poderosas.Fazer mais com menos esforço.

Somar os N valores mais pequenos ou inferiores com base em critérios no Excel

AutorXiaoyang Data de Modificação

Num tutorial anterior, abordámos como somar os n valores mais pequenos num intervalo de dados. Neste artigo, vamos dar um passo além – somar os n valores mais baixos com base num ou mais critérios no Excel.

doc-sum-bottom-n-with-criteria-1

Somar os N valores mais pequenos ou inferiores com base em critérios no Excel

Suponhamos que tenho um conjunto de dados como o ilustrado na imagem seguinte e quero somar as três encomendas mais baixas do produto Apple.

doc-sum-bottom-n-with-criteria-2

No Excel, para somar os n menores valores de um intervalo com base em critérios específicos, pode criar uma fórmula de matriz utilizando as funções SOMA, MENOR e SE. A sintaxe genérica é:

{=SUM(SMALL(IF(range=criteria,values),{1,2,N}))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • range=criteria: O intervalo de células a comparar com os critérios específicos;
  • values: A lista que contém os n valores inferiores que pretende somar;
  • N: O enésimo valor mais baixo.

Para resolver o problema acima, aplique a seguinte fórmula de matriz numa célula vazia:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

Em seguida, prima simultaneamente as teclas Ctrl + Shift + Enterpara obter o resultado correto, tal como ilustrado na imagem seguinte:

doc-sum-bottom-n-with-criteria-3

Explicação da fórmula:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

  • IF(($A$2:$A$14=D2), $B$2:$B$14): Se o produto no intervalo A2:A14 for igual a «Apple», devolve o número correspondente da lista de encomendas (B2:B14); caso contrário, apresenta FALSO. Obterá um resultado semelhante a este: {800;FALSO;FALSO;FALSO;1000;230;FALSO;FALSO;1600;FALSO;900;FALSO;500}.
  • SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Esta função MENOR ignora os valores FALSO e devolve os três valores mais baixos do array, pelo que o resultado será: {230,500,800}.
  • SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Por fim, a função SOMA adiciona os números do array para obter o resultado: 1530.

Dicas: Tratar duas ou mais condições:

Se precisar de somar os n valores inferiores com base em dois ou mais critérios, basta adicionar outros intervalos e critérios utilizando o caráter * dentro da função SE, desta forma:

{=SUM(SMALL(IF((range1=criteria1)*(range2=criteria2) *(range3=criteria3)…,values),{1,2,N}))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • Range1=criteria1: O primeiro intervalo de células a comparar com o primeiro critério;
  • Range2=criteria2: O segundo intervalo de células a comparar com o segundo critério;
  • Range3=criteria3: O terceiro intervalo de células a comparar com o terceiro critério;
  • values: A lista que contém os n valores inferiores que pretende somar;
  • N: O enésimo valor inferior.

Por exemplo, pretende somar as 3 encomendas mais baixas do produto Apple vendidas pela Kerry. Nesse caso, aplique a fórmula seguinte:

=SUM(SMALL(IF(($A$2:$A$14=E2)*($B$2:$B$14=F2), $C$2:$C$14),{1,2,3}))

Em seguida, prima simultaneamente as teclas Ctrl + Shift + Enterpara obter o resultado pretendido:

doc-sum-bottom-n-with-criteria-4

Função relacionada utilizada:

  • SOMA:
  • A função SOMA adiciona valores — quer sejam individuais, referências de células, intervalos ou uma combinação dos três.
  • MENOR:
  • A função MENOR do Excel devolve um valor numérico com base na sua posição numa lista ordenada por ordem crescente.
  • SE:
  • A função SE avalia uma condição específica e devolve o valor que você indicar para VERDADEIRO ou FALSO.

Mais artigos:

  • Somar os N Valores Mais Pequenos ou Inferiores
  • No Excel, somar um intervalo de células é fácil com a função SOMA. Porém, por vezes poderá precisar de somar os 3, 5 ou n menores valores num intervalo de dados, tal como ilustrado na imagem seguinte. Nesses casos, a combinação das funções SOMARPRODUTO e MENOR permite-lhe resolver este problema no Excel.
  • Subtotalizar Valores de Faturas por Idade no Excel
  • Somar os montantes das faturas com base na idade, tal como ilustrado na imagem seguinte, é uma tarefa comum no Excel. Este tutorial mostra-lhe como subtotalizar esses montantes por idade utilizando a função SOMASE.
  • Somar Todas as Células Numéricas Ignorando Erros
  • Ao somar um intervalo de números que contenha valores de erro, a função SOMA normal não funcionará corretamente. Para somar apenas os números e ignorar os erros, utilize a função AGREGAR ou combine as funções SOMA e SEERRO.

As Melhores Ferramentas de Produtividade para o Office

Kutools for Excel – Ajuda-o a destacar-se da multidão

🤖KUTOOLS AI Assistente: Revolucione o Análise de Dados com base em:Execução Inteligente   |  Gerar Código|  Criar fórmulas personalizadas  |  Analisar Dados e Gerar Gráficos|  Invocar Funções Aprimoradas…
Funcionalidades Populares:Localizar, Destacar ou Marcar Duplicatas  |  Excluir linhas em branco  |  Combinar Colunas ou Células sem Perder Dados  |  Arredondamento sem usar fórmula…
Super VLookup:Múltiplos Critérios  |  Múltiplos Valores  |  Entre Múltiplas Folhas  |  Correspondência Fuzzy…
Avanç. Lista suspensa:Lista de Opções Simples  |  Lista de Opções Dependente  |  Lista de Opções com Seleção Múltipla…
Gestor de Colunas:Adicionar um Número Específico de Colunas  |  Mover Colunas  |  Alternar Estado de Visibilidade de Colunas Ocultas  |Comparar Colunas para Selecionar Células Iguais/Diferentes…
Funcionalidades em Destaque:Grade de foco  |  Visualização de Design  |  Barra de fórmulas aprimorada  |  Gestor de Pastas de Trabalho e Folhas|Biblioteca de Recursos(Texto Automático)|  Seleção de Data  |  Consolidar Planilhas  |  Encriptar/Descriptografar Células  |  Enviar Emails por Lista  |  Super Filtro  |  Filtro Especial(Filtrar Células com Fonte em Negrito/itálico/rasurado…) …
Principais Conjuntos de Ferramentas 15:12 Ferramentasde Texto(Adicionar Texto,Excluir Caracteres Específicos…)|  50+Tiposde Gráfico(Gráfico de Gantt…)|  40+ Fórmulas Práticas(Calcular a idade com base na data de nascimento…)|  19 Ferramentasde Inserção(Inserir QR Code,Inserir Imagem a Partir do Caminho…)|  12 Ferramentasde Conversão(Converter em Palavras,Conversão de moeda…)|  7 Ferramentasde Mesclar e Dividir(Mesclar Linhas Avançado,Dividir Células do Excel…)|… e muito mais
Utilize o Kutools na sua língua preferida – suporta inglês, espanhol, alemão, francês, chinês e mais 40+ idiomas!

Kutools for Excel Oferece mais de 300 funcionalidades,garantindo que tudo o que precisa está a apenas um clique…

Office Tab – Ative a leitura e edição com separadores no Microsoft Office (incluindo o Excel)

  • Um segundo para alternar entre dezenas de documentos abertos!
  • Reduza centenas de cliques diários e diga adeus à tendinite do rato.
  • Aumente a sua produtividade em 50 % ao visualizar e editar vários documentos simultaneamente.
  • Traz uma navegação eficiente com separadores ao Office (incluindo o Excel), tal como no Chrome, Edge e Firefox.