☰

Como encontro o próximo menor/maior valor sem classificar?

tag-icon • tag-icon microsoft-excel-2010 worksheet-function microsoft-excel-2013
Como encontro o próximo menor/maior valor sem classificar?

Eu tenho uma tabela da qual preciso extrair o próximo valor maior e/ou menor, relativo 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.

Pesquisei e li vários métodos de uso de VLOOKUP, LOOKUP, HLOOKUP e MATCH para encontrar os próximos valores maiores/próximos menores, mas tudo que encontro parece exigir que a coluna de origem seja classificada em ordem crescente/decrescente dependendo se você deseja o próximo menor/maior valor. Isso interrompe a funcionalidade quando desejo ter os dois disponíveis ao mesmo tempo ou quero classificar a tabela sem quebrar a fórmula.

Existe uma maneira de fazer uma pesquisa insensível à classificação dos próximos valores maiores/menores no Excel?

As soluções preferidas usariam exclusivamente funções nativas do Excel, já que atualmente não estou muito familiarizado com 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 Excel 2010e2013.

Responder1

Resultado proposto

insira a descrição da imagem aqui

Usando IF e SMALL na fórmula ARRAY

Se o resultado mostrado na imagem for o que você está procurando, a fórmula para encontrar o próximo maior será assim:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

Explicação

  • Relative[Value]>[@Value]retorna uma matriz de TRUEouFALSE
  • IF(Relative[Value]>[@Value];Relative[Value];99999999999)em seguida, retorna os valores maiores da tabela relativa e, para aqueles que não são, retorna algum valor enorme fora do intervalo. Escolha um que nunca ocorrerá naturalmente em seus dados. Alternativamente, você pode usar 0valor para FALSES ou pode trabalhar com valores de erro.
  • Em seguida, apenas usamos a SMALLfunção com argumento k=1para encontrar o primeiro menor dos valores maiores.
  • É uma fórmula de matriz, então insira a fórmula com CTRL+SHIFT+ENTER.

Links:

Responder2

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)

Caso contrário, você poderá fazer algumas manipulações mais complexas usando fórmulas de matriz ou colunas auxiliares. Você também precisará decidir como deseja lidar com números duplicados (retornar o mesmo valor ou um valor diferente), para os quais você pode usar LARGEe alterar a ordem de classificação de RANK.

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

Responder3

Use uma coluna auxiliar em branco (mal) e copie e cole até o fim. =IF(B3>AGORA(),B3,"") Vou chamá-la de coluna T. Em seguida, no campo "próximo", digite. **=MIN(T1:T1000)

Como função 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

informação relacionada