Здравствуйте, уважаемые читатели. Сегодня разбираем, как найти ближайшее большее значение в таблице. Многие начинающие пользователи применяют для этого формулу из нескольких вложенных друга в друга функций ЕСЛИ, однако есть и более простое решение.
Для такого поиска можно использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ. Мы рассмотрим это на двух примерах.
Если нужно найти ближайшее меньшее – можно использовать функцию ВПР в режиме приблизительного поиска!
Поиск если данные отсортированы по убыванию
Так или иначе, но для приблизительного поиска ближайшего значения, таблицы с данными приходится сортировать. В статье о функции ПОИСКПОЗ я рассказывал, что она может искать ближайшее большее значение лишь в том случае, когда исходная таблица отсортирована по убыванию. Значит, рассматриваемый сейчас пример максимально удобен для работы.
В примере на картинке есть прайс-лист, цена в котором зависит от объема заказанной партии:
- До 100 ед – 800 евро/ед
- До 200 ед – 750 евро/ед
- До 300 ед – 700 евро/ед и т.д.
Во второй таблице задается размер заказа. Требуется из прайса получить ближайшую большую партию и соответствующую ей цену. Давайте соберем формулу по частям:
- Ищем в прайсе ближайший больший заказ. Применяем функцию ПОИСКПОЗ в режиме поиска ближайшего большего: =ПОИСКПОЗ(E3;A3:A11;-1)
- Получаем цену для этой партии с помощью функции ИНДЕКС( массив ; номер_по_порядку ). Номером по порядку здесь будет результат предыдущего шага, поэтому вместо аргумента «номер» впишем предыдущую формулу: ИНДЕКС(B3:B11;ПОИСКПОЗ(E3;A3:A11;-1))
- Если в заказе указать партию, большую чем 5000 (максимальная в прайсе) – функция ПОИСКПОЗ вернет ошибку #Н/Д. Чтобы этого не было, применим функцию обработки ошибок ЕСНД. Обернём ею всю формулу и укажем, что в случае ошибки давать клиенту минимальную цену. Вот так:
=ЕСНД(ИНДЕКС(B3:B11;ПОИСКПОЗ(E3;A3:A11;-1));B3)
Видите, получилось аккуратно, формула работает бесперебойно. Но так просто бывает не всегда, переходим к следующему примеру.
Поиск если таблица сортирована по возрастанию
Сейчас такой случай. В исходной таблице у нас этапы жизни человека, от рождения до 100 лет. Нужно ввести возраст и получить текущий этап и следующий.
Таблица отсортирована по возрастанию, а это делает невозможным применение ПОИСКПОЗ, т.к. ей требуется убывание данных. Пересортировывать таблицу мы не будем, т.к. она будет выглядеть нелогично – от старого человека к молодому. Т.е. против хода времени. Это усложняет поиск следующего этапа жизни.
Я предлагаю простое решение проблемы. С помощью ПОИСКПОЗ мы сможем найти текущий этап (поиск ближайшего меньшего возможен сейчас), и выведем на экран не найденный индекс, а следующий. Вот так:
=ЕСЛИОШИБКА(ИНДЕКС(B3:B11;ПОИСКПОЗ(E3;A3:A11)+1);B11)
Формула отличается от предыдущего случая тем, что к результату ПОИСКПОЗ прибавляется единица, то есть сдвигаемся на следующий номер строки (к следующему этапу).
Кроме того, если указать возраст больше максимального – получим ошибку не #Н/Д, а #ССЫЛКА. Поэтому, я заменил функцию ЕСНД на ЕСЛИОШИБКА.
Вот и всё, задача реализована. Пишите в комментариях ваш возраст, буду определять, к какому этапу жизни вам готовиться!