Excel cung cấp 2 phương pháp chính để lọc dữ liệu có điều kiện: hàm FILTER (cho Excel 365/2019) và Advanced Filter (mọi phiên bản).
Hàm FILTER sử dụng cú pháp =FILTER(array, include, [if_empty]) để lọc động và trả về kết quả tự động. Ví dụ: =FILTER(A2:B10, B2:B10>10000000) lọc nhân viên có lương trên 10 triệu.
Advanced Filter phù hợp cho điều kiện phức tạp, yêu cầu tạo bảng điều kiện riêng. Thực hiện qua Data > Advanced, thiết lập List range (dữ liệu gốc), Criteria range (bảng điều kiện) và Copy to (vị trí xuất kết quả). Phương pháp này mạnh mẽ hơn cho các truy vấn phức tạp và có thể xuất dữ liệu đến vị trí khác.
Lọc dữ liệu có điều kiện trong Excel có hai công cụ chính: hàm FILTER cho các phiên bản mới và Advanced Filter cho mọi phiên bản. Tôi đã thử nghiệm cả hai cách này với hàng nghìn dòng dữ liệu khách hàng, và mỗi công cụ có thế mạnh riêng tùy thuộc vào độ phức tạp của điều kiện lọc.
Điều thú vị là nhiều người chỉ biết đến AutoFilter cơ bản, nhưng khi bạn cần lọc theo nhiều tiêu chí phức tạp hoặc muốn kết quả tự động cập nhật, hai công cụ này mới thực sự phát huy sức mạnh. Hàm FILTER cho kết quả động và linh hoạt, trong khi Advanced Filter giúp xử lý các điều kiện phức tạp mà hàm thông thường không làm được.
Hàm FILTER – Công Cụ Lọc Động Cho Excel Hiện Đại
Hàm FILTER chỉ có sẵn trong Microsoft 365 và Excel 2019 trở lên, nhưng đây chính là lý do tôi khuyên mọi người nâng cấp. Khác với Advanced Filter, FILTER tạo ra kết quả tự động cập nhật mỗi khi dữ liệu gốc thay đổi.
Cú Pháp và Tham Số Của Hàm FILTER
=FILTER(array, include, [if_empty])
- array: Vùng dữ liệu bạn muốn lọc
- include: Điều kiện lọc (mảng logic cùng số hàng với array)
- if_empty: Giá trị hiển thị khi không có dữ liệu nào thỏa mãn
Tôi thường đặt tham số if_empty là “Không có dữ liệu” để tránh hiển thị lỗi #CALC! khi không tìm thấy kết quả nào.
Lọc Theo Một Điều Kiện Đơn Giản
Giả sử tôi có danh sách nhân viên từ A2:C10 với các cột: Tên (A), Phòng ban (B), Lương (C). Để lọc những nhân viên có lương trên 15 triệu:
=FILTER(A2:C10, C2:C10>15000000, “Không có nhân viên nào”)
Kết quả sẽ hiển thị ngay lập tức tất cả thông tin của những nhân viên thỏa mãn điều kiện. Điều tuyệt vời là khi tôi thay đổi mức lương của bất kỳ nhân viên nào, danh sách lọc tự động cập nhật.
Lọc Theo Nhiều Điều Kiện
Để lọc nhân viên phòng “Kinh doanh” có lương trên 12 triệu, tôi sử dụng phép AND:
=FILTER(A2:C10, (B2:B10=”Kinh doanh”)*(C2:C10>12000000), “Không tìm thấy”)
Dấu * ở đây đóng vai trò như phép AND. Nếu muốn dùng phép OR (nhân viên phòng “Kinh doanh” HOẶC có lương trên 20 triệu):
=FILTER(A2:C10, (B2:B10=”Kinh doanh”)+(C2:C10>20000000), “Không tìm thấy”)
Lọc Dữ liệu Văn Bản Với Ký Tự Đại Diện
Khi tôi muốn tìm tất cả nhân viên có tên chứa “Anh”, tôi kết hợp FILTER với SEARCH:
=FILTER(A2:C10, ISNUMBER(SEARCH(“Anh”, A2:A10)), “Không tìm thấy”)
Hàm SEARCH trả về vị trí của chuỗi “Anh” trong tên, ISNUMBER kiểm tra xem có tìm thấy hay không. Cách này không phân biệt hoa thường và tìm được cả “Anh”, “Khánh”, “Thành”.
Advanced Filter – Giải Pháp Mạnh Mẽ Cho Mọi Phiên Bản Excel
Advanced Filter là công cụ tôi dùng khi cần lọc theo điều kiện phức tạp hoặc khi làm việc với Excel cũ. Điểm mạnh của nó là khả năng tạo bảng điều kiện trực quan và linh hoạt.
Chuẩn Bị Bảng Điều Kiện
Trước khi sử dụng Advanced Filter, tôi luôn tạo một bảng điều kiện riêng. Ví dụ, để lọc nhân viên có lương từ 10-20 triệu trong phòng “Kỹ thuật” hoặc “Kinh doanh”:
| Tên | Phòng ban | Lương | Tên | Phòng ban | Lương |
|---|---|---|---|---|---|
| Kỹ thuật | >=10000000 | Kinh doanh | >=10000000 | ||
| <=20000000 | <=20000000 |
Cách bố trí này cho phép tôi tạo điều kiện OR giữa các cột và AND giữa các hàng trong cùng một cột.
Thực Hiện Advanced Filter
- Chọn toàn bộ vùng dữ liệu gốc (bao gồm tiêu đề)
- Vào tab Data → Advanced trong nhóm Sort & Filter
- Trong hộp thoại Advanced Filter:
– List range: Vùng dữ liệu gốc (A1:C10)
– Criteria range: Vùng bảng điều kiện (E1:J3)
– Copy to another location: Tích chọn nếu muốn kết quả ở vị trí khác
– Copy to: Chọn ô đầu tiên để dán kết quả
- Nhấn OK
Các Loại Điều Kiện Phức Tạp
Lọc theo khoảng thời gian: Tôi thường đặt điều kiện >=01/01/2024 và <=31/12/2024 trong cùng một cột để lọc dữ liệu theo năm.
Lọc loại trừ: Để lọc tất cả trừ phòng "Hành chính", tôi dùng <>Hành chính trong cột Phòng ban.
Lọc theo danh sách: Khi cần lọc nhiều phòng ban cụ thể, tôi tạo nhiều hàng điều kiện:
| Phòng ban |
|---|
| Kỹ thuật |
| Kinh doanh |
| Marketing |
So Sánh FILTER vs Advanced Filter
| Tiêu chí | Hàm FILTER | Advanced Filter |
|---|---|---|
| Phiên bản Excel | Microsoft 365, Excel 2019+ | Mọi phiên bản |
| Cập nhật tự động | Có | Không |
| Điều kiện phức tạp | Hạn chế | Rất mạnh |
| Dễ sử dụng | Cao | Trung bình |
| Hiệu suất | Tốt với dữ liệu nhỏ | Tốt với dữ liệu lớn |
Mẹo Thực Tế Từ Kinh Nghiệm Của Tôi
Tối Ưu Hiệu Suất
Với hàm FILTER, tôi tránh sử dụng toàn bộ cột (A:A) mà chỉ chọn vùng dữ liệu cần thiết. Thay vì A:C, tôi dùng A2:C1000 để Excel không phải kiểm tra hàng triệu ô trống.
Xử Lý Lỗi Thông Minh
Khi dùng FILTER với điều kiện phức tạp, tôi thường wrap trong hàm IFERROR:
=IFERROR(FILTER(A2:C10, (B2:B10="Kỹ thuật")*(C2:C10>15000000)), "Không có dữ liệu phù hợp")
Kết Hợp Với Các Hàm Khác
Một trick hay là kết hợp FILTER với SORT để có kết quả đã sắp xếp:
=SORT(FILTER(A2:C10, C2:C10>10000000), 3, -1)
Lệnh này lọc nhân viên có lương trên 10 triệu và sắp xếp theo cột thứ 3 (lương) từ cao xuống thấp.
Khi Nào Dùng Công Cụ Nào?
Sau 3 năm làm việc với cả hai công cụ, tôi đã rút ra quy tắc đơn giản:
- Dùng FILTER khi: Cần kết quả tự động cập nhật, điều kiện đơn giản đến trung bình, làm việc với Excel mới
- Dùng Advanced Filter khi: Cần điều kiện phức tạp, làm việc với Excel cũ, muốn lưu bảng điều kiện để tái sử dụng
Tôi thường bắt đầu với FILTER cho các tác vụ hàng ngày, và chuyển sang Advanced Filter khi cần xử lý báo cáo phức tạp cuối tháng. Cả hai công cụ đều có chỗ đứng riêng trong bộ kỹ năng Excel, và việc thành thạo cả hai giúp tôi xử lý mọi tình huống lọc dữ liệu một cách hiệu quả.
