جمع ساعت در اکسل – آموزش ۹ روش کاربردی

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

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

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

ساعت و زمان در اکسل به‌عنوان عدد ذخیره می‌شوند. به این شکل که عدد کامل نشان دهنده روز است و قسمت اعشاری آن، بخش‌های مختلف روز را نشان می‌دهد. مثلا عدد «1» در اکسل نشان دهنده تاریخ 1 ژوئن 1999 است. به این ترتیب، همان طور که گفتیم، عدد «1.5» می‌شود ساعت 12 ظهر روز 1 ژوئن 1999.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

به همین دلیل و برای راحت‌تر شدن محاسبات و نمایش داده‌ها، باید قالب‌بندی سلول‌ها را تغییر دهیم. با تغییر قالب سلول، مقادیر عوض نمی‌شوند، بلکه داده‌ها به شکلی دیگر نمایش داده خواهند شد. برای تغییر قالب‌بندی سلول‌ها، مراحل زیر را دنبال کنید. (همچنین بهتر است بدانید تاریخ شمسی در اکسل از نسخه 2016 به بعد موجود و قابل استفاده است).

تغییر قالب بندی سلول‌ها برای نمایش درست تاریخ و ساعت

اول سلول مد نظر را انتخاب و روی آن راست کلیک کنید. سپس گزینه Format Cells را بزنید تا پنجره تغییر قالب‌بندی باز شود. همچنین می‌توانید ترکیب دو کلید Ctrl+1 روی صفحه کلید را هم به‌کار ببرید. در مثال زیر سلول‌های C5 و C6 را از جدول بالا انتخاب کرده‌ایم.

در منوی تغییر قالب بندی، به زبانه Number رفته و از لیست Category گزینه Time را انتخاب کنید. سپس قالب مورد نظر خود را برگزینید و دکمه OK را بزنید.

• در نهایت نتیجه مانند تصویر زیر خواهد شد.

همچنین می‌توانید قالب‌بندی سلول‌های خود را شخصی‌سازی کنید. برای این کار، سلول را انتخاب کرده و دکمه‌های Ctrl+1 را بزنید. این بار در پنجره باز شده، گزینه Custom را برگزینید و OK را بزنید.

• برحسب انتخابی که کرده‌اید، قالب‌بندی سلول شما تغییر می‌کند. مثال ما به شکل زیر درآمده است:

روش‌های جمع ساعت در اکسل

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

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

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

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

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

1=C5+C6+C7

• با فشردن دکمه Enter، ساعت‌ها با هم جمع می‌شوند و مقدار نهایی را در سلول C8 خواهید دید.

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

در چنین موقعیتی باید قالب‌بندی سلول نتیجه را تعویض کنید. برای این کار، مانند قبل روی سلول کلیک کرده و دکمه‌های Ctrl+1 را بزنید. سپس در زبانه (Number، گزینه Custom را برگزینید. بعد در کادر Type در بالای فهرست قالب‌ها، عبارت زیر را بنویسید و دکمه OK را فشار دهید:

[h]:mm;@

• در نهایت نتیجه مانند تصویر زیر خواهد شد:

محاسبه جمع ساعت در اکسل با تابع SUM

در روش قبلی برای جمع ساعت در اکسل از یک فرمول بسیار ساده استفاده کردیم. در ادامه می‌بینیم که چطور می‌شود از تابع جمع در اکسل (SUM) برای محاسبه مدت کلی و جمع ساعت‌ها استفاده کرد.

• ابتدا فرمول زیر را در سلول نتیجه وارد کنید:

1=SUM(C5:C7)

• بعد از نوشتن فرمول جمع در اکسل کافی است دکمه Enter را بفشارید تا تابع SUM، جمع کلی ساعت‌ها را در بازه C5:C7 (از سلول C5 تا سلول C7) به دست آورد.

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

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

• فرمول زیر را در سلول نتیجه بنویسید:

1=C5-B5

• در نهایت پس از زدن دکمه Enter، نتیجه مانند تصویر زیر خواهد شد. برای کپی کردن فرمول، علامت مثبت (+) سمت چپ پایین سلول را با ماوس گرفته و روی سلول‌های دیگر بکشید.

محاسبه جمع ساعت‌ها در فاصله زمانی بیشتر از 24 ساعت

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

• همچنین مهم است دقت کنید که از قالب زیر برای زمان‌های شروع و پایان استفاده کرده‌ایم.

• برای انجام این کار، فرمول زیر را در سلول نتیجه وارد کنید.

1=(C5-B5)*24

• سپس دکمه (Enter‌) را فشار داده و فرمول را در بقیه سلول‌ها هم کپی کنید. در نهایت، نتیجه مانند تصویر زیر خواهد شد.

در این مثال، برای قالب‌بندی ستون نتایج (یعنی ستون Total Hours) گزینه General را انتخاب کرده‌ایم. به همین دلیل عددهایی با اعشار مانند 25٫5 و 26٫5 در نتیجه دیده می‌شوند. شما می‌توانید قالب‌بندی این سلول‌ها را به شکلی که می‌خواهید تغییر دهید.

• مطالب پیشنهادی برای مطالعه:
  • تبدیل تاریخ میلادی به شمسی در اکسل — راهنمای کاملاً کاربردی
  • جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده + فیلم آموزشی رایگان
  • جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها + فیلم آموزشی
  • کلیدهای میانبر اکسل (Excel Shortcuts) — کامل ترین فهرست (+ دانلود PDF)
  • آموزش جدول در اکسل – نحوه درست کردن + تصاویر مراحل

محاسبه جمع ساعات بین رکوردهای زمانی با استفاده از تابع IF

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

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

کلیک کنید

•  فرمول زیر را در سلول نتیجه بنویسید:

1=IF(B5>C5,C5+1,C5)-B5

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

• در نهایت، دکمه Enter‌ را بزنید تا نتیجه مانند زیر محاسبه شود.

استفاده از تابع MOD اکسل برای جمع ساعت‌ها

مانند روش چهارم، در شرایطی که اختلاف زمانی منفی باشد، می‌توانیم از تابع MOD برای محاسبه مدت کلی استفاده کنیم. در ادامه روش استفاده از این تابع را در مثال توضیح داده‌ایم:

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

1=MOD(C5-B5,1)

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

• در آخر، نتیجه کار مانند تصویر زیر خواهد شد.

محاسبه جمع ساعت در اکسل با تابع TEXT

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

• فرمول زیر را در سلول نتیجه بنویسید:

1=TEXT(C5-B5,"[hh]:mm")

• تابع TEXT نتیجه تفریق را به قالب hh:mm (دقیقه:ساعت) تبدیل می‌کند. کلید (Enter) را بزنید تا نتیجه نهایی را مشاهده کنید.

تابع HOUR‌ اکسل برای محاسبه جمع ساعات

روش دیگری که برای حساب کردن جمع ساعت در اکسل پیش می‌گیریم، استفاده از تابعی به نام HOUR است. این تابع ساعت را به شکل عدد نشان می‌دهد. به این صورت که «0» یعنی ساعت 12 ظهر و «23» یعنی ساعت 11 شب. مراحل زیر، روش کار با تابع HOUR هستند:

• فرمول را در سلول نتیجه وارد کنید:

1=HOUR(C5-B5)

• بعد از فشردن کلید Enter، نتیجه مانند تصویر زیر نشان داده خواهد شد.

تابع NOW برای محاسبه مجموع ساعات سپری شده (از زمانی معین تا حال)

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

• فرمول تابع NOW را در سلول نتیجه وارد کنید:‌

1=NOW()-B5

• پس از فشردن دکمه Enter، نتیجه مانند تصویر زیر خواهد شد.

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

در نهایت، خروجی زیر را تحویل خواهید گرفت.

جمع‌بندی

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