Somar os menores ou os N valores inferiores com base em critérios no Excel
No tutorial anterior, discutimos como somar os menores n valores em um intervalo de dados. Neste artigo, realizaremos uma operação mais avançada – somar os menores n valores com base em um ou mais critérios no Excel.
Somar os menores ou os N valores inferiores com base em critérios no Excel
Suponha que eu tenha um intervalo de dados como mostrado na captura de tela abaixo; agora, quero somar as 3 menores encomendas do produto Apple.
No Excel, para somar os N valores inferiores em um intervalo com critérios, você pode criar uma fórmula de matriz usando as funções SOMA, MENOR e SE. A sintaxe genérica é:
Fórmula de matriz, deve-se pressionar as teclas Ctrl + Shift + Enter juntas.
- intervalo=critério: O intervalo de células que corresponde ao critério específico;
- valores: A lista que contém os N valores inferiores que você deseja somar;
- N: O N-ésimo valor inferior.
Para resolver o problema acima, aplique a seguinte fórmula de matriz em uma célula em branco:
E então pressione as teclas Ctrl + Shift + Enter juntas para obter o resultado correto, conforme mostrado na captura de tela abaixo:
Explicação da fórmula:
=SOMA(MENOR(SE(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}))
- SE(($A$2:$A$14=D2); $B$2:$B$14): Se o produto no intervalo A2:A14 for igual a “Apple”, ele retornará o número relativo da lista de pedidos (B2:B14); se o produto não for “Apple”, será exibido FALSO. Você obterá o resultado assim: {800;FALSO;FALSO;FALSO;1000;230;FALSO;FALSO;1600;FALSO;900;FALSO;500}.
- MENOR(SE(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}): Esta função MENOR ignorará os valores FALSO e retornará os 3 menores valores na matriz, então o resultado será este: {230;500;800}.
- SOMA(MENOR(SE(($A$2:$A$14=D2); $B$2:$B$14);{1;2;3}))=SOMA({230;500;800}): Finalmente, a função SOMA soma os números na matriz para obter o resultado: 1530.
Dicas: Lidando com duas ou mais condições:
Se você precisar somar os N valores inferiores com base em dois ou mais critérios, basta adicionar outro intervalo e critério usando o caractere * dentro da função SE, assim:
Fórmula de matriz, deve-se pressionar as teclas Ctrl + Shift + Enter juntas.
- Intervalo1=critério1: O primeiro intervalo de células que corresponde ao primeiro critério;
- Intervalo2=critério2: O segundo intervalo de células que corresponde ao segundo critério;
- Intervalo3=critério3: O terceiro intervalo de células que corresponde ao terceiro critério;
- valores: A lista que contém os N valores inferiores que você deseja somar;
- N: O N-ésimo valor inferior.
Por exemplo, quero somar as 3 menores encomendas do produto Apple vendidas por Kerry, aplique a seguinte fórmula:
E então pressione as teclas Ctrl + Shift + Enter juntas para obter o resultado de que você precisa:
Função relacionada usada:
- SOMA:
- A função SOMA adiciona valores. Você pode adicionar valores individuais, referências de células ou intervalos, ou uma mistura dos três.
- MENOR:
- A função MENOR do Excel retorna um valor numérico com base em sua posição em uma lista quando ordenada por valor em ordem crescente.
- SE:
- A função SE testa uma condição específica e retorna o valor correspondente que você forneceu para VERDADEIRO ou FALSO.
Mais artigos:
- Somar os menores ou os N valores inferiores
- No Excel, é fácil somar um intervalo de células usando a função SOMA. Às vezes, você pode precisar somar os menores ou os 3, 5 ou N menores números em um intervalo de dados, conforme mostrado na captura de tela abaixo. Nesse caso, a função SOMARPRODUTO junto com a função MENOR pode ajudá-lo a resolver esse problema no Excel.
- Subtotalizar valores de faturas por idade no Excel
- Somar os valores das faturas com base na idade, conforme mostrado na captura de tela abaixo, pode ser uma tarefa comum no Excel; este tutorial mostrará como subtotalizar valores de faturas por idade usando a função normal SOMASE.
- Somar todas as células numéricas ignorando erros
- Ao somar um intervalo de números que contêm alguns valores de erro, a função SOMA normal não funcionará corretamente. Para somar apenas números e ignorar os valores de erro, a função AGREGAR ou a função SOMA junto com a função SEERRO podem ajudá-lo.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.