ساخت سرچ باکس در اکسل با ۴ روش – به زبان ساده

وقتی با حجم بسیار بالایی از داده مواجه هستیم، وجود یک سرچ باکس در اکسل می‌تواند مدیریت این داده‌ها را به مراتب آسان‌تر کند. اکسل به طور پیش فرض سرچ باکس ندارد اما خوشبختانه می‌توان با چند روش در اکسل سرچ باکس ایجاد کرد. در این مطلب از مجله فرادرس می‌آموزیم چگونه سرچ باکسی ایجاد کنیم که به جستجو در یک ستون یا چند ستون بپردازد. در انتهای مطلب نیز برای کاربران حرفه ای اکسل روش‌هایی برای شخصی‌سازی جستجو با استفاده از کدنویسی «VBA» ارائه می‌دهیم.

سرچ باکس در اکسل با یک ستون جستجو

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

فیلم آموزش اکسل Microsoft Excel 2021 در فرادرس

کلیک کنید

تنظیمات صفحه گسترده اکسل

برای این کار ابتدا باید صفحه داده‌های خود را مرتب کنیم. در این مثال داده خام در یک صفحه جدا به عنوان یک جدول در اکسل تعریف شده است که آن را «DataTable» نام‌گذاری کرده‌ایم. در تصویر زیر در سربرگ «Table Design» این نام گذاری را انجام داده‌ایم.

فرمول سرستون (هدر) جدول

نیاز است ستون‌های این جدول نیز نام‌گذاری شوند. این کار را می‌توانید با تایپ کردن نامی برای ستون‌ها یا استفاده از نام‌های موجود در سرستون‌های جدول اصلی انجام دهید. همچنین، اگر با نحوه فرمول‌نویسی در اکسل آشنا باشید، در این مثال با استفاده از این فرمول در تصویر زیر اسامی ستون‌ها را وارد کرده‌ایم.

=DataTable[#Headers]

اضافه کردن دکمه رادیویی

برای اضافه کردن گزینه انتخاب ستون جستجو، نیاز است سربرگ «Developer» بروید سپس در بخش «Control»، منوی «Insert» را باز کنید و روی آیکن‌ نشان داده شده در تصویر زیر (آیکن‌ دکمه رادیویی) کلیک کنید. اگر سربرگ Developer در اکسل شما فعال نیست، می‌توانید آن را در File > Options > Customize Ribbon   فعال کنید. بعد از انتخاب آیکن‌، نشانگر شما تغییر شکل می‌دهد و می‌توانید آیکن‌ را هر جایی از صفحه که مایل باشید رسم کنید.

حال متن Label آیکن را تغییر دهید تا با عناوین ستون‌های جستجو هماهنگ باشد. ستون‌های جدول و آیکن‌‌های انتخاب باید به ترتیب یکسانی قرار گرفته باشند.

در این جا نیاز دارید آیکن‌های انتخاب را به سلولی ارتباط دهید. برای این کار روی یکی از آیکن‌ها کلیک راست کنید، سپس گزینه «Format Control» را انتخاب کنید. در آخر بخش «Cell Link» را پر کنید و Ok را بزنید. باقی آیکن‌ها نیز به سلول یکسانی ارتباط داده خواهند شد، بدون اینکه نیاز باشد این مراحل را تکرار کنید.

ایجاد فرمول اصلی سرچ باکس

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

• تابع «Filter»: این تابع، شرطی را در ستون جستجو می‌کند و ردیفی را برمی‌گرداند که شرط اعمالی را داشته باشد. آرگومان سومی نیز برای این تابع وجود دارد که اگر نتیجه‌ای برای جستجو یافت نشد مقدار دلخواه شما را بازگرداند.
• تابع «IsNumber/Search»: از جایی که تابع فیلتر امکان جستجوی وایلد کارت را ندارد، می‌توانیم از این توابع استفاده استفاده کنیم.
• تابع «Index»: با استفاده از این تابع می‌توانیم تعیین کنیم کدام ستون برای جستجو انتخاب شود.

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

=FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,$I$2))),"No Results!")

در تصویر زیر نتیجه استفاده از این فرمول در مثال ذکر شده را مشاهده می‌کنید:

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

در این بخش ایجاد سرچ باکسی را آموزش می‌دهیم که در چند ستون جستجو کند. مشابه مثال‌های قبلی جدول را برای ساخت سرچ باکس آماده می‌کنیم.

