استخراج تعداد دقایق

در مثال مشابه قصد داریم تعداد دقایق موجود در داده زمانی را به صورت داده‌ای جداگانه داشته باشیم برای این کار می‌توانیم به سادگی داده‌های موجود در ستون A را در ۱۴۴۰ ضرب کنیم:

=A2*1440

اگر قصد داشته باشید تعداد دقایق کامل را نیز محاسبه کنید، می‌توانید از تابع INT کمک بگیرید:

=INT(A2*1440)

استخراج تعداد ثانیه‌ها

به طور مشابه در مثال زیر می‌توانیم با ضرب داده‌های زمانی ستون A در ۸۶۴۰۰ تعداد ثانیه‌ها را طبق فرمول زیر محاسبه کنیم:

=A2*86400

یادگیری ترفندهای محاسباتی کاربردی اکسل

اکسل برنامه بسیار جامعی است و با یادگیری تعدادی ترفند و اصول پایه، می‌توانید پاسخ تمام سوالات و نیاز‌های ابتدایی خود را در اکسل بیابید. فرادرس فیلم‌های آموزشی مفصل و کاملی برای اکسل تهیه کرده است. به کمک این فیلم‌ها ایده‌های متنوعی برای حل مسائل مدیریت داده بدست خواهید آورد:

اگر مایلید تمامی فیلم‌های آموزشی اکسل فرادرس را یکجا مشاهده کنید، روی لینک زیر کلیک کنید:

۳. استفاده از تابع Convert برای تبدیل زمان به عدد

تابع Convert نیز می‌تواند عملیاتی مشابه را روی زمان انجام دهد و تعداد ثانیه‌ها دقیقه‌ها و ثانیه‌ها را از یک داده زمانی استخراج کند. سینتکس تابع Convert به صورت زیر است:

CONVERT(number,from_unit,to_unit)

آرگمان اول عددیست که قصد داریم واحد آن را تغییر دهیم آرگمان دوم واحد فعلی عدد است و آرگمان سوم واحدی است که قصد داریم تغییر واحد به آن انجام شود.

در مثال زیر با استفاده از این فرمول تعداد ساعات موجود در داده زمانی ستون A را استخراج کرده‌ایم:

=CONVERT(A2, "day", "hr")

با استفاده از فرمولی مشابه، این بار تعداد دقایق وجود در داده زمانی ستون A را محاسبه می‌کنیم:

=CONVERT(A2, "day", "mn")

در نهایت با استفاده از فرمول زیر می‌توان تعداد ثانیه‌های موجود در داده زمانی را استخراج کرد:

=CONVERT(A2, "day", "sec")

۴. استفاده از توابع زمانی برای تبدیل ساعت به عدد در اکسل

با استفاده از توابع زمانی، داده را به ساعت دقیقه و ثانیه تجزیه می‌کنیم. ابتدا به معرفی این سه تابع می‌پردازیم:

• HOUR(serial_number)

   : تابع HOUR ساعت موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی این تابع عددی از 0 (نشان دهنده ۱۲ نیمه شب) تا 23 (نشان دهنده ۱۱ شب) است. تنها آرگومان این تابع، داده‌ای از جنس زمان است که می‌تواند در فرمت‌های زیر باشد:

  • داده متنی (Text String) داخل دو “

     ، مثل « “6:32 PM”

     »

  • عددی اعشاری مثل 0.78125 که نشان دهنده ساعت 6:45 عصر است. در این فرمت ۲۴ ساعت شبانه‌روز معادل «1» و سایر ساعات کسری از یک در نمایش عدد اعشاری هستند.
  • خروجی سایر توابع مثل TIMEVALUE(“6:45 PM”)
• MINUTE(serial_number)

   : تابع MINUTE، دقیقه موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی می‌تواند عددی از 0 تا 59 باشد. آرگومان این تابع مشابه تابع HOUR مقدار می‌پذیرد. به عنوان مثال اگر روی « “6:32 PM”

   » تابع MINUTE را اعمال کنیم، عدد «32» برگردانده می‌شود.

• SECOND(serial_number)

   : تابع SECOND، ثانیه موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی می‌تواند عددی از 0 تا 59 باشد. آرگومان این تابع مشابه تابع HOUR مقدار می‌پذیرد. به عنوان مثال اگر روی « “6:32:22 PM”

   » تابع SECOND را اعمال کنیم، عدد «22» برگردانده می‌شود.

با استفاده از ترکیب این توابع در فرمول زیر، می‌توان تعداد ساعت‌های موجود در داده زمانی را استخراج کرد:

=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600

فرمول زیر نیز با ترکیب توابع زمانی تعداد دقایق موجود در داده را استخراج می‌کند:

=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60

در نهایت این فرمول نیز تعداد ثانیه موجود در داده زمانی را با استفاده از ترکیب توابع زمانی محاسبه می‌کند:

=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)

جداسازی ساعت، دقیقه و ثانیه

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

• با استفاده از تابع INT تعداد روزهای موجود در داده زمانی را استخراج می‌کنیم.
• با استفاده از توابع زمانی، ساعت، دقیقه و ثانیه را از داده استخراج می‌کنیم.
• مقادیر استخراج شده را در کنار هم در یک سلول با توضیح نمایش می‌دهیم.

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

=B4-$B$1

می‌توانید زمان حال را با تابع ()NOW نیز نشان دهید.

=B4-NOW()

حال اختلاف زمانی به دست آمده را با استفاده از فرمول زیر به روز، ساعت، دقیقه و ثانیه تجزیه می‌کنیم:

=INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"

همانطور که مشاهده می‌کنید مقدار صفر نیز به عنوان خروجی در بعضی از سلول‌ها نوشته شده است. برای مثال در سلول D6 صفر روز تا شروع رویداد باقی مانده است. می‌توانیم با استفاده از تابع شرطی IF به سادگی مقادیر صفر را از نتیجه حذف کنیم:

=IF(INT(C4)>0, INT(C4)&" days, ","") & IF(HOUR(C4)>0, HOUR(C4) & " hours, ","") & IF(MINUTE(C4)>0, MINUTE(C4) & " minutes and ","") & IF(SECOND(C4)>0, SECOND(C4) & " seconds","")

تبدیل ساعت به داده متنی

برای بسیاری از محاسبات و تحلیل‌ها نیاز است از رشته متنی برای تعریف داده استفاده کنیم. فرض کنید حجمی از داده زمانی دارید، اما نیاز دارید این داده‌ها را به فرمت متنی تبدیل کنید. اگر فرمت این سلول‌ها را به سادگی از زمان به متن تغییر دهید داده زمانی تبدیل به عددی اعشاری می‌شود. در واقع برای تعریف داده زمانی به عنوان داده متنی نیاز است از تابع TEXT استفاده کنید، برای مثال تصویر زیر را در نظر بگیرید. در این جدول از داده‌های زمانی محتوای ستون B، C وD هر چند نمایش زمانی دارند، اما به عنوان داده متنی برای اکسل تعریف شدند. آرگمان اول تابع TEXT زمان مد نظر و آرگمان دوم فرمت نمایش دلخواه شما را نشان می‌دهد.

فیلم‌های آموزشی مرتبط فرادرس

علاوه بر فیلم‌هایی که تا اینجا معرفی شدند، فیلم‌های آموزشی فرادرس زیر به شما کمک می‌کنند تا درک کامل‌تری از مفاهیم آموزشی پیدا کنید که در این مطلب آورده شده‌اند:

جمع‌بندی

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