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

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

Эта статья особенно полезна тем, кто ищет формулы в Excel, хочет быстрее разобраться в функциях и применять рабочие примеры без путаницы между русской и английской версиями Excel.

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

1. Создайте таблицу подстановки. Вынесите все правила (пороги продаж и соответствующие проценты скидки) в отдельный диапазон ячеек. Например, столбец F — пороговые значения, столбец G — проценты.
2. Используйте функцию ПРОСМОТРХ (XLOOKUP). Вместо вложенных ЕСЛИ примените формулу: Русская версия: =ПРОСМОТРХ(B10; F13:F18; G13:G18; ; -1)English version: =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): Русская версия: =ЕСЛИМН(B10>500000;20%;B10>250000;15%;B10>100000;10%;B10>50000;5%;B10>10000;1%;ИСТИНА;0)English version: =ЕСЛJNO(B10>,500000,20%,B10>,250000,15%,B10>,100000,10%,B10>,50000,5%,B10>,10000,1%,TRUE,0). Однако таблица подстановки с ПРОСМОТРХ (XLOOKUP) остается более гибким решением, особенно при частом изменении условий.

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