آموزش تابع LAMBDA در اکسل | آکادمی پویا حیاتی

معرفی تابع LAMBDA

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

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

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

=LAMBDA(x, x*2)(10)

در این مثال، مقدار ۱۰ به جای x قرار می‌گیرد و نتیجه برابر با ۲۰ می‌شود.

نکته مهم این است که LAMBDA معمولاً زمانی بیشترین ارزش را دارد که آن را از طریق Name Manager اکسل نام‌گذاری کنید. مثلا تابعی با نام DoubleNumber بسازید و بعد در شیت مانند یک تابع عادی از آن استفاده کنید.

کاربردهای اصلی تابع LAMBDA

ساخت توابع سفارشی بدون نیاز به VBA
کوتاه کردن فرمول‌های طولانی و پیچیده
استفاده مجدد از یک منطق محاسباتی در چندین شیت یا سلول
افزایش خوانایی و نگهداری بهتر فایل‌های اکسل
کاهش خطاهای ناشی از کپی کردن فرمول‌های تکراری
ساخت توابع محاسباتی برای گزارش‌گیری، مالی، فروش، منابع انسانی و انبار
ترکیب با توابع جدید اکسل مثل MAP، BYROW، REDUCE و SCAN
ساخت فرمول‌های پیشرفته با منطق شرطی، جستجو، شمارش و محاسبه پویا

ساختار (Syntax)

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

=LAMBDA(parameter1, parameter2, calculation)

در حالت فارسی یا محیط‌هایی که جداکننده آرگومان‌ها نقطه‌ویرگول است، ساختار را می‌توان این‌طور در نظر گرفت:

=LAMBDA(پارامتر۱; پارامتر۲; محاسبه)

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

برای اجرای مستقیم یک LAMBDA در سلول، باید بعد از تعریف آن، مقدارهای ورودی را داخل پرانتز دوم وارد کنید:

=LAMBDA(x, x+5)(20)

نتیجه این فرمول برابر با ۲۵ است.

آرگومان‌ها

parameter1 / پارامتر اول

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

parameter2 / پارامتر دوم

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

پارامترهای بعدی

تابع LAMBDA می‌تواند چندین پارامتر داشته باشد. در اکسل تعداد پارامترهای قابل استفاده محدود است و نباید بیش از حد فرمول را شلوغ کنید. بهتر است فقط پارامترهایی را تعریف کنید که واقعاً در محاسبه نیاز دارید.

calculation / محاسبه نهایی

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

مثال‌های ساده و پایه

مثال ۱: دو برابر کردن یک عدد

در این مثال عدد داخل سلول A2 را دو برابر می‌کنیم:

=LAMBDA(x, x*2)(A2)

اگر مقدار سلول A2 برابر با ۱۵ باشد، نتیجه فرمول برابر با ۳۰ خواهد شد.

مثال ۲: محاسبه قیمت با مالیات

فرض کنید قیمت کالا در سلول A2 و نرخ مالیات در سلول B2 قرار دارد. می‌خواهیم قیمت نهایی را محاسبه کنیم:

=LAMBDA(price, tax, price*(1+tax))(A2, B2)

اگر قیمت برابر با ۱۰۰۰۰۰ و نرخ مالیات برابر با ۰٫۰۹ باشد، نتیجه برابر با ۱۰۹۰۰۰ خواهد بود.

مثال ۳: ساخت تابع برای محاسبه میانگین فروش روزانه

اگر کل فروش در A2 و تعداد روزها در B2 باشد، می‌توانیم میانگین فروش روزانه را با LAMBDA محاسبه کنیم:

=LAMBDA(totalSales, days, totalSales/days)(A2, B2)

اگر فروش کل ۳۰,۰۰۰,۰۰۰ و تعداد روزها ۳۰ باشد، نتیجه برابر با ۱,۰۰۰,۰۰۰ خواهد بود.

مثال ۴: استفاده از LAMBDA با Name Manager

برای استفاده حرفه‌ای از LAMBDA بهتر است آن را نام‌گذاری کنید. برای مثال، در Name Manager یک نام با عنوان AddTax بسازید و در قسمت Refers to فرمول زیر را قرار دهید:

=LAMBDA(price, tax, price*(1+tax))

بعد از ذخیره نام، می‌توانید در سلول‌ها از تابع جدید خودتان استفاده کنید:

=AddTax(A2, B2)

مثال‌های کاربردی و واقعی

مثال ۱: ترکیب LAMBDA با SUM برای جمع یک محدوده

فرض کنید می‌خواهید یک تابع سفارشی بسازید که جمع یک محدوده را محاسبه کند:

=LAMBDA(rng, SUM(rng))(A2:A10)

این فرمول مجموع مقادیر محدوده A2 تا A10 را محاسبه می‌کند.

مثال ۲: بررسی معتبر بودن نمره با AND

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

=LAMBDA(score, AND(score>=0, score<=100))(B2)

اگر نمره داخل B2 بین ۰ تا ۱۰۰ باشد، نتیجه TRUE می‌شود؛ در غیر این صورت FALSE نمایش داده می‌شود.

مثال ۳: بررسی وضعیت با OR

فرض کنید وضعیت پرداخت در سلول C2 قرار دارد و می‌خواهید بررسی کنید آیا پرداخت انجام شده یا تسویه شده است:

=LAMBDA(status, OR(status="پرداخت شد", status="تسویه شد"))(C2)

اگر مقدار C2 یکی از دو متن مشخص‌شده باشد، نتیجه TRUE خواهد بود.

مثال ۴: شمارش تعداد تکرار یک شهر با COUNTIF

در این مثال تعداد دفعات تکرار شهر تهران را در یک محدوده محاسبه می‌کنیم:

=LAMBDA(rng, city, COUNTIF(rng, city))(A2:A100, "تهران")

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

مثال ۵: جستجوی نام کالا با XLOOKUP

فرض کنید کد کالا در سلول E2 قرار دارد، کدها در ستون A و نام کالاها در ستون B هستند. می‌توانیم با LAMBDA و XLOOKUP نام کالا را پیدا کنیم:

=LAMBDA(code, XLOOKUP(code, A2:A100, B2:B100, "یافت نشد"))(E2)

اگر کد موردنظر پیدا شود، نام کالا نمایش داده می‌شود؛ در غیر این صورت متن یافت نشد برگردانده می‌شود.

مثال ۶: محاسبه پاداش فروش با منطق شرطی

فرض کنید فروش هر کارمند در A2 است. اگر فروش بیشتر از ۵۰,۰۰۰,۰۰۰ باشد، پاداش ۱۰٪ و در غیر این صورت پاداش ۵٪ محاسبه شود:

=LAMBDA(sales, IF(sales>50000000, sales*10%, sales*5%))(A2)

این مثال نشان می‌دهد که LAMBDA می‌تواند منطق شرطی را هم در خود جای دهد.

ترکیب تابع LAMBDA با فرمول‌های دیگر

ترکیب با SUM برای ساخت تابع جمع سفارشی:

=LAMBDA(rng, SUM(rng))(A2:A20)

ترکیب با AVERAGE برای محاسبه میانگین سفارشی:

=LAMBDA(rng, AVERAGE(rng))(B2:B30)

ترکیب با IF برای ساخت شرط‌های قابل استفاده مجدد:

=LAMBDA(amount, IF(amount>10000000, "فروش بالا", "فروش عادی"))(A2)

ترکیب با AND برای اعتبارسنجی چند شرط همزمان:

=LAMBDA(age, score, AND(age>=18, score>=70))(A2, B2)

ترکیب با OR برای بررسی چند حالت قابل قبول:

=LAMBDA(status, OR(status="فعال", status="در انتظار"))(C2)

ترکیب با COUNTIF برای شمارش شرطی:

=LAMBDA(rng, criteria, COUNTIF(rng, criteria))(A2:A100, "قبول")

ترکیب با XLOOKUP برای ساخت تابع جستجوی اختصاصی:

=LAMBDA(id, XLOOKUP(id, A2:A100, C2:C100, "پیدا نشد"))(E2)

ترکیب با MAP برای اعمال یک محاسبه روی تک‌تک آیتم‌های یک محدوده:

=MAP(A2:A10, LAMBDA(x, x*1.09))

ترکیب با BYROW برای انجام محاسبه روی هر ردیف از یک جدول:

=BYROW(B2:D10, LAMBDA(row, SUM(row)))

