Neste artigo, aprenderemos como somar os valores N superiores ou inferiores em uma matriz de números no Excel. Se você deseja somar os 4, 5 ou N valores principais em um determinado intervalo, este artigo o ajudará.
Soma dos valores TOP N
Para este artigo, seremos obrigados a usar a função SUMPRODUCT. Agora faremos uma fórmula com essas funções. Aqui, recebemos um intervalo e precisamos superar os 5 valores principais do intervalo e obter a soma dos valores.
Fórmula genérica:
= SUMPRODUCT (LARGE (range, {1, 2,…., N}})
faixa: faixa de valores
Valores: números separados por vírgulas, como se você deseja encontrar os 3 valores principais, use {1, 2, 3}.
Exemplo: Soma dos 5 principais valores do intervalo
Vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui temos uma faixa de valores de A1: A23.
O intervalo A1: A23 contém valores e é denominado como valores.
Em primeiro lugar, precisamos encontrar os cinco principais valores usando a função LARGE e, em seguida, a operação de soma ser realizada sobre esses 5 valores. Agora usaremos a seguinte fórmula para obter a soma dos 5 principais valores.
Use a fórmula:
= SUMPRODUCT (LARGE (range, {1, 2, 3, 4, 5}))
Explicação:
- A função LARGE retorna os 5 principais valores numéricos e retorna a matriz para a função SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- A função SUMPRODUCT obtém uma matriz dos 5 principais valores para somar.
Aqui, o intervalo é fornecido como o intervalo nomeado. Pressione Enter para obter a soma dos 5 primeiros números.
Como você pode ver no instantâneo acima, essa soma é 544. A soma dos valores 149 + 123 + 100 + 87 + 85 = 544.
O processo acima é usado para calcular a soma de alguns números a partir do topo. Mas para calcular para n (grande) número de valores em um longo intervalo.
Use a fórmula:
= SUMPRODUCT (LARGE (range, ROW (INDIRECT ("1:10"))
Aqui, geramos a soma dos 10 principais valores obtendo uma matriz de 1 a 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} usando as funções ROW & INDIRECT Excel.
Aqui, obtivemos a soma dos 10 primeiros números {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}) que resulta em 850.
Soma dos valores de N inferiores
Como resolver o problema?
Fórmula genérica:
= SUMPRODUCT (PEQUENO (intervalo, {1, 2,…., N}})
Faixa: faixa de valores
Valores: números separados por vírgulas, como se você deseja encontrar os 3 valores inferiores, use {1, 2, 3}.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui temos uma faixa de valores de A1: A23.
Aqui está o intervalo fornecido com a ferramenta Excel de intervalo nomeado.
Em primeiro lugar, precisamos encontrar os cinco valores inferiores usando a função SMALL e, em seguida, a operação de soma ser realizada sobre esses 5 valores. Agora vamos usar a seguinte fórmula para obter a soma
Use a fórmula:
= SUMPRODUCT (PEQUENO (intervalo, {1, 2, 3, 4, 5}))
Explicação:
- A função SMALL retorna os 5 valores numéricos inferiores e retorna a matriz para a função SUMPRODUCT.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- A função SUMPRODUCT obtém uma matriz dos 5 valores inferiores para somar.
Aqui, o intervalo é fornecido como o intervalo nomeado. Pressione Enter para obter a soma dos 5 últimos números.
Como você pode ver no instantâneo acima, a soma é 136. A soma dos valores 23 + 27 + 28 + 28 + 30 = 136.
O processo acima é usado para calcular a soma de alguns números da parte inferior. Mas para calcular para n (grande) número de valores em um longo intervalo.
Use a fórmula:
= SUMPRODUCT (PEQUENO (intervalo, LINHA (INDIRETO ("1:10"))
Aqui, geramos a soma dos 10 valores mais baixos obtendo uma matriz de 1 a 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} usando as funções ROW & INDIRECT Excel.
Aqui temos a soma dos 10 últimos números que resultarão em 307.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números.
- A fórmula só funciona quando não há duplicatas na tabela de pesquisa
- A função SUMPRODUCT considera valores não numéricos (como texto abc) e valores de erro (como #NUM!, #NULL!) Como valores nulos.
- A função SUMPRODUCT considera o valor lógico TRUE como 1 e False como 0.
- A matriz do argumento deve ter o mesmo comprimento que a função.
Espero que este artigo sobre como retornar soma dos valores n superiores ou valores n inferiores no Excel seja explicativo. Encontre mais artigos sobre as funções SUMPRODUCT aqui. Por favor, compartilhe sua consulta abaixo na caixa de comentários. Nós iremos atendê-lo.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Converter polegadas em pés e polegadas no Excel 2016
Junte-se ao nome e sobrenome no excel
Contar células que correspondam a A ou B
