Formula generik
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Ringkasan
Untuk mengira gaji purata setiap minggu, tidak termasuk minggu di mana tidak ada jam yang dicatat, dan tanpa jumlah gaji per minggu yang sudah dikira, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT dan COUNTIF. Dalam contoh yang ditunjukkan, formula di J5 adalah:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
yang mengembalikan gaji purata setiap minggu, tidak termasuk minggu di mana tiada jam dicatat. Ini adalah formula array, tetapi tidak perlu masuk dengan control + shift + enter kerana fungsi SUMPRODUCT secara semula jadi dapat menangani kebanyakan operasi array.
Penjelasan
Anda mungkin berfikir masalah ini dapat diselesaikan dengan fungsi AVERAGEIF atau AVERAGEIFS. Namun, kerana jumlah gaji per minggu bukan merupakan bagian dari lembaran kerja, kami tidak dapat menggunakan fungsi ini kerana memerlukan julat.
Berusaha dari dalam ke luar, kami mengira jumlah gaji untuk semua minggu:
D5:I5*D6:I6 // total pay for all weeks
Ini adalah operasi array yang mengalikan jam dengan kadar untuk mengira jumlah gaji mingguan. Hasilnya adalah susunan seperti ini:
(87,63,48,0,12,0) // weekly pay amounts
Oleh kerana terdapat 6 minggu dalam lembaran kerja, susunan mengandungi 6 nilai. Susunan ini dikembalikan terus ke fungsi SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Fungsi SUMPRODUCT kemudian mengembalikan jumlah item dalam array, 840. Pada ketika ini, kita mempunyai:
=840/COUNTIF(D5:I5,">0")
Seterusnya, fungsi COUNTIF mengembalikan kiraan nilai yang lebih besar daripada sifar dalam julat D5: I5. Oleh kerana 2 dari 6 nilai kosong, dan Excel menilai sel kosong sebagai sifar, COUNTIF mengembalikan 4.
=840/4 =210
Hasil akhir adalah 840 dibahagi dengan 4, yang sama dengan 210
