Všeobecný vzorec
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Zhrnutie
Ak chcete vypočítať priemernú mzdu za týždeň, okrem týždňov, v ktorých neboli zaznamenané žiadne hodiny, a bez už vypočítanej celkovej mzdy za týždeň, môžete použiť vzorec založený na funkciách SUMPRODUCT a COUNTIF. V zobrazenom príklade je vzorec v J5:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
ktorá vracia priemernú mzdu za týždeň, s výnimkou týždňov, keď neboli zaznamenané žiadne hodiny. Toto je vzorec poľa, ale nie je potrebné ho zadávať pomocou klávesov control + shift + enter, pretože funkcia SUMPRODUCT dokáže natívne zvládnuť väčšinu operácií poľa.
Vysvetlenie
Najskôr by ste si mohli myslieť, že tento problém je možné vyriešiť pomocou funkcie AVERAGEIF alebo AVERAGEIFS. Pretože však celková mzda za týždeň nie je súčasťou pracovného hárka, nemôžeme tieto funkcie používať, pretože vyžadujú určitý rozsah.
Pri práci zvnútra najskôr spočítame celkovú mzdu za všetky týždne:
D5:I5*D6:I6 // total pay for all weeks
Toto je operácia poľa, ktorá vynásobí hodiny sadzbami na výpočet súm týždenných platieb. Výsledkom je pole ako je toto:
(87,63,48,0,12,0) // weekly pay amounts
Pretože v pracovnom hárku je 6 týždňov, pole obsahuje 6 hodnôt. Toto pole je vrátené priamo do funkcie SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Funkcia SUMPRODUCT potom vráti súčet položiek v poli 840. V tomto okamihu máme:
=840/COUNTIF(D5:I5,">0")
Ďalej funkcia COUNTIF vráti počet hodnôt väčších ako nula v rozsahu D5: I5. Keďže 2 zo 6 hodnôt sú prázdne a program Excel vyhodnotí prázdne bunky ako nulu, funkcia COUNTIF vráti hodnotu 4.
=840/4 =210
Konečný výsledok je 840 vydelený štyrmi, čo sa rovná 210
