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

زمانی که شما از یک نرم افزار(مثلا اکسل) استفاده می کنید و کلی اطلاعات داخل اون ذخیره می کنید یکی از ضروری ترین نیاز های هر شما، داشتن فرم جستجو داخل اطلاعات ثبت شده هست.درواقع اگر نرم افزاری امکان جستجو نداشته باشد یکی ایراد بزرگ برای آون محسوب میشه.در این آموزش قصد داریم تا نحوه ایجاد باکس جستجو در اکسل را بصورت کامل برای شما آموزش دهیم.

در نرم افزار اکسل ابزار و توابع مختلفی برای جستجو وجود دارد که هرکدام در موارد مختلفی استفاده می شود :

  • ابزار Find
  • ابزار Advanced Filter که امکان جستجوی پیشرفته رو هم فراهم می کنه.
  • تابع VLOOKUP
  • تابع HLOOKUP
  • تابع FIND
  • تابع SEARCH
  • تابع MATCH
  • تابع INDEX
  • و…

ادغام اطلاعات چند سلول در یک سلول با تابع CONCATENATE :

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

فرم اطلاعات تماس مشتری در اکسل
شکل شماره 1 – شیت Data برای ذخیره اطلاعات تماس مشتری در اکسل

اما مشکلی که وجود داره این هست که ما تابعی مستقیمی نداریم که با تایپ در یک سلول، اون مقدار رو در کل جدول و در سلول های مختلف سرچ کنه و برای ما لیست کنه.برای همین ما باید از یکسری ترفندها در نوشتن توابع استفاده کنیم.برای همین بجای اینکه ما جست و جو رو در سلول های مختلف انجام دهیم، اطلاعات تمام سلول هارو در یک سلول جمع می کنیم یا به عبارتی به هم می چسبانیم تا عمل جست و جو فقط در یک سلول انجام بشه.تابع CONCATENATE این کار رو برای ما انجام میده.پس مثل شکل شماره 2، به جدولمون 2 تا ستون کمکی اضافه می کنیم.

 ایجاد ستون کمکی برای نوشتن تابع CONCATENATE
شکل شماره 2 – شیت Data – ایجاد ستون کمکی برای نوشتن تابع CONCATENATE

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

حتما بخوانید:   add-ins و همه چیز درمورد افزونه ها در اکسل

استفاده از تابع FIND برای جستجو بین عبارات سلول ها:

خب تا اینجا ما تونستیم اطلاعات رو در یک سلول ترکیب کنیم.حالا یک شیت جدید به نام Search ایجاد کنید و جدولی دقیقا مثل شیت Data ایجاد کنید با این تفاوت که ستون های کمکی و اطلاعات مربوط به مشتری رو حذف کنید و و فقط عنوان ستون ها باقی بمونه.حالا فرمول سلول G3 در شیت Data رو به شکل زیر تغییر بدین و به سلول های پایینی درگ کنید:

شیت Search برای نمایش نتایج جستجو
شکل شماره 3 – شیت Search برای نمایش نتایج جستجو

الان اگر شما به شیت Search برید و در سلول A1 عبارتی رو وارد کنید، در شیت Data و در ستون G اون ردیف هایی که عبارت موجود در سلول A1 هست براتون شماره ردیفش رو میندازه.ما اینجوی متوجه میشیم که کدام ردیف ها جزء نتیجه جستجوی ماست.

جستجو و گزارش مخاطبین در اکسل
شکل شماره 4 – نتیجه جستجو در شیت Data

همانطور که در شکل بالا می بینید، ما در شیت Search و در سلول A1 عبارت “ان” را تایپ کردیم و در شیت Data اون ردیف هایی که شامل عبارت “ان” هستند با شماره مشخص شد.اما مشکلی که وجود داره این هست که در ستون کمکی1 بین شماره گذاری ها سلول خالی وجود دارد و ما باید کاری کنیم که این شماره گذاره از اولین سلول شروع بشه.پس در ستون کمکی2 و در سلول H3 فرمول زیر رو تایپ کرده و به سلول های پایینی درگ کنید:

حتما بخوانید:   حذف سلول خالی محدوده در اکسل فقط با چند کلیک
استفاده از تابع SMALL برای ترتیب شماره گذاری
شکل شماره 5 – استفاده از تابع SMALL برای ترتیب شماره گذاری

خب کار ما درشیت Data تموم شد.حالا میریم به شیت Search تا اطلاعات سرچ شده را لیست کنیم.برای اینکار به شیت Search رفته و در سلول A3 فرمول زیر را نوشته و در تمام سلول ها درگ کنید:

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

همانطور که در فرم جستجو شماره 6 مشاهده می کنید زمانی که در سلول A1 یک عبارت را تایپ می کنیم و Enter را می زنیم میره در شیت Data و در بین تمام سلول های محدوده، ردیف هایی که عبارت “ان” داخلش موجود هست رو برامون لیست میکنه.

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

ایرادی که به فرم جستجو جستجوی بالا داره این هست که ابتدا عبارت جستجو باید در سلول A1 وارد شده و بعد از زدن Enter شروع به سرچ در بین سلول ها میکنه.اما ما میخواهیم همزمان با تایپ کردن، عمل جستجو هم انجام بشه.برای اینکار باید تب Developer شما فعال باشد.اکنون مطابق شکل زیر یک TextBox در بالای جدول ایجاد کنید.

ایجاد باکس جستجو از تب Developer
شکل شماره 7 – ایجاد باکس جستجو از تب Developer

روی TextBox کلیک راست کرده و گزینه Properties را بزنید تا پنجره Properties باز شود.مقابل گزینه LinkedCell عبارت A1 را بنویسید تا به سلول A1 لینک شود.

لینک کردن TextBox به سلول شیت
شکل شماره 8 – لینک کردن TextBox به سلول شیت

اکنون در تب Developer با یکبار کلیک روی گزینه DesignMode، حالت طراحی را غیرفعال کنید.

حتما بخوانید:   تبدیل عدد به حروف فارسی،انگلیسی و روسی در اکسل + ABH

خب دوستان کار ما تموم شد.الان زمانی که عبارتی داخل باکس جستجو TextBox وارد کنید، عمل جستجو بصورت هوشمند و آنی اتفاق می افته.برای بهتر شدن نمای فرم جستجو می توانید رنگ فونت سلول A1 رو با رنگ زمینه ش یکی کنید تا دیده نشه.

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

اشتراک گذاری:
مطالب زیر را حتما بخوانید

2 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

پاسخی بگذارید