Hàm lọc dữ liệu có điều kiện trong Excel và các lỗi thường gặp

Đôi khi, bạn cần phải sử dụng hàm lọc dữ liệu có điều kiện trong Excel để tính toán hoặc lọc bảng tính cho một đối tượng cụ thể. Lúc đó, bạn có thể tham khảo bài viết dưới đây để cùng Hoàng Hà Mobile tìm hiểu cách dùng bộ lọc FILTER, hàm FILTER và cách khắc phục một số lỗi thường gặp khi lọc dữ liệu trong Excel.

Khi nào bạn cần sử dụng hàm lọc dữ liệu có điều kiện trong Excel?

Hàm lọc dữ liệu có điều kiện trong Excel thường được sử dụng để lọc dữ liệu từ một bảng tính hoặc phạm vi dữ liệu dựa trên các điều kiện cụ thể. Dưới đây là một số trường hợp bạn có thể sẽ cần đến bộ lọc hoặc hàm này:

• Lọc dữ liệu đáp ứng một hoặc nhiều điều kiện: Khi bạn muốn hiển thị các dữ liệu thỏa mãn một hoặc nhiều điều kiện cụ thể, bạn có thể sử dụng hàm lọc này. Chẳng hạn như lọc danh sách khách hàng có độ tuổi từ 30 trở lên với số tiền mua hàng trên 10 triệu đồng.
• Tạo bảng dữ liệu: Từ một bảng tính lớn, bạn có thể sử dụng hàm lọc dữ liệu có điều kiện trong Excel để tổng hợp thành một bảng tính nhỏ hơn. Chẳng hạn như bạn có thể tạo các bảng lương riêng cho mỗi nhân viên từ bảng lương chung của một văn phòng/công ty.
• Lọc dữ liệu có khả năng thay đổi dựa trên điều kiện hoặc lựa chọn tùy chỉnh: Bạn có thể tạo một bảng hoặc biểu đồ động dựa trên điều kiện thay đổi. Chẳng hạn như tạo bảng hiển thị chỉ số kết quả của các sản phẩm và cho phép người dùng chọn loại sản phẩm cụ thể từ danh sách thả xuống để xem kết quả chi tiết.
• Tính toán trên dữ liệu đã lọc: Tương tự, bạn có thể lọc dữ liệu và thực hiện phép tính như tính tổng, trung bình, min/max… Chẳng hạn như để tạo bảng thống kê doanh thu của từng mặt hàng, bạn có thể lọc và tính tổng theo từng sản phẩm riêng.

Cách dùng bộ lọc FILTER trong Excel

Để lọc dữ liệu, bạn có thể sử dụng hàm lọc dữ liệu có điều kiện trong Excel - FILTER, hoặc sử dụng bộ lọc FILTER có sẵn trên thanh công cụ của Excel.

Trước khi khám phá cách dùng hàm FILTER, chúng ta sẽ tìm hiểu về bộ lọc FILTER. Bộ lọc FILTER trong Excel cho phép người dùng lọc dữ liệu đáp ứng một hoặc nhiều điều kiện cụ thể. Sau khi áp dụng bộ lọc, tất cả dữ liệu thỏa mãn điều kiện sẽ hiển thị, còn những dữ liệu còn lại sẽ bị ẩn đi.

Trong Excel có 2 chức năng lọc cơ bản là:

• Auto Filter: Bộ lọc mặc định.
• Advanced Filter: Bộ lọc nâng cao.

Đầu tiên, bạn cần tạo bộ lọc FILTER như sau:

• Bước 1: Nhập dữ liệu hoặc mở bảng tính có sẵn mà bạn cần lọc trên Excel.

• Bước 2: Chọn vùng chứa dữ liệu bằng cách bôi đen toàn bộ dữ liệu của bảng tính, hoặc chọn hàng tiêu để mà bạn muốn lọc.
• Bước 3: Bạn bấm vào tab Home, sau đó click chọn Sort & Filter rồi bấm vào Filter. Ngoài ra, bạn cũng có thể thử cách khác đó là tại tab Data, bạn chọn Filter.

Sử dụng Auto Filter

Để sử dụng hàm lọc dữ liệu có điều kiện trong Excel với bộ lọc mặc định, bạn thực hiện các bước sau:

Bước 1: Click vào dấu mũi tên nằm ngay bên cạnh mục bạn muốn lọc.

Bước 2: Bây giờ, bạn lần lượt bấm chọn các tiêu chí mà mình muốn lọc, chẳng hạn như tick chọn ô “Nam” để lọc những người có giới tính là nam.

