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 hàm FILTER cùng với hàm ISNUMBER và hà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à các phạm vi được đặt tên.
Giải trình
Hàm 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 "tìm thấy" ("đỏ", "xanh lam" 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 bằng cách sử dụng một hằng số mảng, 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")
