آموزش تابع Find و Search در اکسل ! معرفی تابع تابع Search و Find در Excel

تابع FIND و SEARCH در اکسل

شاید جستجو در یک لیست با استفاده از تابع Vlookup یا Hlookup قابل اجرا باشد، ولی اگر عبارتی که به دنبال آن هستید در بین یک رشته متنی قرار گرفته باشد، جستجو با این توابع کارساز نیست. بنابراین باید بتوانیم براساس روش یا تابعی خاص، محتویات یک رشته متنی را بررسی کرده و عبارت دلخواه را در آن پیدا و جدا کنیم. به همین دلیل به بررسی دو تابع FIND و SEARCH در اکسل خواهیم پرداخت.

از میان توابع گروه متنی، که از طریق برگه Formulas، بخش Function Library و انتخاب گزینه Text، قابل دسترس هستند، دو تابع FIND و SEARCH در اکسل از محبوبیت زیادی برخوردارند. به کمک این توابع می‌توانیم موقعیت یک عبارت را در یک رشته متنی مشخص کنیم. برای مثال اگر به دنبال عبارت “ARMAN” در رشته متنی “Excel Tutorial by ARMAN” هستیم (که در یک سلول نوشته شده)، هر یک از توابع FIND و SEARCH در اکسل را می‌توانیم به کار گرفته و موقعیت یا محل قرارگیری اولین حرف عبارت “ARMAN” را در رشته متنی مشخص کنیم. با توجه مثال گفته شده، این موقعیت برابر با نوزدهمین حرف خواهد بود. به یاد داشته باشید که فاصله‌های خالی (Space) نیز حرف محسوب شده و هنگام تعیین موقعیت شمرده می‌شود.

نکته: هر چند به نظر می‌رسد که تابع FIND و SEARCH یکسان عمل می‌کنند، ولی تابع FIND به حروف بزرگ و کوچک لاتین حساس (Case Sensitive) بوده و امکان اجازه استفاده از حروف جایگزین (Wildcard) مانند علامت * , ? را نمی‌دهد. در عوض تابع SEARCH این محدودیت‌ها را ندارد. این موضوعات را در ادامه مورد بررسی قرار خواهیم داد.

درمورد کاربرد متغیر ها در ماکرو نویسی اکسل بخوانید.

تابع FIND در اکسل

همانطور که گفته شد، تابع FIND موقعیت یک عبارت یا حرف را در یک رشته متنی که در یک سلول نوشته شده، نشان می‌دهد. شکل دستوری این تابع به صورت زیر است.

FIND( find_text, within_text, [start_num] )

مشخص است که هر دو پارامتر اول یعنی find_text و within_text، اجباری هستند ولی پارامتر start_num یک پارامتر اختیاری است. به یاد داشته باشید که در راهنمای اکسل، برای نمایش پارامتر یا پارامترهایی اختیاری از علامت [  ] استفاده می‌شود و پارامترهای اجباری معمولا به صورت پر رنگ (Bold) در راهنمای تابع نشان داده می‌شوند. در ادامه این بخش، در مورد نقش هر یک از این پارامترها در نحوه محاسبه تابع FIND صحبت و آن‌ها را معرفی خواهیم کرد.

• پارامتر Find_text: همانطور که از اسم این پارامتر مشخص است، متن مورد جستجو را تعیین می‌کند. به یاد داشته باشید که مقدار این پارامتر باید متنی باشد. اگر می‌خواهید به جای ذکر آدرس سلولی که متن در آن قرار دارد، از خود عبارت متنی استفاده کنید، حروف یا کلمات را داخل علامت ” ” قرار دهید تا اکسل متوجه متنی بودن آن بشود.
• پارامتر Within_text: رشته متنی یا آدرس سلولی که باید جستجو در آن صورت گیرد نیز در این پارامتر نوشته می‌شود. به این ترتیب عبارتی که در پارامتر Find_text نوشته شده در پارامتر Within_text جستجو شده و موقعیت آن براساس تعداد حروف از چپ به راست، به عنوان خروجی تابع، ظاهر می‌شود.
• پارامتر Start_num: این پارامتر اختیاری بوده و مشخص می‌کند که جستجو باید از چندمین حرف Within_text آغاز شود. بطور پیش‌فرض مقدار این پارامتر برابر با ۱ است. به این معنی که محل شروع جستجو از اولین حرف پارامتر Within_text است. به این ترتیب اگر مقدار ۱۰ را برای این پارامتر وارد کنید،  جستجو از حرف دهم رشته متنی، آغاز می‌شود.