تنظیمات صفحه گسترده اکسل

در این مثال، مشابه بخش قبلی، داده خام در یک صفحه جدا به عنوان یک جدول در اکسل تعریف شده است که آن را «DataTable» نام‌گذاری کرده‌ایم.

فرمول سرستون (هدر) جدول

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

ایجاد فرمول اصلی سرچ باکس

در مرحله بعدی نیاز است فرمولی برای سرچ باکس بنویسید. این فرمول می‌تواند یک فرمول تک‌آرایه باشد که مشابه بخش قبلی از سه تابع تشکیل شده است، اما این بار از Loop روی تابع Filter نیز استفاده می‌شود. در ادامه این توابع را شرح می‌دهیم:

• تابع Filter  : این تابع، شرطی را در ستون جستجو می‌کند و ردیفی را برمی‌گرداند که شرط اعمالی را داشته باشد. آرگومان سومی نیز برای این تابع وجود دارد که اگر نتیجه‌ای برای جستجو یافت نشد مقدار دلخواه شما را بازگرداند.
• تابع IsNumber/Search  : از جایی که تابع فیلتر امکان جستجوی وایلد کارت را ندارد، می‌توانیم از این توابع استفاده استفاده کنیم.
• تابع Index  : با استفاده از این تابع می‌توانیم تعیین کنیم کدام ستون برای جستجو انتخاب شود.
• Loop در ستون: برای تکرار جستجو در یک ستون، در واقع نیاز است تابع فیلتر را چندین بار اجرا کنیم. تابع فیلتر آرگومان شرطی  If_Empty   دارد، با استفاده از این آرگومان، اگر در یک ستون مقدار مورد نظر یافت نشد، می‌توانیم به سادگی تابع فیلتر دیگری را در ستون جدیدی اجرا کنیم.

در این فرمول تابع فیلتر ۵ بار تکرار شده است، چراکه در مثال، ۵ ستون داده داریم. این ۵ فرمول دقیقاً مشابه یکدیگر هستند و تنها آرگومان سوم تابع Index متفاوت است تا به شماره ستون جستجو در جدول اشاره کند. طبق این فرمول اگر در نهایت مقدار جستجو یافت نشد، تابع عبارت «!No Results» را برمی‌گرداند.

=FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,1))),
FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,2))),
FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,3))),
FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,4))),
FILTER(DataTable,ISNUMBER(SEARCH($C$2,INDEX(DataTable,0,5))),"No Results!")))))

در تصویر زیر شیوه اعمال این فرمول را مشاهده می‌کنید.

ایجاد سرچ باکس فعال حین تایپ

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

تنظیمات صفحه گسترده اکسل

در این مثال، مشابه بخش قبلی، داده خام در یک صفحه جدا به عنوان یک جدول در اکسل تعریف شده است که آن را «DataTable» نام‌گذاری کرده‌ایم.

نامگذاری سرستون (هدر) جدول

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

اضافه کردن جعبه متن ActiveX

در مرحله بعدی نیاز است سربرگ Developer بروید، سپس در بخش Control، منوی Insert را باز کنید و در «ActiveX» روی آیکن «Text Box» کلیک کنید. (اگر سربرگ Developer در اکسل شما فعال نیست، می‌توانید آن را در File > Options > Customize Ribbon   فعال کنید.) بعد از انتخاب Text Box نشانگر شما تغییر شکل می‌دهد و می‌توانید Text Box را هر جایی از صفحه رسم کنید.

بعد از رسم کادر Text Box (به عنوان کادر جستجو)، کادر را انتخاب کنید و «Properties» را در بخش Control سربرگ Developer باز کنید. در پنجره باز شده تغییرات را مطابق تصویر زیر وارد کنید. طبق این تنظیمات فیلد جستجو به سلول «I2» لینک می‌شود.

ایجاد فرمول اصلی سرچ باکس

در این بخش نیز از فرمولی دقیقا مشابه بخش قبلی استفاده کنید، تنها با این تفاوت که این بار فرمول را در سلول «I2» وارد کنید.

=FILTER(DataTable,ISNUMBER(SEARCH($I$2,INDEX(DataTable,0,1))),
FILTER(DataTable,ISNUMBER(SEARCH($I$2,INDEX(DataTable,0,2))),
FILTER(DataTable,ISNUMBER(SEARCH($I$2,INDEX(DataTable,0,3))),
FILTER(DataTable,ISNUMBER(SEARCH($I$2,INDEX(DataTable,0,4))),
FILTER(DataTable,ISNUMBER(SEARCH($I$2,INDEX(DataTable,0,5))),"No Results!")))))

