تابع LET در اکسل – تعریف متغیر و سادهسازی فرمول + مثال
تابع LET از توابع کاربردی اکسل در نسخه ۲۰۲۱ و مایکروسافت ۳۶۵ است که با استفاده از آن میتوانیم نامهای مشخص برای متغیرهای مختلف یک فرمول طولانی تعریف کنیم. به این شکل با حذف محاسبات تکراری، درک فرمولهای پیچیده سادهتر میشود. این کار مشابه نامگذاری سلولها و محدودههای جدول است با این تفاوت که نامهای تعریف شده فقط در همان فرمول معتبر هستند. با این کار میتوانیم بهجای تکرار چندین باره یک محاسبه در فرمول، آن را در شکل یک نام تعریف کنیم و در محاسبات فقط آن نام را بهکار ببریم. برای آشنایی با فرمول تابع LET در اکسل در این مطلب از مجله فرادرس روش کار با آن را همراه مثالهای مختلف یاد میگیریم.
- با فرمول کلی تابع LET در اکسل آشنا خواهید شد.
- کاربرد تابع LET برای جلوگیری از محاسبات تکراری در فرمول را همراه چهار مثال یاد خواهید گرفت.
- کاربرد تابع LET برای درک بهتر و خواناتر کردن فرمول را همره چهار مثال یاد خواهید گرفت.
- با پیامهای خطا هنگام کار با تابع LET و قوانین نامگذاری متغیرها آشنا خواهید شد.

مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید
فرمول کلی تابع LET در اکسل
ساختار کلی فرمول تابع LET در اکسل به شکل زیر است.
=LET (name1, name_value1, [name2], [name_value2], …, calculation)در این فرمول هر یک از آرگومانها بهصورت زیر تعریف میشوند.
- name1 (اجباری): نامی که برای متغیر اول انتخاب میکنیم.
- name_value1 (اجباری): مقدار یا محاسبهای که به متغیر اول اختصاص میدهیم.
- name2 (اختیاری): نامی که برای متغیر دوم یا متغیرهای بعدی انتخاب میکنیم.
- name_value2 (اختیاری): مقدار یا محاسبهای که به متغیر دوم یا متغیرهای بعدی اختصاص میدهیم.
- calculation (اجباری): محاسبه نهایی که با استفاده از نامها و مقادیر تعریف شده انجام میدهیم.

برای نامگذاری متغیرها در فرمول لازم است به نکات زیر توجه کنیم.
- نام متغیر با یک حرف انگلیسی شروع میشود.
- نام میتواند شامل حروف، اعداد و علامت آندرلاین «_» باشد.
- استفاده از فاصله و سایر علائم نگارشی در نام مجاز نیست.
- نامها به حروف کوچک و بزرگ حساس نیستند. بهعنوان مثال، عبارت «Total» و «total» یکسان هستند.
- نام متغیر نباید با نشانی سلولهای اکسل یکسان باشد.
مثال فرمول نویسی با تابع LET در اکسل
برای آشنایی بیشتر با فرمولنویسی تابع LET در اکسل چند مثال در دو کاربرد اصلی زیر را توضیح میدهیم.
- جلوگیری از محاسبات تکراری در فرمول
- خواناتر کردن و درک بهتر فرمول
در مثالهای هر دسته از فرمولنویسی ترکیبی تابع LET با سایر توابع اکسل استفاده کردهایم. بنابراین در صورت عدم آشنایی با برخی از این توابع پیشنهاد میکنیم مسیر یادگیری در فرمولنویسی پیشرفته اکسل را تکمیل کنید. پیش از شروع، پیشنهاد میکنیم ویدیوی آموزشی رایگان زیر را برای آشنایی با نحوه بهکارگیری تابع LET در اکسل حتما مشاهده کنید:
۱. مثال استفاده از تابع LET برای جلوگیری از محاسبات تکراری
تابع LET در سادهسازی فرمولهایی که محاسبات ریاضی پیچیدهای دارند، بسیار کاربرد دارد. در این حالت با نامگذاری متغیرهای فرمول از محاسبات تکراری جلوگیری میکنیم. در این بخش روش کارکرد تابع LET در اکسل را با چهار مثال مختلف یاد میگیریم.
مثال ۱. نمایش نتیجه نمرات دانشآموزان
در جدول فرضی زیر نمرات دانشآموزان در سه درس مختلف را داریم. حال میخواهیم بر اساس میانگین نمرات هر دانشآموز به آنها نمره «عالی»، «خوب»، «رضایت بخش» و «ضعیف» بدهیم.

