Как найти следующее наименьшее / наибольшее значение без сортировки?

Question

У меня есть таблица, из которой мне нужно извлечь следующее наибольшее и / или наименьшее значение по сравнению со значением в отдельной таблице. Однако мне также необходимо разрешить сортировку первой таблицы по любым критериям, не влияя на точность поиска.

Я искал и читал о различных методах использования VLOOKUP, LOOKUP, HLOOKUP и MATCH для поиска следующих по величине / следующих по наименьшему значений, но все, что я нахожу, требует сортировки исходного столбца по возрастанию / убыванию в зависимости от того, Вы хотите следующее наименьшее / наибольшее значение. Это нарушает функциональность, когда я хочу, чтобы оба были доступны одновременно, или я хочу иметь возможность сортировать таблицу, не нарушая формулу.

Есть ли способ сделать нечувствительный к сортировке поиск следующих по величине / следующих наименьших значений в Excel?

Предпочтительные решения будут использовать исключительно собственные функции Excel, поскольку в настоящее время я не очень хорошо знаком с VBScript, и установка сторонних инструментов в настоящее время невозможна. Решения также должны быть совместимы с Excel 2010 и 2013.

Answer 1

Предлагаемый результат

Использование IF и SMALL в формуле ARRAY

Если результат, показанный на рисунке, является тем, что вы ищете, формула для нахождения следующего большего размера выглядит следующим образом:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

объяснение

• Relative[Value]>[@Value] возвращает массив TRUE или FALSE
• IF(Relative[Value]>[@Value];Relative[Value];99999999999) затем возвращает значения, которые больше из относительной таблицы, а для тех, которые не являются, он возвращает некоторые из огромного значения вне диапазона. Выберите тот, который никогда не встречается в ваших данных. В качестве альтернативы вы можете использовать значение 0 для FALSES или работать со значениями ошибок.
• Затем мы просто используем функцию SMALL с аргументом k=1 чтобы найти первое Smallest из значений Bigger.
• Это формула массива, поэтому введите формулу, нажав CTRL+SHIFT+ENTER.

Ссылки:

• МАЛЕНЬКАЯ функция
• ExcelJet nth наименьшее значение с критериями
• MS Guidelines и примеры формул массива
• Серия ExcelIsFun Youtube: Ctrl + Shift + Enter: формулы массива Excel

Answer 2

Используйте пустой вспомогательный (злой) столбец, скопируйте и вставьте его до конца. = IF (B3> NOW (), B3, "") я назову это столбцом T. Затем в поле "next up" введите. ** = MIN (Т1:T1000)

Как функция что-то вроде этого:

    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

Answer 3

Если ваши номера уникальны, это будет работать:

Следующее самое маленькое:

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

Следующий по величине:

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

Если это не так, то вы можете выполнять более сложные манипуляции, используя формулы массива или вспомогательные столбцы. Вам также необходимо решить, как вы хотите обрабатывать повторяющиеся числа (возвращать одинаковые или разные значения), для которых вы можете переключиться на использование LARGE и изменение порядка сортировки RANK .

Тем не менее, это должно дать вам отправную точку.