Создание пользовательских функций ЛЯМБДА (LAMBDA) в Excel: от простых формул до рекурсии
Функция ЛЯМБДА (LAMBDA) в Excel открывает новые горизонты для создания пользовательских функций без программирования. Это руководство покажет, как превращать сложные формулы в переиспользуемые блоки, работать с типами данных и даже реализовывать рекурсивные алгоритмы.
Примеры требуют Microsoft 365 или Excel 2024 с поддержкой ЛЯМБДА. Именованная функция хранится в конкретной книге; для использования в другой книге нужно перенести её определение и зависимости.
Функция ЛЯМБДА (LAMBDA) позволяет вам создавать собственные, именованные формулы, которые можно использовать в любой ячейке этой книги, значительно повышая эффективность работы.
Объединение подформул в одну ЛЯМБДА (LAMBDA)
Часто сложную формулу проще построить поэтапно. Рассмотрим задачу: менеджер просит рассчитать продажи на один рабочий день по данным за 2028 год.
Логику можно разбить на шаги:
- Преобразовать название месяца в дату начала месяца.
- Использовать
КОНМЕСЯЦА / EOMONTHдля получения последнего дня месяца. - Использовать
ЧИСТРАБДНИ / NETWORKDAYSдля подсчета рабочих дней (понедельник-пятница). - Разделить продажи на количество рабочих дней.
LET позволяет записать промежуточные расчёты в одной формуле. В некоторых версиях Excel Labs доступен импорт из сетки Import from Grid; расположение команды зависит от версии экспериментальной надстройки. Определение можно также вручную сохранить в диспетчере имён.
- Создайте систему подформул на листе.
- Выделите ячейку с исходными данными, подформулы и итоговый результат.
- Установите надстройку Excel Labs и откройте её с панели инструментов.
- Если в Advanced Formula Environment доступна команда Modules → Import from Grid, откройте её. Иначе используйте определение ниже в диспетчере имён.
При импорте проверьте диапазон, входные параметры и итоговую ячейку. В окне Preview проверьте предложенную формулу, прежде чем сохранять имя. Автоматически предложенные имена и формулы могут требовать правки.
Для имени Sales_Per_Day с областью «Книга» задайте следующее определение. В A4 должно быть полное название месяца на языке, распознаваемом Excel (например, «Январь» для русского варианта или January для английского), в B4 — сумма продаж. Год фиксирован: 2028; праздники не вычитаются.
=ЛЯМБДА(monthText;sales;LET(firstDay;ДАТАЗНАЧ("1 "&monthText&" 2028");lastDay;КОНМЕСЯЦА(firstDay;0);workDays;ЧИСТРАБДНИ(firstDay;lastDay);sales/workDays))=LAMBDA(monthText,sales,LET(firstDay,DATEVALUE("1 "&monthText&" 2028"),lastDay,EOMONTH(firstDay,0),workDays,NETWORKDAYS(firstDay,lastDay),sales/workDays))После сохранения функцию Sales_Per_Day можно использовать как любую встроенную: Русская версия: =Sales_Per_Day(A4;B4)English version: =Sales_Per_Day(A4,B4). Логика хранится в диспетчере имен и переносится вместе с книгой.
Использование ЛЯМБДА (LAMBDA) с типами данных
ЛЯМБДА может получать связанные типы данных, если они поддерживаются учётной записью, языком и платформой Excel. Для типа «География» нужны распознанные города и доступные поля координат; наличие ЛЯМБДА само по себе не гарантирует работу связанных типов.
MILES на иллюстрации — пользовательское имя, а не встроенная функция и не её русский перевод. Вызов =MILES(A2;B2) / =MILES(A2,B2) работает только после определения этой функции в книге. Для расчёта нужно извлечь широту и долготу и выбрать модель расстояния; расстояние по поверхности Земли не равно длине автомобильного маршрута. Полное определение MILES в этом примере не приводится.
Бонус: Рекурсивная ЛЯМБДА (LAMBDA) для "слаггификации" текста
ЛЯМБДА (LAMBDA) может вызывать саму себя, что позволяет реализовать рекурсию. Рассмотрим задачу преобразования заголовка статьи в URL-слаг (только строчные буквы a-z, цифры 0-9 и дефисы).
Обычный подход с множеством функций ПОДСТАВИТЬ / SUBSTITUTE громоздок. Рекурсивная ЛЯМБДА (LAMBDA) решает это одной функцией.
- Функция
SLUGIFYпринимает фразу и начальный индекс (1). - Проверяет, не обработан ли уже весь текст.
- Извлекает текущий символ, проверяет, является ли он буквой или цифрой.
- Заменяет недопустимые символы на дефис.
- Рекурсивно вызывает себя для следующего символа.
- По завершении заменяет множественные дефисы на один.
=ЛЯМБДА(phrase; ndx;
ЕСЛИ(ndx > ДЛСТР(phrase);
ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(phrase; "-"; " ")); " "; "-");
SLUGIFY(
LET(
char; СТРОЧН(ПСТР(phrase; ndx; 1));
allowed; ЕЧИСЛО(НАЙТИ(char; "abcdefghijklmnopqrstuvwxyz0123456789"));
ЛЕВСИМВ(phrase; ndx - 1) &
ЕСЛИ(allowed; char; "-") &
ПРАВСИМВ(phrase; ДЛСТР(phrase) - ndx)
);
ndx + 1
)
)
)=LAMBDA(phrase, ndx,
IF(ndx > LEN(phrase),
SUBSTITUTE(TRIM(SUBSTITUTE(phrase, "-", " ")), " ", "-"),
SLUGIFY(
LET(
char, LOWER(MID(phrase, ndx, 1)),
allowed, ISNUMBER(FIND(char, "abcdefghijklmnopqrstuvwxyz0123456789")),
LEFT(phrase, ndx - 1) &
IF(allowed, char, "-") &
RIGHT(phrase, LEN(phrase) - ndx)
),
ndx + 1
)
)
)
Сохраните определение под именем SLUGIFY с областью «Книга», затем вызовите =SLUGIFY(A2;1) / =SLUGIFY(A2,1). Начальный индекс должен быть 1. Функция оставляет только ASCII-буквы a–z и цифры: кириллица заменяется дефисами, транслитерации нет. Допустимость символа проверяется через ЕЧИСЛО(НАЙТИ(...)) / ISNUMBER(FIND(...)) по явному списку ASCII-символов, без зависимости от системной кодовой страницы. Для длинных текстов возможна ошибка #ЧИСЛ! / #NUM! из-за глубины рекурсии; фиксированный безопасный предел не гарантируется.
Заключение
Функция ЛЯМБДА (LAMBDA) — это мощный инструмент, который превращает Excel в платформу для создания пользовательской логики. Вы можете:
- Упаковывать сложные формулы в переиспользуемые функции.
- Работать с типами данных для решения специализированных задач.
- Реализовывать рекурсивные алгоритмы, ранее доступные только в языках программирования.
Освоение ЛЯМБДА (LAMBDA) открывает путь к созданию библиотек корпоративных функций, повышающих стандартизацию и производительность работы с данными.