=AVERAGE(B2:D2)سپس با استفاده از تابع IF تودرتو بر اساس هر میانگین محاسبه شده، وضعیت دانشآموزان را مشخص میکنیم. در این حالت فرمول به شکل زیر درمیآید.
=IF(AVERAGE(B2:D2)>17,"عالی",IF(AVERAGE(B2:D2)>=15,"خوب",IF(AVERAGE(B2:D2)>14,"رضایتبخش","ضعیف")))همانطور که در این فرمول میبینیم، تابع AVERAGE سه بار تکرار شده است. یعنی اکسل مجبور است یک محاسبه را چند بار انجام دهد. برای حذف این تکرار از تابع LET استفاده میکنیم. به این شکل که نتیجه تابع AVERAGE را به شکل عبارت avg مانند فرمول زیر مینویسیم.
=LET(avg, AVERAGE(B2:D2), IF(avg>17, "عالی", IF(avg>=15, "خوب", IF(avg>14, "رضایتبخش", "ضعیف")))نتیجه نهایی به شکل زیر درمیآید.

فایده مهم استفاده از تابع LET در اکسل بروزرسانی محاسبات در صورت تغییر دادههای جدول است. بهعنوان مثال اگر یک درس دیگر در ستون E به جدول اضافه کنیم، در حالت فرمولنویسی بدون تابع LET باید محدوده محاسبه میانگین را در تمام قسمتهای فرمول IF تغییر دهیم. این کار علاوه بر زمانبر بودن، احتمال اشتباه را بالا میبرد. اما با استفاده از تابع LET فقط کافی است با یک تغییر محدوده محاسبه میانگین را از B2:D2 به B2:E2 تغییر دهیم. بنابراین بقیه قسمتهای فرمول نیازی به تغییر ندارند.
=LET(avg, AVERAGE(B2:E2), IF(avg>17, "عالی", IF(avg>=15, "خوب", IF(avg>14, "رضایتبخش", "ضعیف")))نتیجه نهایی به شکل زیر درمیآید.

مثال ۲. جستجوی تاریخ یک سفارش مشخص
در جدول فرضی زیر چند تاریخ میلادی داریم. هدف پیدا کردن تاریخ تحویل سفارش با توجه به یک سفارش مشخص است. در این جستجو میخواهیم اگر تاریخی در شماره سفارش مورد نظر نباشد، فرمول سلول خالی را نمایش دهد.

در حالت معمولی بدون استفاده از LET فرمول زیر را مینویسیم.
=IF(XLOOKUP(E2,A2:A11,C2:C11)="","",XLOOKUP(E2,A2:A11,C2:C11))همانطور که مشخص است در این فرمول تابع XLOOKUP دو بار اجرا میشود. بار اول برای برگرداندن نتیجه سلول خالی در صورت پیدا نکردن تاریخ و بار دوم نیز در صورت پیدا کردن تاریخ همان مقدار را برمیگرداند. اما با استفاده از تابع LET میتوانیم از این تکرار جلوگیری کنیم. بنابراین فرمول را به شکل زیر مینویسیم.
=LET(x,XLOOKUP(G9,B5:B16,D5:D16),IF(x="","",x))در این فرمول ابتدا نتیجه جستجوی تاریخ در متغیر x ذخیره میشود. سپس تابع IF این متغیر را بررسی میکند. اگر تاریخ مطابقت داشت، همان مقدار را برمیگرداند. در غیر این صورت سلول خالی را نمایش میدهد.
نتیجه نهایی به شکل زیر درمیآید.

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

در حالت معمولی اگر بخواهیم روزهای غیر کاری (شنبه، یکشنبه) را از این فهرست جدا کنیم، فرمول زیر را مینویسیم.
=FILTER(SEQUENCE(C5-C4+1,1,C4,1),WEEKDAY(SEQUENCE(C5-C4+1,1,C4,1),2)<6)
همانطور که مشخص است در این فرمول آرایه مربوط به تابع SEQUENCE دو بار تکرار شده است. اما با استفاده از تابع LET در اکسل و تعریف متغیر dates یکی از توابع SEQUENCE حذف میشود.
=LET(dates,SEQUENCE(C2-C1+1,1,C1,1),FILTER(dates,WEEKDAY(dates,2)<6))بنابراین نتیجه به شکل زیر درمیآید.

مثال ۴. ترکیب دادههای سه کاربرگ مختلف اکسل
مطابق تصویر زیر جدول فروش چند کالا را در سه کاربرگ با نامهای «Sheet2» ،«Sheet1» و «Sheet3» داریم. ساختار این کاربرگها یکسان است، اما در هر یک تعدادی ردیف خالی وجود دارد.

حال میخواهیم بعد از حذف ردیفهای خالی هر کاربرگ، آنها را با یکدیگر ترکیب کنیم. برای اینکار یک کاربرگ جدید بهنام «گزارش کل» درست میکنیم و در سلول A2 فرمول زیر را مینویسیم.
=LET(data,VSTACK(Sheet1:Sheet3!A2:D7),FILTER(data,CHOOSECOLS(data,1)<>""))در این فرمول تابع VSTACK اطلاعات محدوده B5:E16 را از هر سه کاربرگ جمعآوری میکند و دادههای آن را بهصورت یک آرایه واحد در کنار هم قرار میدهد. سپس تابع LET خروجی را در متغیر data ذخیره میکند. در مرحله بعد تابع FILTER محدودههای خالی را حذف میکند. در این فرمول، تابع FILTER دو بار از متغیر data استفاده میکند. یکبار بهعنوان آرایهای که قرار است ردیفهای خالی آن حذف شود. بار دیگر در تابع CHOOSECOLS که خالی بودن سلولهای ستون اول را بررسی میکند.
نتیجه نهایی به شکل زیر درمیآید.

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

افزایش مهارت فرمولنویسی با توابع اکسل همراه فرادرس
پیشنهاد اول برای همراهی با فرادرس و یادگیری تکمیلی فرمولنویسی اکسل، مشاهده فیلمهای آموزشی منتخب زیر است.
- فیلم آموزش استفاده از توابع و فرمولنویسی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش رایگان ساخت فرمولهای پیچیده در فرادرس
- فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
همچنین در مجموعه فیلم آموزش زیر امکان انتخاب موارد بیشتر بر حسب علاقهمندی وجود دارد.
۲. مثال استفاده از تابع LET برای خواناتر کردن و درک بهتر فرمول
استفاده از متغیرهای نامگذاری شده با تابع LET در اکسل درک فرمول را آسانتر میکند. با این کار هر بخش از محاسبات در فرمول را یکبار تعریف میکنیم به شکلی که در ادامه میتوانیم چند بار از آن استفاده کنیم. نامگذاری چند متغیر هر چند ممکن است سرعت و عملکرد فرمول را بیشتر نکند، اما باعث میشود ویرایش و عیبیابی آن در صورت مشاهده پیام خطا سادهتر شود.
برای آشنایی بیشتر با این قابلیت تابع LET در اکسل، چهار مثال کاربردی در این مورد را توضیح میدهیم.
مثال ۱. جدا کردن نام و نام خانوادگی افراد در دو ستون
در جدول زیر فهرستی از نام و نام خانوادگی افراد یک شرکت را در ستون A داریم.

حال میخواهیم نام و نام خانوادگی را در دو ستون جداگانه بنویسیم. برای این کار فرمول زیر را در سلول B2 مینویسیم.
=LET(txt,A2,space,FIND(" ",txt),first,LEFT(txt,space-1),last,MID(txt,space+1,LEN(txt)),HSTACK(first,last))همانطور که میبینیم، تابع LET فرمول را کوتاهتر نکرده است، بلکه با استفاده از نامگذاری متغیرها آن را خواناتر کرده است. فرمول به این شکل عمل میکند.
- ابتدا در عبارت txt,A2 مقدار سلول A2 را بهعنوان متغیر txt تعریف میکنیم.
- سپس متغیر space را تعریف میکنیم. این نامگذاری برای پیدا کردن محل فاصله بین نام و نام خانوادگی با استفاده از تابع FIND در بخش space,FIND(" ",txt) است. بهعنوان مثال در سلول A2 نتیجه این عبارت عدد «۴» خواهد بود.
- در مرحله سوم متغیر first را برای استخراج نام تعریف میکنیم. این نامگذاری برای نشان دادن عملکرد تابع LEFT در بخش first,LEFT(txt,space-1) است. بهعنوان مثال در سلول A2 عدد چهار از یک کم میشود و تابع LEFT سه کاراکتر اول را استخراج میکند.
- مرحله بعدی استخراج نام خانوادگی با استفاده از تابع MID است. در اینجا متغیر last را بهعنوان نماینده عملکرد تابع MID در بخش last,MID(txt,space+1,LEN(txt)) تعریف میکنیم. بهعنوان مثال تابع MID در سلول A2 از کاراکتر پنجم (۴+۱) شروع میکند و به تعداد طول متن کاراکتر استخراج میکند.
- در مرحله آخر، تابع HSTACK در بخش HSTACK(first,last) دو نتیجه را در کنار یکدیگر و دو ستون جداگانه نمایش میدهد.
بنابراین نتیجه به شکل جدول زیر درمیآید.

مثال ۲. ایجاد پیام تبریک اختصاصی برای هر مشتری
فرض میکنیم فروشگاهی امتیازات مشتریان خود را همراه یک کد در جدول زیر فهرست کرده است.

حال میخواهیم یک پیام تبریک اختصاصی برای مشتریانی که امتیاز بالای ۸۰۰ دارند، ارسال کنیم.
در حالت معمولی برای این کار فرمول زیر را مینویسیم.
=IF(VLOOKUP(A2,A2:C6,3,FALSE)>800, "تبریک "&VLOOKUP(A2,A2:C6,2,FALSE)&"، شما تاکنون "&VLOOKUP(A2,A2:C6,3,FALSE)&" امتیاز کسب کردهاید.","")این فرمول خوانایی بالایی ندارد. بنابراین با استفاده از تعریف متغیرهای name و points هر بخش محاسباتی را نامگذاری میکنیم. به این شکل که متغیر name مربوط به خروجی جستجوی اسامی و متغیر points مربوط به خروجی امتیازات است.
=LET(name,VLOOKUP(A2,A2:C6,2,FALSE), points,VLOOKUP(A2,A2:C6,3,FALSE),
IF(points>800,"تبریک "&name&"، شما تاکنون "&points&" امتیاز کسب کردهاید.","")
در این فرمول هر جستجو با تابع VLOOKP فقط یک مرتبه تعریف میشود و خروجی آن در دو متغیر name و points قرار میگیرند.
بنابراین نتیجه نهایی به شکل زیر درمیآید.

در مطلب زیر از مجله فرادرس، نکات بیشتری از ترفندهای فرمولنویسی با تابع VLOOKUP را توضیح دادهایم.
مثال ۳. گرد کردن اعداد به تعداد رقمهای معنادار
در جدول فرضی زیر میخواهیم اعداد ستون A را بر اساس تعداد رقمهای معنادار نمایش داده شده در ستون B گرد کنیم. ارقام غیر معنادار در این مثال اعداد صفر قبل از اولین رقم است.

برای این کار فرمول زیر را در سلول C2 مینویسیم.
=LET(number,A2,sf,B2,dp,sf-(1+INT(LOG10(ABS(number)))),rounded,ROUND(number,dp),
IF(dp>0,TEXT(rounded,"0."&REPT("0",dp)),TEXT(rounded,"0")))
- در این فرمول ابتدا متغیر number را برای عدد اصلی و متغیر sf را برای تعداد رقمهای معنادار تعریف میکنیم.
- متغیر dp تعداد رقمهای اعشاری مورد نیاز برای گرد کردن را با استفاده از تابع ABS، تابع LOG10 و تابع INT محاسبه میکند.
- متغیر rounded عدد را با استفاده از تابع ROUND و بر اساس تعداد رقمهای اعشاری محاسبه شده در مرحله قبل گرد میکند.
- در مرحله آخر تابع IF مقدار متغیر dp را بررسی میکند. اگر مقدار آن بزرگتر از صفر باشد، تابع TEXT عدد را با تعداد رقمهای اعشار مشخص شده نمایش میدهد. تابع REPT هم به تعداد مورد نیاز عدد صفر را تکرار میکند. در غیر این صورت، عدد بدون رقم اعشار نمایش داده میشود.
نتیجه نهایی به شکل زیر درمیآید.

مثال ۴. تعریف چند متغیر برای مدیریت چند شرط در فرمول
تابع LET برای خواناتر کردن فرمولهایی که چند شرط جداگانه دارند، انتخاب مناسبی است. زیرا با کمک آن میتوانیم هر شرط را به شکل مستقل نامگذاری کنیم. بهعنوان مثال فهرست چند فیلم بر اساس سال تولید، کارگردان و بازیگر را داریم. حال میخواهیم این جدول را با توجه به سه متغیر سال تولید، کارگردان و بازیگر فیلتر کنیم.
برای این کار فرمول زیر را مینویسیم.
=LET(
all,SEQUENCE(ROWS(movies))^0,
year,IF(C2="",all,movies[Year]=C2),
director,IF(D2="",all,ISNUMBER(SEARCH(D2,movies[Director]))),
actor,IF(E2="",all,ISNUMBER(SEARCH(E2,movies[Cast]))),
FILTER(movies,year*director*actor,"No match"))<br>نتیجه به شکل زیر درمیآید.

در این فرمول چهار متغیر مختلف تعریف کردهایم.
- متغیر all آرایهای از چند عدد «۱» است که با استفاده از تابع SEQUENCE ایجاد میشود. این متغیر برای هر ردیف از دادهها یک عدد «۱» را در نظر میگیرد.
- هر یک از متغیرهای director ، year و actor یکی از ورودیها را با ستون مربوط به خود مقایسه میکند. اگر ورودی خالی باشد، مقدار all را برمیگردانند.
- تابع FILTER نیز سه آرایه را در هم ضرب میکند و نتایجی را که با شرایط تعیین شده مطابقت دارند، بهعنوان خروجی برمیگرداند.
با این نامگذاری در هر شرط، دنبال کردن منطق فرمول برای کاربر آسان میشود.
پیامهای خطا هنگام کار با تابع LET در اکسل
یکی از نکات مهم در فرمولنویسی با تابع LET در اکسل نامگذاری درست متغیرها است. بنابراین برای جلوگیری از مشاهده پیام خطا لازم است به چند نکته توجه کنیم.
۱. یکی از رایجترین پیامهای خطا در اثر نامگذاری اشتباه، پیام «There`s a problem with this formula» است. این حالت زمانی پیش میآید که نام متغیرها مشابه با نام توابع اکسل یا آدرس سلول در جدول باشد. بهعنوان مثال اکسل نامهایی مانند «Fun1»، «Abc1» یا «A1» را با آدرس سلول یا اسم توابع یکسان میداند. این تداخل باعث ایجاد خطای داخلی در پردازش درست فرمول توسط اکسل میشود. بنابراین لازم است از منحصر به فرد بودن نامها اطمینان پیدا کنیم.

۲. اگر یکی از نامهای تعریف شده در بخشهای مختلف فرمول بهدلیل غلط تایپی یکسان نباشند، اکسل پیام خطای #NAME? را نشان میدهد. همچنین اگر نسخه اکسل قدیمی باشد، بهدلیل پشتیبانی نکردن تابع LET در این نسخهها با این پیام روبرو میشویم.
۳. نام متغیر در تابع LET حتما باید با حرف انگلیسی شروع شود. بنابراین اگر اولین حرف را به شکل عدد یا کاراکتر تعریف کنیم، پیام خطای «The first argument of LET must be a valid name» را میبینیم. همچنین استفاده از خط تیره یا فاصله مانند dep-1 یا dep 1 هم مخالف قوانین نامگذاری متغیرها در تابع LET است.

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














