تابع LAMBDA در اکسل – آموزش ساخت توابع سفارشی با مثال

آزاده عباسی
آزاده عباسی
۱۴۰۵/۰۸/۳۰
۱۳ دقیقه
۲
آموزش متنی جامع

تابع LAMBDA یکی از توابع اکسل در نسخه مایکروسافت ۳۶۵ است که برای ساخت فرمول‌ها و توابع سفارشی کاربرد دارد. به این شکل که بعد از تعریف محاسبه دلخواه خود در قالب یک فرمول و نام‌گذاری آن، تابع ساخته شده را در فایل اکسل استفاده می‌کنیم. با تعریف تابع سفارشی در قالب تابع LAMBDA در اکسل نیازی به کپی کردن فرمول‌های تکراری نداریم. بنابراین فرمول‌های طولانی ساده‌تر می‌شوند و انجام محاسبات پیچیده نیز سریع‌تر خواهد بود. در این مطلب از مجله فرادرس ابتدا روش فرمول‌نویسی با تابع LAMBDA و کاربردهای آن را همراه مثال‌های مختلف یاد می‌گیریم. سپس با محدودیت‌های کار با این تابع و نحوه برطرف کردن پیام‌های خطا آشنا می‌شویم.

آنچه در این مطلب می‌آموزید:
  • با فرمول کلی تابع LAMBDA در اکسل آشنا خواهید شد.
  • مراحل ساخت تابع سفارشی با تابع LAMBDA در اکسل را همراه مثال ساده می‌آموزید.
  • کاربردهای مهم تابع LAMBDA در اکسل همراه شش مثال را یاد می‌گیرید.
  • با محدودیت‌ها و چالش‌های کار با تابع LAMBDA در اکسل آشنا می‌شوید.
  • نحوه رفع پیام‌های خطا هنگام کار با تابع LAMBDA را درک می‌کنید.
تابع LAMBDA در اکسل – آموزش ساخت توابع سفارشی با مثال

مطالب مجله فرادرس را در جستجوی گوگل سریع‌تر ببینید

997696

فرمول کلی تابع LAMBDA در اکسل

فرمول کلی تابع LAMBDA در اکسل به شرح زیر است.

=LAMBDA([parameter1, parameter2, …,] calculation)

در این فرمول هر آرگومان به شکل زیر تعریف می‌شود.

  • Parameter(آرگومان اختیاری): متغیرهای ورودی که در قالب یک مقدار مانند عدد، متن، محدوده سلول یا مقدار منطقی تعریف می‌کنیم. این آرگومان را حداکثر تا ۲۵۳ مورد می‌توانیم در تابع تعریف کنیم.
  • Calculation(آرگومان اجباری): فرمول یا محاسبه‌ دلخواهی که باید اجرا شود. این آرگومان، آخرین بخش تابع است و یک نتیجه را برمی‌گرداند.
فرمول کلی ساخت تابع سفارشی-تابع LAMBDA در اکسل
فرمول کلی تابع LAMBDA در اکسل

در فرمول‌نویسی با تابع LAMBDA رعایت قوانین استاندارد نام‌گذاری اکسل ضروری است. بنابراین حتما به نکات زیر توجه می‌کنیم.

  • استفاده از علامت نقطه در نام‌گذاری مجاز نیست.
  • در نام‌گذاری آرگومان Parameter از نام‌هایی مانند «a1» یا «val1» که ممکن است با نشانی سلول در اکسل اشتباه گرفته شوند، استفاده نمی‌کنیم.
همچنین، رعایت اصول فرمول‌نویسی برای جلوگیری از خطای احتمالی بسیار اهمیت دارد. در غیر این صورت با پیام‌های خطای اکسل روبرو می‌شویم که برطرف کردن آن‌ها زمان‌بر و خسته‌کننده است. در ادامه مطلب با بررسی مثال‌های مختلف تا حد زیادی روش درست فرمول‌نویسی را یاد می‌گیریم. اما برای تکمیل یادگیری پیشنهاد می‌کنیم فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل در فرادرس را مشاهده کنید.

مراحل ساخت تابع سفارشی در اکسل

برای ساخت تابع سفارشی با تابع LAMBDA در اکسل چهار مرحله زیر را دنبال می‌کنیم.

  1. نوشتن فرمول اصلی محاسبات
  2. ایجاد فرمول LAMBDA در یک سلول
  3. نام‌گذاری تابع LAMBDA
  4. فراخوانی تابع سفارشی
مراحل ساخت تابع سفارشی در اکسل
مراحل ساخت تابع سفارشی در اکسل

حال، هر یک از این مراحل را همراه مثال توضیح می‌دهیم.

