تابع Choose در اکسل – آموزش به زبان ساده + مثال

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

آشنایی با فرمول تابع Choose در اکسل

از تابع Choose در اکسل برای دریافت عددی در یک لیست، بر اساس موقعیت دریافتی، استفاده می‌شود.

فیلم آموزش اکسل – محاسبات ریاضی و ماتریسی با Microsoft Excel 2016 در فرادرس

کلیک کنید

این تابع از اکسل ۲۰۰۷ در دسترس است و فرمول آن به شکل زیر نوشته می‌شود:

1CHOOSE(index_num, value1, [value2], …)

آرگومان‌های این تابع به شرح زیر هستند:

• Index_num  (ضروری): مشخص کننده موقعیت داده موردنظر در لیست. این آرگومان می‌تواند عددی بین ۱ تا ۲۵۴، ارجاع سلولی یا حتی فرمول دیگری باشد.
• Value1 : لیستی متشکل از ۲۵۴ مقدار برای انتخاب کردن. وارد کردن Value1 ضروری است اما سایر مقادیر اختیاری هستند. این آرگومان نیز می‌تواند عدد، مقدار متنی، ارجاع سلولی، فرمول یا نام‌های تعریف شده باشد.

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

1=CHOOSE(3, "بازیکن۴", "بازیکن۳", "بازیکن۲", "بازیکن۱")

1=CHOOSE(D1,A2,A3,A4,A5)

با نوشتن یکی از فرمول‌های بالا، نتیجه نهایی «بازیکن۳» خواهد بود که همان سومین مقدار در لیست است.

۳ نکته مهم درباره تابع Choose در اکسل

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

• تعداد اعداد لیستی که می‌توانید برای این تابع به کار ببرید، تا ۲۵۴ عدد است.
• اگر آرگومان Index_num  کمتر از یک یا بیشتر از تعداد مقادیر موجود در لیست باشد، با خطای «Value» مواجه خواهید شد.
• در صورتی که آرگومان Index_num ، عدد کسری باشد، به کمترین مقدار صحیح گرد می‌شود.

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

مثال‌هایی که در ادامه بررسی می‌کنیم، به خوبی نشان می‌دهند که تابع Choose چگونه می‌تواند توانایی سایر توابع را گسترش داده و روشی جایگزین برای انجام ساده‌تر برخی وظایف باشد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

استفاده از تابع Choose به جای ترکیب چند تابع IF

یکی از رایج‌ترین وظایف هنگام کار با داده‌ها، درخواست یافتن مقداری خاص بر اساس شرطی مشخص است. در بیشتر مواقع می‌توانید این کار را به کمک چند تابع IF تودرتو انجام دهید. اما ورود تابع Choose می‌تواند روش جایگزین ساده‌تر و راحت‌تری باشد.

مثال اول، دریافت مقادیر مختلف بر اساس شرط

تصور کنید فهرستی از نمرات دانش‌آموزان خود را دارید و می‌خواهید آن‌ها را بر اساس شرط های زیر برچسب گذاری کنید.

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

1=IF(B2>=15, "عالی", IF(B2>=10, "خوب", IF(B2>=5, "متوسط", "Poor")))

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

1=CHOOSE((B2>0) + (B2>=5) + (B2>=10) + (B2>=15), "ضعیف", "متوسط", "خوب", "عالی")

در نهایت نتیجه مانند تصویر زیر خواهد شد.

در این فرمول، ابتدا شرایط را در آرگومان Index_num  مشخص می‌کنیم، در این حالت اگر شرط درست باشد عبارت «True» و اگر غلط باشد عبارت «False» در فرمول قرار می‌گیرد. مثلا مقدار سلول ‌B4، سه شرط اول را برآورده می‌کند. پس فرمول در مواجه با این مقدار در سطح زیرین خود به شکل زیر تبدیل می‌شود:

1=CHOOSE(TRUE + TRUE + TRUE + FALSE, "ضعیف", "متوسط", "خوب", "عالی")

