Đánh giá lequocthai.com:
FILTER là một hàm tích hợp sẵn trong bảng tính và thuộc nhóm Mảng Động (Dynamic Arrays) mới của Excel. Hàm FILTER trả về một mảng các giá trị sẽ tràn (spill) ra bảng tính, trừ khi hàm được lồng vào để chuyển đầu ra cho hàm khác.
FILTER là một hàm động: khi bạn thay đổi giá trị hoặc kích thước của dữ liệu nguồn, kết quả trả về sẽ tự động cập nhật.
Cú pháp
Cú pháp của hàm FILTER như sau:
=FILTER(array, include, [if_empty])Đối số:
- array – Đối số bắt buộc, chỉ định phạm vi hoặc mảng mà bạn muốn lọc.
- include – Đối số bắt buộc, cung cấp tiêu chí lọc dưới dạng một mảng luận lý (Boolean). Kích thước của mảng này phải khớp với kích thước của array.
- if_empty – Đối số tùy chọn, chỉ định giá trị (hoặc chuỗi) hàm sẽ trả về nếu không có mục nào thỏa tiêu chí.
Các đặc điểm quan trọng của hàm FILTER
- Bạn có thể dùng FILTER để lọc cả mảng ngang và mảng dọc.
- Khi dùng FILTER giữa hai workbook, hãy chắc chắn rằng cả hai đều đang mở; nếu không hàm sẽ trả về lỗi #REF!.
- Vì kết quả của FILTER sẽ tràn ra các ô bên phải và bên dưới, hãy đảm bảo có đủ ô trống để chứa toàn bộ mảng trả về; nếu không sẽ xuất hiện lỗi #SPILL!.
- Nếu kích thước của mảng include không khớp với array, hàm sẽ trả về lỗi #VALUE!.
Ví dụ về hàm FILTER
Ví dụ 1 – Phiên bản cơ bản
Giả sử muốn lấy danh sách sinh viên có Điểm “A”.
Công thức:
=FILTER(A2:C16, C2:C16="A", "No matches")Nếu muốn, bạn có thể thay “A” bằng một ô tham chiếu, ví dụ ô G6 chứa “A”:
=FILTER(A2:C16, C2:C16=G6, "No matches")Khi không có bản ghi nào khớp, hàm sẽ trả về chuỗi “No matches” như hình dưới:
Ví dụ 2 – Kết hợp FILTER với hàm EXACT
Sử dụng hàm EXACT để tìm kiếm khớp chính xác, phân biệt hoa thường:
=FILTER(A2:C16, EXACT(A2:A16, "Bing"), "No matches")
Hàm EXACT hoạt động như đối số include của FILTER, trả về một mảng luận lý (TRUE/FALSE). Ở ví dụ này, chỉ có hàng thứ 5 chứa “Bing” nên FILTER sẽ trả về hàng đó.
Lưu ý: Nếu chỉ dùng (A2:A16)=”Bing” trong include, việc tìm kiếm sẽ không phân biệt hoa thường.
Ví dụ 3 – Lọc bằng ký tự đại diện (wildcard)
FILTER không hỗ trợ wildcard trực tiếp, nhưng có thể kết hợp ISNUMBER và SEARCH:
=FILTER(A2:A16, ISNUMBER(SEARCH("G*", A2:A16)), "No matches")
Giải thích: SEARCH trả về vị trí của ký tự “G”; nếu tìm thấy, kết quả là một số, còn không thì trả về lỗi #VALUE!. ISNUMBER chuyển số thành TRUE và lỗi thành FALSE, nên FILTER chỉ giữ lại các ô có TRUE (ở đây là “Bing”, “Geller” và “Green”).
Ví dụ 4 – Nhiều tiêu chí (toán tử AND/OR)
Tìm sinh viên có Điểm “A” và điểm số > 100.
- Tiêu chí AND: dùng dấu `*` (nhân) để nhân các giá trị luận lý (TRUE=1, FALSE=0).
=FILTER(A2:C16, (C2:C16=G6)*(B2:B16>100), "No matches")- Tiêu chí OR: dùng dấu `+` (cộng) giữa các biểu thức luận lý.
=FILTER(A2:C16, (C2:C16=G6)+(B2:B16>100), "No matches")Excel coi bất kỳ giá trị nào khác 0 đều là TRUE, vì vậy tổng 2 (1+1) vẫn được xem là TRUE.
Ví dụ 5 – Lọc các bản sao (duplicates) bằng FILTER và COUNTIFS
Khi dữ liệu lớn, việc loại bỏ các bản sao là cần thiết:
=FILTER(A2:C20, COUNTIFS(A2:A20, A2:A20, B2:B20, B2:B20, C2:C20, C2:C20)>1, "No matches")
COUNTIFS đếm số lần xuất hiện của mỗi bộ dữ liệu; FILTER chỉ giữ lại những hàng có đếm > 1.
Ví dụ 6 – Lọc các bản ghi trống
Kiểm tra ba cột A, B, C đều không rỗng:
=FILTER(A2:C16, (A2:A16<>"")(B2:B16<>"")(C2:C16<>""), "No matches")
Ví dụ 7 – Kết hợp FILTER với các hàm tổng hợp (SUM, MIN, MAX, AVERAGE)
Bạn có thể truyền kết quả của FILTER cho các hàm tính toán:
=SUM(FILTER(B2:B16, C2:C16="A", 0)) // Tổng điểm của các sinh viên có Điểm A
=MIN(FILTER(B2:B16, C2:C16="A", 0)) // Điểm thấp nhất
=MAX(FILTER(B2:B16, C2:C16="A", 0)) // Điểm cao nhất
=AVERAGE(FILTER(B2:B16, C2:C16="A", 0)) // Trung bình điểm</code></pre>
Lưu ý: Đặt if_empty = 0; nếu dùng chuỗi như "No matches" thì các hàm tổng hợp sẽ trả về lỗi #VALUE! vì chúng không chấp nhận chuỗi.
Ví dụ 8 – FILTER trả về các cột không liền kề
Lấy chỉ cột Tên và Điểm của sinh viên có Điểm "A":
=FILTER(FILTER(A2:C16, C2:C16="A"), {1,0,1})
Giải thích: FILTER bên trong trả về toàn bộ các hàng thỏa "A"; mảng `{1,0,1}` (hoặc TRUE,FALSE,TRUE) trong FILTER bên ngoài chỉ giữ lại cột 1 và 3, bỏ qua cột 2.
Hy vọng bài viết đã giải đáp mọi thắc mắc về hàm FILTER và cung cấp nhiều ví dụ thực tế. Hãy áp dụng các công thức này vào bảng tính của bạn để nhanh chóng trở thành bậc thầy sử dụng FILTER. Chúc bạn thành công!
