Vispārēja formula
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Kopsavilkums
Lai aprēķinātu vidējo atalgojumu nedēļā, izņemot nedēļas, kurās netika reģistrētas stundas, un bez jau aprēķinātās kopējās algas nedēļā, varat izmantot formulu, kuras pamatā ir funkcijas SUMPRODUCT un COUNTIF. Parādītajā piemērā formula J5 ir:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
kas atgriež vidējo atalgojumu nedēļā, izņemot nedēļas, kurās nav reģistrētas stundas. Šī ir masīva formula, taču nav nepieciešams ievadīt ar vadības taustiņu + shift + enter, jo funkcija SUMPRODUCT var dabiski apstrādāt lielāko daļu masīva darbību.
Paskaidrojums
Vispirms jūs domājat, ka šo problēmu var atrisināt, izmantojot funkciju AVERAGEIF vai AVERAGEIFS. Tomēr, tā kā kopējais atalgojums nedēļā nav darblapas sastāvdaļa, mēs nevaram izmantot šīs funkcijas, jo tām ir nepieciešams diapazons.
Strādājot no iekšpuses, vispirms mēs aprēķinām kopējo atalgojumu par visām nedēļām:
D5:I5*D6:I6 // total pay for all weeks
Šī ir masīva darbība, kas reizina stundas ar likmēm, lai aprēķinātu nedēļas algas summas. Rezultāts ir šāds masīvs:
(87,63,48,0,12,0) // weekly pay amounts
Tā kā darblapā ir 6 nedēļas, masīvā ir 6 vērtības. Šis masīvs tiek tieši atgriezts funkcijā SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Tad funkcija SUMPRODUCT atgriež masīva vienumu summu 840. Šajā brīdī mums ir:
=840/COUNTIF(D5:I5,">0")
Tālāk funkcija COUNTIF atgriež vērtību vērtību, kas ir lielāka par nulli diapazonā D5: I5. Tā kā 2 no 6 vērtībām ir tukšas un Excel tukšās šūnas novērtē kā nulli, COUNTIF atgriež 4.
=840/4 =210
Gala rezultāts ir 840 dalīts ar 4, kas ir vienāds ar 210
