Як користуватися функцією Excel LAMBDA -

Зміст

Резюме

Функція Excel LAMBDA забезпечує створення спеціальних функцій, які можна використовувати повторно у всій книзі, без VBA або макросів.

Призначення

Створіть власну функцію

Повернене значення

Як визначено формулою

Синтаксис

= LAMBDA (параметр,…, розрахунок)

Аргументи

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

Версія

Excel 365

Примітки щодо використання

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

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

Приклад 1 | Приклад 2 | Приклад 3

Створення функції LAMBDA

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

Існує чотири основних етапи створення та використання власної формули на основі функції LAMBDA:

  1. Перевірте логіку, яку ви будете використовувати, зі стандартною формулою
  2. Створіть та протестуйте загальну (неназвану) версію формули LAMBDA
  3. Назвіть і визначте формулу LAMBDA за допомогою менеджера імен
  4. Перевірте нову спеціальну функцію, використовуючи визначене ім'я

У наведених нижче прикладах докладніше обговорюються ці кроки.

Приклад 1

Щоб проілюструвати, як працює LAMBDA, почнемо з дуже простої формули:

=x*y // multiple x and y

У Excel ця формула зазвичай використовує посилання на клітинки, як це:

=B5*C5 // with cell references

Як бачите, формула чудово працює, тому ми готові перейти до створення загальної формули LAMBDA (неназвана версія). Перше, що слід врахувати, це якщо формула вимагає входів (параметрів). У цьому випадку відповідь "так" - формула вимагає значення x, а значення y. Після цього ми починаємо з функції LAMBDA і додаємо необхідні параметри для введення користувачем:

=LAMBDA(x,y // begin with input parameters

Далі нам потрібно додати фактичний розрахунок x * y:

=LAMBDA(x,y,x*y)

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

=LAMBDA(x,y,x*y)(B5,C5) // testing syntax

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

Тепер ми готові назвати функцію LAMBDA за допомогою Менеджера імен. Спочатку виберіть формулу, * не включаючи * параметри тестування в кінці. Далі відкрийте диспетчер імен за допомогою комбінації клавіш Control + F3 та натисніть кнопку Створити.

У діалоговому вікні «Нове ім’я» введіть ім’я «XBYY», залиште область обсягу робочою книгою та вставте скопійовану формулу у область введення «Надіслати на».

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

Нова спеціальна функція повертає той самий результат, що й інші дві формули.

Приклад 2

У цьому прикладі ми перетворимо формулу для обчислення обсягу кулі у власну функцію LAMBDA. Загальна формула Excel для обчислення обсягу кулі:

=4/3*PI()*A1^3 // volume of sphere

де А1 - радіус. Наведений нижче екран показує цю формулу в дії:

Зверніть увагу, що для обчислення обсягу для формули потрібен лише один вхід (радіус), тому нашій функції LAMBDA знадобиться лише один параметр (r), який з’явиться як перший аргумент. Ось формула, перетворена на LAMBDA:

=LAMBDA(r,4/3*PI()*r^3) // generic lambda

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

Результати загальної формули LAMBDA точно такі ж, як і вихідна формула, тому наступним кроком є ​​визначення та назва цієї формули LAMBDA за допомогою Менеджера імен, як пояснено вище. Ім’я, яке використовується для функції LAMBDA, може бути будь-яким дійсним іменем Excel. У цьому випадку ми будемо називати формулу "SphereVolume".

Повернувшись на аркуші, ми замінили загальну (неназвану) формулу LAMBDA на названу версію LAMBDA і ввели B5 для r. Зверніть увагу, що результати, повернуті користувацькою функцією SphereVolume, точно такі ж, як і попередні результати.

Приклад 3

У цьому прикладі ми створимо функцію LAMBDA для підрахунку слів. Excel не має для цього функції, але ви можете підраховувати слова в комірці за допомогою власної формули на основі функцій LEN і ЗАМІНИ, як це:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Детальне пояснення читайте тут. Ось формула, що діє на робочому аркуші:

Зверніть увагу, що ми отримуємо неправильний рахунок 1, коли формула отримує порожню клітинку (B10). Ми розглянемо цю проблему нижче.

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

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

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

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)(B5)

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

Нижче ми замінили загальну (неназвану) формулу LAMBDA на названу версію LAMBDA та ввели B5 для тексту. Зверніть увагу, що ми отримуємо абсолютно однакові результати.

Формула, яка використовується в диспетчері імен для визначення CountWords, така ж, як і вище, без синтаксису тестування:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Виправлення проблеми з порожнім осередком

Як зазначалося вище, наведена вище формула повертає неправильний підрахунок 1, коли клітинка порожня. Цю проблему можна виправити, замінивши +1 кодом нижче:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Повне пояснення тут. Щоб оновити існуючу формулу з іменем LAMDA, нам знову потрібно скористатися Диспетчером імен:

  1. Відкрийте Диспетчер імен
  2. Виберіть назву "CountWords" і натисніть "Редагувати"
  3. Замініть код "Посилається на" на цю формулу:
=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))

Після закриття диспетчера імен CountWords працює правильно на порожніх клітинках, як показано нижче:

Примітка. Одноразове оновлення коду в диспетчері імен одночасно оновлює всі екземпляри формули CountWords. Це ключова перевага спеціальних функцій, створених за допомогою LAMBDA - оновленнями формули можна керувати в одному місці.

Цікаві статті...