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