Загальна формула
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Резюме
Для обчислення середньої заробітної плати за тиждень, за винятком тижнів, коли не було зареєстровано годин, і без загальної оплати за тиждень, вже розраховану, ви можете використовувати формулу, засновану на функціях SUMPRODUCT та COUNTIF. У наведеному прикладі формула в J5 має вигляд:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
який повертає середню заробітну плату за тиждень, за винятком тижнів, коли не було зареєстровано годин. Це формула масиву, але необов’язково вводити за допомогою control + shift + enter, оскільки функція SUMPRODUCT може спочатку обробляти більшість операцій з масивом.
Пояснення
Спочатку ви можете подумати, що цю проблему можна вирішити за допомогою функції AVERAGEIF або AVERAGEIFS. Однак, оскільки загальна оплата за тиждень не є частиною робочого аркуша, ми не можемо використовувати ці функції, оскільки вони вимагають діапазону.
Працюючи зсередини, спочатку ми розраховуємо загальну оплату праці за всі тижні:
D5:I5*D6:I6 // total pay for all weeks
Це операція з масивом, яка множить години на тарифи для розрахунку тижневої суми заробітної плати. Результат - такий масив:
(87,63,48,0,12,0) // weekly pay amounts
Оскільки на аркуші 6 тижнів, масив містить 6 значень. Цей масив повертається безпосередньо до функції SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Потім функція SUMPRODUCT повертає суму елементів у масиві, 840. На даний момент ми маємо:
=840/COUNTIF(D5:I5,">0")
Далі функція COUNTIF повертає кількість значень, більших за нуль у діапазоні D5: I5. Оскільки 2 з 6 значень порожні, а Excel вважає порожні клітинки нулем, COUNTIF повертає 4.
=840/4 =210
Остаточний результат - 840, поділений на 4, що дорівнює 210
