Новые функции Excel, основанные на LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять пользовательские функции, написанные на языке формул Excel. Я недавно привел примеры того, как работает LAMBDA. Но Microsoft на этом не остановился, и в июле 2021 г. объявил о создании новых функций, основанных на LAMBDA:

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

REDUCE: реализует цикл обработки элементов массива, применяя функцию LAMBDA к каждому значению и возвращает одно накопленное значение.

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

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

BYCOL: возвращает массив в виде строки, один результат для каждого столбца массива.

BYROW: возвращает массив в виде столбца, один результат для каждой строки массива.

ISOMITTED: проверяет, отсутствует ли значение, и возвращает ИСТИНА, в противном случае – ЛОЖЬ.

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

Функция MAP

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

массив1 – массив который нужно сопоставить (обязательный);

массив2 – еще один массив для сопоставления (необязательный);

массив# – еще массивы для сопоставления (необязательный);

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

Рис. 1. Синтаксис функции MAP

Квадраты целых чисел, превышающих определенное значение

Следующая формула принимает массив А1:С2 и возвращает массив D4:F5. Если элемент в исходном массиве не больше 4, возвращается его значение, если более – квадрат значения:

Рис. 2.  Квадраты целых чисел, превышающих определенное значение

По поводу использования +a в качестве третьего аргумента функции ЕСЛИ() на сайте Microsoft и в Инете сказано, что обозначение +a используется для принудительного преобразования из ссылки на диапазон в массив значений. Без знака + функция MAP может вернуть ошибку #ВЫЧИСЛ! Этот шаг иногда необходим при работе с формулами массива, которые разливаются в диапазон. Я поэкспериментировал, но мне не удалось получить ошибку #ВЫЧИСЛ!. Так что запись

… тоже работает. Возможно, ошибка существовала в более ранних версиях, а сейчас устранена.

Применение логического И к двум массивам

Следующая формула в качестве аргументов принимает два массива – столбцы Элемент1 и Элемент2, и возвращает ИСТИНА, только если оба значения в одной строке = ИСТИНА:

Рис. 3.  Массив, основанный на логическом И

Фильтрация строк, отвечающих нескольким условиям

Рис. 4. Вывод элементов массива по критериям

Следующая формула фильтрует таблицу А1:В11, оставляя только строки, которые соответствуют критериям отбора:

Удаление не цифр

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

Рис. 5. Массив случайных символов

Проанализируем формулу

Функция СЛМАССИВ() создает массив из 12 строк и 6 столбцов целых чисел (аргумент ИСТИНА) в диапазоне от 48 до 122.

Рис. 6. Таблица ASCII кодов

Функция СИМВОЛ() выводит символ по его ASCII коду. Функция СЛМАССИВ() волатильна, т.е. её значения меняются с любым изменением на листе Excel. Можно принудительно запускать пересчет, нажимая кнопку F9.

Создадим новый массив, который вернет только цифры:

Рис. 7. Функция MAP возвращает массив чисел

Несколько слов о формуле

Функция MAP() принимает массив А2#, обернутый в функцию ЗНАЧЕН(). Функция СИМВОЛ() возвращает текст, даже, если это цифра. ЕЧИСЛО() проверяет является символ числом. Если да, то возвращается сам символ +а, если нет – пустая строка "".

Когда использовать MAP

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

В таких кейсах использование MAP не оправдано. Однако есть случаи, когда MAP может быть полезна:

• Для выполнения логических тестов с функциями И, ИЛИ.
• Для выполнения операций агрегирования нескольких массивов. Например,

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

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

Рис. 8. Максимальные значения трех массивов

Пусть у нас есть данные многолетних наблюдений температуры в Москве:

Рис. 9. Среднемесячные температуры в Москве по годам

Наша цель – оставить только те строки таблицы, которые соответствуют среднегодовой температуре > 6°C… и представить все данные по шкале Фаренгейта (извините за некоторую искусственность примера). С этим преобразованием справится следующая формула:

Чтобы номера годов не участвовали в вычислениях среднего значения по строке я предварительно преобразовал их в текст с помощью формулы ТЕКСТ(год;"0000").

Рис. 10. В правом массиве отфильтрованы строки, соответствующие среднегодовой температуре > 6°C; температура указана по шкале Фаренгейта

Проанализируем формулу. Функция MAP() имеет два аргумента: массив (рис. 11а) и LAMBDA (рис. 11б).

Рис. 11. Два аргумента функции MAP

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

Рис. 12. Три аргумента функции ФИЛЬТР

Функция ФИЛЬТР() имеет три аргумента: массив – ссылается на таблицу без строки заголовка (Таблица4); включить – какие строки таблицы оставить (функция BYROW); если_пусто – что вывести, если строк после фильтрации не осталось.

Функция BYROW() подробнее будет рассмотрена ниже. Она принимает в качестве первого аргумента массив (Таблица4), и обрабатывает его с помощью функции LAMBDA(год;СРЗНАЧ(год)>S2). BYROW() возвращает массив в виде одного столбца с количеством строк, равным числу строк в Таблица4. Массив, возвращаемый BYROW(), содержит значения ИСТИНА, если СРЗНАЧ(год)>S2, или ЛОЖЬ, если условие не выполнено.

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