Bước 3: Sau khi chọn tiêu chí xong, bạn bấm OK và hệ thống sẽ bắt đầu lọc. Những dữ liệu đáp ứng tiêu chí được chọn sẽ được hiển thị, trong khi đó, các dữ liệu không thỏa mãn điều kiện sẽ bị ẩn đi.

Sử dụng Advanced Filter

Với bộ lọc dữ liệu nâng cao, bạn thực hiện các bước sau:

Bước 1: Đầu tiên, bạn cần tạo một bảng tiêu chí lọc mới. Cần lưu ý là tiêu đề của các tiêu chí dùng để lọc này phải trùng với tiêu đề bạn đã đặt trong bảng dữ liệu.

Bước 2: Bạn bấm vào mục Data, sau đó chọn Sort & Filter rồi click vào Advanced.

Bước 3: Tại đây, bạn cần quan tâm đến một số thông số lọc như:

• Action: Tại đây, bạn cần xác định được vị trí kết quả dữ liệu sau khi lọc được xuất là ở đâu. Nếu chọn [Filter the list, in-place], kết quả sẽ được xuất và hiển thị trong bảng dữ liệu được lọc, còn [Copy to another location] được hiểu là việc hiển thị dữ liệu ở một vị trí khác trong trang tính.
• List Range: Khu vực được xác định để lọc dữ liệu.
• Criteria range: Bảng chứa các tiêu chí (điều kiện) để lọc.
• Copy to: Nếu như bạn đã chọn [Copy to another location] ở mục Action như đã đề cập ở trên, bạn cần phải cung cấp vị trí xuất và hiển thị kết quả dữ liệu lọc cụ thể tại đây.

Bước 4: Bấm OK và kết quả sẽ hiển thị trong giây lát theo đúng điều kiện bạn đã đặt ra.

Cách dùng FILTER - Hàm lọc dữ liệu có điều kiện trong Excel

Bên cạnh bộ lọc FILTER, bạn cũng có thể sử dụng một hàm lọc dữ liệu có điều kiện trong Excel có tên tương tự là FILTER để tìm kiếm và lọc dữ liệu dựa trên 1 hoặc nhiều điều kiện được xác định ban đầu. Tuy nhiên, cần lưu ý là hàm này hiện chỉ mới hỗ trợ cho Excel 365.

Công thức của hàm FILTER được quy định là:

=FILTER(array,include,if_empty)

Trong đó:

• Array là yếu tố bắt buộc phải có, tại đây bạn cần cung cấp phạm vi hoặc mảng dữ liệu ban đầu mà bạn muốn lọc
• Include cũng là một thành phần bắt buộc, dùng để xác định điều kiện và cột dữ liệu bạn muốn lọc.
• If_empty là một tùy chọn, cho phép bạn chỉ định kết quả trả về trong những trường hợp không có bất kỳ giá trị nào thỏa mãn những điều kiện được đặt ra bên trên. Nếu bỏ trống giá trị này, kết quả trả về sẽ là một vùng trống.

Lưu ý cần biết khi sử dụng hàm lọc dữ liệu có điều kiện trong Excel

Khi sử dụng hàm FILTER, bạn cần lưu ý một số điều sau:

• Hàm FILTER chỉ mới hỗ trợ cho phiên bản Microsoft Excel 365.
• Trong [Array], hàm không yêu cầu bắt buộc phải có hàng tiêu đề.
• Trong [Include], bạn cần đảm bảo rằng tham số này chứa số hàng/số cột tương ứng với phạm vi/mảng dữ liệu bạn muốn lọc.
• Sau khi gõ cú pháp và bấm Enter, kết quả sẽ hiển thị dưới dạng mảng dữ liệu và không đi kèm với định dạng của khu vực dữ liệu gốc được dùng để lọc.

Một số lỗi thường gặp khi sử dụng hàm lọc dữ liệu có điều kiện trong Excel

Trong một số trường hợp, bạn có thể sẽ bắt gặp một số lỗi khi sử dụng hàm FILTER. Tuy nhiên, bạn không cần phải quá lo lắng, hãy tham khảo ngay một số lỗi thường gặp, nguyên nhân và cách khắc phục được chia sẻ tại phần này của bài viết:

Lỗi #CALC!

Nếu như bảng tính của bạn không có bất kỳ dữ liệu nào thỏa mãn điều kiện được áp dụng để lọc, lỗi #CALC! sẽ xuất hiện. Lúc này, bạn chỉ cần nhập thêm [If_empty] hoặc chuyển sang lọc dữ liệu với các điều kiện khác.

Lỗi #NA!, #VALUE!

#NA!, #VALUE! thường xuất hiện khi [Include] chứa điều kiện bất hợp lý hoặc giá trị lỗi. Lúc này, bạn chỉ cần kiểm tra và chỉnh sửa lại [Include] là được.

Lỗi #SPILL!

Bạn sẽ bắt gặp lỗi #SPILL khi trong khu vực xuất và hiển thị kết quả đã chứa những giá trị khác. Vậy nên, hãy đảm bảo rằng vùng xuất dữ liệu mà bạn chọn là vùng trống và không có bất kỳ giá trị nào nằm lẫn trong đó.

Bài tập ví dụ về cách dùng FILTER - hàm lọc dữ liệu có điều kiện trong Excel

Để rõ hơn về cách sử dụng hàm lọc dữ liệu có điều kiện trong Excel, bạn có thể tham khảo một số ví dụ sau.

Lưu ý: Ví dụ dưới đây được triển khai trên Microsoft Excel 365.

Bài tập: Giảng viên yêu cầu bạn lọc thông tin cá nhân và điểm thi của các bạn cùng lớp như hình bên dưới. Bạn cần phải làm gì?

Lọc 1 điều kiện

Với bảng trên, bạn có thể sử dụng hàm lọc dữ liệu có điều kiện trong Excel - FILTER. Chẳng hạn như bạn cần lọc ra các sinh viên là nam giới, bạn có cú pháp sau:

=FILTER(A1:E16,B1:B16=”nam”)

Sau khi nhập cú pháp và bấm Enter, kết quả trả về sẽ được hiển thị như hình dưới đây:

Lúc này, bạn sẽ nhìn thấy một bảng gồm 5 học sinh nam xuất hiện ở ô bạn đã nhập hàm FILTER.

Trong trường hợp bạn muốn tìm những sinh viên có điểm thi từ 9 trở lên, cú pháp tương ứng sẽ là:

=FILTER(A1:E16,E1:E16>=9)

Lọc 2 điều kiện

Hoặc bạn muốn lọc ra các bạn sinh viên nam có điểm thi trên 9 điểm, hãy kết hợp 2 điều kiện lọc bằng dấu sao (*) như cú pháp dưới đây:

=FILTER(A1:E16,(B1:B16=”nam”)*(E1:E16>=9))

Lọc 2 điều kiện và sắp xếp theo thứ tự

Để lọc các sinh viên nữ đạt từ 9 điểm thi trở lên và sắp xếp danh sách đó theo thứ tự năm sinh tăng dần, bạn cần kết hợp hàm lọc dữ liệu có điều kiện trong Excel - FILTER và hàm sắp xếp - SORT.

Cụ thể, hàm SORT trong Excel thường được dùng để sắp xếp các dữ liệu trong một phạm vi hoặc mảng dữ liệu theo thứ tự từ tăng dần hoặc giảm dần. Cú pháp của hàm SORT là:

=SORT(array,sort_index,sort_order,by_col)

Trong đó:

• Array: Là phạm vi/mảng dữ liệu bạn muốn sắp xếp theo giá trị tăng dần hoặc giảm dần. Đây là thành phần bắt buộc trong hàm.
• Sort_index: Đây là một tùy chọn, được dùng để chỉ ra cột/hàng dữ liệu mà bạn muốn sắp xếp (cột 1, hàng 1), nếu bỏ trống ô này, hàm sẽ sắp xếp dựa trên cả mảng dữ liệu.
• Sort_order: Đây là một tùy chọn, được dùng để chỉ định thứ tự sắp xếp là tăng dần hay giảm dần. Nếu bỏ trống ô này, Excel sẽ sắp xếp theo thứ tự mặc định là thứ tự tăng dần.
• By_col: Đây là một tùy chọn. Trong đó, TRUE là sắp xếp theo cột còn FALSE là theo hàng. Nếu bỏ trống ô này, Excel sắp xếp theo mặc định là theo cột.

Quay lại ví dụ, chúng ta có cú pháp:

=SORT(FILTER(A1:E16,(B1:B16=”nữ”)*(E1:E16>=9)),3,1)

Trong đó, số “3” dùng để chỉ định sắp xếp theo năm sinh và số 1 được dùng để chỉ định sắp xếp theo giá trị tăng dần. Ta có kết quả là:

Lời kết

Bên trên là cách sử dụng hàm lọc dữ liệu có điều kiện trong Excel và cách khắc phục một số lỗi thường gặp mà bạn có thể tham khảo. Hy vọng bài viết sẽ hữu ích và đừng quên để lại bình luận bên dưới nếu có bất kỳ thắc mắc hay câu hỏi nào cần được giải đáp, bạn nhé!

Xem thêm:

• Cách dùng hàm Countifs trong Excel
• Cách chuyển chữ thường thành chữ hoa và ngược lại trong Word, Excel nhanh nhất