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

Mục lục

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

Hàm 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 biểu thức boolean 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

Toán tử not () với một chuỗi rỗ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". Vì có 11 ô trong phạm vi, chúng tôi nhận được 11 kết quả trong một mảng như sau:

(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: