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

۲/۵ - (۵ امتیاز)

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

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

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

شرح مسئله:

جدولی داریم که ساعات ورود و خروج یک فرد در ۳۰ روز یک ماه در آن ثبت شده. حالا میخواهیم کارکرد این فرد رو محاسبه کنیم.

حل مسئله:

گام یک: آماده سازی

در مرحله اول باید فرضیاتی رو با توجه به شرایط مجموعه و قوانین کار در نظر بگیریم. مثلا اینکه:

ساعت کاری رسمی شرکت از ساعت ۸:۰۰ تا ۱۶:۰۰ است.
مبلغ هر ساعت کار برای شخص مورد نظر، ساعتی ۵۰ هزار تومان است.
مبلغ هر ساعت اضافه کار برای شخص مورد نظر ۷۰ هزار تومان است.

نکته: این فرضیات میتونه خیلی متفاوت باشه و کاملا بستگی به قوانین کار و قوانین مجموعه مورد نظر خواهد داشت.

حالا این فرضیات رو برای راحتی کار، در سلول های اکسل و در قالب یک جدول ثبت میکنیم، مشابه شکل ۱

ثبت فرضیات مربوط به محاسبه اضافه کاری و کارکرد

شکل ۱ – ثبت فرضیات مربوط به محاسبات کارکرد و اضافه کاری

با توجه به اینکه فرمول نویسی محاسبات کارکرد، پیچیده و ترکیبی هستن و شرایط مختلفی رو باید بررسی کنیم، پیشنهاد میشه که سلول های مربوط به فرضیات رو نامگذاری کنیم. با این کار هم فرمول نویسی خیلی راحت انجام میشه و هم رفع خطا و پیدا کردن مشکلات فرمول ساده تر خواهد شد. اگر با نامگذاری محدوده ها آشنا نیستید حتما مقاله مربوط به این موضوع رو مطالعه کنید. برای نمونه در ادامه یکی از سلول ها رو نامگذاری میکنیم. مثلا میخواهیم سلول C2 که ساعت شروع رسمی کار رو نشون میده نامگذاری کنیم. روی سلول C2 کلیک میکنیم و در Name Box نام مورد نظر مثلا کلمه “Start” رو تایپ کرده و Enter میزنیم. نحوه نامگذاری رو در ویدئو زیر می بینید:

با همین روش، سلول مربوط به ساعت پایان کار رو هم نامگذاری میکنیم. محدوده های نامگذاری شده رو میتونیم از مسیر زیر و همانطور که در شکل زیر نمایش داده شده ببینیم:

Formulas/ Name Manager

شکل ۲- محدوده های نامگذاری شده

گام دوم: انجام محاسبات

برای انجام محاسبات باید این نکات رو در نظر بگیریم که فرد ممکنه:

زودتر از ساعت ۸ وارد مجموعه شده باشه و دیرتر از ساعت ۱۶ هم خارج شده باشه
زودتر از ساعت ۸ وارد مجموعه شده باشه و زودتر از ساعت ۱۶ هم خارج شده باشه
دیرتر از ساعت ۸ وارد مجموعه شده باشه و دیرتر از ساعت ۱۶ هم خارج شده باشه
دیرتر از ساعت ۸ وارد مجموعه شده باشه و زودتر از ساعت ۱۶ هم خارج شده باشه

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

پس شرط هایی که باید بررسی بشه اینه که:

زودتر وارد شده یا دیرتر
زودتر خارج شده یا دیرتر

این حالات رو باید در فرمول نویسی در نظر بگیریم. پس برای محاسبه ساعت کارکرد این فمرول رو مینویسیم:

If(D7>Finish,Finish-If(C7<Start,Start,C7),D7-If(C7<Start,Start,C7))

شرح فرمول:

بررسی ساعت ورود:

باید بررسی بشه که ساعت ورود چطور بوده (زودتر از ۸ یا دیرتر). اگر زودتر از ساعت ۸ وارد شده باشه، همون ۸ (یعنی Start) در نظر گرفته میشه و اگر دیرتر از ۸ وارد شده باشه، همون ساعت ورود در نظر گرفته میشه.

If(C7<Start,Start,C7)

