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

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

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

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

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

Решение

Как происходит поиск

Примечание

=ИНДЕКС(A4:A15;ПОИСКПОЗ( МАКС(ЕСЛИ(A4:A15<=C4;A4:A15;""));
$A$4:$A$15;0);1)

ищется наибольшее значение, которое меньше, чем искомое значение (ближайшее сверху)

Если заданное значение меньше минимального, то выдается ошибка #Н/Д

=ИНДЕКС(A4:A15;ПОИСКПОЗ(
МИН(ЕСЛИ(A4:A15>=C4;A4:A15;""));
$A$4:$A$15;0);1)

ищется наименьшее значение, которое больше, чем искомое значение (ближайшее снизу)

Если заданное значение больше максимального, то выдается ошибка #Н/Д

=ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ABS(A4:A15-C4));
ABS(A4:A15-C4);0))

ищется ближайшее к критерию число

если ближайшее снизу и ближайшее сверху отстоят на одинаковое расстояние от критерия, то берется ближайшее число, расположенное первым в списке (например, ближайшее к 5 в списке 2; 4; 6; 8 будет 4, а в списке 2; 6; 4; 8 - будет 6), т.е. предсказать будет ли число ближайшим сверху или снизу невозможно

=МАКС(ЕСЛИ(МИН(ABS(A4:A15-C4))=ABS(A4:A15-C4);A4:A15;МИН(A4:A15)))

ищется ближайшее к критерию число

если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше

=МИН(ЕСЛИ(МИН(ABS(A4:A15-C4))=ABS(A4:A15-C4);A4:A15;МАКС(A4:A15)))

ищется ближайшее к критерию число

если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше

СОВЕТ:
Для пошагового просмотра хода вычислений формул используйте клавишу F9.

При поиске ближайшего с дополнительным условием см. статью Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список.

 

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

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

Комментарии

Иван (не проверено)

Спасибо за теорию. Пробую формулу для ближайшего к критерию числа, выдаёт ошибку #Н/Д, хотя по выделению формулы и нажатию F9 прекрасно показывает желаемый результат. Как можно решить эту проблему? И ещё вопрос: можно сделать так, чтобы если ближайшее снизу (меньшее) и ближайшее сверху (большее) отстоят от заданного числа (критерия) отстоят на одинаковое расстояние, то выбиралось бы ближайшее сверху (большее)? Ещё жаль, что формулы работают только в диапазоне 0-255 значений массива... Заранее спасибо!

Creator

Результат выполнения формулы в ячейке и пошаговый просмотр результата с помощью клавиши F9 не могут давать различающиеся результаты. Возможно в качестве значений списка у Вас заданы значения с помощью, например, функции СЛУЧМЕЖДУ(), когда значения изменяются при каждом пересчете листа или использованы формулы массива.

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

Проверил работоспособность формул для 450 значений, все в порядке, проверьте свои формулы.

Querrero

Прежде всего, спасибо за замечательный сайт. Теперь к делу. Вторая формула выдаёт ошибку #ЗНАЧ!, а третья формула выдают ошибку #Н/Д, хотя при выделении формул в ячейке и нажатии F9 всё работает как надо, и показывается ожидаемый результат. В ячейке показывается результат только в случае со первой формулой, но совсем не тот, что по F9, по F9 - правильный, а тот, что в ячейке, мало того, что неправильный, ещё и не меняется при изменении значения в ячейке критерия. Какой-то тут похоже глючок, или я что-то делаю неправильно. Все компоненты при проверке копировании в отдельную ячейку работают, соответствующая ошибка возникает при копировании =ПОИСКПОЗ("выражение"), что, естественно, влияет на =ИНДЕКС("выражение"). Прошу помочь разобраться. Ещё хотелось бы сделать как в третьей формуле, только чтобы если от нижнего (меньшего) и верхнего (большего) до заданного числа (критерия) одинаковое расстояние, то выбиралось бы верхнее (большее). Заранее спасибо!
PS. Выяснил, что криво работает уже мин(ABS(диапазон-число)) - показывает в ячейке неправильное, а по F9 - правильное, наверно начинать надо с ABS(). Кстати, файл примера недоступен на сайте.

Creator

см. выше ответ на коммент Ивану. Если не помогло, то присылайте на creator@excel2.ru свой файл

Проверьте, ввели ли Вы формулы как формулы массива, т.е. нажав CTRL+SHIFT+ENTER (вместо ENTER)

Яндекс.Метрика