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

۲۴۰۸۶ بازدید
آخرین به‌روزرسانی: ۰۵ اسفند ۱۴۰۲
زمان مطالعه: ۱۳ دقیقه
تابع FIND و SEARCH در اکسل — راهنمای کاربردی

اغلب برای جستجو و جایگزینی مقدار متنی یا عددی در اکسل از دستور Find/Replace و یا کلیدهای مبانُبر Ctrl+F یا Ctrl+H، استفاده می‌کنیم. ولی این کار هم زمان‌گیر است و هم باید همین روال را برای هر تغییر کوچک در کاربرگ، اجرا کنیم. ولی اگر بتوانیم با استفاده از توابع اکسل، عمل جستجو و حتی جایگزینی را به شکل فرمولی بنویسیم، سرعت و دقت انجام عمل جستجو و جایگزینی افزایش می‌یابد. بعلاوه فرمولی کردن جستجو و جایگزینی، این امکان را به کاربر می‌دهد که محاسبات خاصی را با استفاده از مقادیر پیدا شده انجام دهد. در این نوشتار به معرفی تابع FIND و SEARCH در اکسل خواهیم پرداخت و مزایایی هر یک را با توجه به بررسی و اجرای مثال‌هایی، بازگو خواهیم کرد. توجه داشته باشید که تابع FIND و SEARCH در اکسل نسخه ۲۰۱۰ به بعد قابل استفاده هستند.

برای آشنایی بیشتر با نحوه کار با اکسل و فرمول‌نویسی در آن بهتر است نوشتارهای آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن مطالب توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی و قالب بندی شرطی (Conditional Formatting) با اکسل نیز خالی از لطف نیست.

تابع 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"، موقعیت یا حرف نوزدهم را مشخص می‌کند.

به عنوان مثال کاربرگ مربوط به تصویر ۱ را در نظر بگیرید. می‌خواهیم موقعیت حرف d را در عبارت Find، تعیین کنیم. البته همین کار را هم برای حرف a انجام خواهیم داد.

excel find function
تصویر ۱: کاربرگ جستجو با تابع FIND

همانطور که مشاهده می‌کنید نتیجه فرمول زیر برابر با ۴ است. به این معنی که حرف d در عبارت Find، حرف چهارم است.

= FIND("d",A1)

در حالیکه نتیجه تابع FIND در فرمول زیر به علت اینکه حرف a در عبارت Find وجود ندارد با خطای !VALUE# همراه است.

= FIND("a",A1)

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

  • تابع FIND، به حروف بزرگ و کوچک لاتین حساس است. اگر به دنبال عبارت ARMAN هستید حتما باید آن را با حروف بزرگ لاتین وارد کنید در غیر این صورت با خطای !VALUE# مواجه خواهید شد.
  • استفاده از علامت‌های جایگزین مانند * , ? در تابع FIND جایز نیست.
  • موقعیتی که تابع FIND نشان می‌دهد، مربوط به اولین حرف عبارت Find_text است. برای مثال تابع ("FIND("ap",happy نتیجه‌ای برابر با ۲ دارد. زیرا حرف a دومین حرف از رشته متنی است.
  • اگر در رشته متنی مرتبط با پارامتر Within_text، چند عبارت Find_text موجود باشد، تابع Find‌ اولین مطابقت را مشخص و موقعیت را گزارش می‌کند. برای مثال اگر کسی از تابع (FIND("p",happy استفاده می‌کند پاسخ تابع مقدار ۲ است و نمی‌تواند حرف بعدی را مورد جستجو قرار دهد.
  • اگر به دنبال حرف خالی ("") در یک عبارت متنی می‌گردید، تابع FIND، مقدار ۱ یعنی اولین حرف رشته متنی را نشان می‌دهد.
  • در هر یک از حالات زیر، تابع FIND، خطای !VALUE# را نشان خواهد داد.
    • مقدار پارامتر Find_text در عبارت Within_text‌ موجود نباشد.
    • عدد مشخص شده برای پارامتر Start_num بزرگتر از طول عبارت Within_text باشد.
    • مقدار پارامتر Star_num، منفی یا صفر باشد.
نمودارهای میلی و داده زیر ذره بین

نکته: تابع FIND در همه زبان‌ها در گروه نرم‌افزارهای OFFICE قابل استفاده نیست. همچنین این تابع به صورت تک بایتی (SBCS) یا (Single Byte Character Set) حروف را شناسایی می‌کند. در نتیجه برای استفاده در زبان‌های چینی و ژاپنی باید از نسخه دو بایتی (DBCS) این تابع به نام FINDB کمک گرفت. پارامترهای این تابع دقیقا با تابع FIND یکسان است.

تابع 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")

به تصویر ۲ که یک کاربرگ اکسل را مشخص کرده، توجه کنید. نتیجه تابع SEARCH و پارامترهای آن مشخص شده است.

excel search function
تصویر ۲: کاربرگ اکسل و نتایج تابع SEARCH

هنگام استفاده از تابع SEARCH، ممکن است با خطای !VALUE# مواجه شوید، دلیل این امر در ادامه فهرست شده است.

  • مقدار پارامتر Find_text در عبارت Within_text قرار ندارد.
  • موقعیت جستجو که توسط پارامتر Start_num تعیین می‌شود از طول رشته متنی (پارامتر Within_text) بزرگتر است.
  • پارامتر Start_num مقداری منفی یا صفر است.

نکته: تابع SEARCH در همه زبان‌ها در گروه نرم‌افزارهای OFFICE قابل استفاده نیست. همچنین نسخه دو بایتی (DBCS) برای زمانی که کدگذاری ۲ بایتی برای هر حرف (Double Byte Character Set) صورت گرفته، به شکل SEARCHB در اکسل قابل استفاده است.

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

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

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

در کاربرگ زیر نتیجه استفاده از دو تابع FIND و SEARCH در اکسل با توجه به وجود حروف بزرگ و کوچک در پارامتر Find_text را مشاهده می‌کنید.

excel case sensitive search
تصویر ۳: وجود و عدم حساسیت به حروف بزرگ و کوچک برای تابع FIND و SEARCH در اکسل

همانطور که مشخص است تابع FIND حرف چهارم را برای موقعیت حرف "e" نمایش داده در حالیکه به همان شیوه و پارامترها، تابع SEARCH اولین حرف را به عنوان موقعیت حرف "e"، تعیین کرده است.

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

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

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

حال به تصویر ۴ که یک کاربرگ اکسل را به همراه تابع SEARCH مشخص کرده توجه کنید.

excel wildcard search
تصویر ۴: به کارگیری علامت‌های جایگزین در تابع SEARCH

فرمولی که در سلول‌های B2, B4 و B6 نوشته شده، یکسان هستند، فقط پارامتر دوم که رشته متنی برای جستجو را مشخص می‌کند در اولین فرمول A2 و در دومین و سومین فرمول نیز A4 و A6 است.

از آنجایی که از علامت *‌ در پارامتر Find_text استفاده کرده‌ایم ("function*2013")، اکسل هر قسمتی از متن که با کلمه function شروع و با 2013 خاتمه یافته است را مورد بررسی قرار داده و موقعیت حرف اول function را گزارش کرده است.

نکته: اگر بخواهیم در یک رشته متنی، به دنبال علامت * یا ? بگردیم باید آن‌‌ها را در پارامتر Find_text به همراه یک علامت ~ به کار ببریم. برای مثال از "*~" برای جستجو علامت ستاره استفاده خواهیم کرد.

تصویر دستی که ذره بینی در دست دارد و از طریق آن به داده های اکسل نگاه می کند

در ادامه متن، به مثال‌هایی خواهیم پرداخت که در هر یک از آن‌ها به صورت کاملا کاربردی از دو تابع FIND و SEARCH در اکسل استفاده شده است.

مثال‌هایی کاربردی از تابع 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 محاسبه شده و از سمت راست با توجه به موقعیت فاصله، جداسازی انجام شده است.

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

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

یا

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

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

excel find substring
تصویر ۵: به کارگیری توابع LEFT ،RIGHT با تابع FIND و SEARCH در اکسل

نکته: البته می‌توانستیم این کار را به شکلی ساده‌تر با کمک Flash Fill یا دستور Text to Column به عنوان ابزارهای جانبی اکسل نیز انجام دهیم. ولی در این قسمت با توابع و فرمول‌نویسی این کار صورت گرفته است.

البته اگر بخواهیم نام میانی (Middle Name) افراد را هم استخراج کنیم، از همین شیوه استفاده خواهیم کرد و فقط کمی محاسبات و پیچیدگی به فرمول‌ها برای پیدا کردن اولین و دومین فاصله اضافه می‌شود. ولی  به هر حال، تابع FIND و SEARCH در اکسل به همین ترتیب با تابع MID ترکیب می‌شوند.

برای درک بهتر این نحوه استفاده از تابع FIND و SEARCH در اکسل، توصیه می‌شود به مثال بعدی نیز توجه کنید.

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

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

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

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

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

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

به تصویر ۶ توجه کنید. در این کاربرگ از سه خط تیره برای جداسازی مقادیر ستون A استفاده شده است. بنابراین از تابع FIND به صورت تو در تو استفاده شده تا موقعیت جداکننده خط تیره دوم و سوم هم مشخص شود.

find n occurrence
تصویر ۶: استخراج موقعیت قرارگیری علامت (-) در یک رشته متنی

براساس خروجی‌هایی حاصل در ستون‌های B و C و به کارگیری توابع متنی LEFT ،RIGHT و MID می‌توانیم هر بخش از عبارت متنی در ستون A را از متن اصلی جدا کنیم.

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

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

=MID(A2,SEARCH("(",A2)+1, SEARCH(")",A2)-SEARCH("(",A2)-1)

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

  • واضح است که اولین تابع SEARCH، به دنبال علامت پرانتز باز "(" می‌گردد و موقعیت آن را پیدا می‌کند.
  • تابع SEARCH دوم نیز موقعیت پرانتز بسته یا علامت ")" را مشخص می‌کند. پس تابع MID باید از موقعیتی که تابع SEARCH‌ اول مشخص کرده کار استخراج را آغاز کند.
  • در گام بعدی تعداد حروف استخراجی نیز از تفاضل موقعیت تعیین شده در SEARCH دوم و SEARCH سوم حاصل می‌شود.
find text between parentheses
تصویر ۷: جداسازی عبارت درون پرانتز در اکسل

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

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

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

find and replace dialog box
تصویر 8: پنجره گفتگوی جستجو و جایگزینی (Find and Replace) در اکسل

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

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

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

find and replace dialog box options
تصویر ۹: تنظیمات اختصاصی برای پنجره گفتگوی جستجو و جایگزینی اکسل

به کمک دکمه Format نیز می‌توانید قالب (قلم، رنگ و ...) را هم برای متن مورد جستجو و هم عبارت جایگزین، تعیین نمایید. برای آشنایی بیشتر با این پنجره و کاربردهای آن بهتر است مطلب جستجوی متن در اکسل — به زبان ساده را مطالعه کنید.

خلاصه و جمع‌بندی

همانطور که در این متن خواندید تابع FIND و SEARCH در اکسل بسیار به یکدیگر شبیه هستند ولی تابع SEARCH که تابع جدیدتری نسبت به FIND محسوب می‌شود، بسیاری از معایب تابع FIND را برطرف کرده و قابلیت‌های بیشتری از جمله به کارگیری «علامت‌های جایگزین» (Wildcard) را هم در اختیار کاربر قرار می‌دهد.

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

هر چند فرمانی جستجو جایگزینی (Find and Replace) نیز در اکسل به کار می‌رود ولی استفاده از توابع در حل چنین موضوعاتی گاهی باعث روانی گردش کار و سرعت بیشتر در جستجو و جایگزینی خواهد شد.

بر اساس رای ۰ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
ablebits.comمجله فرادرس
۸ دیدگاه برای «تابع FIND و SEARCH در اکسل — راهنمای کاربردی»

سلام و وقت بخیر
آیا چنین روشی در اکسل وجود دارد؟

میخواهیم بگوییم اگر در صفحه کاری در ستون 1 عدد 1000 رو پیدا کردی، سلول 10 از همون ردیفی که عدد 1000 توش هست رو استفاده کن، درغیراینصورت 0 بذار:

IF(پیداکردن ردیفی که عدد 1000 در ستون 1 اون هست، برداشتن عدد موجود در ستون 10م از ردیفی که عدد 1000 در اون بوده و جایگزینی در اونجایی که داریم دستور IF رو مینویسیم، درغیراینصورت 0 رو در جاییکه دستور IF نوشته میشود بگذارد)

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

سلام و درود
من می خواهم با زدن روی یک متن موضوع که انتخاب می کنم را از شیت های دیگر بالا بیاره

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

سلام و عرض ادب
با تشکر از مطالب مفیدتون
من یک مشکل داشتم که میخاستم با شما درمیون بذارم و اون اینه
میخام نام فایلم رو با فرمول سل استخراج کنم منتهی مشکلی که هست اگه در ورک شیت فقط یک شیت باشه دیگه دور نام کروشه نمی ندازه چطور این مشکل رو حل کنم(اکسل2010)؟

این نمونه متنی هست که میخام نام فایل ازش استخراج بشه(فقط یک شیت داره)
D:\بهارستان\100-200-300-400\300\Doc\exls\503.. – Copy.xlsm
و اگه تعداد شیت بیشتر از 1 باشه نام فایل به این صورت میشه که مشکلی ندارم:
D:\بهارستان\100-200-300-400\300\Doc\exls\[503.. – Copy.xlsm]503

(البته تو تکست اینجا یکم ادرس فایل بخاطر فارسی و لاتین بودن بدنمایی میکنه و ممکنه گیج کننده باشه که قبلش ازتون عذر میخام)

سلام
در سلولی اعداد بین 1 تا 9 بدون ترتیب وارد شده اند
میخواهیم این اعداد را جستجو و در صورتی که هر کدام وجود دارند به صورت جدا در یک ستون عبارت 1 یا true ببینیم . خروجی تابع #Value! میدهد
راهکاری هست که کمک کنه؟

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

سلام و درود،

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

البته استفاده از توابع search و find نیز مفید است که در متن به آن‌ها اشاره شده است.

تندرست و پیروز باشید.

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *