☰

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

tag-icon • tag-icon microsoft-excel-2010 worksheet-function microsoft-excel-2013
Как найти следующее наименьшее/наибольшее значение без сортировки?

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

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

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

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

решение1

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

введите описание изображения здесь

Использование ЕСЛИ и НАИМЕНЬШИЙ в формуле МАССИВ

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

Объяснение

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

Ссылки:

решение2

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

Следующий наименьший:

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

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

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

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

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

решение3

Используйте пустой вспомогательный (злой) столбец, скопируйте и вставьте это до конца. =IF(B3>NOW(),B3,"") Я назову его столбцом T. Затем в поле «следующий» введите. **=MIN(T1: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

Связанный контент