Công thức chung
(=IF(ISNUMBER(MATCH(data,filter,0)),data))
Tóm lược
Để lọc dữ liệu trong công thức mảng (để loại trừ hoặc yêu cầu các giá trị nhất định), bạn có thể sử dụng công thức mảng dựa trên các hàm IF, MATCH và ISNUMBER.
Trong ví dụ được hiển thị, công thức trong H5:
(=COUNT(IF(ISNUMBER(MATCH(data,filter,0)),data)))
trong đó "dữ liệu" là phạm vi được đặt tên B4: D11 và "bộ lọc" là phạm vi được đặt tên F4: F6.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
- Hàm MATCH kiểm tra tất cả các giá trị trong dải ô được đặt tên là "dữ liệu" so với các giá trị trong "bộ lọc"
- ISNUMBER chuyển đổi các giá trị phù hợp thành TRUE và các giá trị không khớp thành FALSE
- IF sử dụng đầu ra của mảng trong # 2 ở trên để lọc các giá trị, loại trừ các giá trị trong "bộ lọc"
Mảng cuối cùng trông như thế này:
(1, FALSE, 3; FALSE, 4, FALSE; FALSE, FALSE, FALSE; 1, FALSE, 3; FALSE, FALSE, FALSE; 3, FALSE, FALSE; FALSE, 4, FALSE; FALSE, FALSE, 4)
COUNT chỉ được sử dụng để xác minh kết quả.
Lọc để loại trừ
Trong ô H6, công thức đã được sửa đổi để loại trừ các giá trị sử dụng 1-ISNUMBER như sau:
=COUNT(IF(1-ISNUMBER(MATCH(data,filter,0)),data))
Điều này đảo ngược hiệu quả đầu ra của mảng trong bước # 2 ở trên.
