Лебединая песня Microsoft Excel – функция LAMBDA

Формулы Excel используют на порядок больше пользователей, чем все программисты C, C++, C#, Java и Python в мире вместе взятые. Несмотря на свой успех, Excel как язык программирования, имеет фундаментальные недостатки: (1) формулы Excel поддерживают только скалярные значения – числа, строки и логические значения; (2) Excel (без кода VBA) не позволяет определять новые функции. Ситуация изменилась с появлением в 2019 г. динамических массивов. С их помощью обычные формулы обрабатывают диапазон ячеек, а результаты также разливаются в диапазон ячеек. В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. В течение 2021 г. постепенно у всех пользователей Excel после плановых обновлений Office 365 появилась функция LAMBDA.

Рис. 1. Механизм создания пользовательских функций Excel без кода VBA

Скачать заметку в формате Word или pdf, примеры в формате Excel

Синтаксис функции LAMBDA

Начнем с простого примера. Создадим функцию для расчета гипотенузы по катетам.

Рис. 2. Формула расчета гипотенузы на основе функции LAMBDA

В общем виде синтаксис функции LAMBDA:

Рис. 3. Синтаксис функции LAMBDA

Последний аргумент функции LAMBDA – расчет (он же вычисление). Все предыдущие аргументы – параметры. Обязательный аргумент один. И это вычисление. Параметров может быть до 253.

Любопытной особенностью функции LAMBDA является отсутствие ссылок на ячейки в ее синтаксисе (см. рис. 2). Вместо них используются условные обозначения – параметры. Попробуйте в ячейке D3 ввести функцию LAMBDA со ссылками (см. рис. 4). Нажмите Enter. Получите ошибку:

Рис. 4. Ссылки на ячейки не работают

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

На рис. 2 вызов функции реализован через передачу ей значений двух параметров: а из ячейки С1 и b из ячейки С2.

В вызове функции параметры разделяются точкой с запятой, даже если они находятся в соседних ячейках. Например, такой синтаксис…

…вернет ошибку #ЗНАЧ!

Естественно, такое использование весьма неудобно, поэтому разработчики MS предлагают присвоить функции имя. Пройдите Формулы –> Диспетчер имен, и введите следующие настройки в окне Создание имени:

Рис. 5. Присвоении функции имени

Рис. 6. Функция ГИПОТЕНУЗА принимает два аргумента в виде ссылок на ячейки

К сожалению, подсказка при вводе формулы выглядит не слишком информативно:

Рис. 7. Ввод формулы ГИПОТЕНУЗА

Если вас интересует, почему так происходит, см. дискуссию на эту тему. Преодолеть такое положение вещей пока невозможно.

Если формула основана на LAMBDA, сервис Формулы –> Вычислить формулу не позволяет увидеть шаги вычисления так, как вы ожидаете… И с этим тоже ничего нельзя поделать.

Вычисление расстояния между городами

Команда разработчиков LAMBDA ранее создала географический тип данных и функцию LET. Так что LAMBDA отлично работает с географией. Допустим, у вас есть пары городов в столбцах A и B. Названия городов желательно ввести на английском языке, хотя часто Excel распознает и русское написание. Преобразуйте диапазон в умную таблицу. Для этого выделите диапазон и нажмите Ctrl+T (англ.). Выделите Таблицу и пройдите Данные –> Типы данных –> География. Если встать в любую ячейку Таблицы, появится значок полей данных. Кликните на него и раскроется список доступных полей:

Рис. 8. Географический тип данных

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

Рассчитаем расстояние между городами с использованием полей Широта (Latitude) и Долгота (Longitude). Для простоты будем считать, что наш земной шар – идеальная сфера с диаметром 12 742 км. Определим функцию РАССТОЯНИЕ(From; To):

=LAMBDA(From;To;(12742)*ASIN(КОРЕНЬ(0,5-COS((To.Latitude-From.Latitude)*ПИ()/180)/2+COS(From.Latitude*ПИ()/180)*COS(To.Latitude*ПИ()/180)*(1-COS((To.Longitude-From.Longitude)*ПИ()/180))/2)))

