Công thức Excel: Lương trung bình mỗi tuần -

Công thức chung

=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")

Tóm lược

Để tính toán mức lương trung bình mỗi tuần, không bao gồm các tuần không có giờ nào được ghi lại và chưa tính tổng tiền lương mỗi tuần, bạn có thể sử dụng công thức dựa trên các hàm SUMPRODUCT và COUNTIF. Trong ví dụ được hiển thị, công thức trong J5 là:

=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")

trả lại mức lương trung bình mỗi tuần, trừ những tuần không ghi lại giờ nào. Đây là một công thức mảng, nhưng không nhất thiết phải nhập bằng control + shift + enter vì hàm SUMPRODUCT có thể xử lý nguyên bản hầu hết các hoạt động của mảng.

Giải trình

Đầu tiên bạn có thể nghĩ rằng vấn đề này có thể được giải quyết bằng hàm AVERAGEIF hoặc AVERAGEIFS. Tuy nhiên, vì tổng tiền lương mỗi tuần không phải là một phần của trang tính, chúng tôi không thể sử dụng các hàm này vì chúng yêu cầu một phạm vi.

Làm việc từ trong ra ngoài, trước tiên chúng tôi tính toán tổng lương cho tất cả các tuần:

D5:I5*D6:I6 // total pay for all weeks

Đây là hoạt động mảng nhân số giờ với tỷ lệ để tính số tiền phải trả hàng tuần. Kết quả là một mảng như thế này:

(87,63,48,0,12,0) // weekly pay amounts

Vì có 6 tuần trong trang tính nên mảng chứa 6 giá trị. Mảng này được trả về trực tiếp cho hàm SUMPRODUCT:

SUMPRODUCT((348,252,192,0,48,0))

Sau đó, hàm SUMPRODUCT trả về tổng các mục trong mảng, 840. Tại thời điểm này, chúng ta có:

=840/COUNTIF(D5:I5,">0")

Tiếp theo, hàm COUNTIF trả về số lượng các giá trị lớn hơn 0 trong phạm vi D5: I5. Vì 2 trong số 6 giá trị trống và Excel đánh giá các ô trống là 0, nên COUNTIF trả về 4.