Công thức Excel: Đếm các ô không chứa nhiều chuỗi
Công thức Excel: Đếm các ô 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))}

nơi “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à một 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 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 “loại trừ”, sau đó cung cấp kết quả cho TÌM KIẾM dưới dạng “tìm văn bản”, với “dữ liệu” là “trong văn bản”. Hàm SEARCH trả về giá trị 2d mảng trong số các giá trị TRUE và FALSE, 10 hàng x 3 cột, như thế này:

{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ố đại diện cho vị trí của một chuỗi văn bản được tìm thấy và các lỗi đại diện cho 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. Tuân theo quy tắc nhân ma trận, số cột trong array1 phải bằng số hàng trong mảng2. Để tạo ra array2, 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 sang 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ị mà í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 nơi không tìm thấy chuỗi bị loại trừ, vì vậy chúng ta cần đảo ngược các giá trị này. Chúng tôi làm điều này bằng cách lấy mảng trừ đ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: 

=SUM({0;0;1;1;0;0;1;1;1;0})

Hàm SUM trả về kết quả cuối cùng là 5.

https://exceljet.net/formula/count-cells-that-do-not-contain-many-strings