Công thức chung
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Tóm lược
Để tạo bảng tóm tắt với số lượng các mục được ghép nối xuất hiện trong danh sách các kết hợp hiện có, bạn có thể sử dụng cột trợ giúp và công thức dựa trên hàm COUNTIFS. Trong ví dụ được hiển thị, công thức trong ô H5 là:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
trong đó "helper" là dải ô được đặt tên E5: E24.
Lưu ý: công thức này giả định các mục không lặp lại trong một tổ hợp nhất định (tức là AAB, EFE không phải là kết hợp hợp lệ).
Giải trình
Chúng tôi muốn đếm tần suất các mục trong cột B, C và D xuất hiện cùng nhau. Ví dụ: tần suất A xuất hiện với C, B xuất hiện với F, G xuất hiện với D, v.v. Điều này có vẻ giống như một cách sử dụng hoàn hảo của COUNTIFS, nhưng nếu chúng tôi cố gắng thêm tiêu chí tìm kiếm 2 mục trên 3 cột, nó sẽ không hoạt động.
Một cách giải quyết đơn giản là nối tất cả các mục lại với nhau trong một ô, sau đó sử dụng COUNTIFS với ký tự đại diện để đếm các mục. Chúng tôi làm điều đó với cột trợ giúp (E) kết hợp các mục trong cột B, C và D bằng cách sử dụng hàm CONCAT như sau:
=CONCAT(B5:D5)
Trong các phiên bản Excel cũ hơn, bạn có thể sử dụng công thức như sau:
=B5&C5&D5
Vì các mục lặp lại không được phép kết hợp nên phần đầu tiên của công thức loại trừ các mục phù hợp. Nếu hai mục giống nhau, công thức trả về dấu gạch ngang hoặc dấu gạch ngang dưới dạng văn bản:
=IF($G5=H$4,"-"
Nếu các mục khác nhau, một hàm COUNTIFS sẽ được chạy:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS được định cấu hình để đếm "cặp" mục. Chỉ khi mục trong cột G và mục tương ứng từ hàng 4 xuất hiện cùng nhau trong một ô thì cặp mới được tính. Một ký tự đại diện (*) được nối với cả hai mặt của mục để đảm bảo kết quả trùng khớp sẽ được tính cho dù nó xuất hiện ở đâu trong ô.
