Obecný vzorec
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
souhrn
Chcete-li vypočítat průměrnou mzdu za týden, s výjimkou týdnů, kdy nebyly zaznamenány žádné hodiny, a bez již vypočítané celkové mzdy za týden, můžete použít vzorec založený na funkcích SUMPRODUCT a COUNTIF. V zobrazeném příkladu je vzorec v J5:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
který vrací průměrnou mzdu za týden, kromě týdnů, kdy nebyly zaznamenány žádné hodiny. Toto je maticový vzorec, ale není nutné jej zadávat pomocí kláves Control + Shift + Enter, protože funkce SUMPRODUCT dokáže nativně zpracovat většinu operací pole.
Vysvětlení
Nejprve si můžete myslet, že tento problém lze vyřešit pomocí funkce AVERAGEIF nebo AVERAGEIFS. Protože však celková platba za týden není součástí pracovního listu, nemůžeme tyto funkce používat, protože vyžadují určitý rozsah.
Při práci zevnitř nejdříve vypočítáme celkovou mzdu za všechny týdny:
D5:I5*D6:I6 // total pay for all weeks
Jedná se o operaci pole, která vynásobí hodiny sazbami pro výpočet týdenních částek plateb. Výsledkem je pole jako toto:
(87,63,48,0,12,0) // weekly pay amounts
Protože v listu je 6 týdnů, obsahuje pole 6 hodnot. Toto pole se vrací přímo do funkce SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Funkce SUMPRODUCT poté vrátí součet položek v poli 840. V tomto okamžiku máme:
=840/COUNTIF(D5:I5,">0")
Dále funkce COUNTIF vrací počet hodnot větších než nula v rozsahu D5: I5. Jelikož 2 ze 6 hodnot jsou prázdné a Excel vyhodnotí prázdné buňky jako nulu, funkce COUNTIF vrátí 4.
=840/4 =210
Konečný výsledek je 840 děleno 4, což se rovná 210
