Tóm lược
Để lọc và trích xuất dữ liệu dựa trên nhiều tiêu chí phức tạp, bạn có thể sử dụng hàm FILTER với chuỗi biểu thức sử dụng logic boolean. Trong ví dụ được hiển thị, công thức trong G5 là:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4))
Công thức này trả về dữ liệu trong đó:
tài khoản bắt đầu bằng "x" VÀ khu vực là "phía đông" và tháng KHÔNG phải là tháng 4.
Giải trình
Trong ví dụ này, chúng ta cần xây dựng logic lọc dữ liệu để bao gồm:
tài khoản bắt đầu bằng "x" VÀ khu vực là "phía đông" và tháng KHÔNG phải là tháng 4.
Logic lọc của công thức này (đối số include) được tạo bằng cách xâu chuỗi ba biểu thức lại với nhau sử dụng logic boolean trên các mảng trong dữ liệu. Biểu thức đầu tiên sử dụng hàm LEFT để kiểm tra xem Tài khoản có bắt đầu bằng "x" hay không:
LEFT(B5:B16)="x" // account begins with "x"
Kết quả là một mảng các giá trị TRUE FALSE như sau:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Biểu thức thứ hai kiểm tra nếu Vùng là "đông" với toán tử bằng (=):
C5:C16="east" // region is east
Kết quả là một mảng khác:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Biểu thức thứ ba sử dụng hàm MONTH với hàm NOT để kiểm tra nếu tháng đó không phải là tháng Tư:
NOT(MONTH(D5:D16)=4) // month is not april
mang lại:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Lưu ý rằng hàm NOT đảo ngược kết quả từ biểu thức MONTH.
Cả ba mảng đều được nhân với nhau. Phép toán ép buộc các giá trị TRUE và FALSE thành 1s và 0, vì vậy tại thời điểm này, chúng ta có thể hình dung đối số include như sau:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Phép nhân Boolean tương ứng với hàm logic AND, vì vậy kết quả cuối cùng là một mảng duy nhất như sau:
(0;0;0;1;1;0;0;0;0;1;0;1)
Hàm FILTER sử dụng mảng này để lọc dữ liệu và trả về bốn hàng tương ứng với các số 1 trong mảng.
Tiêu chí mở rộng
Các biểu thức được sử dụng để tạo đối số include trong bộ lọc có thể được mở rộng khi cần thiết để xử lý các bộ lọc phức tạp hơn. Ví dụ: để lọc thêm dữ liệu để chỉ bao gồm các hàng có số tiền> 10000, bạn có thể sử dụng công thức như sau:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4)*(E5:E16>10000))
