Нахождение следующего более высокого значения в столбце

Question

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

Например:

Column A    Column B   

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

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

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

Answer 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)}

Объяснение:

Функция SMALL(array,n) возвращает 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 для первой формулы - эта формула также возвращает ноль, если нет более высоких значений.)

Answer 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 .