excel make كيفية حساب ساعات في التفوق




userform excel (3)

لدي طريقة لا تستخدم سوى الصيغ. أولا إنشاء جدول البحث الذي يحتوي على كل ساعة ومعدل في القول الأعمدة K & L، شيء من هذا القبيل:

K       L
08:00   15
09:00   10
10:00   10
11:00   10
12:00   10
13:00   10
14:00   10
15:00   10
16:00   10
17:00   10
18:00   10
19:00   10
20:00   10
21:00   15
22:00   15
23:00   15

تأكد من إدخال ساعات كنص عن طريق إدخال اقتباس واحد قبل الأرقام.

ثم إذا كانت ساعاتك في الخلية B2، يمكنك استخدام هذه الصيغة لحساب المجموع: = سوم (إنديركت ("L" & ماتش (ليفت (B2،5)، K2: K40،0) & ": L" & ماتش (رايت (B2،5)، K2: K40،0)))

كل الصيغة تقوم به هو الحصول على النص الأيسر والأيمن من وقت العمل الخاص بك، وذلك باستخدام ماتش للعثور على مواقعها في جدول البحث الذي يستخدم لإنشاء عنوان نطاق الذي يتم تمريره بعد ذلك إلى سوم عبر الدالة إنديركت.

إذا كنت بحاجة للقلق حول دقائق كل ما عليك القيام به هو إنشاء جدول بحث أكبر الذي يحمل كل دقيقة من اليوم. قد تحتاج إلى إضافة بعض المنطق الإضافي إذا كانت أيام العمل الخاصة بك تمتد منتصف الليل.

لدي ملف زلس في التنسيق التالي

Name    1              2             3            4
John    09:00-21:00                  09:00-21:00
Amy                    21:00-09:00                09:00-21:00

حيث 1،2،3،4 وهلم جرا تمثل أيام من الشهر الحالي، 09: 00-21: 00 - ساعات العمل.

أريد حساب الراتب على أساس الشروط التالية:

09:00-21:00 - 10$/hour
21:00-00:00 - 15$/hour
00:00-03:00 - 20$/hour
etc.

(على سبيل المثال 03: 00-04: 00 - 20 $ / ساعة، 04: 00-05: 00 - 19 $ / ساعة، وما إلى ذلك)

كيف يمكن تحقيق ذلك باستخدام إكسيل فقط (وظائف أو فبا)؟

بس طريقة سهلة: تصدير إلى كسف وعملية في الثعبان / فب / الخ.


هنا هو الحل غير فبا. انها صيغة سيئة جدا، لكنه يعمل. وأنا واثق من أنه يمكن أن يكون أسهل للاستخدام وفهم مع بعض أكثر براعة:

على افتراض إعداد جدول البيانات على النحو التالي:

أدخل هذه الصيغة في الخلية G1 واسحب لأسفل لمجموعة البيانات:

=IF(ISBLANK(B2),"",IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2),SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B2,2),$Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2)),SUMIF($Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B2,2),$P$2:$P$24)))

لشرح الصيغة بالتفصيل:

  1. IF(ISBLANK(B2),"" سيعود سلسلة فارغة إذا لم يكن هناك وقت لمجموعة معينة / يوم الجمع.
  2. LEFT(B2,2) يستخرج وقت البدء إلى ساعة.
  3. Mid(B2,Find("-",B2)+1,2) يستخرج الوقت النهائي إلى ساعة.
  4. IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2) سيتحقق مما إذا كان وقت البدء أقل من الوقت النهائي (بمعنى أنه لا يوجد عمل أثناء الليل) وقت البدء هو أقل من الوقت النهائي، فإنه سيتم استخدام هذه الصيغة لحساب التكلفة الإجمالية للساعة الواحدة: SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B3,2),$Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2))
  5. إذا كان وقت البدء أعلى من نهاية الوقت (أي العمل بين عشية وضحاها)، فإنه سيتم استخدام هذه الصيغة لحساب: SUMIF($Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B3,2),$P$2:$P$24)
  6. استخدام " Find("-",[cell]) يقسم مرات البدء و النهاية إلى القيم التفوق يمكن استخدامها للقيام الرياضيات ضد الجدول الوقت / التكلفة.
  7. الصيغة في العمود Q من جدول الوقت / التكلفة =VALUE(MID(O2,FIND("-",O2)+1,2)) وتحول ساعة النهاية للنظر في التكلفة إلى قيمة يمكن لبرنامج إكسيل استخدامها لإضافة ، بدلا من أن يكون النص من تنسيق المصدر الأصلي.

القيام بذلك في فبا! فمن الأصلي إلى التفوق وسهلة التعلم. وظيفيا، وأود أن حلقة من خلال الجدول، وكتابة وظيفة لحساب الدولارات المكتسبة على أساس المعلومات المقدمة. إذا كنت تريد أن تكون نتائجك التحديث المباشر (مثل صيغة في التفوق) يمكنك كتابة وظيفة تعريف المستخدم . قد تكون الدالة مفيدة دالة HoursIntersect ، على النحو التالي:

Public Function HoursIntersect(Period1Start As Date, Period1End As Date, _
                               Period2Start As Date, Period2End As Date) _
                               As Double
Dim result As Double

' Check if the ends are greater than the starts.  If they are, assume we are rolling over to
' a new day
If Period1End < Period1Start Then Period1End = Period1End + 1
If Period2End < Period2Start Then Period2End = Period2End + 1

With WorksheetFunction
result = .Min(Period1End, Period2End) - .Max(Period1Start, Period2Start)
HoursIntersect = .Max(result, 0) * 24
End With

End Function

ثم يمكنك تحديد بداية ونهاية الوقت عن طريق تقسيم القيمة على حرف "-". ثم قم بضرب كل جدول زمني للسداد عن طريق ساعات العمل في ذلك الوقت:

DollarsEarned = DollarsEarned + 20 * HoursIntersect(StartTime, EndTime, #00:00:00#, #03:00:00#)
DollarsEarned = DollarsEarned + 10 * HoursIntersect(StartTime, EndTime, #09:00:00#, #21:00:00#)
DollarsEarned = DollarsEarned + 15 * HoursIntersect(StartTime, EndTime, #21:00:00#, #00:00:00#)




excel-formula