Для примера возьмем некую таблицу премирования условного продавца/токаря/менеджера - на ваш выбор

1. План\факт и итоговый % премирования:

2. Шкала % премирования в зависимости от % выполнения плана:

Как мы видим, условия довольно просты: каждые 10% выполнения плана соответствуют своему % премирования, который не является линейным:

Исходные данные определены, переходим к главному вопросу: Как написать формулу с 26-ю условиями?

Помимо функции ЕСЛИ() нам понадобится ещё одна функция из категории логических - функция И(). Начинаем с первой строки: 

На человеческом языке эта формула значит следующее: Если % выполнения плана (С2) больше или равен нулю (F2) и, в то же время, % выполнения плана (С2) меньше 10 (G2) , то результат равен 1% (H2).

Теперь не составит труда написать аналогичную формулу для следующей строки:

и вложить одну формулу в другую:

Далее мы "тиражируем" уже написанную часть формулы 12 раз (для каждой строки) и вкладываем их друг в друга. В итоге получается такая формула:

Примечание: Если Вы работает с файлами, имеющими трехбуквенные расширениями (например .xls), то на седьмом вложении Вы увидите следующее сообщение:

Что-бы избежать появления этого сообщения, необходимо сохранить файл с четырехбуквенным расширением (я, обычно, использую расширение .xlsb), закрыть файл, открыть заново и продолжить писать формулу. Количество вложений функций в четырехбуквенных форматах составляет 128 (для Excel 2007 - 64).

Итак, формула написана и даже работает)

Однако на её написание ушло около трех минут и она, при этом, получилась громоздкой (339 знаков) и неудобной для редактирования, поскольку вложенные если визуально сложно отделять друг от друга. Конечно, можно записать формулу с переводом строк (см. Как сделать визуально наглядной большую формулу?) и это добавит "читаемости":

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

Самый нетрудоемкий и простой вариант - это использование функции ВПР(). Формула с этой функцией будет выглядеть так:

Как ни странно, такая формула, которую можно написать за несколько секунд для ЛЮБОГО количества условий (вплоть до миллиона, умещающегося на листе 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 условий, то формула из такой:

изменится на такую:

При этом на изменения в формуле уйдет не полчаса, как в случае со вложенными ЕСЛИ(), а всего пара-тройка секунд. Так же хочу обратить внимание, на то, что при использовании формулы с функцией ВПР() второй столбец таблицы становится ненужным (поиск идет только по первому столбцу), его можно просто скрыть, очистить или даже удалить (но в этом случае в аргументе 3  - Номер_столбца надо будет изменить значение 3 на 2, т. к в таблице останутся только два столбца).

В Excel многие задачи можно решить разными способами. И далеко не всегда этих способов один-два. И данный случай - не исключение. Выше я уже привел два варианта решения, но есть и другие. Приведу ещё два варианта решения формулами:

Разбирать в этой статье мы их не будем, они, фактически, работают по тому же принципу, что и вариант формулы с функцией ВПР().