Содержание:
На данный момент в Microsoft Excel имеется почти пятьсот функций рабочих листов, доступных через окно «Мастер функций» — кнопка fx в строке формул. Это очень приличный набор, но, тем не менее, практически каждый пользователь рано или поздно сталкивается с ситуацией, когда в этом списке нет нужной ему функции – просто потому, что ее нет в Excel.
До сих пор единственным способом решения этой проблемы были макросы, т.е. написание собственной пользовательской функции (UDF = User Defined Function) на Visual Basic, что требует соответствующих навыков программирования и порой совсем непросто. Однако с последними обновлениями Office 365 ситуация изменилась в лучшую сторону — в Excel добавлена специальная функция «обертка». LAMBDA. С его помощью задача создания собственных функций теперь решается легко и красиво.
Давайте рассмотрим принцип его использования на следующем примере.
Как вы, скорее всего, знаете, в Excel есть несколько функций анализа даты, которые позволяют определить номер дня, месяца, недели и года для заданной даты. Но почему-то нет функции, определяющей номер квартала, которая тоже часто нужна, верно? Давайте исправим этот недостаток и творим с LAMBDA собственная новая функция для решения этой проблемы.
Шаг 1. Напишите формулу
Начнем с того, что вручную обычным способом запишем в ячейку листа формулу, вычисляющую то, что нам нужно. В случае с номером квартала это можно сделать, например, так:
Шаг 2. Доработка в LAMBDA и тестирование
Теперь пришло время применить новую функцию LAMBDA и обернуть в нее нашу формулу. Синтаксис функции следующий:
=ЛАМБДА(Переменная1; Переменная2; ... ПеременнаяN ; Выражение)
где имена одной или нескольких переменных указаны первыми, а последний аргумент всегда представляет собой формулу или вычисляемое выражение, в котором они используются. Имена переменных не должны выглядеть как адреса ячеек и не должны содержать точек.
В нашем случае переменная будет только одна — дата, для которой мы рассчитываем номер квартала. Назовем для него переменную, скажем, d. Затем обернем нашу формулу функцией LAMBDA и подставив адрес исходной ячейки А2 на вымышленное имя переменной, получим:
Обратите внимание, что после такого преобразования наша формула (на самом деле правильная!) стала выдавать ошибку, поскольку теперь в нее не переносится исходная дата из ячейки А2. Для тестирования и уверенности вы можете передать ему аргументы, добавив их после функции. LAMBDA в скобках:
Шаг 3. Создайте имя
Теперь самое простое и интересное. Мы открыты Менеджер имен таб формула (Формулы — Менеджер имен) и создайте новое имя с помощью кнопки Создавай (Создавать). Придумайте и введите имя для нашей будущей функции (например, Номквартала), и в поле Ссылка (Ссылка) аккуратно скопируйте из строки формул и вставьте нашу функцию LAMBDA, только без последнего аргумента (A2):
Все. После нажатия на OK созданную функцию можно использовать в любой ячейке на любом листе этой книги:
Использование в других книгах
LAMBDA и динамические массивы
Пользовательские функции, созданные с помощью функции LAMBDA успешно поддерживают работу с новыми динамическими массивами и их функциями (ФИЛЬТР, UNIK, GRADE) добавлено в Microsoft Excel в 2020 году.
Допустим, мы хотим создать новую пользовательскую функцию, которая бы сравнивала два списка и возвращала разницу между ними — те элементы из первого списка, которых нет во втором. Дело жизни, не так ли? Раньше для этого использовали либо функции а-ля ВПР (ВПР), или сводные таблицы, или запросы Power Query. Теперь можно обойтись одной формулой:
В английской версии это будет:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Здесь функция COUNTIF подсчитывает количество вхождений каждого элемента первого списка во второй, а затем функция ФИЛЬТР выбирает только тех из них, у кого этих явлений не было. Обернув эту структуру в LAMBDA и создать на его основе именованный диапазон с именем, например, ПОИСК РАСПРЕДЕЛЕНИЯ — получим удобную функцию, возвращающую результат сравнения двух списков в виде динамического массива:
Если исходными данными являются не обычные, а «умные» таблицы, наша функция также справится без проблем:
Другой пример — динамическое разделение текста путем его преобразования в XML и последующего анализа его ячейка за ячейкой с помощью функции FILTER.XML, которую мы недавно анализировали. Чтобы не воспроизводить каждый раз эту сложную формулу вручную, проще будет обернуть ее в LAMBDA и создать на ее основе динамический диапазон, т.е. новую компактную и удобную функцию, назвав ее, например, RAZDTEXT:
Первым аргументом этой функции будет ячейка с исходным текстом, а вторым – символ-разделитель, и она вернет результат в виде горизонтального динамического массива. Код функции будет следующим:
=ЛАМБДА(t;d; ТРАНСПОЗИРОВАТЬ(ФИЛЬТР.XML("
Список примеров бесконечен – в любой ситуации, когда вам часто приходится вводить одну и ту же длинную и громоздкую формулу, функция ЛАМБДА заметно облегчит жизнь.
Рекурсивное перечисление символов
Все предыдущие примеры показывали только одну, самую очевидную сторону функции ЛАМБДА — ее использование в качестве «обертки» для обертывания в нее длинных формул и упрощения их ввода. На самом деле у LAMBDA есть и другая, гораздо более глубокая сторона, превращающая его практически в полноценный язык программирования.
Дело в том, что принципиально важной особенностью LAMBDA-функций является возможность реализации их в рекурсия – логика вычислений, когда в процессе расчета функция вызывает саму себя. С непривычки это может показаться жутким, но в программировании рекурсия — обычное дело. Даже в макросах в Visual Basic это можно реализовать, а теперь, как видите, дело дошло и до Excel. Попробуем разобраться в этой технике на практическом примере.
Предположим, мы хотим создать пользовательскую функцию, которая будет удалять все заданные символы из исходного текста. Полезность такой функции, думаю, доказывать не нужно — было бы очень удобно с ее помощью очищать захламленные входные данные, не правда ли?
Однако по сравнению с предыдущими, нерекурсивными примерами, нас ждут две трудности.
- Имя для нашей функции нам придется придумать до того, как мы начнем писать ее код, потому что в нем это имя уже будет использоваться для вызова самой функции.
- Ввести такую рекурсивную функцию в ячейку и отладить ее путем указания аргументов в скобках после LAMBDA (как мы делали ранее) не получится. Вам придется создавать функцию сразу «с нуля» в Менеджер имен (имя-менеджер).
Давайте назовем нашу функцию, скажем, CLEAN, и нам нужно, чтобы она имела два аргумента — текст, который нужно очистить, и список исключенных символов в виде текстовой строки:
Создадим, как и раньше, на вкладке формула в Имя менеджера именованный диапазон, назовите его CLEAR и введите в поле Диапазон следующая конструкция:
=LAMBDA(t;d;IF(d="";t;CLEAR(SUBSTITUTE(t;LEFT(d);"");MID(d;2;255))))
Здесь переменная t — это исходный текст, который нужно очистить, а d — список символов, которые нужно удалить.
Все это работает следующим образом:
Итерация 1
Фрагмент SUBSTITUTE(t;LEFT(d);””), как нетрудно догадаться, заменяет в исходном тексте t первый символ левого символа из удаляемого набора d на пустую текстовую строку, т.е. удаляет “ А». В качестве промежуточного результата получаем:
Вш зкз н 125 руб.
Итерация 2
Далее функция вызывает саму себя и на вход (первый аргумент) получает то, что осталось после очистки на предыдущем шаге, а второй аргумент — строку исключенных символов, начиная не с первого, а со второго символа, т.е. «BVGDEEGZIKLMNOPRSTUFHTSCHSSHSHCHYYYYYA. », без начальной буквы «А» — это делает функция MID. Как и раньше, функция берет первый символ слева из оставшихся (В) и заменяет его в заданном ей тексте (Зкз н 125 рублей) на пустую строку – получаем в качестве промежуточного результата:
125 руб.
Итерация 3
Функция вызывает себя снова, получая в качестве первого аргумента то, что осталось от очищаемого текста на предыдущей итерации (Бш зкз н 125 ру.), а в качестве второго аргумента набор исключенных символов, усеченный еще на один символ до левый, т. е. «ВГДЕЭГЗИКЛМНОПРСТУФХТЩЩШЩИЙЫЮЯ.», без инициала «Б». Затем снова берет первый символ слева (B) из этого набора и удаляет его из текста — получаем:
ш зкз н 125 ру.
И так далее – надеюсь, вы поняли. На каждой итерации список удаляемых символов будет обрезаться слева, и мы будем искать и заменять следующий символ из набора на пустоту.
Когда все символы закончатся, нам нужно будет выйти из цикла — эту роль как раз выполняет функция IF (ЕСЛИ), в который завернут наш дизайн. Если не осталось символов для удаления (d=""), то функция больше не должна вызывать саму себя, а должна просто вернуть очищаемый текст (переменная t) в его окончательном виде.
Рекурсивная итерация ячеек
Аналогичным образом вы можете реализовать рекурсивный перебор ячеек в заданном диапазоне. Предположим, мы хотим создать лямбда-функцию с именем СПИСОК ЗАМЕНЫ для оптовой замены фрагментов исходного текста по заданному списку литературы. Результат должен выглядеть так:
Те. на нашем мероприятии СПИСОК ЗАМЕНЫ будет три аргумента:
- ячейка с текстом для обработки (адрес источника)
- первая ячейка столбца со значениями для поиска из справочника
- первая ячейка столбца со значениями замены из справочника
Функция должна идти по каталогу сверху вниз и заменять последовательно все параметры из левого столбца. Найти к соответствующим опциям из правого столбца Замена. Вы можете реализовать это с помощью следующей рекурсивной лямбда-функции:
Сдвиг вниз на каждой итерации реализуется стандартной функцией Excel. УДАЛЕНИЕ (КОМПЕНСИРОВАТЬ), который в данном случае имеет три аргумента — исходный диапазон, сдвиг строки (1) и сдвиг столбца (0).
Ну а как только мы дошли до конца каталога (n=""), мы должны завершить рекурсию — перестать вызывать себя и отобразить то, что накопилось после всех замен в исходной текстовой переменной t.
Вот и все. Никаких хитрых макросов или запросов Power Query — вся задача решается одной функцией.
- Как использовать новые функции динамических массивов Excel: FILTER, SORT, UNIC
- Замена и очистка текста с помощью функции ПОДСТАВИТЬ
- Создание макросов и пользовательских функций (UDF) в VBA.