چهار روش تبدیل متن به عدد
تبدیل متن به عدد در اکسل
گاهی اوقات اعداد در اکسل با فرمت متنش نمایش داده میشن و خاصیت عددی ندارند که ممکنه هنگام استفاده این مقادیر در محاسبات و توابع دیگه، با خطا مواجه بشید. این موضوع در شرایط مختلفی رخ میده. مثلا وقتی داده ها را از یک نرم افزار دیگه وارد اکسل می کنیم، یا وقتی که از توابع متنی بر روی اعداد استفاده میکنیم، با این موضوع مواجه می شیم. علت ایجاد این مشکل اینه که اعداد به صورت متن در سلول قرار گرفته. پس برای حل این مشکل باید با روش های تبدیل متن به عدد ، دوباره به عدد تبدیل بشن.
در این بخش، به معرفی چهار روش متفاوت برای تبدیل متن به عدد میپردازیم.
- با استفاده از دستور Error Checking
- با استفاده از دستور text to columns
- با استفاده از دستور paste special
- با استفاده از توابع اکسل
اول- تبدیل متن به عدد با استفاده از دستور Error Checking
یکی از قابلیت های اکسل، امکان بررسی کردن خطا است که وجود محتوای نمایش متنی اعداد رو به شما تذکر میده. این قابلیت، طبق شکل ۱ به صورت یک مثلث کوچک رنگی (نشانگر خطا) در گوشه بالا سمت چپ سلول شما دیده خواهد شد.
شکل ۱- نشانگر خطا در سلول
اگر سلول شما این نشانگر خطا را نمایش بده، شما می تونید از قابلیت بررسی خطا برای تبدیل محتوای نمایش متنی اعداد به مقادیر عددی واقعی استفاده کنید.
برای اینکار مراحل زیر را انجام دهید:
- انتخاب سلول (یا سلولها) شامل مقادیری که می خواید به عدد تبدیل کنید.
با انجام این مرحله، یک نماد هشدار در بالای سلول(ها) ظاهر میشود. اگر ماوس را بر روی نماد هشدار نگه دارید، پیام هشدار به نمایش در می آید (شکل ۲).
شکل ۲- نمایش پیغام خطا
- مطابق شکل ۳ بر روی نماد هشدار کلیک کنید تا منوی بررسی خطا ظاهر بشه.
شکل ۳- مشاهده منو بررسی خطا
- گزینه Convert to Number را برای تبدیل مقادیر به اعداد انتخاب کنید.
برای انجام عملیات بالا، نیاز است مطمئن شوید که گزینه بررسی خطا برای اعداد ذخیره شده به عنوان متن، فعال است یا نه. برای بررسی این موضوضع از مسیر زیر،
File/ Excel Options/ Formulas/
مطمئن شوید که:
- در بخش Error Checking ، گزینه Enable background error checking و
- در بخش Error checking rules ، گزینه Number formatted as text or preceded by an apostrophe تیک خورده باشند.
دوم- تبدیل متن به عدد با استفاده از دستور Text to Columns
یکی از کاربردهای ابزار Text to Columns تبدیل فرمت انواع دادههای اکسلی است. برای استفاده از این ابزار مراحل زیر رو انجام بدید:
- بازه سلول (ها)ی مورد نظر برای تبدیل رو انتخاب کنید (نباید بیشتر از یک ستون باشد).
- در قسمت Data از نوار ابزار اکسل، گزینه Text to Columns رو انتخاب کنید.
با انجام این کار پنجره Convert Text to Columns Wizard باز می شه. در این پنجره:
- مطمئن بشید گزینه Delimited انتخاب شده باشه. سپس بر روی Next کلیک کنید.
- مطمئن بشید که هیچ کدام از گزینه های بخش Delimiter انتخاب نشده باشند، سپس بر روی Next کلیک کنید.
- حالا باید یک فرمت برای ستون داده های خود انتخاب کنید. پس گزینه General را انتخاب و بر روی دکمه Finish کلیک کنید.
شکل۴- تبدیل متن به عدد با استفاده از Text to Columns
سوم- با استفاده از دستور Paste Special
شما میتوانید از دستور Paste Special برای این تبدیل در بازه سلولهای انتخابی خود استفاده کنید.
روشی که در زیر توصیف شده است، مقدار صفر را به هر سلول در بازه انتخاب شده اضافه میکند. این روند، نمایش متنی اعداد را به مقادیر عددی واقعی تبدیل میکند، اما هر سلول که شامل متن غیر عددی است را بدون تغییر باقی میگذارد. برای این کار مراحل زیر رو انجام بدید:
- در یکی از سلول های صفحه گسترده اکسل عدد صفر را وارد کنید.
- سلولی را که شامل صفر است انتخاب کرده و این سلول را کپی کنید.
- سلول (ها) ی مقصد که جنس عدد نیستند و میخواید تبدیل به عدد بشن رو انتخاب کنید.
شکل ۵- مسیر Paste Special
- مطابق شکل ۵ از قسمت Home در نوار ابزار اکسل، گزینه Paste → Paste Special رو انتخاب کنید.
پنجره Paste Special همانند شکل ۶ ظاهر خواهد شد.
شکل ۶- پنجره Paste Special
- در پنجره Paste Special گزینه Add را در قسمت Operation انتخاب و سپس بر روی OK کلیک کنید.
کلید میانبر کپی Ctrl+C است.
کلید میانبر Paste Specialنیز ، Ctrl+Alt+V است.
چهارم- با استفاده از تابع عددی اکسل
تابع Value اکسل، مقادیر متنی را به عدد تبدیل میکند. این تابع هنگامی مفید است که بخواهید مقادیر عددی را از رشته های پیچیده متنی استخراج کنید.
مثالهایی از تابع Value
مثال ۱
ستون A از صفحه گسترده زیر شامل نمایش متنی اعداد است. برای تبدیل مقادیر متنی به عدد، از تابع Value اکسل، در ستون B صفحه گسترده استفاده می شه.
مثال ۲
در مثال زیر، از تابع Value اکسل به همراه تابع Left اکسل برای استخراج مقادیر عددی از رشته های متنی در سلول A1 استفاده شده است.
توجه داشته باشید، اگر متنی که قرار است به عنوان ورودی تابع عددی اکسل قرار بگیرد، قابلیت تبدیل به مقادیر عددی را نداشته باشد، تابع خطای #VALUE! Error را نمایش می دهد.
روش چهارم یا بعبارتی استخراج اعداد از رشته های متنی، با توجه به شرایط داده و الگوی قابل درک بین رشته های متنی بسیار متنوع و گاها پیشرفته و آرایه ای خواهد بود. پس این مثال نمونه ساده ای از استخراج عدد از یک عبارت متنی بود. برای حل حالت های پیشرفته تر و متنوع تر حتما باید روی توابع متنی تسلط کافی رو داشته باشیم.
باسلام تشکرازمطالب یادشده یه ستون دارم که سلولهایی نظیر(ر_الف/۱)الی (ر_الف/۵۰۰)دارم ازچه روشی ایناروتبدیل به عددکنم که موقع استفاده از کاندیشن فرمتینگ(condition formating) به راحتی بتونم ازقسمت بیت وین (beetwen)شماره های موردنظرروفیلترواستخراج کنم درحالت فعلی امکان پذیرنیست .
درود
اعداد رو جدا کنید با استفاده از flash fill بعد فیلتر کنید
اعدادی که در اکسل عدد تشخیص داده نمیشوند را چگونه به عدد تغییر دهم؟
اگر از این چهار روش گقته شده استفاده کنید باید بتونید این کار رو انجام بدید.
سلام وقت بخیر پرسشنامه دادم به صورت آنلاین توی سایت پر کردن و الان فایل اکسلش رو دارم و گزینه های سوالاتی که جواب دادن به حروف فارسیه ولی من برای spssلازم دارم این حروف به عدد تبدیل بشن هیچ کدوم از راه ها رو متوجه نشدم لطفا راهنماییم کنید
درود
اون تبدیل داده با جنس متنی به جنس عددی هست
نه حروف
منظورتون چیه حروف بشه عدد؟مثال بزنید
سلام وقت بخیر
یک شیت اکسل د ارم که یکی از ستونهاش اضافه کاری به ساعت و دقیقه میباشد (۱۲۰:۰۷) میخوام که int این عدد بدست بیارم هر کاری میکنم نتیجه رو نمیده
ممنون میشم راهنمایی بفرمایید
درود
ساعت در اکسل عدده و وقتی Int یک مقدار زمانی رو میگیرید، اول اون مقدار تقسیم بر ۲۴ میشه و بعد int حساب میشه
اگر منظورتون از int در اینجا جدا کردن ساعت از دقیقه است، از تابع hour استفاده کنید
سلام
سپاس از شما
من یک فایل دارم
یک سلول جمع سه مقدار عددی است
وقتی کپی می کنم و سه مقدار عددی را حذف می کنم، ستون جمه FREE میشه چیکارکنم؟
مرسی
درود
منظور REF نیست احیانا؟
چون Free رو نمیدونم چیه
اما REF زمانی اتفاق میفته که مرجع فرمول حذف بشه
سلام خروجی یک نرم افزار حسابداری تاریخ شمسی رو به شکل درست نمایش (۱۴۰۱/۰۱/۰۱) میده اما قابل تشخیص بعنوان تاریخ نیست برای تبدیل سلول به تاریخ حقیقی چه توابعی باید استفاده کرد
سلام
از افزونه های تبدیل تاریخ شمسی به میلادی استفاده کنید.
سلام
من وقتی از MSP خروجی اکسل میگیرم تاریخ هارو ۱۴۰۱ شهریور ۲۰ ۰۸:۰۰ ق.ظ به این صورت نشان میده و با اینکه کل ستون رو Date انخاب میکنم بازم هم به تاریخ تبدیل نمیوشد و باید تک تک سلولهارو به Date تبدیل کرد.ممنون میشم راهنمایی کنید.
درود
باید بررسی بشه ببینید جنس تاریخ داره یا نه (نشونه اینه که اون مقدار باید با تغییر فرمت به General به یک عدد ۵ رقمی تبدیل بشه)
اگر نداره با فرمت تبدیل نمیشه و باید تفکیک ها رو یا با flash fill یا با توابع انجام بدید و بعد به تاریخ تبدیل کنید
گاهی اوقات قبل از اینکه عدد به متن تبدیل شود seprator نقطه بوده ولی الان در سیستم شما seprator / می باشد
و به هیچ یک از روش های بالا نمی توان متن را دوباره تبدیل به عدد کرد .و بسیار گیج کننده است فقط نیازه که seprator را از region دوباره به نقطه تغییر بدهید
some times you need change seprator to”.”
may be before the change to text ,that seprator was “.” but now seprator is”/”
there is errore for change to value
فکر کنم منظورتون اعشار هست.
اعشار بعضیجاها . و بعضی جاها / هست
و الا جداکننده سه رقم سه رقم، که Separator میگیم بهش، من ندیدم جایی نقطه باشه.
سلام من هر ۴ روش رو انجام دادم ولی بازهم اعداد بصورت متن بودن و نمیشود محاسبات روشون انجام داد.
درود
چک کنید , جدا کننده توی اعداد نباشه
د رنهایت هم len بگیرید ببینید با تعداد کاراکتری که میبینید برابری میکنه یا نه. اگر نباشه باید کاراکتر اضافه رو پیدا و حذف کنید
سلام
یک سلول هست که ظاهرا عدد نوشته شده ولی وقتی ضرب در عددی میخوام بشه خطا میده، کارکتر اضافه نداره و راه های بالا هم جواب نداد، جالب اینکه وقتی میخوای سرچ کنی تو قسمت find اون رو میزنی چیزی پیدا نمیکنه ولی وقتی علامت سوال میزنی اون عدد رو میاره برات. میشه کاری کرد که اکسل بتونه اون عدد رو بخونه
درود
قطعا تکسته
همینه با ؟ پیدا میشه تایید میکنه…
از چند روش ترکیبی برای عدد کردن استفاده کنید
در نهایت len حساب کنید ببینید تعدادی که میده، با تعدادی که میبینید یکیه؟
احتمالا نیست
اگر نبود، باید کاراکتر اضافی رو پیدا کنید (که لزوما به چشم هم دیده نمیشه!) یعنی اسپیس نیست