۱. نوشتن فرمول اصلی محاسبات

در بیشتر موارد اولین مرحله در استفاده از تابع LAMBDA، نوشتن یک فرمول اصلی است که محاسبه مورد نظر ما را انجام می‌دهد.

به‌عنوان مثال در جدول زیر می‌خواهیم درصد اختلاف بین قیمت واقعی و قیمت تخمینی چند کالا را حساب کنیم.

جدول قیمت کالا-توابع سفارشی
برای محاسبه اختلاف قیمت فرمول C2/B2-1 را در سلول D2 می‌نویسیم. از آنجا که احتمال دارد حالت تقسیم بر صفر در فرمول ایجاد شود، برای رفع خطا از تابع IFERROR به شکل زیر استفاده می‌کنیم.
=IFERROR(C2/B2-1,"-")

در این فرمول اگر تقسیم بر صفر اتفاق بیفتد، تابع به‌جای پیام خطا، علامت - را به‌عنوان خروجی نشان می‌دهد.

تا این مرحله نتیجه محاسبه به شکل زیر درمی‌آید.

نتیجه محاسبه اختلاف قیمت-تابع سفارشی

۲. ایجاد فرمول LAMBDA در یک سلول

در این مرحله ابتدا مقادیر ورودی تابع LAMBDA یعنی آرگومان Parameter را تعریف می‌کنیم. فرمول نمونه ما دو ورودی «قیمت واقعی» و «قیمت تخمینی» دارد. بنابراین آرگومان‌ها را به ترتیب «new» و «old» تعریف می‌کنیم. سپس فرمول محاسبه اختلاف قیمت را با توجه به این پارامترها نام‌گذاری می‌کنیم.

بنابراین فرمول تابع LAMBDA به شکل زیر درمی‌آید.

=LAMBDA(old, new, IFERROR(new/old-1, "-"))

اگر در این مرحله فرمول را در یک سلول وارد کنیم، خطای#CALC! را می‌بینیم. زیرا هنوز هیچ مقدار ورودی برای محاسبه به آن نداده‌ایم.

پیام خطای محاسبات-تابع سفارشی

برای رفع خطا، مقادیر ورودی را به‌صورت جداگانه مطابق فرمول زیر در انتهای فرمول تعریف می‌کنیم.

=LAMBDA(old, new, IFERROR(new/old-1, "-"))(B2, C2)

برای اطمینان از کارکرد درست فرمول، آن‌ را در جدول امتحان می‌کنیم.

اولین آزمایش تابع-تابع lambda در اکسل

نتیجه نشان می‌دهد که تا این مرحله، فرمول‌نویسی صحیح است. بنابراین به مرحله آخر می‌رویم.

۳. نام‌گذاری تابع LAMBDA

بعد از آزمایش فرمول، به شکل زیر تابع LAMBDA را نام‌گذاری می‌کنیم.

  • فرمول LAMBDA را بدون بخش فراخوان ورودی‌ها مانند نمونه زیر کپی می‌کنیم.
=LAMBDA(old, new, IFERROR(new/old-1, "-"))
  • دو کلید Ctrl+F3 را هم‌زمان فشار می‌دهیم تا پنجره «Name Manager» باز شود. سپس روی بخش «New» کلیک می‌کنیم.
پنجره نامگذاری-تابع سفارشی
  • مانند تصویر زیر بخش‌های مختلف را پر می‌کنیم.
پر کردن بخش‌های مختلف فرمول-تابع lambda در اکسل

در این مرحله ابتدا در بخش «Name» نام دلخواه تابع سفارشی مانند «PercentVar» را وارد می‌کنیم. سپس در بخش «Refers to» فرمول کپی شده را «Paste» می‌کنیم. فرمول کپی شده حتما باید علامت مساوی را داشته باشد.

  • با زدن دکمه «Ok» یک تابع سفارشی به‌نام «PercentVar» برای محاسبه اختلاف قیمت در اکسل ساخته‌ایم.
فرمول سفارشی ساخته شده-تابع lambda در اکسل

برای ویرایش فرمول نیز کافی است کلید F2 را فشار دهیم و تغییرات دلخواه را دنبال کنیم.

۴. فراخوانی تابع سفارشی

در آخرین مرحله، تابع سفارشی ساخته شده را فراخوان می‌کنیم. برای این کار کافی است در سلول D2 نام تابع یعنی «PercetVar» را بنویسیم. همان‌طور که مشخص است، اکسل مانند سایر توابع، این تابع را همراه فرمول خود فراخوان می‌کند.

فراخوان تابع سفارشی-تابع lambda در اکسل

