Лямбда-функція в Excel: повний посібник та практичні приклади

Інформатек Діджитал » Ресурси » Лямбда-функція в Excel: повний посібник та практичні приклади

Функція Лямбда в Excel революціонізувала спосіб роботи з формулами в електронних таблицях Microsoft. Вона дозволяє створювати власні функції, використовуючи лише мову формул Excel, не торкаючись жодного рядка VBA чи традиційного програмування . Це так, ніби ви маєте можливість додавати до програми нові, спеціально розроблені вбудовані функції.

Крім того, навколо LAMBDA з'явився ряд пов'язаних функцій, таких як BYROW, BYCOL, MAP, SCAN, REDUCE та MAKEARRAY , призначених для роботи з діапазонами та матрицями набагато гнучкішим та потужнішим способом. Ці функції поводяться, загалом кажучи, як невеликі цикли, які перебирають дані та застосовують перетворення, визначене LAMBDA, відкриваючи широкий спектр можливостей для розширеного аналізу безпосередньо в електронній таблиці.

Що таке функція ЛЯМБДА в Excel і для чого вона використовується?

Лямбда - функція – це інструмент, який дозволяє визначати власні функції, використовуючи лише формули Excel. Замість програмування на VBA або використання макросів, ви можете інкапсулювати будь-яке складне обчислення в одну функцію, яку можна використовувати повторно, з власними параметрами та чітким кінцевим результатом.

На практиці, LAMBDA перетворює будь-яку формулу на функцію , яку можна використовувати скільки завгодно разів. Її головна перевага полягає в тому, що вона бездоганно інтегрується з рештою обчислювального механізму Excel і може поєднуватися зі стандартними функціями, посиланнями, визначеними іменами та динамічними масивами.

Основний синтаксис при використанні безпосередньо в комірці такий:

=ЛЯМБДА(параметр1; параметр2; …; параметрN; обчислення)(значення1; значення2; …; значенняN)

У цій структурі параметр1, параметр2, …, параметрN – це імена, які ви надаєте змінним у функції, тоді як обчислення – це формула, яка використовує ці параметри для генерації результату. Нарешті, у другому наборі дужок ви передаєте фактичні значення , які ці параметри візьмуть під час виконання функції.

Якщо ви використовуєте Диспетчер імен Excel для створення постійної функції LAMBDA, синтаксис дещо змінюється, оскільки ви визначаєте функцію там, але ще не викликаєте її. У такому випадку формат буде таким:

=LAMBDA(перемінна1; змінна2; …; зміннаN; обчислення)

Пізніше ви викличете цю функцію, використовуючи ім'я, яке ви їй дали в Менеджері імен, просто ввівши ім'я та аргументи так само, як ви це робили б з SUM, AVERAGE або будь-якою іншою стандартною функцією.

Найкращі практики створення та тестування лямбда-функцій

Коли ви починаєте працювати з лямбда-функціями, важливо дотримуватися кількох правил, щоб забезпечити їхню належну роботу та не витрачати час на налагодження складних помилок. Один із найпрактичніших способів почати – це створити та протестувати лямбда-функцію безпосередньо в комірці.

Звичайна процедура полягає в тому, щоб спочатку записати повну формулу з визначенням LAMBDA та викликом в одному виразі, щоб одразу побачити, чи відповідає результат очікуванням. Таким чином, можна виявити синтаксичні або логічні помилки, перш ніж зберегти її як іменовану функцію.

Наприклад, дуже типова структура тесту буде такою:

=ЛЯМБДА(); розрахунок)(тестові_значення)

Щоб перевірити щось дуже просте, як-от додавання 1 до числа, можна використати:

=ЛЯМБДА(число; число + 1)(1)

У цьому випадку функція повертатиме значення 2. Це дуже простий приклад, але він служить для ілюстрації механіки: спочатку ви визначаєте параметри та обчислення, а потім викликаєте цю функцію, передаючи відповідний аргумент.

