تفریق ساعت در اکسل – فرمول و نحوه محاسبه به زبان ساده

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

۱. تفریق زمان در اکسل با استفاده از تابع Time

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

تفریق دقیقه و ساعت

فرض کنید قصد داریم مدت زمان دو ساعت و ۳۰ دقیقه را از زمان وارد شده در سلول A1 در تصویر زیر کم کنیم. برای این کار می‌توانیم از تابع Time استفاده کنیم. آرگومان‌های این تابع به ترتیب نشان دهنده ساعت، دقیقه و ثانیه هستند و هر آرگومان عددی بزرگتر از ۰ را می‌پذیرد. با استفاده از فرمول زیر و به‌کارگیری توابع HOUR و MINUTE زمان وارد شده در سلول A1 را به ساعت و دقیقه تجزیه می‌کنیم و سپس ۲ را از آرگومان ساعت و ۳۰ را از آرگومان دقیقه کم می‌کنیم. فرمول زیر را در سلول B1 وارد می‌کنیم تا نتیجه در این سلول نمایش داده شود. دقت کنید آرگومان سوم فرمول را خالی می‌گذاریم چرا که زمان وارد شده در سلول A1 ثانیه ندارد. با خالی گذاشتن آرگومان سوم، ثانیه صفر در نظر گرفته می‌شود.

=TIME(HOUR(A1)-2,MINUTE(A1)-30,)

همانطور که در تصویر مشاهده می‌کنید نتیجه در فرمت زمانی با پسوند AM نمایش داده می‌شود. برای تغییر این فرمت نمایش نیاز است فرمت این سلول را تغییر دهیم. ابتدا سلول B1 را انتخاب کنید و سپس با زدن Ctrl+1 روی کیبورد یا راست کلیک روی سلول، پنجره «Format Cells» را باز کنید. در بخش «Category» روی «Time» کلیک کنید و فرمت زمانی مد نظر را از بخش «Type» انتخاب کنید.

با زدن OK مشاهده خواهید کرد که نمایش نتیجه در سلول B1 نیز تغییر خواهد کرد.

تفریق ثانیه

حال مثال دیگری را در نظر می‌گیریم که شامل ثانیه نیز باشد. برای مثال فرض کنید از داده وارد شده در سلول A1 که شامل ثانیه است، قصد داریم ۱۰ ثانیه کم کنیم. مشابه مثال قبل از فرمول Time ستفاده می‌کنیم و از آرگومان سوم یا آرگومان ثانیه عدد ۱۰ را کم می‌کنیم.

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1)-10)

برای نمایش نتیجه نیز کافیست فرمت سلول B1 را به نمایش زمان با ثانیه تغییر دهیم.

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

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

• فیلم آموزش ترفند‌های کاربردی اکسل فرادرس
• فیلم آموزش رایگان اکسل در ۱۸۰ دقیقه فرادرس
• آموزش رایگان ترفندهای اکسل برای ناشنوایان فرادرس

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

• مجموعه فیلم‌های آموزشی اکسل از مقدماتی تا پیشرفته فرادرس

۲. تفریق زمان با استفاده از تابع SUM

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

برای این کار نیاز است هر یک از سلول‌های B2 تا B4 را از سلول B5 کم کنیم. برای این کار فرمول زیر را در سلول B6 وارد کنید تا نتیجه در این سلول نمایش داده شود.

=SUM(B5,-B4,-B3,-B2)

این فرمول داده‌های موجود در سلول‌های B2 تا B5 را با یکدیگر جمع می‌زند. با قرار دادن نشانگر تفریق قبل از B2 ،B3 و B4 عملیات جمع به تفریق تبدیل می‌شود و نتیجه تفریق نمایش داده می‌شود.

نمایش بازه زمانی بیش از ۲۴ ساعت در اکسل

در حالت پیش فرض تمامی عملگرها و فرمول‌ها در اکسل، زمان را در ساعتی کوچکتر از ۲۴ نشان خواهند داد. به این معنا که اگر قصد نمایش یک بازه زمانی را دارید که از ۲۴ ساعت بیشتر است، به طور خودکار اکسل ۲۴ ساعت از آن به دفعات کم می‌کند تا عددی کوچکتر از ۲۴ حاصل شود و سپس نتیجه را نمایش می‌دهد. برای اصلاح این حالت نیاز است فرمت سلول را تغییر دهید. ابتدا روی سلول‌ مدنظر کلیک کنید و پنجره Format Cells را باز کنید. در پنجره Format Cells در بخش Category روی Custom بزنید و در پایین لیست Typeروی h:mm;@ کلیک کنید و سپس اوکی را بزنید. با این کار نتیجه به صورت یک بازه زمانی نشان داده می‌شود که می‌تواند بیشتر از ۲۴ ساعت نیز باشد.

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

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

در این مثال به سادگی زمان شروع را از زمان پایان کم کنیم.

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

زمان شروع و پایان در دو روز مختلف به این معنی است که ساعت شروع بزرگ‌تر از ساعت پایان کار باشد. برای محاسبه اختلاف ساعت در این حالت نیاز است به ساعت پایان ۲۴ ساعت اضافه شود تا تفریق به درستی صورت پذیرد. این ۲۴ ساعت می‌تواند در فرمت اعشاری یعنی عدد ۱ به ساعت پایان اضافه شود و سپس تفریق صورت پذیرد.

با استفاده از تابع IF می‌توان هر دوی این حالات را برای محاسبه اختلاف زمانی لحاظ کرد. در این مثال فرمول زیر را در سلول C2 و C3 وارد می‌کنیم:

=IF(B3>=A3,B3-A3,B3+1-A3)

بر اساس فرمول اگر زمان شروع در ۲۴ ساعت عقب‌تر از زمان پایان باشد به سادگی زمان شروع از زمان پایان کم می‌شود و اگر زمان شروع در ۲۴ ساعت جلوتر از زمان پایان باشد، آرگومان سوم اجرا می‌شود، یعنی ابتدا به زمان پایان یک واحد یا ۲۴ ساعت اضافه می‌کنیم و سپس زمان شروع را از آن کم می‌کنیم.

زمان در فرمت عدد اعشاری

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

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

یادگیری تبدیل تاریخ ها با فرادرس

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

• فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل فرادرس
• فیلم آموزش رایگان تبدیل تاریخ شمسی به میلادی در اکسل فرادرس
• فیلم آموزش رایگان تبدیل تاریخ میلادی به شمسی در اکسل فرادرس
• فیلم آموزش رایگان قالب بندی در اکسل ۲۰۱۹ فرادرس

جمع‌بندی

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