Sử dụng hàm lọc điều kiện trong Excel và các vấn đề thường gặp

Khi bạn cần sử dụng hàm lọ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ể. Xem thêm thông tin về bộ lọc FILTER, hàm FILTER và cách sửa một số lỗi phổ biến khi lọc dữ liệu trong Excel tại Mytour.

Khi nào bạn nên dùng hàm lọc điều kiện trong Excel?

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

• Lọc dữ liệu thỏa mãn một hoặc nhiều điều kiện: Khi muốn hiển thị dữ liệu thỏa mãn một hoặc nhiều điều kiện cụ thể, bạn có thể áp dụng hàm lọc này. Ví dụ, lọc danh sách khách hàng có độ tuổi từ 30 trở lên và số tiền mua hàng trên 10 triệu đồng.
• Tạo bảng dữ liệu mới: 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. Ví dụ, tạo các bảng lương riêng cho từng nhân viên từ bảng lương chung của 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 bảng hoặc biểu đồ động dựa trên điều kiện thay đổi. Ví dụ, 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 chi tiết kết quả.
• 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 các phép tính như tổng, trung bình, min/max… Ví dụ, để 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 sử 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 tìm hiểu cách sử dụng hàm FILTER, chúng ta sẽ cùng khám phá về bộ lọc FILTER. Bộ lọc FILTER trong Excel cho phép người dùng lọc dữ liệu thỏa mãn 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ẽ được hiển thị, trong khi những dữ liệu khác sẽ bị ẩn đi.

Trong Excel, có hai tính năng lọc cơ bản như sau:

• Auto Filter: Bộ lọc tự động.
• Advanced Filter: Bộ lọc nâng cao.

Để tạo bộ lọc FILTER, bạn cần làm như sau:

• Bước 1: Nhập dữ liệu hoặc mở bảng tính có sẵ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 mục mà bạn muốn lọc.
• Bước 3: Nhấp vào tab Home, sau đó chọn Sort & Filter và bấm vào Filter. Ngoài ra, bạn cũng có thể thử cách khác là trên tab Data, bạn chọn Filter.

Sử dụng Bộ lọc Tự động

Để 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 mũi tên bên cạnh mục bạn muốn lọc.

Bước 2: Tiếp theo, bạn lần lượt chọn các tiêu chí mà bạn muốn lọc, ví dụ như tick chọn ô “Nam” để lọc những người có giới tính là nam.

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

Sử dụng Bộ lọc Nâng cao

Đối 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. Chú ý rằng tên các tiêu chí này phải trùng với tên đã đặt trong bảng dữ liệu của bạn.

Bước 2: Bạn chọn tab Data, sau đó chọn Sort & Filter và click vào Advanced.

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

Bước 4: Nhấn OK và kết quả sẽ hiển thị ngay lập tức theo điều kiện bạn đã đặt ra.

Sử dụng FILTER – Hàm lọc dữ liệu có điều kiện trong Excel

Ngoài 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 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 trước. Tuy nhiên, lưu ý rằng hàm này chỉ hỗ trợ trong Excel 365 hiện tại.

Công thức của hàm FILTER được định nghĩa như sau:

=FILTER(array,include,if_empty)

Trong công thức này:

Những điều cần lưu ý 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 chú ý những điều sau đây:

• Hàm FILTER chỉ hỗ trợ trong Microsoft Excel 365.
• Trong [Mảng], hàm không yêu cầu phải có hàng tiêu đề.
• Trong [Bao gồm], bạn cần đảm bảo rằng tham số này chứa số hàng hoặc số cột tương ứng với phạm vi dữ liệu bạn muốn lọc.
• Sau khi nhập công thức và nhấn Enter, kết quả sẽ hiển thị dưới dạng một mảng dữ liệu và không giữ định dạng khu vực dữ liệu gốc được sử 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ể gặp phải một số lỗi khi sử dụng hàm FILTER. Tuy nhiên, đừng quá lo lắng, bạn có thể xem ngay các lỗi thường gặp, nguyên nhân và cách khắc phục được chia sẻ ở đây:

Lỗi #CALC!

Nếu bảng tính của bạn không có dữ liệu nào thỏa mãn các điều kiện lọc, lỗi #CALC! sẽ xuất hiện. Đơn giản chỉ cần thêm [Nếu trống] 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 [Bao gồm] chứa các điều kiện không hợp lý hoặc giá trị lỗi. Đơn giản chỉ cần kiểm tra và điều chỉnh lại [Bao gồm] là được.

Lỗi #SPILL!

Bạn sẽ gặp phải lỗi #SPILL khi khu vực xuất và hiển thị kết quả bị chồng chéo các giá trị khác. Vì vậy, 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 bê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

Để hiểu 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ể xem các ví dụ sau đây.

Lưu ý: Các ví dụ dưới đây được thực hiện 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 làm gì?

Lọc 1 điều kiện

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

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

Sau khi nhập cú pháp và nhấn Enter, kết quả trả về sẽ được hiển thị như trong hình sau:

Kết quả sẽ là một bảng gồm 5 học sinh nam xuất hiện trong ô mà bạn đã nhập hàm FILTER.

Trường hợp bạn muốn tìm các sinh viên có điểm thi từ 9 trở lên, bạn có thể sử dụng cú pháp sau đây:

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

Lọc 2 điều kiện

Ngoài ra, để lọc ra các sinh viên nam có điểm thi trên 9 điểm, bạn có thể kết hợp 2 điều kiện lọc với dấu sao (*) như cú pháp sau:

=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ữ có điểm thi từ 9 điểm 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 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: 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: Tùy chọn, chỉ ra cột/hàng dữ liệu bạn muốn sắp xếp (cột 1, hàng 1), nếu không nhập, hàm sẽ sắp xếp dựa trên toàn bộ mảng dữ liệu.
• Sort_order: Tùy chọn, xác định thứ tự sắp xếp là tăng dần hoặc giảm dần. Nếu không nhập, Excel sẽ sắp xếp theo thứ tự tăng dần mặc định.
• By_col: Tùy chọn, TRUE để sắp xếp theo cột, FALSE để sắp xếp theo hàng. Nếu không nhập, Excel sắp xếp theo cột mặc định.

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

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

Số '3' ở đây chỉ định sắp xếp theo năm sinh và số '1' chỉ định sắp xếp theo thứ tự tăng dần. Kết quả như sau:

Lời kết

Đó 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. Hy vọng bài viết hữu ích và đừng ngần ngại để lại câu hỏi nếu cần giải đáp thêm!