Công thức Excel: Bộ lọc để trích xuất các giá trị phù hợp
Công thức Excel: Bộ lọc để trích xuất các giá trị phù hợp

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 Chức năng FILTERCOUNTIF hoặc là Hàm COUNTIFS. Trong ví dụ được hiển thị, công thức trong F5 là:

=FILTER(list1,COUNTIF(list2,list1))

Ở đâu list1 (B5: B16) và list2 (D5: D14) là 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 cái mới hàm mảng động trong Excel 365.

Giải trình

Công thức này dựa trên Chức năng FILTER để truy xuất dữ liệu dựa trên kiểm tra logic được xây dựng bằng 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)

Thông báo chúng tôi đang sử dụng list2 như là phạm vi đối số, và list1 như là tiêu chí tranh luận. 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 danh sách2. Bởi 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 với 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 giá trị trong list1 cái đó không phải 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ái đó 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 bao gồm tranh luận:

=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. Bất kỳ giá trị nào trong list1 được liên kết với số 0 sẽ bị xóa, trong khi bất kỳ giá trị nào đượ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.

Các giá trị không khớp

Để trích xuất các giá trị không phù hợp từ list1 (nghĩa là các giá trị trong list1 điều đó không xuất hiện trong list2) bạn có thể thêm Không hoạt động với công thức như sau:

=FILTER(list1,NOT(COUNTIF(list2,list1)))

Hàm NOT đả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 điều đó không có trong list2.

FILTER để trích xuất các giá trị không khớp với NOT

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:

Trích xuất các giá trị phù hợp với INDEX

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à một 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, nhận được list1 như là mảng tranh luận. 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 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 với 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

Kết quả mảng 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 Chức năng NHỎ, đượ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 NHỎ (nghĩ thứ n) được tính bằng một mở rộng phạm vi:

ROWS($G$5:G5) // incrementing value for k

Các 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. Để biết một ví dụ khác về ý tưởng này, hãy xem công thức này.

https://exceljet.net/formula/filter-to-extract-matching-values