باکس جستجو در اکسل بصورت پیشرفته با توابع جستجو
زمانی که شما از یک نرم افزار(مثلا اکسل) استفاده می کنید و کلی اطلاعات داخل اون ذخیره می کنید یکی از ضروری ترین نیاز های هر شما، داشتن فرم جستجو داخل اطلاعات ثبت شده هست.درواقع اگر نرم افزاری امکان جستجو نداشته باشد یکی ایراد بزرگ برای آون محسوب میشه.در این آموزش قصد داریم تا نحوه ایجاد باکس جستجو در اکسل را بصورت کامل برای شما آموزش دهیم.
در نرم افزار اکسل ابزار و توابع مختلفی برای جستجو وجود دارد که هرکدام در موارد مختلفی استفاده می شود :
- ابزار Find
- ابزار Advanced Filter که امکان جستجوی پیشرفته رو هم فراهم می کنه.
- تابع VLOOKUP
- تابع HLOOKUP
- تابع FIND
- تابع SEARCH
- تابع MATCH
- تابع INDEX
- و…
ادغام اطلاعات چند سلول در یک سلول با تابع CONCATENATE :
اما ما اینجا قصد داریم تا با فرمول نویسی پیشرفته یک باکس جستجوی حرفه ای و لحظه ای برای اطلاعات خودمون ایجاد کنیم.قبل از شروع آموزش ساخت جستجوی هوشمند در اکسل ما دیتای زیر رو آماده کردیم و می خواهیم برای این فرم یک باکس جستجو ایجاد کنیم طوری که با تایپ کردن قسمتی از عبارت در یک باکس یا سلول،در کل دیتاها اون مورد رو سرچ کنه و ردیف هایی ک شامل اون عبارت هستند را برای ما لیست کنه.
اما مشکلی که وجود داره این هست که ما تابعی مستقیمی نداریم که با تایپ در یک سلول، اون مقدار رو در کل جدول و در سلول های مختلف سرچ کنه و برای ما لیست کنه.برای همین ما باید از یکسری ترفندها در نوشتن توابع استفاده کنیم.برای همین بجای اینکه ما جست و جو رو در سلول های مختلف انجام دهیم، اطلاعات تمام سلول هارو در یک سلول جمع می کنیم یا به عبارتی به هم می چسبانیم تا عمل جست و جو فقط در یک سلول انجام بشه.تابع CONCATENATE این کار رو برای ما انجام میده.پس مثل شکل شماره ۲، به جدولمون ۲ تا ستون کمکی اضافه می کنیم.
اکنون در سلول G3 تابع زیر رو نوشته وبه سلول های پایینی درگ کنید.خواهید دید که تمام اطلاعات موجود در یک ردیف، داخل یک سلول قرار می گیرند.
1 | =CONCATENATE(A3,B3,C3,D3,E3,F3) |
استفاده از تابع FIND برای جستجو بین عبارات سلول ها:
خب تا اینجا ما تونستیم اطلاعات رو در یک سلول ترکیب کنیم.حالا یک شیت جدید به نام Search ایجاد کنید و جدولی دقیقا مثل شیت Data ایجاد کنید با این تفاوت که ستون های کمکی و اطلاعات مربوط به مشتری رو حذف کنید و و فقط عنوان ستون ها باقی بمونه.حالا فرمول سلول G3 در شیت Data رو به شکل زیر تغییر بدین و به سلول های پایینی درگ کنید:
1 | =IFERROR(IF(FIND(Search!$A$1,CONCATENATE(A3,B3,C3,D3,E3,F3))>0,ROW()-2,""),"") |
الان اگر شما به شیت Search برید و در سلول A1 عبارتی رو وارد کنید، در شیت Data و در ستون G اون ردیف هایی که عبارت موجود در سلول A1 هست براتون شماره ردیفش رو میندازه.ما اینجوی متوجه میشیم که کدام ردیف ها جزء نتیجه جستجوی ماست.
همانطور که در شکل بالا می بینید، ما در شیت Search و در سلول A1 عبارت “ان” را تایپ کردیم و در شیت Data اون ردیف هایی که شامل عبارت “ان” هستند با شماره مشخص شد.اما مشکلی که وجود داره این هست که در ستون کمکی۱ بین شماره گذاری ها سلول خالی وجود دارد و ما باید کاری کنیم که این شماره گذاره از اولین سلول شروع بشه.پس در ستون کمکی۲ و در سلول H3 فرمول زیر رو تایپ کرده و به سلول های پایینی درگ کنید:
1 | =IFERROR(SMALL($G$3:$G$7,ROWS($G$3:G3)),"") |
خب کار ما درشیت Data تموم شد.حالا میریم به شیت Search تا اطلاعات سرچ شده را لیست کنیم.برای اینکار به شیت Search رفته و در سلول A3 فرمول زیر را نوشته و در تمام سلول ها درگ کنید:
1 | =IFERROR(INDEX('Data Base'!$A$3:$H$7,'Data Base'!$H3,COLUMN()),"") |
همانطور که در فرم جستجو شماره ۶ مشاهده می کنید زمانی که در سلول A1 یک عبارت را تایپ می کنیم و Enter را می زنیم میره در شیت Data و در بین تمام سلول های محدوده، ردیف هایی که عبارت “ان” داخلش موجود هست رو برامون لیست میکنه.
ایجاد باکس جستجو در اکسل بصورت لحظه ای:
ایرادی که به فرم جستجو جستجوی بالا داره این هست که ابتدا عبارت جستجو باید در سلول A1 وارد شده و بعد از زدن Enter شروع به سرچ در بین سلول ها میکنه.اما ما میخواهیم همزمان با تایپ کردن، عمل جستجو هم انجام بشه.برای اینکار باید تب Developer شما فعال باشد.اکنون مطابق شکل زیر یک TextBox در بالای جدول ایجاد کنید.
روی TextBox کلیک راست کرده و گزینه Properties را بزنید تا پنجره Properties باز شود.مقابل گزینه LinkedCell عبارت A1 را بنویسید تا به سلول A1 لینک شود.
اکنون در تب Developer با یکبار کلیک روی گزینه DesignMode، حالت طراحی را غیرفعال کنید.
خب دوستان کار ما تموم شد.الان زمانی که عبارتی داخل باکس جستجو TextBox وارد کنید، عمل جستجو بصورت هوشمند و آنی اتفاق می افته.برای بهتر شدن نمای فرم جستجو می توانید رنگ فونت سلول A1 رو با رنگ زمینه ش یکی کنید تا دیده نشه.
