Công thức Excel: Bộ lọc chứa một trong nhiều
Công thức Excel: Bộ lọc chứa một trong nhiều

Công thức chung
=FILTER(data,ISNUMBER(MATCH(rng1,rng2,0)),"No data")
Tóm lược

Để lọc dữ liệu để chỉ bao gồm các bản ghi trong đó cột bằng một trong nhiều giá trị, bạn có thể sử dụng Chức năng FILTER cùng với Hàm ISNUMBERHàm MATCH. Trong ví dụ được hiển thị, công thức trong F5 là:

=FILTER(data,ISNUMBER(MATCH(color,list,0)),"No data")

trong đó “dữ liệu” (B5: D15), “màu” (C5: C15) và “danh sách” (J5: J7) là phạm vi được đặt tên.

Giải trình

Các Chức năng FILTER có thể lọc dữ liệu bằng cách sử dụng biểu thức logic được cung cấp dưới dạng đối số “bao gồm”. Trong ví dụ này, đối số này được tạo bằng một biểu thức sử dụng các hàm ISNUMBER và MATCH như sau:

=ISNUMBER(MATCH(color,list,0))

MATCH được định cấu hình để tìm kiếm từng màu trong C5: C15 bên trong phạm vi nhỏ hơn J5: J7. Hàm MATCH trả về một mảng như sau:

{1;#N/A;#N/A;#N/A;2;3;2;#N/A;#N/A;#N/A;3}

Số thông báo tương ứng với vị trí của các màu “được tìm thấy” (“đỏ”, “xanh” hoặc “đen”) và lỗi tương ứng với các hàng không tìm thấy màu đích. Để buộc kết quả là TRUE hoặc FALSE, mảng này đi vào hàm ISNUMBER, trả về:

{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}

Mảng trên được chuyển đến hàm FLITER dưới dạng đối số bao gồm và FILTER chỉ trả về các hàng tương ứng với giá trị TRUE.

Với các giá trị được mã hóa cứng

Ví dụ trên được tạo bằng tham chiếu ô, trong đó màu mục tiêu được nhập trong phạm vi J5: J7. Tuy nhiên, nhưng sử dụng một mảng hằng số, bạn có thể mã hóa các giá trị vào công thức như thế này với cùng một kết quả:

=FILTER(data,ISNUMBER(MATCH(color,{"red","blue","black"},0)),"No data")

https://exceljet.net/formula/filter-contains-one-of-many