Замена вложенных функций ЕСЛИ на таблицу подстановки с ПРОСМОТРХ (XLOOKUP) в Excel

Создание сложных условий в Excel, таких как расчет скидок или бонусов, часто приводит к громоздким формулам с вложенными функциями ЕСЛИ. В этой статье мы рассмотрим использование ПРОСМОТРХ (XLOOKUP) для замены вложенных ЕСЛИ — более элегантный и масштабируемый подход с помощью таблицы подстановки.

Классический метод расчета скидки по объему продаж предполагает построение длинной цепочки условий. Например: «Если продажи превышают 500 000, скидка 20%; иначе, если продажи превышают 250 000, скидка 15%...» и так далее. С каждым новым уровнем формула становится все сложнее для чтения и редактирования.

1. Создайте таблицу подстановки. Вынесите все правила (пороги продаж и соответствующие проценты скидки) в отдельный диапазон ячеек. Например, столбец F — пороговые значения, столбец G — проценты.
2. Используйте функцию ПРОСМОТРХ (XLOOKUP). Вместо вложенных ЕСЛИ примените формулу: =ПРОСМОТРХ(Б10, Е13:Е18, Ё13:Ё18, , -1) (=XLOOKUP(B10, F13:F18, G13:G18, , -1)).
   • Аргумент -1 в качестве Match_Mode указывает функции найти значение, не превышающее искомое (приблизительное совпадение «меньше или равно»).
   • Важное преимущество ПРОСМОТРХ (XLOOKUP) — таблица подстановки не требует сортировки.
3. Проверьте результат. Для значения продаж в 550 000 функция вернет скидку 20%, соответствующую порогу 500 000, так как это ближайшее меньшее значение из таблицы.

Совет для самоcтоятельных формул: Если нужно скрыть таблицу подстановки, можно использовать константы массивов. Выделите в строке формул ссылку на диапазон (например, F13:F18), нажмите F9, и Excel заменит ее на значения массива {500000;250000;100000;50000;10000;0}. Повторите для второго диапазона. После этого исходную таблицу можно удалить.

Бонусный совет по скобкам: Excel выделяет парные скобки черным цветом. Завершая ввод сложной формулы, просто продолжайте вводить закрывающие скобки, пока не появится черная — это сигнал, что все открытые скобки закрыты.

Альтернатива в Microsoft 365: Для решения той же задачи можно использовать функцию ЕСЛИМН (УСЛОВИЯ (IFS)): =ЕСЛИМН(Б10>500000;20%;Б10>250000;15%;Б10>100000;10%;Б10>50000;5%;Б10>10000;1%;ИСТИНА;0) (=ЕСЛИМН(B10>500000;20%;B10>250000;15%;B10>100000;10%;B10>50000;5%;B10>10000;1%;ИСТИНА;0)). Однако таблица подстановки с ПРОСМОТРХ (XLOOKUP) остается более гибким решением, особенно при частом изменении условий.

Переход от вложенных условий ЕСЛИ к таблице подстановки с ПРОСМОТРХ (XLOOKUP) — это шаг к созданию более чистых, удобных для поддержки и профессиональных таблиц Excel.