با نوشتن ورودی‌ها و کپی کردن فرمول در سلول‌های دیگر، محاسبه با سرعت بالا انجام می‌گیرد و نتیجه به شکل زیر درمی‌‌آید.

تابع سفارشی ایجاد شده-تابع lambda در اکسل
ایجاد فرمول سفارشی در حجم بالای داده‌ها محاسبات را بسیار سریع‌تر می‌کند. در ادامه، کاربرد تابع LAMBDA در اکسل را همراه با مثال‌های کاربردی بهتر یاد می‌گیریم. پیش از آن، پیشنهاد می‌کنیم برای دسترسی همیشگی به مطالب آموزشی اکسل در گوشی موبایل خود، اپلیکیشن رایگان مجله فرادرس را نصب کنید:

یادگیری ترفندهای فرمول‌نویسی اکسل همراه با فرادرس

کار با تابع LAMBDA نیاز به آشنایی کامل با سایر توابع اکسل دارد. زیرا در اولین مرحله ساخت تابع سفارشی، مهارت در فرمول‌نویسی و کار با سایر توابع اکسل بسیار اهمیت دارد. افراد با توجه به زمینه فعالیت خود ممکن است توابع شرطی، توابع مالی، توابع متنی و توابع محاسباتی مختلفی را برای این منظور استفاده کنند. بنابراین در اختیار داشتن مرجعی جامع و یکپارچه مانند مجموعه فیلم‌های آموزشی فرادرس دسترسی به پرکاربردترین توابع اکسل را به‌سادگی فراهم می‌کند. تنوع بالای موضوعات، اساتید باتجربه، امکان دریافت گواهینامه معتبر و تخفیف‌های ویژه برای کاربران فعال مسیر یادگیری اکسل همراه فرادرس را جذاب‌تر می‌کند. ضمن اینکه با تهیه یک اشتراک فرادرس به همه آموزش‌های مورد علاقه دسترسی خواهید داشت.

اما برای یادگیری تکمیلی ترفندهای فرمول‌نویسی با تابع LAMBDA پیشنهاد اول، مشاهده فیلم‌های آموزشی منتخب زیر است.

در مرحله بعدی علاقه‌مندان می‌توانند مطلب دلخواه را از دو مجموعه فیلم آموزشی زیر انتخاب کنند.

در ادامه مطلب، کاربرد تابع LAMBDA در فرمول‌نویسی با اکسل را همراه مثال‌های متنوع توضیح می‌دهیم.

چند مثال از کاربردهای تابع LAMBDA در اکسل

تا این بخش از مطلب، مبانی ساخت تابع سفارشی با تابع LAMBDA در اکسل را یاد گرفتیم. در ادامه، با شش کاربرد اصلی آن به شرح زیر آشنا می‌شویم.

  1. کوتاه کردن فرمول‌های بلند
  2. ساده‌سازی فرمول‌های پیچیده
  3. ساخت تابع سفارشی با چند پارامتر
  4. ساخت تابع سفارشی با فرمول‌نویسی آرایه‌ای
  5. ساخت تابع سفارشی با آرگومان‌های پیش‌فرض
  6. ساخت تابع سفارشی شمارش تعداد کلمات
چند مثال از کاربردهای مهم تابع LAMBDA در اکسل
چند مثال از کاربردهای مهم تابع LAMBDA در اکسل

این موارد مهم‌ترین کاربردهای تابع LAMBDA در اکسل هستند که برای درک بهتر هر مثال نیاز به آشنایی با سایر توابع اکسل داریم.

مثال ۱: کوتاه کردن فرمول‌های بلند

تابع LAMDA یکی از انتخاب‌های مناسب برای بهینه‌سازی فرمول‌های پیچیده است. به‌عنوان مثال در جدول زیر چند عبارت شامل متن و عدد داریم.

نمونه مثال رشته متنی-تابع سفارشی

برای استخراج اعداد از این رشته‌ها می‌توانیم از فرمول بسیار پیچیده زیر استفاده کنیم.

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

نوشتن این فرمول نیاز به مهارت بالای اکسل دارد. اما با کمی دقت، متوجه می‌شویم که برای نوشتن آن فقط به یک مقدار ورودی که همان رشته متنی است، نیاز داریم. بنابراین با تابع LAMBDA و انجام مراحل زیر فرمول را ساده‌تر می‌کنیم.

  • ابتدا پارامتر string را تعریف می‌کنیم. این پارامتر همان رشته متنی اصلی است که می‌خواهیم عدد را از آن استخراج کنیم.
  • سپس تمام مواردی را که در فرمول به سلول A2 ارجاع داده‌ شده‌اند، با پارامتر string جایگزین می‌کنیم.

