صيغة EXCEL: متوسط الأجر في الأسبوع

صيغة عامة

= SUMPRODUCT (hours*rate)/ COUNTIF (hours,'>0')

ملخص

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

 = SUMPRODUCT (D5:I5*D6:I6)/ COUNTIF (D5:I5,'>0')

التي تُرجع متوسط الأجر في الأسبوع ، باستثناء الأسابيع التي لم يتم فيها تسجيل ساعات. هذا صيغة الصفيف ، ولكن ليس من الضروري الدخول باستخدام control + shift + enter لأن الدالة SUMPRODUCT يمكنها في الأصل التعامل مع معظم عمليات الصفيف.

تفسير

قد تعتقد أولاً أنه يمكن حل هذه المشكلة باستخدام متوسط أو المتوسطات وظيفة. ومع ذلك ، نظرًا لأن إجمالي الأجر الأسبوعي ليس جزءًا من ورقة العمل ، لا يمكننا استخدام هذه الوظائف لأنها تتطلب نطاقًا.

صيغة Excel لحساب القيم الفريدة في عمود

بالعمل من الداخل إلى الخارج ، نحسب أولاً إجمالي الأجر لجميع الأسابيع:

 D5:I5*D6:I6 // total pay for all weeks

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

كيف يمكنني عمل رسم بياني شريطي في Excel

 {87,63,48,0,12,0} // weekly pay amounts

نظرًا لوجود 6 أسابيع في ورقة العمل ، فإن الصفيف يحتوي على 6 قيم. يتم إرجاع هذا الصفيف مباشرة إلى الدالة SUMPRODUCT:

  SUMPRODUCT ({348,252,192,0,48,0})

تُرجع الدالة SUMPRODUCT بعد ذلك مجموع العناصر في المصفوفة ، 840. في هذه المرحلة ، لدينا:

كيفية إضافة العمود الإجمالي في Excel

 =840/ COUNTIF (D5:I5,'>0')

بعد ذلك ، ترجع الدالة COUNTIF عدد القيم الأكبر من الصفر في النطاق D5: I5. نظرًا لأن قيمتين من القيم الست فارغة ، ويقوم Excel بتقييم الخلايا الفارغة على أنها صفر ، فإن COUNTIF تُرجع 4.