Как использовать функцию ЛЯМБДА в Excel -

Содержание

Резюме

Функция Excel LAMBDA позволяет создавать настраиваемые функции, которые можно повторно использовать в книге без VBA или макросов.

Цель

Создать собственную функцию

Возвращаемое значение

Как определено формулой

Синтаксис

= ЛЯМБДА (параметр,…, расчет)

Аргументы

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

Версия

Excel 365

Примечания по использованию

В компьютерном программировании LAMBDA относится к анонимной функции или выражению. Анонимная функция - это функция, определенная без имени. В Excel функция LAMBDA предоставляет способ определения и инкапсуляции определенных функций формулы, как и функция Excel. После определения функция LAMBDA может быть названа и повторно использована в другом месте книги. Другими словами, функция LAMBDA - это способ создания пользовательских функций.

Одним из ключевых преимуществ пользовательской функции LAMBDA является то, что логика, содержащаяся в формуле, существует только в одном месте. Это означает, что при устранении проблем или обновлении функциональности необходимо обновить только одну копию кода, и изменения будут автоматически распространяться на все экземпляры функции LAMBDA в книге. Функция LAMBDA не требует VBA или макросов.

Пример 1 | Пример 2 | Пример 3

Создание LAMBDA-функции

Функции LAMBDA обычно создаются и отлаживаются в строке формул на листе, а затем перемещаются в диспетчер имен, чтобы присвоить имя, которое можно использовать в любом месте книги.

Существует четыре основных шага для создания и использования настраиваемой формулы на основе функции ЛЯМБДА:

  1. Проверьте логику, которую вы будете использовать, со стандартной формулой
  2. Создайте и протестируйте универсальную (безымянную) версию формулы LAMBDA.
  3. Назовите и определите формулу LAMBDA с помощью диспетчера имен
  4. Протестируйте новую пользовательскую функцию, используя определенное имя

В приведенных ниже примерах эти шаги рассматриваются более подробно.

Пример 1

Чтобы проиллюстрировать, как работает LAMBDA, давайте начнем с очень простой формулы:

=x*y // multiple x and y

В Excel эта формула обычно использует следующие ссылки на ячейки:

=B5*C5 // with cell references

Как видите, формула работает нормально, поэтому мы готовы перейти к созданию общей формулы LAMBDA (безымянная версия). В первую очередь следует учитывать, требует ли формула входных данных (параметров). В этом случае ответ - «да» - формула требует значения для x и значения для y. Установив это, мы начинаем с функции LAMBDA и добавляем необходимые параметры для пользовательского ввода:

=LAMBDA(x,y // begin with input parameters

Затем нам нужно добавить фактическое вычисление x * y:

=LAMBDA(x,y,x*y)

Если вы введете формулу на этом этапе, вы получите #CALC! ошибка. Это происходит потому, что формула не имеет входных значений, с которыми можно было бы работать, так как больше нет ссылок на ячейки. Чтобы проверить формулу, нам нужно использовать специальный синтаксис вроде этого:

=LAMBDA(x,y,x*y)(B5,C5) // testing syntax

Этот синтаксис, в котором параметры указываются в конце функции LAMBDA в отдельном наборе скобок, уникален для функций LAMBDA. Это позволяет проверять формулу непосредственно на рабочем листе до того, как будет названа LAMBDA. На приведенном ниже экране вы можете видеть, что общая функция LAMBDA в F5 возвращает точно такой же результат, что и исходная формула в E5:

Теперь мы готовы назвать функцию LAMBDA с помощью диспетчера имен. Сначала выберите формулу, * не включая * параметры тестирования в конце. Затем откройте диспетчер имен с помощью сочетания клавиш Control + F3 и нажмите «Создать».

В диалоговом окне «Новое имя» введите имя «XBYY», оставьте область действия равной книге и вставьте скопированную формулу в область ввода «Относится к».

Убедитесь, что формула начинается со знака равенства (=). Теперь, когда у формулы LAMBDA есть имя, ее можно использовать в книге, как и любую другую функцию. На экране ниже скопированная формула в G5 выглядит так:

Новая настраиваемая функция возвращает тот же результат, что и две другие формулы.

Пример 2

В этом примере мы преобразуем формулу для вычисления объема сферы в пользовательскую функцию LAMBDA. Общая формула Excel для расчета объема сферы:

=4/3*PI()*A1^3 // volume of sphere

где A1 представляет радиус. На экране ниже показана эта формула в действии:

Обратите внимание, что этой формуле требуется только один вход (радиус) для вычисления объема, поэтому нашей функции LAMBDA потребуется только один параметр (r), который будет отображаться как первый аргумент. Вот формула, преобразованная в LAMBDA:

=LAMBDA(r,4/3*PI()*r^3) // generic lambda

Вернувшись к рабочему листу, мы заменили исходную формулу общей версией LAMBDA. Обратите внимание, что мы используем синтаксис тестирования, который позволяет нам подключить B5 для радиуса:

Результаты общей формулы LAMBDA точно такие же, как и исходная формула, поэтому следующим шагом будет определение и присвоение имени этой формуле LAMBDA с помощью диспетчера имен, как описано выше. Имя, используемое для функции LAMBDA, может быть любым допустимым именем Excel. В этом случае мы назовем формулу SphereVolume.

Вернувшись на рабочий лист, мы заменили общую (безымянную) формулу LAMBDA на именованную версию LAMBDA и ввели B5 для r. Обратите внимание, что результаты, возвращаемые настраиваемой функцией SphereVolume, точно такие же, как и предыдущие результаты.

Пример 3

В этом примере мы создадим функцию LAMBDA для подсчета слов. В Excel нет функции для этой цели, но вы можете подсчитывать слова с помощью ячейки с настраиваемой формулой, основанной на функциях LEN и SUBSTITUTE, например:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+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 такие же, как и исходная формула, поэтому следующим шагом является определение и присвоение имени этой формуле LAMBDA с помощью диспетчера имен, как объяснялось ранее. Назовем эту формулу «CountWords».

Ниже мы заменили общую (безымянную) формулу LAMBDA на именованную версию LAMBDA и ввели B5 для текста. Обратите внимание, что мы получаем точно такие же результаты.

Формула, используемая в диспетчере имен для определения CountWords, такая же, как и выше, без синтаксиса тестирования:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Устранение проблемы с пустой ячейкой

Как упоминалось выше, приведенная выше формула возвращает неправильный счетчик 1, когда ячейка пуста. Эту проблему можно решить, заменив +1 на приведенный ниже код:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Полное объяснение здесь. Чтобы обновить существующую именованную формулу LAMDA, нам снова нужно использовать диспетчер имен:

  1. Откройте Диспетчер имен
  2. Выберите имя «CountWords» и нажмите «Изменить».
  3. Замените код "Относится к" следующей формулой:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))

После закрытия диспетчера имен CountWords корректно работает с пустыми ячейками, как показано ниже:

Примечание: при обновлении кода один раз в диспетчере имен все экземпляры формулы CountWords обновляются одновременно. Это ключевое преимущество настраиваемых функций, созданных с помощью LAMBDA - обновления формул можно управлять в одном месте.

Интересные статьи...