Ключова рекомендація, щоб уникнути помилки #CALC!, полягає в тому, щоб ваша функція LAMBDA завжди повертала результат . Це досягається шляхом чіткого включення виразу в кінці, який повертає одне значення або масив, залежно від ваших потреб. Якщо під час тестування ви бачите помилку #CALC!, перевірте, чи формула дійсно генерує результат, який Excel може відобразити.

Після того, як ви перевірили функцію LAMBDA в комірці та переконалися, що вона працює правильно, саме час перенести цю логіку до Диспетчера імен і перетворити її на функцію повторного використання для всього аркуша або книги.

Зв'язок LAMBDA з новими матричними функціями

Навколо LAMBDA з'явився ряд розширених функцій, таких як BYROW, BYCOL, MAP, SCAN, REDUCE та MAKEARRAY (остання в деяких версіях перекладається як ARCHIVOMAKEARRAY), які покладаються на LAMBDA для застосування перетворень до діапазонів та повних матриць.

Загальна ідея полягає в тому, що ці функції перебирають діапазони даних (по рядках, по стовпцях або поелементно) і для кожного елемента або групи елементів виконують лямбда-функцію, яку ви визначаєте. Іншими словами, вони працюють як цикли, але інтегровані в мову формул Excel.

Це дозволяє виконувати операції, які раніше вимагали допоміжних стовпців, проміжних таблиць або навіть макросів, безпосередньо за допомогою однієї формули масиву, яка розширює значення та повертає результати для всього діапазону одночасно.

Серед функцій, пов'язаних з LAMBDA, виділяються REDUCE, MAP, SCAN, BYCOL, BYROW та MAKEARRAY , кожна з яких має певну мету: перехід по рядках, застосування перетворень по стовпцях, накопичення результатів, створення матриць, обчислених з нуля тощо. Усі вони мають спільну рису: вони використовують LAMBDA як внутрішній «двигун», якому передають значення та акумулятори під час руху по матриці.

Функція BYROW: перебирає рядки та повертає результати рядок за рядком

Функція BYROW використовується для застосування функції LAMBDA до кожного рядка в діапазоні та повернення масиву з одним значенням для кожного обробленого рядка. Це дуже ефективний спосіб обчислення проміжних підсумків або статистики рядок за рядком без необхідності копіювання формул по вертикалі.

Його загальний синтаксис такий:

=BYROW(матриця; ЛЯМБДА(рядок; вираз))

Перший аргумент – це масив або діапазон, який потрібно перебрати (наприклад, B2:D7), а другий – це функція LAMBDA, яка приймає кожен рядок у цьому діапазоні як параметр, один за одним. Функція LAMBDA повертає значення, яке потрібно пов’язати з цим рядком (можливо, суму, середнє значення, логічну перевірку тощо).

Уявіть, що у вас є таблиця даних у діапазоні B2:D7 , і ви хочете отримати проміжний підсумок для кожного рядка. Ви можете написати щось подібне в клітинці E2:

=BYROW(B2:D7; ЛЯМБДА(рядок; СУММА(рядок)))

Результатом буде вихідний вектор з одним значенням для кожного рядка матриці B2:D7, де кожне значення представляє суму елементів у цьому рядку. Таким чином, вам не потрібно записувати SUM рядок за рядком: BYROW робить це за вас і виводить результат вниз.

Функція BYCOL: застосування LAMBDA за стовпцями

Дуже подібно до функції BYROW, функція BYCOL призначена для ітерації по масиву за стовпцями, а не за рядками. Вона застосовує функцію LAMBDA до кожного стовпця в діапазоні та повертає масив результатів, де кожен елемент відповідає стовпцю.

Його типовий синтаксис такий:

=BYCOL(масив; ЛЯМБДА(стовпець; вираз))

У цьому випадку параметр, який отримує LAMBDA, – це весь стовпець матриці, що обробляється на кожному кроці. Подібно до BYROW, функція повертає вектор, але ця призначена для роботи з підсумками або показниками по стовпцях.

Продовжуючи попередній приклад, якщо потрібно обчислити середнє значення кожного стовпця в діапазоні B2:D7 , можна розмістити в клітинці B8 таку формулу:

