Công thức Excel: Đếm các hàng có ít nhất n giá trị phù hợp
Công thức Excel: Đếm các hàng có ít nhất n giá trị phù hợp

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)<70),TRANSPOSE(COLUMN(data)^0))>=2))}

Ở đâu dữ liệuphạm vi được đặt tên C5: I14.

Lưu ý đây là một công thức mảng và phải được nhập với phím enter của ca điều khiển.

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ảng 1 (7) bằng số hàng trong array2.

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.

https://exceljet.net/formula/count-rows-with-at-least-n-matching-values