جشنواره تابستانی اکسل پدیا (یک روز تا پایان)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

تابع SUBSTITUTE در اکسل

تابع SUBSTITUTE در اکسل
۵/۵ - (۱۰ امتیاز)

تابع SUBSTITUTE در اکسل تابعی برای جایجایی کارکتر در متن

حتما تا حالا با این موضوع برخورد کردید که بین داده های موجود در اکسل، دنبال یک مورد خاص بگردید. مثلا اون مورد رو پیدا کنید با مورد جدیدی جایگزین کنید. همونطور که میدونید یکی از راه حل های این مسئله، ابزار Find هست. علاوه بر ابزار Find تابعی رو معرفی میکنیم به نام تابع SUBSTITUTE در اکسل هست که همین کار و میکنه یعنی در یک سلول مقداری رو پیدا میکنه و با مقدار جدیدی جایگزین میکنه که یک ویژگی خیلی مهم هم داره که در ادامه به تشریح این تابع می پردازیم.

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

Text: سلولی که در آن جستجو میکنیم.

Old_Text: عبارتی که آن را جستجو میکنیم.

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

[Instance_Num]: آرگومان اختیاری و از جنس عدد هست. در صورتی که خالی بذاریم، همه عبارت های معادل موردی که جستجو میکنیم رو با عبارت جدید جایگزین میکنه. اما اگر عدد بذاریم، مثلا ۳، فقط سومین عبارت مورد نظر رو پیدا میکنه و جایگزین میکنه. این ویژگی هست که ی جاهایی اهمیت این تابع رو خیلی زیاد میکنه.

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

مثال اول

در داده های نشان داده شده در شکل ۱ میخوایم عدد “۱۳” رو با “۱۶” جایگزین کنیم.

داده هایی که میخوایم با داده جدید جایگزین بشه

شکل ۱- تابع SUBSTITUTE در اکسل – داده هایی که میخوایم با داده جدید جایگزین بشه

ممکنه بتونیم راه حل های مختلفی برای این سوال پیدا کنیم. اما در این مقاله میخوایم کاربرد تابع Substitute رو توضیح بدیم. پس برای این کار فرمول زیر رو می نویسیم:

=SUBSTITUTE(A1,”13″,”16″)

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

شکل ۲- تابع SUBSTITUTE در اکسل – جایگزین کردن عبارت ۱۳ با ۱۶

همونطور که می بینید در مثال بالا، این تابع اومد همه ۱۳ ها رو پیدا کرد و با ۱۶ جایگزین کرد. یعنی سال ۱۳۹۷ رو هم به ۱۶۹۷ تبدیل کرد. اما ما میخوایم فقط اولین ۱۳ رو پیدا کنه و جایگزین کنه. اینجاست که آرگومان آخر کاربرد خودشو نشون میده.

تا اینجا کاری که انجام دادیم مشابه ابزار Find بود. که بیاد ۱۳ رو پیدا کنه و با ۱۶ جایگزین کنه. اما وقتی بخوایم ویژه تر جستجو کنیم و مثلا بگیم اولین ۱۳ رو پیدا کن. دیگه با ابزار Find نمیشه. به فرمول زیر دقت کنید:

=SUBSTITUTE(A1,”13″,”16″,۱)

آرگومان آخر رو ۱ گذاشتیم. این یعنی اینکه فقط اولین موردی رو که پیدا کردی با عبارت جدید جایگزین کن و کاری به بقیه موارد نداشته باش.

کاربرد آرگومان آخر تابع Substitute

شکل ۳- تابع SUBSTITUTE در اکسل – کاربرد آرگومان آخر تابع Substitute

تابع Substitute و بطور کلی دسته توابع متنی خیلی توابع پرکاربردی هستن. کاربرد این توابع در ترکیب و فرمول نویسی های پیشرفته خیلی اهمیت پیدا میکنه. سعی کنید به تک تک رگومان های این توابع مسلط باشید و اثرگذاری هر قست رو خوب درک کنید. در دسته توابع متنی به غیر از تابع Substitute تابع Replace هم وجود داره که کار مشابه این تابع انجام میده ولی با عملکردی متفاوت. پیشنهاد میکنیم حتما مطلب آموزش کار با تابع Replace رو مطالعه کنید.

آواتار
127

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

دیدگاه کاربران
  • حسین ۱۳ مرداد ۱۴۰۰ / ۱۱:۵۴ ب٫ظ

    سلام لطفا این فرمول را توضیح دهید.
    LEFT(A1,LEN(A1)-1)* IF(RIGHT(A1)=”B”,10^9,10^6)=

    • آواتار
      حسنا خاکزاد ۲۰ مرداد ۱۴۰۰ / ۱۰:۴۱ ق٫ظ

      سلام
      مقدار سلول A1 رو مشخص نکردید
      ولی بصورت کلی
      اگر داخل سلول A1 مساوی با B باشه، ۱۰ به توان ۹ میرسه و در مقدار کاراکترهای یکی مونده به اخر سلول A1 ضرب میشه!!!!
      که تناقض داره اجزای فرمولتون با هم

      اگه مقدار کاراکتر هست
      پس نمیتونید در یک عدد ضرب کنید!
      به هر حال معنی چیزی که نوشته شده اینه

  • کیوان ۱۳ اردیبهشت ۱۴۰۰ / ۱۲:۵۲ ب٫ظ

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

  • محمد هادی رحیمیان ۱۸ خرداد ۱۳۹۹ / ۷:۳۲ ب٫ظ

    سلام
    وقت بخیر
    ممنون از سایت خوبتون
    من میخوام اطلاعات یک ستون رو که در اون اعداد به صورت ۲.۶M و ۷.۱B نمایش داده میشه به ترتیب به ۲.۶۰۰.۰۰۰ و ۷.۱۰۰.۰۰۰.۰۰۰ تبدیل کنم .
    ممنون راهنمایی بفرمائید .

    • سامان چراغی ۱۹ خرداد ۱۳۹۹ / ۸:۳۵ ق٫ظ

      سلام
      از فرمول زیر استفاده کنید:

      • محمدهادی رحیمیان ۲۱ خرداد ۱۳۹۹ / ۶:۳۴ ق٫ظ

        از لطف شما بی نهایت سپاسگزارم

  • موسوی ۲۴ آذر ۱۳۹۸ / ۷:۵۰ ب٫ظ

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

    • سامان چراغی ۲۴ آذر ۱۳۹۸ / ۱۰:۲۸ ب٫ظ

      سلام، ممنون
      اگر بین این کلمات نقطه اشتراکی وجود داشته باشه که اونا رو نسبت به بقیه کلمات خاص کنه میتونید با استفاده از Find & Replace و Wild Characters این کار رو انجام بدید.

      • موسوی ۲۵ آذر ۱۳۹۸ / ۲:۱۱ ب٫ظ

        بله روشی که فرمودید و انجام میدم ولی بدلیل اینکه تعداد اسامی زیاده ( بعنوان مثال : باید اسامی ۲۰ نفر به تیم کاری شون-مثلا مکانیک- تغییر کنه و همین طور چندین نفر به برق و … ) زمان زیادی صرف میشه و چون بصورت هفتگی از نرم افزار خروجی گرفته میشه می بایست هربار تکرار بشه .
        میخواستم ببینم راهی هست که بشه فرمول ثابتی نوشته بشه که هربار این کار رو تکرار نکنم؟
        مثال مشخص اینکه : A1=علی،رضا،محمد B1=حسن،سعید C1=خسرو
        میخواهم اسامی علی،سعید،خسرو به مکانیک تغییر کنه و اسامی محمد و حسن به برق .

        • سامان چراغی ۲۵ آذر ۱۳۹۸ / ۷:۰۴ ب٫ظ

          بله اگه به صورت روتین این کار رو انجام میدید دو روش وجود داره:
          ۱- استفاده از VBA
          ۲- استفاده از فرمول (این قسمت رو براتون بیشتر توضیح میدم)
          اگر اسم هر نفر رو توی یک سلول بنویسید و جلوی اون نام بخش رو بذارید، کافیه در دیتا اصلیتون از Vlookup استفاده کنید و بخش مربوط رو بیارید.
          اگر اسامی هر بخش رو توی یک سلول بنویسید یکم مسئله پیچیده میشه که باید فرمول نویسی آرایه ای انجام بشه. برای همین پیشنهاد میکنم به صورت بالا اسامی و بخش هاشون رو مشخص کنید.

  • vahidexcelpedia ۱۴ اسفند ۱۳۹۷ / ۸:۱۷ ب٫ظ

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

  • علیرضا ۱۳ بهمن ۱۳۹۷ / ۳:۰۷ ب٫ظ

    سلام
    برای تبدیل عدد به حروف فایل مورد نظر را دانلود و نصب کردم
    طبق دستورات اجرا میکنم
    ولی خطای !VALUE#: نشان می ده
    باید چکار کنم

    • آواتار
      حسنا خاکزاد ۱۳ بهمن ۱۳۹۷ / ۳:۲۳ ب٫ظ

      درود بر شما
      عینا طبق همون مراحل پیش برید، نباید مشکلی باشه.
      مسیر ذخیره add ins هم حروف فارسی نداشته باشه

  • hamedmotlaghlinkedin ۲۳ مرداد ۱۳۹۷ / ۱۱:۵۶ ق٫ظ

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

    • سامان چراغی ۲۹ مرداد ۱۳۹۷ / ۸:۲۸ ب٫ظ

      سلام
      با استفاده از Power Query و Power Pivot

  • سينا1983 ۲۳ بهمن ۱۳۹۶ / ۷:۳۶ ب٫ظ

    سلام خسته نباشید.
    با تابع =row() شماره سطرهام رو نوشتم فقط مشکل اینه که وقتی یک یا چند سطر رو insert میکنم توی یک قسمت فایلم شماره سطرهای تازه insert شده رو نمی نویسه خواستم بدونم میشه کاری کرد که وقتی سطری رو insert کردیم اتوماتیک شماره بخوره.؟
    ممنون از سایت عالی توووووووووووون.

    • آواتار
      حسنا خاکزاد ۲۴ بهمن ۱۳۹۶ / ۹:۴۶ ق٫ظ

      سلام
      Insert نکنید
      بجاش کپی کنید. اگه کپی کنید فرمول هم کپی میشه

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

      https://excelpedia.net/excel-table/

  • جمالی ۹ بهمن ۱۳۹۶ / ۵:۰۷ ب٫ظ

    با سلام و سپاس
    به نظر این حقیر اکسل هم شبیه به ریاضیه. اوایلش ای خوبه. واردش که میشی ۲ شاخه میشه اگه بهش دل ندی و حوصله نکنی که هر چه جلوتر بری زجر آورتر میشه تا حدی که یی هو دفتر و کتاب و جزوه ووووو رو پرت می کنی کنار.
    اما اگه یه مقدار صبوری کنی و بهش دل بدی، هر چی بیشتر توش تعمق کنی لذت بخش تر میشه و دوست داری هی بیشتر و بیشتر بری دنباش و می بینی که هنوزم شیرین و دلچسبه هر چند که خیلی خیلی هم سخت تر و پر محتواتر شده.
    پیشنهاد می کنم زحمت بکشید فرمول های کاربردی رو بصورت یه منظومه تهیه فرمائید و ارائه فرمائین.
    سپاسگزام. جمالی

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان