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 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ể. Khi đó, bạn có thể tham khảo bài viết dưới đây để cùng tuyengiaothudo.vn tìm hiểu cách sử 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.

Có thể bạn quan tâm

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

Các 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ừ bảng tính hoặc phạm vi dữ liệu dựa trên các điều kiện cụ thể. Sau đây là một số trường hợp mà bạn có thể cần bộ lọc hoặc hàm này:

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

Lọc dữ liệu thỏa mãn một hoặc nhiều điều kiện: Khi bạn 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ể sử dụng chức năng lọc này. Ví dụ, lọc danh sách khách hàng từ 30 tuổi trở lên có 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 chức năng lọc dữ liệu có điều kiện trong Excel để tóm tắt thành một bảng tính nhỏ hơn. Ví dụ, bạn có thể 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 một văn phòng/công ty.
Lọc dữ liệu động 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 các điều kiện thay đổi. Ví dụ: tạo bảng hiển thị số liệu kết quả cho 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ự như vậy, 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 số cho từng mặt hàng, bạn có thể lọc và tính tổng cho từng sản phẩm riêng lẻ.

Cách sử dụng bộ lọc FILTER trong Excel

Để lọc dữ liệu, bạn có thể sử dụng chức năng 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ụ Excel.

Trước khi tìm hiểu cách sử 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 đáp ứng điều kiện sẽ được hiển thị, trong khi dữ liệu còn lại sẽ bị ẩn.

Trong Excel có 2 hàm lọc cơ bản:

Bộ lọc tự động: Bộ lọc mặc định.
Bộ lọc nâng cao: Bộ lọc nâng cao.

Đầu tiên, bạn cần tạo bộ lọc FILTER như thế này:

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

Bước 2: Chọn vùng dữ liệu bằng cách tô sáng toàn bộ dữ liệu trong bảng tính hoặc chọn hàng tiêu đề mà bạn muốn lọc.
Bước 3: Nhấp vào tab Trang chủ, sau đó nhấp vào Sắp xếp & Lọc rồi nhấp vào Lọc. Ngoài ra, bạn cũng có thể thử cách khác: trên tab Dữ liệu, chọn Lọc.

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

Để sử dụng chức năng lọc dữ liệu có điều kiện trong Excel với bộ lọc mặc định, hãy làm theo các bước sau:

Bước 1: Nhấp vào mũi tên bên cạnh mục bạn muốn lọc.

Bước 2: Bây giờ, bạn nhấp để chọn tiêu chí bạn muốn lọc, chẳng hạn như đánh dấu vào “Nam giới” hộp để lọc những người có giới tính nam.

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

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

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

Bước 1: Trước tiên, bạn cần tạo một bảng tiêu chí lọc mới. Lưu ý rằng tiêu đề của tiêu chí được sử dụng cho bộ lọc này phải khớp với tiêu đề bạn đặt trong bảng dữ liệu.

Bước 2: Nhấp vào Dữ liệu, sau đó chọn Sắp xếp & Lọc và nhấp vào Nâng cao.

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

Hành động: Ở đây, bạn cần xác định nơi kết quả dữ liệu đã lọc được xuất ra. Nếu bạn chọn [Filter the list, in-place]kết quả sẽ được xuất ra và hiển thị trong bảng dữ liệu đã lọc và [Copy to another location] được hiểu là hiển thị dữ liệu ở một vị trí khác trong bảng tính.
Phạm vi danh sách: Khu vực được xác định để lọc dữ liệu.
Phạm vi tiêu chí: Bảng chứa các tiêu chí (điều kiện) để lọc.
Sao chép vào: Nếu bạn đã chọn [Copy to another location] Trong phần Hành động như đã đề cập ở trên, bạn cần cung cấp vị trí đầu ra và hiển thị kết quả dữ liệu đã lọc cụ thể tại đây.

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

Cách sử 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 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 trước. Tuy nhiên, cần lưu ý rằng hàm này hiện chỉ được hỗ trợ cho Excel 365.

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

=FILTER(mảng,bao gồm,nếu_trống)

Trong đó:

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

Những điề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 ý những điều sau:

Hàm FILTER chỉ được hỗ trợ cho phiên bản Microsoft Excel 365.

Hàm FILTER chỉ được hỗ trợ trong phiên bản Microsoft Excel 365.
TRONG [Array]chức năng này không yêu cầu hàng tiêu đề.
TRONG [Include]bạn cần đảm bảo rằng tham số này chứa số hàng/cột tương ứng với phạm vi/mảng dữ liệu bạn muốn lọc.
Sau khi nhập cú pháp và nhấn Enter, kết quả sẽ được hiển thị dưới dạng một mảng dữ liệu và không theo định dạng của vùng 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ể gặp phải một số lỗi khi sử dụng hàm FILTER. Tuy nhiên, bạn không cần quá lo lắng, hãy tham khảo một số lỗi thường gặp, nguyên nhân và cách khắc phục được chia sẻ trong phần này của bài viết:

Lỗi #CALC!

Nếu bảng tính của bạn không có bất kỳ dữ liệu nào đáp ứng tiêu chí lọc, lỗi #CALC! sẽ xuất hiện. Tại thời điểm này, chỉ cần nhập [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 các điều kiện không 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ẽ nhận được lỗi #SPILL khi vùng đầu ra và vùng hiển thị đã chứa các giá trị khác. Vì vậy, hãy đảm bảo rằng vùng đầu ra bạn chọn là trống và không có bất kỳ giá trị nào được trộn lẫn trong đó.

Bài tập ví dụ về cách sử 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 các 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áo viên yêu cầu bạn lọc thông tin cá nhân và điểm kiểm tra của các bạn cùng lớp như hình dưới đây. Bạn nên 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. Ví dụ, nếu bạn cần lọc ra sinh viên nam, bạn có cú pháp sau:

Xem thêm : Cách tra cứu quá trình đóng BHXH chính xác

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

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

Bây giờ, bạn sẽ thấy một bảng gồm 5 học sinh nam xuất hiện trong ô mà bạn đã nhập hàm FILTER.

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

=BỘ LỌC(A1:E16,E1:E16>=9)

Lọc 2 điều kiện

Hoặc nếu bạn muốn lọc ra những học sinh 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 hoa thị

như cú pháp dưới đây:

=9))” chiều rộng=”800″ chiều cao=”450″ kích thước dữ liệu=”(chiều rộng tối đa: 800px) 100vw, 800px”/>

bộ lọc dữ liệu có điều kiện trong excel 17

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

Để lọc những nữ sinh đạt 9 điểm trở lên và sắp xếp danh sách theo thứ tự tăng dần của năm sinh, 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 sử dụng để sắp xếp dữ liệu trong một phạm vi hoặc mảng dữ liệu theo thứ tự tăng dần hoặc giảm dần. Cú pháp của hàm SORT là:

=SORT(mảng,chỉ số sắp xếp,thứ tự sắp xếp,theo cột)

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

By_col: Tùy chọn. TRUE là sắp xếp theo cột và FALSE là sắp xếp theo hàng. Nếu hộp này để trống, Excel sẽ sắp xếp theo cột theo mặc định.

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

=SẮP XẾP(BỘ LỌC(A1:E16,(B1:B16=”nữ”)*(E1:E16>=9)),3,1)

bộ lọc dữ liệu có điều kiện trong excel 18

Lời kết

Trên đây 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 bạn có bất kỳ thắc mắc hay câu hỏi nào cần được giải đáp nhé!

Xem thêm:

Nguồn: https://tuyengiaothudo.vn
Danh mục: Thủ thuật