ساخت باکس جستجو در اکسل (ساده ترین روش)

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

برای ایجاد یک باکس جستجو در اکسل از ویژگی Conditional Formatting (قالب بندی شرطی) استفاده می شود، این ابزار قرار گرفته در تب Home. یک قانون جدید براساس فرمول مورد نظر برای قالب بندی سلول ها ایجاد می کند.

ویدیوی آموزشی

لینک دانلود ویدیو 

اگر ویدیو کافی نبود به خواندن ادامه دهید:

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

نحوه ساخت باکس جستجو در اکسل

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

لیست همه دروس کاربردی اکسل ما

کاربرد باکس جستجو در اکسل

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

داده های زیر را در نظر بگیرید:

برای ساخت یک قانون جدید با قالب بندی شرطی (براساس داده های بالا) مراحل زیر را دنبال کنید:

1- سلول مورد نظر خود برای باکس جستجو را در نظر بگیرید. در این مثال سلول G1 به عنوان باکس جستجو انتخاب شده و هر مقداری که در آن نوشته شود، در صفحه مورد جستجو قرار می گیرد. می توانید سلول انتخاب شده را برجسته کنید تا از سلول های دیگر متمایز شود.

2- محدوده داده های مورد نظر برای جستجوی مقدار در آن را انتخاب کنید. در این مثال، محدوده A2:D27 را انتخاب می کنیم.

(همانطور که در تصویر زیر نشان داده شده است).

3- پس از انتخاب محدوده داده ها روی گزینه Conditional Formatting در تب Home کلیک کرده و گزینه New Rule را از منوی آن انتخاب کنید.

4- در پنجره بعدی در قسمت Select a Rule Type روی گزینه “Use a formula to determine which cells to format” کلیک کنید.

سپس، فرمول زیر را در کادر متنی پایین آن وارد کنید.

=SEARCH($G$1,$A2&$B2&$C2&$D2)

5- پس از نوشتن فرمول روی دکمه Format در زیر آن کلیک کنید و یک رنگ دلخواه برای برجسته کردن مقادیر نتیجه جستجو انتخاب کنید.

برای این کار در پنجره باز شده روی تب Fill کلیک کرده و رنگ را انتخاب می کنیم. در کادر Preview پیش نمایش رنگ انتخاب شده قابل مشاهده است.

6- روی OK کلیک کنید. همانطور که می بینید همه محدوده داده انتخاب شده با رنگ انتخابی برجسته شده است.

7- حالا می توانید کلمه کلیدی مورد نظر خود را جستجو کنید. خواهید دید که همه ردیف های شامل کلمه جستجو شده با رنگ انتخاب شده برجسته می شوند.

در اینجا کلمه Scooter را جستجو می کنیم. همه ردیف های شامل کلمه Scooter برجسته می شوند.

باکس جستجو در اکسل فرآیند جستجوی هر مقداری را ساده می کند. به عنوان مثال می توانیم بعد از برجسته شدن ردیف های حاوی Scooter به راحتی یک فیلتر روی رنگ اعمال کنیم و حتی نتایج جستجو را بیشتر اصلاح کنیم.

در ادامه مفهوم پارامترهای استفاده شده در فرمول Search و نحوه کار فرمول را توضیح خواهیم داد.

فرمول باکس جستجو در اکسل

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

=SEARCH($G$1, $A2&$B2&$C2&$D2)

این فرمول حاوی تابع SEARCH است، این تابع دو آرگومان ورودی دارد:

• آرگومان اول، متنی است که می خواهیم آن را جستجو کنیم.
• آرگومان دوم، متنی است که مقدار آرگومان اول در آن جستجو می شود.

بنابراین پارامترها در فرمول بدین صورت تعریف می شوند:

1- $G$1

=SEARCH($G$1, $A2&$B2&$C2&$D2)

این پارامتر، مکان سلول حاوی متن مورد جستجو است. در اینجا پارامتر $G$1 سلولی است که قاون قاالب بندی شرطی در آن اعمال شده است، همان سلول G1 است که مقدار مورد جستجو در آن نوشته می شود. می توانیم بعد از نوشتن )SEARCH = شماره سلول را تایپ کنیم یا اینکه به راحتی روی سلول حاوی باکس جستجو کلیک کنیم.

2- $A2&

=SEARCH($G$1,$A2&$B2&$C2&$D2)

آرگومان دوم در تابع مقدار (یا مقادیری) است که آرگومان اول در آن (ها) جستجو می شود.

