Neste artigo, aprenderemos como Encontrar o enésimo menor com os critérios e o enésimo maior com os critérios da tabela fornecida no Excel.
Cenário:
Em palavras simples, ao trabalhar com números em números de dados, às vezes é dada uma condição, ou seja, quando precisamos procurar o quinto maior valor fornecido. Você pode executar a solução para este problema facilmente usando as funções do Excel, conforme explicado a seguir.
Enésimo maior com critérios
Como resolver o problema?
Para este artigo, seremos obrigados a usar a função LARGE. Agora faremos uma fórmula com essas funções. Aqui, temos uma tabela e precisamos encontrar o enésimo maior valor no intervalo com determinados critérios.
Fórmula genérica:
{ = GRANDE (IF (c_range = "valor", intervalo), n ) }
c_range : intervalo de critérios
valor : valor de correspondência de critérios
faixa : array de resultados
n : enésimo maior
Observação : Não coloque chaves manualmente. Esta é uma fórmula de matriz, deve usar Ctrl + Shift + Enter no lugar de apenas Enter onde retorna # num! erro.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui temos detalhes do pedido com data do pedido, região e preço do pedido.
Precisamos descobrir o quinto maior preço de pedido da região leste. Aqui, o intervalo é fornecido usando a ferramenta Excel de intervalo nomeado.
região (C3: C16)
preço (D3: D16)
Em primeiro lugar, precisamos encontrar os quintos maiores valores 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:
{ = GRANDE (IF (região = G5, preço), F5)}
Explicação:
- A função IF verifica os critérios que correspondem a todos os valores na região com o valor Leste fornecido na célula G5 e retorna os valores VERDADEIROS correspondentes da faixa de preço.
= GRANDE ({58,41; 303,63; FALSO; 153,34; 82,84; FALSO; 520,01; FALSO; 177; FALSO; FALSO; 57,97; 97,72; FALSO}), F5)
- A função LARGE pega a matriz que tem toda a faixa de preço conforme mostrado acima e retorna o quinto maior valor da matriz, conforme mostrado no instantâneo abaixo.
Você pode visualizar a fórmula na caixa de fórmula como mostrado acima. Use Ctrl + Shift + Enter para obter o resultado.
Como você pode ver, a fórmula da matriz retorna o quinto maior preço $ 97,72 tendo a região Leste.
Você também pode alimentar array como array, critérios entre aspas e valor n diretamente para a função, em vez de usar a referência de célula ou intervalo nomeado.
Use a fórmula:
{ = GRANDE (IF (C3: C16 = "Oeste", D3: D16), 3)}
Usar Ctrl + Shift + Enter
Você pode ver que a fórmula funciona bem onde encontrar o enésimo maior valor tendo os critérios na tabela.
Enésimo mais baixo com critérios
Como resolver o problema?
Para este artigo, seremos obrigados a usar a função SMALL. Agora faremos uma fórmula com essas funções. Aqui, temos uma tabela e precisamos encontrar o enésimo menor valor no intervalo com os critérios dados.
Fórmula genérica:
{ = PEQUENO (SE (c_range = "valor", intervalo), n ) }
c_range : intervalo de critérios
valor : valor de correspondência de critérios
faixa : array de resultados
n : enésimo maior
Observação : Não coloque chaves manualmente. Esta é uma fórmula de matriz, deve usar Ctrl + Shift + Enter no lugar de apenas Enter onde retorna # num! erro.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui temos detalhes do pedido com data do pedido, região e preço do pedido.
Precisamos descobrir o enésimo menor preço de pedido da região leste. Aqui, o intervalo é fornecido usando a ferramenta Excel de intervalo nomeado.
região (C3: C16)
preço (D3: D16)
Em primeiro lugar, precisamos encontrar o quinto menor ou menor valor usando a função SMALL e, em seguida, a operação de pesquisa ser realizada sobre os valores. Agora vamos usar a seguinte fórmula para obter a soma
Use a fórmula:
{ = PEQUENO (SE (região = G5, preço), F5)}
Explicação:
- A função IF verifica os critérios que correspondem a todos os valores na região com o valor Leste fornecido na célula G5 e retorna os valores VERDADEIROS correspondentes da faixa de preço.
= GRANDE ({58,41; 303,63; FALSO; 153,34; 82,84; FALSO; 520,01; FALSO; 177; FALSO; FALSO; 57,97; 97,72; FALSO}), F5)
- A função SMALL pega o array que tem toda a faixa de preço conforme mostrado acima e retorna o quinto maior valor do array, conforme mostrado no instantâneo abaixo.
Você pode ver a fórmula na caixa de fórmula como mostrado acima. Usar Ctrl + Shift + Enter para obter o resultado.
Como você pode ver, a fórmula da matriz retorna o quinto menor preço $ 153,34 tendo a região Leste.
Você também pode alimentar array como array, critérios entre aspas e valor n diretamente para a função, em vez de usar a referência de célula ou intervalo nomeado.
Use a fórmula:
{ = PEQUENO (IF (C3: C16 = "oeste", D3: D16), 3)}
Usar Ctrl + Shift + Enter
Você pode ver que a fórmula funciona bem onde você encontra o enésimo maior valor com critérios na tabela.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números.
- Não coloque colchetes manualmente. Esta é uma fórmula de matriz, deve usar Ctrl + Shift + Enter no lugar de apenas Enter onde retorna # num! erro.
- O valor de não pode ser maior que o número do valor do critério ou retorna #NUM! Erro.
- Os critérios IF não correspondem à fórmula devolve erro.
- A matriz do argumento deve ter o mesmo comprimento do que a função.
Espero que este artigo sobre como Encontrar o enésimo maior com critérios e o enésimo menor com critérios no Excel seja explicativo. Explore mais artigos sobre fórmulas de pesquisa no Excel 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-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 : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP 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 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.
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.