Рис. 13. Два аргумента функции LAMBDA

Первый аргумент функции LAMBDA – параметр (темп). Этот параметр кодирует (ссылается на) массив, возвращаемый функции ФИЛЬТР(). Параметр темп обрабатывается функцией ЕСЛИ.

Рис. 14. Три аргумента функции ЕСЛИ

Первый аргумент функции ЕСЛИ() – лог_выражение – проверяет, является ли элемент массива (отфильтрованной Таблица4) числом. Если да, то возвращается значение_если_истина – ПРЕОБР(темп;"C";"F"). Именно здесь происходит преобразование градусов Цельсия в Фаренгейта. Если проверяемый элемент массива не является числом (а в Таблица4 таковыми будут только года), то возвращается сам элемент.

Вуаля))

Функция REDUCE

Смысл функции – обработать массив, чтобы вернуть одно число. Синтаксис:

[начальное_значение] – задает начальное значение для аккумулятора (необязательный аргумент);

массив – массив, каждый элемент которого нужно обработать с помощью функции LAMBDA;

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

• а – аккумулятор, накапливает значение, возвращаемое в качестве конечного результата функцией REDUCE; сначала аккумулятор получает начальное_значение, а затем изменяется с каждым циклом обработки массива.
• b – ссылается на массив (другими словами – на каждый элемент массива).

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

Суммирование квадратов значений

Рис. 15. Суммирование квадратов элементов массива

Разберем формулу

Функция REDUCE имеет три аргумента. Первый – initial_value – необязательный (рис. 16а). Иногда его можно опустить, иногда следует явно указать ноль (см. пример 3). Иногда нужно присвоить значение, отличное от нуля, чтобы избежать деления или умножения на ноль. Второй аргумент – array – обязательный (рис. 16б). Это массив, вычисления по которому вернет одно значение. В нашем примере аргумент задается динамическим массивом А1#, который разлит в диапазоне А1:С2. Третий аргумент – function – обязательный. Он задает функцию обработки массива. По определению здесь может использоваться только функция LAMBDA. Параметр a соответствует начальному значению (равному нулю), b – массиву А1#. В третьем аргументе функции LAMBDA происходит суммирование квадратов всех элементов массива А1#.

Если вы немного программируете, то воспринимайте аргумент a+b^2, как выражение внутри цикла: a = a + b^2.

Рис. 16. Три аргумента функции REDUCE

Произведение значений, превышающих критерий

Задача – найти произведение всех элементов массива, значение которых превышает 50. Используем формулу:

Рис. 17. Произведение значений, превышающих критерий

Основное действие происходит в этом фрагменте:

Функция REDUCE имеет три аргумента: initial_value = 1, array – A2#, function – LAMBDA(a;b;ЕСЛИ(b>50;a*b;a)). LAMBDA принимает два параметра: а = 1 и b = массив A2#. Третий аргумент функции LAMBDA – расчет = ЕСЛИ(b>50;a*b;a). Если элемент массива A2# > 50, умножаем значение элемента на произведение, полученное на предыдущем шаге. Иначе, оставляем произведение, полученное на предыдущем шаге без изменения. Переходим к следующему элементу.

Функция REDUCE() обёрнута во внешнее ЕСЛИ(). Здесь проверяется: если REDUCE = 1, значит не нашлось ни одного элемента > 50. Возвращается текст "нет значений более 50". Иначе возвращается значение REDUCE().

Сумма четных и нечетных значений

Рис. 18. Сумма четных и нечетных значений

С кодом формулы предлагаю вам разобраться самостоятельно:

Подсчет числа значений, удовлетворяющих критерию

Продолжим примеру со среднемесячными температурами в Москве. Подсчитаем, сколько месяцев за последовательные 10-летние периоды имели среднюю температуру ≥ 18°С:

Рис. 19. Число месяцев в десятилетии, начинающемся с указанного года, с температурой ≥ 18°С

Функция REDUCE имеет три аргумента: initial_value = 0, array – ДВССЫЛ(«B»&(O5-1879)&»:M»&(O5-1870)), function – LAMBDA(a;b;ЕСЛИ(b>=$O$2;a+1;a)). Функция ДВССЫЛ() возвращает диапазон (массив), соответствующий году в столбце О. Например, в ячейке Р5 функция ДВССЫЛ() возвращает диапазон В2:М11. (Подробнее о ДВССЫЛ см. Примеры использования функции ДВССЫЛ.) LAMBDA принимает два параметра: а = 0 и b = массив, сформированный ДВССЫЛ(). Третий аргумент функции LAMBDA – расчет = ЕСЛИ(b>=$O$2;a+1;a). Если элемент массива ≥ $O$2, накопитель – а – увеличивается на 1, если нет, то а остается без изменения.

Справедливости ради отметим, что «старые» формулы Excel справятся с задачей даже лучше. Например, введите в ячейке Q5 формулу (см. рис. 19):

Функция SCAN

Функция сканирует массив и возвращает промежуточные результаты. Синтаксис:

[начальное_значение] – задает начальное значение для аккумулятора (необязательный аргумент); если вы работаете с текстом, установите начальное значение "".

массив – массив, который нужно просканировать;

LAMBDA – функция сканирования массива; включает два параметра, ссылающиеся на начальное_значение и массив:

• а – сканируемое значение, возвращаемое в качестве конечного результата функцией SCAN; сначала аккумулятор получает начальное_значение, а затем изменяется с каждым циклом обработки массива.
• b – ссылается на массив (другими словами – на каждый элемент массива).

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

Вычисление факториала

Рис. 20. Вычисление факториала

Сцепление символов в массиве

Рис. 21. Сцепление символов в массиве

На этом примере видно, как работает накопитель – параметр а функции LAMBDA.

Специфика SCAN, REDUCE и MAP

Функция SCAN возвращает массив, содержащий промежуточные значения, созданные во время сканирования. SCAN можно использовать для вычислений, которые показывают промежуточные результаты. Функция SCAN возвращает массив того же размера, что и исходный. Подобно функции REDUCE, SCAN реализует цикл по всем элементам массива, выполняя вычисления для каждого элемента. Отличие в том, что REDUCE возвращает одно – финальное – значение, а SCAN возвращает все промежуточные значения. Ранее изученная функция MAP также сканирует массив, и возвращает массив того же размера, что и исходный. Отличие в том, что MAP обрабатывает каждый элемент массива как независимый, а SCAN последовательно накапливает результат.

Накопительный итог в Умной Таблице

Допустим, у нас есть продажи за 6 месяцев:

Рис. 22. Таблица продаж

… и вы хотите показать продажи накопительным итогом. Можно использовать следующую неуклюжую формулу:

Рис. 23. Сумма продаж накопительным итогом

В этой формуле смешаны ссылка на ячейку (C1) и структурированная ссылка ([@Sales]). А функция Ч() добавлена, чтобы текстовое значение вернуло 0, а не ошибку #ЗНАЧ!

Формула работает, если добавляются значения, но ломается при вставке значений:

Рис. 24. Формула ломается при вставке строк

Вот тут-то и приходит на помощь функция SCAN:

Рис. 25. Накопительный итог

SCAN сканирует массив – столбец Продажи таблицы Sales (диапазон А1:В10), последовательно суммируя значения на основе LAMBDA. Затем SCAN возвращает массив результатов (диапазон D2:D10), который подстраивается под размер столбца Продажи. И дополнительные строки Таблицы, и вставка новых строк, отражаются корректно.

Функция MAKEARRAY

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

строки – количество строк в массиве; должно быть больше нуля;

столбцы – количество столбцов в массиве; должно быть больше нуля;

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

• r – ссылается на число строк;
• с – ссылается на число столбцов.

Третий аргумент функции LAMBDA – вычисление – задает значения возвращаемого массива.

Таблица умножения

Рис. 26. Таблица умножения

Массивы фиксированных и случайных значений

Элементы массива могут быть жестко заданными. Например…

… вернет прямоугольник 2х3 с шестью нулями.

Массив может состоять из букв. Например…

Более интересен вариант со случайными элементами массива. Например…

… вернет массив 6х6 со случайно отобранными прописными английскими буквами:

Рис. 27. Массив случайных букв

Визуализация адресов ячеек

Рис. 28. Адреса ячеек

Функция BICOL

Функция возвращает массив в виде строки, один результат для каждого столбца массива. Например, если исходный массив включает 3 столбцами, BYCOL вернет строку из 3 элементов. Синтаксис:

массив – исходный массив;

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

Возврат максимального значения в каждом столбце

Рис. 29. Максимальное значение в каждом столбце массива

Сумма квадратов элементов массива отдельно по каждому столбцу

Рис. 30. Сумма квадратов по столбцу

Функция BIROW

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

Рис. 31. Примеры использования функции BYROW

Для расчета суммы по строке в ячейки I2 используется формула:

Для нахождения числа вхождений со значениями более 90 по строке в ячейке J2 используется формула:

Еще один метод использования функции BYROW был показан выше.

Функция ISOMITTED на момент написания заметки отсутствовала в моей версии Excel.

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

New LAMBDA functions available in Excel https://insider.office.com/es-es/blog/new-lambda-functions-available-in-excel

Excel MAP Function https://exceljet.net/excel-functions/excel-map-function

Excel REDUCE Function https://exceljet.net/excel-functions/excel-reduce-function

Excel SCAN Function https://exceljet.net/excel-functions/excel-scan-function

Excel MAKEARRAY Function https://exceljet.net/excel-functions/excel-makearray-function

Excel BYCOL Function https://exceljet.net/excel-functions/excel-bycol-function

Excel BYROW Function https://exceljet.net/excel-functions/excel-byrow-function

Liam Bastick and Chris Gross. Not Just Mary Can Have a Little LAMBDA / Excel Hits 500 https://www.sumproduct.com/news/article/not-just-mary-can-have-a-little-lambda-excel-hits-500