LAMBDA в Excel: собственная функция из обычной записи без макросов и надстроек

Одна и та же формула скидки, налога или перевода единиц живёт в десятке ячеек, и после правки её приходится менять везде. Найти выход помогает LAMBDA: по справке Microsoft функция принимает до 253 параметров и вычисление, а сохранённая в Диспетчере имён вызывается по имени, как встроенная^[1]. Её пара — LET: она убирает повторы внутри одной формулы.

Путь к своей функции такой: найти повторяющееся выражение, ввести LET или LAMBDA с вызовом прямо в ячейке, проверить результат на знакомых числах, исправить #ВЫЧИСЛ! и #ЗНАЧ! и сохранить функцию в Диспетчере имён, чтобы применить на своих данных. LAMBDA в Excel не требует программирования: функция остаётся обычной формулой листа, и файл не нужно сохранять в особом формате.

Ключевые факты: LAMBDA в Excel

• LAMBDA превращает формулу с параметрами в функцию, которую вызывают по имени после сохранения в Диспетчере имён^[1].
• LAMBDA принимает до 253 параметров^[1], LET — до 126 пар имя — значение^[4].
• Справка Microsoft относит LAMBDA к Microsoft 365 и Excel 2024^[1], а LET — ещё и к Excel 2021^[4].
• LAMBDA, созданная в ячейке без вызова, возвращает #ВЫЧИСЛ!^[1].
• LET отличается от LAMBDA тем, что её имена действуют только внутри одной формулы.

Как работает LAMBDA в Excel: параметры и вычисление

LAMBDA в Excel превращает формулу с параметрами в свою функцию, которую вызывают по имени. Синтаксис по справке: =LAMBDA([параметр1;параметр2;…];вычисление)^[1]. Параметры идут первыми, вычисление — последним обязательным аргументом.

LAMBDA в Excel проверяют прямо в ячейке: после закрывающей скобки дописывают вызов с аргументами. Формула =LAMBDA(цена;скидка;ОКРУГЛ(цена*(1-скидка);2))(1999;15%) на листе-примере вернула 1699,15^[2].

Примеры взяты из справки Microsoft^[1]; перевод 104 °F на нашем листе тоже дал 40^[2]. Имена параметров подчиняются правилам именования Excel, но точку в имени параметра использовать нельзя^[1].

Если LAMBDA создана в ячейке и не вызвана, справка описывает ошибку #ВЫЧИСЛ!^[1]: на листе =LAMBDA(x;x*2) дала именно её, а =LAMBDA(x;x*2)(5) — 10^[2]. Справка Microsoft относит LAMBDA к Microsoft 365 и Excel 2024, включая Mac^[1]. В Google Таблицах есть LAMBDA и LET с той же идеей^[3]. Параметры называют словами — «цена», «скидка», — чтобы через месяц формула читалась без расшифровки.

Разобравшись с LAMBDA, переходим к её паре — LET, которая работает внутри одной формулы.

Функция LET по шагам: имена внутри одной формулы

LET даёт имена промежуточным значениям, и повторяющееся выражение считается один раз. Синтаксис: =LET(имя1;значение1;[имя2;значение2;…];вычисление) — нужна хотя бы одна пара, а последний аргумент должен быть вычислением, которое возвращает результат^[4].

Пример из справки: =LET(x;5;СУММ(x;1)) возвращает 6^[4]. Польза видна там, где выражение повторяется. Скидка 5 % даётся заказам дороже 10000:

1. Выберите D2 и найдите повторяющуюся часть: в =ЕСЛИ(B2*C2>10000;B2*C2*0,95;B2*C2) произведение написано трижды.
2. Введите =LET(сумма;B2*C2;, чтобы дать произведению имя.
3. Допишите вычисление ЕСЛИ(сумма>10000;сумма*0,95;сумма)) и нажмите Enter.
4. Протяните формулу до D3 и сравните с исходной формулой.

На листе обе записи дали 11400 для кресла и 2000 для лампы^[2]. Справка называет два плюса LET: производительность, потому что повторяющееся выражение вычисляется один раз, и лёгкое чтение формулы^[4]. Имя «сумма» вместо трёх копий B2*C2 делает правку безопасной: множитель меняют в одном месте, а не в трёх.