باید توجه داشته باشید که در بیشتر فرمول‌های اکسل، عبارت True برابر با «۱» و عبارت False برابر با «۰» است. به همین شکل می‌توانیم فرمول را به شکل زیر نیز ببینیم:

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

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

1=CHOOSE(3, "ضعیف", "متوسط", "خوب", "عالی")

در نتیجه، سومین مورد موجود در لیست نتایج که «خوب» است، نشان داده می‌شود. برای ساده‌تر و انعطاف‌ پذیرتر کردن فرمول می‌توانید از ارجاعات سلولی به جای نوشتن برچسب‌ها استفاده کنید. در این صورت فرمول به شکل زیر نوشته می‌شود:

1=CHOOSE((B2>0) + (B2>=5) + (B2>=10) + (B2>=15), $E$1, $E$2, $E$3, $E$4)

این در شرایطی است که برچسب‌ها را از قبل درون سلول‌های E1:E4 نوشته باشید.

اگر هیچ یک از شرایط درست نباشند و عبارت True وجود نداشته باشد، آرگومان Index_num  روی عدد صفر قرار می‌گیرد و با خطای Value مواجه می‌شوید. برای جلوگیری از بروز این مشکل می‌توانید کل فرمول را درون یک تابع Iferror قرار دهید، مانند نمونه زیر:

1=IFERROR(CHOOSE((B2>0) + (B2>=5) + (B2>=10) + (B2>=15), "ضعیف", "متوسط", "خوب", "عالی"), "")

مثال انجام محاسبات مختلف بر اساس شرط خاص

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

فیلم آموزش اکسل برای حل مسائل مهندسی در فرادرس

کلیک کنید

به عنوان نمونه در مثال زیر می‌خواهیم حق کمسیون هر فروشنده را بر اساس میزان فروش آن‌ها محاسبه کنیم.

با در نظر گرفتن اینکه مقادیر فروش در ستون B نوشته شده باشند، فرمول به شکل زیر خواهد بود:

1=CHOOSE((B2>0) + (B2>=5100000) + (B2>=10100000), B2*5%, B2*7%, B2*10%)

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

1=CHOOSE((B2>0) + (B2>=51) + (B2>=101), B2*$E$2, B2*$E$3, B2*$E$4)

استفاده از تابع Choose در اکسل برای تولید محتوای تصادفی

همان‌طور که احتمالا می‌دانید، تابعی مخصوص برای تولید عدد تصادفی در اکسل بین بالاترین و پایین‌ترین میزان مشخص شده به نام «RANDBETWEEN» وجود دارد. با ترکیب این تابع درون آرگومان Index_num  تابع Choose می‌توانید هر نوع داده تصادفی‌ که می‌خواهید را در اکسل تولید کنید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

1=CHOOSE(RANDBETWEEN(1,4), "ضعیف", "متوسط", "خوب", "عالی")

منطق این فرمول بسیار ساده است، ابتدا تابع RANDBETWEEN، عددی تصادفی بین یک تا چهار تولید می‌کند و سپس تابع Choose، مقدار متناظر با آن را از لیست پیدا کرده و در نتیجه نمایش می‌دهد.

توجه داشته باشید که تابع RANDBETWEEN تابع بسیار متغیری است و با هر تغییر کاربرگ، دوباره محاسبه می‌شود. در نتیجه لیست نتایج نهایی نیز تغییر خواهد کرد. برای جلوگیری از این مشکل می‌توانید با استفاده از قابلیت «Paste Special»، نتایج فرمول را با مقادیر جابه‌جا کنید تا تغییر نکنند.

انجام Vlookup سمت چپ به کمک تابع Choose

اگر تا به حال از تابع Vlookup استفاده کرده باشید، می‌دانید که این تابع تنها می‌تواند روی ستون سمت راست جست‌وجو کند. اگر در شرایطی بخواهید مقداری را از ستون سمت چپ پیدا کنید، باید با استفاده از ترکیب دو تابع «Index/Match» یا تابع Choose، ویلوکاپ را فریب دهید که در ادامه روند انجام این کار را می‌بینیم:

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

