LET в Excel: как сделать длинный расчёт понятным и проверяемым

В формуле закупки повторяются одни и те же выражения: спрос на день, число дней покрытия и доступный остаток. Через месяц трудно понять, почему в одной ветке вычтена поставка, а в другой нет. LET позволяет дать промежуточным значениям имена и сделать правило расчёта читаемым.

Сначала запишите правило словами

Пусть B2 — средний спрос в штуках за день, C2 — желаемое покрытие в днях, D2 — доступный остаток, E2 — подтверждённая поставка, которая придёт вовремя. Целевой запас равен спросу, умноженному на покрытие. Потребность — целевой запас минус остаток и поставка, но не меньше нуля.

Такое правило предполагает, что спрос уже оценён отдельно, поставка надёжна и единицы совпадают. Если товар заказывается упаковками, потребуется ещё округление до кратности. Не добавляйте его незаметно: сначала проверяется базовая потребность в штуках.

Назовите промежуточные значения

В LET пары «имя; значение» идут перед итоговым выражением. В примере demand — спрос, target — целевой запас, available — доступное количество. Имена должны быть допустимыми именами Excel и не совпадать с адресами ячеек. Латинские слова здесь помогают отличить имена от русских функций.

Такая формула читает каждое исходное поле один раз и показывает ход вычисления. Для проверки можно временно вернуть target или available последним аргументом LET и увидеть промежуточное значение. В рабочей версии последним остаётся итоговая потребность.

=LET(demand;B2;target;demand*C2;available;D2+E2;МАКС(0;target-available))

Проверьте ветви, а не только один результат

При спросе 4 штуки в день, покрытии 10 дней, остатке 15 и поставке 10 потребность равна 15. При остатке 50 она равна 0. При нулевом спросе формула также даёт 0. Эти случаи проверяют основную арифметику и ограничение снизу.

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

Сохраните возможность объяснить результат

Рядом с итогом полезно показывать спрос, покрытие и доступный запас. Пользователь сможет понять изменение закупки после обновления. LET делает формулу компактнее, но не заменяет прозрачность бизнес-параметров.

Когда расчёт усложнится, выделите подтверждённые поставки по сроку. Поставка после конца периода покрытия не решает текущую потребность. Для нескольких складов сначала определите, доступен ли запас другого склада и сколько времени займёт перемещение.

Не пытайтесь вместить все правила в один огромный LET. Если выражение отвечает сразу за качество данных, выбор склада и оптимизацию закупки, разделите его на несколько объяснимых этапов. Имена помогают читать расчёт, а не оправдывают бесконечное вложение логики.

При передаче книги сохраните несколько контрольных строк с ожидаемыми результатами. После изменения правила коллега сможет проверить обычную потребность, избыток и отсутствие спроса. Затем повторите расчёт на следующем комплекте данных — именно там проявляются пустые значения и новые комбинации параметров.

Соберите обновляемый отчёт на практике

В курсе есть урок о LET и проверке длинной формулы, а в практикуме — расчёт закупки по нормативу покрытия. Вы сможете отработать и читаемую запись правила, и контрольные результаты на новых данных. Это особенно полезно для книги, которую затем должен сопровождать другой сотрудник.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.