Công thức chung
=FILTER(data,(header="a")+(header="b"))
Tóm lược
Để lọc các cột, hãy cung cấp một mảng ngang cho đối số include. Trong ví dụ được hiển thị, công thức trong I5 là:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Kết quả là một tập hợp dữ liệu được lọc chỉ chứa các cột A, C và E từ dữ liệu nguồn.
Giải trình
Mặc dù FILTER được sử dụng phổ biến hơn để lọc các hàng, nhưng bạn cũng có thể lọc các cột, mẹo là cung cấp một mảng có cùng số cột với dữ liệu nguồn. Trong ví dụ này, chúng tôi xây dựng mảng chúng tôi cần với logic boolean, còn được gọi là đại số Boolean.
Trong đại số Boolean, phép nhân tương ứng với logic AND và phép cộng tương ứng với logic OR. Trong ví dụ được hiển thị, chúng tôi đang sử dụng đại số Boolean với logic OR (phép cộng) để chỉ nhắm mục tiêu các cột A, C và E như sau:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Sau khi mỗi biểu thức được đánh giá, chúng ta có ba mảng giá trị TRUE / FALSE:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Phép toán (phép cộng) chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s, vì vậy bạn có thể nghĩ về phép toán như sau:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Cuối cùng, chúng ta có một mảng ngang duy nhất gồm 1s và 0:
(1,0,1,0,1,0)
được gửi trực tiếp đến hàm FILTER dưới dạng đối số bao gồm:
=FILTER(B5:G12,(1,0,1,0,1,0))
Lưu ý rằng có 6 cột trong dữ liệu nguồn và 6 giá trị trong mảng, tất cả là 1 hoặc 0. FILTER sử dụng mảng này làm bộ lọc để chỉ bao gồm các cột 1, 3 và 5 từ dữ liệu nguồn. Cột 2, 4 và 6 bị xóa. Nói cách khác, các cột duy nhất tồn tại được liên kết với 1s.
Với chức năng MATCH
Áp dụng logic HOẶC với phép cộng như được hiển thị ở trên hoạt động tốt, nhưng nó không mở rộng quy mô tốt và khiến bạn không thể sử dụng phạm vi giá trị từ trang tính làm tiêu chí. Thay vào đó, bạn có thể sử dụng hàm MATCH cùng với hàm ISNUMBER như thế này để tạo đối số include hiệu quả hơn:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Hàm MATCH được cấu hình để tìm kiếm tất cả các tiêu đề cột trong hằng số mảng ("a", "c", "e") như được hiển thị. Chúng tôi làm theo cách này để kết quả từ MATCH có thứ nguyên tương thích với dữ liệu nguồn, chứa 6 cột. Cũng lưu ý rằng đối số thứ ba trong MATCH được đặt bằng 0 để bắt buộc đối sánh chính xác.
Sau khi MATCH chạy, nó trả về một mảng như sau:
(1,#N/A,2,#N/A,3,#N/A)
Mảng này đi thẳng vào ISNUMBER, trả về một mảng khác:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Như trên, mảng này nằm ngang và chứa 6 giá trị cách nhau bằng dấu phẩy. FILTER sử dụng mảng để loại bỏ các cột 2, 4 và 6.
Với một phạm vi
Vì tiêu đề cột đã có trên trang tính trong phạm vi I4: K4, công thức trên có thể dễ dàng được điều chỉnh để sử dụng trực tiếp phạm vi như sau:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Phạm vi I4: K4 được đánh giá là ("a", "c", "e") và hoạt động giống như hằng số mảng trong công thức ở trên.
