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

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

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

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

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

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

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (4 оценок)