LET و LAMBDA: فرمول‌های خوانا و ساختن تابع اختصاصی بدون VBA

نام‌گذاری درون فرمول و تابع‌سازی

فرمول‌های حرفه‌ای معمولاً یک عبارت را چند بار تکرار می‌کنند؛ مثلاً یک FILTER طولانی که هم باید شمرده شود و هم نمایش داده شود. LET (Excel 2021 و 365) اجازه می‌دهد به هر بخش یک نام بدهید، یک بار محاسبه‌اش کنید و بارها استفاده کنید. LAMBDA (365 و 2024) یک قدم جلوتر می‌رود: فرمول را به تابعی با نام دلخواه تبدیل می‌کند که در کل فایل مثل SUM صدا زده می‌شود.

LET

=LET(نام1, مقدار1, نام2, مقدار2, …, محاسبه‌ی_نهایی)

بدون LET (FILTER دو بار محاسبه می‌شود):
=IF(ROWS(FILTER(Sales, Sales[Rep]=G1))>0, SUM(CHOOSECOLS(FILTER(Sales, Sales[Rep]=G1),6)), 0)

با LET:
=LET(
  hits,  FILTER(Sales, Sales[Rep]=G1, ""),
  amt,   CHOOSECOLS(hits, 6),
  IF(ISNUMBER(INDEX(amt,1)), SUM(amt), 0)
)

LAMBDA؛ از فرمول به تابع

مراحل: ۱) فرمول را در یک سلول با LAMBDA بنویسید و با پرانتز دوم آزمایش کنید؛ ۲) Formulas ← Name Manager ← New؛ نام تابع را بنویسید و در Refers to خود LAMBDA را (بدون پرانتز آزمایشی) بچسبانید؛ ۳) در Comment توضیح بدهید که هنگام تایپ نمایش داده می‌شود.

آزمایش در سلول:
=LAMBDA(x, x*2)(21)         → 42

تابع CLEANFA (پاک‌سازی ی/ک، ارقام فارسی و عربی، NBSP) — در Name Manager:
=LAMBDA(txt,
  LET(
    yk, SUBSTITUTE(SUBSTITUTE(txt, UNICHAR(1610), UNICHAR(1740)), UNICHAR(1603), UNICHAR(1705)),
    dg, REDUCE(yk, SEQUENCE(10,,0), LAMBDA(s,i,
          SUBSTITUTE(SUBSTITUTE(s, UNICHAR(1776+i), i), UNICHAR(1632+i), i))),
    TRIM(SUBSTITUTE(dg, CHAR(160), " "))
  )
)

تابع COMMISSION:
=LAMBDA(amount, amount * XLOOKUP(amount, {0,2E9,5E9}, {0.01,0.02,0.03}, , -1))

استفاده:
=CLEANFA(A2)
=--CLEANFA(B2)                 ← «۱۲۵۰۰۰۰» به عدد
=MAP(A2:A500, CLEANFA)         ← روی کل ستون
=COMMISSION(C2)

توابع کمکی LAMBDA

نکته‌هایی که کمتر کسی می‌داند

• LET فقط خوانایی نیست؛ سرعت هم هست. عبارتی که با نام تعریف شده یک بار محاسبه می‌شود، در حالی که بدون LET هر تکرار دوباره محاسبه می‌شد.
• پارامتر اختیاری در LAMBDA را با کروشه نمی‌نویسید؛ فراخواننده می‌تواند آن را خالی بگذارد و شما با ISOMITTED(param) مقدار پیش‌فرض بدهید.
• توابع LAMBDA داخل فایل ذخیره می‌شوند؛ با کپی کردن یک شیت که از آن‌ها استفاده می‌کند به فایل مقصد، تعریف‌ها هم منتقل می‌شوند — راه ساده‌ی پخش یک «کتابخانه‌ی تابع» در سازمان.
• LAMBDA می‌تواند خودش را صدا بزند (بازگشتی)، به شرطی که با نام در Name Manager تعریف شده باشد؛ مثلاً پیمایش یک ساختار درختی کالا.
• در فایلی که در Excel 2021 باز می‌شود، LET کار می‌کند اما LAMBDA و MAP و REDUCE خطای #NAME? می‌دهند؛ برای فایل‌های مشترک، فقط LET را به کار ببرید یا نتیجه را Paste Values کنید.