ایجاد لیست فرو ریز قابل جستجو در اکسل
فرض کنید لیستی داریم از اسامی استان های ایران و میخواهیم با تایپ قسمتی از نام یک استان، لیست محدودتری برای انتخاب داشته باشیم.
منطق کلی کار این هست که باید لیستی از سلول هایی که شامل عبارت مورد نظر ما هستن رو ایجاد کنیم و به عنوان ورودی لیست دیتاولیدیشن قرار بدیم. برای انجام این کار، طبق مراحل زیر پیش میریم:
گام اول: پیدا کردن عبارت مورد نظر
تابعی که میتونه جستجو کنه ببینه یک عبارت در یک سلول وجود داره یا نه تابع 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 تایپ کنیم و بعد لیست فروریز رو باز کنیم. می بینیم که فقط سلول هایی که شامل اون عبارت هستن در لیست نمایش داده میشه. امیدورام مطلب مفيدي بوده باشد.
علاقه مندی ها (Bookmarks)