Como encontro o menor / maior valor seguinte sem ordenar?

Question

Eu tenho uma tabela da qual preciso extrair o próximo maior e / ou menor valor, em relação a um valor em uma tabela separada. No entanto, também preciso permitir que a primeira tabela seja classificável por qualquer critério, sem afetar a precisão da pesquisa.

Eu pesquisei e li vários métodos para usar o VLOOKUP, o LOOKUP, o HLOOKUP e o MATCH para encontrar os valores next-largest / next-smallest, mas tudo que eu encontrar parece exigir que a coluna de origem seja classificada em ascendente / descendente dependendo se você quer o próximo valor menor / maior. Isso interrompe a funcionalidade quando eu quero ter os dois disponíveis de uma só vez, ou eu quero poder classificar a tabela sem quebrar a fórmula.

Existe uma maneira de fazer uma pesquisa insensível a classificação para os valores next-largest / next-smallest no Excel?

As soluções preferenciais usariam exclusivamente funções nativas do Excel, já que atualmente não estou muito familiarizado com o VBScript, e a instalação de ferramentas de terceiros não é uma opção no momento. As soluções também devem ser compatíveis com o Excel 2010 e 2013.

    

Answer 1

Resultado proposto

UsandoIFeSMALLnafórmulaARRAY

Seoresultadomostradonaimagemforoquevocêestáprocurando,afórmulaparaencontraropróximomaiorseparececomisso:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

Explicação

• Relative[Value]>[@Value]retornaumamatrizdeTRUEouFALSE
• IF(Relative[Value]>[@Value];Relative[Value];99999999999)retornaosvaloresquesãomaioresdatabelarelativae,paraaquelesquenãosão,retornaalgumvalorenormeforadafaixa.Escolhaumquenuncaocorreránaturalmenteemseusdados.Comoalternativa,vocêpodeusarovalor0paraosFALSESoupodetrabalharcomvaloresdeerro.
• Emseguida,usamosapenasafunçãoSMALLcomoargumentok=1paraencontraroprimeiroMenordosvaloresMaiores.
• Éumafórmuladematriz,portanto,insiraafórmulacomCTRL+SHIFT+ENTER.

Links:

• função SMALL
• ExcelJet é o menor valor com critérios
• MS Guidelines e exemplos de matriz fórmulas
• Série ExcelIsFun do Youtube: Ctrl + Shift + Enter: Fórmulas da Matriz do Excel

Answer 2

Se seus números forem exclusivos, isso funcionará:

Próximo menor:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)+1)

Próximo maior:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)-1)

Se não estiverem, você pode fazer manipulações mais complexas usando fórmulas de matriz ou colunas auxiliares. Você também precisará decidir como deseja manipular números duplicados (retornar o mesmo valor ou valor diferente), para os quais você pode alternar para usar LARGE e alterar a ordem de classificação de RANK .

Ainda assim, isso deve lhe dar um ponto de partida.

    

Answer 3

Use uma coluna de ajudante em branco (mal) e copie e cole tudo até o fim. = IF (B3 > NOW (), B3, "") Eu chamarei de coluna T. Então, no campo "next up", digite. ** = MIN (T1: T1000)

Em função de algo assim:

    Function Soonest(scolumn As String) As Date

'
'
Dim a, b
Dim test(20000) As Date
Dim Min As Date

b = 0

   For a = 1 To 20000
    If (IsEmpty(Range(scolumn & a))) Then
     GoTo SkipMe
    End If

     If (Range(scolumn & a).Value - Now() > 0) Then
     b = b + 1
     test(b) = Range(scolumn & a).Value
     End If
SkipMe:
  Next a

  If b = 0 Then
   Min = "None"
  GoTo NoneFound
  End If
  Min = test(1)
  For c = 1 To b
   If test(c) < Min Then Min = test(c)
  Next c
NoneFound:

Soonest = Min
End Function