Здравствуйте. В этой статье расскажу, как в Экселе найти ближайшее значение. Казалось бы, можно для этого использовать функции ПОИСКПОЗ, или ВПР, однако они умеют искать либо ближайшее большее, либо меньшее. Нам же нужно, чтобы программа сама определила, какое число ближе, большее, или меньшее.

список фестивалей

На картинке выше – пример для тренировок. Это график проведения мировых фестивалей в январе 2020 года. Мы хотим найти ближайший к указанной дате фестиваль. Сразу оговорюсь, что я присвоил имена диапазонам:

  • А3:А11 – «даты»
  • В3:В11 – «фестивали»

Формула поиска ближайшего мероприятия будет такой:

=ИНДЕКС(фестивали;ПОИСКПОЗ(МИН(ABS(даты-E2));ABS(даты-E2);0))

Это формула массива, после её ввода нажмите Ctrl+Shif+Enter вместо традиционного Enter!

дата найдена

Как это работает? Начну объяснение из глубины формулы:

  1. Функция ABS вычисляет модуль числа. Формула ABS(даты-E2) вернёт массив разностей между искомой датой и датами всех фестивалей
  2. Выражение МИН(ABS(даты-E2)) – определит минимальное значение в полученном ранее массиве
  3. ПОИСКПОЗ определит положение в списке разностей (из п.1) минимального значения (из п.2). Здесь мы уже нашли позицию ближайшего совпадения в диапазоне с датами
  4. Теперь с помощью функции ИНДЕКС выводим название фестиваля по найденному положению в списке

Задачу мы выполнили, нашли ближайшее значение, получили универсальную формулу. Можно на этом остановиться, но у меня есть бонус для вас.

Формула бесперебойно работает, если в ячейку Е2 введена дата. Но вы увидите ошибку #ЗНАЧ! если кто-то случайно введёт туда текст. Это делает наш проект незавершенным. Что можно предпринять? Предлагаю 2 варианта:

    1. Перехватить ошибку с помощью функции ЕСЛИОШИБКА, и вернуть вместо неё какое-то информативное сообщение:
      сообщение вместо ошибки
      Здесь я так же использовал условное форматирование, чтобы текст становился красным при появлении фразы «Введено некорректное значение»
    2. Организовать проверку данных в этой ячейке. Пользователь даже не сможет ввести что-то другое. Для этого нажмите на ленте Данные – Работа с Данными – Проверка данных. В открывшемся окне выберите Тип данных: Дата. Теперь программа ограничит типы вводимых значений.

    Вот теперь у меня всё. До встречи!