Công thức Excel: BỘ LỌC với nhiều tiêu chí phức tạp
Công thức Excel: BỘ LỌC với nhiều tiêu chí phức tạp

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 Chức năng FILTER với một 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À vùng 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À vùng 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 ( bao gồm đối số) được tạo bằng cách xâu chuỗi ba biểu thức 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 trong tổng số các giá trị TRUE FALSE như thế này:

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

Biểu thức thứ hai kiểm tra xem Vùng là “đông” với giá trị bằng (=) nhà điều hành:

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 Không hoạt động để kiểm tra xem tháng đó là không phải Tháng 4:

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à 0s, 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, do đó, 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))

https://exceljet.net/formula/filter-with-complex-multiple-criteria