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

در بسیاری از مواقع، گزینه های مد نظر برای لیست بسیار زیاد بوده و در صورت انتخاب کلیه داده ها در لیست، کارایی لیست کم می شود. لذا در این بخش به بررسی روش ایجاد لیست با قابلیت جست و جو کردن پرداخته شده است.

تعریف مسئله ایجاد لیست پیشرفته با قابلیت جست و جو در اکسل

فرض کنیم لیست افراد یک شرکت شامل ۵۰ نفر به صورت ذیل درون اکسل نگهداری میشود.

در این حالت میخواهیم درون سلول F2 (که در شکل ذیل به رنگ دیگری نشان داده شده است) لیست مد نظر با قابلیت سرچ را ایجاد نماییم.

روش حل مسئله ایجاد لیست پیشرفته با قابلیت جست و جو در اکسل

به منظور حل این مسئله کافی است مراحل ذیل را انجام داد.

۱-   درون سلول B2 (مقابل اولین اسم) فرمول ذیل را وارد نموده و فرمول مد نظر را برای کلیه سلولهای زیر سلول B2 نیز اعمال نموده.

=IF(ISERR(FIND($F$2,A2)),0,MAX($B$1:B1)+1)

حاصل این تابع در مقابل هر اسمی که کاندید انتخاب باشد (شامل عبارت ثبت شده درون سلول F2 باشد) برابر با یک عدد می باشد به گونه ای که مقابل اولین کاندید عدد ۱ و مقابل کاندید بعدی عدد ۲ و …. ثبت خواهد شد.

در حالتی که درون سلول F2 مقداری وارد نشده باشد، تمامی اسامی کاندید انتخاب خواهند بود در حالی که اگر دورن سلول F2 یک عبارت مانند “علی” ثبت شود ملاحظه میشود که مقابل بسیاری از نامها که شامل عبارت “علی” نمیشوند (کاندید انتخاب نمی باشند) عدد ثبت ثبت میشود و مقابل سایر اسامی که کاندید می باشند، به ترتیب عدد ۱ تا … ثبت شده است.

۲-   درون سلول D1 فرمول ذیل را نوشته و تا سلول D50 اعمال نموده.

=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),””)

حاصل این فرمول در سلول D1 برابر با اولین اسم کاندید (بر اساس خروف ثبت شده درون سلول F2) و در سلول D2 برابر با دومین اسم کاندید بوده و به همین صورت درون سلول D50، ۵۰ امین اسم کاندید (در صورت وجود) آورده خواهد شد. لذا در صورت ثبت واژه “علی” درون سلول F2 حاصل این تابع به صورت شکل ذیل بوده.

که مطابق با شکل فوق در این حالت در محدوده D2 تا D50 کلیه اسامی که در آنها از واژه علی استفاده شده است آورده شده است.

در حالت دیگر در صورت ثبت واژه محمد درون سلول F2 نتیجه به صورت شکل ذیل بوده

و همچنین در صورت ثبت حرف “ص” درون سلول F2 نتیجه به صورت ذیل می باشد.

همانطور که در شکل فوق مشاهده میشود کلیه اسامی که در آنها از حرف “ص” استفاده شده است در این حالت نمایش داده خواهد شد.

۳-   در گام بعدی باید از تب Formulas قسمت Define Name دستور Name Manager  را انتخاب نموده تا پنجره مربوط به آن مطابق با شکل ذیل باز شود.

۴-   در پنجره Name Manager بر روی دستور New کلیک کرده تا پنجره تعریف نام باز شود، سپس در قسمت Name این پنجره واژه “dropDown” را به عنوان نام وارد نموده و در قسمت Reffer To فرمول ذیل را وارد کرده .

=OFFSET(Sheet1!$D$1,,,MAX(Sheet1!$B:$B))

در این فرمول عبارت Sheet1 نام شیتی می باشد که اطلاعات اسامی در آن نگهداری شده است.

۵-   بر روی سلول F2 کلیک کرده و از تب Data  دستور Data Validation را برگزیده، سپس حالت Allow را بر روی List قرار داده و در قسمت Source کلیک کرده و عبارت Dropdown را مطابق شکل ذیل وارد نموده.

۶-   مطابق با شکل ذیل، در دستور Data Validation به تب Error Allert رفته و گزینه Show Error alert after invalid data intred  را غیر فعال نموده و پنجره را تایید کرده.

در این حالت در صورت ثبت حرف “ص” درون سلول F2 لیست مربوطه به صورت شکل ذیل نمایش داده خواهد شد:

و در صورت ثبت حرف “ک” لیست مربوطه به صورت شکل ذیل خواهد بود.