نکته: در این فرمول مقدار مورد جستجو در بیش از یک مقدار ها جستجو می شود، چراکه مقدار باکس جستجو باید در هر ردیف بررسی شود که در اینجا یک ردیف حاوی 4 مقدار است. (یعنی سلول های A2، B2، C2 و D2)

نکته: فرمول قانون در قالب بندی شرطی برای اولین ردیف در محدوده داده ها نوشته می شود سپس اکسل به طور خودکار آن را روی بقیه ردیف ها اعمال می کند.

&A2 را بررسی می کنیم، بقیه نیز مشابه همین می باشند. در پارامتر A2، حرف “A” نشان دهنده شماره ستون و “2” شماره سطر سلول را مشخص می کند. بنابراین “A2” یعنی اینکه می خواهیم مقدار موررد جستجو را در ستون “A” از ردیف 2 جستجو کنیم. علامت $ قبل از “A” تضمین می کند که مکان جستجو ثابت می ماند حتی اگر مکان باکس جستجو تغییر داده شود.

برای اضافه کردن تعداد مکان های بیشتر از کاراکتر “&” استفاده می کنیم. در اینجا “&” بعد از “A2” یعنی اینکه می خواهیم علاوه بر سلول A2 سلول های بعدی ردیف را نیز اضافه کنیم. همانطور که در فرمول زیر مشخص شده اند:

=SEARCH($G$1,$A2&$B2&$C2&$D2)

در اینجا نیز فرمول قانون برای جستجو در ردیف اول در محدوده نوشته شده است (A2&$B2&$C2&$D2 ردیف اول در محدوده انتخاب شده است) سپس اکسل آن را به ردیف دوم از محدوده SEARCH($G$1,$A3&$B3&$C3&$D3)= و به همین ترتیب بقیه ردیف ها اعمال می کند.

کاربرد باکس جستجو در اکسل

تا اینجا با نحوه پیاده سازی باکس جستجو در اکسل آشنا شدیم،حالا می خواهیم همان مثال قبلی را توسعه دهیم و نتیجه را ببینیم. در مثال قبل کلمه “Scooter” را در باکس جستجو نوشته و آن را در صفحه جستجو کردیم، در نتیجه نیز همه ردیف های حاوی کلمه “Scooter” برجسته شدند. حالا اگر بخواهیم سلول متناظر از ستون Brand Name را برای مقادیر “Scooter” به دست آوریم به شرطی که مقدار سلول در ستون Year of Sale برابر با 2008 باشد، باید چکار کنیم؟

بسیار ساده است. به راحتی یک فیلتر روی ستون Year of sale اعمال می کنیم تا فقط ردیف های حاوی مقدار 2008 نمایش داده شود. همانطور که در تصویر زیر می بینید، ابتدا روی نام ستون کلیک می کنیم سپس از فیلترهای موجود مقدار 2008 را انتخاب کرده و روی OK کلیک می کنیم.

در نتیجه، دو ردیف حاوی مقدار”Scooter” وجود دارد که مقدار سلول متناظر آنها در ستون Year of sale برابر با 2008 است، حالا می توانیم مقدار مورد نیاز خود را در ستون Brand Name پیدا کنیم.

به این ترتیب یک باکس جستجو در اکسل می تواند در ساده سازی فرآیند جستجو به ما کمک کند.

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

نکاتی برای یاد آوری

• مطمئن شوید که فرمول را در پنجره قالب بندی شرطی به درستی وارد می کنید.
• برای جلوگیری از حرکت سلول‌ها از کاراکتر $ استفاده کنید مشابه آنچه که در فرمول بالا نشان داده شد.
• برای اضافه کردن تعداد موارد (ستون های) بیشتر برای جستجوی مقدار در آنها از کاراکتر & استفاده می شود. مطمئن شوید که یک & در انتهای فرمول اضافه نگذاشته باشید.
• می توانیم از یک فیلتر برای رسیدن به هدف باکس جستجو در اکسل استفاده کنیم اما ممکنه لازم باشد که فیلترها را به چندین ستون اعمال کنیم تا به هدف خود برسیم.
• گرچه هر دو ویژگی باکس جستجو و فیلترها در اکسل برای به دست آوردن نتایج بر اساس شرایط مختلف استفاده می شوند اما نباید آنها را به جای یکدیگر استفاده کنیم زیرا اهداف منحصر به فرد و شیوه های مختلفی برای حل مسئله دارند. برای بهبود عملکرد فیلتر می توان از باکس جستجو در اکسل استفاده کرد.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه