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

Мы можем использовать комбинацию формул Countif, Large и Small, чтобы получить результат.

Countif используется для подсчета количества повторений определенных данных в диапазоне ячеек.

Синтаксис = СЧЁТЕСЛИ (диапазон, критерии)

Большой: возвращает k-е наибольшее значение в наборе данных.

Синтаксис = LARGE (массив, k)

Маленький: возвращает k-е наименьшее значение в наборе данных.

Синтаксис = МАЛЕНЬКИЙ (массив, k)

Давайте разберемся, что такое ближайшее большее и меньшее число

У нас есть список со случайными числами 1,2,3,5,8,10, и нам нужна формула для вычисления следующего:

  • Если k есть в списке, вернуть k — Если k нет в списке, вернуть следующее более высокое значение. Например, если k = 7, вернуть 8 (в случае ближайшего большего числа)

  • Если k нет в списке, вернуть следующее меньшее значение. Например, если k = 7, вернуть 5 (в случае ближайшего меньшего числа)

Давайте возьмем пример, у нас есть несколько случайных чисел в столбце A, и нам нужно найти значение в этом диапазоне, который является ближайшим меньшим или ближайшим большим к ячейке B2, содержащей 46. Это число недоступно в нашем диапазоне. Смотрите скриншот ниже

img1

Давайте сначала посмотрим, как мы вычисляем ближайшее большее число:

Мы можем вручную проверить следующее наибольшее значение после 46, то есть 54.

Теперь давайте попробуем получить тот же результат, используя формулу. Мы будем использовать комбинацию функций НАИБОЛЬШИЙ и СЧЁТЕСЛИ *, чтобы сгенерировать ближайшее большее число.

Критерий больше, чем знак «>» со значением поиска, т.е. ячейка B2. Знак «больше» в сочетании со значением поиска вернет 5 значений, т.е. 54, 57, 61, 74, 83, которые больше 46.

  • Когда мы объединяем функции Big & Countiffunctions, мы получим 54. См. Формулу в ячейках G2 и G3 на снимке ниже.

img2

PS: B2 в Countifformula — это значение поиска, т.е. 46 В ячейке C2 формула, используемая для поиска ближайшего большего числа *, имеет вид

= БОЛЬШОЙ ($ A $ 2: $ A $ 8, СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, «>» & B2))

img3

Теперь давайте посмотрим, как мы вычисляем ближайшее меньшее число — мы можем вручную проверить следующее меньшее значение до 46, которое составляет 28.

Получим тот же результат, используя формулу. Мы будем использовать комбинацию функций МАЛЕНЬКИЙ и СЧЁТЕСЛИ *, чтобы сгенерировать ближайшее меньшее число.

Критерий меньше, чем «<» со значением поиска, т.е. ячейка B2 Знак «меньше» в сочетании со значением поиска вернет 2 значения, то есть 16 и 28, которые меньше 46.

  • Когда мы объединяем Small & Countiffunctions, мы получаем 28. См. Формулу в ячейках G5 и G6 на снимке ниже.

Смотрите ниже снимок экрана с формулой:

img4

Примечание: ячейка B2 в CountifFormula — это значение поиска, т.е. 46 В ячейке D2 формула, используемая для поиска ближайшего меньшего * числа, имеет вид

= МАЛЕНЬКИЙ ($ A $ 2: $ A $ 8, СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, «<» & B2))

img5

Таким образом мы можем вернуть ближайшее большее число и закрыть меньшее число.