عمومی، کاربردی ۷۴ بازدید

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

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

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

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

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

قالب بندی سلول‌ها در اکسل

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

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

روش قالب بندی سلول ها در اکسل

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

انتخاب قالب بندی سلول
  • در نهایت نتیجه مانند تصویر زیر خواهد شد.
سلول‌های قالب بندی شده

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

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

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

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

مطلب پیشنهادی:
قالب بندی سلول در اکسل (Format Cell) — به زبان ساده
قالب بندی سلول در اکسل فقط به منظور زیبا کردن نمایش محتویات صورت نمی‌گیرد. این کار به منظور معنی بخشی به محتویات و توجه کاربر به سلول‌های مهم جدول‌ها است.

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

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

فرمول ساده جمع ساعت در اکسل

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

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

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

جمع ساعت ها بیشتر از 24 ساعت

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

[h]:mm;@

قالب بندی سلول برای جمع بیشتر از 24 ساعت
  • در نهایت نتیجه مانند تصویر زیر خواهد شد:
نمایش جمع بیشتر از 24 ساعت

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

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

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

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

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

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

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

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

محاسبه اختلاف زمانی بیشتر از 24 ساعت
  • همچنین مهم است دقت کنید که از قالب زیر برای زمان‌های شروع و پایان استفاده کرده‌ایم.
قالب بندی سلول برای جمع بیشتر از 24 ساعت
  • برای انجام این کار، فرمول زیر را در سلول نتیجه وارد کنید.
=(C5-B5)*24
فرمول جمع ساعت برای بیشتر از 24 ساعت
  • سپس دکمه (Enter‌) را فشار داده و فرمول را در بقیه سلول‌ها هم کپی کنید. در نهایت، نتیجه مانند تصویر زیر خواهد شد.
نتیجه نهایی جمع بیشتر از 24 ساعت

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

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

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

  •  فرمول زیر را در سلول نتیجه بنویسید:
=IF(B5>C5,C5+1,C5)-B5
جمع ساعت در اکسب با تابع IF

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

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

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

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

  • ابتدا فرمول زیر را در سلول نتیجه وارد کنید.
=MOD(C5-B5,1)
جمع ساعت در اکسل با تابع MOD

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

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

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

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

  • فرمول زیر را در سلول نتیجه بنویسید:
=TEXT(C5-B5,"[hh]:mm")
جمع ساعت با تابع TEXT
  • تابع TEXT نتیجه تفریق را به قالب hh:mm (دقیقه:ساعت) تبدیل می‌کند. کلید (Enter) را بزنید تا نتیجه نهایی را مشاهده کنید.
نتیجه جمع ساعت با تابع TEXT

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

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

  • فرمول را در سلول نتیجه وارد کنید:
=HOUR(C5-B5)
جمع ساعت با تابع HOUR
  • بعد از فشردن کلید Enter، نتیجه مانند تصویر زیر نشان داده خواهد شد.
نتیجه جمع ساعت با تابع HOUR

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

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

  • فرمول تابع NOW را در سلول نتیجه وارد کنید:‌
=NOW()-B5
جمع ساعت با تابع NOW
  • پس از فشردن دکمه Enter، نتیجه مانند تصویر زیر خواهد شد.
نتیجه جمع ساعت با تابع NOW

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

قالب بندی سلول برای تابع NOW

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

نتیجه قالب بندی سلول برای تابع NOW

جمع‌بندی

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

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

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

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