ترکیب های تابع Choose در اکسل

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

1- جستجوی بازه ای

یکی از سوالاتی که خیلی زیاد پرسیده میشه این هست که یک جدولی داریم بصورت شکل 1، میخواهیم برای یک سری افراد که امتیازهای مختلفی کسب کرده اند، رتبه بندی مرتبط با هر امتیاز از جدول شکل 1 نمایش داده بشه. مثلا برای امتیاز 65 کلمه متوسط نمایش داده بشه. این کار 3 تا راه حل پیشنهاد میدیم:

شکل 1- جدول رده بندی

1-1- حل با استفاده از تابع VLOOKUP

 این روش در مقاله جستجوی بازه ای تشریح شده است.

1-2- حل با استفاده از Nested If یا If تو در تو

در این روش با استفاده از تابع IF مسئله حل میشه:

=IF(E2>150;”عالی”;IF(E2>100;”خوب”;IF(E2>50;”متوسط”;”ضعیف”)))

شکل 2- ترکیب های تابع Choose – جستجوی بازه ای با استفاده از Nested If

1-3- حل با استفاده از ترکیب Choose و توابع منطقی

یکی از ترکیب های تابع Choose جستجوی بازه ای است. برای اینکه این مسئله رو با استفاده از تابع Choose و عملگرهای منطقی حل کنید از فرمول زیر استفاده کنید:

=CHOOSE ( (E2>150) + (E2>100) + (E2>50) + (E2>0); “” ; ” خوب ” ; ” متوسط ” ; ” ضعیف عالی ” )

شکل 3- جستجوی بازه ای با استفاده از Choose و Logical

این فرمول چطور کار میکنه؟

هموطور که میدونید، خروجی گزاره های مقایسه ای (Logical)، True/False هست. یعنی اگر بخوایم مثال بالا رو شرح بدیم، به این شکل خواهد بود:

چون عدد 101 از 0، 50 و 100 بیشتره، 3 تا True خواهیم داشت و چون از 150 کمتره، یک False خواهیم داشت.

=CHOOSE ( FALSE + TRUE + TRUE + TRUE ; “” ; ” خوب ” ; ” متوسط ” ; ” ضعیف عالی ” )

از طرفی میدونیم که گزاره های منطقی True/False متن نیستند و مقدار دارند. مقدار True برابر با 1 و مقدار False برابر با 0 هست. پس نتیجه فرمول بالا بصورت زیر خواهد بود:

=CHOOSE ( 0 + 1 + 1 + 1 ; “” ; ” خوب ” ; ” متوسط ” ; ” ضعیف عالی ” )

پس فرمول نهایتا بصورت زیر درمیاد:

=CHOOSE ( 3 ; “” ; ” خوب ” ; ” متوسط ” ; ” ضعیف عالی “ )

و با توجه به مقدار 3 در آرگومان اول، فرمول، سومین مقدار از چهار مقدار، یعنی “خوب” رو بر میگردونه.

2- جستجوی بازه ای برای اعمال محاسبات متنوع

فرض کنید یک جدول فروش داریم. به ازای مبالغ مختلف فروش، درصدهای مختلفی از مالیات بر روی میزان فروش اعمال میشه. برای محاسبه میزان مالیات با توجه به مبلغ فروش، مشابه مثال قبل عمل میکنیم:

=CHOOSE( (E2>100) + (E2>50) + (E2>0) ; 5%*E2 ; 7%*E2 ; 9%*E2 )

شکل 4- جستجوی بازه ای و اعمال محاسبات متنوع با Choose

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

3- تولید داده تصادفی

از تابع Choose میشه برای تولید داده تصادفی استفاده کرد. مثلا فرض کنید داده های طیف لیکرت (عالی، خوب، متوسط، بد، خیلی بد) رو بصورت رندوم تولید کنید. برای این کار از ترکیب این تابع با Randbetween استفاده میکنیم:

=CHOOSE ( RANDBETWEEN (1;5) ; “عالی” ; “خوب” ; “متوسط” ; “بد” ; “ضعیف” )

تابع Randbwetween عدد تصادفی بین 1 تا 5 تولید میکنه و هر بار با توجه به عدد تولید شده، یکی از پنج مقدار تعیین شده رو به عنوان خروجی به ما میده.

4- جستجوی عقبگرد Backward Vlookup

حتما تا بحال به این موضوع برخورد کردید که بخواید مقداری رو در یک جدول Vlookup کنید و داده متناظر با اون رو در ستون های قبلی فراخوانی کنید. همونطور که میدونید، Vlookup ذاتا بصورت Forward یا رو به جلو هست. برای همین باید این اصل رعایت بشه. در مثال زیر میبینید که چطور با استفاده از تابع Choose چینش Table در این تابع رو تعیین میکنیم.

فرض کنید در جدول شکل 5 میخوایم میزان تحصیلات شخص رو با استفاده از کد ملی فراخوانی کنیم. در حالت عادی باید ستون کد ملی رو بیاریم قبل از میزان تحصیلات، چون تابع Vlookup جستجو به سمت عقب انجام نمیده.

شکل 5- جدول اطلاعات افراد

برای حل این مسئله از تابع Choose برای جابجا کردن ستون ها در دل فرمول استفاده میکنیم. به فرمول زیر دقت کنید. {2,1} چینش رو تعیین میکنه. در واقع داره میگه دو ستون B و D رو با این ترتیب کنار هم قرار بده: اول D و بعد B.

=CHOOSE ( {1,2} ; D2:D4 ; B2:B4 )

حالا این فرمول رو به عنوان آرگومان سوم یعنی Table_Array در تابع Vlookup قرار میدیم.

=VLOOKUP ( B6 ; CHOOSE ({1,2} ; D2:D4 ; B2:B4 ) ; 2 ; 0)

شکل 6- ترکیب های تابع Choose – جستجوی عقبگرد Backward Vlookup

5-  استخراج نام ماه یا روز هفته با توجه به تاریخ

فرض کنید میخواهیم نام ماه یا روز هفته رو با توجه به تاریخ روز استخراج کنیم. همونطور که قبلا در مقاله مربوط به تاریخ در اکسل توضیح داده شد، تابع Today تاریخ روز سیستم و تابع Month ماه یک رشته تاریخ رو به عدد بر میگردونه. یعنی اگر ماه آوریل باشه، تابع Month عدد 4 رو به ما نشون میده. پس برای این که بتونیم نام ماه مورد نظر رو استخراج کنیم، کافیه فرمول زیر رو بنویسیم:

=CHOOSE ( MONTH ( TODAY () ) ; “Jan” ; “Feb” ; “Mar” ; “Apr” ; “May” ; “Jun” ; “Jul” ; “Aug” ; “Nov” ; “Sep” ; “Dec”)

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

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

دانلود فایل این آموزش

برای دانلود مثال های تابع Choose در اکسل روی دکمه زیر کلیک کنید

با عضویت در سایت به صورت مستقیم دانلود کنید

• ارسال به ایمیل

Des