نکته: توجه داشته باشید که با تعیین پارامتر Star_num، فقط محل آغاز جستجو تغییر پیدا می‌کند ولی موقعیت اعلام شده از همان ابتدای متن Within_text محسوب می‌شود. بنابراین حتی اگر Start_num برابر با ۱۰ باشد، باز هم نتیجه جستجو عبارت “ARMAN” در رشته “Excel Tutorial by ARMAN”، موقعیت یا حرف نوزدهم را مشخص می‌کند.

درمورد تابع ایندکس در اکسل بدانید.

تابع SEARCH در اکسل

برعکس تابع FIND، تابع SEARCH، به حروف بزرگ و کوچک لاتین حساس نبوده (Case Insensitive) و امکان استفاده از علامت‌های جایگزین (Wildcard) در رشته متنی را هم به کاربر می‌دهد. شکل دستوری و پارامترهای این تابع کاملا مشابه با تابع FIND است.

SEARCH( find_text, within_text, [start_num] )

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

با توجه به یکسان بودن پارامترهای دو تابع FIND و SEARCH در اکسل از معرفی پارامترهای تابع SEARCH خودداری می‌کنیم. در عوض به چند مثال در مورد این تابع خواهیم پرداخت. تابع زیر را در نظر بگیرید.

=SEARCH(“market”, “supermarket”)

مشخص است که قرار است عبارت market در رشته متنی supermarket جستجو شده و موقعیت اولین حرف market به عنوان خروجی ظاهر شود. همانطور که می‌توان حدس زد، نتیجه اجرای این تابع مقدار ۶ خواهد بود، زیرا ششمین حرف عبارت supermarket مربوط به عبارت market است.

همچنین نتیجه اجرای فرمول زیر، مقدار ۱ خواهد بود. از آنجایی که تابع SEARCH به حروف بزرگ و کوچک حساس نیست، با رسیدن به اولین حرف “e”، موقعیت را گزارش می‌کند.

=SEARCH(“e”, “Excel”)

مقایسه دو تابع FIND و SEARCH در اکسل

در این قسمت از متن، به کمک دو مثال به مقایسه این دو تابع خواهیم پرداخت. اولین مثال مربوط به نحوه برخورد دو تابع FIND و SEARCH در اکسل با متنی است که دارای حروف بزرگ و کوچک هستند. سپس در مثال بعدی، به کارگیری علامت‌های جایگزین را در تابع SEARCH بازگو خواهیم کرد.

درمورد ماکرو نویسی در اکسل بخوانید.

مثال ۱: حساسیت به حروف بزرگ و کوچک

مثال ۲: استفاده از علامت‌های جایگزین در جستجو

همانطور که گفته شد، تابع SEARCH برعکس تابع FIND، اجازه از علامت‌های جایگزین * و ? را برای مشخص کردن حروف جایگزین می‌دهد. ابتدا به نقش هر یک از این علامت‌ها در تعیین حروف مورد جستجو می‌پردازیم، سپس مثال را دنبال خواهیم کرد.

• هر بار استفاده از علامت ?، جایگزین یک حرف در عبارت مورد جستجو می‌شود. به این ترتیب علامت‌های ?? جایگزین یا نشانگر دو حرف در رشته متنی برای جستجو خواهند بود.
• به کارگیری علامت *، جایگزین حروف بعدی در یک رشته متنی خواهد بود. پس “*arman” نشانگر عبارت‌هایی است که با کلمه arman آغاز می‌شوند.

درمورد تابع جمع در اکسل بخوانید.

مثال‌هایی کاربردی از تابع FIND و SEARCH در اکسل

حل مسائل کاربردی، کمتر از تابع FIND و SEARCH در اکسل به صورت منفرد و بدون ترکیب با توابع دیگر، استفاده می‌کنیم و اغلب این توابع در ترکیب با توابع دیگر کاربرد پیدا می‌کنند. در ادامه به بعضی از کاربردهای این توابع در حل مسائل کاربردی اشاره خواهیم کرد. در این بین از توابع دیگر متنی مانند MID ،LEFT و Right استفاده خواهیم کرد.