با این‌کار فرمول به‌صورت خلاصه‌تر زیر درمی‌آید.

=LAMBDA(string, IF(SUM(LEN(string)-LEN(SUBSTITUTE(string, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&string, LARGE(INDEX(ISNUMBER(--MID(string, ROW(INDIRECT("$1:$"&LEN(string))),1))* ROW(INDIRECT("$1:$"&LEN(string))),0), ROW(INDIRECT("$1:$"&LEN(string))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(string)))/10),""))

بعد از آزمایش عملکرد صحیح فرمول، با استفاده از «Name Manager»، نام «ExtractNum» را برای تابع انتخاب می‌کنیم.

تعریف تابع استخراج عدد-تابع سفارشی

بنابراین برای جدا کردن اعداد، با فراخوانی این تابع سفارشی، فقط یک فرمول خلاصه را در جدول می‌نویسیم.

نمونه تابع خلاصه-تابع lambda در اکسل

نتیجه نهایی به شکل تصویر زیر است.

نتیجه نهایی استخراج عدد-تابع lambda در اکسل

مثال ۲: ساده‌سازی فرمول‌های پیچیده

ساخت فرمول سفارشی همراه با تابع LET و تابع LAMBDA روشی مناسب برای خواناتر کردن فرمول‌های پیچیده است. این دو تابع مکمل بسیار خوبی برای کاهش عبارت‌های تکراری هستند. ابتدا با تابع LET می‌توانیم متغیرهایی را تعریف کنیم و مقادیر مشخصی را به آن‌ها اختصاص دهیم. سپس با تابع LAMBDA ورودی‌های فرمول را نام‌گذاری می‌کنیم.

به‌عنوان مثال فرمول زیر برای تبدیل واحد اندازه‌گیری اینچ به فوت مناسب است.

=LET(
  input,B5,
  n,ROUND(ABS(input),0),
  sign,IF(input<0,"-",""),
  feet,INT(n/12),
  inches,MOD(n,12),
  sign&feet&"' "&inches&""""
)

در این فرمول نشانی سلول A1 فقط یک‌بار در فرمول نوشته شده است که در فرمول LET مقدار input به آن اختصاص داده می‌شود. این ساختار ایجاد تابع سفارشی با تابع LAMBDA را ساده می‌کند. برای این کار خط اول تابع LET را حذف می‌کنیم و متغیر input را به‌عنوان پارامتر ورودی تابع LAMBDA درنظر می‌گیریم.

=LAMBDA(input,
  LET(
    n,ROUND(ABS(input),0),
    sign,IF(input<0,"-",""),
    feet,INT(n/12),
    inches,MOD(n,12),
    sign&feet&"' "&inches&""""
  )
)

بنابراین اگر در «Name Manager» تابع سفارشی InchesToFeet را بسازیم، نتیجه برای یک جدول فرضی به شکل زیر درمی‌آید.

تابع سفارشی اینچ به فوت-تابع lambda در اکسل

به این شکل جدول فرضی اعداد زیر با تابع سفارشی ساخته شده بر حسب فوت و اینچ درمی آیند.

نتیجه تبدیل اعداد به فوت و اینچ-تابع سفارشی

برای آشنایی بیشتر با تابع LET در مطلب زیر نکات فرمول‌نویسی با آن را توضیح داده‌ایم.

مثال ۳: ساخت تابع سفارشی با چند پارامتر

در تابع LAMBDA تعریف حداکثر ۲۵۳ پارامتر مجاز است. اما این موضوع دلیلی بر تلاش برای تعریف پارامترهای زیاد نیست. زیرا هر اندازه تعداد پارامترها بیشتر باشد، استفاده از تابع LAMBDA سخت‌تر می‌شود. دو دلیل برای این موضوع وجود دارد.

  • برخی مواقع هنگام فراخوانی تابع سفارشی، اکسل آرگومان‌های آن را نشان نمی‌دهد. بنابراین همیشه باید پارامترها و ترتیب وارد کردن آن‌ها را به‌خاطر داشته باشیم.
  • پارامترها در تابع LAMBDA اختیاری هستند. تعریف تعداد پارامتر زیاد، زمانی دردسر ایجاد می‌کند که هنگام فراخوانی تابع بخواهیم فقط پارامترهای میانی را در فرمول بنویسیم. به‌عنوان مثال برای وارد کردن پارامتر پنجم در یک فرمول «۱۰» پارامتری، باید چهار جایگاه اول در فرمول را خالی بگذاریم. در این حالت تشخیص دقیق جایگاه درست پارامتر سخت است. بنابراین در هنگام ساخت تابع سفارشی باید فقط پارامترهایی را تعریف کنیم، که واقعا به آن‌ها نیاز داریم.

اما تعریف مناسب پارامترها، مانند مثال زیر کاربری فرمول را ساده می‌کند. فرض می‌کنیم درآمد سالانه یک شرکت را داریم.

درآمد فرضی شرکت-تابع سفارشی
می‌خواهیم با استفاده از تابع LAMBDA در اکسل یک تابع سفارشی برای محاسبه نرخ رشد مرکب سالانه (CAGR) بسازیم. این شاخص نشان می‌دهد که سرمایه بعد از گذشت یک زمان مشخص، به‌طور متوسط چه مقدار رشد داشته است. فرمول استاندارد محاسبه CAGR به شکل زیر است.
CAGR=(EV/BV)^(1/N)-1

این فرمول سه مقدار ورودی دارد که به شکل زیر آن‌ها را تعریف می‌کنیم.

  • BV: مبلغ ارزش اولیه سرمایه
  • EV:  مبلغ سرمایه بعد از دوره زمانی مشخص
  • N: مدت زمان

بنابراین با تابع LAMBDA این پارامترها به شکل زیر در می‌آیند.

=LAMBDA(BV, EV, N, (EV/BV)^(1/N)-1)

بعد از آزمایش عملکرد فرمول، نام تابع سفارشی را «CAGR» تعریف می‌کنیم. در فرایند نام‌گذاری بهتر است هر یک از ورودی‌ها را مانند تصویر زیر در بخش «Comment» توضیح دهیم. این کار درک فرمول را ساده می‌کند.

تعریف پارامترها-تابع سفارشی

تعریف پارامترها باعث می‌شود که در زمان فرمول‌نویسی، نام‌گذاری هر یک از این پارامترها به ما یادآوری شود.

یادآوری نامگذاری پارامتر-تابع سفارشی

در نهایت با جاگذاری صحیح اعداد در فرمول، مقدار CAGR را به شکل زیر محاسبه می‌کنیم.

محاسبه نرخ مرکب-تابع LAMBDA در اکسل

مثال ۴: ساخت تابع سفارشی با فرمول‌نویسی آرایه‌ای

تابع LAMBDA به‌خوبی با قابلیت فرمول‌نویسی آرایه‌ای در آفیس ۳۶۵ کار می‌کند. فرض می‌کنیم می‌خواهیم تابعی سفارشی بسازیم که فهرستی از داده‌ها را بر اساس تعداد تکرار هر یک مرتب کند. برای این‌کار از تابع SORTBY، تابع COUNTIF و تابع UNIQUE استفاده می‌کنیم. اگر داده‌های مورد نظر ما در محدوده C2:C85 باشند، فرمول اصلی را به شکل زیر می‌نویسیم.
=SORTBY(UNIQUE(C2:C85), COUNTIF(C2:C85, UNIQUE(C2:C85)), -1)

عملکرد این فرمول به شرح زیر است.

  • ابتدا تابع UNIQUE تمام مقادیر غیرتکراری از فهرست را استخراج می‌کند. آرایه خروجی، آرگومان اول تابع SORTBY است.
  • سپس در بخش COUNTIF(C2:C85, UNIQUE(C2:C85) تعداد دفعات تکرار هر داده از فهرست محاسبه می‌شوند. این تعداد آرگومان دوم تابع SORTBY است.
  • آرگومان سوم تابع SORTBY را نیز عدد «۱-» قرار می‌دهیم تا نتایج به‌صورت نزولی یعنی از بیشترین به کمترین مقدار مرتب شوند.

برای درک بهتر فرمول جدولی فرضی را در نظر می‌گیریم که فهرستی از قهرمانان تنیس برنده گرند اسلم در آن قرار دارد.

فهرست تنیس‌بازان-تابع سفارشی

حال می‌خواهیم این فهرست را بر اساس تعداد قهرمانی‌ هر فرد مرتب کنیم. طبق آنچه گفتیم، اگر نام محدوده این فهرست را به‌عنوان پارامتر تابع LAMBDA عبارت list  بگذاریم، تابع نهایی به شکل زیر درمی‌آید.

=LAMBDA(list, SORTBY(UNIQUE(list), COUNTIF(list, UNIQUE(list)), -1))

اگر این تابع سفارشی را «SortByCount» نام‌گذاری کنیم، با فراخوانی فرمول ساده =SortByCount(C2:C85) فهرست مرتبی از افراد بر اساس تعداد قهرمانی آن‌ها داریم. مانند هر فرمول‌نویسی آرایه‌ای، کافی است در سلول E2 فرمول را وارد کنیم. بعد از این‌کار هر دو ستون به‌صورت خودکار طبق دستور تابع سفارشی پر می‌شوند.

نتیجه نهایی سورت کردن فهرست-تابع LAMBDA در اکسل
در مطلب مرتبطی از مجله فرادرس، نکات بیشتری در مورد فرمول‌نویسی آرایه‌ای را توضیح داده‌ایم. حال اگر بخواهیم تعداد محدودی از افراد، به‌عنوان مثال سه نفر اول را فقط فهرست کنیم، از تابع INDEX همراه با ثابت آرایه‌ای {1,2,3} به شکل زیر استفاده می‌کنیم. بنابراین فرمول به شکل زیر در می‌آید.
=LAMBDA(list, INDEX(SORTBY(UNIQUE(list), COUNTIF(list, UNIQUE(list)), -1), {1;2;3}))
و تابع سفارشی نهایی را به شکل =TopThree(C2:C85) تعریف می‌کنیم. در مطلب زیر مثال‌های بیشتری از عملکرد تابع INDEX را توضیح داده‌ایم.

مثال ۵. ساخت تابع سفارشی با آرگومان‌های پیش‌فرض

در تابع LAMBDA آرگومان‌های مربوط به پارامتر اختیاری هستند. اما برای کاهش تعداد ورودی‌های اجباری و صرفه‌جویی در زمان می‌توانیم مقداری پیش‌فرض برای آرگومان‌های اختیاری تعریف کنیم. برای این کار نام آرگومان اختیاری را در کروشه قرار می‌دهیم و سپس با تابع ISOMITTED در بخش آرگومان محاسباتی تابع LAMBDA مشخص می‌کنیم که آیا کاربر برای آن آرگومان مقدار پیش‌فرضی وارد کرده است یا خیر. یعنی در ابتدای فرمول، کاربرد آن آرگومان خاص را توضیح می‌دهیم، سپس مقداری پیش‌فرض برای آن تعیین می‌کنیم تا کاربر مجبور به وارد کردن آن در هر بار فرمول‌نویسی نباشد.

به‌عنوان مثال در یک جدول اکسل تابع سفارشی «SalesPrice» را برای محاسبه قیمت کالا بعد از اعمال تخفیف نوشته‌ایم. در این تابع آرگومان discount اختیاری است و مقدار پیش‌فرض آن را تخفیف ۱۰ درصد درنظر گرفته‌ایم.

ساختار تابع LAMBDA به شکل زیر است.

=LAMBDA(price,[discount],
  IF(ISOMITTED(discount),price*0.9,price*(1-discount)))

اگر آرگومان discount را وارد نکنیم، تابع ISOMITTED مقدار TRUE را برمی‌گرداند و مقدار هر قیمت مشخص از جدول بعد از اعمال ۱۰ درصد تخفیف، نمایش داده می‌شود. اما اگر برای آرگومان discount مقداری مشخص وارد کنیم، تابع از همان مقدار برای محاسبه قیمت نهایی استفاده می‌شود.

=SalePrice(C5) // default 10% discount
=SalePrice(C5,0.25) // 25% discount

در حالتی‌که مقدار پیش‌فرض تخفیف را وارد می‌کنیم، فرمول اول در یک جدول فرضی به شکل زیر درمی آید.

نمونه تابع سفارشی تخفیف پیش فرض-تابع lambda در اکسل

اما در حالتی که پیش‌فرض نداریم، مقدار تخفیف را مانند فرمول دوم می‌نویسیم. نتیجه برای یک جدول فرضی به شکل زیر خواهد بود.

نمونه تابع بدون پیش‌فرض-تابع سفارشی

مثال ۶. ساخت تابع سفارشی شمارش کلمات با تابع LAMBDA

اکسل، تابع خاصی برای شمارش تعداد کلمات ندارد. اما با نوشتن فرمول همراه تابع TRIM، تابع LEN و تابع SUBSTITUTE می‌توانیم این کار را انجام دهیم.
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

این فرمول فقط یک ورودی یعنی عبارت متنی را نیاز دارد. بنابراین با استفاده از تابع LAMBDA پارامتر text را به‌عنوان ورودی تعریف می‌کنیم.

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

اما این فرمول وقتی سلول جدول خالی است، نتیجه شمارش را عدد «۱» نشان می‌دهد. این خروجی به‌دلیل اضافه شدن «۱» در انتهای فرمول است. برای رفع مشکل، عبارت «۱+» را با یک متن شرطی جایگزین می‌کنیم. به شکلی که فقط زمانی عدد «۱» به انتهای آن اضافه می‌شود که سلول حاوی متن باشد. بنابراین فرمول نهایی به شکل زیر درمی‌آید.

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

بعد از آزمایش عملکرد درست این فرمول، در بخش «Name Manager» تابع سفارشی «Countwords» را تعریف می‌کنیم. به این ترتیب برای جدول فرضی زیر تعداد کلمات به‌راحتی با یک فرمول ساده و کوتاه محاسبه می‌شوند.

تابع شمارش کلمات-تابع سفارشی

نتیجه نهایی به شکل زیر درمی‌آید.

شمارش تعداد کلمات-تابع lambda در اکسل

مزیت‌ها و محدودیت‌های تابع LAMBDA در اکسل

تابع LAMBDA در کنار مزایایی مانند ساده‌سازی فرمول‌ها و سریع‌تر کردن انجام محاسبات، به‌خصوص به‌دلیل پشتیبانی نشدن در همه نسخه‌های اکسل، محدودیت‌هایی نیز دارد. در این بخش هر یک از این موارد را بیشتر توضیح می‌دهیم.

مزیت‌های تابع LAMBDA در اکسل

تابع LAMBDA در اکسل سه مزیت مهم دارد که استفاده از آن را برای کاربران جذاب می‌کند.

۱. فرمول‌ها را کوتاه‌تر و خواناتر می‌کند.

با استفاده از تابع LAMBDA می‌توانیم به‌جای فرمول‌های طولانی و پیچیده، توابعی ساده با نام دلخواه بسازیم. این کار با بهینه کردن ساختار فرمول، درک عملکرد آن را آسان می‌کند.

۲. ویرایش فرمول‌ها را ساده‌تر و سریع‌تر می‌کند.

با ساخت توابع سفارشی ویرایش فرمولی که چند بار در بخش‌های استفاده کرده‌ایم، بسیار ساده می‌شود. در این حالت برای هر تغییر نیازی به ویرایش تک‌تک سلول‌های دارای فرمول نداریم. بلکه فقط کافی است از طریق بخش «Name Manager» یک‌بار تغییر را انجام دهیم. به این صورت همه محل‌هایی که از آن فرمول استفاده کرده‌ایم، تغییر می‌کند.

۳. بدون نیاز به برنامه‌نویسی، محاسبات پیچیده قابل انجام هستند.

با تابع LAMBDA می‌توانیم بسیاری از کارهایی که فقط با برنامه‌نویسی ویژوال بیسیک در اکسل امکان‌پذیر بود، با روشی ساده‌تر انجام دهیم. بنابراین در محاسبات پیچیده نیازی به ذخیره کردن فایل اکسل با فرمت ماکرو یا فعال‌سازی قابلیت ماکرو در اکسل نداریم.

محدودیت‌های کار با تابع LAMBDA در اکسل

موارد زیر مهم‌ترین چالش‌های کار با تابع LAMBDA هستند.

۱. سازگار نبودن با همه نسخه‌های اکسل

تابع LAMBDA فقط در نسخه مایکروسافت ۳۶۵ قابل استفاده است. بنابراین اگر فایل حاوی این تابع را در نسخه‌های قدیمی اکسل باز کنیم، محاسبات به‌درستی انجام نمی‌شوند.

۲. محدود بودن تابع به یک فایل اکسل

فرمول‌های ساخته شده با تابع LAMBDA مختص همان فایلی هستند که در آن تعریف شده‌اند. به این معنا که نمی‌توانیم از آن‌ها در فایل های دیگر اکسل استفاده کنیم. این موضوع زمانی مشکل‌ساز است که در دو فایل مختلف اکسل، توابعی با نام یکسان اما ساختار متفاوت بسازیم. در این صورت ممکن است با وارد کردن داده‌های یکسان نتایج متفاوتی به‌دست آوریم. حتی با کپی کردن فرمول و سپس پاک کردن کاربرگ حاوی تابع سفارشی هم این موضوع حل نمی‌شود. زیرا با حذف کاربرگ، تابع LAMBDA هم‌چنان در بخش «Name Manager» باقی می‌ماند.

۳. نداشتن راهنمای آرگومان

در بیشتر موارد، بر خلاف سایر توابع داخلی اکسل، توابع سفارشی LAMBDA راهنمایی برای نمایش آرگومان‌ها و ترتیب آن‌ها ندارد. بنابراین لازم است هر بار ترتیب و تعداد آرگومان در توابع سفارشی را به‌خاطر بسپاریم. البته برای رفع این مشکل می‌توانیم در بخش «Comments» از پنجره «Name Manager» توضیح کوتاهی درباره هر پارامتر بنویسیم. به این شکل هنگام نوشتن نام تابع در یک سلول، این راهنما نمایش داده می‌شود و تشخیص پارامترها ساده‌تر خواهد بود.

رفع پیام‌های خطا هنگام کار با تابع LAMBDA در اکسل

اگر بعد از تعریف تابع LAMBDA با پیام خطا روبرو شدیم، راه‌های زیر به شناسایی دلیل مشکل و رفع آن کمک می‌کند.

رفع خطای !NAME#

خطای #NAME! به یکی از دلایل زیر رخ می‌دهد.

  • نسخه اکسل از تابع LAMBDA پشتیبانی نمی‌کند. بنابراین لازم است اطمینان پیدا کنیم که نسخه نصب شده روی سیستم مایکروسافت ۳۶۵ است.
  • اگر نام تابع سفارشی را در فرمول اشتباه وارد کنیم، با این پیام روبرو می‌شویم. در زمان استفاده از تابع LAMBDA، نامی که در نوار فرمول اکسل وارد می‌کنیم، باید با نامی که در «Name Manager» تعریف می‌کنیم، یکسان باشد.

رفع خطای !VALUE#

خطای #VALUE! به دلایل زیر رخ می‌دهد.

  • نام پارامترهایی که در آرگومان Calculation استفاده کرده‌ایم، با نام پارامترهای تعریف شده در ابتدای تابع مطابقت ندارد.
  • هنگام وارد کردن تابع در یک سلول، تعداد آرگومان‌ها را به اشتباه وارد کرده‌ایم. برای رفع مشکل لازم است ساختار تابع LAMBDA را در بخش «Name Manager» از نظر صحیح بودن تعداد و ترتیب آرگومان‌ها بررسی کنیم.
  • تعداد پارامترهای تعریف شده بیش از ۲۵۳ مورد است. البته این حالت معمولا اتفاق نمی‌افتد.

رفع خطای !NUM#

این خطا زمانی رخ می‌دهد که تابع LAMBDA از داخل ساختار خود، بارها فراخوانی شود و راهی برای خروج از این حلقه نداشته باشد. به‌عنوان مثال ممکن است در یک فرمول، شرطی برای پایان دادن به تکرار محاسبه تعریف نکرده باشیم.

رفع خطای !CALC#

این خطا زمانی اتفاق می‌افتد که تابع LAMBDA را برای اولین بار در سلول وارد می‌کنیم و مقادیر ورودی را برای آن تعریف نکرده‌ایم. برای رفع این خطا باید مقادیر ورودی را بعد از فرمول LAMBDA داخل پرانتز تعریف کنیم. به این شکل اکسل محاسبات را با توجه به مقادیر تعریف شده، انجام می‌دهد.

رفع خطای نام‌های نامعتبر برای پارامترها

برخی مواقع خطا به انتخاب نام‌ برای پارامترها برمی‌گردد. به عنوان مثال اگر نام پارامتر شبیه نشانی سلول در جدول باشد، اکسل آن را به‌عنوان ارجاع سلول تشخیص می‌دهد. در این حالت پارامترهای غیرمعتبر به رنگ دیگری مشخص می‌شوند و پیام خطای زیر را می‌بینیم.

پیام خطای نان نامعتبر-تابع lambda در اکسل

در این مثال از نظر اکسل نام‌های num1 و num2 با آدرس سلول‌های NUM1 و NUM2 مطابقت دارند. بنابراین آن‌ها را به‌عنوان نام معتبر برای پارامتر نمی‌پذیرد. برای رفع مشکل نام پارامتر را به عبارت‌های دیگر مانند num_1 و num_2 تغییر می‌دهیم.

رفع خطای پیام نامعتبر-تابع سفارشی

جمع‌بندی پایانی

تابع LAMBDA برای ایجاد توابع سفارشی و انجام محاسبات دلخواه به‌شکل ساده استفاده می‌شود. مزیت استفاده از این تابع کوتاه کردن و ساده‌سازی فرمول‌ها و ویرایش سریع آن‌ها است. با این حال تابع LAMBDA فقط در نسخه ۳۶۵ اکسل پشتیبانی می‌شود. در این مطلب از مجله فرادرس کاربردهای مختلف تابع LAMBDA در اکسل را همراه مثال‌ توضیح دادیم. از آنجا که بخش اصلی فرمول‌نویسی در این تابع نیاز به مهارت در کار با سایر توابع اکسل دارد، مشاهده فیلم آموزش استفاده از توابع و فرمول اکسل در فرادرس تکمیل‌کننده مسیر یادگیری است.

مطالب مجله فرادرس را در جستجوی گوگل سریع‌تر ببینید

بر اساس رای ۰ نفر
آیا این مطلب برای شما مفید بود؟
اگر پرسشی درباره این مطلب دارید، آن را با ما مطرح کنید.
منابع:
Ablebits.comEXCELJET
PDF
مطالب مرتبط
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *