اکسل توابع زیادی دارد و علاوه بر توابع آماده، امکان نوشتن تابع در بخش برنامه‌نویسی اکسل به زبان VBA نیز فراهم شده است. در کنار استفاده از ماکروها و بخش برنامه‌نویسی می‌توانید از تابع LAMBDA در اکسل استفاده کنید و به کمک آن، تابع دلخواه بسازید! شاید کار با تابع LAMBDA در نگاه اول به نظر عجیب و پیچیده باشد اما با مرور کردن چند مثال ساده، متوجه می‌شوید که استفاده از آن ساده است و در عین کار کاربرد آن بسته به پروژه و هدف شما از کار با Excel، زیاد است.
در ادامه مطلب با تابع LAMBDA در نرم‌افزار Excel آشنا می‌شویم و با مثال‌های ساده، شیوه استفاده از این تابع را توضیح می‌دهیم.
تابع LAMBDA در کدام نسخه اکسل در دسترس است؟
قبل از اینکه فرمولی با استفاده از تابع LAMBDA بنویسید، دقت کنید که این تابع در Excel for Microsoft 365 برای ویندوز و مک‌او‌اس و همین‌طور Excel 2024 و نسخه تحت وب اکسل در دسترس است اما در اکسل 2019 و نسخه‌های قبلی آن چنین تابعی وجود ندارد!
در اکسل 2019 و نسخه‌های قبلی آن برای ایجاد تابع سفارشی می‌توانید از برنامه‌نویسی اکسل که به زبانی شبیه به ویژوآل بیسیک است، استفاده کنید که البته نیاز به طی کردن دوره آموزشی برنامه‌نویسی VBA دارد.
سینتکس تابع LAMBDA در اکسل
تابع LAMBDA مثل هر تابع دیگری، ورود و خروجی دارد. ورودی آن می‌تواند حداکثر 253 متغیر باشد! آرگومان اول تابع LAMBDA همان ورودی است. آرگومان دوم نیز محاسبه‌ای است که با استفاده از ورودی‌ها انجام می‌شود. بنابراین سینتکس تابع LAMBDA به صورت زیر است:
=LAMBDA(x,y)
در این فرمول ساده، x ورودی است و y محاسبات است.
با یک مثال ساده از کاربرد تابع LAMBDA شروع می‌کنیم: فرض کنید که میخواهید دو ورودی a و b به تابع LAMBDA بدهید و خروجی تابع، حاصلضرب ۲ ورودی باشد. البته این محاسبه بسیار ساده است و نیازی به تابع سفارشی ندارد. می‌توانید فرمول بنویسید و از علامت در اکسل برای محاسبات ساده مثل جمع و تفریق و ضرب و تقسیم استفاده کنید. هدف ما توضیح روش ایجاد تابع به کمک LAMBDA است.
برای نوشتن تابع کاستوم موردبحث، در سلول A1 عبارت زیر را وارد کنید:
=LAMBDA(a,b,a*b)
a و b ورودی‌ها و فرمول موردنظر، ضرب a در b است.
در پایان کلید Enter را بزنید، اکسل ارور #CALC! در سلول A1 نمایش می‌دهد و علت این است که برای متغیرهایی به اسم a و b، مقدار مشخص نکرده‌اید.
برای مشخص کردن مقدار و تست کردن عملکرد تابع LAMBDA روی سلول A1 دبل‌کلیک کنید. فرضاً اگر بخواهید ۴ و ۶ ورودی‌های تابع باشد، این دو عدد را به صورت جدا و داخل پرانتز وارد کنید. به عبارت دیگر در پایان فرمول، (4,6) را اضافه کنید.
=LAMBDA(a,b,a*b)(4,6)
با زدن کلید Enter ارور #CALC! در اکسل برطرف می‌شود و حاصلضرب دو ورودی نمایش داده می‌شود.
فرمولی که برای ضرب کردن دو عدد در اکسل با تابع LAMBDA نوشته شده، به نظر عجیب و بسیار طولانی است. اما اگر برای تابعی که به کمک LAMBDA می‌سازید، نام کوتاه انتخاب کنید، فرمول‌های طولانی کوتاه و ساده می‌شود و مزیت تابع LAMBDA روشن می‌شود.
اما روش نام‌گذاری تابع ساخته شده به کمک تابع LAMBDA در Excel:
روی سلولی که فرمول تابع LAMBDA را برای تست کردن نوشته‌اید، دبل‌کلیک کنید و بخش مربوط به فرمول را انتخاب کنید. در مثال ساده‌ای که توضیح دادیم، بخش LAMBDA(a,b,a*b) را انتخاب کنید و با کلید میانبر Ctrl + C آن را کپی کنید.
اکنون در بالای صفحه روی تب Formula کلیک کنید. سپس در گروه Defined Names روی Define Name کلیک کنید.
پنجره‌ی تعریف کردن نام ظاهر می‌شود. در این پنجره ۴ کادر موجود است:
- در کادر اول نام تابعی که با استفاده از LAMBDA می‌سازید را وارد کنید.
- در کادر دوم محدوده‌ی استفاده از تابع را مشخص کنید. اگر قرار است تابع در همه شیت‌ها قابل استفاده باشد، Workbook را انتخاب کنید و در غیر این صورت شیت موردنظر را انتخاب کنید.
- در کادر Comment توضیح و کامنت دلخواه در مورد عملکرد تابع بنویسید. این توضیح به شکل تول‌تیپ یا متن راهنما در زمان مکث کردن روی تابعی سفارشی شما نمایش داده می‌شود.
- در کادر Refers To علامت = بزنید و سپس فرمولی که با تابع LAMBDA نوشته‌اید را پیست کنید.
ما در این مثال ساده نام تابع سفارشی را SIMPLELAMBDA گذاشته‌ایم. در پایان روی OK کلیک کنید تا تابع جدید و سفارشی شما ثبت شود.
اکنون به سلول A1 برگردید، روی آن دبل‌کلیک کنید و اولین حروف تابع اختصاصی ساخته شده را وارد کنید تا در لیست توابع پیشنهادی ظاهر شود. با مکث کردن روی تابع ساخته شده، توضیحاتی که حین ثبت تابع وارد کرده بودید، نمایش داده می‌شود.
روی تابع ساخته شده با LAMBDA دبل‌کلیک کنید و داخل پرانتز متغیرها یا ورودی‌های تابع را وارد کنید. به عنوان مثال برای ضرب کردن عدد ۹ در ۶ به کمک تابع اختصاصی مثال ما که نام آن SIMPLELAMBDA است، فرمول زیر در سلول A1 ثبت می‌شود:
=SIMPLELAMBDA(9,6)
با زدن کلید Enter نتیجه نمایش داده می‌شود.
در توابعی که با استفاده از تابع LAMBDA ساخته می‌شود، امکان استفاده از سلول‌ها به عنوان ورودی نیز وجود دارد. به عنوان مثال برای ضرب کردن مقدار سلول A1 و A2 می‌توان فرمول زیر را نوشت:
=SIMPLELAMBDA(A1,A2)
مثال پیشرفته استفاده از تابع LAMBDA در اکسل
مثالی که بررسی کردیم، ضرب کردن دو عدد بود که بسیار ساده است و نیازی به ساخت تابع خاص وجود ندارد اما اگر محاسبات طولانی‌تر و پیچیده‌تر باشد، مزیت استفاده از تابع سفارشی اهمیت پیدا می‌کند. فرض کنید که قیمت اجناس مشخص است و میخواهید قیمت اجناس را ۲۰ درصد افزایش دهید و در صورت تغییر شرایط اقتصادی، عدد ۲۰ درصد را کم و بیش تغییر دهید. برای این افزایش می‌توان یک تابع به کمک LAMBDA ایجاد کرد.
در جدول زیر ستون B قیمت اجناس است:
با ضرب کردن قیمت‌ها در 1.2، افزایش ۲۰ درصدی محاسبه می‌شود. لذا فرمول ساده‌ای برای ستون C و سلول C2 در نظر می‌گیریم:
=B2*1.2
برای ساخت تابع سفارشی به کمک LAMBDA، تابعی به صورت زیر تعریف می‌کنیم که آرگومان اول قیمت است و محاسبه‌ی موردنظر، قیمت ضربدر 1.2 است:
=LAMBDA(cost,cost*1.2)
همان‌طور که پیش‌تر اشاره کردیم، اگر این فرمول را مستقیماً در سلول‌های ستون C ثبت کنید و Enter را فشار دهید، طبعاً ارور #CALC! ظاهر می‌شود.
برای رفع ارور باید ورودی تابع را مشخص کنید، به عنوان مثال برای سلول C2 فرمول موردنظر به صورت زیر است که در آن سلول B2 ورودی تابع است:
=LAMBDA(cost,cost*1.2)(B2)
می‌توانید همین فرمول را در سلول‌های بعدی کپی و پیست کنید یا با روش درگ کردن مربع کوچک گوشه پایین و راست سلول، فرمول را در سلول‌های بعدی کپی کنید. نتیجه جدول زیر است:
اما سراغ ساخت تابع به کمک LAMBDA برویم، برای این کار از تب Formulas گزینه‌ی Define Name را انتخاب کنید. فرمول را در کادر Refers to پیست کنید و نام دلخواهی برای تابع در کادر Name وارد کنید. ما در این مثال از نام AddVAT استفاده می‌کنیم. در پایان روی OK کلیک کنید.
اکنون فرمول سلول C2 به شکل ساده‌تری تبدیل می‌شود، روی این سلول دبل‌کلیک کنید. با استفاده از تابع تعریف شده و B2 به عنوان ورودی تابع، فرمول به شکل زیر ساده می‌شود:
=AddVAT(B2)
اگر در اکسل جدول ساخته باشید، می‌توانید به جای اشاره به سلول B2، به نام ستون که Cost است، نیز اشاره کنید و از فرمول زیر استفاده کنید:
=AddVAT([@Cost])
و اما تغییر دادن درصد افزایش قیمت‌ها:
فرض کنید که قرار است به جای ۲۰ درصد، ۱۵ درصد افزایش اعمال شود. برای تغییر دادن درصد و اصلاح فرمول تابع ساخته شده به کمک LAMBDA روی تب Formulas در نوار ابزار بالای صفحه کلیک کنید. سپس روی Name Manager کلیک کنید.
در پنجره‌ی باز شده لیستی از جدول‌های ساخته شده در اکسل و همین‌طور لیستی از توابع ساخته شده به کمک تابع LAMBDA نمایش داده می‌شود. روی تابع موردنظر کلیک کنید و سپس روی Edit کلیک کنید.
در کادر Refers to فرمول جدید را وارد کنید. در مثال ما به جای 1.2 از 1.15 استفاده می‌شود که به معنی افزایش ۱۵ درصدی قیمت‌ها است. با کلیک روی OK ، تابعی که به کمک LAMBDA ساخته شده، تغییر می‌کند و به راحتی تمام محاسبات قبلی، تصحیح می‌شود.
نتیجه‌ی تغییر دادن تابع ساخته شده به کمک LAMBDA را در جدول زیر مشاهده می‌کنید:
مثال دوم ما نیز نسبتاً ساده است و در واقع برای روشن شدن مزیت و کاربرد تابع LAMBDA از ۲ مثال ساده و کاربردی استفاده کردیم. می‌توانید با استفاده از تابع LAMBDA در اکسل 2024 و نسخه‌های جدیدتر، توابع سفارشی پیچیده و طولانی بسازید و به جای نوشتن فرمول‌های پیچیده در سلول‌ها، فرمول‌های کوتاه و ساده بنویسید. در صورت نیاز به تغییر فرمول و روش محاسبات نیز فقط تابعی که با استفاده از LAMBDA ساخته شده، تغییر می‌کند و نیازی به ویرایش کردن فرمول در سلول‌های مختلف وجود ندارد.
در پایان یادآوری می‌کنیم که تابع ساخته شده به کمک LAMBDA در شیت انتخاب شده یا فایل اکسل انتخاب شده عمل می‌کند. برای انتقال تابع به فایل‌های جدید، می‌توانید از فایل قبلی کپی بگیرید و آن را ویرایش کنید و همین‌طور می‌توانید فایلی که در آن تابع ساخته‌اید را به عنوان قالب و تمپلیت ذخیره کنید و برای کارهای بعدی از آن استفاده کنید.
howtogeekسیاره‌ی آی‌تی