Công thức Excel: Bộ lọc loại trừ các giá trị trống
Công thức Excel: Bộ lọc loại trừ các giá trị trống

Công thức chung
=FILTER(data,(rng1<>"")*(rng2<>"")*(rng3<>""))
Tóm lược

Để lọc ra các hàng có ô trống hoặc ô trống, bạn có thể sử dụng hàm FILTER với logic boolean. Trong ví dụ được hiển thị, công thức trong F5 là:

=FILTER(B5:D15,(B5:B15<>"")*(C5:C15<>"")*(D5:D15<>""))

Đầu ra chỉ chứa các hàng từ dữ liệu nguồn trong đó cả ba cột đều có giá trị.

Giải trình

Các Chức năng FILTER được thiết kế để trích xuất dữ liệu phù hợp với một hoặc nhiều tiêu chí. Trong trường hợp này, chúng tôi muốn áp dụng tiêu chí yêu cầu tất cả ba cột trong dữ liệu nguồn (Tên, Nhóm và Phòng) phải có dữ liệu. Nói cách khác, nếu một hàng thiếu bất kỳ giá trị nào trong số này, chúng tôi muốn loại trừ hàng đó khỏi đầu ra.

Để làm điều này, chúng tôi sử dụng ba boolean biểu thức hoạt động trên mảng. Biểu thức đầu tiên kiểm tra các tên trống:

B5:B15<>"" // check names

Không nhà điều hành () với một chuỗi trống (“”) dịch thành “không trống”. Đối với mỗi ô trong phạm vi B5: B15, kết quả sẽ là TRUE hoặc FALSE, trong đó TRUE có nghĩa là “không trống” và FALSE có nghĩa là “trống”. Bởi vì có 11 ô trong phạm vi, chúng tôi nhận được 11 kết quả trong một mảng như thế này:

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

Biểu thức thứ hai kiểm tra các nhóm trống:

C5:C15<>"" // check groups

Một lần nữa, chúng tôi đang kiểm tra 11 ô, vì vậy chúng tôi nhận được 11 kết quả:

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

Cuối cùng, chúng tôi kiểm tra số phòng trống:

D5:D15<>"" // check groups

sản xuất:

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

Khi các mảng là kết quả của ba biểu thức trên được nhân với nhau, phép toán sẽ ép các giá trị TRUE và FALSE thành 1s và 0s. Chúng tôi sử dụng phép nhân trong trường hợp này, vì muốn thực thi logic “AND”: biểu thức1 VÀ biểu thức2 Biểu thức AND3. Nói cách khác, cả ba biểu thức phải trả về TRUE trong một hàng nhất định.

Tuân theo các quy tắc của logic boolean, kết quả cuối cùng là một mảng như sau:

{1;0;1;0;1;1;1;0;0;0;1}

Mảng này được phân phối trực tiếp đến hàm FILTER dưới dạng đối số bao gồm. FILTER chỉ bao gồm 6 hàng tương ứng với 1s trong đầu ra cuối cùng.

https://exceljet.net/formula/filter-exclude-blank-values