No Excel, se você tem uma lista de valores e não consegue encontrar uma correspondência exata, então você precisa encontrar o Mais próximo maior ou Menor mais próximo valor. Por exemplo, se você estiver fazendo uma comparação de números e não encontrar uma correspondência exata no intervalo, poderá usar o próximo número maior ou menor e prosseguir.
Podemos usar uma combinação de Countif, Grande e Pequena fórmula para obter nossa saída.
Countif é usado para contar o número de vezes que dados específicos são repetidos em um intervalo de células.
Sintaxe = CONT.SE (intervalo, critérios)
Grande: Retorna o k-ésimo maior valor em um conjunto de dados.
Sintaxe = LARGE (matriz, k)
Pequena: Retorna o k-ésimo menor valor em um conjunto de dados.
Sintaxe = SMALL (array, k)
Vamos entender o que é o número maior e menor mais próximo
Temos uma lista com números aleatórios 1,2,3,5,8,10 e precisamos de uma fórmula para avaliar o seguinte:
- Se k estiver na lista, retorna k
- Se k não estiver na lista, retorna o próximo valor mais alto. Por exemplo, se k = 7, retorna 8 (no caso do número maior mais próximo)
- Se k não estiver na lista, retorna o próximo valor menor. Por exemplo, se k = 7, retorne 5
(no caso do número menor mais próximo)
Vejamos um exemplo, temos alguns números aleatórios na coluna A e precisamos encontrar o valor neste intervalo, que é o Menor mais próximo ou Mais próximo maior para a célula B2 que contém 46. Este número não está disponível em nossa linha. Veja a imagem abaixo
Vamos ver como calculamos o Número Maior Mais Próximo primeiro:
Podemos verificar manualmente o próximo maior valor para 46, que é 54.
Agora vamos tentar obter o mesmo resultado usando a fórmula -
- Usaremos uma combinação de GRANDE E CONT.SE funções para gerar o maior número mais próximo.
- Critério é maior que ‘>’ assine com valor de pesquisa, ou seja, célula B2
- O sinal de maior que combinado com o valor de pesquisa retornará 5 valores, ou seja, 54, 57, 61, 74, 83 que são maiores que 46.
- Quando fundimos as funções Grande e Contagem, obteremos 54. Consulte a fórmula nas células G2 e G3 no instantâneo abaixo.
PS: B2 em Countifformula é o valor de pesquisa, ou seja, 46
- Na célula C2, a fórmula usada para encontrar Mais próximo maior número é = GRANDE ($ A $ 2: $ A $ 8, CONT.SE ($ A $ 2: $ A $ 8, ">" & B2))
Agora vamos ver como calculamos o Número Menor Mais Próximo -
Podemos verificar manualmente o próximo valor menor para 46, que é 28.
Vamos obter o mesmo resultado usando a fórmula -
- Usaremos uma combinação de PEQUENO E CONT.SEfunções para gerar o menor número mais próximo.
- Critério é menos que ‘<’ com valor de pesquisa, ou seja, célula B2
- Sinal menor que combinado com o valor de pesquisa retornará 2 valores, ou seja, 16 e 28, que são menores que 46.
- Quando mesclamos as funções Small e Countiff, obtemos 28. Consulte a fórmula nas células G5 e G6 no instantâneo abaixo.
Veja a captura de tela abaixo com fórmula:
Observação: a célula B2 em CountifFormula é o valor de pesquisa, ou seja, 46
- Na célula D2, a fórmula usada para encontrar Menor mais próximo número é = PEQUENO ($ A $ 2: $ A $ 8, CONT.SE ($ A $ 2: $ A $ 8, "<" & B2))
É assim que podemos retornar o número maior mais próximo e o número menor do armário.
