С добавлением лямбда-функций язык программирования Excel стал полным по Тьюрингу
Microsoft теперь называет электронные таблицы Excel языком программирования, а с добавлением лямбд он стал полным по Тьюрингу.
Язык программирования считается полным по Тьюрингу, если на нём можно реализовать любой возможный алгоритм. Именно эту возможность реализуют лямбды.
Проект разработала научно-исследовательская группа Calc Intelligence в Кембриджском университете. Они давно поставили задачу превратить формулы Excel в полноценный язык программирования.
LAMBDA могут ссылаться на другие функции лямбды, с любой глубиной вложенных ссылок, даже рекурсивно. Именно это даёт возможность выразить на языке программирования Excel любое вычисление.
В данный момент лямбды доступны участникам программы бета-тестирования Excel.
Изначально исследователи из Укембриджа скептически отнеслись к идее выкатить лямбды на массовую аудиторию. Они считали, что это слишком сложная функция для среднего пользователя Excel. Но потом получили массу восторженных отзывов от тестеров — и изменили своё мнение. Оказалось, что пользователи нашли массу интересных применений для LAMBDA, причём многие из них исследователи даже не могли себе представить.
Кроме того, особые навыки требуются для написания лямбд, а не для их использования. Таким образом, программисты могут расширить сферу применения приложений Excel, не вызывая дополнительных неудобств у конечных пользователей, которые просто воспользуются результатом.
«Интересно посмотреть, как пользователи будут экспериментировать не только с лямбдами, но с типами данных и динамическими массивами. Мы считаем, что эти новые функции функционального программирования изменят подход к работе в Excel», — написал в корпоративном блоге Энди Гордон, старший научный руководитель Microsoft Research.
По мнению создателей, это открывает богатые возможности для программирования в Excel, потому что аудитория Excel «на порядок больше, чем количество всех программистов в мире на C, C++, C#, Java и Python, вместе взятых».
«В ближайшей перспективе будут реализованы полностью вложенные массивы и эффективные комбинаторы обработки массивов, такие как MAP и REDUCE, которые принимают лямбда-функции в качестве аргументов, — говорят исследователи. — Кроме того, мы надеемся определять функции не только по одной формуле, но и по целому листу с электронной таблицей, это так называемые функции листа (sheet-defined functions) или даже эластичные функции листа (elastic sheet-defined functions). С практической точки зрения, функции листа идут в ногу с потоком типичного проектирования электронных таблиц, позволяя пользователям определять более крупную функцию с помощью нескольких формул, распределённых по нескольким ячейкам».
В посте Microsoft Research от 25 января приводится больше технических деталей, которые могут быть интересны любителям математики и программистам. Есть также видео:
Давайте рассмотрим принцип её использования на следующем примере.
Как вы, скорее всего, знаете в Excel есть несколько функции для разборки даты позволяющие определить номер дня, месяца, недели и года для указанной даты. Но почему-то нет функции, определяющий номер квартала, который тоже часто нужен, правда? Давайте исправим этот недостаток и создадим с помощью LAMBDA собственную новую функцию для решения этой задачи.
Шаг 1. Пишем формулу
Начём с того, что вручную привычным образом напишем формулу в ячейке листа, которая вычисляет то, что нам нужно. В случае с номером квартала это можно сделать, например, так:
Шаг 2. Заворачиваем в LAMBDA и тестируем
Теперь пришло время применить новую функцию LAMBDA и завернуть в неё нашу формулу. Синтаксис у функции следующий:
где сначала перечисляются имена одной или нескольких переменных, а последним аргументом всегда идёт формула или вычисляемое выражение, которое их использует. Имена переменных не должны быть похожи на адреса ячеек и не должны содержать точки.
Шаг 3. Создаем имя
Всё. После нажатия на ОК созданную функцию можно использовать в любой ячейке на любом листе этой книги:
Использование в других книгах
LAMBDA и динамические массивы
Пользовательские функции, создаваемые с помощью функции LAMBDA успешно поддерживают работу с новыми динамическими массивами и их функциями (ФИЛЬТР, УНИК, СОРТ), добавленными в Microsoft Excel в 2020 году.
В английской версии это будет:
Рекурсивный перебор символов
Однако, по сравнению с предыдущими, нерекурсивными примерами, нас ожидают две сложности.
Создадим, как делали ранее, на вкладке Формулы в Диспетчере имён именованный диапазон, назовём его ОЧИСТКА и введём в поле Диапазон следующую конструкцию:
Работает всё это следующим образом:
Итерация 1
Фрагмент ПОДСТАВИТЬ(t;ЛЕВСИМВ(d);»»), как легко догадаться, заменяет в исходном тексте t первый слева символ из удаляемого набора d на пустую текстовую строку, т.е. удаляет букву «А». В качестве промежуточного результата получаем:
Итерация 2
Итерация 3
Рекурсивный перебор ячеек
Похожим образом можно реализовать и рекурсивный перебор ячеек в заданном диапазоне. Предположим, что мы хотим создать лямбда-функцию с именем ЗАМЕНАПОСПИСКУ для оптовой замены фрагментов в исходном тексте по заданному списку-справочнику. Выглядеть всё это в результате должно так:
Т.е. у нашей функции ЗАМЕНАПОСПИСКУ будет три аргумента:
Функция должна проходить сверху-вниз по справочнику и заменять последовательно все варианты из левого столбца Найти на соответствующие варианты из правого столбца Заменить. Реализовать такое можно следующей рекурсивной лямбда-функцией:
Λ Греческая заглавная буква лямбда
U+039B
Нажмите, чтобы скопировать и вставить символ
Техническая информация
Значение символа
Греческая заглавная буква лямбда. Греческое и коптское письмо.
Символ «Греческая заглавная буква лямбда» был утвержден как часть Юникода версии 1.1 в 1993 г.
Свойства
| Версия | 1.1 |
| Блок | Греческое и коптское письмо |
| Тип парной зеркальной скобки (bidi) | Нет |
| Композиционное исключение | Нет |
| Изменение регистра | 03BB |
| Простое изменение регистра | 03BB |
Похожие символы
Греческая заглавная буква альфа
Греческая заглавная буква омикрон
Греческая строчная буква лямбда
Греческая заглавная буква бета
Греческая заглавная буква омега
Греческая заглавная буква пси
Греческая заглавная буква хи
Греческая заглавная буква фи
Греческая заглавная буква ипсилон
Греческая заглавная буква сигма
Греческая заглавная буква ро
Греческая заглавная буква пи
Греческая заглавная буква тау
Греческая заглавная буква кси
Греческая заглавная буква эта
Кодировка
| Кодировка | hex | dec (bytes) | dec | binary |
|---|---|---|---|---|
| UTF-8 | CE 9B | 206 155 | 52891 | 11001110 10011011 |
| UTF-16BE | 03 9B | 3 155 | 923 | 00000011 10011011 |
| UTF-16LE | 9B 03 | 155 3 | 39683 | 10011011 00000011 |
| UTF-32BE | 00 00 03 9B | 0 0 3 155 | 923 | 00000000 00000000 00000011 10011011 |
| UTF-32LE | 9B 03 00 00 | 155 3 0 0 | 2600665088 | 10011011 00000011 00000000 00000000 |
Наборы с этим символом:
© Таблица символов Юникода, 2012–2021.
Юникод® — это зарегистрированная торговая марка консорциума Юникод в США и других странах. Этот сайт никак не связан с консорциумом Юникод. Официальный сайт Юникода располагается по адресу www.unicode.org.
Мы используем ?cookie, чтобы сделать сайт максимально удобным для вас. Подробнее
Функция ЛЯМБДА
С помощью функции ЛЯМБДА можно создавать пользовательские функции для повторного использования и вызывать их по понятным именам. Новая функция доступна во всей книге и вызывается так же, как и основные функции Excel.
Вы можете создать функцию для часто используемой формулы, избавиться от необходимости копировать и вставлять эту формулу (что может приводить к ошибкам), а также эффективно добавлять собственные функции в основную библиотеку функций Excel. Кроме того, функция ЛЯМБДА не требует VBA, макросов или JavaScript, поэтому ее могут использовать также пользователи, не являющиеся программистами.
Эта возможность в настоящее время доступна только участникам программы предварительной оценки Office.
Синтаксис
=ЛЯМБДА([параметр1; параметр2; …;] вычисление)
Значение, которое вы хотите передать функции, например ссылку на ячейку, строку или число. Вы можете ввести до 253 параметров. Этот аргумент является необязательным.
Формула, которую необходимо выполнить и вывести в качестве результата выполнения функции. Это должен быть последний аргумент и он должен возвращать результат. Это обязательный аргумент.
Замечания
В случае ввода более 253 параметров Excel возвращает ошибку «#ЗНАЧ!».
В случае передачи функции ЛЯМБДА неправильного количества аргументов Excel возвращает ошибку «#ЗНАЧ!».
Если вызвать ЛЯМБДА-функцию из самой себя, применив циклический вызов, Excel возвращает ошибку «#ЧИСЛО!».
Если создать функцию ЛЯМБДА в ячейке, не вызвав ее также из самой ячейки, Excel возвращает ошибку «#ВЫЧИС!».
Имена и параметры функции ЛЯМБДА соответствуют правилам синтаксиса Excel в отношении именования, за одним исключением: запрещено использовать точку (.) в имени параметра. Дополнительные сведения см. в статье Имена в формулах.
При создании функции ЛЯМБДА, как и в случае с любой основной формулой Excel, необходимо придерживаться рекомендаций, например, передавать правильное количество и тип аргументов, следить за соответствием открывающих и закрывающих скобок и вводить числа в неформатированном виде. Кроме того, при использовании команды Вычислить Excel немедленно возвращает результат функции ЛЯМБДА, и вы не сможете выполнить ее пошагово. Дополнительные сведения см. в статье Обнаружение ошибок в формулах.
Создание функции ЛЯМБДА
Ниже описана пошаговая процедура, выполнив которую, вы обеспечите правильную работу функции ЛЯМБДА, которая будет выполняться аналогично основной функции Excel.
Убедитесь в правильности работы формулы, используемой вами при вычислении аргумента. Это крайне важно, так как при создании функции ЛЯМБДА вам необходимо обеспечить, чтобы формула работала надлежащим образом, и в случае возникновения ошибок или непредвиденного поведения вы могли быть уверены, что не формула является тому причиной. Дополнительные сведения см. в статье Полные сведения о формулах в Excelи Создание простой формулы в Excel.
Рекомендуется создавать и испытывать свою функцию ЛЯМБДА в ячейке, чтобы убедиться в правильности ее работы, включая определение и передачу параметров. Чтобы избежать ошибки #CALC!, добавьте вызов функции ЛЯМБДА, чтобы немедленно вернуть результат:
В приведенном ниже примере возвращается значение 2.
=LAMBDA(number, number + 1)(1)
Завершив создание функции ЛЯМБДА, переместите ее в диспетчер имен для окончательного определения. Таким образом вы сможете присвоить функции ЛЯМБДА понятное имя, добавить описание и обеспечить возможность ее повторного использования в любой ячейке книги. Вы также можете управлять функцией ЛЯМБДА точно так же, как и любым именем, например, строковой константы, диапазона ячеек или таблицы.
Выполните одно из указанных ниже действий.
В Excel для Windows выберите Формулы > Диспетчер имен.
В Excel для Mac выберите Формулы > Задать имя.
Выберите Новое, а затем введите необходимые данные в диалоговом окне Новое имя:
Введите имя функции ЛЯМБДА.
Книга указывается по умолчанию. Также можно выбрать отдельные листы.
Необязательно, но настоятельно рекомендуется. Введите до 255 знаков. Кратко опишите назначение функции, укажите правильное количество и тип аргументов.
Отображается в диалоговом окне Вставить функцию и в виде подсказки (вместе с аргументом Вычисление ) при вводе формулы и использовании функции Автозаполнение формул (Intellisense).
Введите функцию ЛЯМБДА. Нажмите F2, чтобы изменить текст и запретить автоматическую вставку ссылок на ячейки.
Чтобы создать функцию ЛЯМБДА, нажмите ОК.
Чтобы закрыть диалоговое окно Диспетчер имен, нажмите Закрыть.
Дополнительные сведения см. в статье Использование Диспетчера имен.
Примеры
Определите в Диспетчере имен следующие данные:
Преобразование градусов по Фаренгейту в градусы Цельсия
=ЛЯМБДА(temp; (5/9) * (Temp-32))
Скопируйте данные примера из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. При необходимости измените ширину столбцов, чтобы видеть все данные.
Microsoft позволит создавать пользовательские функции в Excel
Разработчики 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, вместо того, чтобы искать каждый раз, когда вы использовали формулу, и вносить одно и то же изменение.
Рекурсия
До появления функции 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 и расширит функционал для продвинутых пользователей.
В настоящее время функция LAMBDA остается в стадии бета-тестирования, и Microsoft еще не указала, когда она станет общедоступна. LAMBDA постепенно развертывается для сборок в бета-канале Office Insider. Тестирование функции будет присутствовать во всех подписках на Office 365 и Microsoft 365, которые включают Excel для Microsoft Windows и Mac.