فرادرسنحوه محاسبه ساعت کاری در اکسل – از صفر تا صد
اگر مسئول پیگیری حقوق دریافتی کارمندان هستید نیاز است ساعات کاری آنها را زیر نظر داشته باشید. برای صرفه‌جویی در زمان می‌توانید از مایکروسافت اکسل برای کارهای اداری استفاده کنید. این برنامه به شما امکان محاسبه ساعات کاری و حقوق دریافتی کارکنان را با استفاده از توابع می‌دهد. به کمک اکسل محاسبات خود را ساده کنید و اطلاعات را مدیریت کرده و تحت کنترل داشته باشید. در این مطلب به معرفی ۹ روش مختلف برای محاسبه ساعت کاری در اکسل با استفاده از ساعت ورود و خروج کارمندان معرفی می‌کنیم. بخشی از این روش ها با استفاده از عملگرهای ساده ریاضی و باقی با استفاده از توابعی چون تابع IF، MOD و تابع NOW محاسبه می‌شوند.
چرا اکسل برای محاسبه ساعت کاری کارآمد است؟
به کمک اکسل می‌توانید جدول از ساعت ورود و خروج کارمندان ایجاد کرده و به راحتی اطلاعات موجود در فایل را بررسی و تحلیل کنید و توابع فراوان اکسل محاسبه و آنالیز داده را با سرعت کیفیت زیادی انجام می‌دهند. برای مثال می‌توانید ساعات مورد نیاز برای انجام یک پروژه را در جدولی که شامل زمان انجام هر بخش پروژه می‌شود به دست آورید. به طور مشابه می‌توانید حقوق کارمندان را به کمک حاصل جمع ساعت کاری و با تفریق ساعت ورود و خروج ساعت کاری هر کارمند را محاسبه کنید. در این مطلب با یک مثال ساده با استفاده از فرمول‌های مختلف به محاسبه ساعت کل و ساعت کاری با توجه به ساعت آغاز و پایان کار می‌پردازیم.
۱. محاسبه ساعت کل با عملگر جمع
در این روش مقادیر عددی را با استفاده از یک فرمول ساده وارد می‌کنیم. برای مثال جدولی که در تصویر زیر آمده را در نظر بگیرید در این جدول مدت زمان انجام تعدادی کار آورده شده است.
حال به سادگی مقادیر زمانی موجود در جدول را با هم جمع می‌زنیم. ابتدا در سلول C8 فرمول زیر را وارد کنید:
=C5+C6+C7
با توجه به اینکه مقادیر موجود در هر سلول به عنوان داده زمانی شناخته می‌شود، مجموعه اعداد نیز از جنس زمان خواهد بود که در سلول C8 نمایش داده می‌شود.
اگر مجموع ساعات بیشتر از ۲۴ ساعت باشد، فرمول ذکر شده در این روش، ۲۴ را از عدد حاصل جمع کم می‌کند و نتیجه را مشابه آنچه در تصویر زیر آمده است نمایش می‌دهد.
در این صورت نیاز است سلول حاوی نتیجه را تغییر فرمت دهید. برای تغییر فرمت روی سلول کلیک کنید و Ctrl + 1 را بزنید تا پنجره «Format Cells» باز شود. سپس به سربرگ «Number» بروید و در فهرست «Category» گزینه «Custom» را انتخاب کنید. حال در بخش «Type» که در بالای لیست فرمت‌ها قرار دارد عبارت « [h]:mm;@ » را تایپ کنید اوکی را بزنید.
در نتیحه فرمت اصلاح شده و ساعت به شکل صحیح نمایش داده می‌شود.
فیلم‌‌های آموزشی مرتبط فرادرس
در این مطلب نحوه محاسبه ساعات کاری را به کمک اکسل آموزش می‌دهیم. اگر مایلید داده‌های بیشتری را از کسب و کار خود به کمک اکسل مدیریت کنید، فیلم آموزشی فرادرس زیر را به شما پیشنهاد می‌کنیم:
به علاوه مشاهده فیلم آموزشی زیر به شما درک بیشتری از نحوه کار با توابع اکسل خواهد داد:
۲. محاسبه ساعت کل با استفاده از تابع SUM در اکسل
در روش قبلی از عملگر جمع برای محاسبه ساعت کل استفاده کردیم. در این بخش از تابع پرکاربرد SUM برای این کار استفاده خواهیم کرد. پیش‌تر در مجله فرادرس به طور مفصل به عملکرد تابع جمع در اکسل پرداخته ایم. مشابه مثال قبل تا فرمول زیر را در سلول C8 وارد کنید:
=SUM(C5:C7)
با این کار تابع SUM تمامی مقادیر موجود در سلول‌های C6 تا C7 را جمع می‌زند و ساعت کل را به عنوان نتیجه برمی‌گرداند.
۳. محاسبه ساعت کاری در اکسل با عملگر تفریق
گاهی تنها ساعت شروع و پایان بخش‌های مختلف یک کار را داریم و نیاز است ساعت کاری را محاسبه کنیم. برای این کار از عملگر تفریق استفاده می‌کنیم.
برای این کار ابتدا فرمول زیر را در سلول D5 می‌نویسیم:
=C5-B5
با کشیدن مربع کوچک کنار سلول D5 تا انتهای جدول این فرمول برای باقی ردیف‌ها نیز تکرار می‌شود.
۴. محاسبه ساعت کاری با اختلاف زمانی بیش از ۲۴ ساعت
در مثال قبلی اختلاف زمانی محاسبه شده برای همه ردیف‌ها کمتر از ۲۴ ساعت بود. در این روش محاسبه اختلاف زمانی را در صورتی محاسبه می‌کنیم که شروع و پایان کار در دو روز مختلف اتفاق افتاده باشد و اختلاف آن بیش از ۲۴ ساعت باشد. برای این کار باید فرمت تمام سلول‌هایی که داده زمانی دارند با فرمت مشخص شده در تصویر زیر اصلاح کنیم. در این مثال تاریخ به صورت میلادی درج شده است پیش‌تر در مجله فرادرس به تغییر فرمت تاریخ از میلادی به شمسی صحبت کرده‌ایم.
بعد از اصلاح فرمت تاریخ فرمول زیر را در سلول D5 می‌نویسیم.
=(C5-B5)*24
«Enter» را بزنید و و با کشیدن مربع کوچک پایین سلول D5 تا انتهای جدول فرمول را برای سایر ردیف‌ها نیز تکرار کنید.
نتیجه این فرمول در سلول‌های D5 تا D8 به صورت عدد اعشاری خواهد بود، چرا که این سلول‌ها به طور پیش فرض در فرمت «General» تعریف شده‌اند. می‌توانید با تغییر فرمت سلول نمایش نتیجه را به دلخواه تغییر دهید.
۵. استفاده از تابع IF برای محاسبه ساعت کل
تا به اینجا برای محاسبه اختلاف زمانی از فرمول‌های ساده‌ای شامل عملگرهای اصلی استفاده کردیم. اما اگر ساعت شروع کاری ۹ شب باشد و در روز بعد ساعت ۵ صبح به اتمام برسد با استفاده از عملگر تفریق نمی‌توان این اختلاف زمانی را محاسبه کرد. در چنین مواردی اختلاف زمانی را می‌توانیم با استفاده از تابع IF محاسبه کنیم. برای این کار ابتدا فرمول زیر را در سلول D5 وارد می‌کنیم:
=(C5-B5)*24
در این فرمول تابع IF تشخیص می‌دهد که آیا ساعت شروع و ساعت پایان کار بزرگتر است یا خیر. اگر زمان اتمام کار و زمان شروع کمتر باشد فرمول یک واحد به زمان پایان اضافه می‌کند. در نهایت ساعت شروع از خروجی تابع IF که ساعت پایان کار است، کم می‌شود. با کشیدن مربع کوچک پایین سلول تا پایان جدول، فرمول برای سایر ردیف‌ها نیست تکرار می‌شود و می‌توانید نتیجه فرمول را مشاهده کنید.
۶. استفاده از تابع MOD برای محاسبه ساعت کاری در اکسل
مشابه روش چهارم می‌توانیم از تابع MOD برای محاسبه ساعات کل استفاده کنیم، در صورتی که حاصل تفریق ساعت شروع از ساعت پایان منفی باشد. این تابع آرگومان اول را بر آرگومان دوم تقسیم می‌کند و باقی‌مانده تقسیم را نمایش می‌دهد. ابتدا فرمول زیر را در سلول D5 تایپ کنید:
=MOD(C5-B5,1)
در این فرمول تابع MOD پاسخ منفی را مثبت می‌کند. نتیجه را در تصویر زیر مشاهده می‌کنید.
۷. استفاده از تابع TEXT برای محاسبه ساعت کاری
حال از تابع TEXT برای محاسبه فاصله زمانی استفاده می‌کنیم. ابتدا فرمول زیر را در سلول D5 وارد کنید:
=TEXT(C5-B5,"[hh]:mm")
با کشیدن مربع کوچک پایین سلول تا پایین جدول فرمول برای سایر ردیف‌ها تکرار می‌شود و می‌توانید نتیجه را مشاهده کنید.
در این فرمول تابع تک ست حاصل تفریق را در فرمت ساعت و دقیقه نشان می‌دهد.
۸. استفاده از تابع HOUR برای محاسبه ساعت
حال از تابع HOUR برای محاسبه ساعت کاری در اکسل استفاده می‌کنیم. این تابع نتیجه را به صورت عددی از 0 تا 23 برمی‌گرداند. به صورتی که «0» نشانگر ۱۲ نیمه‌شب و «23» نشانگر ۱۱ شب است. براس استفاده از این تابع نیاز فرمول زیر را در سلول D5 وارد کنیم:
=HOUR(C5-B5)
به عنوان نتیجه عدد «8» را به عنوان اختلاف زمانی مشاهده خواهید کرد. با کشیدن مربع کوچک پایین سلول تا انتهای جدول فرمول برای سایر ردیف‌ها تکرار می‌شود.
۹. استفاده از تابع NOW برای محاسبه زمان سپری شده
با استفاده از تابع NOW می‌توانیم زمان سپری شده از نقطه مشخصی در گذشته تا حال را محاسبه کنیم. ابتدا فرمول زیر را در سلول B5 وارد کنید:
=NOW()-B5
با کشیدن مربع کوچک پایین سلول تا انتهای جدول نتیجه نمایش داده می‌شود. در این مثال زمان محاسبه ساعت ۱۰ و ۱۰ دقیقه شب بوده است.
اگر اختلاف زمانی در این مثال بیشتر از ۲۴ ساعت باشد نیاز است برای نمایش صحیح فرمت سلول را به d “days” h:mm:ss تغییر دهیم.
کاربرد این حالت در مثالی که در تصویر زیر آمده قابل مشاهده است.
شروع تسلط بر اکسل با فرادرس
در این مطلب نکات و ترفندهایی محاسباتی برای محاسبه ساعت کاری معرفی کردیم، در واقع می‌توان با کمی تسلط بر عملکرد اکسل، در استفاده از ابزارها و توابع آن خلاقیت به خرج داد و هر مسئله‌ای را حل کرد. برای شروع مسیر تسلط بر اکسل می‌توانید فیلم آموزشی سه ساعته رایگان فرادرس زیر را مشاهده کنید:
جمع‌بندی
به‌طور کلی کار با داده‌های زمانی در اکسل با چند ترفند ساده محاسباتی انجام می‌شود. از جایی که داده‌های زمانی به صورت عددی در اکسل تعریف و تحلیل می‌شوند، به راحتی می‌توان انواع محاسبات را روی این داده‌ها انجام داد. نکته مهم در برای داشتن خلاقیت در کار با داده، درک صحیح از عملکرد اکسل است که در این مطلب و سایر مطالب مجله فرادرس به آموزش و تفهیم آن پرداختیم.
