Как найти лямбда в экселе

На данный момент в Microsoft Excel имеется почти пять сотен функций рабочего листа, доступных через окно Мастера функций — кнопка fx в строке формул. Это весьма приличный набор, но, тем не менее, почти каждый пользователь рано или поздно сталкивается с ситуацией, когда в этом списке не оказывается нужной ему функции — просто потому, что её нет в Excel.

До сих пор единственным способом решить эту проблему были макросы, т.е. написание своей пользовательской функции (UDF = User Defined Function) на Visual Basic, что требует соответствующей программистской квалификации и бывает, порой, совсем непросто. Однако, с последними обновлениями Office 365 ситуация изменилась в лучшую сторону — в Excel была добавлена специальная «обёрточная» функция LAMBDA. С её помощью задача по созданию собственных функций теперь решается легко и красиво.

Давайте рассмотрим принцип её использования на следующем примере.

Как вы, скорее всего, знаете в Excel есть несколько функции для разборки даты позволяющие определить номер дня, месяца, недели и года для указанной даты. Но почему-то нет функции, определяющий номер квартала, который тоже часто нужен, правда? Давайте исправим этот недостаток и создадим с помощью LAMBDA собственную новую функцию для решения этой задачи.

Шаг 1. Пишем формулу

Начём с того, что вручную привычным образом напишем формулу в ячейке листа, которая вычисляет то, что нам нужно. В случае с номером квартала это можно сделать, например, так:

Шаг 2. Заворачиваем в LAMBDA и тестируем

Теперь пришло время применить новую функцию LAMBDA и завернуть в неё нашу формулу. Синтаксис у функции следующий:

=LAMBDA(Переменная1; Переменная2; … ПеременнаяN ; Выражение)

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

В нашем случае переменная будет только одна — дата, для которой мы считаем номер квартала. Переменную для неё давайте назовем, допустим, d. Тогда завернув нашу формулу в функцию LAMBDA и подменив адрес исходной ячейки A2 на придуманное имя переменной, мы получим:

Обратите внимание, что после такого преобразования наша формула (по сути — правильная!) стала выдавать ошибку, т.к. теперь в неё не передается исходная дата из ячейки А2. Для тестирования и уверенности, можно передать ей аргументы, дописав их после функции LAMBDA в круглых скобках:

Шаг 3. Создаем имя

Теперь самая легкая и приятная часть. Открываем Диспетчер имен на вкладке Формулы (Formulas — Name Manager) и создаём новое имя кнопкой Создать (Create). Придумываем и вводим имя для нашей будущей функции (например, НОМКВАРТАЛА), а в поле Ссылка (Reference) аккуратно копируем из строки формул и вставляем нашу функцию LAMBDA, только без последнего аргумента (А2):

Всё. После нажатия на ОК созданную функцию можно использовать в любой ячейке на любом листе этой книги:

Использование в других книгах

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

LAMBDA и динамические массивы

Пользовательские функции, создаваемые с помощью функции LAMBDA успешно поддерживают работу с новыми динамическими массивами и их функциями (ФИЛЬТР, УНИК, СОРТ), добавленными в Microsoft Excel в 2020 году.

Допустим, мы хотим создать новую пользовательскую функцию, которая сравнивала бы два списка и выдавала разницу между ними — те элементы из первого списка, которые отсутствуют во втором. Жизненная задача, не правда ли? Раньше для такого использовали либо функции а-ля ВПР (VLOOKUP), либо сводные таблицы, либо запросы Power Query. Теперь же можно обойтись одной формулой:

В английской версии это будет:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Здесь функция СЧЁТЕСЛИ подсчитывает количество вхождений каждого элемента первого списка во второй, а затем функция ФИЛЬТР отбирает только те из них, у кого этих вхождений не оказалось. Завернув эту конструкцию в LAMBDA и создав на её основе именованный диапазон с названием, например, ПОИСКОТЛИЧ — мы получим удобную функцию, выдающую результат сравнения двух списков в виде динамического массива:

Если в качестве исходных данных будут не обычные, а «умные» таблицы — наша функция тоже справится без проблем:

Другой пример — динамическое разделение текста путём превращения его в XML и последующего разбора по ячейкам с помощью функции ФИЛЬТР.XML, которое мы недавно разбирали. Чтобы не воспроизводить эту сложную формулу каждый раз вручную, проще будет завернуть её в LAMBDA и создать на её основе динамический диапазон, т.е. новую компактную и удобную функцию, назвав её, например, РАЗДТЕКСТ:

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