پیدا کردن حرف خاص در ابتدا و انتهای یک رشته متنی

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

فرض کنید مقداری به صورت رشته‌های متنی (ترکیبی از نام و فامیل افراد) در ستون A در کاربرگ اکسل وجود دارند و می‌خواهیم اولین کلمه از چپ (نام لاتین) و اولین کلمه از راست (نام خانوادگی) افراد را تشخیص بدهیم و در ستون‌هایی جداگانه ثبت کنیم.

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

=LEFT(A2, FIND(” “, A2)-1)

یا

=LEFT(A2, SEARCH(” “, A2)-1)

جدا کردن رشته

مشخص است که در اولی فرمول، با استفاده از تابع FIND، فاصله یا علامت ” ” را پیدا کرده و از سمت چپ با کمک تابع LEFT از ابتدا تا حرف قبلی را به عنوان نام استخراج می‌کنیم. فرمول بعدی نیز به همین شیوه عمل کرده ولی فقط تابع SEARCH جایگزین تابع FIND شده است. در عمل با توجه به یکسان بودن پارامترهای هر دو این توابع و جستجوی علامت فاصله، استفاده از تابع FIND یا SEARCH در اکسل تفاوتی ندارد.

نکته: تابع LEFT از سمت چپ یک عبارت متنی،‌ به تعداد حروف گفته شده، جداسازی انجام داده و یک رشته متنی جدید می‌سازد. پارامتر اول رشته متنی اصلی و پارامتر دوم تعداد حروفی است که از چپ باید جداسازی شوند. از آنجایی که تابع FIND یا SEARCH موقعیت فاصله خالی را تعیین کرده، تابع LEFT از اولین حرف تا قبل از موقعیت گفته شده را جدا می‌کند. به همین دلیل در فرمول عبارت FIND(” “, A2)-1 یا SEARCH(” “, A2)-1 دیده می‌شود.

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

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

پیدا کردن Nامین موقعیت یک حرف در رشته متنی

باز هم به مانند قبل تصور کنید که رشته‌های متنی متفاوتی در ستون A قرار گرفته‌اند و می‌خواهیم اجزای تشکیل دهنده این رشته را از هم تشخیص دهیم. هر جزء از این عبارت بوسیله خط تیره (-) از یکدیگر جدا شده‌اند. بنابراین باید به دنبال اولین، دومین و … خط تیره (-) در متن بگردیم و موقعیت آن را گزارش کنیم. به فرمول زیر توجه کنید.

=FIND(“-“, A2, FIND(“-“,A2)+1)

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

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

=FIND(“-“,A2, FIND(“-“, A2, FIND(“-“,A2)+1) +2)

استخراج متن بین دو علامت پرانتز

فرض کنید رشته متنی طولانی در سلول‌های ستون A دارید که می‌خواهید قسمت متنی درون پرانتز آن‌ها را شناسایی کرده و از متن اصلی جدا کنید. به این منظور از تابع MID و SEARCH استفاده خواهیم کرد. به فرمول زیر توجه کنید.

