Hàm LỌC 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 sử dụng LỌC 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ư Bộ lọc tự động và Bộ lọc nâng cao để đạt được điều tương tự, với một số lưu ý quan trọng mà chúng ta sẽ thảo luận trong hướng dẫn này.
Chức năng LỌC 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 sử 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 sử dụng đều được nhập bằng hàm này, khiến hàm này 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ì đầ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 Hàm LỌC được ưu tiên hơn Bộ lọc nâng cao?
Hầu hết Người mới bắt đầu Excel sử 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 tại sao lại phải bận tâm đến việc sử dụng chức năng LỌC?
Ưu điểm chính của việc sử 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 LỌC đủ đơ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 sử 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”.
Liên quan: Bạn cần xem dữ liệu mẫu phù hợp với tập dữ liệu lớn hơn chính xác đến mức nào? Hãy xem hướng dẫn của chúng tôi về tính sai số chuẩn trong Excel.
Sử dụng Chức năng LỌC
Bây giờ chúng ta đã biết cú pháp của hàm FILTER, hãy xem cách thực sự sử dụng FILTER trong bảng tính.
Dữ liệu mẫu mà chúng tôi đang sử 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 10 học sinh cùng với phân bố chuẩn.
Hãy viết một hàm để lọc các mục 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. Đây phải là cú pháp:
=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 sử dụng hàm trong một ô có đủ khoảng trống sau nó. Chúng ta sẽ thực hiện bên dưới bảng gốc:
Và chúng ta 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ị ở cùng một định dạng bảng.
Bạn cũng không bị giới hạn ở một điều kiện duy nhất. Sử 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 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ả phù hợp”)
Đối với tiêu chí không độc quyền, bạn cũng có thể sử 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, chúng tôi sử 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),,”No Records Found”)
Cuối cùng, thay vì sử dụng một giá trị hoặc chuỗi để 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 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ả phù hợp”)
Như bạn có thể thấy, kết quả chỉ có một chuỗi duy nhất, không giống với định dạng dự kiến. Đâ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ùng định dạng 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 chúng tôi kết quả dễ chịu hơn, phù hợp với phần còn lại của định dạng bảng tính.
Chức năng LỌC có đáng giá không?
Ngay cả khi bạn chỉ sử 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 LỌC 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 của bạn đạt đến kích thước nhất định, đị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 sử 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.
.