Hướng dẫn cách sử dụng hàm FILTER trong Excel Office 365

Nếu như trước đây, khi muốn lọc dữ liệu và xuất dữ liệu ra vị trí khác, chúng ta thường sử dụng các công cụ như Bộ lọc, Bộ lọc nâng cao, công thức mảng. Tuy nhiên, những công việc này khiến chúng ta tốn rất nhiều công sức, nhiều thời gian và đôi khi rất khó thực hiện. Bạn có muốn có một hàm trong Excel giúp việc này trở nên đơn giản và dễ dàng không? Tin tốt là Office 365 đã cung cấp cho chúng tôi Chức năng lọc để làm điều này. Vậy chức năng này có gì đặc biệt và cách sử dụng nó như thế nào? Hãy Học Excel Online bất kỳ khám phá nào.

Cấu trúc chức năng lọc (Chức năng lọc)

Chức năng này có tác dụng lọc dữ liệu và xuất dữ liệu đã lọc sang vị trí khác (ô khác, trang tính khác, sổ làm việc khác).

Đây là một ứng dụng của chức năng Lọc – ham loc giúp lọc ra những món có tên Táo:

Cấu trúc của hàm:

= FILTER (mảng, bao gồm,[if_empty])

Tải game crack việt hoá tại: https://daominhha.com
  • mảng: vùng dữ liệu được lọc (tiêu đề không bắt buộc)
  • bao gồm: điều kiện lọc là gì, trong cột nào (xác định cả hai yếu tố này cùng một lúc)
  • if_empty: Nếu không có kết quả, giá trị nào được trả về? (mục nhập tùy chọn)

Ghi chú về các tính năng của chức năng:

Đây là những lưu ý về chức năng lọc trong excel:

  • Hàm trả về kết quả dạng mảng, dựa trên số cột của mảngsố dòng kết quả thu được
  • Bất kể tiêu đề của các trường dữ liệu.
  • Kết quả trả về không bao gồm định dạng của dữ liệu gốc
  • Không cần sử dụng công thức mảng

Các trường hợp lỗi

Trường hợp 1: Không có kết quả lọc theo điều kiện đã cho

Nếu không có kết quả phù hợp và không được chỉ định trong if_empty, hàm sẽ trả về một lỗi # CALC! (không tính được)

>>> Xem thêm:  Xây dựng Custom Visuals cho Power BI Chart

Trong trường hợp này, chúng tôi sẽ cần chỉ định giá trị tại tham số if_empty trống (hai dấu ngoặc kép liên tiếp) để không hiển thị lỗi hoặc thông báo về việc không tìm thấy kết quả.

Trường hợp 2: Có các giá trị khác được trộn lẫn trong vùng kết quả

Vì kết quả của hàm Bộ lọc trả về một mảng nên khi có các giá trị khác trong vùng này (do người dùng nhập vào), giá trị lỗi #SPILL! (kết quả sẽ làm tràn dữ liệu hiện có)

Vì vậy khi sử dụng chức năng này cần đảm bảo không có khoảng trống cho vùng kết quả, không có dữ liệu nào bị trộn lẫn trong vùng này.

Hàm FILTER – ví dụ

Lọc theo 1 điều kiện

Trong hình ảnh ví dụ ở đầu bài viết, công thức trong ô G5 là

=FILTER(B5:D13,D5:D13=H2,"Không tìm thấy")

Chúng ta có thể giải thích công thức này một cách dễ dàng: lọc trong phạm vi B5: D13 các hàng mà cột D có giá trị là Màu đỏ.

Chúng ta có thể viết công thức trên như sau (viết điều kiện trực tiếp trong công thức)

=FILTER(B5:D13,D5:D13="Red","Không tìm thấy")

Tuy nhiên, việc viết điều kiện trực tiếp vào công thức này không được khuyến khích, vì khi điều kiện thay đổi, chúng ta phải nhập công thức để chỉnh sửa nó. Do đó, để điều kiện trong một ô bên ngoài công thức, và sau đó tham chiếu đến ô đó sẽ giúp chúng ta có một công thức linh hoạt hơn.

Lọc theo nhiều điều kiện

Chúng ta có thể sử dụng nhiều điều kiện phức tạp để lọc dữ liệu với chức năng FILTER. Giả sử, trong vùng dữ liệu B5: D13, chúng ta muốn giữ đồng thời các dòng dữ liệu thỏa mãn hai tiêu chí sau:

  • Cột D có giá trị là Màu đỏ
  • Cột C có giá trị lớn hơn hoặc bằng 80

Công thức chúng ta có thể sử dụng trong trường hợp này là:

>>> Xem thêm:  Merge Cells Có Nên Sử Dụng Không? Cách Thay Thế Như Thế Nào?

