آموزش تابع LET در اکسل

معرفی تابع LET

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

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

مثلاً فرض کنید قیمت کالا در سلول A2 و درصد تخفیف در سلول B2 قرار دارد. می‌خواهیم قیمت نهایی بعد از تخفیف را محاسبه کنیم. با LET می‌توانیم نام‌های ساده‌ای مثل price و discount بسازیم و سپس از آن‌ها استفاده کنیم:

=LET(price, A2, discount, B2, price*(1-discount))

در این مثال، اکسل مقدار A2 را با نام price و مقدار B2 را با نام discount می‌شناسد. سپس محاسبه نهایی را با استفاده از این نام‌ها انجام می‌دهد.

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

• کوتاه‌تر و خواناتر کردن فرمول‌های طولانی و پیچیده
• جلوگیری از تکرار چندباره یک محاسبه در یک فرمول
• افزایش سرعت محاسبات، مخصوصاً در فایل‌های بزرگ
• ساده‌تر کردن عیب‌یابی فرمول‌ها
• تعریف متغیرهای موقت داخل یک فرمول
• ترکیب بهتر با توابع جدید اکسل مانند FILTER، XLOOKUP، SORT، UNIQUE و LAMBDA
• ساخت فرمول‌های حرفه‌ای‌تر برای گزارش‌گیری، داشبورد و تحلیل داده
• کمک به مستندسازی فرمول؛ چون نام‌های انتخاب‌شده معنی محاسبات را بهتر نشان می‌دهند

ساختار (Syntax)

ساختار کلی تابع LET در اکسل به این صورت است:

=LET(name1, value1, calculation_or_name2, [value2], ...)

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

=LET(نام_اول; مقدار_اول; محاسبه_نهایی)

اگر بخواهید چند نام یا متغیر تعریف کنید، ساختار به این شکل می‌شود:

=LET(نام_اول; مقدار_اول; نام_دوم; مقدار_دوم; محاسبه_نهایی)

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

آرگومان‌ها

name1 / نام متغیر اول

این آرگومان نامی است که برای مقدار یا محاسبه میانی انتخاب می‌کنید. این نام باید معتبر باشد و بهتر است معنی‌دار انتخاب شود. مثلاً برای قیمت از price، برای تعداد از qty و برای تخفیف از discount استفاده کنید.

نمونه:

=LET(price, A2, price*10%)

value1 / مقدار متغیر اول

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

نمونه:

=LET(total, SUM(A2:A10), total/10)

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

آخرین آرگومان تابع LET نتیجه نهایی فرمول را مشخص می‌کند. در این بخش می‌توانید از نام‌هایی که قبلاً تعریف کرده‌اید استفاده کنید.

نمونه:

=LET(score, B2, IF(score>=60, "قبول", "رد"))

name2, value2 و آرگومان‌های بعدی

تابع LET می‌تواند چندین نام و مقدار را در یک فرمول تعریف کند. این قابلیت برای فرمول‌های تحلیلی و چندمرحله‌ای بسیار مفید است.

نمونه:

=LET(price, A2, qty, B2, discount, C2, price*qty*(1-discount))

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

مثال ۱: محاسبه مبلغ کل فروش

فرض کنید قیمت واحد کالا در سلول A2 و تعداد فروش در سلول B2 قرار دارد. برای محاسبه مبلغ کل می‌توانیم از LET استفاده کنیم:

=LET(price, A2, qty, B2, price*qty)

در این فرمول، price برابر مقدار A2 و qty برابر مقدار B2 است. نتیجه نهایی ضرب قیمت در تعداد خواهد بود.

مثال ۲: محاسبه قیمت بعد از تخفیف

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

=LET(price, A2, discount, B2, price-(price*discount))

اگر A2 برابر 100000 و B2 برابر 10% باشد، خروجی برابر 90000 خواهد بود.

مثال ۳: تبدیل نمره عددی به وضعیت قبولی

فرض کنید نمره دانشجو در سلول B2 قرار دارد. اگر نمره 60 یا بیشتر باشد، عبارت قبول و در غیر این صورت رد نمایش داده شود:

=LET(score, B2, IF(score>=60, "قبول", "رد"))

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

