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

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

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

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

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

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

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

تابع FIND در اکسل

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

FIND(find_text,within_text,[start_num])

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

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

FIND(“m”;”Hamed Ghadimi”;1)

FIND(“m”;”Hamed Ghadimi”;6)

خروجی فرمول اول ۳ و خروجی فرمول دوم ۱۲ میباشد، فرمول اول موقعیت کاراکتر m را در قسمت اسم و فرمول دوم موقعیت m را در قسمت فامیلی نشان میدهد، عدد ۶ موقعیت کاراکتر Space میباشد. حال به جای آرگومان سوم میتوانید از یک تابع FIND دیگر استفاده کنید که به صورت خودکار محل Space (کاراکتر فاصله) را مشخص کند، مانند زیر:

FIND(“m”;”Hamed Ghadimi”;FIND(” “;”Hamed Ghadimi”))

اگر رشته ی جستجو شده در رشته ی متنی مرجع وجود نداشته باشد یا مقدار آرگومان سوم صفر یا عددی بیشتر از تعداد کاراکترهای رشته ی متنی مرجع باشد، خروجی تابع FIND پیام خطای !VALUE# خواهد بود.

تابع SEARCH در اکسل

تابع SEARCH دقیقاً مانند تابع FIND عمل میکند و Syntax مشابهی دارد، با این تفاوت که این تابع Case-Sensitive نیست.

SEARCH(find_text, within_text, [start_num])

تابع REPLACE در اکسل

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

REPLACE(old_text,start_num,num_chars,new_text)

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

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

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

تابع SUBSTITUTE در اکسل

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

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

آرگومان اول رشته ی متنی مرجع میباشد، آرگومان دوم کاراکترهایی از رشته ی متنی مرجع میباشد که قرار است با کاراکترهای جدید جایگزین شوند و آرگومان سوم رشته ی متنی جدید است. حال میخواهیم مانند مثال تابع REPLACE عدد صفر را با ۹۸+ جایگزین کنیم:

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

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

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

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

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

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

تابع BAHTTEXT در اکسل

تابع BAHTTEXT اعداد را به رشته های متنی (با فونت تایلندی یا Thai Text) تبدیل میکند. کاربرد این تابع تا امروز برای من مجهول باقی مانده است.

تابع DOLLAR در اکسل

تابع DOLLAR اعداد را به رشته ی متنی تبدیل میکند و نماد پول (Currency) را به آن اضافه میکند. ساختار این تابع به صورت زیر است:

DOLLAR(number,[decimals])

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

خروجی تابع DOLLAR یک رشته ی متنی است، حال اگر برای تعریف یک عدد به عنوان Currency  از تنظیمات سلول و تغییر نوع محتوای سلول به Currency  استفاده کنید، محتوای سلول عدد باقی میماند.

تابع FIXED در اکسل

تابع FIXED ابتدا عدد مورد نظر را به تعداد ارقام اعشار دلخواه گرد (Round) میکند، و سپس آن را به صورت رشته متنی تبدیل میکند. ساختار این تابع مانند زیر است:

FIXED(number,[decimals],[no_commas])

آرگومان اول عدد مورد نظر و آرگومان دوم تعداد ارقام اعشار مورد نظر است، آرگومان سوم یک پارامتر Boolean است، در صورت TRUE بودن بدین معنی است که از Comma در ساختار عدد استفاده نگردد. آرگومان دوم و سوم اختیاری هستند. به مثال زیر توجه کنید:

FIXED(1000000,0,FALSE)=1,000,000

  • اعداد در اکسل تا ۱۵ رقم میتوانند باشند، اما اعداد اعشار تا ۱۲۷ رقم میتوانند وجود داشته باشند.
  • اگر آرگومان دوم خالی گذاشته شود، دو رقم اعشار در نظر گرفته میشود.
  • اگر آرگومان دوم منفی باشد، عدد تا رقم قبل از اعشار گرد (Round) میشود.
  • آرگومان سوم اگر خالی یا FALSE باشد نتیجه یکسان است.

تابع TEXT در اکسل

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

TEXT(value, format_text)

آرگومان اول عدد وآرگومان دوم فرمت مورد نظر است، به مثال های زیر توجه کنید:

TEXT(9122048458;”+98″&”0″)=+989122048458

TEXT(1000000;”$0.00″)=$1000000.00

TEXT(0.45;”%0.0″&” OFF”)=%45.0 OFF

تابع T در اکسل

تابع T در واقع برای تایید رشته ی متنی بودن یک عدد است و خروجی آن همواره یک رشته متنی میباشد، اگر عدد قرار گرفته به عنوان آرگومان تابع T یک رشته متنی باشد خروجی این تابع دقیقاً معادل ورودی آن است ولی اگر عدد وارد شده یک عدد باشد خروجی تابع T یک رشته متنی خالی (“”) است. ساختار این تابع مانند زیر است:

