Функция лямбда в excel

Добавил пользователь Евгений Кузнецов
Обновлено: 19.09.2024

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

Преобразование формулы в результат вычисления

Если в ячейке нужна не формула, а результат ее вычисления, то можно, конечно, скопировать результат и вставить в ту же ячейку в режиме сохранить только текст, однако есть более быстрый способ. Достаточно просто выделить формулу и нажать клавишу F9. Когда такое вычисление может пригодиться? Вариантов можно подобрать несколько, ярким примеров будет работа с функциями Excel, которые возвращают случайные значения СЛЧИС (RAND) или СЛУЧМЕЖДУ (RANDBETWEEN), поскольку по умолчанию в Excel настроено автоматическое вычисление, то значения функций постоянно обновляются, а с помощью такого нехитрого способа можно результат вычисления быстро зафиксировать.

Преобразование части формулы в результат вычисления

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

Пошаговое вычисление сложной формулы

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

Что такое сложная формула в Excel?

Сложные формулы — это формулы, которые, как правило, содержат в себе несколько функций, которые, в свою очередь, также могут оказаться не самыми простыми к пониманию, но чрезвычайно полезными. Ярким примером является чрезвычайно популярная функция ВПР.

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

Вы можете создать функцию для часто используемой формулы, избавиться от необходимости копировать и вставлять эту формулу (что может приводить к ошибкам), а также эффективно добавлять собственные функции в основную библиотеку функций Excel. Кроме того, функция ЛЯМБДА не требует VBA, макросов или JavaScript, поэтому ее могут использовать также пользователи, не являющиеся программистами.

Синтаксис

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

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

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

Замечания

Имена и параметры функции ЛЯМБДА соответствуют правилам синтаксиса Excel в отношении именования, за одним исключением: запрещено использовать точку (.) в имени параметра. Дополнительные сведения см. в статье Имена в формулах.

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

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

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

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

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

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

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

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

Последовательность действий

Выполните одно из указанных ниже действий.

В Excel для Windows выберите Формулы > Диспетчер имен.

В Excel для Mac выберите Формулы > Задать имя.

Выберите Новое, а затем введите необходимые данные в диалоговом окне Новое имя:

Введите имя функции ЛЯМБДА.

Книга указывается по умолчанию. Также можно выбрать отдельные листы.

Необязательно, но настоятельно рекомендуется. Введите до 255 знаков. Кратко опишите назначение функции, укажите правильное количество и тип аргументов.

Отображается в диалоговом окне Вставить функцию и в виде подсказки (вместе с аргументом Вычисление ) при вводе формулы и использовании функции Автозаполнение формул (Intellisense).

Объект ссылки:

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

Чтобы создать функцию ЛЯМБДА, нажмите ОК.

Чтобы закрыть диалоговое окно Диспетчер имен, нажмите Закрыть.

Синтаксис

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

Замечания

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

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

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

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

Последовательность действий

Выполните одно из указанных ниже действий.

В Excel для Windows выберите Формулы > Диспетчер имен.

В Excel для Mac выберите Формулы > Задать имя.

Выберите Новое, а затем введите необходимые данные в диалоговом окне Новое имя:

Введите имя функции ЛЯМБДА.

Книга указывается по умолчанию. Также можно выбрать отдельные листы.

Объект ссылки:

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

Чтобы создать функцию ЛЯМБДА, нажмите ОК.

Чтобы закрыть диалоговое окно Диспетчер имен, нажмите Закрыть.

На данный момент в 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 и создав на её основе именованный диапазон с названием, например, ПОИСКОТЛИЧ - мы получим удобную функцию, выдающую результат сравнения двух списков в виде динамического массива:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итерация 1

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

Вш зкз н 125 руб.

Итерация 2

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

Вш зкз н 125 ру.

Итерация 3

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

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

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

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

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

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

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

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

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

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

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

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

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

Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.

You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use.

Syntax

=LAMBDA([parameter1, parameter2, …,] calculation)

A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional.

calculation

The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required.

Remarks

Lambda names and parameters follow the Excel syntax rules for names, with one exception: don't use a period (.) in a parameter name. For more information, see Names in formulas.

Make sure you follow best practices when creating a LAMBDA function as you do with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted . Also, when you use the Evaluate command, Excel immediately returns the result of the LAMBDA function and you can't step into it. For more information, see Detect errors in formulas.

Create a LAMBDA function

Here's a step-by-step process to follow that helps make sure your Lambda works as you intended and closely resembles the behavior of a native Excel function.

Make sure the formula you use in the calculation argument is working correctly. This is vital because as you create the LAMBDA function, you want to ensure that the formula works and you can rule that out if you encounter errors or unexpected behavior. For more information, see Overview of formulas in Excel and Create a simple formula in Excel.

=LAMBDA function ([parameter1, parameter2, . ],calculation) (function call)

The following example returns a value of 2.

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

Once you have finalized the LAMBDA function, move it to the Name Manager for final definition. By doing so, you give the LAMBDA function a meaningful name, provide a description, and make it re-usable from any cell in the workbook. You can also manage the LAMBDA function as you can for any name, such as a string constant, a cell range, or a table.

Do one of the following:

In Excel for Windows, select Formulas > Name Manager.

In Excel for Mac, select Formulas > Define Name.

Select New, and then enter information in the New Name dialog box:

Enter the name for the LAMBDA function.

Workbook is the default. Individual sheets are also available.

Optional, but highly recommended. Enter up to 255 characters. Briefly describe the purpose of the function and the correct number and type of arguments.

Displays in the Insert Function dialog box and as a tooltip (along with the Calculation argument) when you type a formula and use Formula Autocomplete (also called Intellisense).

Enter the LAMBDA function. Press F2 to edit the text and prevent automatic cell reference insertion.

Читайте также: