اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • آموزش کار با تابع Replace

آموزش کار با تابع Replace

  • ارسال شده توسط سامان چراغی
  • دسته بندی توابع اکسل
  • تاریخ ۱۲ فروردین ۱۳۹۹
  • نظرات ۲ دیدگاه ها
تابع Replace
3 / 5 ( 1 امتیاز )

جایگزینی اطلاعات در سلول با استفاده از توابع

توابع متنی در اکسل کاربردهای زیادی دارن. با استفاده از توابع متنی میتونیم در یک رشته متنی یا عددی، تغییراتی ایجاد کنیم. مثلا قسمت هایی رو جدا کنیم، قسمت هایی رو با عبارات جدید جایگزین کنیم و …. در این دسته از توابع دو تابع برای جایگزین کردن وجود داره. تابع Substitute و تابع Replace. این دو تابع هر دو کار جایگزینی رو انجام میدن. یعنی قسمت هایی از یک رشته متنی رو با موارد جدید جایگزین میکنند. تابع Substitute رو مفصل در مقاله تابع Substitute در اکسل توضیح داده ایم. در این مقاله راجع به تابع Replace و مثال هایی در این خصوص صحبت خواهیم کرد:

تابع Replace و آرگومان های اون به شرح زیر است:

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

=REPLACE(old_text, start_num, num_chars, new_text)

Old_Text: سلول یا رشته ای که میخوایم تغییرات در اون ایجاد کنیم.

Start_Num: این آرگومان از جنس عدد هست و مکان شروع تغییر رو نشون میده. مثلا عدد 2، نشون دهنده این هست که از دومین کاراکتر شروع کنیم برای ایجاد تغییر.

Num_Chars: این آرگومان نیز از جنس عدد هست و تعداد کاراکتری که میخواهیم حذف بشه رو نشون میده. مثلا عدد 4 به این معنی هست که از دومین کاراکتر، چهار کاراکتر رو حذف کن. یعنی کاراکتر 2، 3، 4 و 5.

New_Text: عبارت جدیدی (با هر تعداد کاراکتر) که میخواهیم جایگزین اون کارکترهای قدیمی بشه رو می نویسیم.

چند مثال از تابع Replace

در ادامه مثال هایی از کاربرد این تابع رو تشریح میکنیم:

مثال اول: جایگزینی کارکتر با یک عبارت

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

حتما بخوانید:  جستجو پیشرفته در اکسل با استفاده از Wildcard

=REPLACE(A1,3,1,”DD”)

جایگزینی یک کاراکتر با یک عبارت جدید

شکل 1- تابع Replace – جایگزینی یک کاراکتر با یک عبارت جدید

در این فرمول، سومین کاراکتر از داده موجود در سلول A1 حذف میشه و بجاش عبارت DD گذاشته میشه.

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

Value (Replace (…))

 

مثال دوم: اضافه کردن کارکتر به متن

فرض کنید یک سری کد ملی داریم که میخواهیم دو تا – بین کاراکترها قرار بدیم. از طرفی مطمئنیم که کد ملی ده رقم هست و از طرفی میخواهیم بین کارکتر 3 و 4 و بین کاراکتر 9 و 10 یک – قرار بدیم و کد ها رو بصورت الگوی اصلی کد ملی تبدیل کنیم.

برای حل این مسئله راه حل های متفاوتی وجود داره. در اینجا میخوایم از تابع Replace برای اینکار استفاده کنیم. برای حل این مسئله باید طی دو مرحله از تابع Replace استفاده کنیم.

گام اول: باید بین کاراکتر سوم و چهارم یک – اضافه کنیم. یعنی از کاراکتر چهارم، هیچ کاراکتری رو حذف نکنیم و فقط – رو اضافه کنیم. یعنی:

=REPLACE( A1 , 4 , 0 , “-“)

تابع  Replace- اضافه کردن کاراکتر اول

شکل 2 – تابع  Replace- اضافه کردن کاراکتر اول

گام دوم: حالا باید – دوم رو به خروجی فرمول اول اضافه کنیم. یعنی فرمول اول میشه ورودی فرمول دوم:

=REPLACE ( REPLACE(A1,4,0,”-“) ,11 , 0 , “-“)

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

شکل 3- تابع Replace – اضافه کردن کاراکتر دوم

این فرمول میاد رشته ای که اولین – رو داره به عنوان ورودی در نظر میگیره که الان 11 رقمی هست و بعد از یازدهمین کاراکتر هیچ چیزی رو حذف نمیکنه و – دوم رو اضافه میکنه.

حتما بخوانید:  تابع Sumifs اکسل | تکنیک های کاربردی در اعمال شروط

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

حالا میخوایم مثالی رو حل کنیم که در اون مکان چیزی که میخوایم تغییر بدیم، متغیر هست. یعنی اینکه از چندمین کاراکتر باید شروع به حذف کنیم، برای هر داده متغیر هست. مثلا یک سری آدرس ایمیل داریم، بعضی از اونها، تغییراتی دارند. مثلا اسم شرکت از PDC تبدیل شده به PDCA. از طرفی یک سری آدرس دیگه از شرکت های دیگه هم داریم که اونها بدون تغییر میمونن. از طرفی باید حواسمون باشه اگر عبارت PDC در خود آدرس ایمیل بود، اون تغییر نکنه. فقط آدرس دامین باید تغییر کنه.

تابع Replace – اصلاح آدرسهای ایمیل

شکل 4 – تابع Replace – اصلاح آدرسهای ایمیل

برای اینطور مسائل چه باید بکنیم؟

همونطور که قبلا هم گفتیم، برای استفاده از توابع متنی باید بتونیم الگوی مناسبی از داده ها پیدا کنیم. در داده های نمای شداده شده در شکل 4، مشخصه که عبارت PDC در بعضی از آدرس های ایمیل هم وچود داره. که ما اونو نمیخوایم. پس الگویی که میتونیم بین داده ها پیدا کنیم اینه که، دنبال عبارت PDC بعد از @ باشیم. پس برای اینکه مکان PDC بعد از @ رو پیدا کنیم از تابع Search/Find استفاده میکنیم (از Search استفاده میکنیم چون میدونیم به حروف بزرگ و کوچک حساسیتی نداره).

=SEARCH( “pdc” , A1 , SEARCH (“@”,A1) +1)

برای اینکه جستجو از بعد از @ شروع بشه، از آرگومان آخر استفاده میکنیم و در واقع با یک Search دیگه به تابع میگیم که از @ به بعد شروع به جستجو کن.

پیدا کردن عبارت مورد نظر با استفاده از تابع Search

شکل 5- تابع Replace- پیدا کردن عبارت مورد نظر با استفاده از تابع Search

حتما بخوانید:  Vlookup از چند شیت یا فایل

همونطور که در شکل 5 نمایش داده شده، مکان حرف P از کلمه PDC بعد از @ در هر سلول مشخص شده و آدرس هایی که PDC ندارند، با خطا مواجه شده.

حالا باید PDC رو به PDCA تبدیل کنیم. پس از تابع Replace به شکل زیر استفاده میکنیم:

=REPLACE (A1 , B1 , 3 , “PDCA”)

در این فرمول، سلول B1 برابر است با مکان شروع p از عبارت PDC بعد از @ در هر آدرس ایمیل. از اونجا، سه کاراکتر رو حذف میکنیم و بجاش PDCA رو قرار میدیم.

پیدا کردن یک عبارت خاص و جایگزین کردن با عبارت جدید

شکل 6- تابع Replace – پیدا کردن یک عبارت خاص و جایگزین کردن با عبارت جدید

حالا باید با یک Iferror خطاهای ناشی از فرمول نویسی رو مدیریت کنیم. پس فرمول رو به شرح زیر تغییر میدیم:

=IFERROR(C1 , A1)

تابع Replace – مدیریت خطای فرمول نویسی

شکل 7 – تابع Replace – مدیریت خطای فرمول نویسی

پس بصورت کلی، مکان عبارت PDC بعد از @ رو پیدا کردیم و بعد با تابع Replace با عبارت PDCA جایگزین کردیم و در نهایت هم با Iferror، خطاها رو مدیریت کردیم.

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

=IFERROR ( REPLACE ( A1 , SEARCH ( “pdc”, A1 , SEARCH(“@”,A1) +1 ) , 3 , “PDCA”) , A1)

در این مقاله کاربردهای تابع Replace رو دیدیم. برای درک بهترِ این مقاله، پیشنهاد میکنیم مقالات مربوط به Substitute, find/Search و مدیریت خطا رو هم مطالعه کنید.

دانلود فایل آموزش تابع Replace

برای دانلود فایل این آموزش روی لینک زیر کلیک کنید:

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایل این آموزشبرای دانلود فایل کلیک کنید

برچسب:تابع Iferror, تابع Replace, تابع Search, تابع Value

  • اشتراک گذاری:
profile avatar
سامان چراغی
من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

مطلب قبلی

فرمول نویسی با استفاده از Table
۱۲ فروردین ۱۳۹۹

مطلب بعدی

انتقال داده از وبسایت به اکسل
۱۲ فروردین ۱۳۹۹

ممکن است همچنین دوست داشته باشید

Text-Functions-Cover
توابع ویرایش متن انگلیسی در اکسل
۱۷ مرداد, ۱۳۹۹
Mid Function Cover-min
تابع Mid و چند کاربرد در اکسل
۱ دی, ۱۳۹۸
Date Functions Cover
توابع کاربردی تاریخ در اکسل
۲۷ آذر, ۱۳۹۸

    2 نظر

  1. profile avatar
    اکسلنقی اکسلپور
    ۱۷ مهر ۱۳۹۹
    پاسخ

    آقا یا خانوم :
    کارکتر * چجوری در ابزار فایند (کنترل+اف ) با یه کارکتر دیگه ریپلیس میشه ؟؟؟؟؟؟
    آیا اکسل باهوش در این مورد ساده خنگه؟

    • profile avatar
      سامان چراغی
      ۲۱ مهر ۱۳۹۹
      پاسخ

      سلام
      برای جستجو ستاره عبارت *~ رو جستجو کنید.

نظر بدهید لغو پاسخ

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

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

آخرین مطالب

نصب Power Query
فعال کردن Power Query در ورژن های مختلف اکسل
۰۷اسفند۱۳۹۹
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۰۱اسفند۱۳۹۹
مدیریت لینک
مدیریت لینک ها در اکسل
۲۷بهمن۱۳۹۹
جستجو پیشرفته با Wild Card
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲بهمن۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

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

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما