Hàm FILTER trong Microsoft Excel là một trong những hàm quan trọng nhất cần làm chủ. Nếu không có nó, bạn sẽ gặp khó khăn tìm dữ liệu bạn cần. Đây là khóa học tập trung về việc dùng FILTER trong Excel.
Cũng hữu ích khi lưu ý rằng hàm này không phải là cách duy nhất để lọc dữ liệu trong MS Excel. Bạn có công cụ như Auto Filter (Lọc Tự Động) và Advanced Filter (Lọc Nâng Cao) để đạt được cùng điều, với một số lưu ý quan trọng chúng tôi sẽ thảo luận trong hướng dẫn này.
Hàm FILTER Là Gì?
Hàm hoặc Công thức Excel là xương sống của Excel, cho phép bạn làm những việc như tìm trung bình của tập dữ liệu lớn hoặc tạo đồ thị đường chuông. Mỗi hàm có cú pháp riêng, thường bạn có thể kiểm tra bằng cách chỉ nhập tên hàm trong Excel.
Hàm FILTER của Excel, như tên mô tả, được dùng để "lọc" giá trị của một phạm vi (range) được chỉ định theo các điều kiện nhất định. Cả phạm vi và điều kiện được nhập cùng với hàm, làm cho nó cực kỳ tùy chỉnh.
Với tham số đúng, bạn có thể trích xuất chính xác thông tin bạn cần từ bảng tính mà không phải duyệt qua toàn bộ để tìm mục khớp thủ công. Vì kết quả được chứa trong một ô, bạn có thể nối thêm hàm để thực hiện tính toán hoặc trực quan hóa kết quả trong biểu đồ.
Tại Sao Hàm FILTER Được Ưu Tiên Hơn Advanced Filter?
Hầu hết người mới Excel gắn bó với công cụ lọc dữ liệu tích hợp sẵn trong Excel thay vì cố gắng học cú pháp hàm. Auto filter là dễ nhất, cho phép bạn loại trừ cột và đặt tiêu chí lọc từ giao diện menu. Sau đó là Advanced filter với khả năng áp dụng nhiều tiêu chí cho việc triển khai các lược đồ lọc phức tạp.
Vậy tại sao còn phiền dùng hàm FILTER?
Lợi thế chính của dùng hàm Excel so với làm bất kỳ thao tác nào thủ công (bằng công cụ Excel khác hoặc thậm chí chương trình khác) là hàm là động (dynamic). Auto filter hoặc Advanced filter cho kết quả một lần không thay đổi khi dữ liệu nguồn được sửa đổi. Hàm FILTER, mặt khác, cập nhật kết quả của nó tương ứng khi dữ liệu thay đổi.
Cú Pháp Hàm FILTER
Cú pháp công thức FILTER đủ đơn giản:
=FILTER(array, include, [if_empty])
Một array (mảng) là tập hợp con hình chữ nhật của bảng tính, được chỉ định bằng cách xác định phạm vi giữa ô trên cùng bên trái và ô dưới cùng bên phải. Ví dụ, A3:E10 là một mảng bao gồm cột A đến E và hàng 3 đến 10.
Tham số tiếp theo đơn giản là tiêu chí được dùng, hoặc kỹ thuật hơn là một mảng boolean (logic). Điều này được nhập dưới dạng biểu thức đánh giá giá trị của một phạm vi ô (thường là một cột) trả về TRUE hoặc FALSE. Ví dụ, A3:A10="Pass" sẽ trả về TRUE khi giá trị ô khớp với chuỗi đã cho.
Cuối cùng, bạn có thể nhập một giá trị để hàm FILTER trả về khi không có hàng nào khớp điều kiện. Có thể là một chuỗi đơn giản như "No Records Found" (Không Tìm Thấy Bản Ghi).
Liên quan: Cần xem dữ liệu mẫu phù hợp với tập dữ liệu lớn như thế nào? Xem hướng dẫn tính standard error trong Excel.
Bây giờ chúng ta biết cú pháp hàm FILTER, hãy xem cách thực sự dùng FILTER trong bảng tính.
Dữ liệu mẫu chúng ta dùng cho demo có mảng từ A2 đến F11, bảng điểm Sinh học của mười học sinh cùng với phân phối chuẩn.
Hãy viết hàm để lọc các mục dựa trên điểm thi (liệt kê ở cột D) và chỉ trả về những cái đạt dưới 30. Cú pháp nên là:
=FILTER(A2:F11,D2:D11<30,"No Matches Found")
Vì kết quả lọc là tập con của mảng, dùng hàm trong ô có đủ không gian sau nó. Chúng ta sẽ làm bên dưới bảng gốc:
Và chúng ta có kết quả kỳ vọng. Tất cả mục với điểm dưới 30 được chọn và hiển thị cùng định dạng bảng.
Bạn không bị giới hạn ở một điều kiện. Dùng toán tử AND (*), để nối nhiều biểu thức thành một tham số đơn, tạo bộ lọc phức tạp hơn.
Hãy xây dựng hàm trả về các mục nằm giữa 30 và 70 điểm. Đây là cú pháp và kết quả:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),"No Matches Found")
Đối với tiêu chí non-exclusive (không loại trừ), bạn cũng có thể dùng toán tử OR (+). Điều này khớp bộ lọc ngay cả khi chỉ một trong các điều kiện bao gồm đánh giá thành TRUE.
Trong công thức sau, chúng ta dùng nó để tìm outliers (giá trị ngoại lai), bằng cách lọc kết quả nhỏ hơn 15 hoặc lớn hơn 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),"No Records Found")
Cuối cùng, thay vì dùng một giá trị hoặc chuỗi đơn lẻ để trả về khi hàm FILTER không tìm thấy gì, bạn có thể chỉ định giá trị cho mỗi cột để đảm bảo đầu ra luôn giữ định dạng nhất quán.
Trước, hãy thử điều kiện chúng ta biết là sai để xem mặc định trông như thế nào:
=FILTER(A2:F11,D2:D11>90,"No Matches Found")
Như bạn thấy, kết quả chỉ có một chuỗi đơn, mâu thuẫn với định dạng kỳ vọng. Điều này thường không phải vấn đề trừ khi bạn muốn đưa kết quả (hoặc một số giá trị từ nó) vào công thức khác.
Vậy hãy thử đưa giá trị mặc định cùng định dạng như một mục của mảng. Chúng ta có thể làm bằng cách chỉ định các giá trị phân tách bằng dấu phẩy trong ngoặc nhọn. Như này:
=FILTER(A2:F11,D2:D11>90,{"No Record", "No Record", "No Record", 0})
Điều này cho chúng ta kết quả dễ chấp nhận hơn, nhất quán với định dạng phần còn lại của bảng tính.
Hàm FILTER Có Giá Trị Không?
Ngay cả khi bạn chỉ dùng MS Excel để lưu giữ bản ghi và không định làm tính toán phức tạp, hàm FILTER là một trong những hàm ít bạn vẫn nên tìm hiểu.
Bởi vì khi workbook của bạn đạt đến một cỡ nhất định, tìm dữ liệu thủ công có thể đau đầu. Và dù Auto filter và Advanced filter tiện lợi, dùng hàm thực sự tiện lợi hơn trong dài hạn vì kết quả tự cập nhật và có thể ghép với hàm khác.