=ЗАКОЛОНА(B2:D7; ЛЯМБДА(стовпець; СРЕРНЄ(стовпець)))

Результатом буде матриця з такою ж кількістю стовпців, як B2:D7, де кожна позиція містить середнє значення цього стовпця . Таким чином, ви отримаєте всі середні значення одним махом, без необхідності перетягувати формули чи турбуватися про відносні посилання.

Функція MAKEARRAY (MAKEARRAYFILE): створення обчислюваних масивів

Функція MAKEARRAY (іноді позначається як ARCHIVOMAKEARRAY) дозволяє створювати абсолютно новий масив, вказуючи кількість рядків і стовпців та обчислюючи кожен елемент за допомогою функції LAMBDA. Вона не починає з існуючого діапазону, а будує масив з нуля.

Його загальний синтаксис такий:

=MAKEARRAY(рядки; стовпці; ЛЯМБДА(рядок; стовпець; вираз))

Аргумент `rows` вказує, скільки рядків матиме вихідна матриця, `columns` визначає кількість стовпців, а функція LAMBDA отримує індекси рядків і стовпців, що обчислюються на кожній ітерації, як параметри. За допомогою цієї інформації можна побудувати практично будь-який числовий або текстовий шаблон.

Дуже показовим прикладом є створення матриці, де кожен елемент вказує свою власну позицію. У будь-якій комірці можна написати щось на кшталт:

=MAKEARRAYFILE(3; 2; ЛЯМБДА(рядок; стовпець; -(рядок та стовпець)))