ترکیب با LET برای خواناتر کردن فرمول‌های طولانی:

=LAMBDA(price, tax, LET(finalPrice, price*(1+tax), finalPrice))(A2, B2)

خطاهای رایج و روش رفع آنها

۱) خطای #CALC! به دلیل اجرا نکردن LAMBDA

اگر فقط تابع LAMBDA را تعریف کنید اما آن را اجرا نکنید، اکسل ممکن است خطای #CALC! نمایش دهد.

=LAMBDA(x, x+1)

روش رفع: یا باید بعد از تعریف LAMBDA مقدار ورودی را وارد کنید، یا آن را در Name Manager نام‌گذاری کنید.

=LAMBDA(x, x+1)(5)

۲) خطای #VALUE! به دلیل تعداد ورودی‌های اشتباه

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

=LAMBDA(x, y, x+y)(10)

در این مثال دو پارامتر x و y تعریف شده، اما فقط یک مقدار ورودی داده شده است. روش درست:

=LAMBDA(x, y, x+y)(10, 20)

۳) خطای #NAME? به دلیل نام اشتباه تابع یا پارامتر

اگر تابع سفارشی را در Name Manager تعریف نکرده باشید، نام آن را اشتباه تایپ کنید، یا از نام پارامتری نامعتبر استفاده کنید، اکسل خطای #NAME? نشان می‌دهد.

روش رفع: نام تابع را در Name Manager بررسی کنید، از فاصله در نام تابع استفاده نکنید، و نام پارامترها را ساده و خوانا انتخاب کنید.

۴) خطای #NUM! در LAMBDA بازگشتی

اگر از LAMBDA برای ساخت فرمول بازگشتی استفاده کنید و شرط توقف درست تعریف نشده باشد، اکسل ممکن است خطای #NUM! نمایش دهد.

روش رفع: همیشه برای فرمول‌های بازگشتی یک شرط پایان واضح قرار دهید تا تابع بی‌نهایت اجرا نشود.

۵) خطای #SPILL! هنگام خروجی آرایه‌ای

اگر LAMBDA خروجی چندسلولی تولید کند اما سلول‌های اطراف خالی نباشند، خطای #SPILL! نمایش داده می‌شود.

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

۶) خطاهای داخلی توابع ترکیبی

گاهی خطا از خود LAMBDA نیست، بلکه از تابعی است که داخل آن استفاده کرده‌اید؛ مثل XLOOKUP، COUNTIF یا IF.

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

=LAMBDA(code, XLOOKUP(code, A2:A100, B2:B100, "یافت نشد"))(E2)

نکات حرفه‌ای و ترفندهای مهم

برای نام پارامترها از نام‌های واضح استفاده کنید؛ مثلا price بهتر از p است.
اگر فرمول طولانی است، ابتدا آن را به صورت معمولی در سلول تست کنید و بعد داخل LAMBDA قرار دهید.
برای استفاده مجدد، LAMBDA را در Name Manager ذخیره کنید.
در نام تابع‌های سفارشی از فاصله استفاده نکنید؛ به جای آن از خط زیرین یا نام انگلیسی خوانا استفاده کنید.
برای فرمول‌های پیچیده، LAMBDA را با LET ترکیب کنید تا بخش‌های مختلف فرمول نام‌گذاری شوند.
قبل از استفاده گسترده، تابع سفارشی را با چند ورودی مختلف تست کنید.
اگر در اکسل شما جداکننده آرگومان‌ها نقطه‌ویرگول است، فرمول‌های انگلیسی با کاما را متناسب با تنظیمات سیستم خود تغییر دهید.
برای کار با آرایه‌ها، LAMBDA را همراه با MAP، BYROW، BYCOL، REDUCE و SCAN یاد بگیرید.
اگر قرار است فایل را برای دیگران ارسال کنید، مطمئن شوید نسخه اکسل آن‌ها از LAMBDA پشتیبانی می‌کند.
برای جلوگیری از خطاهای پنهان، در توابع جستجو مثل XLOOKUP مقدار جایگزین برای حالت پیدا نشدن تعیین کنید.

تفاوت تابع LAMBDA با توابع مشابه

تفاوت LAMBDA با LET

