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

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


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

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

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

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

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

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

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

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

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

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

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

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


Комментарии
Только для авторизованных пользователей

Аноним, 25 марта 2019 г.
Формула для числа 250 не работает
Михаил, 25 марта 2019 г.
Как именно формула? Какое значение у Вас она возвращает?
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved