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

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

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

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

مرحله اول: ایجاد و تنظیم باکس جستجو

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

در تب (سربرگ) 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

فرمول زیر را در سلول 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 را انتخاب نمایید

در پنجره ظاهر شده کد های نوشته شده را پاک کرده کدهای زیر را قرار دهید.

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = “DropDownList“

Me.ComboBox1.DropDown

End Sub

برای نتیجه و ظاهر بهتر کار می توانید سلول B3 را با کامبوباکس بپوشانید و ستونهای کمکی را نیز Hide نمایید.

امیدوارم ورد استفاده شما عزیزان قرار گیرد.

لینک دانلود فایل این مقاله