☰

Поиск следующего большего значения в столбце

tag-icon • tag-icon microsoft-excel worksheet-function lookup
Поиск следующего большего значения в столбце

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

Например:

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

Итак, для значения 5в столбце Bя хочу вернуть 7из столбца A.

Думаю, мне, вероятно, понадобится какая-то функция поиска/сопоставления индексов, но я не смог написать формулу самостоятельно.

решение1

Сортировано

Самая простая формула применима к случаю, когда столбец Aотсортирован по возрастанию:

Скриншот рабочего листа

Введите следующую формулу C1и нажмите ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:

=INDEX(A:A,1+MATCH(B1,A:A,1))

Объяснение:

Третий 1аргумент MATCH()означает, что он находит наибольшее значение, которое меньше или равно первому аргументу. Добавление 1к этому индексу приводит к индексу следующего большего числа. INDEX()Затем функция извлекает число.

Обратите внимание, что я добавил дополнительное значение в конце столбца A. Это для особого случая, когда нет следующего более высокого значения.

Несортированный

Для случая, когда столбец Aне отсортирован (также работает, если отсортирован), формула немного сложнее:

Массив, введите ( Ctrl+ Shift+ Enter) следующую формулу C1и скопируйте-вставьте/заполните оставшуюся часть столбца таблицы (не забудьте удалить и {) }:

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

Объяснение:

Функция возвращает n-ное наименьшее значение массива,SMALL(array,n)игнорирование булевых значений. Поскольку значение по умолчанию для третьего аргумента функции IF()равно , проверяются FALSEтолько значения, превышающие значение в столбце , в результате чего получается следующее большее значение.B

Обратите внимание, что специальное конечное значение для столбца Aне требуется, так как #NUM!результатом будет ошибка, если в столбце нет значений, Aбольших значения в столбце B.

Наконец, как указал Авантюрин, существует альтернативная, похожая формула, которая работает независимо от сортировки (но с важной оговоркой).

Для Excel 2016+:

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

Это работает, поскольку MINIFS()функция отфильтровывает значения, не соответствующие критериям, перед извлечением минимального значения.

Для более ранних версий Excel:

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

Это работает по той же причине, что и SMALL()функция — она игнорирует логические значения, генерируемые функцией IF().

Предостережение:

Обе формулы =MINIFS()и {=MIN(IF())}не будут работать правильно, если ноль может быть правильным следующим более высоким значением, поскольку ноль также возвращается, когда нетявляетсянет следующего более высокого значения. (Это та же причина, по которой добавляется дополнительное значение в конце столбца Aдля первой формулы — эта формула также возвращает ноль, если нет более высоких значений.)

решение2

Например, можно использовать функцию массива {=MIN(IF(A1:A6 > B1; A1:A6))}или {=MIN(IF(A1:A6 > B1; A1:A6; 1000))}(с 1000 в качестве резервного значения).

Он берет минимум из всех значений из столбца A, которые больше значения из текущей ячейки из столбца B (здесь B1). Поэтому ни один из столбцов не должен быть отсортирован.

В Excel >= 2016 вы также можете использовать эту MINIFSфункцию.

Обратите внимание, что функции массива необходимо вставлять нажатием Ctrl+Shift+Enter.

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