T(value)

تابع VALUE در اکسل

از تابع VALUE برای تبدیل اعدادی که قبلاً به رشته ی متنی تبدیل شده اند به عدد استفاده میشود. ساختار این تابع به صورت زیر است:

VALUE(text)

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

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

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۲۴ نظر

  1. الناز

    توضیحات خوب بود ولی کاربرد اصلی این فرمولها کجاس؟؟؟؟واینکه اگه عدد من ٢۴۴۶۶۴۴۴۶٧٠٠٠٠٠٠ باشه و بخوام به جای رقم اخر ١بزارم چطور تغییر میکنه؟

    1. حامد قدیمی

      خواهش میکنم، فرمول های متنی برای کار با داده های متنی مورد استفاده قرار میگیرند، اگر داده های عددی نیاز به ویرایش داشته باشند (مثلاً نمایش صفر قبل از عدد) میتوان اعداد را به متن تبدیل کرد و با استفاده از فرمول های متنی ویرایش مورد نیاز را انجام داد. در مورد سوالتون، در اکسل دقت عدد (Number precision) تا ۱۵ رقم است، یعنی اعدادی مانند عدد شما که دارای ۱۶ رقم میباشد خارج از دقت عدد اکسل هستند، بنابراین محاسبات مربوط به آنها و نمایش آنها درست و قابل اتکا نیست، با این تفاسیر، شما باید به لطایف الحیلی سعی کنید اعدادتان از این تعداد ارقام تجاوز نکند، میتوانید عدد خود را به صورت جمع چند عدد کوچکتر در چند سلول نمایش دهید و یا یک مقدار مشخص از تمام اعداد خود کم کنید، این بحث در حیطه ی Excel specifications and limits قرار دارد که در آینده مقالاتی در این مورد در سایت قرار خواهم داد.
      موفق باشید.

      1. رضا

        سلام .میشه جوابی که در مورد سوال اسی دادی (REPLACE و SUBSTITUTE میتوانید استفاده کنید،) یه مثال یا نمونش بنویسی .ممنون میشم ایمیل کنی برام

  2. اسی

    ممنون از مطالبتون. واقعا استفاده بردم و به کارم اومد فقط ی چیزی نتونستم بدونم اونم اینه که مثلا بجای عدد ۱ کلمه سلام و بجای عدد دو کلمه خداحافظ و بای عدد کلمه درود و… بخام تغییر بدم از چه فرمول و تابعی استفاده میشه
    در یک سلولی اعداد ۱ تا انتخاب میشه و در سلول دیگر بجای ۱ سلام و بجای ۲ خداحافظ وارد شود. ممنون میشم کمک کنید

  3. شبنم

    سلام
    یه سوالی داشتم از متخصصین ، اینکه فرمولی میخوام توی اکسل ، دنبال یه کلمه بگرده و شماره ستون اون کلمه رو برام دربیاره .
    مثلا
    اگر حرف aدر ستون b3 باشه …. نتیجه جستجوی حرف a بشه b3
    راهنمایی لطفن…..

    1. حامد قدیمی

      سلام، باید از توابع HLOOKUP و COLUMNاستفاده کنید، با استفاده از تابع COLUMN با پرانتز خالی COLUMN() میتوانید شماره ستون هر سلول را بدست بیاوید، حال کافیست این شماره ستون را در سطر پایینی سطری که میخواهید در آن جستجو کنید بنویسید و با استفاده از تابع HLOOKUP آن را بخوانید، برای خواندن آدرس هم از ترکیب تابع ADDRESS با توابع ROW و COLUMN میتوانید استفاده کنید.

  4. داود

    با سلام
    یه مشکلی دارم خواهشا هر چه زودتر راهنماییم کنید
    میخام یک سلول که داخلش به این صورت
    ۲,۳۷۶,۰۰۳ ریال
    اومده و به صورت متن هست را تبدیل کنم به عدد ولی نمیتونم

    1. حامد قدیمی

      سلام، شما ابتدا فرمولی بنویسید که کلمه ی ریال را پیدا کند (تابع FIND یا REPLACE یا SUBSTITUTE)، بعد از پیدا کردن، آن کلمه را از رشته متنی جدا کند، حالا شما یک عدد دارید که به صورت متن میباشد، برای تبدیل متن به عدد از تابع VALUE میتواند استفاده کنید

  5. داود

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

  6. مهدی

    با سلام و ممنون از شما بابت مطالب ارزشمندتون.
    در مورد جایگزینی سلول ها سوالی داشتم ممنون میشم اگر راهنماییم کنید.
    در ستون A کد هایی نوشته شده متشکل از حروف و اعداد که هر کد ۵ الی ۷ بار تکرار شده و در ستون B همین کد ها تکرار شده که فقط یکبار هرکدام تکرار شده است . که هر سلول آن ارتباط مستقیمی با سلول های ستون C دارند . حالا بنده میخواهم این ارتباط برای ستون A هم بر قرار باشه و در صورت پیدا کردن کد های مشابه ستون A با ستون B ، کدی که روبروی هر ستون B می باشد و در ستون C قرار دارد ، روبروی هر سطر در ستون D قرار گیرد.
    ستون D جوابی است که میخواهم پس از بررسی ارتباط بین ستون های A , B , C روبروی هر سلول ستون A نمایش داده شود .
    A B C D
    الف الف ۲۱د۱ ۲۱د۱
    الف ب ۲ز۲۵ ۲۲د۱
    الف ج ۱ک۲۰ ۲۲د۱
    ج د ۲۱ل۱ ۱ک۲۰
    ب ۲ز۲۵
    ب ۲ز۲۵
    د ۱ک ۲۰

    1. حامد قدیمی

      از تابع vlookup استفاده کنید، یک مقاله ی جداگانه در مورد این تابع در پایگاه دانش نوشته شده، فرمول را در ستون D بنویسید، آرگومان اول تابع یک عضو ستون A مثلاً A1 باشد و آرگومان دوم محدوده ی B1:C3 باشد، آرگومان سوم ۲ و آرگومان چهارم صفر باشد. لطفاً مقاله ی مربوطه را برای جزییات بیشتر مطالعه کنید. موفق باشید.

  7. حمید احدی

    با عرض سلام خمت دوستان عزیز
    من در یک شیت نا مرتب که اطلاعاتم رو از یک فایل HTML
    import کرده ام دنبال تعداد یک عدد خاص می گردم.
    از توابع count و countif هم استفاده کردم ولی جواب صحیح رو نمیگیرم.
    ممنون میشم راهنمایی کنید.
    اطلاعات من از ستون AA شروع میشه تا ستون AZ.
    دستوری که استفاده می کنم:
    (countif(‘sheetname’!aa:az;megadr adadi ke donbalesham)=
    (countif(‘sheet1′!aa:az;209)=
    (sum(countif(‘sheet1′!aa:az;209=

    1. حامد قدیمی

      سلام، فرمول نوشته شده برای انجام کاری که مد نظر دارین مناسب است، در انتقال داده ها از منبعی دیگر به اکسل ممکن است برخی کاراکترهای اضافه هم همراه با داده ها وارد شده باشد که لازم است ابتدا شما داده های خود را با توابعی مانند CLEAN و TRIM مرتب کنید و بعد از اینکه مطمئن شدید همه چیز مرتب است مجدداً تلاش کنید، موفق باشید.

  8. اسماعیل

    با سلام
    من حدود پنج هزار شماره دارم که ۶ کاراکتر حروف فارسی دارد چگونه حروف فار سی را حذف کنم ؟
    شماره ها به شکل زیر میباشد.
    بی نام-۰۹۱۵۴۲۲۲۲۲۵
    با تشکر

    1. حامد قدیمی

      سلام، میتوانید از تابع LEFT یا MID استفاده کنید، برای پیدا کردن کاراکترها هم میتوانید از توابع SEARCH یا FIND استفاده نمایید. اگر تعداد کاراکترهای فارسی همیشه ثابت است و همیشه مثل همین مثال بالا سمت راست رشته قرار دارند، به راحتی با ترکیب تابع LEFT و LEN میتوانید شماره ها را جدا کنید، موفق باشید.

    1. حامد قدیمی

      سلام، چرا باید یک متن مثل “سلام” تبدیل به عدد بشه؟!!! در اکسل گاهی مکن است اعداد در سلول هایی با فرمت متنی قرار داشته باشند، در این حالت با استفاده از توابعی مانند VALUE یا توابع دیگر و همچنین تغییر فرمت سلول ها از متن به عدد میتوان اصالت اعداد را به آنها باز گرداند و بر روی آنها یک عملیات ریاضی انجام داد، موفق باشید.

  9. رامین

    سلام،من دو تا فایل دارم که می خوام کد ملی ویا نام خانوادگی مشترک را پیدا کنم به بیان دیگر دو فایل رو با هم مقایسه کنم یک فایل حاوی ۳۰۰۰ کد ملی ونام خانوادگی است ودیگری حاوی ۲۵۰۰ کدملی ونام خانوادگی،اختلاف دو فایل چطور مشخص می شه؟
    ممنون

    1. حامد قدیمی

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

  10. احسان

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

    1. حامد قدیمی

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

دیدگاهتان را بنویسید؟