Công thức Excel: Đếm ô không chứa nhiều chuỗi -

Công thức chung

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

Tóm lược

Để đếm các ô không chứa nhiều chuỗi khác nhau, bạn có thể sử dụng một công thức khá phức tạp dựa trên hàm MMULT. Trong ví dụ được hiển thị, công thức trong F5 là:

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

trong đó "dữ liệu" là phạm vi được đặt tên B5: B14 và "loại trừ" là phạm vi được đặt tên D5: D7.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter

Lời nói đầu

Công thức này phức tạp bởi yêu cầu "chứa". Nếu bạn chỉ cần một công thức để đếm các ô không * bằng * nhiều thứ, bạn có thể sử dụng một công thức đơn giản hơn dựa trên hàm MATCH. Ngoài ra, nếu bạn có một số chuỗi giới hạn để loại trừ, bạn có thể sử dụng hàm COUNTIFS như sau:

=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")

Tuy nhiên, với cách tiếp cận này, bạn phải nhập một cặp đối số phạm vi / tiêu chí mới cho mỗi chuỗi để loại trừ. Ngược lại, công thức được giải thích bên dưới có thể xử lý một số lượng lớn các chuỗi để loại trừ được nhập trực tiếp trên trang tính.

Cuối cùng, công thức này rất phức tạp. Hãy cho tôi biết nếu bạn có một công thức đơn giản hơn để đề xuất :)

Giải trình

Cốt lõi của công thức này là ISNUMBER và SEARCH:

ISNUMBER(SEARCH(TRANSPOSE(exclude),data))

Ở đây, chúng tôi chuyển đổi các mục trong phạm vi được đặt tên là "loại trừ", sau đó cung cấp kết quả cho TÌM KIẾM là "tìm văn bản", với "dữ liệu" là "trong văn bản". Hàm SEARCH trả về một mảng 2d gồm các giá trị TRUE và FALSE, 10 hàng x 3 cột, như sau:

(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)

Đối với mỗi giá trị trong "dữ liệu", chúng tôi có 3 kết quả (một kết quả cho mỗi chuỗi tìm kiếm) là lỗi hoặc số #VALUE. Các số thể hiện vị trí của một chuỗi văn bản được tìm thấy và các lỗi thể hiện các chuỗi văn bản không được tìm thấy. Nhân tiện, hàm TRANSPOSE là cần thiết để tạo ra mảng 10 x 3 kết quả hoàn chỉnh.

Mảng này được đưa vào ISNUMBER để nhận các giá trị TRUE FALSE, chúng tôi chuyển đổi thành các giá trị 1 và 0 bằng toán tử âm kép (-). Kết quả là một mảng như thế này:

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

đi vào hàm MMULT dưới dạng array1. Theo quy tắc nhân ma trận, số cột trong mảng 1 phải bằng số hàng trong mảng 2. Để tạo mảng 2 , chúng tôi sử dụng hàm ROW như sau:

ROW(exclude)^0

Điều này tạo ra một mảng 1s, 3 hàng x 1 cột:

(1;1;1)

chuyển thành MMULT dưới dạng array2 . Sau khi nhân mảng, chúng ta có một kích thước mảng để khớp với dữ liệu ban đầu:

(2;1;0;0;1;1;0;0;0;2)

Trong mảng này, bất kỳ số nào khác 0 đại diện cho một giá trị trong đó ít nhất một trong các chuỗi bị loại trừ đã được tìm thấy. Zeros cho biết không có chuỗi bị loại trừ nào được tìm thấy. Để buộc tất cả các giá trị khác 0 thành 1, chúng tôi sử dụng lớn hơn 0:

(2;1;0;0;1;1;0;0;0;2)>0

tạo ra một mảng khác hoặc các giá trị TRUE và FALSE:

(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)

Mục tiêu cuối cùng của chúng tôi là chỉ đếm các giá trị văn bản không tìm thấy chuỗi bị loại trừ, vì vậy chúng tôi cần đảo ngược các giá trị này. Chúng tôi thực hiện điều này bằng cách trừ mảng đi 1. Đây là một ví dụ về logic boolean. Phép toán tự động ép các giá trị TRUE và FALSE thành 1s và 0s, và cuối cùng chúng ta có một mảng để trả về hàm SUM: