☰

¿Cómo encuentro el siguiente valor más pequeño/más grande sin ordenar?

tag-icon • tag-icon microsoft-excel-2010 worksheet-function microsoft-excel-2013
¿Cómo encuentro el siguiente valor más pequeño/más grande sin ordenar?

Tengo una tabla de la que necesito extraer el siguiente valor más grande y/o más pequeño, en relación con un valor en una tabla separada. Sin embargo, también necesito permitir que la primera tabla se pueda ordenar según cualquier criterio, sin afectar la precisión de la búsqueda.

He buscado y leído sobre varios métodos de uso de BUSCARV, BUSCAR, BUSCARH y COINCIDIR para encontrar los siguientes valores más grandes/siguientes más pequeños, pero todo lo que encuentro parece requerir que la columna de origen se ordene de forma ascendente/descendente dependiendo de si desea el siguiente valor más pequeño/más grande. Esto interrumpe la funcionalidad cuando quiero tener ambos disponibles a la vez o quiero poder ordenar la tabla sin romper la fórmula.

¿Hay alguna manera de realizar una búsqueda sin distinción de ordenación para los siguientes valores más grandes/siguientes más pequeños en Excel?

Las soluciones preferidas usarían exclusivamente funciones nativas de Excel, ya que actualmente no estoy muy familiarizado con VBScript y la instalación de herramientas de terceros no es una opción en este momento. Las soluciones también deben ser compatibles con Excel 2010.y2013.

Respuesta1

Resultado propuesto

ingrese la descripción de la imagen aquí

Usando IF y SMALL en la fórmula ARRAY

Si el resultado que se muestra en la imagen es lo que está buscando, la fórmula para encontrar el siguiente más grande se ve así:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

Explicación

  • Relative[Value]>[@Value]devuelve una matriz de TRUEoFALSE
  • IF(Relative[Value]>[@Value];Relative[Value];99999999999)luego devuelve los valores que son mayores de la tabla relativa y, para aquellos que no lo son, devuelve algún valor enorme fuera de rango. Elija uno que nunca aparezca naturalmente en sus datos. Alternativamente, puede usar 0valores para FALSES o puede trabajar con valores de error.
  • Luego simplemente usamos la SMALLfunción con argumento k=1para encontrar el primer valor más pequeño de los más grandes.
  • Es una fórmula matricial, así que ingrese la fórmula con CTRL+MAYÚS+ENTRAR.

Enlaces:

Respuesta2

Si sus números son únicos, esto funcionará:

Siguiente más pequeño:

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

Siguiente más grande:

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

Si no es así, puede realizar algunas manipulaciones más complejas utilizando fórmulas de matriz o columnas auxiliares. También deberá decidir cómo desea manejar los números duplicados (devolver el mismo valor o un valor diferente), para lo cual puede LARGEcambiar el orden de clasificación de RANK.

Aún así, esto debería darle un punto de partida.

Respuesta3

Utilice una columna auxiliar (malvada) en blanco y cópiela y péguela hasta el fondo. =IF(B3>NOW(),B3,"") La llamaré columna T. Luego, en el campo "siguiente", ingrese. **=MÍN(T1:T1000)

Como función algo como esto:

    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

información relacionada