Công thức Excel: Đếm các hàng có chứa các giá trị cụ thể -

Công thức chung

=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))

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 G5 là:

(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))

trong đó dữ liệu là phạm vi được đặt tên B4: B12.

Lưu ý: đây là 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=90)

trong đó dữ liệu là dải ô được đặt tên B4: D12. Đ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:

(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)

Giống như dữ liệu ban đầu, mảng này có 9 hàng x 3 cột (9 x 3) và đi vào hàm MMULT dưới dạng array1 .

Array2 được dẫn xuất bằng:

TRANSPOSE(COLUMN(data))

Đây là phần khó và thú vị của công thức này. Hàm COLUMN được sử dụng đơn giản để thuận tiện như một cách để tạo một mảng số có kích thước phù hợp. Để thực hiện phép nhân ma trận với MMULT, số cột trong mảng 1 (3) phải bằng số hàng trong mảng 2 .

COLUMN trả về mảng 3 cột (2,3,4) và TRANSPOSE thay đổi mảng này thành mảng 3 hàng (2; 3; 4). Sau đó, MMULT chạy và trả về kết quả mảng 9 x 1:

=SUM(--((2;0;7;2;0;0;0;0;4)>0))

Chúng tôi kiểm tra các mục nhập khác 0 với> 0 và một lần nữa é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;1;1;0;0;0;0;1))

Trong mảng cuối cùng này, 1 đại diện cho một hàng mà kiểm tra logic (dữ liệu = 90) trả về true. Tổng được trả về bởi SUM là tổng số tất cả các hàng có chứa số 90.

Literal chứa

Nếu bạn cần kiểm tra các giá trị văn bản cụ thể, hay nói cách khác, kiểm tra theo nghĩa đen để xem các ô có chứa các giá trị văn bản nhất định hay không, bạn có thể thay đổi logic trong công thức trên trang này để sử dụng hàm ISNUMBER và SEARCH. Ví dụ: để đếm các ô / hàng có chứa "apple", bạn có thể sử dụng: