Hàm FILTER lọc dữ liệu tự động trong Excel
Mỗi lần cần danh sách đơn hàng của một nhân viên, một khu vực, bạn lại bật AutoFilter, lọc, copy sang sheet khác rồi dán? Dữ liệu gốc thay đổi là phải làm lại từ đầu. Hàm FILTER trên Excel 365 và Excel 2021 trả về cả bảng kết quả chỉ bằng một công thức, dữ liệu gốc đổi thì kết quả tự cập nhật.
Ví dụ: cột A là Ngày, cột B là Nhân viên, cột C là Khu vực, cột D là Doanh số, dữ liệu từ dòng 2 đến dòng 100, dòng 1 là tiêu đề.
1. Cú pháp FILTER
• =FILTER(vùng_dữ_liệu; điều_kiện; [nếu_rỗng])
• vùng_dữ_liệu: bảng cần lấy ra, ví dụ A2:D100.
• điều_kiện: một vùng cùng số dòng, cho kết quả TRUE/FALSE, ví dụ B2:B100="An".
• nếu_rỗng (không bắt buộc): nội dung hiện ra khi không có dòng nào thỏa, ví dụ "Không có". Bỏ trống phần này thì Excel báo lỗi #CALC!.
2. Lọc theo một điều kiện
• Đơn hàng của nhân viên An: =FILTER(A2:D100;B2:B100="An").
• Lấy tên nhân viên từ ô G1 để đổi nhanh: =FILTER(A2:D100;B2:B100=G1;"Không có"). Kết hợp với danh sách thả xuống (Data Validation) ở G1 là có ngay một bảng tra cứu gọn.
• Doanh số trên 5 triệu: =FILTER(A2:D100;D2:D100>5000000).
• Đơn hàng tháng 10: =FILTER(A2:D100;MONTH(A2:A100)=10).
3. Nhiều điều kiện VÀ, HOẶC
• Điều kiện VÀ dùng dấu nhân: =FILTER(A2:D100;(B2:B100="An")*(D2:D100>5000000)) — đơn của An và doanh số trên 5 triệu.
• Điều kiện HOẶC dùng dấu cộng: =FILTER(A2:D100;(C2:C100="HN")+(C2:C100="HCM")) — đơn ở Hà Nội hoặc TP.HCM.
• Mỗi điều kiện phải đặt trong ngoặc đơn riêng, nếu không Excel tính sai thứ tự.
• Lọc theo chữ chứa trong ô (tên có chữ "Minh"): =FILTER(A2:D100;ISNUMBER(SEARCH("Minh";B2:B100))).
4. Kết hợp SORT, chỉ lấy vài cột
• Lọc rồi sắp xếp doanh số giảm dần: =SORT(FILTER(A2:D100;D2:D100>0);4;-1) (4 là cột thứ 4 của kết quả, -1 là giảm dần).
• Chỉ lấy cột Nhân viên và Doanh số: =CHOOSECOLS(FILTER(A2:D100;C2:C100="HN");2;4) (Excel 365).
• Tổng doanh số các dòng đã lọc: =SUM(FILTER(D2:D100;B2:B100="An";0)).
5. Lỗi thường gặp và lưu ý
• Lỗi #SPILL!: kết quả cần trải xuống nhiều ô nhưng bên dưới hoặc bên phải đang có dữ liệu. Xóa trống vùng đó là hết lỗi.
• Lỗi #CALC!: không có dòng nào thỏa điều kiện và chưa điền đối số nếu_rỗng.
• Lỗi #VALUE!: vùng điều kiện lệch số dòng so với vùng dữ liệu.
• Lỗi #NAME?: máy đang dùng Excel 2019 trở về trước, chưa có FILTER. Khi đó dùng AutoFilter hoặc Advanced Filter.
• Nên chuyển bảng gốc thành Table (Ctrl + T) để thêm dòng mới vẫn được lọc tự động. Máy Excel Việt Nam dùng dấu ; giữa đối số, máy US dùng dấu ,.
Lời Kết
FILTER thay cho thao tác lọc, copy, dán lặp đi lặp lại: viết một lần, dữ liệu gốc thay đổi thì bảng kết quả tự đổi theo. Nhớ dấu * cho điều kiện VÀ, dấu + cho điều kiện HOẶC, và để trống vùng bên dưới công thức. Bạn đang lọc báo cáo bằng AutoFilter hay đã chuyển sang FILTER? Chia sẻ ở phần bình luận nhé.
• Tác giả: Group Thủ thuật Excel Word PowerPoint
• Chủ đề: Tin học văn phòng / Thủ thuật Excel
Không có nhận xét nào:
Đăng nhận xét