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

Файл примера

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