جستجو در لیست کشوئی

[amir_ts]

با سلام

برای ایجاد لیست کشویی با قابلیت جستجو ما به سه مرحله پردازش نیاز داریم:

1-تنظیمات combo box از ActiveX Controls
2-پردازش اطلاعات لیست
3-استفاده از یک کد کوتاه vba در قسمت کد نویسی


ابتدا از تب Developer از برگه insert یک combo box به شیت اضافه میکنیم.
10564

با کلیک راست بر روی combo box و انتخاب گزینه properties موارد زیر را بر روی combo box انجام میدهیم :
قسمت AutoWordSelect را برابر با false قرار میدهیم.
LinkedCell را برابر آدرس مورد نظر که قراره لیست در آن نمایش داده شود قرار دهید . ( درفایل نمونه توضیحی سلول a3 میباشد)
در قسمت ListFillRange یک نام قرار بدید مثلا DropDownList ( توجه داشته باشید در قسمت name manager با همین نام باید محدوده ای پویا را نامگذاری کنیم.)
در قسمت MatchEntry گزینه 2 – fmMatchEntryNone را انتخاب کنید.

10565

10566

به شیت لیست اسامی بر میگردیم .

در اولین ستون کمکی (h) ابتدا با استفاده از فرمول


=--ISNUMBER(IFERROR(SEARCH(Sheet4!$A$3;A2;1);""))

از وجود نزدیک ترین حروف تایپی به ستون نام و نام خانوادگی مطلع میشیم.چنانچه نام مورد نظر و یا نزدیک ترین حروف به آن وجود داشت عدد 1 و در غیر اینصورت عدد صفر میگیره.

در ستون کمکی دوم (i) هم با این استفاده ازفرمول زیر تعداد 1 ها یعنی مورد های نزدیک به جستجو شمارش میشن و یک ها( نام های مورد نظر ) رو رو به نوعی از هم تفکیک میکنیم که در مرحله بعد قابل بازیابی و تشخیص باشند .


=IF(H2=1;COUNTIF($H$2:H2;1);"")

در ستون سوم کمکی (j) با استفاده از این فرمول تمام موارد یافته شده رو لیست میکنیم.


=IFERROR(INDEX($A$2:$A$33;MATCH(ROWS($I$2:I2);$I$2 :$I$33;0));"")

ستون سوم لیست اصلی ماست که باید نمایش داده بشه که با استفاده از name mangerو انتخاب نام DropDownList (دقیقا نامی که در combo box قرار داده شد) و قرار دادن محدوده پویای آن در قسمت Refers to که در این فایل نمونه توضیحی محدوده زیر میباشد.


=$J$2:INDEX($J$2:$J$33;MAX($I$2:$I$33);1)

و در آخر یک کد کوتاه vba رو باید در قسمت کد نویسی شیتی که combo box در آن قرار داره بنویسید.


Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub




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

[tabas]

ممنون ولی فقط تا 20 اسم جستجو میکنه و اسم21م به یعد رو نمیشناسه. من فرمولها را تا خانه 99 هم درگ کردم و عملا خونه 20 با 21 هیچ فرقی نداره ولی نتیجه فرمول na و در نتیجه خالیه.
لطفاً بررسی کنید.

[mokaram]

دوست عزیز فرمولها را ببینید فقط برای سل های محدودی نوشته شده. اونا را بسط بدید ، بازه تغییر میکنه ( name manager را هم ببینید )

[Nasim90]

ایجاد لیست فرو ریز قابل جستجو در اکسل
فرض کنید لیستی داریم از اسامی استان های ایران و میخواهیم با تایپ قسمتی از نام یک استان، لیست محدودتری برای انتخاب داشته باشیم.
منطق کلی کار این هست که باید لیستی از سلول هایی که شامل عبارت مورد نظر ما هستن رو ایجاد کنیم و به عنوان ورودی لیست دیتاولیدیشن قرار بدیم. برای انجام این کار، طبق مراحل زیر پیش میریم:


گام اول: پیدا کردن عبارت مورد نظر
تابعی که میتونه جستجو کنه ببینه یک عبارت در یک سلول وجود داره یا نه تابع Find/Search هست. این دو تابع در مورد سرچ فارسی عینا مشابه عمل میکنن. در صورت پیدا کردن عبارت مورد نظر، خروجی عدد و در غیر اینصورت خطای #Value! خواهد بود.
پس با فرض اینکه لیست مورد نظر قراره در سلول D1 ایجاد بشه، فرمول زیر رو مینویسیم.

=FIND ($D$1 , A2)

در صورتی که عبارت مورد نظر پیدا بشه، خروجی عدد خواهد بود و در غیر اینصورت خطا.


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

=IF ( ISNUMBER ( FIND ($D$1,A2) ) ,MAX ($B$1:B1) +1 ,”” )


در واقع تابع Isnumber چک میکنه که آیا خروجی تابع Find عدد هست یا نه. اگه عدد بود، ماکزیمم محدوده بالای سرشو باضافه 1 میکنه، اگر هم عدد نبود (خطا بود) خالی میذاره. مقابل دو استان که شامل عبارت “رد” هستن، یعنی اردبیل و کردستان، به ترتیب شماره 1 و 2 نمایش داده می شه.


گام سوم: لیست کردن موارد پیدا شده
حالا کافیه که سلول های مشخص شده رو پشت سر هم لیست کنیم. برای این کار میتونیم از Backward Vlookup استفاده کنیم. یا اینکه از ترکیب Match و Index استفاده کنیم:

=IFERROR ( INDEX ($A$2:$A$32, MATCH ( ROW(A1) , $B$2:$B$32, 0)) , “”)


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


گام چهارم: نام گذاری پویا برای محدوده لیست
مرحله بعد ایجاد یک محوده نامگذاری پویا هست که بتونیم به دیتاولیدیشن اختصاص بدیم.
از تب Formula گزینه Name Manager رو کلیک میکنیم و یک نام به عنوان List ایجاد میکنیم و فرمول زیر رو در اون مینویسیم.

=OFFSET ( Sheet2!$C$2,0,0, COUNTIF ( Sheet2!$C$2:$C$32 , “?*” ) ,1)


برای درک بهتر محدوده های نامگذاری داینامیک، این مقاله را مطالعه کنید.


نکته:
تابع

COUNTIF( Sheet2!$C$2:$C$32 , “?*” )
تعداد سلول های پر که داده نمایش میدن رو شمارش میکنه و کاری به سلول هایی که با فرمول پر شدن ولی خالی نمایش داده میشن نداره.

گام پنجم: ایجاد لیست کشویی
کافیه که نام ایجاد شده رو به Data Validation اختصاص بدیم. برای این کار روی سلول D1 کلیک کرده و از تب Data گزینه Data Validation رو انتخاب میکنیم. از تب Settings گزینه List رو انتخاب میکنیم و نام تعیین شده که در گام چهارم فرمول نویسی کردیم رو تخصیص میدیم. قبل از اینکه Ok رو بزنیم، به تب Error Alert رفته و تیک گزینه اخطار رو برمیداریم و بعد Ok رو میزنیم. حالا کافیه عبارت مورد نظر رو در سلول D1 تایپ کنیم و بعد لیست فروریز رو باز کنیم. می بینیم که فقط سلول هایی که شامل اون عبارت هستن در لیست نمایش داده میشه. امیدورام مطلب مفيدي بوده باشد.