Cách sử dụng MMULT trong Google Sheets để tính trung bình có điều kiện trong mảng các giá trị

Hãy xóa hết công thức AVERAGEIFS không phải mảng trong các ô I2:I9. Sau đó, chèn công thức mảng SUMIFS dựa trên MMULT vào ô I2.

=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),N(F2:F)))))) 

Tiêu chí: Các ngày từ phạm vi H2:H.
Công thức này kiểm tra xem ngày ‘Nhập cử’ (A2:A) <= ngày tiêu chí và ngày ‘Rời đi’ > ngày tiêu chí.

Chúng ta có thể chuyển đổi SUMIFS trên thành COUNTIFS.

Đơn giản thay thế cột tổng N(F2:F) trong công thức trên bằng Row(A2:A)^0 như sau.

Chèn vào ô J2.

=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),Row(A2:A)^0)))))

Dựa trên công thức chung AVERAGEIFS = SUMIFS / COUNTIFS, đây là công thức mảng AVERAGEIFS để sử dụng trong khoảng ngày trong Google Sheets.

=ArrayFormula(iferror(IF(LEN(H2:H),(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),N(F2:F)))/(MMULT(((TRANSPOSE(A2:A)<=H2:H)*(TRANSPOSE(B2:B)>H2:H)),Row(A2:A)^0)),),0))) 

Tôi đã kết hợp công thức Sumifs và Countifs. Cắt công thức J2 và kết hợp (bằng cách đặt toán tử chia) với công thức I2.

Lợi ích của AVERAGEIFS ArrayFormula trong Google Sheets

Tôi đã đề cập đến giới hạn của công thức mảng này ở đầu (dưới dạng từ chối trách nhiệm) của bài viết này. Bây giờ hãy nói về các lợi ích.

1. Chỉ cần chèn công thức vào ô I2. Nó sẽ mở rộng thành I2:I9. Khi bạn thêm nhiều tiêu chí hơn trong cột H, nó sẽ mở rộng tự động.
2. Khi bạn chèn các hàng mới giữa phạm vi hiện có, bạn phải sao chép dán công thức không phải mảng. Vì công thức AVERAGEIFS ArrayFormula của chúng tôi dựa trên MMULT nên không cần phải sao chép dán.

Vậy là tất cả. Enjoỵ!