تابع LET برای نام‌گذاری متغیرها داخل یک فرمول استفاده می‌شود، اما LAMBDA برای ساخت تابع سفارشی کاربرد دارد. LET فرمول را خواناتر می‌کند، ولی LAMBDA امکان استفاده مجدد از فرمول را فراهم می‌کند.

=LET(x, A2*10%, A2+x)

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

=LAMBDA(amount, amount+amount*10%)(A2)

تفاوت LAMBDA با VBA

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

تفاوت LAMBDA با فرمول‌های معمولی

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

تفاوت LAMBDA با Named Range

Named Range فقط به یک سلول، محدوده یا فرمول نام می‌دهد، اما LAMBDA می‌تواند ورودی بگیرد، محاسبه انجام دهد و نتیجه برگرداند. بنابراین LAMBDA بسیار پویا‌تر و انعطاف‌پذیرتر است.

تفاوت LAMBDA با توابع آماده اکسل

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

سازگاری با نسخه‌های مختلف اکسل

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

Excel for Microsoft 365: پشتیبانی کامل و بهترین گزینه برای استفاده از LAMBDA
Excel for the web: پشتیبانی می‌شود، مخصوصاً برای فایل‌های ذخیره‌شده در OneDrive یا SharePoint
Excel 2021 و Excel 2024: در بسیاری از نصب‌ها پشتیبانی می‌شود، اما امکانات توابع جدید همراه آن ممکن است به‌روزرسانی نیاز داشته باشد
Excel 2019 و نسخه‌های قدیمی‌تر: معمولاً از LAMBDA پشتیبانی نمی‌کنند
Excel Mac: در نسخه‌های جدید Microsoft 365 برای مک پشتیبانی می‌شود

اگر فایل شما قرار است بین چند کاربر جابه‌جا شود، قبل از استفاده گسترده از LAMBDA حتماً نسخه اکسل کاربران را بررسی کنید. همچنین توابعی مثل MAP، BYROW، REDUCE و SCAN ممکن است فقط در نسخه‌های جدیدتر و به‌روزشده در دسترس باشند.

سؤالات پرتکرار درباره تابع LAMBDA

آیا برای استفاده از LAMBDA باید برنامه‌نویسی بلد باشیم؟

خیر. LAMBDA با فرمول‌نویسی اکسل کار می‌کند و نیازی به VBA یا زبان برنامه‌نویسی ندارد.

آیا می‌توانم LAMBDA را مثل یک تابع معمولی اکسل استفاده کنم؟

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

چرا وقتی فقط LAMBDA را می‌نویسم خطا می‌دهد؟

چون LAMBDA باید یا مستقیماً اجرا شود یا به عنوان یک تابع نام‌گذاری شود. اگر فقط تعریف شود و ورودی نگیرد، نتیجه‌ای برای نمایش ندارد.

آیا LAMBDA جایگزین VBA است؟

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

آیا می‌توان داخل LAMBDA از IF و XLOOKUP استفاده کرد؟

بله. تقریباً می‌توانید از بیشتر توابع اکسل داخل LAMBDA استفاده کنید، از جمله IF، SUM، COUNTIF، AND، OR و XLOOKUP.

آیا LAMBDA می‌تواند چند خروجی بدهد؟

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

بهترین روش یادگیری LAMBDA چیست؟

ابتدا با مثال‌های ساده مثل محاسبه مالیات، تخفیف و جمع شروع کنید. سپس LAMBDA را با LET، IF، XLOOKUP، MAP و BYROW ترکیب کنید.

جمع‌بندی و پیشنهاد یادگیری بعدی

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

اگر مبتدی هستید، ابتدا LAMBDA را با مثال‌های ساده مثل دو برابر کردن عدد، محاسبه مالیات و تخفیف تمرین کنید. اگر در سطح متوسط هستید، ترکیب LAMBDA با LET، IF، XLOOKUP، MAP و BYROW می‌تواند مهارت فرمول‌نویسی شما را چند سطح بالاتر ببرد.

پیشنهاد یادگیری بعدی: بعد از LAMBDA، حتماً تابع LET را یاد بگیرید؛ چون ترکیب LET و LAMBDA یکی از بهترین روش‌ها برای نوشتن فرمول‌های تمیز، حرفه‌ای و قابل نگهداری در اکسل است.