Công thức chung
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
Tóm lược
Để đếm các hàng có chứa các giá trị cụ thể, bạn có thể sử dụng công thức mảng dựa trên các hàm MMULT, TRANSPOSE, COLUMN và SUM. Trong ví dụ được hiển thị, công thức trong K6 là:
(=SUM(--(MMULT(--((data)=2)))
trong đó dữ liệu là dải ô được đặt tên C5: I14.
Lưu ý rằng đây là một công thức mảng và phải được nhập bằng phím enter.
Giải trình
Làm việc từ trong ra ngoài, tiêu chí logic được sử dụng trong công thức này là:
(data)<70
trong đó dữ liệu là dải ô được đặt tên C5: I14. Điều này tạo ra kết quả TRUE / FALSE cho mọi giá trị trong dữ liệu và âm kép buộc các giá trị TRUE FALSE thành 1 và 0 để mang lại một mảng như thế này:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Giống như dữ liệu ban đầu, mảng này có 10 hàng x 7 cột (10 x 7) và đi vào hàm MMULT dưới dạng array1 . Đối số tiếp theo, array2 được tạo bằng:
TRANSPOSE(COLUMN(data)^0))
Ở đây, hàm COLUMN được sử dụng như một cách để tạo một mảng số có kích thước phù hợp, vì phép nhân ma trận yêu cầu số cột trong mảng1 (7) bằng số hàng trong mảng2 .
Hàm COLUMN trả về mảng 7 cột (3,4,5,6,7,8,9). Bằng cách nâng mảng này lên lũy thừa 0, chúng ta kết thúc với mảng 7 x 1 như (1,1,1,1,1,1,1), mảng này TRANSPOSE thay đổi thành mảng 1 x 7 như (1; 1 ; 1; 1; 1; 1; 1).
Sau đó, MMULT chạy và trả về kết quả mảng 10 x 1 (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), được xử lý với biểu thức logic> = 2, dẫn đến một mảng TRUE Giá trị FALSE:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE).
Chúng tôi lại ép TRUE FALSE thành 1 và 0 với số âm kép để có được mảng cuối cùng bên trong SUM:
=SUM((1;0;0;1;0;0;0;0;0;0))
Mà đúng trả về 2, số tên có ít nhất 2 điểm dưới 70.
