فرمول محاسبه اضافه کاری در اکسل به زبان ساده

محاسبه ساعات کاری و اضافه کاری کارکنان یک مجموعه، در تمامی کسب‌و‌کارها اتفاق می‌افتد. خوشبختانه اکسل، به عنوان یکی از قوی‌ترین ابزارهای مدیریت داده، این کار را آسان کرده است. در این مطلب از مجله فرادرس، ۴ روش برای محاسبه ساعات کاری و اضافه کاری آموزش داده می‌شود. در ادامه این مطلب، فرمول محاسبه اضافه کاری در اکسل را با استفاده از توابع «TIME»، «MIN» و «MAX» و ترکیب تابع TIME و «IF» برای محاسبه شرطی اضافه کاری آموزش خواهیم داد.

استفاده از تابع TIME برای محاسبه اضافه کاری در اکسل

در ابتدا نیاز است جدولی از ساعات کاری هر کارمند داشته باشیم. در این جدول ساعت شروع و پایانی کار و باقی اطلاعات ضروری ذکر شده است. سپس با توجه به این جدول میزان اضافه کاری هر کارمند را محاسبه کنیم.

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

کلیک کنید

جدولی که در تصویر زیر آمده است، مثالی است که در تمامی روش ها در ادامه مطلب استفاده خواهیم کرد.

در این بخش محاسبه اضافه کاری با استفاده از تابع TIME را توضیح می‌دهیم. خروجی تابع TIME زمان در فرمت عددی اعشاری است، به این صورت که یک روز کامل ۲۴ ساعته معادل «۱» در نظر گرفته شده و باقی ساعات مقداری اعشاری کمتر از یک می‌گیرند. البته این نکته به طور خاص زمانی مهم است که قصد داشته باشید از تابع TIME در تابع دیگری استفاده کنید. در این مثال برای محاسبه اضافه کاری با استفاده از این تابع، در قدم اول نیاز است کل ساعات کاری یک کارمند را محاسبه کنید. با توجه به جدولی که در تصویر قبلی آورده‌ایم، در این مثال با استفاده از فرمول زیر ساعات کاری به راحتی محاسبه می‌شود:

=E11-D11

در این فرمول E11 ساعت خروج کارمند و D11 ساعت ورود کارمند به شرکت است. از این رو با تفریق این دو عدد، ساعت کاری به راحتی محاسبه می‌شود. بعد از وارد کردن فرمول در سلول F11، مربع کوچک سمت راست پایین این سلول را تا ردیف هجدهم به پایین می‌کشیم تا ساعات کاری تمام کارمندان به این ترتیب در ستون F محاسبه شود.

در مرحله بعدی از تابع TIME کمک می‌گیریم. ستون G را برای محاسبه اضافه کاری انتخاب می‌کنیم و فرمول زیر را در سلول G11 تایپ می‌کنیم:

=F11-TIME(8,0,0)

در این فرمول F11 مجموعه ساعات کاری است که در بخش قبل محاسبه کردیم. تابع TIME سه آرگومان دارد که به ترتیب نشان دهنده ساعت دقیقه و ثانیه هستند. که در این مثال ۸ ساعت را از تعداد ساعت کاری کم می‌کنیم. (دقت کنید این آرگومان‌ها به صورت ۲۴ ساعته تعریف می‌شوند.)

سپس به سادگی ساعات اضافه کاری هر کارمند با تفریق ۸ ساعت از کل تعداد ساعات کار محاسبه می‌شود. این فرمول را در سلول G11 وارد می‌کنیم و با کشیدن مربع کوچک سمت راست پایین سلول تا ردیف هجدهم ساعت اضافه کاری هر کارمند وارد جدول می‌شود.

دقت کنید که ساعت اضافه کاری در فرمت ساعت و دقیقه نمایش داده شده است. با استفاده از ابزار «Format Cells» می‌توانید نمایش ساعت را اصلاح کنید. (پنجره Format Cells با میانبر Ctrl+1 روی کیبورد، باز می‌شود.)

استفاده از تابع TIME و IF برای محاسبه اضافه کاری شرطی

اگر قصد محاسبه اضافه کاری با شرط مشخصی را دارید، می‌توانید فرمول اضافه کاری در اکسل را با استفاده از تابع IF به همراه تابع TIME محاسبه کنید. برای مثال فرض کنید قصد دارید اضافه کاری بیش از یک ساعت را محاسبه کنید.

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

کلیک کنید

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

=IF(E11-TIME(8,0,0)>=TIME(1,0,0),E11-TIME(8,0,0),0)

در این فرمول E11 سلولی است که مجموعه ساعات کاری یک کارمند را نشان می‌دهد. بخش E11-TIME(8,0,0)>=TIME(1,0,0) آرگومان اول تابع IF یا آرگومان logical_test است. با استفاده از این آرگومان شرط اضافه کاری یک ساعته را در فرمول لحاظ می‌کنیم. در ادامه بخش E11-TIME(8,0,0) در صورتی محاسبه می‌شود، که شرط ذکر شده در آرگومان اول یعنی اضافه کاری بیش از یک ساعت محقق شده باشد. در غیر این صورت تابع مقدار صفر را برمی‌گرداند که در آرگومان سوم تابع تعریف کرده‌ایم.

با وارد کردن این تابع در سلول G11 و کشیدن مربع کوچک سمت راست پایین سلول تا ردیف هجدهم، اضافه کاری تمام کارمندان محاسبه می‌شود.

اگر با دقت به این ستون نگاه کنید، متوجه می‌شوید که در سلول G14 و G18 مقدار اضافه کاری شرطی صفر در نظر گرفته شده است، چراکه این دو نفر اضافه کاری کمتر از یک ساعت داشتند که در ستون سمت چپ قابل مشاهده است.

استفاده از تابع MIN برای محاسبه اضافه کاری

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

=(D11-C11)*24

در این فرمول D11 ساعت خروج و C11 ساعت ورود کارمند است. با تفریق این دو عدد مجموعه ساعات کاری کارمند مشخص می‌شود. با ضرب حاصل تفریق در ۲۴، اکسل نتیجه تفریق را به صورت یک عدد اعشاری نشان می‌دهد.

حال با استفاده از فرمول MIN ساعت کاری استاندارد را محاسبه می‌کنیم:

=MIN(8,E11)

در این فرمول، تابع MIN اگر مساوی یا بیشتر از ۸ ساعت کار شده باشد، عدد 8 را برمی‌گرداند، در غیر این صورت مقدار ساعات کاری کمتر از ۸ ساعت را نمایش می‌دهد.

در نهایت با تفریق مجموعه ساعات کاری از ساعت کاری استاندارد، اضافه کاری طبق فرمول زیر محاسبه می‌شود:

=E11-F11

در این فرمول E11 ساعت کاری و F11 ساعت کاری استاندارد ۸ ساعت است.

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

محاسبه اضافه کاری با استفاده از فرمول MAX

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

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

کلیک کنید

برای این کار در ادامه مثال قبل می‌توانیم برای ستون اضافه کاری فرمول زیر را تایپ کنیم:

=MAX(0,E11-F11)

در این فرمول اگر حاصل تفریق مساوی صفر باشد، تابع MAX مقدار صفر را برمی‌گرداند، در غیر این صورت حاصل تفریق را به صورت زمان در فرمت اعشاری نمایش می‌دهد.

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