Neste artigo, aprenderemos como somar os N maiores valores em um intervalo no Excel.
Cenário:
Em palavras simples, ao trabalhar com números de dados, às vezes dada uma condição, ou seja, quando precisamos obter a soma dos números dos 5 primeiros números da matriz ou da parte inferior da matriz. Você pode executar a solução para este problema facilmente usando as funções do Excel, conforme explicado a seguir.
Soma dos valores TOP N
Como resolver o problema?
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}}) |
intervalo: intervalo de valores
Valores: números separados por vírgulas, como se você deseja encontrar os 3 valores principais, 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, o intervalo é fornecido usando a ferramenta Excel de intervalo nomeado.
Em primeiro lugar, precisamos encontrar os cinco valores principais usando a função LARGE 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 (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 a matriz dos 5 valores principais, que possui uma matriz dos 5 números principais e retorna a soma desses números.
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, a 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?
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 atingir os 5 valores mais baixos do intervalo e obter a soma dos valores.
Fórmula genérica:
| = SUMPRODUCT (PEQUENO (intervalo, {1, 2,…., N}}) |
intervalo: intervalo 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, o intervalo é fornecido usando 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 a matriz dos 5 valores inferiores, que possui uma matriz dos 5 números inferiores que retorna a SOMA desses números.
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, obtivemos a soma dos 10 últimos números, o que resulta em 307.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números.
- A fórmula funciona apenas 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 do que a função.
Espero que este artigo sobre como somar os N maiores valores em um intervalo no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
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 :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.