در تصویر زیر شیوه اعمال این فرمول را مشاهده می‌کنید.

ایجاد سرچ باکس با استفاده از VBA در اکسل

«VBA»  فضای برنامه نویسی در برنامه‌های مایکروسافت (Visual Basic for Applications) است که برای خودکارسازی کارها شخصی‌سازی فایل‌های شما استفاده می‌شود. در این مثال با استفاده از VBA کادر جستجوی خود را شخصی سازی می‌کنیم. دقت داشته باشید این روش ساخت سرچ باکس برای کاربران حرفه ای مایکروسافت آفیس مناست است که با کدنویسی و استفاده از VBA آشنایی کافی داشته باشند. در انتهای این بخش کدهای مربوط قرار گرفتند. ابتدا به شرح آنچه در کد وارد شده است، می‌پردازیم.

فیلم آموزش برنامه نویسی VBA برای خودکار کردن برنامه های Office در فرادرس

کلیک کنید

امکان انتخاب ستون جستجو

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

برای اضافه کردن گزینه انتخاب ستون جستجو، نیاز است سربرگ Developer بروید سپس در بخش Control، منوی Insert را باز کنید و روی آیکن‌ نشان داده شده در تصویر زیر (آیکن‌ دکمه رادیویی) کلیک کنید. اگر سربرگ Developer در اکسل شما فعال نیست، می‌توانید آن را در File > Options > Customize Ribbon   فعال کنید. بعد از انتخاب آیکن‌ نشانگر شما تغییر شکل می‌دهد و می‌توانید آیکن‌ را هر جایی از صفحه که مایل باشید رسم کنید. دکمه‌ها را به ترتیب تصویر مرتب کرده و نام‌گذاری کنید در انتهای این مطلب کدی برای استفاده از این دکمه‌ها وجود دارد.

نام‌گذاری المان‌ها

مهمترین بخش در استفاده VBA نام‌گذاری صحیح المان‌هاست تا از این نامه‌ها در کد نویسی استفاده شود. در این مثال نیاز داریم برای سرچ باکس نامی انتخاب کنیم. برای این کار روی سرچ باکس کلیک کنید و در بخشی که در کنار نوار فرمول قرار دارد نام باکس را وارد کنید. ابتدا نام پیش فرض «Text Box 1» را خواهید دید. می‌توانید این نام را به نامی دلخواه تغییر دهید. بعد از تغییر نام حتماً دکمه Enter را بزنید، در غیر این صورت تغییر نام اعمال نخواهد شد.

در این مثال نیازی به نام‌گذاری المان‌های گزینه جستجو وجود ندارد. تنها باید دقت کنید که نام نوشته شده کنار هر آیکن‌ دقیقاً مشابه نام ستون‌ها باشد. این نام‌گذاری بسیار اهمیت دارد چرا که کدی که در ادامه آورده‌ایم، جستجو را بر اساس این نام‌گذاری‌ها انجام می‌دهد.

جستجوی حدودی متن

در کد زیر، با اضافه کردن دو ستاره قبل و بعد از عبارت جستجو، جستجو به شکل انعطاف‌پذیرتری انجام می‌شود. اگر کاربر تنها سه حرف «whi» را تایپ کند، اکسل هر سلولی را که شامل این سه کاراکتر باشد را نمایش خواهد داد.

برای استفاده از این کد نیاز است داده‌های خود را به عنوان متغیر «DataRange» تعریف کنید. نام کادر جستجو نیز باید در مرجع «Shapes» تغییر کند. اگر داده شما از ستون A شروع نمی‌شود، نیاز است متغیر «myField» را در کد تغییر دهید تا مطمئن شوید جستجو در ستون‌های درستی اتفاق خواهد افتاد. در ادامه کد مربوط به توضیحاتی که در این بخش داریم را مشاهده می‌کنید.

