Когда нам нужно установить соответствие введенного числового значения определенному диапазону значений, обычно мы используем формулу ЕСЛИ. Однако, если диапазонов проверяемого значения много, то можно столкнуться с ограничениями функции ЕСЛИ (количество вложенных функций ограничено 7-ю в Excel 2003 и более старых выпусках, 64-мя в Excel 2007 и 128-ю в Excel 2010+), да и контролировать (добавлять, удалять, изменять) диапазоны становиться затруднительно. Разберем вариант подобной проверки.

Допустим, мы имеем 8 диапазонов значений:

В случае использования функции ЕСЛИ, формула выглядела бы следующим образом:

=ЕСЛИ(E2<=1;"x";ЕСЛИ(И(E2>=2;E2<=9);"группа 1";ЕСЛИ(И(E2>=10;E2<=16);"группа 2";ЕСЛИ(И(E2>=17;E2<=22);"группа 3";ЕСЛИ(И(E2>=23;E2<=51);"группа 4";ЕСЛИ(И(E2>=52;E2<=74);"группа 5";ЕСЛИ(И(E2>=75;E2<=89);"группа 7")))))))

В ней достаточно сложно разобраться и вероятность возникновения ошибок (человеческий фактор) высока.

Вместо этого создадим таблицу диапазонов и соответствующее каждому диапазону название или любое другое значение: текстовое, числовое, логическое. В нашем случае используем диапазоны с текстовым названием групп.

Определим, что ввод проверяемого значения будет производится в ячейку E2. В ячейке F2 введем следующую формулу:

=ИНДЕКС(C2:C9;ПОИСКПОЗ(E2;A2:A9;1))

Рассмотрим данную формулу подробнее:

  • ПОИСКПОЗ(E2;A2:A9;1) – благодаря последнему параметру функции ПОИСКПОЗ («1»), мы определяем наибольшее значение среди перечня минимальных значений (приведены в ячейках A2:A9), которое не превышает искомое значение (E2). Функция возвращает нам позицию (номер строки) найденного значения среди минимальных значений
  • ИНДЕКС(C2:C9;ПОИСКПОЗ(…)) – по определенной функцией ПОИСКПОЗ позиции искомого значения, определяем название группы

Таким образом, мы получим небольшую по объему формулу определения диапазона значений, возможность легко вносить в них изменения и значительно снизим человеческий фактор.

Рассмотренный способ работает в любой версии Excel и не имеет ограничений по объему диапазонов. Есть одно условие – диапазоны должны быть расположены в порядке возрастания.

Скачать готовый пример вы можете здесь.

Добавить комментарий


Защитный код
Обновить

Комментарии   

Евгений
0 # Евгений 01.06.2016 16:35
А если искомое значение дробное? Например, 1,5
Ответить | Ответить с цитатой | Цитировать

Похожие статьи

Вверх