Результатом буде матриця розміром 3 рядки на 2 стовпці , де кожне значення представляє комбінацію рядка та стовпця (наприклад, 11, 12, 21, 22, 31, 32), перетворені відповідно до введеного вами обчислення (у цьому випадку знак мінус застосовується до об'єднання рядків і стовпців).

Ще одне цікаве використання MAKEARRAY — це перетворення вектора в масив , контролюючи кількість елементів. Припустимо, ви хочете створити масив з першими 6 значеннями вертикального діапазону. Ви можете спочатку створити масив позицій за допомогою MAKEARRAYFILE, отримати k найменших значень позицій, а нарешті використати INDEX для отримання фактичних елементів з вихідного діапазону.

Приклад формули, що поєднує кілька функцій, може мати таку структуру:

=LET(arrPos; MAKEARRAYFILE(3; 2; ЛЯМБДА(рядок; стовпець; -(рядок та стовпець))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Тут LET використовується для визначення проміжних імен (arrPos, arrPosF), масив позицій будується за допомогою ARCHIVOMAKEARRAY (3×2), 6 найменших позицій вибираються за допомогою SMALLEST та SEQUENCE, і, нарешті, INDEX використовується для повернення відповідних значень з діапазону G8:G13. Це потужний приклад того, як поєднувати LAMBDA та динамічні функції масивів для виконання складних перетворень без макросів.

Функція MAP: поелементне перетворення

Функція MAP використовується для одночасного перебору одного або кількох масивів та повернення нового масиву, де кожен вихідний елемент обчислюється шляхом застосування функції LAMBDA до відповідного(их) вхідного(их) елемента(ів). Вона еквівалентна класичній функції "map" у функціональному програмуванні.

Основний синтаксис:

=MAP(матриця1; ЛЯМБДА_або_більше_матриць)

У своїй найпростішій формі, вона приймає один масив та функцію LAMBDA, яка отримує кожне значення з цього масиву. Ця функція LAMBDA перетворює значення та повертає нову версію, яка буде частиною вихідного масиву, зберігаючи ті ж розміри, що й вихідний масив.

Наприклад, якщо ви хочете пройтися по вертикальному діапазону A21:A26 і залишити початкове число, якщо воно парне, або дефіс, якщо воно непарне, ви можете використовувати щось на кшталт:

=MAP($A$21:$A$26; ЛЯМБДА(параметр1; ЯКЩО(ES.PAR(параметр1); параметр1; «-«)))

У цьому випадку MAP аналізує кожен елемент A21:A26. LAMBDA перевіряє за допомогою IS.EVEN, чи є число парним. Якщо так, вона повертає саме число; інакше повертає дефіс. Результатом є масив того ж розміру, що й вихідний діапазон, але з перетворенням, застосованим до кожного елемента.

Такий підхід дуже корисний, коли потрібно застосувати умовну логіку, перетворення тексту, нормалізацію значень або будь-яку іншу просту операцію, уникаючи допоміжних стовпців та повторюваних формул.

Функція SCAN: кумулятивні та проміжні результати

Функція SCAN використовується для перевірки масиву шляхом застосування функції LAMBDA до кожного значення та генерації вихідного масиву, що відображає всі проміжні значення процесу накопичення. Вона дуже схожа на REDUCE, але замість того, щоб повертати лише кінцевий результат, вона зберігає кожен крок.

Його загальний синтаксис такий:

=СКАНУВАННЯ(;масив;ЛЯМБДА(акумулятор;значення))

Перший аргумент, який є необов'язковим, – це початкове значення акумулятора (наприклад, 0, якщо ви додаєте). Другий аргумент – це масив або діапазон, який потрібно перебрати. Нарешті, функція LAMBDA отримує два параметри: акумулятор (частковий результат до цієї точки) та поточне значення масиву, який ви обробляєте.

На кожному кроці SCAN обчислює значення LAMBDA, оновлює акумулятор та генерує новий елемент у вихідній матриці з результуючим значенням. Таким чином, ви отримуєте послідовність накопичених значень або прогресивних перетворень.

Типовим прикладом є обчислення кумулятивного підсумку (проміжного підсумку) за набором значень і, звідти, отримання відносної кумулятивної частоти. Уявіть, що у вас є дані в A31:A36, і вам потрібен абсолютний кумулятивний підсумок:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))

Ця формула перебирає діапазон A31:A36, додаючи кожне значення до попереднього підсумку. Результатом є масив з такою ж кількістю елементів, як і у вихідному діапазоні, але кожна позиція відображає сукупний підсумок до цієї точки.

З цього сумарного значення легко обчислити сумарну відсоткову частоту, поділивши кожен сумарний підсумок на загальний підсумок. Наприклад, можна спочатку визначити підсумок за допомогою SUM, а потім знову застосувати SCAN:

=LET(загальний; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/total)

У цьому випадку LET присвоює підсумку суму всього діапазону A31:A36. Потім SCAN генерує послідовність кумулятивних значень і, поділивши її на загальну кількість, отримує відносну кумулятивну частоту для кожного кроку , все в одній матричній формулі.

Функція REDUCE: зведення до одного накопиченого значення

Функція REDUCE також перебирає масив, застосовуючи функцію LAMBDA до кожного елемента, але на відміну від SCAN, тут вас цікавить лише отримання кінцевого результату процесу накопичення. Тобто вона виконує той самий тип ітерації, що й SCAN, але повертає лише останнє значення в акумуляторі.

Його синтаксис:

=ЗМЕНШИТИ(; масив; ЛЯМБДА(акумулятор; значення))

Як і в SCAN, initial_value встановлює початкову точку акумулятора, matrix – це діапазон, який потрібно обробити, а LAMBDA має як параметри поточний акумулятор та значення, яке зчитується в цей момент.

Дуже типовим використанням є обчислення поточної суми або кумулятивної операції, де вас цікавить лише останній результат . Наприклад, щоб підсумувати A1:A6 за допомогою REDUCE, ви можете написати:

=REDUCE(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Тут REDUCE перебирає клітинки A1:A6 і на кожному кроці оновлює кумулятивне значення , додаючи значення поточної комірки (param1). Зрештою, повертається одне значення: загальна сума. Концептуально це схоже на використання SUM, але за допомогою REDUCE можна визначити будь-яку складнішу логіку накопичення, а не лише прості суми.

Сила REDUCE полягає в його здатності працювати з попереднім результатом на кожному кроці та продовжувати застосовувати операції до завершення процесу. Це дозволяє реалізовувати складні користувацькі обчислення, які традиційно оброблялися за допомогою циклів у макросах.

Створення користувацьких функцій за допомогою LAMBDA та менеджера імен

Одна з найпотужніших функцій LAMBDA — це її здатність перетворювати будь-яку формулу на користувацьку функцію за допомогою диспетчера імен Excel. Це дозволяє вашій функції мати власне ім'я та використовувати її як будь-яку іншу вбудовану функцію в програмі.

Типовий робочий процес такий: спочатку перевірте функцію LAMBDA у комірці , включаючи як визначення, так і виклик із прикладами аргументів. Після того, як ви переконаєтеся, що вона працює правильно та повертає очікуваний результат, скопіюйте частину, що відповідає визначенню LAMBDA (без остаточного виклику), та вставте її в Менеджер імен.

У Менеджері імен ви створюєте нову назву (наприклад, MyVATFunction, MyDiscount, MyWeightedAverage тощо) і в полі «Посилається на» вводите:

=LAMBDA(перемінна1; змінна2; …; зміннаN; обчислення)

З цього моменту в будь-якій комірці вашої робочої книги ви можете викликати функцію, записавши її назву так, ніби це вбудована функція , передаючи значення параметрів у тому ж порядку, в якому ви їх визначили.

Це має дві очевидні переваги: ​​по-перше, це робить ваші електронні таблиці більш читабельними (замість довгих формул ви бачите функцію з описовою назвою); по-друге, це централізує логіку в одному місці. Якщо пізніше ви захочете змінити обчислення, просто змініть визначення в Менеджері імен, і всі формули, які його використовують, будуть оновлені автоматично.

Практичні аспекти та додаткові міркування

Щоб по-справжньому використовувати LAMBDA та пов’язані з ним функції, корисно зрозуміти деякі практичні аспекти їхньої поведінки та вимоги . По-перше, ці функції є частиною сучасних функцій Excel, тому вам потрібна версія, яка вже містить динамічні масиви та новіші функції LAMBDA, BYROW, BYCOL та інші. Зазвичай вони доступні в найновіших випусках Microsoft 365.

Ще одним важливим питанням є продуктивність : хоча LAMBDA та функції обходу є дуже потужними, якщо застосовувати їх до величезних діапазонів з дуже складною логікою, перерахунок у книзі може зайняти більше часу. Бажано розробляти LAMBDA-функції з урахуванням ефективності, уникаючи надлишкових обчислень та використовуючи структури, такі як LET, для визначення проміжних значень, які можна використовувати повторно.

Також важливо дотримуватися узгодженої схеми іменування параметрів і функцій . Використання описових імен допомагає зрозуміти логіку, коли ви повертаєтеся до файлу через кілька місяців або коли комусь іншому потрібно буде працювати з вашими робочими книгами. Параметр з іменем amount, rate, dataRow або valuesCol набагато зрозуміліший, ніж просто x або yoa.

Щодо помилки #CALC!, вона зазвичай з'являється, коли Excel не може обчислити вираз масиву або коли функція LAMBDA не повертає коректний результат. Завжди перевіряйте, чи ваша формула має чітко визначений вивід, а якщо ви працюєте з функціями масиву, чи розмірності узгоджені (наприклад, чи не поєднуються несумісні діапазони розмірів без належного перетворення).

Зрештою, хоча LAMBDA у багатьох випадках усуває потребу в VBA, він не замінює його повністю. Бувають ситуації, коли автоматизація макросів залишається найкращим варіантом, але для величезної кількості користувацьких обчислень і перетворень даних LAMBDA та пов'язані з ним функції дозволяють вам виконувати всю роботу в межах звичайного середовища формул Excel.

Завдяки цим можливостям, ті, хто щодня працює з електронними таблицями, тепер мають набагато гнучкіші інструменти для розробки власних розрахунків, узагальнення інформації за рядками або стовпцями, повного або часткового обходу матриць, генерації детальних підсумків та створення нових матриць, що обчислюються на льоту, і все це без необхідності виходити з мови формул, яку вони вже знають.