وقتی مایکروسافت توابع کمکی LAMBDA را به اکسل افزود، من زیاد به آنها اهمیت ندادم. اما پس از اینکه در نهایت آنها را امتحان کردم، متوجه شدم که یک کشف بزرگ هستند. این توابع با توابع LAMBDA کار میکنند و به شما امکان میدهند توابع سفارشی را بر روی ردیفها، ستونها و حتی آرایههای کامل اعمال کنید بدون این که چندین فرمول یا VBA پیچیده بنویسید. اگرچه در اکسل هفت تابع کمکی LAMBDA وجود دارد، من بر روی چهار تا که بیشتر استفاده میکنم—BYCOL، BYROW، SCAN و REDUCE—تمرکز میکنم.
من وقتی مایکروسافت این توابع کمکی LAMBDA را به اکسل افزود، زیاد به آنها فکر نکردم. اما پس از اینکه سرانجام آنها را امتحان کردم، یک کشف بزرگ بودند. آنها با توابع LAMBDA کار میکنند، که به شما اجازه میدهد توابع سفارشی را به سطرها، ستونها و حتی آرایههای کامل اعمال کنید بدون نوشتن چندین فرمول یا VBA پیچیده. در حالی که هفت تابع کمکی LAMBDA در اکسل وجود دارد، من بر چهار تا که بیشترین استفاده را از آنها میکنم تمرکز میکنم — BYCOL، BYROW، SCAN و REDUCE.
اگر تا به حال با توابع LAMBDA کار نکردهاید، این توابع کمکی ممکن است ترسناک بهنظر برسند. خبر خوب این است که برای استفاده از آنها نیازی به مطالعه عمیق LAMBDA ندارید. اما پیش از ادامه، اطمینان حاصل کنید که از اکسل ۳۶۵ یا اکسل ۲۰۲۴ استفاده میکنید اگر میخواهید این فرمولها را بهکار ببرید (اکسل ۲۰۲۱ و نسخههای قبلی از آنها پشتیبانی نمیکنند).
BYCOL
اعمال LAMBDAها بر ستونها
فرض کنید با دادههای فروش گستردهای کار میکنید که در آن ستونها نشاندهنده محصولات، سطرها ماهها و سلولها اعداد فروش هستند. اگر بخواهید مجموع فروش هر یک را بهدست آورید، فرض کنید ارقام فروش محصول A در محدوده B2:B6 هستند، اولین فرمولی که مینویسید شبیه این در سلول B7 خواهد بود:
=SUM(B2:B6)
اگر ۲۰ محصول داشته باشید، باید ۲۰ فرمول جداگانه بنویسید. نکته مثبت این است که این کار زمانبر نیست، چون میتوانید فرمول را به سلولهای مجاور کپی کنید. نکته منفی این است که این کار صفحهگستره شما را شلوغ میکند، بهروزرسانی فرمولها را در آینده دشوارتر میسازد و خطر خطاها را افزایش میدهد.
بهجای آن میتوانید از تابع BYCOL استفاده کنید. این تابع یک تابع سفارشی (LAMBDA) را بر هر ستون یک آرایه یا بازه اعمال میکند و سپس یک نتیجه برای هر ستون بهصورت یک آرایه افقی باز میگرداند.
=BYCOL(array, lambda)
ادامه با مثال محصولات. فرض کنید سه محصول داریم و ارقام فروش در بازه B2:D6 هستند، فرمول زیر هر یک از ستونها را بهخوبی جمع میکند.
=BYCOL(B2:D6, LAMBDA(myCol, SUM(myCol)))
در LAMBDA، یک پارامتر به نام myCol تعریف میکنم که BYCOL بهصورت یکبهیک هر ستون را از بازه B2:D6 میگذارد. سپس این پارامتر را در تابع SUM استفاده میکنم. حتی اگر کل بازه را پاس بدهیم، BYCOL ستون فروش را ستون به ستون جمع میکند و هر یک را در یک ستون جداگانه باز میگرداند.
اگر تا به حال از این همه گفتوگوی LAMBDA دلگیر نشدهاید، نکته کلیدی این است: نیازی به استفاده از LAMBDA ندارید. میتوانید از هر تابع نامگذاری شدهای، از جمله SUM، بهصورت مستقیم استفاده کنید. اما اگر نیاز به محاسبهٔ پیچیدهای دارید، با LAMBDAها بمانید.
=BYCOL(B2:D6, SUM)
اگر نامهای محصولات در ستونهای B1:D1 (سطر سرصفحه) قرار دارند، فرمول زیر که از تابع FILTER استفاده میکند، باید فقط محصولاتی را که فروش کلی آنها بالای ۶۰۰ دلار است، برگرداند
=FILTER(B1:D1, BYCOL(B2:D6, LAMBDA(myCol, SUM(myCol))) > 600, "")
BYROW
اعمال LAMBDAها بر سطرها
تابع BYROW یک LAMBDA را بر هر سطر در یک آرایه یا بازه اعمال میکند و یک نتیجه برای هر ستون بهصورت یک آرایه عمودی باز میگرداند. این تابع برای کار با دادههای طولانی ایدهآل است.
BYROW(array, lambda)
با ادامه مثال بخش قبلی، میتوانیم هر سطر را با یک فرمول واحد توسط تابع BYROW جمع کنیم.
=BYROW(B2:D6, LAMBDA(myRow, SUM(myRow)))
دوباره، اگر محاسبهای که نیاز دارید ساده باشد، میتوانید مستقیماً از تابع SUM استفاده کنید.
=BYROW(B2:D6, SUM)
SCAN
ردیابی مجموعهای تجمعی
تابع SCAN بهترین استفاده را زمانی دارد که به یک مجموع تجمعی نیاز دارید — جمعی که بهصورت سطر به سطر انباشته میشود و مقدار انباشتهشده در هر مرحله نشان داده میشود. این تابع یک مقدار شروع، یک آرایه یا بازه و یک تابع LAMBDA میگیرد. سپس یک آرایه از نتایج میانی را باز میگرداند که با مقدار شروع آغاز میشود.
SCAN(start_value, array, lambda)
اگر بخواهیم مجموع تجمعی محصول A را از مثال قبلی محاسبه کنیم، فرمول بهصورت زیر خواهد شد:
=SCAN(0, B2:B6, LAMBDA(a, v, a + v))
دوباره، این بهنظر پیچیده میآید، اما پس از تجزیه آن، فهم آن آسان است.
پارامتر start_value برابر 0 است، به این معنی که میخواهیم فروش را از صفر شروع کنیم. پارامتر array که مقدار مورد جمع را دارد، B2:B6 است. در LAMBDA، a نتیجهٔ جاری است که ردیابی میکنیم و v مقدار فعلی آرایه است.
REDUCE
چندین به یک تبدیل میشوند
تابع REDUCE شبیه SCAN است، اما فقط نتیجهٔ نهایی را میدهد، نه یک آرایه از نتایج میانی. بهعبارت دیگر، همه نتایج در یک مقدار واحد جمع میشوند.
REDUCE(start_value, array, lambda)
برای نشان دادن مفید بودن آن، من از مثال جمع استفاده نمیکنم چون فقط نتیجهٔ نهایی را نشان میدهد و اطلاعات زیادی به ما نمیدهد. اما فرض کنید مجموعهای از برچسبها در بازه A2:A6 وجود دارد. میتوانم یک رشته با استفاده از فرمول زیر بسازم:
=REDUCE("", A2:A6, LAMBDA(str, tag, str & ", " & tag))
پارامتر start_value برابر “” (یک رشتهٔ خالی) است و A2:A6 بازه است. در LAMBDA، پارامتر str رشتهای است که میسازیم و tag برچسب جاری در آرایه است.
بخش str & “, ” & tag در LAMBDA رشته را میگیرد و برچسب را بهصورت تجمعی به آن اضافه میکند. بنابراین اگر str در حال حاضر computers, mobile باشد و tag برابر video games باشد، رشتهٔ ساختهشده تا کنون computers, mobile, video games خواهد بود.
مشکل این فرمول این است که یک کامای پیشرو دارد. میتوانیم آن را با استفاده از تابع TEXTAFTER حذف کنیم.
=TEXTAFTER(REDUCE("", A2:A6, LAMBDA(str,tag, str & ", " & tag)), ", ")
قدرت LAMBDAها برآشفت
در ابتدا، من قدرت این توابع کمکی LAMBDA را درک نمیکردم. میدانم بخش LAMBDA میتواند مفهوم سختی باشد. اما وقتی عمیقاً به آنها پرداختم و به ذهنم اجازه دادم که آنها ذهنم را بشکنند، روشی کاملاً جدید برای نوشتن فرمولها کشف کردم. دیگر توابع کمکی LAMBDA که میتوانید بررسی کنید MAP، MAKEARRAY و ISOMITTED هستند.