Công thức chung
=FILTER(list1,COUNTIF(list2,list1))
Tóm lược
Để so sánh hai danh sách và trích xuất các giá trị chung, bạn có thể sử dụng công thức dựa trên các hàm FILTER và COUNTIF. Trong ví dụ được hiển thị, công thức trong F5 là:
=FILTER(list1,COUNTIF(list2,list1))
trong đó list1 (B5: B15) và list2 (D5: D13) là các phạm vi được đặt tên. Kết quả, các giá trị xuất hiện trong cả hai danh sách, tràn vào phạm vi F5: F11.
Giải trình
Hàm FILTER chấp nhận một mảng giá trị và một đối số "bao gồm" sẽ lọc mảng dựa trên một biểu thức hoặc giá trị lôgic.
Trong trường hợp này, mảng được cung cấp dưới dạng phạm vi có tên "list1", chứa tất cả các giá trị trong B5: B15. Đối số bao gồm được phân phối bởi hàm COUNTIF, được lồng bên trong FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF được thiết lập với list2 là dải ô và list1 làm tiêu chí . Bởi vì chúng tôi cung cấp cho COUNTIF mười một giá trị tiêu chí, COUNTIF trả về mười một kết quả trong một mảng như sau:
(1;1;0;1;0;1;0;1;0;1;1)
Chú ý số 1 tương ứng với các mục trong list2 xuất hiện trong list1.
Mảng này được phân phối trực tiếp đến hàm FILTER dưới dạng đối số "bao gồm":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Hàm FILTER lọc danh sách1 bằng các giá trị do COUNTIF cung cấp. Các giá trị được liên kết với số 0 bị loại bỏ; các giá trị khác được giữ nguyên.
Kết quả cuối cùng là một mảng các giá trị tồn tại trong cả hai danh sách, tràn vào phạm vi F5: F11.
Logic mở rộng
Trong công thức trên, chúng tôi sử dụng kết quả thô từ COUNTIF làm bộ lọc. Điều này hoạt động vì Excel đánh giá mọi giá trị khác 0 là TRUE và 0 là FALSE. Nếu COUNTIF trả về số lượng lớn hơn 1, bộ lọc sẽ vẫn hoạt động bình thường.
Để buộc kết quả TRUE và FALSE một cách rõ ràng, bạn có thể sử dụng "> 0" như sau:
=FILTER(list1,COUNTIF(list2,list1)>0)
Loại bỏ các bản sao hoặc sắp xếp
Để loại bỏ các bản sao, chỉ cần lồng công thức vào bên trong hàm UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Để sắp xếp kết quả, hãy lồng vào hàm SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Liệt kê các giá trị bị thiếu trong list2
Để xuất các giá trị trong list1 bị thiếu trong list2, bạn có thể đảo ngược logic như sau:
=FILTER(list1,COUNTIF(list2,list1)=0)
