зміст
На даний момент 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. Створіть назву
Тепер про легку та цікаву частину. Ми відкриваємо Менеджер імен таб формула (Формули — Менеджер імен) і створіть нову назву за допомогою кнопки Створювати (Створити). Придумайте та введіть назву для нашої майбутньої функції (наприклад, Номквартала), і в полі посилання (Довідка) акуратно скопіюйте з рядка формул і вставте нашу функцію LAMBDA, тільки без останнього аргументу (A2):
все Після натискання на OK створену функцію можна використовувати в будь-якій клітинці на будь-якому аркуші цієї книги:
Використання в інших книгах
LAMBDA і динамічні масиви
Спеціальні функції, створені за допомогою функції LAMBDA успішно підтримує роботу з новими динамічними масивами та їх функціями (ФІЛЬТР, UNIK, GRADE) додано до Microsoft Excel у 2020 році.
Скажімо, ми хочемо створити нову призначену для користувача функцію, яка б порівнювала два списки та повертала різницю між ними – ті елементи з першого списку, яких немає в другому. Справа життя, чи не так? Раніше для цього використовували або функції а-ля VPR (ВПР), або зведені таблиці, або запити Power Query. Тепер можна обійтися однією формулою:
В англійській версії це буде:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Ось функція COUNTIF підраховує кількість входжень кожного елемента першого списку в другий, а потім у функцію ФІЛЬТР вибирає лише ті з них, які не мали цих входжень. Загорнувши цю структуру в LAMBDA і створення на його основі іменованого діапазону з назвою, наприклад, РОЗПОДІЛ ПОШУКУ – отримаємо зручну функцію, яка повертає результат порівняння двох списків у вигляді динамічного масиву:
Якщо вихідними даними є не звичайні, а «розумні» таблиці, наша функція теж впорається без проблем:
Іншим прикладом є динамічне розбиття тексту шляхом перетворення його в XML і подальшого аналізу клітинка за клітинкою за допомогою функції FILTER.XML, яку ми нещодавно аналізували. Щоб не відтворювати кожен раз цю складну формулу вручну, простіше буде загорнути її в LAMBDA і створити на її основі динамічний діапазон, тобто нову компактну і зручну функцію, назвавши її, наприклад, RAZDTEXT:
Першим аргументом цієї функції буде комірка з вихідним текстом, а другим – символ роздільника, і вона поверне результат у вигляді горизонтального динамічного масиву. Код функції буде таким:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Список прикладів нескінченний – у будь-якій ситуації, коли вам часто доводиться вводити ту саму довгу та громіздку формулу, функція LAMBDA помітно полегшить життя.
Рекурсивне перерахування символів
Усі попередні приклади показали лише одну, найбільш очевидну, сторону функції LAMBDA – її використання як «обгортки» для загортання в неї довгих формул і спрощення їх введення. Насправді LAMBDA має іншу, набагато глибшу сторону, яка перетворює її майже на повноцінну мову програмування.
Справа в тому, що принципово важливою особливістю функцій LAMBDA є можливість їх реалізації в рекурсія – логіка обчислень, коли в процесі обчислень функція викликає сама себе. За звичкою це може прозвучати моторошно, але в програмуванні рекурсія - звична справа. Навіть у макросах у Visual Basic ви можете це реалізувати, а тепер, як бачите, це дійшло до Excel. Спробуємо розібратися в цій техніці на практичному прикладі.
Припустімо, ми хочемо створити визначену користувачем функцію, яка б видаляла всі задані символи з вихідного тексту. Корисність такої функції, думаю, доводити не потрібно – з її допомогою було б дуже зручно очищати засмічені вхідні дані, чи не так?
Однак, порівняно з попередніми, нерекурсивними прикладами, нас чекають дві труднощі.
- Нам доведеться придумати ім'я для нашої функції, перш ніж ми почнемо писати її код, тому що в ньому це ім'я вже буде використовуватися для виклику самої функції.
- Введення такої рекурсивної функції в комірку та її налагодження шляхом вказівки аргументів у дужках після LAMBDA (як ми робили раніше) не спрацює. Функцію доведеться створювати відразу «з нуля». Менеджер імен (Менеджер імен).
Давайте назвемо нашу функцію, скажімо, CLEAN і хочемо, щоб вона мала два аргументи – текст, який потрібно очистити, і список виключених символів у вигляді текстового рядка:
Давайте, як і раніше, створимо вкладку формула в Менеджер імен іменований діапазон, назвіть його CLEAR і введіть у поле Діапазон наступна конструкція:
=LAMBDA(t;d;ЯКЩО(d=””;t;CLEAR(ПІДСТАВИТИ(t;ЛІВО(d);””);СЕРЕДИНА(d;2;255))))
Тут змінна t — вихідний текст, який потрібно очистити, а d — список символів, які потрібно видалити.
Це все працює так:
Ітерація 1
Фрагмент SUBSTITUTE(t;LEFT(d);””), як можна здогадатися, замінює у вихідному тексті t перший символ з лівого символу з набору d, який потрібно видалити, на порожній текстовий рядок, тобто видаляє “ A”. Як проміжний результат отримуємо:
Вш зкз п 125 руб.
Ітерація 2
Потім функція викликає сама себе і на вхід (перший аргумент) отримує те, що залишилося після очищення на попередньому кроці, а другий аргумент — це рядок виключених символів, починаючи не з першого, а з другого символу, тобто «BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” без початкової букви “А” – це робиться функцією MID. Як і раніше, функція бере перший символ зліва з інших (B) і замінює його в наданому їй тексті (Zkz n 125 рублів) порожнім рядком – отримуємо проміжний результат:
125 руб.
Ітерація 3
Функція викликає себе знову, отримуючи в якості першого аргументу те, що залишилося від тексту, який потрібно очистити на попередній ітерації (Bsh zkz n 125 ru.), і в якості другого аргументу набір виключених символів, скорочених ще на один символ, щоб ліворуч, тобто «ВГДЕЕГЗИКЛМНОПРСТУФХЦШШЩЙЙЮЯ.», без ініціалу «Б». Потім знову бере перший символ зліва (B) з цього набору і видаляє його з тексту – отримуємо:
ш зкз п 125 ру.
І так далі – сподіваюся, ви зрозуміли. З кожною ітерацією список символів, які потрібно видалити, буде скорочено зліва, і ми будемо шукати та замінювати наступний символ із набору пустотою.
Коли всі символи закінчаться, нам потрібно буде вийти з циклу – цю роль якраз виконує функція IF (ЯКЩО), в який загорнуто наш дизайн. Якщо не залишилося символів для видалення (d=””), то функція більше не повинна викликати сама себе, а має просто повернути текст, який потрібно очистити (змінна t) у його остаточній формі.
Рекурсивна ітерація комірок
Подібним чином можна реалізувати рекурсивне перерахування клітинок у заданому діапазоні. Припустімо, ми хочемо створити лямбда-функцію з назвою СПИСОК НА ЗАМІНУ для повної заміни фрагментів у вихідному тексті за заданим списком літератури. Результат повинен виглядати так:
Ті. на нашій функції СПИСОК НА ЗАМІНУ буде три аргументи:
- комірка з текстом для обробки (адреса джерела)
- перша комірка стовпця зі значеннями для пошуку з пошуку
- перша клітинка стовпця зі значеннями заміни з пошуку
Функція має йти зверху вниз у каталозі та послідовно замінювати всі параметри з лівого стовпця Знайти до відповідних параметрів у правій колонці Замінити. Ви можете реалізувати це за допомогою наступної рекурсивної лямбда-функції:
Зсув вниз на кожній ітерації реалізовано стандартною функцією Excel ВІДХОДЖЕННЯ (ЗМІЩЕННЯ), який у цьому випадку має три аргументи – початковий діапазон, зсув рядка (1) і зсув стовпця (0).
Що ж, як тільки ми досягаємо кінця каталогу (n = “”), ми повинні завершити рекурсію – ми перестаємо викликати себе і показуємо те, що накопичилося після всіх замін у вихідній текстовій змінній t.
Це все. Жодних хитрих макросів чи запитів Power Query – все завдання вирішується однією функцією.
- Як використовувати нові функції динамічного масиву Excel: FILTER, SORT, UNIC
- Заміна та очищення тексту за допомогою функції ЗАМІНА
- Створення макросів і призначених для користувача функцій (UDF) у VBA