LAMBDA - новая суперфункция Excel

На данный момент в 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 на придуманное имя переменной, мы получим:

Заворачиваем в LAMBDA

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

Добавляем аргументы к LAMBDA

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

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

Создаем имя с функцией LAMBDA

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

Готовая функция в выпадающем списке

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

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

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

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

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

Сравнение списков

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

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

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

Готовая функция ПОИСКОТЛИЧ

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

Умные таблицы и функция 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 - вся задача решается одной функцией.

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




21.10.2021 16:27:09
Спасибо! Но пример не скачивается :( Его нет.
22.10.2021 05:31:49
Подтверждаю.
21.10.2021 21:30:57
Николай, добрый день!
Функция просто революционная, но не совсем понятно когда и у каких лицензий она появится. У меня офис 365 Enterprise, но такой функции нет. Поделитесь, пожалуйста, информацией, как можно функцию получить.
Спасибо!
22.10.2021 05:16:18
В MS Оffice'21 нет лямбды.
Всегда на шаг позади от 365-го.
22.10.2021 17:41:31
В нашем 365м тоже нет
22.10.2021 07:37:05
очень жаль, что так.
22.10.2021 08:43:26
Николай, спасибо вам огромное за многолетний труд. Не нашёл ни одного ресурса, даже близко приближающегося по качеству наполнения к вашему.

Пример скачивается, но вот функции даже в октябрьской сборке Office 365 нет, к сожалению.

Подскажите, пожалуйста, когда её ждать?
24.10.2021 11:53:13
Поддерживаю, очень благодарен Николаю за ту простоту, когда всё знаешь ;-),  но было бы не лишним упомянуть, что это пока "инсайдерская" фича (см. анонс). Помню, как долго ждал динамические массивы в своё время)
Я так понимаю, обратная совместимость тоже пока не заработала:


(у меня подписка "Актуальный канал" / Current Channel)
01.11.2021 10:48:07
У меня Актуальный канал (предварительная версия). Видимо, дело в этом.
25.10.2021 16:31:19
Граждане, ну приложите хоть немножко усилий прежде чем задавать вопросы:
Ответ всем от Microsoft имеется на официальной странице функции:
This feature is available to Office Insiders only right now.
01.11.2021 10:42:28
Так я не инсайдер, вроде, но у меня есть :)
01.11.2021 12:39:44
Ну, дык, предварительная версия это оно и есть. Разве нет?
02.11.2021 12:22:57
В excel 2019 также нет lambda-функции.
05.11.2021 10:33:34
Млин, у меня Office 365 по подписке и нет этой функции)
22.11.2021 16:19:37
еще в 365 появилась функция LET (). Уже доступна.
https://msoffice-prowork.com/ref/excel/excelfunc/insider/let/
Наверх