حالا باید ساعت خروج بررسی بشه، اگر دیرتر از ساعت ۱۶ خارج بشه، همون ساعت ۱۶ (یعنی Finish) در نظر گرفته میشه و ساعت ورود ازش کم میشه یعنی:

If(D7>Finish,Finish-If(C7<Start,Start,C7)

اگر هم ساعت خروج زودتر از ساعت ۱۶ باشه، همون ساعت خروج در نظر گرفته میشه و ساعت ورود ازش کم میشه

D7-If(C7<Start,Start,C7)

خب این فرمول برای این بود که با نحوه اعمال شرایط و فرضیات در محاسبات زمان و … آشنا بشیم. اما راه راحت تر برای حل این مسئله هم وجود داره. اونم اینکه کسری کار رو حساب کنیم. و از ۸ ساعت موظفی کم کنیم.

برای محاسبه کسری کار دو تا حالت داریم:

اینکه شخص دیرتر (بعد از ساعت ۸ صبح) وارد شرکت شده باشه. که برای این حالت کافیه ساعت ۸:۰۰ (Start) ورودش رو از مثلا ساعت ۹ صبح کم کنیم. میشه ساعتی که دیرتر رسیده:

If(C7>Start,C7-Start,0)

یا زودتر (قبل از ساعت ۱۶) از شرکت خارج شده باشه. که برای این حالت کافیه ساعت خروجش رو از ساعت ۱۶:۰۰ (Finish) کم کنیم. میشه میزان ساعتی که زودتر خارج شده:

If(D7>Finish,0,Finish-D7)

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

If(C7>Start,C7-Start,0)+If(D7>Finish,0,Finish-D7)

حالا که کسری کار محاسبه شد، میتونیم این میزان رو از ۸ ساعت موظفی روزنه کم کنیم تا کارکرد روزانه محاسبه بشه. (فرمول نوشته شده در سلول E7 در شکل ۳)

با همین منطق اضافه کار رو هم حساب میکنیم. یعنی میزان ساعاتی که زودتر از ۸:۰۰ وارد شده و دیر از ۱۶:۰۰ خارج شده.

If(D7>Finish,D7-Finish,0)+If(C7<Start,Start-C7,0)

شکل ۳- محاسبات کارکرد و اضافه کاری

حالا همین فرمول های نوشته شده رو برای همه روزهایی که ساعت ورود و خروج ثبت شده درگ میکنیم و جدول رو تکمیل میکنیم.

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

شکل ۴- محاسبه مجموع ساعات اضافه کار

برای اینکه نتیجه به درستی نمایش داده بشه ،باید روی سلول G27 کلیک راست کرده و از قسمت Format cells/ Custom این فرمت رو برای این سلول تنظیم کنیم: [hh]:mm. جهت آشنایی با علت و مفاهیم این کار حتما مقاله مربوط به مان در اکسل رو مطالعه کنید.

شکل ۵- تنظیمات فرمت سل برای سلول حاوی جمع ساعات

حالا که جمع ساعت رو حساب کردیم (۱۸:۲۵) برای اینکه بتونیم مبلغ اضافه کاری رو حساب کنیم، باید این ساعت رو در مبلغ هر ساعت اضافه کاری یعنی ۷۰۰۰۰۰ ضرب کنیم. اینجا هم به جهت اینکه محاسبات اکسل بر مبنای روز است، باید در ۲۴ ضرب کنیم که به ساعت تبدیل بشه. یعنی:

G27*G3*24

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

شکل ۶- محاسبه مبلغ اضافه کاری در اکسل

نکته تکمیلی:
مبلغ اضافه کاری بدست آمده معادل ۱۲,۸۹۱,۶۶۶.۶۷ است برای اینکه مبلغ پرداختی رو به مضرب ۱۰۰۰ گرد کنیم از تابع mround و مضرب ۱۰۰۰ استفاده میکنیم:

شکل ۷- گرد کردن عدد محاسبه شده به مضرب ۱۰۰۰

در این مقاله سعی کردیم با در نظر گرفتن فرضیاتی، به محاسبه ساعات کارکرد و اضافه کار و … بپردازیم. چیزی که در این تیپ محاسبات مهمه، درک کامل مفهوم زمان در اکسل و تسلط به IF است. اگر به این دو مسئله مسلط باشیم خیلی خوب میتونیم هر شرط و فرضیه ای رو در محاسبات لحاظ کنیم.