آشنايي با باكس جستجوي هوشمند در اكسل

سه شنبه ۰۲ خرداد ۹۶ | ۱۰:۴۴ ۳,۲۶۹ بازديد

هدف از اين پست آموزش ايجاد ليست پايين افتادني هوشمند است كه با تايپ هر كلمه، كليه نام هاي داراي حرف تايپ شده را جهت انتخاب به كاربر پيشنهاد دهد.

براي ايجاد چنين ليستي بايد مراحل زير را انجام دهيم

مرحله اول: ايجاد و تنظيم باكس جستجو

در اين مرحله ما يك كامبو باكس داريم بايد تنظيمات آن را طوري انجام دهيم كه هنگام تايپ متن در اين كامبوباكس، متن آن نيز در جعبه جستجو ظاهر شود. براي انجام اين كار به شرح ذيل عمل مي كنيم.

در تب (سربرگ) developer و در بخش ActiveX Control ابزار ComboBox را انتخاب مي كنيم (اگر سربرگ Developer در نوار ريبون ديده نمي شود مسير زير را براي فعال كردن آن طي كنيد.

File/Options/Customize Ribbon و چك باكس كنار گزينه Developer را فعال كنيد)

در يك سلول دلخواه كليك كرده تا كامبوباكس در آنجا قرا گيرد.

بر روي كامبوباكس راست كليك كرده و Properties را انتخاب مي كنيم

در پنجره اي كه ظاهر مي شود تغييرات را به صورت زير اعمال مي كنيم.

AutoWordSelect: False

LinkedCell: B3

ListFillRange: DropDownList (( در گام دوم يك يك نام براي اين مرحله ايجاد خواهيم كرد

MatchEntry: 2 – fmMatchEntryNone

شكل 1

سلول B3 به كامبوباكس لينك مي شود به اين معني كه هر مقداري كه در كامبوباكس وارد شود در سلول B3 نيز ظاهر مي شود.

به سربرگ Developer رفته و بر روي Design mode كليك مي كنيم تا بتوانيم متن خود را در كامبوباكس وارد كنيم.

از آنجا كه سلول B3 به بامبوباكس لينك شده است هر مقداري كه در كامبوباكس وارد شود در سلول B3 نمايش داده مي شود.

مرحله دوم: تنظيم اطلاعات

حال كه تنظيمات باكس جستجو را انجام داده ايم بايد داده هاي مورد نظر خود را وارد نماييم. ما مي خواهيم اگر هر اطلاعاتي در باكس جستجو وارد كرديم كلمه اي يا قسمتي از متني را كه وارد شده است نمايش داده شود

براي انجام اين كار از سه ستون كمكي و يك دامنه اسم پويا (dynamic name range) استفاده مي كنيم.

ستون كمكي 1

فرمول زير را در سلول F3 تايپ كرده و آن را تا F22 كپي مي كنيم.

=,,ISNUMBER(IFERROR(SEARCH($B$3,E3,1),””))

اين فرمول در صورتي كه متن وارد شده در كامبوباكس در ستون نام كشورها وجود داشته باشد عدد 1 را نمايش مي دهد. مثلاً اگر شما حرف UNI را تايپ نماييد فقط در مقابل نام هاي United stats و United kingdom عدد 1 و در مقابل نام ساير كشورها عدد 0 قرار خواهد گرفت

شكل 2

ستون كمكي 2

فرمول زير را در سلول G3 وارد كرده و تا سلول G22 كپي مي كنيم.

=IF(F3=1,COUNTIF($F$3:F3,1),””)

اين فرمول مقدار وارد شده در كامبوباكس را بررسي كرده و در صورتي كه اين مقدار با ليست مورد جستجو مطابقت داشته باشد براي اولين مورد يافته شده عدد 1، براي دومين مورد يافته شده عدد 2 و به همين ترتيب در مقابل نام كشورهايي كه با مقدار وارد شده در كامبوباكس مطابقت داشته باشند اعداد ترتيبي قرار مي دهد.

براي مثال اگر شما عبارت UNI را در كامبوباكس وارد كنيد در سلول G3 عدد 1 كه مطابق با United States و سلول G9 عدد 2 را كه مطابق با نام United kingdom و دومين مورد يافته شده است نمايش مي دهد.در اين حالت اگر هيچكدام از كلمات نام كشوري در باكس جستجو واردنشده باشد در مقابل نام آن چيزي قرار نمي گيرد و سلول مقابل آن نام خالي خواهد بود.

شكل 3

ستون كمكي 3

در سلول H3 فرمول زير را قرار داده و تا H22 آن را كپي نماييد.

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)

اين فرمول تمام نام هايي را كه با مقدار تايپ شده در كامبوباكس مطابقت داشته باشند را بدون فاصله خالي بين آنها پشت سر هم قرار مي دهد.

مثلاً اگر شما UNI را در كامبوباكس (جعبه جستجو) وارد نماييد نام هاي United States و United Kingdom ليست شده و نام بقيه كشورها نمايش داده نمي شوند.

شكل 4

ايجاد نام دامنه پويا (Dynamic range Name)

حال كه ستون هاي كمكي را ايجادكرديم نوبت به ايجاد نام دامنه پويا مي رسد. اين نام دامنه پويا فقط شامل آيتم هايي خواهد بود كه با مقادير واردشده در كامبوباكس مطابقت داشته باشند.

ما از اين نام دامنه پويا براي نشان دادن مقادير كامبوباكس استفاده مي كنيم.

تذكر: همانطور كه در گام اول نام DropDownList در مقابل فيلد ListFillRange در خواص كامبوباكس وارد كرديم در اينجا نام دامنه پويا را مشابه همان نام ايجاد مي كنيم.

براي ايجاد اين نام دامنه مطابق مراحل زير عمل نماييد.

به سربرگ Formulas و سپس Name Manager برويد

در كادر Name Manager بر روي New كليك كنيد تا پنجره نام جديد ظاهر شود

در فيلد نام عبارت DropDownList را وارد نماييد

در كادر Refer to فرمول زير را وارد كنيد.

=$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

مرحله سوم

استفاده از كد VBA براي تكميل گام آخر

براي تكميل مرحله نهايي كد هاي زير را به كامبوباكس اضافه نماييد.آموزش اكسل براي اين منظور مطابق مراحل زير عمل كنيد.

در سربرگ Developer بر روي Design كليك كنيد

بر روي كامبوباكس راست كليك كرده و گزينه View code را انتخاب نماييد

در پنجره ظاهر شده كد هاي نوشته شده را پاك كرده كدهاي زير را قرار دهيد.