No artigo anterior, aprendemos como somar os valores N superiores ou inferiores. Neste artigo, tentamos somar N valores superiores ou inferiores com um critério.
Soma dos valores TOP N com critérios
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 um critério. Precisamos obter os 5 valores principais do intervalo e obter a soma dos valores com base nos critérios fornecidos.
Fórmula genérica:
= SUMPRODUCT (GRANDE ((lista = critérios) * (intervalo), {1, 2,…., N}})
Lista: lista de critérios
Critério: critérios para combinar
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:
Aqui temos os valores do conjunto de dados de A1: D50.
Em primeiro lugar, precisamos encontrar os cinco valores principais usando a função LARGE que corresponde à cidade "Boston" e, em seguida, a operação de soma deve ser realizada sobre esses 5 valores. Agora vamos usar a seguinte fórmula para obter a soma
Use a fórmula:
= SUMPRODUCT (GRANDE ((City = "Boston") * (quantidade), {1, 2, 3, 4, 5}))
Explicação:
- O "Boston" da cidade corresponde à faixa da cidade mencionada. Isso retorna uma matriz de verdadeiro e falso.
- A função LARGE retorna os 5 principais valores numéricos do intervalo de quantidade e retorna a matriz para a função SUMPRODUCT.
= SUMPRODUTO {193, 149, 138, 134, 123}
- A função SUMPRODUCT obtém uma matriz dos 5 valores principais, com uma matriz dos 5 números principais retornando a SOMA desses números.
Aqui, o intervalo de cidade e quantidade é 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 é 737. A soma dos valores 193 + 149 + 138 + 134 + 123 = 737.
Você pode verificar os valores acima no conjunto de dados usando a opção de filtro do Excel. Aplique o filtro ao cabeçalho Cidade e quantidade e clique no botão de seta no cabeçalho da cidade que aparece. Siga as etapas conforme mostrado abaixo.
Passos:
- Selecione a célula de cabeçalho City. Aplicar filtro usando atalho Ctrl + Shift + L
- Clique na seta que aparece como uma opção de filtro.
- Selecione a opção (Selecionar tudo).
- Selecione apenas a cidade de Boston.
- Selecione o cabeçalho da quantidade agora.
- Classifique a lista do maior para o menor e você poderá ver todos os 5 principais valores que calculamos usando a fórmula.
Como você pode ver no gif acima, todos os 5 valores que correspondem aos critérios fornecidos. Isso também significa que a fórmula funciona bem para obter a contagem desses valores
GRANDES Números N
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 (GRANDE ((City = "Boston") * (quantidade), ROW (INDIRETO ("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 temos a soma dos 10 primeiros números, que resulta em 1147.
Soma dos valores de N inferiores com critérios
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:
{= SOMA (PEQUENO (SE (Cidade = "Boston", quantidade), {1, 2, 3, 4, 5}))}
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: D50.
Aqui, a faixa de cidade e quantidade é fornecida usando a ferramenta Excel de faixa nomeada.
Em primeiro lugar, precisamos encontrar os cinco valores inferiores usando a função SMALL que corresponde aos critérios e, em seguida, a operação de soma deve ser realizada sobre esses 5 valores. Agora vamos usar a seguinte fórmula para obter a soma
Use a fórmula:
{= SOMA (PEQUENO (SE (Cidade = "Boston", quantidade), {1, 2, 3, 4, 5}))}
NÃO use chaves manualmente. Cintas encaracoladas aplicadas usando o Ctrl + Shift + Enter no lugar de apenas Digitar.
Explicação:
- A função SMALL com a função IF retorna os 5 valores numéricos inferiores que correspondem à cidade "Boston" e retorna a matriz para a função SUM.
= SUM ({23, 27, 28, 28, 30}))
- A função SUM obtém a matriz dos 5 valores inferiores, que possui uma matriz dos 5 números inferiores e retorna a soma dos números usados com CTRL + SHIFT + ENTER.
Aqui, o intervalo de cidade e quantidade é fornecido como o intervalo nomeado. pressione Ctrl + Shift + Enter para obter a soma dos 5 últimos números, pois esta é uma fórmula de matriz.
Como você pode ver no instantâneo acima, a soma é 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:
{ = SOMA (PEQUENO (SE (Cidade = "Boston", quantidade), LINHA (INDIRETO ("1:10")))) }
NÃO use as chaves manualmente. Use Ctrl + Shift + Enter em vez de usar Enter.
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 155.
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 a soma dos 5 valores principais ou dos 5 valores inferiores com critérios 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.
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-nos 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
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