Поиск ЧИСЛА ближайшего к заданному. Несортированный список в 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. Несортированный список .

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

Аноним, 1 декабря 2019 г.
Спасибо большое, всё получилось!
Аноним, 15 мая 2020 г.
Как только список сортируешь, формула тут же начинает криво работать. Почему?
Михаил, 18 мая 2020 г.
Все должно работать в сортированном и несортированном списке. я в файле примера посортировал список, все работает нормально. можете прислать в группу https://vk.com/excel2ru свой пример, где формула не работает? Вообще, для сортированного списка формула проще, используйте ее https://excel2.ru/articles/poisk-chisla-blizhayshego-k-zadannomu-sortirovannyy-spisok-v-ms-excel
(только для авторизованных пользователей)

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