تابع CHOOSE اکسل-انتخاب و یا جدا کردن

ایام هفته را در نظر بگیرید:

شنبه    یکشنبه    دوشنبه    سه شنبه    چهارشنبه    پنج شنبه    جمعه

همه ما می دانیم شنبه اولین روز و یکشنبه دومین روز و …..می باشد.یعنی در ذهن خود شنبه را متناظر با عدد 1 و یکشنبه را متناظر با عدد 2 و…در نظر می گیریم.

تابع Choose مشابه همین کار را انجام می دهد.یعنی اگر عدد 1 را به آن بدهیم، روز شنبه را بر می گرداند و اگر عدد 2 را به آن بدهیم روز یکشنبه را برای ما بر می گرداند.اجازه دهید این مفاهیم را در مثال تابع Choose به شما نشان دهیم.

ساختار تابع Choose:

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

=CHOOSE(مقدار دوم , مقدار اول, عدد, …)

index_num:

این قسمت یک آرمگان اجباری می باشد.در این بخش یک عدد بین 1 تا 254 باید وارد کنید.دقت کنید در این بخش می توانید از مرجع یا توابعی استفاده کنید که خروجی آن ها عددی بین 1 تا 254 باشد.

اگر عدد اعشاری وارد کردید.اکسل جز صحیح آن را در نظر می گیرد و اگر عدد وارد شده کمتر یا بیشتر از آخرین Value  متناظر بود، خطای #VALUE! به ما نشان داده خواهد شد.

value1:

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

خوب اکنون قصد داریم مثال گفته شده را با تابع Choose بیان کنیم:

=CHOOSE(1,”شنبه”,”یکشنبه”,”دوشنبه”,”سه شنبه”,”چهارشنبه”,”پنج شنبه”,”جمعه”)

این فرمول می گوید وقتی عدد 1 را تایپ کردید من ارزش (Value) متناظر با آن را بر می گردانم.ارزش متناظر با عدد 1 روز “شنبه” خواهد بود.چون اولین ارگومان ما “شنبه” می باشد پس خروجی فرمول برابر با شنبه می باشد.

اگر مانند فرمول زیر عدد 2 را به کار بگیریم، خروجی آن برابر با “یکشنبه “می باشد.زیرا ارزش متناظر با آن برابر با “یکشنبه” می باشد.

=CHOOSE(2,”شنبه”,”یکشنبه”,”دوشنبه”,”سه شنبه”,”چهارشنبه”,”پنج شنبه”,”جمعه”)

تا اینجا با مفهوم این تابع آشنا شدید،اما بیایید این بار به جای اعداد و متن ها از رفرنس ها استفاده کنیم.

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

=CHOOSE(A1,”شنبه”,”یکشنبه”,”دوشنبه”,”سه شنبه”,”چهارشنبه”,”پنج شنبه”,”جمعه”)

یعنی الان وقتی در سلول A1 اعداد 1 تا 7 را تایپ کنیم، آنگاه تابع ایام هفته را برای ما نشان خواهد داد.

شاید شما تا کنون از IF های متداخل برای این کار استفاده می کرده اید:

=IF(A1=1,”شنبه”,IF(A1=2,”یکشنبه”,IF(A1=3,”دوشنبه”,IF(A1=4,”سه شنبه”,IF(A1=5,”چهارشنبه”,IF(A1=6,”پنج شنبه”,IF(A1=7,”جمعه”,””)))))))

یا اگر تایع IFS در اکسل 2016 را فرا گرفته اید، می توانستید از این روش استفاده کنید:

=IFS(A1=1,”شنبه”,A1=2,”یکشنبه”,A1=3,”دوشنبه”,A1=4,”سه شنبه”,A1=5,”چهارشنبه”,A1=6,”پنج شنبه”,A1=7,”جمعه”)

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

اکنون می خواهیم به شما نشان دهیم که تابع Choose می تواند به عنوان رفرنس یک تابع دیگر به کار گرفته شود و چطور از VALUE ها به عنوان رنج استفاده کنیم:

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

ترکیب choose و sum

قصد داریم جمع  تعداد قطعات معیوب را به دست آوریم.

ابتدا محدوده ها را با تابع Choose انتخاب می کنیم.در این مثال ما دو محدوده داریم، محدوده اول A2:A5 و محدوده دوم B2:B5 می باشد.چون قصد داریم قطعات معیوب را به دست آوریم پس در قسمت index عدد 2 را وارد می کنیم:

=CHOOSE(2,A2:A5,B2:B5)

اگر این فرمول را به تنهایی در سلولی تایپ کنید به شما پیغام خطا خواهد داد(زیرا خروجی آن یک محدوده می باشد نه یک عدد).این فرمول در ترکیب با تابع Sum خواسته ما را تکمیل خواهد کرد:

=SUM(CHOOSE(2,A2:A5,B2:B5))

این فرمول می گوید از دو محدوده موجود،محدوده دوم (B2:B5) را انتخاب کن و سپس آن ها را با یکدیگر جمع کن.

البته شاید با خود بگویید می توانستید خیلی راحت از فرمول زیر استفاده کنید:

=SUM(B2:B5)

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

در مثال بعدی قصد داریم نشان دهیم آرایه ها چطور در تابع Chooos برای ما نقش بازی می کنند.

[cf-restrict]

مثال قبل را در نظر بگیرید.از شما می خواهند که جمع دو ستون سالم و معیوب را به دست آورید.

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

در واقع به جای index از {1,2} استفاده کرده ایم و معنی آن این می باشد که هر دو محدوده را انتخاب کن.

اما یک کاربرد بسیار عالی از تابع Choose می توان به ترکیب آن با Vlookup اشاره کرد:

اگر با تابع Vlookup کار کرده باشید،قطعا مشاهده کردید که در ستون اول جستجو را انجام میدهد و در ستون های بعدی مقدار متناظر آن را نمایش می دهد.اما عکس آن ممکن نیست.به تصویر زیر دقت کنید.اگر در ستون B دنیال کد ملی بگردیم و بخواهیم نتیجه را در ستون A نمایش دهد، توانایی انجام این کار را ندارد.

ترکیب choose و vlookup

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

=VLOOKUP(1005,CHOOSE({1,2},B1:B9,A1:A9),2,0)

در قسمت Table_array تابع vlookup به جای اینکه محدوده را معرفی بکنیم،از تابع Choose استفاده می کنیم.در واقع به کمک این تابع، جدولی را تشکیل داده ایم که از دو ستون تشکیل شده است و ستون اول آن، همان ستون کد ملی است  B1:B9 و ستون دوم آن همان ستون نام A1:A9 می باشد.

بیایید با یک مثال دیگر به شما نشان دهیم که تابع Choose چطور می تواند نقش یک محدوده  در دو منطقه جداگانه را بازی کند:

تصویر زیر را در نظر بگیرید.همان طور که مشاهده می کنید از دو جدول جداگانه تشکیل شده است.از شما می خواهند که سطح در آمد کد ملی 1005 را محاسبه نمایید.

تابع choose برای دو محدوده

شاید در نگاه اول از تابع Vlookup به شکل زیر استفاده کنید:

=VLOOKUP(1005,A1:G9,7,0)

اما شما با این کار دو محدوده را یکی کرده اید و از طرفی اگر جدول دوم در یک شیت دیگر باشد اصلا نمی توانید از این فرمول استفاده کنید.پس مجددا به سراغ فرمول Choose خواهیم رفت:

=VLOOKUP(1005,CHOOSE({1,2},A2:B9,F2:G9),2,0)

در واقع تابع Choose یک جدول دو ستونی برای ما ایجاد کرده است که ستون اول محدوده A2:A9 و محدوده دوم آن G2:G9 می باشد.یعنی کد ملی را در ستون A2:A9 جستجو می کند و سطح در آمد را در ستون G2:G9 جستجو خواهد کرد.

اگر جدول دوم در شیت 4 (Sheet 4) باشد فرمول به شکل زیر خواهد بود:

=VLOOKUP(1005,CHOOSE({1,2},A2:B9,Sheet4!F2:G9),2,0)

شما اگر از این تابع استفاده های دیگری می کنید لطفا برای ما ارسال کنید.

