در این آموزش یاد میگیرید چگونه ساعت کاری و زمانهای بیشتر از 24 ساعت را بهدرستی در اکسل جمع بزنید.
کار با زمان و ساعت در اکسل، یکی از چالشهای رایج برای کاربران است. مخصوصاً زمانی که بخواهید ساعت کاری، مجموع زمان پروژهها یا بازههای زمانی بیشتر از 24 ساعت را محاسبه کنید. اگر فرمت سلولها و فرمولها را درست انتخاب نکنید، نتیجه نهایی اشتباه نمایش داده میشود. در این آموزش، بهصورت کامل با نحوه جمع ساعت در اکسل آشنا میشوید و تمام حالتهای کاربردی را بررسی میکنیم.
فهرست مطالب
قبل از هر کاری باید مطمئن شوید که ساعتها با فرمت درست وارد شدهاند. اکسل ساعت را بهصورت بخشی از یک شبانهروز در نظر میگیرد. فرمت استاندارد ساعت به شکل hh:mm یا hh:mm:ss است.
برای تنظیم فرمت ساعت:
اگر فرمت سلول اشتباه باشد، حتی درستترین فرمول هم نتیجه درستی نمیدهد.
در سادهترین حالت، اگر چند ساعت کمتر از 24 ساعت دارید، میتوانید از تابع SUM استفاده کنید. کافی است ساعتها در چند سلول وارد شده باشند.
مثلاً اگر ساعتها در سلولهای A1 تا A5 قرار دارند، فرمول به شکل زیر است:
=SUM(A1:A5)در این حالت، اکسل مجموع ساعتها را محاسبه میکند. اما اگر حاصل جمع از 24 ساعت بیشتر شود، نتیجه بهدرستی نمایش داده نمیشود. برای این حالت باید سراغ تنظیمات پیشرفتهتر برویم.
یکی از مشکلات رایج، جمع ساعتهایی است که مجموع آنها بیشتر از 24 ساعت میشود. بهطور پیشفرض، اکسل بعد از 24 ساعت دوباره از صفر شروع میکند.
برای حل این مشکل:
[h]:mm یا [h]:mm:ss را انتخاب کنید.با این کار، اکسل تعداد کل ساعتها را بدون ریست شدن نمایش میدهد. این روش برای محاسبه مجموع زمان پروژهها یا شیفتهای کاری بسیار کاربردی است.
برای محاسبه ساعت کاری، معمولا با چند بازه زمانی سروکار داریم. مثلا ساعت شروع و پایان کار. در این حالت ابتدا باید اختلاف زمان را محاسبه کنید. اگر ساعت شروع در A1 و ساعت پایان در B1 باشد، فرمول به شکل زیر است:
=B1-A1پس از محاسبه ساعت کاری هر روز، میتوانید همه نتایج را با تابع SUM جمع بزنید. حتماً فرمت نهایی را روی [h]:mm تنظیم کنید تا مجموع دقیق نمایش داده شود.
اکسل ساعت و دقیقه را همزمان پشتیبانی میکند. کافی است زمانها بهصورت صحیح وارد شوند. مثلا 02:45 یا 01:30. در این حالت هم فرمول SUM بدون مشکل کار میکند. اگر مجموع دقیقهها از 60 عبور کند، اکسل بهصورت خودکار آن را به ساعت تبدیل میکند. فقط فراموش نکنید که فرمت سلول نتیجه مناسب باشد.
برای جمع و تفریق ساعت، نیازی به تابع خاصی ندارید. اکسل با عملگرهای ریاضی ساده هم این کار را انجام میدهد.
مثلا:
=A1+B1=B1-A1در تفریق ساعت، اگر زمان پایان کمتر از زمان شروع باشد، ممکن است نتیجه منفی شود. در این شرایط باید از تنظیمات فرمت یا فرمولهای کمکی استفاده کنید تا نتیجه قابل فهم نمایش داده شود.
در بسیاری از فایلهای حضور و غیاب، ممکن است برخی سلولها خالی باشند یا داده ناقص داشته باشند. در این حالت، اگر مستقیم اختلاف زمان را محاسبه کنید، نتیجه خطا خواهد بود. برای جلوگیری از این مشکل میتوانید از تابع IF استفاده کنید.
فرض کنید:
فرمول محاسبه به این شکل است:
=IF(AND(A2<>"",B2<>""),B2-A2,"")این فرمول بررسی میکند که هر دو سلول خالی نباشند. اگر داده وجود داشته باشد، اختلاف زمان محاسبه میشود. در غیر این صورت، سلول خالی میماند.
پس از محاسبه ساعت هر ردیف، میتوانید با تابع SUM مجموع کل ساعات را محاسبه کنید. این روش برای جمع زدن ساعت کاری در اکسل در فایلهای واقعی بسیار کاربردی است.
گاهی هنگام جمع و تفریق ساعت در اکسل با مشکل نمایش زمان منفی یا عبور از نیمه شب مواجه میشویم. در این شرایط، تابع MOD راهحل مناسبی است. اگر ساعت شروع 22:00 و ساعت پایان 06:00 باشد، فرمول ساده B2-A2 نتیجه منفی میدهد. برای اصلاح آن از فرمول زیر استفاده کنید:
=MOD(B2-A2,1)عدد 1 نشاندهنده یک شبانهروز کامل است. تابع MOD باعث میشود نتیجه همیشه در بازه 0 تا 24 ساعت باقی بماند. این روش برای شیفتهای شبانه بسیار مفید است.
گاهی میخواهید نتیجه جمع ساعت در اکسل را بهصورت متنی و با قالب دلخواه نمایش دهید. در این حالت میتوانید از تابع TEXT استفاده کنید. مثلا اگر مجموع ساعات در سلول C10 باشد:
=TEXT(C10,"[h]:mm")این فرمول نتیجه را با فرمت دلخواه نمایش میدهد. توجه داشته باشید که خروجی تابع TEXT متن است و دیگر نمیتوان روی آن عملیات ریاضی انجام داد. از این روش معمولاً در گزارشهای مدیریتی استفاده میشود.
تابع HOUR فقط بخش ساعت را از یک مقدار زمانی استخراج میکند. ساختار آن به شکل زیر است:
=HOUR(A1)اگر مقدار A1 برابر 02:45 باشد، نتیجه عدد 2 خواهد بود. اگر بخواهید فقط مجموع ساعتها را بدون دقیقه محاسبه کنید، میتوانید ابتدا زمانها را جمع بزنید و سپس روی نتیجه از تابع HOUR استفاده کنید. البته توجه داشته باشید که HOUR فقط عدد بین 0 تا 23 را برمیگرداند. بنابراین برای مجموع بیشتر از 24 ساعت مناسب نیست، مگر این که ابتدا فرمت سلول را به شکل صحیح تنظیم کرده باشید.
تابع NOW زمان و تاریخ فعلی سیستم را نمایش میدهد. این تابع برای محاسبه مدت زمان سپری شده از یک تاریخ یا ساعت مشخص کاربرد دارد. فرض کنید زمان شروع در A1 ثبت شده است. برای محاسبه مدت زمان سپری شده تا این لحظه از فرمول زیر استفاده کنید:
=NOW()-A1پس از وارد کردن فرمول، باید فرمت سلول را روی [h]:mm تنظیم کنید تا مجموع ساعتهای سپری شده بهدرستی نمایش داده شود. تابع NOW بهصورت خودکار بهروزرسانی میشود. بنابراین هر بار فایل را باز کنید، مقدار جدید محاسبه خواهد شد.
در کار با ساعتها چند نکته مهم وجود دارد. اول این که همیشه فرمت سلولها را بررسی کنید. دوم این که برای زمانهای بیشتر از 24 ساعت، حتماً از فرمت [h] استفاده کنید. همچنین از وارد کردن ساعت بهصورت متن خودداری کنید، چون اکسل آن را محاسبه نمیکند.
جمع ساعت در اکسل، اگر با شناخت درست از ساختار زمان انجام شود، کار پیچیدهای نیست. مهمترین نکته، تنظیم صحیح فرمت سلولها است. برای زمانهای بیشتر از 24 ساعت باید از فرمت [h]:mm استفاده کنید. در شرایط پیشرفتهتر میتوانید از توابعی مانند IF، MOD ،TEXT ،HOUR و NOW کمک بگیرید. با یادگیری این تکنیکها، محاسبه ساعت کاری، شیفتهای شبانه و مجموع زمان پروژهها بسیار دقیق و ساده انجام میشود.
[h]:mm تنظیم کنید.
در آیدت،
فایلهای
خود را به فروش بگذارید و یا
مقالاتتان
را منتشر کنید👋
پاسخ ها