Поиск ЧИСЛА ближайшего к заданному. Сортированный список в EXCEL

Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существуют специальные функции, например, ВПР() , но они работают только если исходный список сортирован по возрастанию или убыванию.

Пусть имеется сортированный по возрастанию список чисел (в диапазоне A4:A15 ) (см. Файл примера ).

В качестве критерия для поиска используем любое число (не обязательно из списка), введем его в ячейку С4 . Найдем значение из диапазона, ближайшее к критерию.

Для решения этой задачи в EXCEL существует функция ВПР() : = ВПР(C4;A4:A15;1;ИСТИНА)

Тот же результат можно получить с помощью функции ПРОСМОТР() и ПОИСКПОЗ() с типом сопоставления =1: = ПРОСМОТР(C4;A4:A15;A4:A12) = ИНДЕКС(A4:A15;ПОИСКПОЗ(C4;A4:A15;1))

Эти формулы работают, только если исходный список сортирован по возрастанию (наш случай).

При использовании этих формул следует помнить следующее:

• в результате поиска будет найдено наибольшее значение, которое меньше или равно, чем искомое значение (ближайшее снизу);
• как следствие предыдущего пункта: если заданное значение меньше минимального в списке поиска, то выдается ошибка;
• если столбец не отсортирован по возрастанию, то результат непредсказуем.

Чтобы найти наименьшее значение, которое больше или равно, чем искомое значение (ближайшее сверху) можно использовать функцию ПОИСКПОЗ() с типом сопоставления =-1, но для этого требуется чтобы список был отсортирован по убыванию.

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

Список с пустыми ячейками

Если список сортирован и содержит пустые ячейки, то ВПР() будет работать без проблем и найдет ближайшее значение, которое МЕНЬШЕ или равно заданному (4-й критерий равен ИСТИНА или опущен).

В файле примера также приведена формула, которая ищет ближайшее значение, которое БОЛЬШЕ или равно заданному.