Tóm lược
Để trích xuất dữ liệu với nhiều điều kiện HOẶC, bạn có thể sử dụng hàm FILTER cùng với hàm MATCH. Trong ví dụ được hiển thị, công thức trong F9 là:
=FILTER(B5:D16, ISNUMBER(MATCH(items,F5:F6,0))* ISNUMBER(MATCH(colors,G5:G6,0))* ISNUMBER(MATCH(cities,H5:H6,0)))
trong đó các mục (B3: B16), màu sắc (C3: C16) và thành phố (D3: D16) là các phạm vi được đặt tên.
Công thức này trả về dữ liệu trong đó mục là (áo phông HOẶC áo hoodie) VÀ màu là (đỏ HOẶC xanh) VÀ thành phố là (denver HOẶC ghế ngồi).
Giải trình
Trong ví dụ này, tiêu chí được nhập trong phạm vi F5: H6. Logic của công thức là:
mặt hàng là (áo phông HOẶC áo hoodie) VÀ màu là (đỏ HOẶC xanh lam) VÀ thành phố là (denver HOẶC ghế ngồi)
Logic lọc của công thức này (đối số include) được áp dụng với các hàm ISNUMBER và MATCH, cùng với logic boolean được áp dụng trong một hoạt động mảng.
MATCH được định cấu hình "ngược", với các giá trị tra cứu đến từ dữ liệu và tiêu chí được sử dụng cho mảng tra cứu. Ví dụ, điều kiện đầu tiên là các mặt hàng phải là Áo phông hoặc Áo khoác. Để áp dụng điều kiện này, MATCH được thiết lập như sau:
MATCH(items,F5:F6,0) // check for tshirt or hoodie
Bởi vì có 12 giá trị trong dữ liệu, chúng tôi cho kết quả là một mảng có 12 giá trị như sau:
(1;#N/A;#N/A;2;#N/A;2;2;#N/A;1;#N/A;2;1)
Mảng này chứa lỗi # N / A (không khớp) hoặc số (khớp). Số thông báo tương ứng với các mặt hàng là Áo phông hoặc Áo khoác. Để chuyển đổi mảng này thành các giá trị TRUE và FALSE, hàm MATCH được bao bọc trong hàm ISNUMBER:
ISNUMBER(MATCH(items,F5:F6,0))
tạo ra một mảng như thế này:
(TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE)
Trong mảng này, các giá trị TRUE tương ứng với áo phông hoặc áo hoodie.
Công thức đầy đủ chứa ba biểu thức như trên được sử dụng cho đối số bao gồm của hàm FILTER:
ISNUMBER(MATCH(items,F5:F6,0))* // tshirt or hoodie ISNUMBER(MATCH(colors,G5:G6,0))* // red or blue ISNUMBER(MATCH(cities,H5:H6,0))) // denver or seattle
Sau khi MATCH và ISNUMBER được đánh giá, chúng ta có ba mảng chứa các giá trị TRUE và FALSE. Phép toán nhân các mảng này với nhau ép các giá trị TRUE và FALSE thành 1s và 0, vì vậy chúng ta có thể hình dung các mảng tại thời điểm này như sau:
(1;0;0;1;0;1;1;0;1;0;1;1)* (1;0;1;1;0;1;0;0;0;0;0;1)* (1;0;1;0;0;1;0;1;1;0;0;1)
Kết quả, tuân theo các quy tắc của số học boolean, là một mảng duy nhất:
(1;0;0;0;0;1;0;0;0;0;0;1)
mà trở thành đối số bao gồm trong hàm FILTER:
=FILTER(B5:D16,(1;0;0;0;0;1;0;0;0;0;0;1))
Kết quả cuối cùng là ba hàng dữ liệu được hiển thị trong F9: H11
Với các giá trị được mã hóa cứng
Mặc dù công thức trong ví dụ sử dụng tiêu chí được nhập trực tiếp trên trang tính, nhưng tiêu chí có thể được mã hóa cứng dưới dạng hằng số mảng thay thế như thế này:
=FILTER(B5:D16, ISNUMBER(MATCH(items,("Tshirt";"Hoodie"),0))* ISNUMBER(MATCH(colors,("Red";"Blue"),0))* ISNUMBER(MATCH(cities,("Denver";"Seattle"),0)))
