Добрый Дзен всем!
Функция ЕСЛИ() - одна из самых часто используемых в Excel. Однако в одной ЕСЛИ() можно задать не более двух вариантов "ветвления" (да/нет). И если для новичков является сложным разобраться с тем, как в ЕСЛИ() увеличить количество вариантов, то для пользователей, обладающих опытом применения этой функции, возникает прямо противоположный вопрос - как избавиться от большого числа вложенных ЕСЛИ().
Для примера возьмем некую таблицу премирования условного продавца/токаря/менеджера - на ваш выбор
1. План\факт и итоговый % премирования:
2. Шкала % премирования в зависимости от % выполнения плана:
Как мы видим, условия довольно просты: каждые 10% выполнения плана соответствуют своему % премирования, который не является линейным
Исходные данные определены, переходим к главному вопросу: Как написать формулу с 26-ю условиями?
Помимо функции ЕСЛИ() нам понадобится ещё одна функция из категории логических - функция И(). Начинаем с первой строки:
=ЕСЛИ(И(C2>=F2;C2<G2);H2)
На человеческом языке эта формула значит следующее: Если % выполнения плана (С2) больше или равен нулю (F2) и, в то же время, % выполнения плана (С2) меньше 10 (G2) , то результат равен 1% (H2).
Теперь не составит труда написать аналогичную формулу для следующей строки:
=ЕСЛИ(И(C2>=F3;C2<G3);H3)
и вложить одну формулу в другую:
=ЕСЛИ(И(C2>=F2;C2<G2);H2;ЕСЛИ(И(C2>=F3;C2<G3);H3)
Далее мы "тиражируем" уже написанную часть формулы 12 раз (для каждой строки) и вкладываем их друг в друга. В итоге получается такая формула:
=ЕСЛИ(И(C2>=F2;C2<G2);H2;ЕСЛИ(И(C2>=F3;C2<G3);H3;ЕСЛИ(И(C2>=F4;C2<G4);H4;ЕСЛИ(И(C2>=F5;C2<G5);H5;ЕСЛИ(И(C2>=F6;C2<G6);H6;ЕСЛИ(И(C2>=F7;C2<G7);H7;ЕСЛИ(И(C2>=F8;C2<G8);H8;ЕСЛИ(И(C2>=F9;C2<G9);H9;ЕСЛИ(И(C2>=F10;C2<G10);H10;ЕСЛИ(И(C2>=F11;C2<G11);H11;ЕСЛИ(И(C2>=F12;C2<G12);H12;ЕСЛИ(И(C2>=F13;C2<G13);H13;ЕСЛИ(И(C2>=F14;C2<G14);H14)))))))))))))
Примечание: Если Вы работает с файлами, имеющими трехбуквенные расширениями (например .xls), то на седьмом вложении Вы увидите следующее сообщение:
Что-бы избежать появления этого сообщения, необходимо сохранить файл с четырехбуквенным расширением (я, обычно, использую расширение .xlsb), закрыть файл, открыть заново и продолжить писать формулу. Количество вложений функций в четырехбуквенных форматах составляет 128 (для Excel 2007 - 64).
Итак, формула написана и даже работает)
Однако на её написание ушло около трех минут и она, при этом, получилась громоздкой (339 знаков) и неудобной для редактирования, поскольку вложенные если визуально сложно отделять друг от друга. Конечно, можно записать формулу с переводом строк (см. Как сделать визуально наглядной большую формулу?) и это добавит "читаемости":
=ЕСЛИ(И(C2>=F2;C2<G2);H2;
ЕСЛИ(И(C2>=F3;C2<G3);H3;
ЕСЛИ(И(C2>=F4;C2<G4);H4;
ЕСЛИ(И(C2>=F5;C2<G5);H5;
ЕСЛИ(И(C2>=F6;C2<G6);H6;
ЕСЛИ(И(C2>=F7;C2<G7);H7;
ЕСЛИ(И(C2>=F8;C2<G8);H8;
ЕСЛИ(И(C2>=F9;C2<G9);H9;
ЕСЛИ(И(C2>=F10;C2<G10);H10;
ЕСЛИ(И(C2>=F11;C2<G11);H11;
ЕСЛИ(И(C2>=F12;C2<G12);H12;
ЕСЛИ(И(C2>=F13;C2<G13);H13;
ЕСЛИ(И(C2>=F14;C2<G14);H14
)))))))))))))
Но, к сожалению, такая запись не добавит функциональности. Например, в том случае, если прибавятся новые условия, то их необходимо будет вручную прописывать в формуле. А если таблица содержит не 26 условий, а в десять раз больше, то и на написание такой формулы будет уходить не три минуты, а все полчаса, что, разумеется, непродуктивно и, значит, самое время задуматься об альтернативе.
Самый нетрудоемкий и простой вариант - это использование функции ВПР(). Формула с этой функцией будет выглядеть так:
=ВПР(C2;F2:H14;3)
Как ни странно, такая формула, которую можно написать за несколько секунд для ЛЮБОГО количества условий (вплоть до миллиона, умещающегося на листе Excel) будет делать ровно тоже самое, как и любое количество вложенных ЕСЛИ() в нашем примере. Причем формула с функцией ВПР() содержит всего 16 знаков.
Как это работает?
Всё довольно просто: функция ВПР(), при опущенном своем аргументе (4) - Интервальный_просмотр ищет поиском нестрогое совпадение своего аргумента (1) - Искомое_значение в первом столбце диапазона, заданном в аргументе (2) - Таблица и возвращает значение из столбца указанного в аргументе (3) - Номер_столбца. Звучит немного устрашающе, но на практике, пользоваться этой функцией легко.
Разберем как функция ВПР() производит вычисления на нашем примере:
% выполнения плана (1) = 100. В таблице условий премирования это значение находится в ячейке F12 (двенадцатая строка). В диапазоне (2 F2:H14 третий номер столбца (3) - это столбец H. На двенадцатой строке в третьем столбце значение 57,76. Именно его и возвратит формула при 100% выполнения плана.
Но, 100% выполнения плана есть в таблице, а как быть, если % выполнения плана, скажем, 99?
Вот тут-то и начинает играть роль аргумент (4) - Интервальный_просмотр. Если этот аргумент оставить пустым, написать в нем цифру 1 или ввести логическое (булево) значение ИСТИНА (все эти три варианта абсолютно равнозначны), то в этом случае функция будет искать в первом столбце таблицы, внимание (!), НАИБОЛЬШЕЕ ЗНАЧЕНИЕ, КОТОРОЕ МЕНЬШЕ ИЛИ РАВНО ИСКОМОМУ. Таким образом, функция ВПР() остановит свой поиск на ячейке F11, поскольку значение 90%, которое находится в этой ячейке, является наибольшим значением, которое меньше искомого 1 99%. Ну а далее - мы уже знаем. Найденная ячейка F11 находится на одиннадцатой строке, В диапазоне 2 F2:H14 третий номер столбца (3) - это столбец H. На одиннадцатой строке в третьем столбце диапазона значение 38,44. Именно его и возвратит формула при 99% выполнения плана.
Ценность формулы с функцией ВПР(), помимо краткости записи, заключается ещё и в том, что при добавлении условий в таблицу достаточно просто увеличить диапазон аргумента (2) - Таблица. Например, если мы изменим исходные данные с 26 до 260 условий, то формула из такой:
=ВПР(C2;F2:H14;3)
изменится на такую:
=ВПР(C2;F2:H140;3)
При этом на изменения в формуле уйдет не полчаса, как в случае со вложенными ЕСЛИ(), а всего пара-тройка секунд. Так же хочу обратить внимание, на то, что при использовании формулы с функцией ВПР() второй столбец таблицы становится ненужным (поиск идет только по первому столбцу), его можно просто скрыть, очистить или даже удалить (но в этом случае в аргументе (3) - Номер_столбца надо будет изменить значение 3 на 2, т. к в таблице останутся только два столбца).
В Excel многие задачи можно решить разными способами. И далеко не всегда этих способов один-два. И данный случай - не исключение. Выше я уже привел два варианта решения, но есть и другие. Приведу ещё два варианта решения формулами:
=ПРОСМОТР(C2;F2:F14;H2:H14)
=ИНДЕКС(H2:H14;ПОИСКПОЗ(C2;F2:F14))
Разбирать в этой статье мы их не будем, они, фактически, работают по тому же принципу, что и вариант формулы с функцией ВПР().