=LAMBDA(t;d; ТРАНСП(ФИЛЬТР.XML(«<x><y>»&ПОДСТАВИТЬ(t;d;»</y><y>»)&»</y></x>»;»//y»)))

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

Рекурсивный перебор символов

Все предыдущие примеры демонстрировали только одну, наиболее очевидную, сторону функции LAMBDA — её применение в качестве «обёртки» для заворачивания в неё длинных формул и упрощения их ввода. На самом деле, у LAMBDA есть ещё одна, гораздо более глубокая, сторона, превращающая её почти что в полноценный язык программирования.

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

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

Однако, по сравнению с предыдущими, нерекурсивными примерами, нас ожидают две сложности.

1. Нам придётся придумать название для нашей функции до того, как мы начнём писать её код, т.к. в нём это название уже будет использоваться для вызова функцией самой себя.
2. Ввести такую рекурсивную функцию в ячейку и отлаживать её, указав после LAMBDA аргументы в скобках (как мы делали ранее) не получится. Придётся создавать функцию сразу «с нуля» в Диспетчере Имен (Name Manager).

Назовём нашу функцию, допустим, ОЧИСТКА и хотелось бы, чтобы у неё было два аргумента — текст, который нужно почистить и список исключаемых символов в виде текстовой строки:

Создадим, как делали ранее, на вкладке Формулы в Диспетчере имён именованный диапазон, назовём его ОЧИСТКА и введём в поле Диапазон следующую конструкцию:

=LAMBDA(t;d;ЕСЛИ(d=»»;t;ОЧИСТКА(ПОДСТАВИТЬ(t;ЛЕВСИМВ(d);»»);ПСТР(d;2;255))))

Здесь переменная t — это исходный очищаемый текст, а d — список символов на удаление.

Работает всё это следующим образом:

Итерация 1

Фрагмент ПОДСТАВИТЬ(t;ЛЕВСИМВ(d);»»), как легко догадаться, заменяет в исходном тексте t первый слева символ из удаляемого набора d на пустую текстовую строку, т.е. удаляет букву «А». В качестве промежуточного результата получаем:

Вш зкз н 125 руб.

Итерация 2

Затем функция вызывает сама себя и в качестве входных данных (первый аргумент) получает уже то, что осталось после очистки на предыдущем шаге, а вторым аргументом задавая строку исключаемых символов начиная не с первого, а со второго символа, т.е. «БВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.,» без начальной «А» — это делает функция ПСТР. Как и до этого, функция берет первый слева символ из оставшихся (Б) и заменяет его в данном ей тексте (Зкз н 125 руб.) на пустую строку — получаем в качестве промежуточного результата:

Вш зкз н 125 ру.

Итерация 3

Функция опять вызывает сама себя, получая в качестве первого аргумента то, что осталось от зачищаемого текста на предыдущей итерации (Вш зкз н 125 ру.), а в качестве второго — урезанный слева ещё на один символ набор исключаемых знаков, т.е. «ВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.,» без начальной «Б». Затем опять берёт из этого набора первый символ слева (В) и удаляет его из текста — получаем:

ш зкз н 125 ру.

И так далее — надеюсь, вы ухватили идею. С каждой итерацией список удаляемых символов будет обрезаться слева и мы будем искать и заменять на пустоту очередной символ из набора.

Когда все символы закончатся нам необходимо будет выйти из цикла — эту роль как раз и выполняет функция ЕСЛИ (IF), в которую завернута наша конструкция. Если символов для удаления не осталось (d=»»), то функция не должна больше вызывать саму себя, а просто должна вернуть зачищаемый текст (переменная t) в его финальном виде.

Рекурсивный перебор ячеек

Похожим образом можно реализовать и рекурсивный перебор ячеек в заданном диапазоне. Предположим, что мы хотим создать лямбда-функцию с именем ЗАМЕНАПОСПИСКУ для оптовой замены фрагментов в исходном тексте по заданному списку-справочнику. Выглядеть всё это в результате должно так:

Т.е. у нашей функции ЗАМЕНАПОСПИСКУ будет три аргумента:

1. ячейка с текстом для обработки (исходный адрес)
2. первая ячейка столбца со значениями для поиска из справочника
3. первая ячейка столбца со значениями на замену из справочника

Функция должна проходить сверху-вниз по справочнику и заменять последовательно все варианты из левого столбца Найти на соответствующие варианты из правого столбца Заменить. Реализовать такое можно следующей рекурсивной лямбда-функцией:

Здесь в переменной t хранится исходный текст из очередной ячейки столбца Адрес, а переменные n и z указывают на первые ячейки в столбцах Найти и Заменить, соответственно.

Как и в предыдущем примере, сначала эта функция заменяет в исходном тексте с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) данные по первой строке справочника (т.е. СПб на Санкт-Петербург), а затем вызывает сама-себя, но со сдвигом по справочнику вниз на следующую строку (т.е. заменяет С-Пб на Санкт-Петербург). Затем вызывает себя ещё раз со сдвигом вниз — и заменяет уже Питер на Санкт-Петербург и т.д.

Сдвиг вниз на каждой итериации реализован стандартной экселевской функцией СМЕЩ (OFFSET), у которой в данном случае три аргумента — исходный диапазон, сдвиг по строкам (1) и сдвиг по столбцам (0).

Ну, и как только мы достигаем конца справочника (n=»»), то должны закончить рекурсию — прекращаем вызывать сами себя и выводим то, что накопилось после всех выполненных замен в переменной исходного текста t.

Вот и всё. Никаких хитрых макросов или запросов Power Query — вся задача решается одной функцией.

Ссылки по теме

• Как использовать новые функции динамических массивов Excel: ФИЛЬТР, СОРТ, УНИК
• Замена и зачистка текста функцией ПОДСТАВИТЬ
• Создание макросов и пользовательских функций (UDF) на VBA

Функция ЛЯМБДА (LAMBDA) используется для создания пользовательских, повторно используемых функций и вызова их по выбранному имени. Новая функция доступна во всей книге Excel.

Описание функции

Синтаксис

=ЛЯМБДА([параметр1;параметр2;...;] вычисление)

Аргументы

Замечания

• Если ввести более 253 параметров, Excel вернет ошибку «#ЗНАЧ!»
• Функция ЛЯМБДА анонсирована после выхода Office 2019, поэтому нужно использовать Office по подписке, либо дождаться следующего большого обновления Office
• Если в функцию ЛЯМБДА передано неправильное число аргументов, Excel возвращает ошибку «#ЗНАЧ!»
• Если создать функцию ЛЯМБДА в ячейке, не вызывая ее из ячейки, Excel вернет ошибку «#ВЫЧИСЛ!»
• Имена и параметры «Лямбда» должны следовать синтаксисам имен Excel за одним исключением: не используйте точка (.) в имени параметра

Создание функции ЛЯМБДА

Шаг1. Проверка формулы

Убедитесь, что формула, используемая в аргументе вычисления, работает правильно. Это крайне важно, поскольку при создании функции ЛЯМБДА необходимо убедиться в том, что формула работает, и исключить ее в случае ошибок или неожиданных ситуаций.

Шаг2. Создание Лямбда в ячейке

Лучше всего создать и проверить функцию ЛЯМБДА в ячейке, чтобы убедиться в ее правильности, включая определение и передачу параметров. Чтобы избежать #CALC! добавьте звонок к функции ЛЯМБДА, чтобы немедленно вернуть результат:

=ЛЯМБДА ([параметр1; параметр2; …];вычисление) (вызов функции)

В следующем примере возвращается значение 2.

=LAMBDA(number, number + 1)(1)

Шаг3. Добавление Лямбда в диспетчер имен

Завершив работу с функцией ЛЯМБДА, переместит ее в диспетчер имен для окончательного определения. Таким образом вы даете функции ЛЯМБДА понятное имя, даете описание и позволяет повторно работать с любой ячейкой в книге. Можно также управлять функцией ЛЯМБДА для любого имени, например для строковой константы, диапазона ячеек или таблицы.

Пример

Видео работы функции

The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple, or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros.

Example 1 | Example 2 | Example 3 | More examples

In computer programming, the term LAMBDA refers to an anonymous function or expression. An anonymous function is a function defined without a name. In Excel, the LAMBDA function is first used to create a generic (unnamed) formula. Once a generic version has been created and tested, it is ported to the Name Manager, where it is formally defined and named.

One of the key benefits of a custom LAMBDA function is that the logic contained in the formula exists in just one place. This means there is just one copy of code to update when fixing problems or updating functionality, and changes will automatically propagate to all instances of the LAMBDA function in a workbook.

The LET function is often used together with the LAMBDA function. LET provides a way to declare variables and assign values in a formula. This makes more complicated formulas easier to read by reducing redundant code. The LET function can also improve performance by reducing the number of calculations performed by a formula.

By default, all arguments in a LAMBDA function are required. To create optional arguments, see the ISOMITTED function.

Creating a LAMBDA function

LAMBDA functions are typically created and debugged in the formula bar on a worksheet, then moved into the name manager to assign a name that can be used anywhere in a workbook. 

There are four basic steps to creating and using a custom LAMBDA function:

1. Verify the logic you will use with a standard formula
2. Create and test a generic (unnamed) LAMBDA version of the formula
3. Name and define the LAMBDA formula with the name manager
4. Call the new custom function with the defined name

The examples below discuss these steps in more detail.

Example 1 — basic example

To illustrate how LAMBDA works, let’s begin with a very simple formula:

=x*y // multiply x and y

In Excel, this formula would use typically use cell references like this:

=B5*C5 // with cell references

As you can see, the formula works fine, so we are ready to move on to creating a generic LAMBDA formula (unnamed version). The first thing to consider is if the formula requires inputs (parameters). In this case, the answer is «yes» – the formula requires a value for x, and a value for y. With that established, we start off with the LAMBDA function, and add the required parameters for user input:

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

Next, we need to add the actual calculation, x*y:

=LAMBDA(x,y,x*y)

If you enter the formula at this point, you’ll get a #CALC! error. This happens because the formula has no input values to work with since there are no longer any cell references. To test the formula, we need to use a special syntax like this:

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

This syntax, where parameters are supplied at the end of a LAMBDA function in a separate set of parentheses, is unique to LAMBDA functions. This allows the formula to be tested directly on the worksheet before the LAMBDA is named. In the screen below, you can see that the generic LAMBDA function in F5 returns exactly the same result as the original formula in E5:

We are now ready to name the LAMBDA function with the Name Manager. First, copy the formula, not including the testing parameters at the end. Next, open the Name Manager with the shortcut Control + F3, and click New.

In the New Name dialog, enter the name  «XBYY», leave the scope set to workbook, and paste the formula you copied into the «Refers to» input area. (Tip: Use the tab key to navigate to the «Refers to» field).

Make sure the formula begins with an equals sign (=). Now that the LAMBDA formula has a name, it can be used in the workbook like any other function. In the screen below the formula in G5, copied down, is:

=XBYY(B5,C5)

The screen below shows how things look in the workbook:

The new custom function returns the same result as the other two formulas.

Example 2 — volume of sphere

In this example, we’ll convert a formula to calculate the volume of a sphere into a custom LAMBDA function. The general Excel formula for calculating the volume of a sphere is:

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

where A1 represents radius. The screen below shows this formula in action:

Notice this formula only requires one input (radius) to calculate volume, so our LAMBDA function will only need one parameter (r), which will appear as the first argument. Here is the formula converted to LAMBDA:

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

Back in the worksheet, we’ve replaced the original formula with the generic LAMBDA version. Notice we are using the testing syntax, which allows us to plug in B5 for radius:

The results from the generic LAMBDA formula are exactly the same as the original formula, so the next step is to define and name this LAMBDA formula with the Name Manager, as explained above. The name used for a LAMBDA function can be any valid Excel name. In this case, we’ll name the formula «SphereVolume».

Back in the worksheet, we’ve replaced the generic (unnamed) LAMBDA formula with the named LAMBDA version, and entered B5 for r. Notice the results returned by the custom SphereVolume function are exactly the same as previous results.

Example 3 — count words

In this example, we’ll create a LAMBDA function to count words. Excel doesn’t have a function for this purpose, but you can count words with a cell with a custom formula based on the LEN and SUBSTITUTE functions like this:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Read the detailed explanation here. Here is the formula in action in a worksheet:

Notice we are getting an incorrect count of 1 when the formula is given an empty cell (B10). We’ll address this problem below.

This formula only requires one input, which is the text that contains words. In our LAMBDA function, we’ll name this argument «text». Here is the formula converted to LAMBDA:

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

Notice «text» appears as the first argument, and the calculation is the second and final argument. In the screen below, we’ve replaced the original formula with the generic LAMBDA version. Notice we are using the testing syntax, which allows us to plug in B5 for text:

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

The results from the generic LAMBDA formula are the same as the original formula, so the next step is to define and name this LAMBDA formula with the Name Manager, as explained previously. We’ll name this formula «CountWords».

Below, we’ve replaced the generic (unnamed) LAMBDA formula with the named LAMBDA version, and entered B5 for text. Notice we get exactly the same results.

The formula used in the Name Manager to define CountWords is the same as above, without the testing syntax:

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

Fixing the empty cell problem

As mentioned above, the formula above returns an incorrect count of 1 when a cell is empty. This problem can be fixed by replacing +1 with the code below:

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

Full explanation here. To update the existing named LAMBDA formula, we again need to use the Name Manager:

1. Open the Name Manager
2. Select the name «CountWords» and click «Edit»
3. Replace the «Refers to» code with this formula:

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

Once the Name Manager is closed, the CountWords works correctly on empty cells, as seen below:

Note: by updating the code once in the Name Manager, all instances of the CountWords formula are updated at once. This is a key benefit of custom functions created with LAMBDA –formula updates can be managed in one place.

LAMBDA is a new function available in Excel 365 only.