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

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

ایجاد لیست پایین افتادنی هوشمند در EXCEL

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

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

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

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

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

بر روی کامبوباکس راست کلیک کرده و Properties را انتخاب می کنیم

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

AutoWordSelect: False

LinkedCell: B3

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

MatchEntry: 2 – fmMatchEntryNon

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

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

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

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

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

برای انجام این کار از سه ستون کمکی و یک دامنه اسم پویا (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 و دومین مورد یافته شده است نمایش می دهد.در این حالت اگر هیچکدام از کلمات نام کشوری در باکس جستجو واردنشده باشد در مقابل نام آن چیزی قرار نمی گیرد و سلول مقابل آن نام خالی خواهد بود.

ستون کمکی در اکسل

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

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

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

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

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

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

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

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

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

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

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

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

در کادر Refer to فرمول زیر را وارد کنید.

=$H$3:INDE $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 نمایید.

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