Công thức chung
=FILTER(list1,COUNTIF(list2,list1))
Tóm lược
Để lọc dữ liệu nhằm trích xuất các giá trị phù hợp trong hai danh sách, bạn có thể sử dụng hàm FILTER và hàm COUNTIF hoặc COUNTIFS. Trong ví dụ được hiển thị, công thức trong F5 là:
=FILTER(list1,COUNTIF(list2,list1))
trong đó list1 (B5: B16) và list2 (D5: D14) là các phạm vi được đặt tên. Kết quả trả về bởi FILTER chỉ bao gồm các giá trị trong list1 xuất hiện trong list2 .
Lưu ý: FILTER là một hàm mảng động mới trong Excel 365.
Giải trình
Công thức này dựa vào hàm FILTER để truy xuất dữ liệu dựa trên kiểm tra logic được xây dựng với hàm COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
làm việc từ trong ra ngoài, hàm COUNTIF được sử dụng để tạo bộ lọc thực tế:
COUNTIF(list2,list1)
Lưu ý rằng chúng tôi đang sử dụng list2 làm đối số phạm vi và list1 làm đối số tiêu chí. Nói cách khác, chúng tôi đang yêu cầu COUNTIF đếm tất cả các giá trị trong list1 xuất hiện trong list2. Vì chúng tôi đang cung cấp COUNTIF nhiều giá trị cho tiêu chí, chúng tôi nhận lại một mảng có nhiều kết quả:
(1;1;0;1;0;1;0;0;1;0;1;1)
Lưu ý rằng mảng chứa 12 số đếm, một số cho mỗi giá trị trong list1 . Giá trị 0 cho biết một giá trị trong list1 không được tìm thấy trong list2 . Bất kỳ số dương nào khác chỉ ra một giá trị trong list1 được tìm thấy trong list2 . Mảng này được trả về trực tiếp cho hàm FILTER dưới dạng đối số bao gồm:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Hàm bộ lọc sử dụng mảng làm bộ lọc. Mọi giá trị trong list1 được liên kết với số 0 sẽ bị xóa, trong khi mọi giá trị được liên kết với số dương vẫn tồn tại.
Kết quả là một mảng gồm 7 giá trị phù hợp tràn vào phạm vi F5: F11. Nếu dữ liệu thay đổi, FILTER sẽ tính toán lại và trả về danh sách các giá trị phù hợp mới dựa trên dữ liệu mới.
Giá trị không khớp
Để trích xuất các giá trị không khớp từ list1 (tức là các giá trị trong list1 không xuất hiện trong list2 ), bạn có thể thêm hàm NOT vào công thức như sau:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Hàm NOT sẽ đảo ngược kết quả từ COUNTIF một cách hiệu quả - mọi số khác 0 đều trở thành FALSE và mọi giá trị 0 đều trở thành TRUE. Kết quả là một danh sách các giá trị trong list1 không có trong list2 .
Với INDEX
Có thể tạo công thức để trích xuất các giá trị phù hợp mà không cần hàm FILTER, nhưng công thức phức tạp hơn. Một tùy chọn là sử dụng hàm INDEX trong một công thức như sau:
Công thức trong G5, được sao chép xuống là:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Cốt lõi của công thức này là hàm INDEX, hàm này nhận list1 làm đối số mảng. Hầu hết các công thức còn lại chỉ đơn giản là tính toán số hàng để sử dụng cho các giá trị khớp. Biểu thức này tạo ra một danh sách các số hàng tương đối:
ROW(list1)-ROW(INDEX(list1,1,1))+1
trả về một mảng 12 số đại diện cho các hàng trong list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Chúng được lọc bằng hàm IF và cùng một logic được sử dụng ở trên trong FILTER, dựa trên hàm COUNTIF:
COUNTIF(list2,list1) // find matching values
Mảng kết quả trông như thế này:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Mảng này được phân phối trực tiếp đến hàm SMALL, được sử dụng để tìm nạp số hàng phù hợp tiếp theo khi công thức được sao chép xuống cột. Giá trị k cho SMALL (nghĩ thứ n) được tính với một phạm vi mở rộng:
ROWS($G$5:G5) // incrementing value for k
Hàm IFERROR được sử dụng để bẫy các lỗi xảy ra khi công thức được sao chép xuống và hết giá trị phù hợp. Để có một ví dụ khác về ý tưởng này, hãy xem công thức này.