=MID(A2,SEARCH(“(“,A2)+1, SEARCH(“)”,A2)-SEARCH(“(“,A2)-1)

همانطور که مشاهده می‌کنید تابع MID به منظور استخراج یک عبارت در رشته متنی (که در پارامتر A2 مشخص شده) با توجه به موقعیت (پارامتر دوم) و تعداد حروف تعیین شده (پارامتر سوم تابع MID) مورد استفاده قرار می‌گیرد.

• واضح است که اولین تابع SEARCH، به دنبال علامت پرانتز باز “(” می‌گردد و موقعیت آن را پیدا می‌کند.
• تابع SEARCH دوم نیز موقعیت پرانتز بسته یا علامت “)” را مشخص می‌کند. پس تابع MID باید از موقعیتی که تابع SEARCH‌ اول مشخص کرده کار استخراج را آغاز کند.
• در گام بعدی تعداد حروف استخراجی نیز از تفاضل موقعیت تعیین شده در SEARCH دوم و SEARCH سوم حاصل می‌شود.

جستجو و جایگزینی با استفاده از فرمان Find and Replace

در تصویر ۸، پنجره گفتگوی مربوط به جستجو و جایگزینی (Find and Replace) را در اکسل مشاهده می‌کنید. البته این پنجره شامل دو برگه است که اولی برای جستجو (Find) و دومی برای جایگزینی (Replace) کاربرد دارند.

در قسمت Find what عبارت مورد جستجو را وارد کرده و با فشردن دکمه Find Next سلولی که با محتویات Find what مطابقت دارد، فعال می‌شود. در صورتی که دکمه Find All را کلیک کنید، همه سلول‌های مطابق با مورد جستجو، به صورت متمایز در آمده و در کادر پایینی به شکل یک جدول و تفکیک شده براساس کارپوشه (Book)، کاربرگ (Sheet)، نام سلول (Name)، آدرس سلول (Cell) و مقدار (Value) و همچنین فرمول (Formula) نمایش داده می‌شوند.

اگر برگه Replace را فعال کرده باشید، با مشخص کردن عبارتی در بخش Replace with، عبارت جایگزین را تعیین کرده و عبارت مورد جستجو را با فشردن دکمه Replace یا Replace All، تغییر خواهید داد.

اگر به تنظیمات بیشتری نیز نیاز دارید، کافی است دکمه Options را کلیک کرده تا اختیارات بیشتری برای اجرای جستجو یا جایگزینی در اختیارتان قرار گیرد.

تابع FIND در اکسل

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

ساختار تابع FIND:

FIND(find_text,within_text,start_num)

find_text: رشته متنی است که به دنبال پیدا کردن آن هستید.

Within_text: رشته اصلی است.

start_num: یک آرگومان اختیاری است که نقطه شروع جستجو را در رشته اصلی تعیین می کند. مقدار پیشفرض آن ۱ می باشد.

نحوه استفاده از تابع FIND

شیوه نوشتن فرمول تایع FIND مانند مثال زیر است:

=FIND(“e”, “elephant”)

تابع FIND یک تابع Case-Sensitive است

در مثال زیر، تابع FIND برای جستجو “abc” در رشته سلول C3، عدد ۱۰ را برمی گرداند. زیرا تابع FIND به حروف کوچک و بزرگ حساس است، در نتیجه رشته “abc” و “ABC” برای تابع تفاوت دارد و رشته “ABC” در شروع رشته اصلی را نادیده می گیرد:

خطای جستجوی ناموفق

اگر تابع FIND رشته مورد نظر را در رشته اصلی پیدا نکند، خطای !VALUE# را برمی گرداند.

تعیین نقطه شروع جستجو

همانطور که گفتیم، می توانید با استفاده از آرگومان سوم تابع (start_num) تعیین کنید که تابع از کدام موقعیت عددی رشته شروع به جستجو کند. اگر این آرگومان را تعیین نکنید، تابع FIND از ابتدای رشته شروع می کند.

در مثال زیر، تابع FIND برای پیدا کردن کاراکتر “e” را رشته “elephant” از کاراکتر دوم شروع به جستجو می کند و در نتیجه نیز عدد ۳ را برمی گرداند. چون جستجو از کاراکتر دوم شروع شده پس کاراکترهای قبل از آن (یعنی کاراکتر اول) نادیده گرفته می شوند.

=FIND(B3,C3,D3)

در مثال زیر، آرگومان سوم برای تابع FIND تعیین نشده و تابع از همان کاراکتر اول شروع به جستجو می کند و در نتیجه عدد ۱ را برمی گرداند.

=FIND(B3,C3)

خطاهای نقطه شروع (start_num)

در صورتی که بخواهید نقطه شروع جستجو یا همان آرگومان سوم تابع را تعیین کنید، باید آن را براساس شرایط زیر وارد کنید:

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

اگر قوانین بالا را رعایت نکنید، تابع FIND خطای !VALUE# را نشان می دهد! مانند مثال زیر:

• در مورد اول، نقطه شروع (۲۵) از طول رشته اصلی بزرگتر است.
• در مورد دوم، نقطه شروع (۰) یک عدد غیر مثبت و کوچکتر از ۱ است.
• در مورد سوم، نقطه شروع (۱-) یک عدد منفی است.
• در مورد نقطه شروع، سلول خالی است.

کاراکترهای آزاد در تابعFIND

تابع FIND کاراکترهای آزاد یا جایگزین پذیر (wildcards) را نمی پذیرد.

فرض کنید، تابع در مثال زیر رشته “۰۰۰?” را جستجو می کند. در جستجوی آزاد، این رشته به این معنی است که “هر کاراکتری که بعد از آن سه عدد صفر می آید”. اما تابع FIND، کاراکتر “?” را همان علامت سوال می داند و به دنبال پیدا کردن یک رشته حاوی یک علامت سوال همراه با سه صفر می باشد.

استفاده از تابع FIND برای جدا کردن نام و نام خانوادگی

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

به دست آوردن نام

تابع LEFT در اکسل، تعداد مشخصی از کاراکترهای یک رشته را با شروع از ابتدای رشته را برمی گرداند. آرکومان اول تابع همان رشته یا سلول حاوی رشته مورد نظر است و آرگومان دوم تعداد کاراکترهایی است که باید برگشت داده شود.

در این مثال، لیست افراد به انگلیسی است اما برای به دست آوردن نام های فارسی نیز همین فرمول استفاده می شود.

می توانید از این تابع برای به دست آوردن نام افراد استفاده کنیم اما چون تعداد کاراکترهای آنها متفاوت است، از کجا بفهمیم چند کاراکتر باید برگشت داده شود؟

راه حل آن ساده است، برای به دست آوردن تعداد کاراکترها (همان آرگومان تابع LEFT) از تابع FIND استفاده می کنیم. تابع FIN موقعیت کاراکتر جداکننده بین نام و نام خانوادگی را برمی گرداند سپس باید یکی از مقدار برگشتی تابع کم کنیم. این عدد، تعداد کاراکترهایی را تعیین می کند که تابع LEFT باید از شروع رشته جدا کند.

=LEFT(B3,FIND(“ “,B3)-1)

تابع FIND در VBA

تابع FIND در VBA به صورت زیر نوشته می شود.

application.worksheetfunction.find(find_text,within_text,start_num)

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

استخراج شماره کارت با ترکیب توابع FIND و LEN و RIGHT

RIGHT(A2,LEN(A2)-FIND(“$”,A2))

اگر بخواهید رشته سمت راست کاراکتر”$” (شماره کارت) را استخراج کنید می‌توانید از ترکیب ۳ تابع FIND و LEN و RIGHT استفاده کنید. برای این کار فرمول زیر را در سلول نتیجه ( (C2تایپ کنید.

تفاوت‌های توابع SEARCH و FIND در اکسل

همانطور که گفتیم عملکرد تابع FIND مشابه تابع SEARCH است. هر دوی این توابع به شما کمک می‌کنند تا موقعیت کاراکترها را در یک رشته پیدا کنید.

تابع FIND به بزرگی و کوچکی حروف حساس است.

مثلا اگر از تابع FIND برای بدست آوردن موقعیت یک حرف مثل “F” در یک رشته استفاده کنید، “F” بزرگ و “f” کوچک دارای معانی مختلفی هستند. ولی تابع SEARCH  به بزرگی و کوچکی حروف حساس نیست.  این تفاوت را در قالب یک مثال در تصویر زیر می‌بینید. می‌خواهیم موقعیت رشته “fax” را پیدا کنیم. یک بار با استفاده از تابع SEARCH و یک بار با استفاده از تابع FIND این کار را انجام داده‌ایم. نتایج به دست آمده را در تصویر زیر هایلایت کرده‌ام. همانطور که می‌بینید تابع SEARCH موقعیت درست رشته را برمی‌گرداند و تابع FIND خطای #VALUE! را برمی‌گرداند.

تابع FIND امکان استفاده از کاراکترهای Wildcard را نمی‌دهد

ولی تابع SEARCH امکان استفاده از کاراکترهای Wildcard را می‌دهد. این تفاوت را در قالب یک مثال در تصویر زیر می‌بینید. می‌خواهیم موقعیت رشته “F*” را پیدا کنیم. یک بار با استفاده از تابع SEARCH و یک بار با استفاده از تابع FIND این کار را انجام داده‌ایم. نتایج به دست آمده را در تصویر زیر هایلایت کرده‌ام. همانطور که می‌بینید تابع SEARCH موقعیت درست رشته را برمی‌گرداند و تابع FIND خطای #VALUE! را برمی‌گرداند.

مزایا تابع Search چیست ؟