Имя в LET начинается с буквы и не должно конфликтовать с синтаксисом диапазона^[4]. На листе =LET(c;5;c+1) вернула #ЗНАЧ!^[2].

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

Чем LAMBDA отличается от LET и где хранится своя функция

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

1. Откройте «Формулы → Диспетчер имён» и нажмите «Создать»; на Mac — «Формулы → Задать имя»^[1].
2. В поле «Имя» введите название, например ЦенаСоСкидкой.
3. В поле «Область» оставьте «Книга»; в Excel для Интернета выбрать лист нельзя^[1].
4. В «Примечание» опишите параметры — до 255 знаков, текст появится в подсказке^[1].
5. В поле «Объект ссылки» вставьте формулу без вызова и нажмите «ОК».

После сохранения в любой ячейке книги работает =ЦенаСоСкидкой(B2;C2), а Excel подсказывает имя при вводе, как у встроенных функций. Своя функция в другом файле сама не появится: имя живёт в Диспетчере имён этой книги. Текст из поля «Примечание» Excel показывает в подсказках и в окне «Вставка функции»^[1], поэтому параметры стоит описать там коротко и по делу. LAMBDA принимает и вспомогательные функции массивов: MAP применяет LAMBDA к каждому значению массива и возвращает новый массив^[5].

Остаётся понять, сколько параметров выдержит одна функция и что будет при неверном вызове.

Сколько параметров принимает LAMBDA: 253 имени и 126 пар LET

LAMBDA принимает до 253 параметров, а LET — до 126 пар имя — значение. Справка Microsoft называет оба предела и связанные с ними ошибки^[1].

На листе функция с двумя параметрами, вызванная с одним аргументом, вернула #ЗНАЧ!^[2]. Реальные функции укладываются в несколько параметров: длинный список трудно запомнить и легко перепутать порядок. Неверное число аргументов LAMBDA не прощает, поэтому вызов проверяют на одной строке, прежде чем протягивать формулу по столбцу.

Рекурсия — отдельная граница: LAMBDA может вызывать саму себя по имени, но при слишком глубоком повторе справка описывает #ЧИСЛО!^[1]. Для повторов по массиву проще MAP, которая обходит каждое значение без рекурсии^[5]. MAP справка тоже относит к Microsoft 365 и Excel 2024^[5], а при неверном числе параметров LAMBDA внутри неё функция возвращает #ЗНАЧ!^[5]. В LET следующая пара может использовать имя из предыдущей: так в примере справки результат фильтра получает имя и затем проверяется на пустоту^[4]. Цепочка из нескольких имён заменяет вспомогательные столбцы.

Именно поэтому параметры называют по смыслу — «цена», «скидка», — а не буквами: подсказка из поля «Примечание» покажет их при вводе.

Когда LAMBDA не подойдёт: версия 2021, другие книги, чужие компьютеры

LAMBDA не подойдёт, когда файл откроют в Excel 2021 или старше. В этих случаях выручит обычная формула или LET.

• Если коллеги работают в Excel 2021, на LAMBDA рассчитывать нельзя: справка не включает эту версию в список^[1], а LET там есть^[4].
• Когда функция нужна в другой книге, имя придётся создать там заново: область имени — книга или лист^[1].
• Если формулу открывают в Excel для Интернета и нужна область отдельного листа, выбрать её там нельзя^[1].
• При одноразовом расчёте в одной ячейке достаточно LET: сохранять функцию в Диспетчере имён незачем, а область «Лист» и «Книга» понадобятся только для общей функции^[1].
• Если параметров нужно больше 253, функция вернёт #ЗНАЧ!^[1] — разбейте расчёт на несколько функций.

Список версий в справке — надёжный ориентир: для LAMBDA там указаны Microsoft 365 и Excel 2024 с версиями для Mac^[1]. Перед тем как отправлять книгу с LAMBDA, уточните версию у получателей: при старом выпуске разумнее оставить расчёт в LET или в обычной формуле. LAMBDA и LET — вершина «формульного» пути: дальше начинаются функции массивов вроде MAP. Имена в книге подробнее разобраны на странице о Диспетчере имён и именованных диапазонах, а остальные функции — в обзоре формул Excel от простых к сложным.