Одна из предыдущих заметок была посвящена функции ЕСЛИМН, которая является своеобразным продолжением функции ЕСЛИ и позволяет проверить значение на соответствие сразу нескольким условиям.
При этом был рассмотрен пример, в котором вычислялась заработная плата сотрудника с учетом надбавки за выслугу лет.
У этой задачи есть и другое более простое решение, о котором я и хочу сейчас рассказать. В нем будет использоваться функция ВПР.
Последний аргумент функции ВПР - интервальный просмотр. Именно он чаще всего вызывает непонимание у пользователей. В большинстве задач, в которых используется ВПР, аргумент интервальный просмотр устанавливается равным нулю - точное совпадение. И вот сейчас как раз та ситуация, кода можно применить приблизительное совпадение или 1.
Давайте по шагам составим формулу.
Итак, у нас есть справочная таблица, которая нормирует надбавку к окладу по выслуге лет. Сначала нам нужно подтянуть данные из справочной таблицы в основную и для этого сконструируем формулу с функцией ВПР.
Мы будем сравнивать стаж сотрудника, поэтому первый аргумент берем из соответствующего столбца (ячейка С2), затем указываем диапазон значений, с которым будет сравниваться стаж - выделяем значения справочной таблицы. Фактически стаж будет сравниваться со значениями первого столбца, то есть функция ВПР пройдет вертикально значения первого столбца до тех пор, пока не обнаружит значение, большее заданного. В этом случае она сделает шаг назад и вернет значение из второго столбца справочной таблицы. Поэтому третий аргумент - это цифра 2, то есть второй столбец выбранного диапазона значений.
Ну и чтобы функция ВПР работала именно так, как нам нужно установим приблизительное совпадение - ИСТИНА или 1.
Растягиваем формулу по диапазону и получаем ошибку «НД» в двух ячейках.
А произошло это потому, что стаж у этих сотрудников менее года, и поэтому функция ВПР, начав просмотр, сразу же уперлась в "потолок". Ну а отступать ей было некуда и она вернула ошибку «НД» - нет данных.
По этой причине в справочной таблице обязательно должны присутствовать значения, охватывающие весь диапазон проверяемых значений.
В нашем случае в таблице не хватает нуля, поэтому сместим данные справочной таблицы на строку ниже и подставим ноль.
Теперь откорректируем формулу, подставив в нее изменившийся диапазон.
Размер надбавки будет "подтянут" верно и останется лишь дополнить формулу, то есть умножить результат функции ВПР на размер оклада, чтобы получить надбавку в рублях, а затем еще раз прибавить оклад, чтобы получить сумму к выдаче.
Все готово.
Как видите, эта формула короче и проще формулы с функцией ЕСЛИМН.
Применять функцию ВПР со значением интервального просмотра приблизительное совпадение можно в целом ряде подобных задач. Например, когда есть таблица скидок, размер которых зависит от суммы заказа, или когда нужно рассчитать премию менеджерам в зависимости от их объемов продаж. То есть все задачи, где есть небольшая справочная таблица со шкалой значений могут быть решены подобным образом.
НО ВАЖНО, чтобы значения в справочной таблице, с которыми сравнивается значение из основной таблицы, были отсортированы по возрастанию. А также чтобы была задана нижняя граница справочного диапазона, иначе возникнет ошибка, как это случилось в самом начале рассмотрения данного приема.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
