LET и LAMBDA в Excel: свои функции без VBA
Продвинутое3 минРедакция Innovialabs
Коротко
LET даёт имена промежуточным значениям внутри формулы, а LAMBDA превращает формулу в свою функцию, которую можно сохранить по имени и вызывать как СУММ — без макросов и VBA.
Что делают LET и LAMBDA
- LET задаёт переменные внутри формулы: значение считается один раз и дальше используется по имени.
- LAMBDA описывает функцию с параметрами. Сохранив её в диспетчере имён, вы получаете свою функцию рабочей книги, например
=MARGIN(B2;C2). - MAP, REDUCE, BYROW принимают LAMBDA и применяют её к массивам — построчно, поэлементно или с накоплением.
Функции доступны в Microsoft 365 и Excel для веба; в старых коробочных версиях LAMBDA может не быть — тогда вы увидите #ИМЯ?. В Google Таблицах все они тоже есть.
Примеры ниже записаны с английскими именами функций и запятыми. В русской локали Excel имена функций могут быть локализованы, а разделитель аргументов — точка с запятой.
LET: длинная формула становится читаемой
Типичная проблема — один и тот же кусок повторяется в формуле несколько раз. Например, маржа по клиенту:
=IF(SUMIFS(C:C,A:A,F2)=0,0,(SUMIFS(C:C,A:A,F2)-SUMIFS(D:D,A:A,F2))/SUMIFS(C:C,A:A,F2))
С LET то же самое:
=LET(
rev, SUMIFS(C:C, A:A, F2),
cost, SUMIFS(D:D, A:A, F2),
IF(rev = 0, 0, (rev - cost) / rev)
)
Плюсы: каждое SUMIFS считается один раз, логику видно сразу, а правка одного условия не требует искать все копии. Последний аргумент LET — всегда итоговое вычисление.
LAMBDA: своя функция за три шага
- Напишите и проверьте LAMBDA прямо в ячейке, вызвав её сразу:
=LAMBDA(rev, cost, IF(rev=0, 0, (rev-cost)/rev))(C2, D2) - Откройте Формулы → Диспетчер имён → Создать, задайте имя
MARGIN, а в поле «Диапазон» вставьте формулу без вызова в конце. - Используйте в любой ячейке книги:
=MARGIN(C2, D2).
Внутри LAMBDA можно использовать LET, а именованная LAMBDA может вызывать сама себя — так делают рекурсию, например для многократной замены символов по таблице.
Если LAMBDA ввести в ячейку без вызова, Excel вернёт #CALC!. Неверное число аргументов даёт #VALUE!.
MAP, REDUCE и BYROW на практике
MAP — применить функцию к каждому элементу:
=MAP(A2:A100, LAMBDA(x, PROPER(TRIM(x))))
Очищает пробелы и приводит имена к виду «Иван Петров» сразу для всего столбца.
BYROW — одно значение на каждую строку:
=BYROW(B2:E50, LAMBDA(r, MAX(r) - MIN(r)))
Разброс показателей по каждой строке. Функция внутри должна возвращать одно значение, а не массив.
REDUCE — накопить результат:
=REDUCE(0, B2:B200, LAMBDA(acc, s, acc + IF(s > H1, s * H2, s * H3)))
Сумма комиссии, где ставка зависит от порога в H1, а ставки лежат в H2 и H3. acc — накопитель, 0 — его начальное значение. Если нужны промежуточные итоги, используйте SCAN с той же логикой.
Эквиваленты в Google Таблицах
| Excel | Google Таблицы |
|---|---|
| LET, LAMBDA, MAP, REDUCE, BYROW, BYCOL, SCAN | те же функции |
| Диспетчер имён + LAMBDA | Данные → Именованные функции |
В Google Таблицах именованную функцию создают в отдельном окне: задаёте имя, аргументы-заполнители и определение формулы. Писать LAMBDA вокруг не нужно — аргументы используются по именам прямо в формуле. Именованные функции можно импортировать из другой таблицы.
Частые ошибки
- Ссылки на целые столбцы внутри MAP или BYROW — функция обходит лишний миллион строк и тормозит. Ограничивайте диапазон или используйте умные таблицы.
- Нет описания у именованной функции — через полгода никто не помнит, что она делает. Заполняйте поле комментария.
- Сложная логика без проверки по частям. Сначала отладьте LAMBDA с прямым вызовом в ячейке, потом сохраняйте.
- Ожидание, что функция появится во всех файлах. Именованная LAMBDA живёт внутри книги; для переноса скопируйте лист или используйте надстройку Excel Labs.