Одной из самых популярных функций Эксель является функция ЕСЛИ, но у нее есть один существенный недостаток - когда условий много, то приходится использовать вложенные функции ЕСЛИ, в результате чего формула становится очень громоздкой. Быстро найти и откорректировать в ней нужный аргумент не получится, да и вероятность допустить ошибку при написании или редактировании формулы резко увеличивается.
Начиная с Excel 2019 появилась функция ЕСЛИМН, которая делает формулу максимально простой и удобной. Также эта функция доступна и в Офисе 365.
Давайте рассмотрим простой пример, чтобы понять логику функции ЕСЛИМН.
Есть перечень сотрудников с их окладами.
Необходимо сравнить оклады с некоторой контрольной суммой и если оклад сотрудника меньше этой суммы то вывести слово «Меньше», а если больше, то, соответственно, «Больше».
Воспользуемся функцией ЕСЛИМН.
Синтаксис функции очень прост - указываем условие и затем значение, которое будет выводиться при выполнении условия.
В таком же порядке можем вывести попарно условия и значения для 127 ситуаций. Но не стоит с условиями злоупотреблять, так как, чем их больше, тем сложнее становится «читать» формулу, ведь и условия, и значения отделяются друг от друга точкой с запятой. Поэтому проверять или редактировать длинную формулу будет не очень удобно.
Итак, нам нужно сравнить оклад сотрудника с контрольным значением. Так как мы ссылаемся на конкретную ячейку и в дальнейшем формулу планируем размножить на весь столбец, то с помощью клавиши F4 делаем ссылку абсолютной. Если это условие выполняется, то выводим слово «Больше». По аналогии вводим следующее условие - оклад должен быть меньше контрольного значения.
Если мы ее растиражируем на весь диапазон, то увидим ошибки. Они появились по той причине, что оклады у этих сотрудников равны контрольной сумме, а в условиях функции такая ситуация никак не отражена.
Можем создать еще одно условие, в случае выполнения которого выведем, например, пустоту - двойные кавычки.
Либо можем воспользоваться возможностями самой функции ЕСЛИМН. Дело в том, что в качестве последнего аргумента функции можно задать не какое-то конкретное условие, а просто слово ИСТИНА, и затем определить необходимый результат. Этот аргумент функции будет использоваться в том случае, если ни одно из ранее перечисленных условий не выполняется.
Мы рассмотрели очень простой пример, но давайте немного усложним задачу. Рассчитаем ежемесячную процентную надбавку за стаж непрерывной работы.
У нас есть дата поступления сотрудника на работу и его оклад. Также есть таблица с условиями ежемесячной надбавки. Задача - вычислить заработную плату сотрудника с учетом надбавок.
Сначала вычислим стаж сотрудника. Для этого вставим одноименный столбец в таблицу. Так как нам нужно получить фактическое значение лет, а не округленное, то предлагаю просто вычесть из сегодняшней даты, получаемой с помощью функции СЕГОДНЯ, дату поступления сотрудника на работу. Таким образом мы получим количество отработанных дней. Нам нужно узнать количество лет, поэтому разделим полученное значение на юлианский год, учитывающий високосные года, то есть разделим на 365,25.
Получим фактическое количество лет, которые отработал каждый сотрудник. Теперь можем сразу вычислить необходимую надбавку, но давайте сделаем таблицу более "прозрачной" и сначала подтянем в ее данные о надбавке. Для этого сначала вставим в таблицу соответствующие столбцы, а затем с помощью функции ЕСЛИМН проверим, удовлетворяет ли стаж условиям из соответствующей таблицы.
И тут есть один очень важный нюанс!
Функция ЕСЛИМН проверяет все заданные условия по порядку и возвращает первое значение, которое удовлетворяет условию. Поскольку условия в формулу подставлялись в порядке возрастания выслуги лет, то результат мы получим неверный - значения будут либо нулевыми, либо 5%.
Это случилось потому, что стаж всех сотрудников (кроме двух) удовлетворяет первому условию - больше года. То есть первое заданное в функции условие выполнится и функция прекратит проверку, вернув значение в 5%.
Давайте «развернем» условия, то есть зададим их с конца вспомогательной таблицы.
В итоге надбавка в процентах будет подтянута верно.
То есть при использовании функций в Excel очень важно понимать логику их поведения. Тогда можно всегда подстроить функцию под решение вашей конкретной задачи.
Осталось рассчитать значение надбавки в рублях, перемножив проценты на оклад.
Затем выведем общую сумму к выплате для каждого сотрудника.
Само собой, можно упростить таблицу, усложнив формулу. То есть можем сразу в функции ЕСЛИМН рассчитать или надбавку в рублях, или даже полную сумму к выдаче.
Но такая формула будет очень громоздкой и в ней легко запутаться. Поэтому я бы рекомендовал по возможности всегда использовать промежуточные вычисления, ведь если нам какие-то данные не особо важны, то мы всегда можем скрыть столбцы с ними, оставив на экране только действительно нужное.
Ссылки на мои ресурсы по Excel
★ Телеграм