جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال‌ پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin

خلاصه آمار

تبليغات

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

اکسل

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

  • توابع جستجو

۱-  تابع Find:

با استفاده از این تابع می‌توان در متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا کرد. لازم به ذکر است که فاصله (space) نیز یک کاراکتر محسوب می‌شود. ساختار (Syntax) این تابع به شکل زیر است:

=FIND(find_text , within_text , start_num)

این تابع دارای ۳ آرگومان (پارامتر یا شیء) است:

  • find_text (آرگومان ۱): عبارتی که بایستی جستجو شود که می‌تواند یک حرف یا یک کلمه یا متن باشد.
  • within_text (آرگومان ۲): متن مرجع که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.
  • Start_num (آرگومان ۳): نقطه آغاز جستجو است. در واقع مشخص می‌کنیم که در آرگومان ۲ از چندمین کاراکتر شروع به جستجو کند که در این صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۲ می‌تواند باشد.

توجه: درصورتیکه عدد صفر یا عددی بیش از تعداد کاراکتر موجود در آرگومان ۲ به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

توجه: اگر آرگومان ۱ در آرگومان ۲ موجود نباشد خطای #VALUE! را خواهیم دید.

 

۲- تابع  :Search

تمامی آرگومان‌ها و همچنین عملکرد تابع  Searchبا تابع Find یکسان است و تنها تفاوتشان این است که تابع Find به بزرگی یا کوچکی حرف حساس است ولی تابع Search اینگونه نیست. ساختار (Syntax) این تابع به شکل زیر است:

=Search(find_text , within_text , start_num)

منابع:

rahdar-ie.blogfa.com

introexcel.blogfa.com


  • توابع جایگزینی:

۱- تابع REPLACE:

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

=REPLACE(old_text,start_num,num_chars,new_text)

این تابع دارای ۴ آرگومان است:

  • old_text (آرگومان ۱): متن قدیمی که قصد دارید عملیات جایگزینی را در آن انجام دهید.
  • Start_num (آرگومان ۲): نقطه آغاز جایگزینی در متن قدیمی. در واقع مشخص می‌کنیم که در آرگومان ۱ از چندمین کاراکتر جایگزینی شروع شود. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان ۱ می‌تواند باشد.
  • num_chars (آرگومان ۳): تعداد کاراکتری که در متن قدیمی باید با متن جدید جایگزین شوند.
  • new_text (آرگومان ۴): متن جدید که باید در متن قدیم جایگزین شود.

به مثال زیر توجه کنید:

=REPLACE(“09122048458″,1,1, “+98″)

فرمول بالا عدد صفر ابتدای شماره تماس را با عبارت ۹۸+ جایگزین می‌کند.
 

۲- تابع SUBSTITUTE:

تابع SUBSTITUTE همانند تابع REPLACE برای جایگزین کردن بخش از یک رشته‌ی متنی با رشته‌ی متنی جدید مورد استفاده قرار می‌گیرد با این تفاوت که در این تابع دقیقاً مشخص می‌کنیم چه بخشی (دقیقاً کدام کاراکترها) از یک رشته‌ی متنی با رشته‌ی متنی جدید باید جایگزین شود، ساختار این تابع مانند زیر است:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

این تابع دارای ۴ آرگومان است:

۱-   text (آرگومان ۱): متن مرجع که قصد دارید عملیات جایگزینی را در آن انجام دهید.

۲-   old_text (آرگومان ۲): کاراکترهایی از رشته‌ی متنی مرجع می‌باشد که قرار است با کاراکترهای جدید جایگزین شوند.

۳-    new_text (آرگومان ۳): متن جدید که باید در متن قدیم جایگزین شود.

۴-   [instance_num] (آرگومان ۴): این آرگومان اختیاری است و زمانی استفاده می‌شود که آرگومان ۲ چند مرتبه در متن مرجع تکرار شده باشد و بخواهیم تنها یک مورد از آن‌ها جایگزین شود. در این حالت با استفاده از آرگومان ۴ مکان آن را مشخص می‌کنیم.

