Bendroji formulė
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Santrauka
Norėdami apskaičiuoti vidutinį darbo užmokestį per savaitę, išskyrus savaites, kuriose nebuvo registruojamos valandos, ir be jau apskaičiuoto darbo užmokesčio per savaitę, galite naudoti formulę, pagrįstą funkcijomis SUMPRODUCT ir COUNTIF. Parodytame pavyzdyje formulė J5 yra:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
kuris grąžina vidutinį darbo užmokestį per savaitę, išskyrus savaites, kuriose nebuvo registruojama valandų. Tai yra masyvo formulė, tačiau jos nereikia įvesti su „Control“ + „Shift“ + „Enter“, nes funkcija SUMPRODUCT gali natūraliai tvarkyti daugumą masyvo operacijų.
Paaiškinimas
Pirmiausia galite pagalvoti, kad šią problemą galima išspręsti naudojant AVERAGEIF arba AVERAGEIFS funkciją. Tačiau kadangi visas darbo užmokestis per savaitę nėra darbalapio dalis, negalime naudoti šių funkcijų, nes joms reikalingas diapazonas.
Dirbdami iš vidaus, pirmiausia apskaičiuojame bendrą atlyginimą už visas savaites:
D5:I5*D6:I6 // total pay for all weeks
Tai yra masyvo operacija, kuri padaugina valandas iš normų, kad apskaičiuotų savaitės darbo užmokesčio sumas. Rezultatas yra toks masyvas:
(87,63,48,0,12,0) // weekly pay amounts
Kadangi darbalapyje yra 6 savaitės, masyve yra 6 reikšmės. Šis masyvas grąžinamas tiesiai į funkciją SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Tada funkcija SUMPRODUCT pateikia masyvo elementų sumą 840. Šiuo metu mes turime:
=840/COUNTIF(D5:I5,">0")
Tada funkcija COUNTIF grąžina didesnių nei nulis reikšmių skaičių diapazone D5: I5. Kadangi 2 iš 6 reikšmių yra tušti, o „Excel“ vertina tuščius langelius kaip nulį, COUNTIF pateikia 4.
=840/4 =210
Galutinis rezultatas yra 840, padalytas iš 4, o tai lygus 210