1Sub SearchBox()
2'PURPOSE: Filter Data on User-Determined Column & Text
3'SOURCE: www.TheSpreadsheetGuru.com
4
5Dim myButton As OptionButton
6Dim MyVal As Long
7Dim ButtonName As String
8Dim sht As Worksheet
9Dim myField As Long
10Dim DataRange As Range
11Dim mySearch As Variant
12   
13'Load Sheet into A Variable
14  Set sht = ActiveSheet
15
16'Unfilter Data (if necessary)
17  On Error Resume Next
18    sht.ShowAllData
19  On Error GoTo 0
20   
21'Filtered Data Range (include column heading cells)
22  Set DataRange = sht.Range("A4:E31") 'Cell Range
23  'Set DataRange = sht.ListObjects("Table1").Range 'Table
24
25'Retrieve User's Search Input
26  mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
27  'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
28  'mySearch = sht.Range("A1").Value 'Cell Input
29
30'Loop Through Option Buttons
31  For Each myButton In ActiveSheet.OptionButtons
32      If myButton.Value = 1 Then
33        ButtonName = myButton.Text
34        Exit For
35      End If
36  Next myButton
37  
38'Determine Filter Field
39  On Error GoTo HeadingNotFound
40    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
41  On Error GoTo 0
42  
43'Filter Data
44  DataRange.AutoFilter _
45    Field:=myField, _
46    Criteria1:="=*" & mySearch & "*", _
47    Operator:=xlAnd
48  
49'Clear Search Field
50  sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
51  'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
52  'sht.Range("A1").Value = "" 'Cell Input
53
54Exit Sub
55
56'ERROR HANDLERS
57HeadingNotFound:
58  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
59    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
60    
61End Sub

جستجوی متن و عدد

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

1Sub SearchBox()
2'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value
3'SOURCE: www.TheSpreadsheetGuru.com
4
5Dim myButton As OptionButton
6Dim SearchString As String
7Dim ButtonName As String
8Dim sht As Worksheet
9Dim myField As Long
10Dim DataRange As Range
11Dim mySearch As Variant
12
13'Load Sheet into A Variable
14  Set sht = ActiveSheet
15
16'Unfilter Data (if necessary)
17  On Error Resume Next
18    sht.ShowAllData
19  On Error GoTo 0
20  
21'Filtered Data Range (include column heading cells)
22  Set DataRange = sht.Range("A4:E31") 'Cell Range
23  'Set DataRange = sht.ListObjects("Table1").Range 'Table
24
25'Retrieve User's Search Input
26  mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
27  'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
28  'mySearch = sht.Range("A1").Value 'Cell Input
29   
30'Determine if user is searching for number or text
31  If IsNumeric(mySearch) = True Then
32    SearchString = "=" & mySearch
33  Else
34    SearchString = "=*" & mySearch & "*"
35  End If
36    
37'Loop Through Option Buttons
38  For Each myButton In sht.OptionButtons
39    If myButton.Value = 1 Then
40      ButtonName = myButton.Text
41      Exit For
42    End If
43  Next myButton
44   
45'Determine Filter Field
46  On Error GoTo HeadingNotFound
47    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
48  On Error GoTo 0
49   
50'Filter Data
51  DataRange.AutoFilter _
52    Field:=myField, _
53    Criteria1:=SearchString, _
54    Operator:=xlAnd
55  
56'Clear Search Field
57  sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
58  'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
59  'sht.Range("A1").Value = "" 'Cell Input
60  
61Exit Sub
62
63'ERROR HANDLERS
64HeadingNotFound:
65  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
66    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
67  
68End Sub

ارتباط دادن کد VBA با دکمه جستجو

با اضافه کردن کد VBA نیاز دارید دکمه جستجوی خود را تبدیل به یک «Trigger» برای اجرای کد کنید.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

روی دکمه جستجو کلیک راست کنید و گزینه «Assign Macro» را انتخاب کنید. در پنجره باز شده نام مربوط را انتخاب کنید. این نام در این مثال «SearchBox» است. بعد از زدن Ok با هر بار روی دکمه Search، کد اجرا خواهد شد.

ساخت دکمه پاک کردن جستجو

برای ساخت دکمه پاک کردن جستجو تنها کافی‌ست دکمه جدیدی برای پاک کردن جستجو ایجاد کنید و این دکمه را مشابه مراحلی که در بخش قبل دکمه جستجو گفتیم این بار به «ClearFilter» ارتباط دهید. چند خط زیر نیز انتهای برنامه اضافه خواهد شد.

1Sub ClearFilter()
2'PURPOSE: Clear all filter rules
3
4'Clear filters on ActiveSheet
5  On Error Resume Next
6    ActiveSheet.ShowAllData
7  On Error GoTo 0
8  
9End Sub

در تصویر زیر نتیجه اعمال این کد را می‌بینید.