К сожалению, не по всем городам России имеются данные по широте и долготе. В частности, их нет по Москве и СПб…

В итоге получим:

Рис. 9. Расстояние между городами

Обращение строки

Рассмотрим более сложный пример – использование рекурсии для обращения текста. Ранее встроенные функции Excel с этим не справлялись. Такое было возможно с помощью Visual Basic или JavaScript. Определим функцию ОБРАЩЕНИЕ(мойтекст), использующую рекурсию на основе LAMBDA. В формуле используются вспомогательные функции – ГОЛОВА и ХВОСТ для вычисления первого символа и остального текста.

Рис. 10. Обращение текста

Разберем, как работает функция ОБРАЩЕНИЕ(). На первой итерации функция ГОЛОВА() возвращает букву О, а ХВОСТ() – БРАЩЕНИЕ. Чтобы показать только первую итерацию работы функции ОБРАЩЕНИЕ(), я создал функцию ОБРАЩЕНИЕ1():

Рис. 11. Первая итерация

Конструкция ЕСЛИ(мойтекст=»БРАЩЕНИЕ»;мойтекст;…) служит проверкой для выхода из рекурсии (цикла). Видно, что после одной итерации исходный текст потерял первую букву, поэтому проверка заканчивается успешно и возвращается текст ХВОСТ(мойтекст)&ГОЛОВА(мойтекст) = «БРАЩЕНИЕО». Первая буква ушла в конец текста. После каждой итерации первоначальный текст теряет по одной букве в начале. Когда выполнится условие мойтекст=»», цикл заканчивается, и возвращается обращенный текст.

Функции ГИПОТЕНУЗА() и РАССТОЯНИЕ() продемонстрировали использование LAMBDA для упрощения и повышения наглядности формул. В то же время функция ОБРАЩЕНИЕ() показала нетривиальное использование LAMBDA для организации цикла. Именно такое использование является серьезным шагом в развитии программирования в Excel. Рассмотрим еще один пример организации цикла.

Преобразование заголовка заметки в URL-адрес

Я публикую заметки с помощью WordPress, который делает это преобразование автоматически. Например, недавно я опубликовал заметку Заключительный проект в Power Query: объединяем все вместе. WordPress дал ей адрес https://baguzin.ru/wp/zaklyuchitelnyj-proekt-v-power-query-obedinyaem-vse-vmeste/

Адрес содержит слова в английской транслитерации, разделенные тире. В URL-адресах используются строчные буквы от a до z, цифры и дефис. Все остальные знаки недопустимы. Преобразование заголовка в URL-адрес можно реализовать с помощью LAMBDA-функции Slugify с двумя аргументами.[1] Первый – это текст (phrase), а второй всегда будет равен 1 и будет сохранен в переменной «ndx». Такое имя выбрано потому, что оно напоминает слово «индекс».

Для программистов VBA: «ndx» – это наш счетчик циклов. В отличие от VBA, где можно создать переменную на лету, для LAMBDA мы должны передать 1 в качестве аргумента.

Очень удобно изучать формулы, увеличив строку формул, и кликая на аргументы функций:

Рис. 12. Формула, преобразующая заголовок заметки в URL-адрес; выделен третий аргумент функции LAMBDA – расчет

Далее можно кликнуть во второй строке после открывающей скобки, и изучить аргументы функции ЕСЛИ:

Рис. 13. Подсвечен аргумент значение_если_ложь функции ЕСЛИ

Итак, функция LAMBDA начинается с двух параметров: phrase и ndx (см. рис. 12). Остальная часть формулы – это третий аргумент функции LAMBDA. Далее функция ЕСЛИ проверяет, находится ли ndx в пределах фразы? Если проверка пройдена, выполняется блок со строки LET( до строки ); Если проверка завершилась неудачей, т.е., счетчик вышел за пределы фразы, формируется URL-адрес (см. подсвеченную строку на рис. 13).

Шесть строк внутри блока LET() создают phrase для следующего вызова Slugify. ПСТР() извлекает один символ из phrase, соответствующий номеру индекса ndx. Этот символ переводится в нижний регистр и присваивается переменной char:

Код символа char присваивается переменной charcode:

Если код символа буква или цифра…

…используйте его (char), в противном случае используйте дефис («-«).

Фраза для следующей итерации будет состоять из части слева от исправленного символа…

…самого исправленного символа…

и правой части…

При первом вызове Slugify обрабатывает первый символ. Затем механизм вычислений Excel вызовет Slugify, чтобы исправить 2-й символ. Это будет продолжаться до тех пор, пока ndx не превысит длину phrase. Как только ndx превысит длину phrase формула вернет результат:

Сначала мы заменим многочисленные дефисы, которыми в цикле LET() заменяли недопустимые символы, на пробелы – внутренняя функция ПОДСТАВИТЬ(). Затем удалим лишние пробелы – СЖПРОБЕЛЫ(). И наконец, заменим оставшиеся одиночные пробелы на дефисы – внешняя функция ПОДСТАВИТЬ().

Написание сложной формулы LAMBDA в ячейке Excel довольно сложное занятие. Использование окна Добавления имени еще хуже. Пожалуй, лучше всего писать формулу в блокноте или в текстовом поле в Excel (Вставка –> Текст –> Надпись). Нет хорошего способа проверить LAMBDA, использующую рекурсию. Пока вы не создадите имя, проверить формулу не получится. Но, если вы ошибетесь, формула с именем не будет работать, не сообщая почему. Хорошая новость заключается в том, что Microsoft работает над новым интерфейсом редактирования формул.

Теперь у вас есть функция SLUGIFY:

Рис. 14. Функция SLUGIFY за работой

Является ли число простым

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

Рис. 15. Является ли число простым

Использованные материалы

LAMBDA: The ultimate Excel worksheet function https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/

Подсказки при вводе функции малоинформативны https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=144443&TITLE_SEO=144443-voprosy-po-novoy-funktsii-_lambda_&FID_8=&tags=SQL

Билл Джелен. Срывая маску с Excel. Глава 89. LAMBDA-функция, как хранилище сложной логики вычислений (на англ. языке) https://www.mrexcel.com/products/mrexcel-2021-unmasking-excel/

Лямбда-функция Excel, которая проверяет, является ли число простым https://thefaq.ru/ljambda-funkcija-excel-kotoraja-proverjaet-javljaetsja-li-chislo-prostym/

Дополнительные материалы

С добавлением лямбда-функций язык программирования Excel стал полным по Тьюрингу https://habr.com/ru/news/t/540308/

LAMBDA — новая суперфункция Excel https://www.planetaexcel.ru/techniques/25/20915/

How to write recursive LAMBDA function in Excel with examples https://www.ablebits.com/office-addins-blog/write-recursive-lambda-function-excel/

Функция ЛЯМБДА в Excel https://ru.wiki-base.com/7773197-excel-lambda-function

Microsoft позволит создавать пользовательские функции в Excel https://infostart.ru/journal/news/tekhnologii/microsoft-pozvolit-sozdavat-polzovatelskie-funktsii-v-excel_1354120/

Jon Acampora. The LAMBDA Function Explained – How to Create Custom Functions in Excel https://www.excelcampus.com/functions/lambda-explained/

Разделение по столбцам https://www.youtube.com/watch?v=o9UNfjol_Ok&ab_channel=AbiolaDavid

Calculate Nice Axis Scales with LET and LAMBDA https://peltiertech.com/calculate-nice-axis-scales-with-let-and-lambda/

Справка Microsoft на русском языке: Функция ЛЯМБДА

[1] Это перевод части главы из книги Билла Джелена (см. в конце заметки Использованные материалы)