Hàm FILTER trong Microsoft Excel là một trong những hàm quan trọng nhất cần thành thạo. Nếu không có nó, bạn sẽ khó tìm được dữ liệu mình cần. Đây là khóa học cấp tốc về cách 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ác công cụ như Auto Filter và Advanced Filter để đạt được điều tương tự, cùng với một số lưu ý quan trọng mà mình sẽ thảo luận trong hướng dẫn này.
Chức năng FILTER là gì?
Hàm hoặc Công thức Excel là cốt lõi của Excel, cho phép bạn thực hiện những việc như tìm giá trị trung bình của một tập dữ liệu lớn hoặc tạo biểu đồ đường cong Bell . Mỗi hàm đều có cú pháp riêng mà bạn thường có thể kiểm tra bằng cách nhập tên hàm vào Excel.
Hàm Excel FILTER, như tên mô tả, được dùng để “lọc” các giá trị của một phạm vi được chỉ định theo các điều kiện nhất định. Cả phạm vi và điều kiện dùng đều được nhập bằng hàm này, khiến nó có khả năng tùy chỉnh cực kỳ cao.
Với các tham số phù hợp, bạn có thể trích xuất thông tin chính xác mình cần từ bảng tính mà không cần phải xem lại toàn bộ nội dung để tìm các mục nhập phù hợp theo cách thủ công. Và vì kết quả đầu ra được chứa trong một ô nên bạn có thể xâu chuỗi nhiều hàm hơn để thực hiện các phép tính hoặc trực quan hóa kết quả dưới dạng biểu đồ.
Tại sao Chức năng FILTER được ưa thích hơn Bộ lọc nâng cao?
Hầu hết những người mới bắt đầu sử dụng Excel đều dùng các công cụ lọc Dữ liệu tích hợp sẵn trong Excel thay vì cố gắng tìm hiểu cú pháp của hàm. Bộ lọc Tự động là dễ dàng nhất, cho phép bạn loại trừ các cột và đặt tiêu chí lọc từ giao diện dựa trên menu. Sau đó là bộ lọc Nâng cao với khả năng áp dụng nhiều tiêu chí để triển khai các sơ đồ lọc phức tạp.
Vậy thì tại sao lại phải bận tâm đến việc dùng chức năng FILTER?
Ưu điểm chính của việc dùng các hàm Excel so với việc thực hiện bất kỳ thao tác nào theo cách thủ công (với một công cụ Excel khác hoặc thậm chí bất kỳ chương trình nào khác) là các hàm có tính động. Bộ lọc Tự động hoặc Bộ lọc Nâng cao cung cấp cho bạn các kết quả một lần không thay đổi khi dữ liệu nguồn được sửa đổi. Mặt khác, hàm FILTER cập nhật kết quả tương ứng khi dữ liệu thay đổi.
Cú pháp hàm FILTER
Cú pháp của công thức FILTER đủ đơn giản:
=FILTER(mảng, bao gồm, [if_empty])
Mảng là một tập hợp con hình chữ nhật của bảng tính, được biểu thị bằng cách chỉ định một 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ác cột từ A đến E và các hàng từ 3 đến 10.
Tham số tiếp theo chỉ đơn giản là tiêu chí được dùng hoặc về mặt kỹ thuật hơn là một mảng boolean. Giá trị 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ị của ô khớp với chuỗi đã cho.
Cuối cùng, bạn có thể nhập giá trị được hàm FILTER trả về khi không có hàng nào khớp với điều kiện. Đây có thể là một chuỗi đơn giản như “No Records Found”.
Dùng chức năng FILTER
Bây giờ chúng ta đã biết cú pháp của hàm FILTER, hãy xem cách dùng FILTER trong bảng tính.
Dữ liệu mẫu mà mình đang dùng cho phần trình diễn này có một mảng từ A2 đến F11, lập 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 một hàm để lọc các mục nhập dựa trên điểm thi của chúng (được liệt kê trong cột D) và chỉ trả về những mục có điểm dưới 30. Cú pháp này sẽ như sau:
=FILTER(A2:F11,D2:D11<30,”Không tìm thấy kết quả trùng khớp”)
Vì kết quả được lọc là tập hợp con của mảng nên hãy dùng hàm trong một ô có đủ khoảng trống sau mảng đó. Chúng ta sẽ thực hiện bên dưới bảng gốc:
Và mình nhận được kết quả như mong đợi. Tất cả các mục có số điểm dưới 30 đều được chọn và hiển thị theo cùng một format bảng.
Bạn cũng không bị giới hạn ở một điều kiện duy nhất. Dùng toán tử AND (*) để xâu chuỗi nhiều biểu thức thành một tham số duy nhất, tạo ra bộ lọc phức tạp hơn.
Hãy xây dựng một hàm trả về các mục nằm trong khoảng từ 30 đến 70 điểm. Đây là cú pháp và kết quả:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),“Không tìm thấy kết quả trùng khớp”)
Đối với các tiêu chí không độc quyền, bạn cũng có thể dùng toán tử OR (+). Điều này khớp với bộ lọc ngay cả khi chỉ một trong các điều kiện đi kèm có giá trị là TRUE.
Trong công thức sau đây, mình dùng nó để tìm các giá trị ngoại lệ bằng cách lọc các kết quả nhỏ hơn 15 hoặc lớn hơn 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),,”Không tìm thấy bản ghi”)
Cuối cùng, thay vì dùng một giá trị hoặc chuỗi duy nhất để 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ữ ở format nhất quán.
Trước tiên, chúng ta hãy thử một điều kiện mà chúng ta biết là sai để xem nó trông như thế nào theo mặc định:
=FILTER(A2:F11,D2:D11>90,”Không tìm thấy kết quả trùng khớp”)
Như bạn có thể thấy, kết quả chỉ có một chuỗi duy nhất, trái ngược với format mong đợi. Đây thường không phải là vấn đề trừ khi bạn đang tìm cách đưa kết quả (hoặc một số giá trị từ nó) vào một công thức khác.
Vì vậy, hãy thử đưa ra các giá trị mặc định có cùng format với một phần tử của mảng. Chúng ta có thể thực hiện điều này bằng cách chỉ định các giá trị được phân tách bằng dấu phẩy được đặt trong dấu ngoặc nhọn. Như thế này:
=FILTER(A2:F11,D2:D11>90,{“Không có bản ghi”, “Không có bản ghi”, “Không có bản ghi”, 0})
Điều này mang lại cho mình kết quả hợp lý hơn, phù hợp với phần còn lại của format bảng tính.
Chức năng FILTER có đáng không?
Ngay cả khi bạn chỉ dùng MS Excel để lưu giữ hồ sơ và không có ý định thực hiện bất kỳ phép tính phức tạp nào, hàm FILTER là một trong số ít hàm bạn vẫn nên xem xét.
Bởi vì khi sổ làm việc đạt đến một kích thước nhất định, việc định vị dữ liệu theo cách thủ công có thể là một điều khó khăn . Và mặc dù các công cụ Bộ lọc tự động và Bộ lọc nâng cao rất tiện dụng nhưng về lâu dài, việc dùng một chức năng thực sự thuận tiện hơn vì kết quả tự cập nhật và có thể ghép nối với các chức năng khác.