مثال ۴: محاسبه میانگین و مقایسه با حد نصاب

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

=LET(avgScore, AVERAGE(B2:D2), IF(avgScore>=70, "مطلوب", "نیاز به تلاش"))

در این مثال، ابتدا میانگین در متغیر avgScore ذخیره می‌شود و سپس همان مقدار در تابع IF استفاده می‌شود.

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

مثال ۱: ترکیب LET با SUM برای محاسبه درصد فروش یک فروشنده

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

=LET(totalSales, SUM(B2:B10), sellerSales, B2, sellerSales/totalSales)

در این فرمول، totalSales کل فروش و sellerSales فروش فروشنده انتخابی است. نتیجه، سهم فروشنده از کل فروش را نشان می‌دهد.

مثال ۲: ترکیب LET با AND برای بررسی شرایط استخدام

فرض کنید سن متقاضی در A2 و سابقه کاری او در B2 قرار دارد. اگر سن حداقل 25 و سابقه کاری حداقل 3 سال باشد، شخص واجد شرایط است:

=LET(age, A2, exp, B2, IF(AND(age>=25, exp>=3), "واجد شرایط", "غیر واجد شرایط"))

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

مثال ۳: ترکیب LET با OR برای بررسی وضعیت هشدار

فرض کنید موجودی کالا در A2 و حداقل موجودی مجاز در B2 قرار دارد. همچنین وضعیت اضطراری در C2 با مقدار بله یا خیر ثبت شده است. اگر موجودی کمتر از حد مجاز باشد یا وضعیت اضطراری بله باشد، هشدار نمایش داده شود:

=LET(stock, A2, minStock, B2, emergency, C2, IF(OR(stock<minStock, emergency="بله"), "هشدار", "عادی"))

این فرمول برای کنترل موجودی انبار بسیار کاربردی است.

مثال ۴: ترکیب LET با COUNTIF برای شمارش وضعیت‌ها

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

=LET(statusRange, C2:C100, completed, COUNTIF(statusRange, "تکمیل شده"), completed)

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

مثال ۵: ترکیب LET با XLOOKUP برای جستجوی حرفه‌ای

فرض کنید کد محصول در سلول F2 وارد می‌شود، کدهای محصولات در محدوده A2:A100 و نام محصولات در محدوده B2:B100 قرار دارد. فرمول زیر نام محصول را پیدا می‌کند:

=LET(code, F2, codes, A2:A100, names, B2:B100, XLOOKUP(code, codes, names, "یافت نشد"))

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

مثال ۶: محاسبه مالیات، تخفیف و مبلغ نهایی فروش

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

=LET(price, A2, qty, B2, discount, C2, tax, D2, subtotal, price*qty, afterDiscount, subtotal*(1-discount), afterDiscount*(1+tax))

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

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

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

• ترکیب LET با IF برای ساده‌سازی شرط‌ها
• ترکیب LET با SUM برای جلوگیری از تکرار محاسبه مجموع
• ترکیب LET با AVERAGE برای ذخیره میانگین و استفاده در شرط‌ها
• ترکیب LET با COUNTIF و COUNTIFS برای گزارش‌گیری
• ترکیب LET با XLOOKUP برای ساخت جستجوهای خواناتر
• ترکیب LET با FILTER برای فیلتر کردن داده‌ها با متغیرهای مشخص
• ترکیب LET با TEXT برای ساخت خروجی‌های متنی حرفه‌ای
• ترکیب LET با LAMBDA برای ساخت توابع سفارشی پیشرفته

LET با IF

=LET(sales, B2, IF(sales>=1000000, "عالی", "معمولی"))

در این مثال، مقدار فروش در متغیر sales ذخیره شده و سپس در شرط IF استفاده شده است.

LET با SUM

=LET(total, SUM(B2:B20), IF(total>5000000, "هدف محقق شد", "نیاز به فروش بیشتر"))

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

LET با COUNTIF

=LET(rng, C2:C50, countDone, COUNTIF(rng, "انجام شده"), countDone)

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

LET با XLOOKUP

=LET(id, E2, ids, A2:A100, prices, C2:C100, XLOOKUP(id, ids, prices, "قیمت پیدا نشد"))

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

LET با FILTER