فرض کنید فهرستی از نمرات مختلف را در ستون A و نام دانش‌آموزان را در ستون B دارید و می‌خواهید نمره دانش‌آموز خاصی را پیدا کنید. از آن‌جایی که ستون دریافتی در سمت چپ ستون لوکاپ قرار دارد، استفاده از فرمول «Vlookup» ساده، شما را با خطای «N/A» مواجه خواهد کرد.

برای استفاده از VLOOKUP روی ستون سمت چپ، از تابع Choose برای مبادله کردن و تغییر دادن مکان ستون کمک می‌گیریم. در واقع به اکسل می‌گوییم که ستون «۱»، A و ستون «۲»، B است.

1=CHOOSE({1,2}, B2:B5, A2:A5)

از آن‌جایی که آرایه‌ای شامل {۱,۲} را در آرگومان Index_num   قرار داده‌ایم، تابع Choose، وجود محدوده را در آرگومان Value قبول می‌کند (که در شرایط عادی نمی‌پذیرد). حالا فرمول بالا را در جای آرگومان Table_array  از تابع Vlookup می‌گذاریم:

1=VLOOKUP(E1,CHOOSE({1,2}, B2:B5, A2:A5),2,FALSE)

به همین سادگی می‌توانیم این کار را بدون برخورد با هیچ خطایی انجام دهیم.

کاربرد تابع Choose برای نمایش روز کاری بعدی

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

1=TODAY()+CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2)

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

ممکن است این فرمول در ابتدا کمی پیچیده به نظر برسد اما اگر دقت کنید، منطق بسیار ساده‌ای دارد. در این فرمول، عبارت «WEEKDAY(TODAY())»، یک عدد سریالی متناظر با تاریخ امروز را در نتیجه قرار می‌دهد که از «۱» برای یکشنبه تا «۷» برای شنبه است. این عدد به آرگومان Index_num  تابع Choose می‌رود.

سپس Value1 تا Value7 به شکل «۱,۱,۱,۱,۱,۳,۲» قرار دارند. این اعداد نشان دهنده تعداد روزهایی هستند که باید به تاریخ امروز اضافه کنیم تا در نهایت روز کاری بعدی پیدا شود. مثلا اگر امروز یکشنبه تا پنجشنبه باشد (یعنی آرگومان Index_num  عدد ۱ تا ۵ باشد)، برای برگرداندن روز کاری بعدی، تنها «۱» واحد به تاریخ امروز اضافه می‌شود. به بیانی، تاریخ فردا به عنوان روز کاری بعدی نشان داده می‌شود.

اما اگر امروز جمعه باشد (آرگومان Index_num  عدد ۶ است)، «۳» واحد به تاریخ اضافه می‌شود تا دوشنبه به عنوان روز کاری بعدی نمایش داده شود. همچنین اگر امروز شنبه باشد (آرگومان Index_num روی مقدار ۷ قرار بگیرد)، برای به‌دست آوردن روز کاری بعدی که همان دوشنبه است، «۲» واحد به تاریخ اضافه خواهد شد. همچنین می‌توانید از روش‌های تبدیل تاریخ میلادی به شمسی در اکسل نیز برای نمایش تاریخ شمسی استفاده کنید.

تابع Choose برای دریافت نام روز/ماه سفارشی از تاریخ

اگر بخواهید نام کامل یک روز مانند Monday یا نام مختصر آن مانند Mon را دریافت کنید، امکان استفاده از تابع TEXT را خواهید داشت. اما اگر قصد دریافت نام یک هفته یا ماه با قالب سفارشی را داشته باشید، باید فرمول Choose را مانند مثال زیر بنویسید.

برای دریافت یک روز هفته:

1=CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa")

برای دریافت یک ماه:

1=CHOOSE(MONTH(A2), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

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

سخن پایانی

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

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