حال می‌خواهیم مانند مثال تابع REPLACE عدد صفر را با ۹۸+ جایگزین کنیم:

=SUBSTITUTE(“09122048458″,”0″, “+98″)

خروجی این فرمول عبارت “۹۸۹۱۲۲+۹۸۴۸۴۵۸+” خواهد بود، در واقع تمام صفرهای پیدا شده با عبارت “۹۸+” جایگزین می‌شوند. در این حالت باید از آرگومان ۴ استفاده کرد.اگر این آرگومان خالی باشد (مانند مثال بالا) به این معنی است که تمام صفرها باید جایگزین شوند و اگر به جای این آرگومان عدد ۱ نوشته شود، تنها صفر اول (اولین صفر پیدا شده در رشته) با عبارت “۹۸+” جایگزین می‌شود، مانند زیر:

=SUBSTITUTE(“09122048458″,”0″, “+98″, 1)

منبع: excelpro.ir

 

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

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

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

نظرات (۱۷)

  1. سلام
    ارسال کردم ولی ظاهرا بعد از زدن دکمه ارسال کد میپره
    کدی که میخوام استفاده کنم مربوط به لینک هست و دارای اتریبیوت های تایتل و اچ رف هست
  2. با سلام. عذرخواهی می کنم فرمول رو نذاشته بودم
    یک سری اطلاعات داخل ستون A1 دارم و یک سری اطلاعات داخل ستون B1 ، میخوام اونها رو در یک فرمول به شکل زیر جایگذاری کنم، ممنون میشم راهنمایی کنید

    B1
    • پاسخ:

      سلام
      مجددا اشاره ای به فرمول نکردید!
  3. سلام .وقت بخیر
    یک سری اطلاعات داخل ستون A1 دارم و یک سری اطلاعات داخل ستون B1 ، میخوام اونها رو در یک فرمول به شکل زیر جایگذاری کنم، ممنون میشم راهنمایی کنید
    B1
    • پاسخ:

      سلام
      اشاره ای به فرمول نکردید!
  4. من یه فایل فرهنگ واژگان دارم که متاسفانه در ورد تایپ شده من در اکسل اوردمش اما یه قسمت هایی فاصله خورده در یک ردیف به جای کلمه دو تا کلمه افتاده راهی هست که بشه این فاصله حذف بشه و به صورت یک شت مستقل اضافه بشه
    • پاسخ:

      سلام
      تا جاییکه اطلاع دارم نمی توان بصورت خودکار اینکار را در اکسل انجام داد. باید دستی اصلاح کنید.
  5. سلام؛ من می‌خواهم دو حرف «ی» و «ک» عربی را با دو حرف «ی» و «ک» فارسی در آنِ واحد با یکبار فرمول SUSBTITUTE عوض کنم. برای یک حرف مثلاً حرف ک فرمول زیر را نوشتم و خیلی خوب جواب داد ولی برای هر دو حرف چه می‌شود کرد؟ مثلاً اگر بخواهیم همزمان هم حرف ی و هم حرف ک را در یک بار فرمول نویسی با SUSBTITUTE عوض کنیم چه باید کرد؟ ممنون.
    ‎‎=‎SUBSTITUTE‎(‎A‎2‎;‎"‎ک‎"‎;‎"‎ک‎"‎)‎
    • پاسخ:

      سلام
      از فرمول تو در تو استفاده کنید. به طور مثال در فرمول زیر a با a1 و b با b1 جایگزین می شود.
      SUBSTITUTE(SUBSTITUTE(F1,"a","a1"),"b","b1")
      موفق باشید.
  6. احسان صفابخش

    با سلام
    در اکسل میخواهم با انتخاب یک سل یه لیست شامل اعداد انتخاب گردد.
    ممنونم
    • پاسخ:

      سلام
      با انتخاب سل که بعید می دانم امکان پذیر باشد مگر اینکه یک دکمه برای اینکار بسازید.
  7. سلام راه حل یا فرمول شمردن تعداد یک کاراکتر خاص در یک سلول اکسل چی هستش
    • پاسخ:

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

      سلام
      می خواهید اینکار به صورت خودکار در شیت جدید انجام شود؟ یعنی با اضافه کردن اطلاعات در شیت اول، در شیت دوم خواسته شما انجام شود.
  9. سلام. در اکسل لیستی است که در هر سلول آن کلمه ای وجود دارد و توضیح آن کلمه درون پرانتز قرار دارد . می خواهم آن متن درون پرانتز و خود پرانتز را کامل در اکسل حذف کنم.
    با تشکر
    • پاسخ:

      سلام
      با استفاده از ابزار Find and Replace عبارت (*) را جستجو کنید و با هیچ جایگزین کنید.
      موفق باشید.
  10. سلام
    ایا میشود با نوشتن یک اسم در سلول ,  این اسم را جایگزین اسم اصلی فایل کرد؟
    • پاسخ:

      سلام
      اگر منظورتان این است با نوشتن یک اسم در سلول، اسم فایل اکسل با اسم تایپ شده جایگزین شود، بعید می‌دانم اینکار امکان‌پذیر باشد. چون برای تغییر نام باید عملیات ذخیره فایل انجام شود که در اینکار انجام نمی‌شود.
  11. ممنون از وقتی که گذاشتین
    البته روش find& replace درسته ک روش ساده ایه اما تمام ارزش های یک فایل اکسل رو تغییر میده و نمیشه در مورد یک ستون خاص استفادش کرد
    در اصل تو همین مثالی که زدم ممکنه ستون های دیگه ای هم کلمات بله و خیر داشته باشن که من نخوام تغییر کنن ولی با این روش تمام اونها هم به یک و دو تبدیل میشن
    بازم ممنون از کمکتون
  12. ممنون بابت مطلبی ک لینکشو گذاشتین
    ولی من منظورم اینه که مثلا یک ستون داشته باشم که تمام ارزش های اون ستون کلمات بله و خیر باشن و قصدم این باشه که تمام اون بله ها با عدد یک و تمام اون خیرها با عدد دو جایگزین بشن
    چون با این روشی ک تو این پیج توضیح داده شده فقط ارزش یک سلول تغییر میکنه نه تمام سلول های اون ستون مورد نظر
    جایگزینی گروهی هم خیلی وقت گیر هست
    • پاسخ:

      مطلبی که گفتم دقیقا مشکل شما را حل می‌کند. اما کار شما خیلی ساده است. با ابزار جستجو و جایگزینی اکسل (Ctrl+H) کلمه بله را پیدا کنید و با ۱ جایگزین کنید. برای خیر هم همین کار را انجام دهید.
  13. سلام
    در مورد جایگزینی یک کلمه با کلمه دیگه میخواستم بپرسم چطور میشه این تابع رو روی یک ستون خاص اعمال کرد
    • پاسخ:

      سلام
      منظورتان از یک ستون چیست؟ اگر منظورتان جستجو و جایگزینی گروهی می‌باشد به این مطلب مراجعه کنید.
  14. سلام
    یک سوال داشتم. در یک فایل اکسل با دو شیت
    شیت اول دارای یک ستون است: نام
    شیت دوم هم دارای دو ستون است: نام وتلفن
    نام ها در شیت  اول شامل همون نام های شیت اول + نام های دیگری که اضافه شده است (نام ها بیشتر از شیت دوم است)
    ترتیب هم در دو شیت متفاوت است.
    حال می خواهیم تلفن هایی که قبلا در شیت دوم برای نام ها اختصاص دادیم در شیت اول هم مقابل نامشان (در ستون دوم) کپی شود. آنهایی که خالی می ماند دستی باید وارد شود
    راه حل چیست؟
    • پاسخ:

      سلام
      از تابع vlookup که در اینجا معرفی شده است استفاده کنید.
      مثلا اگر نام‌های شیت اول در ستون A باشد و نام و تلفن‌های شیت دوم در ستون A و B باشد. در ستون B شیت اول از فرمول زیر استفاده کنید.

      =VLOOKUP(A1,Sheet2!A:B,2,FALSE)

      موفق باشید.

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

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

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی