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

Обновлено: 07.07.2024

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

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

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

Эта возможность в настоящее время доступна только участникам программы предварительной оценки Office.

Синтаксис

=ЛЯМБДА([параметр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. Преобразование градусов по Фаренгейту в градусы Цельсия

Определите в Диспетчере имен следующие данные:

Преобразование градусов по Фаренгейту в градусы Цельсия

=ЛЯМБДА(temp; (5/9) * (Temp-32))

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

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

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

Синтаксис

Аргументы

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

Версия

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

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

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

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

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

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

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

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

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

Пример 1

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

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

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

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

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

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

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

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

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

Пример 2

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

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

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

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

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

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

Пример 3

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

Прочтите подробное объяснение здесь. Вот формула в действии на листе:

Обратите внимание, что мы получаем неверное количество единиц, когда в формуле задана пустая ячейка (B10). Мы рассмотрим эту проблему ниже.

Эта формула требует только одного ввода - текста, содержащего слова. В нашей функции LAMBDA мы назовем этот аргумент «текст». Вот формула, преобразованная в LAMBDA:

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

Результаты общей формулы LAMBDA такие же, как и исходная формула, поэтому следующим шагом является определение и присвоение имени этой формуле LAMBDA с помощью диспетчера имен, как объяснялось ранее. Назовем эту формулу «CountWords».

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

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

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

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

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

Откройте Диспетчер имен
Выберите имя «CountWords» и нажмите «Изменить».
Замените код "Относится к" следующей формулой:

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

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

Microsoft теперь называет электронные таблицы Excel языком программирования, а с добавлением лямбд он стал полным по Тьюрингу.

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

Проект разработала научно-исследовательская группа Calc Intelligence в Кембриджском университете. Они давно поставили задачу превратить формулы Excel в полноценный язык программирования.

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

В данный момент лямбды доступны участникам программы бета-тестирования Excel.

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

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

«Интересно посмотреть, как пользователи будут экспериментировать не только с лямбдами, но с типами данных и динамическими массивами. Мы считаем, что эти новые функции функционального программирования изменят подход к работе в Excel», — написал в корпоративном блоге Энди Гордон, старший научный руководитель Microsoft Research.

«В ближайшей перспективе будут реализованы полностью вложенные массивы и эффективные комбинаторы обработки массивов, такие как MAP и REDUCE, которые принимают лямбда-функции в качестве аргументов, — говорят исследователи. — Кроме того, мы надеемся определять функции не только по одной формуле, но и по целому листу с электронной таблицей, это так называемые функции листа (sheet-defined functions) или даже эластичные функции листа (elastic sheet-defined functions). С практической точки зрения, функции листа идут в ногу с потоком типичного проектирования электронных таблиц, позволяя пользователям определять более крупную функцию с помощью нескольких формул, распределённых по нескольким ячейкам».

В посте Microsoft Research от 25 января приводится больше технических деталей, которые могут быть интересны любителям математики и программистам. Есть также видео:

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

Улучшение Excel как платформы программирования

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

«Формулы Excel в мире часто применяются в разработке, но при этом в Excel отсутствует один из основных принципов программирования – возможность использовать язык формул для определения ваших собственных функций многократного использования», – заявили в Microsoft.

«Идея функции LAMBDA заключается в том, что она может получать массив, а не только одно значение. И она может возвращать массив», – пояснил Брайан Джонс, руководитель группы программ Excel.

«Мы также улучшаем сам синтаксис формул», – добавил Джонс. Иногда действительно встречаются мега-формулы, которые становятся слишком сложными для чтения и использования. С этой проблемой справлялась функция LET для объявления переменных – формулы стали более понятными. Но, как выяснилось, переменные также ускоряют выполнение формул. «То, как написана формула, может сделать ее намного более эффективной», – объяснили в Microsoft.

Многоразовые пользовательские функции

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

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

Вот как это выглядит на практике. Можно взять любую формулу, созданную в Excel, заключить ее в функцию LAMBDA и дать ей имя, например, «MYFUNCTION». Затем в любом месте листа можно будет обратиться к MYFUNCTION, повторно используя эту пользовательскую функцию на всем листе.

Пример многоразовой функции для списка идентификаторов станций, состояние которых закодировано в идентификаторе.

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

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

Рекурсия

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

Функция LAMBDA решает эту проблему, разрешая вызывать функцию внутри функции. Если вы, например, создаете LAMBDA с именем MYFUNCTION, вы можете вызвать MYFUNCTION в пределах определения MYFUNCTION.

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

Пример функции с именем REPLACECHARS, которая ссылается на себя, позволяя перебирать список удаляемых символов

Массивы и типы

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

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

Типы данных – значение, хранящееся в ячейке, больше не является просто строкой или числом. Одна ячейка может содержать расширенный тип данных с большим набором свойств.

Синтаксис

= LAMBDA ([параметр1, параметр2,…,] вычисление)

Параметр – это значение, которое передается функции, например ссылка на ячейку, строка или число. LAMBDA допускает до 253 параметров. Это необязательный аргумент.

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

Интерфейс Excel для программирования

Несмотря на изменения, Excel не получит то, что программисты могли бы признать отладчиком, вроде Microsoft Visual Studio Debugger или Android SDK. А добавление дополнительных формальных конструкций языка программирования не означает превращение Excel в инструмент для опытных разработчиков. Но если раньше сложные функции приходилось писать в Python или JS и «прикручивать» руками к Excel, то сейчас LAMBDA позволит делать то же самое внутри Excel и расширит функционал для продвинутых пользователей.

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