كيف تحسب العمل الإضافي وأجوره بسرعة في Excel؟
في العديد من أماكن العمل، يُعد تتبع ساعات عمل الموظفين—وخاصة العمل الإضافي—أمرًا بالغ الأهمية لحساب الرواتب بدقة والامتثال للوائح التنظيمية. تخيل أن لديك جدولًا يسجل أوقات تسجيل دخول الموظف، واستراحة الغداء، وتسجيل خروجه. تريد الآن حساب ساعات العمل الإضافي والمبالغ المستحقة عنها لكل يوم بسرعة، كما يظهر في لقطة الشاشة أدناه. إن الحساب الفعّال لا يُسرّع العمليات فحسب، بل يقلل أيضًا من احتمالية الوقوع في أخطاء يدوية—وهو أمرٌ جوهري عند تلخيص البيانات الخاصة بعدة موظفين أو فترات رواتب متعددة.
ماكرو VBA لحساب العمل الإضافي/الدفع دفعة واحدة
استخدام جدول بيانات محوري للتحليل التلخيصي
احسب العمل الإضافي والدفع
يمكنك تحديد ساعات العمل الإضافي والمبالغ المستحقة عنها في Excel بكفاءة باستخدام الصيغ المدمجة. يُعد هذا الأسلوب مثاليًا لسجلات موظف فردي أو مجموعات بيانات صغيرة تتطلب عمليات حسابية مباشرة. فيما يلي دليلٌ خطوة بخطوة:
1. أولًا، احسب ساعات العمل العادية لكل يوم: انقر على الخلية F2 وأدخل الصيغة التالية:
=IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)اضغط Enter، ثم اسحب مقبض الملء لأسفل لنسخ الصيغة إلى الصفوف الأخرى، ليتم عرض ساعات العمل العادية لكل يوم في العمود F.
2. بعد ذلك، احسب ساعات العمل الإضافي. في الخلية G2، أدخل الصيغة التالية:
=IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)بعد الضغط على Enter، اسحب الصيغة لأسفل لملء عمود العمل الإضافي لجميع الصفوف، وسيتم حساب العمل الإضافي لكل يوم في العمود G.
في هذه الصيغ:
- B2: بداية الدوام (وقت تسجيل الدخول)
- C2: بداية استراحة الغداء
- D2: نهاية استراحة الغداء
- E2: نهاية الدوام (وقت تسجيل الخروج)
- يفترض الحساب يوم عمل قياسي مدته 8 ساعات؛ يمكنك بسهولة تعديل القيمة "8" في الصيغة ومراجع الوقت لتنسجم تمامًا مع سياساتك.
3. لتلخيص إجمالي ساعات العمل العادية والعمل الإضافي للأسبوع، حدد الخلية F8 وأدخل ما يلي:
=SUM(F2:F7)ثم اسحب هذه الصيغة إلى الخلية G8 للحصول على إجمالي ساعات العمل الإضافي.
4. احسب المبالغ المستحقة عن ساعات العمل العادية والعمل الإضافي في الخلايا المخصصة. على سبيل المثال، لحساب الأجر العادي في الخلية F9، أدخل:
=F8*I2وبالمثل، في الخلية G9 لأجر العمل الإضافي، أدخل:
=G8*J2هنا، يجب أن تحتوي الخليتان I2 وJ2 على معدلات الأجر بالساعة للعمل العادي والعمل الإضافي، على التوالي.
للحصول على إجمالي المبلغ المستحق مقابل العمل العادي والعمل الإضافي معًا، استخدم عملية جمع بسيطة في الخلية H9:
=F9+G9يمثّل هذا الناتج النهائي إجمالي التعويض عن الفترة قيد المراجعة، مجمعًا بين الأجر العادي وأجر العمل الإضافي.
يُعد هذا الأسلوب القائم على الصيغ حلاً مباشرًا وسريعًا للحسابات اليومية أو الأسبوعية، كما يُمكن تعديله بسهولة في حال تغيّرت جداول العمل أو معايير العمل الإضافي. ومع ذلك، عند التعامل مع أعداد كبيرة من الموظفين أو عند وجود احتياجات متقدمة لإعداد التقارير، قد تكون ميزات Excel الأخرى أو حلول الأتمتة أكثر كفاءة.
- المزايا:بسيطة، ولا تتطلب أي معرفة بالبرمجة، ويسهل صيانتها لمجموعات البيانات الصغيرة.
- القيود:يتطلب إعدادًا يدويًّا لكل موظف أو جدول، وتحتاج الصيغ إلى صيانة في حال تغيّر هيكل الجدول، كما أنها ليست مثالية لمجموعات البيانات الكبيرة جدًّا.
إذا كان حجم مجموعة البيانات لديك آخذًا في الازدياد، أو احتجتَ إلى حساب العمل الإضافي أو الدفع لعددٍ كبير من الموظفين أو عبر فترات زمنية مختلفة، ففكّر في أتمتة هذه العملية أو استخدام أدوات التحليل المدمجة في Excel. راجع الخيارات أدناه:
ماكرو VBA لحساب العمل الإضافي/الدفع دفعة واحدة
عند العمل مع مجموعات بيانات كبيرة تشمل موظفين متعددين أو أوراق عمل أو فترات—حيث تكون طريقة التعبئة اليدوية غير فعّالة—يمكنك استخدام ماكرو VBA لأتمتة الحساب بالكامل. ويُبسّط هذا الأسلوب المعالجة المتكررة، خاصةً عند التعامل مع هياكل بيانات معقدة أو استيراد البيانات بشكل متكرر.
السيناريو:لديك جدول يحتوي على أعمدة للموظف، وبداية الدوام، وبداية استراحة الغداء، ونهاية استراحة الغداء، ونهاية الدوام، وترغب في حساب ساعات العمل العادية، والعمل الإضافي، والمبالغ المستحقة — كل ذلك دفعة واحدة.
ملاحظة:قبل التشغيل، احفظ ملفك وفعّل الماكرو، وأنشئ نسخة احتياطية لتجنب فقدان البيانات عن طريق الخطأ أثناء الاختبار أو التشغيل الأولي.
1. انقر علىأدوات المطور > Visual Basic. في نافذةMicrosoft Visual Basic for Applications، انقر علىإدراج > وحدة برمجية (Module)، ثم انسخ والصق الكود التالي في الوحدة البرمجية:
Sub BatchOvertimeCalculation()
Dim ws As Worksheet
Dim i As Long
Dim lastRow As Long
Dim regHourCol As String, overtimeCol As String, payCol As String
Dim startCol As String, lunchStartCol As String, lunchEndCol As String, endCol As String
Dim regHourlyRate As Double, overtimeHourlyRate As Double
On Error Resume Next
regHourCol = InputBox("Enter column letter for Regular Hour (output):", "KutoolsforExcel", "F")
overtimeCol = InputBox("Enter column letter for Overtime (output):", "KutoolsforExcel", "G")
payCol = InputBox("Enter column letter for Payment (output):", "KutoolsforExcel", "H")
startCol = InputBox("Enter column letter for Work Start:", "KutoolsforExcel", "B")
lunchStartCol = InputBox("Enter column letter for Lunch Start:", "KutoolsforExcel", "C")
lunchEndCol = InputBox("Enter column letter for Lunch End:", "KutoolsforExcel", "D")
endCol = InputBox("Enter column letter for Work End:", "KutoolsforExcel", "E")
regHourlyRate = Application.InputBox("Enter hourly rate for regular hours:", "KutoolsforExcel", 15, Type:=1)
overtimeHourlyRate = Application.InputBox("Enter hourly rate for overtime:", "KutoolsforExcel", 22.5, Type:=1)
Set ws = Application.ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
For i = 2 To lastRow
Dim totalHours As Double, regHours As Double, overtimeHours As Double
totalHours = ((ws.Range(lunchStartCol & i) - ws.Range(startCol & i)) + _
(ws.Range(endCol & i) - ws.Range(lunchEndCol & i))) * 24
If totalHours > 8 Then
regHours = 8
overtimeHours = totalHours - 8
Else
regHours = totalHours
overtimeHours = 0
End If
ws.Range(regHourCol & i).Value = regHours
ws.Range(overtimeCol & i).Value = overtimeHours
ws.Range(payCol & i).Value = regHours * regHourlyRate + overtimeHours * overtimeHourlyRate
Next i
MsgBox "Batch calculation complete!", vbInformation, "KutoolsforExcel"
End Sub2.بعد إدخال الكود، انقر على زر
في شريط أدوات VBA لتشغيل الماكرو. أدخل المعلومات المطلوبة في مربعات الحوار (مثل الأعمدة التي تحتوي على بيانات الوقت ومعدلات الأجر)، وسيقوم الماكرو تلقائيًّا بملء الأعمدة الخاصة بساعات العمل العادية والعمل الإضافي وإجمالي المبلغ المستحق لكل صف.
استكشاف الأخطاء وإصلاحها:تأكد من أن جميع أعمدة الوقت بتنسيق وقت Excel الصحيح. إذا احتوت أي خلية على بيانات غير صالحة أو فارغة، فسيتخطى الماكرو تلك الخلية أو قد يُرجع ‹0›. تحقق دائمًا يدويًّا من بعض الصفوف بعد تشغيل الماكرو للتأكد من الدقة.
- المزايا:فعّالة للغاية مع مجموعات البيانات الكبيرة أو المعقدة، وتلغي الحاجة إلى النسخ اليدوي وسحب الصيغ.
- القيود:تتطلب بعض الإلمام بلغة VBA، وعرض تنبيه أمان عند تمكين الماكرو، ويجب الحذر عند الإشارة إلى الأعمدة الصحيحة.
اقتراحات تلخيصية:للحسابات اليومية أو ذات الاستخدام الواحد، تكون الصيغ سريعة وبديهية. ومع توسع نطاق حساب العمل الإضافي ليشمل سجلات أكثر أو مع ازدياد تعقيد متطلبات إعداد التقارير، يمكن أن تقلّل الأتمتة باستخدام VBA بشكل كبير من الجهد اليدوي والأخطاء. تأكد دائمًا من دقة تنسيق الوقت، وبعد تطبيق أي حل، تحقق من أن منطق الحساب يتماشى مع سياسات العمل الإضافي في شركتك. إذا واجهت أخطاءً (مثل #VALUE!)، فافحص مجددًا تنسيق الخلية أو وجود إدخالات فارغة. ولا تنسَ الاحتفاظ بنسخة احتياطية قبل تنفيذ العمليات الدفعية.
أضف أيامًا أو سنوات أو أشهرًا أو ساعات أو دقائق أو ثوانٍ إلى التواريخ في Excel بسهولة |
إذا كان لديك تاريخ في خلية واحتجت إلى إضافة أيام أو أشهر أو سنوات أو ساعات أو دقائق أو ثوانٍ، فقد تكون الصيغ معقَّدة ويصعب تذكُّرها. ولكن معKutools لـ Excel’s مساعد التاريخ والوقت، يمكنك بسهولة إضافة أي وحدة زمنية إلى تاريخ، أو حساب الفروق بين التواريخ، أو حتى تحديد عمر شخص بناءً على تاريخ ميلاده — وكل ذلك دون الحاجة إلى حفظ صيغ معقَّدة! |
Kutools لـ Excel- عزِّز Excel بقوة بأكثر من 300 أداة أساسية، لتجعل عملك أسرع وأسهل، واستفد من ميزات الذكاء الاصطناعي لمعالجة البيانات بشكل أكثر ذكاءً ورفع الإنتاجية.احصل عليه الآن |
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل