Функция LAMBDA в Excel позволяет создавать пользовательские функции, которые можно повторно использовать в книге без VBA или макросов.
Что делает функция LAMBDA в Excel?
Функция LAMBDA в Excel создает пользовательскую функцию.
Возвращаемое значение
Как определено формулой.
Аргументы
- parameter — входное значение функции.
- calculation — расчет, выполняемый как результат функции. Должно быть, последний аргумент.
Синтаксис
=LAMBDA(parameter, …, calculation)
Примечания по использованию
Функция ЛАМБДА позволяет создать пользовательскую функцию в Excel. После определения и имени функцию можно использовать в любом месте книги. LAMBDA-функции могут быть очень простыми или довольно сложными, объединяя множество функций Excel в одну формулу. Пользовательская функция LAMBDA не требует VBA или макросов.
В компьютерном программировании термин LAMBDA относится к анонимной функции или выражению. Анонимная функция — это функция, определенная без имени. В Excel функция сначала используется для создания общей (безымянной) формулы. После того как универсальная версия создана и протестирована, она переносится в диспетчер имен, где ей формально определяется и присваивается имя.
Одним из ключевых преимуществ пользовательской функции LAMBDA является то, что логика, содержащаяся в формуле, существует только в одном месте. Это означает, что при устранении проблем или обновлении функциональности требуется обновить только одну копию кода, а изменения будут автоматически распространяться на все экземпляры функции LAMBDA в книге.
Функция LET часто используется вместе с функцией LAMBDA. LET предоставляет возможность объявлять переменные и присваивать значения в формуле. Это упрощает чтение более сложных формул за счет сокращения избыточного кода. Функция LET также может повысить производительность за счет сокращения количества вычислений, выполняемых по формуле.
По умолчанию все аргументы функции LAMBDA являются обязательными. Чтобы создать необязательные аргументы, используйте функцию ISOMITTED.
Создание функции LAMBDA
Функции LAMBDA обычно создаются и отлаживаются в строке формул на листе, а затем перемещаются в диспетчер имен, чтобы назначить имя, которое можно использовать в любом месте книги.
Создание и использование пользовательской функции LAMBDA состоит из четырех основных шагов:
- Проверьте логику, которую вы будете использовать, со стандартной формулой.
- Создайте и протестируйте общую (безымянную) версию формулы LAMBDA.
- Назовите и определите формулу с помощью менеджера имен.
- Вызов новой пользовательской функции с определенным именем
В примерах ниже эти шаги рассматриваются более подробно.
Пример 1 – базовый пример
Чтобы проиллюстрировать, как работает LAMBDA, начнем с очень простой формулы:
=x*y // умножьте x и y
В Excel эта формула обычно использует такие ссылки на ячейки:
=B5*C5 // со ссылками на ячейки
Как видите, формула работает нормально, поэтому мы готовы перейти к созданию общей формулы LAMBDA (безымянная версия). Первое, что следует учитывать, — требуются ли для формулы входные данные (параметры). В этом случае ответ «да» — для формулы требуется значение x и значение y. После этого мы начнем с функции LAMBDA и добавим необходимые параметры для пользовательского ввода:
=LAMBDA(x,y // начните с входных параметров
Далее нам нужно добавить фактический расчет x*y:
=LAMBDA(x,y,x*y)
Если вы введете формулу на этом этапе, вы получите #CALC! ошибка. Это происходит потому, что в формуле нет входных значений для работы, поскольку больше нет ссылок на ячейки. Чтобы проверить формулу, нам нужно использовать специальный синтаксис, подобный этому:
=LAMBDA(x,y,x*y)(B5,C5) //синтаксис тестирования
Этот синтаксис, в котором параметры передаются в конце функции LAMBDA в отдельных скобках, уникален для функций. Это позволяет проверить формулу непосредственно на листе до того, как будет присвоено имя LAMBDA. На экране ниже вы можете видеть, что общая функция в F5 возвращает точно тот же результат, что и исходная формула в E5:
Теперь мы готовы назвать функцию LAMBDA с помощью диспетчера имен. Сначала скопируйте формулу, не включая в конец параметры тестирования. Затем откройте диспетчер имен с помощью сочетания клавиш Control + F3 и нажмите «Создать».
В диалоговом окне «Новое имя» введите имя «XBYY», оставьте в качестве области действия рабочую книгу и вставьте скопированную формулу в область ввода «Ссылается на». (Совет: используйте клавишу табуляции, чтобы перейти к полю «Ссылается на»).
Убедитесь, что формула начинается со знака равенства (=). Теперь, когда у формулы LAMBDA есть имя, ее можно использовать в книге, как любую другую функцию. На экране ниже скопированная формула в G5:
=XBYY(B5,C5)
На экране ниже показано, как все выглядит в книге:
Новая пользовательская функция возвращает тот же результат, что и две другие формулы.
Пример 2 – объем сферы
В этом примере мы преобразуем формулу для расчета объема сферы в пользовательскую функцию LAMBDA. Общая формула Excel для расчета объема сферы:
=4/3*PI()*A1^3 // volume of sphere
где A1 представляет собой радиус. На экране ниже показана эта формула в действии:
Обратите внимание, что для расчета объема этой формуле требуется только один ввод (радиус), поэтому нашей функции LAMBDA потребуется только один параметр (r), который будет отображаться в качестве первого аргумента. Вот формула, преобразованная в LAMBDA:
=LAMBDA(r,4/3*PI()*r^3) // общий лямбда-код
Вернувшись на рабочий лист, мы заменили исходную формулу общей версией LAMBDA. Обратите внимание, что мы используем синтаксис тестирования, который позволяет нам подключить B5 для радиуса:
Результаты общей формулы LAMBDA точно такие же, как исходная формула, поэтому следующим шагом будет определение и присвоение имени этой формуле с помощью диспетчера имен , как описано выше. Имя, используемое для функции LAMBDA, может быть любым допустимым именем Excel. В данном случае мы назовем формулу «SphereVolume».
Вернувшись на рабочий лист, мы заменили общую (безымянную) формулу LAMBDA именованной версией и ввели B5 для r. Обратите внимание, что результаты, возвращаемые пользовательской функцией SphereVolume, точно такие же, как и предыдущие результаты.
Пример 3 – подсчет слов
В этом примере мы создадим функцию LAMBDA для подсчета слов. В Excel нет функции для этой цели, но вы можете подсчитать слова в ячейке с помощью специальной формулы, основанной на функциях LEN and SUBSTITUTE, например:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Вот формула в действии на рабочем листе:
Обратите внимание, что мы получаем неправильное значение 1, когда в формуле указана пустая ячейка (B10). Мы рассмотрим эту проблему ниже.
Для этой формулы требуется только один ввод — текст, содержащий слова. В нашей функции LAMBDA мы назовем этот аргумент «текст». Вот формула, преобразованная в LAMBDA:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)
Обратите внимание, что «текст» отображается в качестве первого аргумента, а вычисление — второго и последнего аргумента. На экране ниже мы заменили исходную формулу общей версией LAMBDA. Обратите внимание, что мы используем синтаксис тестирования, который позволяет нам подключать B5 для текста:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)(B5)
Результаты общей формулы LAMBDA такие же, как и исходная формула, поэтому следующим шагом будет определение и присвоение имени этой формуле с помощью диспетчера имен , как объяснялось ранее. Мы назовем эту формулу «CountWords».
Ниже мы заменили общую (безымянную) формулу именованной версией LAMBDA и ввели текст B5. Обратите внимание, что мы получаем точно такие же результаты.
Формула, используемая в диспетчере имен для определения CountWords, аналогична приведенной выше, без проверочного синтаксиса:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)
Решение проблемы с пустой ячейкой
Как упоминалось выше, приведенная выше формула возвращает неправильное значение 1, когда ячейка пуста. Эту проблему можно решить, заменив +1 на код ниже:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Чтобы обновить существующую именованную формулу LAMBDA, нам снова нужно использовать диспетчер имен:
- Откройте диспетчер имен
- Выберите имя «CountWords» и нажмите «Изменить».
- Замените код «Относится к» этой формулой:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))
После закрытия диспетчера имен CountWords корректно работает с пустыми ячейками, как показано ниже:
Примечание. При однократном обновлении кода в диспетчере имен все экземпляры формулы CountWords обновляются одновременно. Это ключевое преимущество пользовательских функций, созданных с помощью LAMBDA: обновлениями формул можно управлять из одного места.