[/cf-restrict]

 

4 دیدگاه برای “تابع CHOOSE اکسل-انتخاب و یا جدا کردن

  1. مجتبی گفته:

    سلام
    اگر برای انتخاب یک لیست غیر عددی مقادیر عدد بخواهیم تابع CHOOSE چطور به کار ببریم.مثلا با انتخاب کلمه A عدد 2 را نمایش دهد؟

  2. معین گفته:

    سلام خدمت اساتید عزیز
    من دارم یه لیست قیمت از محصولات درست میکنم با اکسل. ولی یه مشکل کوچیک دارم
    در شغل ما، چند کارخانه تولید کننده اصلی وجود دارند مثلا کارخانه چینی سینا، چینی نام، چینی مروارید، چینی گلسار و …. . که هر کدام ازین کارخانه ها چندین محصول با نام های مختلف تولید میکنند (مثلا چینی سینا: آمون، آندره، آروشا، آتیلا و…. چینی نام: ساترون گرد، ساترون مستطیل، هما شیردار ، آمون و ….) قسمت پیچیده اونجایی هست که هر کدوم ازین محصولات از لحاظ کیفیت 3 درجه دارند که هر درجه ای قیمت خودش رو داره.
    من یک شیت از تموم محصولات کارخونه ها و درجه ها و قیمت هاشون تهیه کردم. در شیت دیگه 2 تا لیست کشویی indirect ساختم که وقتی از کشویی اول، سینا رو انتخاب میکنم، کشویی دوم فقط محصولات کارخونه سینا رو نشونم میده.
    تا اینجا همه چیز خوبه. ازینجا به بعد میخوام یه کشویی دیگه اضافه کنم که وقتی داخلش درجه 1، 2 و یا 3 رو انتخاب کردم ، خودش بره قیمت رو برام پیدا کنه و بیاره
    مثلا به این شکل بشه:
    چینی سینا > سنگ آمون > درجه 2 > قیمت: ؟
    ممنون میشم من رو راهنمایی کنید.

  3. مجيد فرحاني گفته:

    باسلام خدمت دوستان گرامي
    فرض بر اينكه 20 تا شيت داشته باشيم كه هر كدام به اسم درامي صنعتي باشد مثلا شيت درام كاستيك دو ستون لول و ليتر باشد
    در شيت اول از سه خانه جهت رويت مقادير ميخواهم استفاده كنم
    شرط خانه اول اسم درام است كه كذام شيت باشد بعنوان مثال اگر اسم درام كاستيك يعني عنوان شيت تايپ شد در خانه دوم هر لولي از آن تايپ شود مقدار ليتر آنرا نمايش دهد
    ممنون ميشم راهنمايي كنيد

  4. پوریا زارعی گفته:

    ممنونم از مطلب مفیدتون که باعث شد مشکلم حل بشه خدا خیرتون بده
    اما من چه استفاده ی دیگه ای از این تابع میکنم :
    وقتی می خواهیم از فرمول رو به رو در جداول و منابع بزرگ استفاده کنیم : =VLOOKUP(کارنامه!$B4,اطلاعات!$B$2:$I$10,2,0) برای درگ کردن و استفاده از اتوفیل برای پر کردن دیگر خونه ها به مشکل می خوریم چون نمی تونیم انتخاب کنیم که اکسل اون 2 رو که به وی لوکاپ میگه تو کدوم ستون دنبال اونی که می خوایم بگرده رو اتوفیل کنه واسمون پس چی کار می کنیم ؟
    از تابع باحال چوزاستفاده میکنیم به این صورت که یک جا تو خون هایی که من اینجا
    c1,d1,e1,f1,g1,h1,i1,
    هستند اعدادی که می خوام رو نوشتم و به تابع چوز آدرسشون رو دادم تا با اتوفیل واسم از 1 تا 7 ستونی که می خوام تو وی لوکاپ واسم سرچ کنه به این صورت :0
    =VLOOKUP(کارنامه!$B3,اطلاعات!$B$2:$I$10,CHOOSE(1,C$1,D$1,E$1,F$1,G$1,H$1,I$1),0)
    به همین راحتی 🙂

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *