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