فرض کنید نام مشتریان در A2:A100 و شهر آن‌ها در B2:B100 قرار دارد. می‌خواهیم فقط مشتریان شهر تهران را نمایش دهیم:

=LET(names, A2:A100, cities, B2:B100, FILTER(names, cities="تهران", "موردی یافت نشد"))

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

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

۱) خطای #NAME? به دلیل نام‌گذاری اشتباه

اگر نام متغیر را اشتباه بنویسید یا در محاسبه نهایی از نامی استفاده کنید که قبلاً تعریف نشده است، اکسل ممکن است خطای #NAME? نمایش دهد.

نمونه اشتباه:

=LET(price, A2, qty, B2, price*quantity)

در این فرمول، متغیر quantity تعریف نشده است و باید به جای آن از qty استفاده شود.

روش رفع:

=LET(price, A2, qty, B2, price*qty)

۲) خطا به دلیل زوج نبودن نام‌ها و مقدارها

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

نمونه اشتباه:

=LET(price, A2, qty, price*qty)

در این فرمول، qty مقدار مشخصی ندارد.

روش رفع:

=LET(price, A2, qty, B2, price*qty)

۳) استفاده از نام‌های نامعتبر

نام متغیرهای LET باید طبق قوانین نام‌گذاری اکسل معتبر باشند. مثلاً نباید شبیه آدرس سلول باشند. استفاده از نامی مثل C یا R در حالت مرجع‌دهی R1C1 می‌تواند مشکل‌ساز شود.

نمونه نام‌گذاری بهتر:

=LET(unitPrice, A2, quantity, B2, unitPrice*quantity)

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

۴) خطای #VALUE! به دلیل نوع داده نامناسب

اگر مقدار مورد استفاده عددی نباشد اما در محاسبه ریاضی استفاده شود، ممکن است خطای #VALUE! ایجاد شود.

نمونه:

=LET(price, A2, qty, B2, price*qty)

اگر A2 یا B2 شامل متن غیرعددی باشد، فرمول خطا می‌دهد.

روش رفع این است که داده‌ها را بررسی کنید یا در صورت نیاز از VALUE برای تبدیل متن عددی به عدد استفاده کنید:

=LET(price, VALUE(A2), qty, VALUE(B2), price*qty)

۵) خطای #N/A در ترکیب با XLOOKUP

اگر مقدار جستجو در جدول پیدا نشود و پیام جایگزین تعریف نکرده باشید، XLOOKUP ممکن است خطای #N/A بدهد.

نمونه بهتر:

=LET(code, F2, codes, A2:A100, names, B2:B100, XLOOKUP(code, codes, names, "یافت نشد"))

با آرگومان پیام جایگزین در XLOOKUP می‌توانید از نمایش خطای #N/A جلوگیری کنید.

۶) استفاده بیش از حد از LET در فرمول‌های خیلی ساده

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

مثلاً برای جمع ساده دو سلول، معمولاً نیازی به LET نیست:

=A2+B2

اما اگر همان مقدارها قرار است چند بار در یک فرمول پیچیده استفاده شوند، LET مفید می‌شود.

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

• برای نام متغیرها از نام‌های معنی‌دار استفاده کنید؛ مثلاً totalSales بهتر از x است.
• از نام‌های کوتاه اما قابل فهم استفاده کنید؛ مثلاً qty برای quantity قابل قبول است.
• اگر یک محاسبه چند بار در فرمول تکرار می‌شود، آن را با LET یک‌بار تعریف کنید.
• در فرمول‌های مالی، مراحل محاسبه را با نام‌های جداگانه مشخص کنید؛ مانند subtotal، discountAmount و finalAmount.
• برای خوانایی بهتر، فرمول‌های طولانی را در نوار فرمول اکسل با Alt + Enter در چند خط بنویسید.
• از LET همراه با XLOOKUP و FILTER برای ساخت گزارش‌های پویا استفاده کنید.
• در فایل‌های بزرگ، LET می‌تواند سرعت محاسبه را بهتر کند؛ چون محاسبات تکراری فقط یک بار انجام می‌شوند.
• اگر فرمول خیلی پیچیده شد، ابتدا هر بخش را جداگانه در سلول‌های کمکی تست کنید، سپس آن را وارد LET کنید.
• از نام‌هایی استفاده نکنید که با نام سلول‌ها یا محدوده‌های تعریف‌شده تداخل داشته باشند.
• برای فرمول‌های بسیار پیشرفته، یادگیری LAMBDA در کنار LET بسیار ارزشمند است.

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