=FILTER(B5:D13,(D5:D13=H2)*(C5:C13>=80),"Không tìm thấy")

Cách viết và hiểu đoạn văn (D5:D13=H2)*(C5:C13>=80) , bạn có thể tìm hiểu thêm trong bài viết SUMPRODUCT sau đây:

Chức năng Bộ lọc có gì đặc biệt?

Phải nói là rất đặc biệt, rất tuyệt vời. Nếu bạn đã từng loay hoay với công thức mảng, mất thời gian với loc Filter, khó chịu với kết quả của Advanced Filter thì khi sử dụng chức năng Filter, bạn sẽ thấy sự khác biệt rất lớn.

So với AutoFilter: giảm thời gian hoạt động

Nếu chưa biết cách sử dụng Filter hoặc các tính năng của bộ lọc, bạn có thể tham khảo bài viết:

Cách lọc dữ liệu trong Excel

Vì vậy, chúng tôi thấy rằng để có thể đưa ra kết quả cho các báo cáo chi tiết, các thao tác thường được sử dụng là:

  • Áp dụng AutoFilter cho bảng dữ liệu
  • Lọc dữ liệu theo vị trí dòng chủ đề
  • Sao chép kết quả bộ lọc
  • Dán giá trị (Dán Đặc biệt / Giá trị) của các giá trị còn lại sau khi lọc vào vị trí cần báo cáo

Rất nhiều thao tác và tốn thời gian phải không? Để khắc phục nhược điểm tốn thời gian và rườm rà này, chúng tôi có AdvancedFilter, có hỗ trợ các công thức mảng. Tuy nhiên, những công cụ này vẫn tồn tại những nhược điểm.

So với Bộ lọc nâng cao: hoàn toàn tự động mà không cần VBA và hơn thế nữa

Bộ lọc nâng cao là một công cụ khá tốt để trích xuất dữ liệu theo nhiều điều kiện. Lý do nó chỉ “khá tốt” là:

  • Tiết kiệm thời gian lọc dữ liệu, nhưng không hoàn toàn tự động. Nếu bạn muốn làm điều đó tự động, bạn phải áp dụng VBA
  • Không có công thức nào được sử dụng, do đó không có tài nguyên, nhưng việc xây dựng phạm vi điều kiện cũng phức tạp.
  • Phụ thuộc hoàn toàn vào tiêu đề của bảng dữ liệu. Do đó sẽ rất hạn chế nếu bảng dữ liệu không có tiêu đề sẽ không làm đúng.
  • Kết quả lọc mang định dạng chính xác của dữ liệu gốc. Điều này khiến chúng ta không thể định dạng dữ liệu theo ý muốn.
>>> Xem thêm:  Hướng dẫn cách vẽ biểu đồ quản lý dự án trong Excel chi tiết nhất

Bạn có thể tham khảo một số bài viết sau để xem ứng dụng của Bộ lọc nâng cao:

Tự động lọc danh sách không trùng với Advanced Filter VBA cực kỳ đơn giản

Hướng dẫn cách lập báo cáo chi tiết kho NXT bằng Bộ lọc nâng cao trong Excel

Hướng dẫn cách lọc dữ liệu sang trang tính khác bằng Bộ lọc nâng cao trong Excel

Do đó, khi sử dụng hàm Filter, chúng ta sẽ thấy không cần tạo vùng điều kiện, không cần VBA mà kết quả sẽ được định dạng theo ý muốn.

So với công thức mảng: Đơn giản hơn nhiều

Chúng ta đã biết về việc sử dụng các công thức mảng như OFFSET để lập báo cáo chi tiết như trong bài viết sau:

Hướng dẫn cách viết công thức để lập báo cáo chi tiết tự động trên Excel

Có thể thấy công thức rất dài và khó. Bởi vì chúng ta phải thực hiện 2 yêu cầu đồng thời trong 1 câu lệnh:

  • Lọc ra các kết quả chính xác
  • Trích xuất kết quả vào báo cáo, các nội dung phải liền nhau

Với các hàm trước đây, để làm được công việc trên cần sử dụng nhiều hàm, việc phân tích logic khá phức tạp. Nhưng giờ đây với chức năng Bộ lọc, điều này trở nên dễ dàng hơn bao giờ hết.

Đây là những hướng dẫn ngắn gọn về Cách sử dụng bộ lọc trong Excel. Trong các bài viết sau, Học Excel Online sẽ giúp bạn hiểu rõ hơn về cách sử dụng chức năng Bộ lọc với nhiều điều kiện để lập báo cáo chi tiết. Mời các bạn chú ý đón xem.

Nguồn : Blog hocexcel