گاهی اوقات به علت کپی و پیست کردن اعدادی مثل قیمت و تاریخ در اکسل، این اعداد به صورت متن ساده پیست می‌شوند و محاسبه امکان‌پذیر نیست. در این صورت باید ابتدا سلول‌هایی با فرمت متن ساده را به سلول‌های حاوی عدد تبدیل کرد و سپس محاسبات لازم را انجام داد.
در این مقاله با مثالی ساده روش تبدیل ماه و روز از حالت Text به حالت عددی را در نرم‌افزار Excel بررسی می‌کنیم. با ما باشید.
بیشتر افراد تاریخ را در اکسل به صورت روز و ماه و سال که با . یا - جدا شده تایپ میکنند. ممکن است برخی از / برای جدا کردن روز و ماه و سال استفاده کنند، به خصوص در زبان فارسی که به جای . از / استفاده می‌شود. در هر صورت ممکن است به دلیل کپی و پیست کردن غیراصولی یا بی‌دقتی حین تایپ کردن تاریخ، عددها به صورت متن ساده در سلول قرار گرفته باشد. در این صورت Excel نمی‌تواند کارهای ساده‌ای مثل اختلاف بین دو تاریخ را محاسبه کند!
در ادامه چند حالت مختلف و چند ترفند برای رفع این مشکل را مرور می‌کنیم.
جایگزین کردن . با / برای تبدیل متن به تاریخ
یک ترفند ساده برای رفع مشکل این است که از روش جستجو و جایگزین کردن یا به زبان دیگر Find ad Replace استفاده کنید و به جای علامت . از / استفاده کنید. برای این کار ابتدا سلول‌های موردنظر را با کلیک و درگ یا با نگه داشتن Ctrl و کلیک انتخاب کنید.
سپس در نوار ابزار بالای صفحه و در تب Home روی Find & Select و سپس Replace کلیک کنید.
شورت‌کات جستجو و جایگزین کردن در اکسل، Ctrl + H است.
اکنون در فیلد Find what عبارت . و در فیلد بعدی که Replace with است، / را تایپ کنید. در نهایت روی Replace All کلیک کنید تا جایگزین کردن سریعاً انجام شود.
ممکن است با این راهکار ساده، تاریخ‌ها به عنوان تاریخ شناخته شود. بسته به تنظیمات سلول‌ها، اکسل استایل نمایش تاریخ را تغییر می‌دهد.
استفاده از تابع Substitute و VALUE برای جایگزین کردن و تبدیل به تاریخ
ممکن است روش قبلی برای فایل ثابت مناسب باشد اما اگر فایلی مرتباً تغییر می‌کند و عبارت‌های جدیدی در آن تایپ می‌شود، بهتر است از تابع SUBSTITUTE استفاده کنید تا جایگزین کردن به صورت خودکار انجام شود.
به عنوان مثال اگر بخواهید در سلولی مقدار سلول A2 را با جایگزین کردن . با / قرار بدهید، می‌توانید از فرمول زیر استفاده کنید:
=VALUE(SUBSTITUTE(A2,".","/"))
با توجه به اینکه تابع SUBSTITUTE برای کار با عبارت‌های متنی طراحی شده، خروجی آن یک عبارت متنی است و نه عبارت عددی. لذا در فرمول فوق از تابع VALUE برای تبدیل عبارت به عدد استفاده شده است. همان‌طور که در تصویر زیر مشاهده می‌کنید، در مورد مثال ساده‌ی ما، خروجی این فرمول یک عدد است که فرمت مناسبی ندارد و ظاهراً تاریخ نیست! اما در حقیقت تاریخ است و مقادیر معادل تعداد روزهای پس از تاریخ مرجع است.
اما در حقیقت اگر فرمت سلول را تغییر بدهید، مشکل رفع می‌شود. از تب Home و گزینه‌ی Number Format استفاده کنید و نوع فرمت را Short Date یا Long Date انتخاب کنید.
در این صورت عددها به شکل تاریخ نمایش داده می‌شود.
تبدیل کردن سال و ماه و روز به هم‌پیوسته به تاریخ
ممکن است اشتباهاً تاریخ‌ها به صورت سال و ماه و روز چسبیده تایپ شده باشد و بین این موارد علامتی مثل . یا / تایپ نشده باشد! مثل تصویر زیر:
در این صورت چه باید کرد؟ یک ترفند ساده این است که ابتدا سال و ماه و روز را جدا کنید و در سه ستون متفاوت قرار بدهید. لذا پس از ستون تاریخ، دو ستون خالی ایجاد کنید. به عبارت دیگر روی عنوان ستون حاوی تاریخ راست‌کلیک کنید و گزینه‌ی Insert را انتخاب کنید.
برای تبدیل کردن ستون تاریخ به سه ستون مجزا، از گزینه‌ی Text to Columns در تب Data استفاده کنید.
قبلاً به معرفی کامل این ابزار جالب و بسیار مفید اکسل پرداختیم:
ابتدا ستون حاوی تاریخ را انتخاب کنید. با کلیک روی گزینه‌ی Text to Columns، پنجره‌ای نمایان می‌شود. در اولین و دومین مرحله روی Next کلیک کنید. در مرحله‌ی سوم گزینه‌ی Date را انتخاب کنید و از منوی کرکره‌ای روبروی آن، فرمت تاریخ را انتخاب کنید. Y مخفف سال است و M مخفف ماه است و D مخفف روز. بنابراین اگر ابتدا سال و سپس ماه و روز در سلول‌ها تایپ شده، فرمت انتخابی YMD خواهد بود.
با کلیک روی Finish ستون حاوی تاریخ به سه ستون مجزا که حاوی سال و ماه و روز است، تبدیل می‌شود. می‌توانید از تابع Date برای ترکیب کردن این موارد و ایجاد تاریخ استفاده کنید. قبلاً در مقاله‌ای به شیوه‌ی استفاده از این تابع پرداختیم:
و اما راهکار بعدی که نیازی به سه ستون و در واقع دو ستون خالی پس از ستون تاریخ ندارد! استفاده از فرمول زیر است:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
فرمول فوق از توابع RIGHT و LEFT و MID برای جدا کردن ۲ عدد سمت راست، ۲ عدد وسط و ۴ عدد سمت چپ عبارتی که در سلول A2 تایپ شده، استفاده می‌کند. به عنوان مثال فرمول زیر ۲ عدد سمت راست که شماره‌ی روز است را جدا می‌کند:
=RIGHT(A2,2)
در نهایت سه عدد استخراج شده از سلول A2 با تابع Date ترکیب می‌شود و تاریخ ساخته می‌شود. در نهایت می‌توانید سلول B2 را کپی کرده و در سلول‌های زیر آن پیست کنید. راهکار ساده‌تر کلیک روی مربع کوچک سمت راست و پایین سلول B2 و درگ کردن آن به سمت پایین است. نتیجه محاسبه تاریخ در سلول‌های بعدی است:
بنابراین با هر دو روش اشاره شده می‌توانید تاریخ که با فرمت متن ساده و به هر شکلی که تایپ شده را به تاریخ عددی تبدیل کنید.
تفاوت تابع DATEVALUE و VALUE در اکسل
گاهی اوقات مشکل پیچیده‌تر است و تاریخ با فرمت‌های عجیب و به صورت متن ساده تایپ شده است. به عنوان مثال برای جدا کردن روز و ماه و سال از - استفاده شده یا پس از تاریخ، ساعت و دقیقه هم ذکر شده است! به چند نمونه‌ی زیر توجه کنید:
برای این حالت‌ها هم می‌توانید متنی که اشتباه تایپ شده را به تاریخ عددی تبدیل کنید. تابعی به اسم DATEVALUE این کار را ساده می‌کند و در واقع کار آن تبدیل کردن متن به تاریخ است. البته تابع VALUE نیز مشابه همین کار را انجام می‌دهد اما تفاوت‌هایی وجود دارد.
کار با تابع DATEVALUE بسیار ساده است. کافی است آدرس سلول حاوی متن را به آن بدهید تا متن را به تاریخ تبدیل کند. به عنوان مثال اگر سلول حاوی متن، A2 است، از فرمول زیر استفاده کنید:
=DATEVALUE(A2)
با پیست کردن سلول حاوی فرمول فوق در سلول‌های بعدی، مقدار سلول‌های A3 و A4 و غیره به تاریخ تبدیل می‌شود اما همان‌طور که در تصویر زیر مشاهده می‌کنید، فرمت عدد می‌بایست تغییر کند.
همان‌طور که در تصویر فوق مشاهده می‌کنید در سلول ردیف ۴ که ساعت ذکر شده هم تابع DATEVALUE درست عمل کرده و در واقع بخش ساعت و دقیقه را حذف کرده است. اگر از تابع VALUE استفاده کنید، نتیجه عجیب می‌شود:
در واقع بخش اعشاری با در نظر گرفتن ساعت و دقیقه محاسبه شده است.
howtogeekسیاره‌ی آی‌تی