تفاوت LET با سلول‌های کمکی

در روش سنتی، برای محاسبات میانی از سلول‌های کمکی استفاده می‌شود. مثلاً ابتدا مبلغ کل در یک سلول، سپس تخفیف در سلول دیگر و در نهایت مبلغ نهایی در سلول سوم محاسبه می‌شود. اما LET همه این مراحل را داخل یک فرمول انجام می‌دهد.

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

تفاوت LET با Name Manager

Name Manager برای تعریف نام‌های سراسری یا محدوده‌ای در فایل اکسل استفاده می‌شود. اما نام‌هایی که در LET تعریف می‌شوند فقط داخل همان فرمول اعتبار دارند.

• LET: نام‌ها فقط داخل همان فرمول قابل استفاده هستند.
• Name Manager: نام‌ها می‌توانند در چندین فرمول و شیت استفاده شوند.

تفاوت LET با LAMBDA

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

نمونه ترکیب LET با LAMBDA:

=LAMBDA(x, LET(result, x*10, result))(5)

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

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

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

فرمول معمولی:

=IF(SUM(B2:B10)>1000, SUM(B2:B10)*10%, SUM(B2:B10)*5%)

فرمول بهتر با LET:

=LET(total, SUM(B2:B10), IF(total>1000, total*10%, total*5%))

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

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

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

• Microsoft 365: پشتیبانی می‌شود
• Excel 2021: پشتیبانی می‌شود
• Excel 2024: پشتیبانی می‌شود
• Excel for the web: پشتیبانی می‌شود
• Excel برای مک در نسخه‌های جدید: پشتیبانی می‌شود
• Excel 2019 و نسخه‌های قدیمی‌تر: معمولاً پشتیبانی نمی‌شود

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

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

آیا تابع LET فقط برای فرمول‌های خیلی پیچیده است؟

خیر. LET در فرمول‌های متوسط هم بسیار مفید است، مخصوصاً زمانی که یک مقدار یا محاسبه چند بار تکرار می‌شود.

آیا LET باعث افزایش سرعت فایل اکسل می‌شود؟

بله، در بسیاری از موارد می‌تواند سرعت را بهتر کند؛ چون محاسبات تکراری را فقط یک بار انجام می‌دهد. البته میزان تأثیر آن به حجم فایل و نوع فرمول‌ها بستگی دارد.

آیا می‌توان داخل LET از چند متغیر استفاده کرد؟

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

=LET(a, A2, b, B2, c, C2, a+b+c)

آیا نام‌های تعریف‌شده در LET در سلول‌های دیگر هم قابل استفاده هستند؟

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

آیا می‌توان مقدار یک متغیر LET را از یک تابع دیگر گرفت؟

بله. مقدار متغیر می‌تواند نتیجه هر تابعی باشد؛ مانند SUM، AVERAGE، COUNTIF، XLOOKUP و غیره.

=LET(total, SUM(A2:A20), total/20)

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

اگر نام‌ها درست انتخاب شوند، LET معمولاً فرمول را ساده‌تر و خواناتر می‌کند. اما برای فرمول‌های خیلی ساده، ممکن است ضرورتی نداشته باشد.

آیا LET در گوگل شیت هم وجود دارد؟

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

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

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

برای ادامه یادگیری، پیشنهاد می‌شود بعد از LET، توابع زیر را تمرین کنید:

• تابع IF برای شرط‌گذاری
• تابع XLOOKUP برای جستجوی حرفه‌ای
• تابع FILTER برای گزارش‌گیری پویا
• تابع COUNTIF و COUNTIFS برای شمارش شرطی
• تابع LAMBDA برای ساخت توابع سفارشی

اگر LET را به‌خوبی یاد بگیرید، نوشتن فرمول‌های پیشرفته در اکسل برای شما بسیار ساده‌تر، منظم‌تر و قابل اعتمادتر خواهد شد.