Hướng dẫn dùng hàm FILTER trong Excel lọc dữ liệu chi tiết

Hướng dẫn chi tiết cách sử dụng hàm FILTER trong Excel từ cơ bản đến nâng cao. Tìm hiểu cú pháp, ví dụ thực tế và mẹo lọc dữ liệu hiệu quả.

Mục lục

Trong công việc văn phòng hiện đại, xử lý dữ liệu lớn là nhiệm vụ thường xuyên đòi hỏi hiệu quả cao. Excel cung cấp nhiều công cụ lọc dữ liệu, nhưng hàm FILTER là giải pháp hiện đại nhất cho người dùng Excel 365 và Excel 2021 trở lên. Hàm này thay thế hoàn toàn bộ lọc truyền thống với khả năng trả về kết quả động — tự động cập nhật khi dữ liệu nguồn thay đổi, không cần thao tác thủ công lặp đi lặp lại.

Sử dụng bộ lọc FILTER

Excel có hai loại bộ lọc chính: Auto Filter (lọc cơ bản) và Advanced Filter (lọc nâng cao). Cả hai đều là công cụ truyền thống tồn tại từ các phiên bản Excel cũ, vẫn hữu ích trong nhiều tình huống trước khi hàm FILTER được ra mắt.

Cách sử dụng bộ lọc Filter mặc định (Auto Filter)

Auto Filter là tính năng lọc dữ liệu phổ biến nhất trong Excel, cho phép người dùng nhanh chóng hiển thị các bản ghi thỏa mãn điều kiện cụ thể. Để kích hoạt Auto Filter, chọn bất kỳ ô nào trong bảng dữ liệu, nhấn tổ hợp phím Ctrl+Shift+L hoặc đi đến tab Data → chọn Filter. Excel sẽ tự động thêm các nút mũi tên xuống vào dòng tiêu đề của mỗi cột.

Cơ chế hoạt động của Auto Filter dựa trên việc ẩn các hàng không thỏa mãn điều kiện lọc thay vì xóa chúng. Khi bạn chọn một giá trị từ danh sách thả xuống, Excel quét toàn bộ cột đó, so sánh từng giá trị với điều kiện đã chọn, sau đó ẩn các hàng có giá trị không khớp. Điều này có nghĩa là dữ liệu gốc vẫn được bảo toàn nguyên vẹn, chỉ có cách hiển thị thay đổi.

Ưu điểm của Auto Filter là giao diện trực quan, không cần nhớ cú pháp hàm, phù hợp cho người dùng mới. Tuy nhiên, nhược điểm lớn là nó không tự động cập nhật khi có dữ liệu mới được thêm vào bảng. Bạn phải mở lại filter để cập nhật danh sách, điều này gây bất tiện khi làm việc với dữ liệu thay đổi liên tục.

Cách sử dụng bộ lọc Filter nâng cao (Advanced Filter)

Advanced Filter mở rộng khả năng lọc dữ liệu với điều kiện phức tạp hơn so với Auto Filter. Tính năng này cho phép lọc theo nhiều điều kiện kết hợp với toán tử AND/OR, sao chép kết quả đến vị trí khác, và sử dụng công thức lọc tùy chỉnh.

Để sử dụng Advanced Filter, bạn cần tạo vùng điều kiện (criteria range) — một khu vực riêng biệt trên bảng tính có dòng tiêu đề khớp với tiêu đề bảng dữ liệu và các dòng dưới chứa điều kiện lọc. Excel hiểu rằng các điều kiện trên cùng một hàng dùng toán tử AND (cả hai phải đúng), trong khi điều kiện ở các hàng khác nhau dùng toán tử OR (bất kỳ cái nào đúng cũng được).

Cơ chế xử lý của Advanced Filter khác Auto Filter ở chỗ nó có thể sao chép kết quả đến vị trí khác, tạo ra bảng mới tách biệt khỏi dữ liệu gốc. Điều này hữu ích khi bạn muốn báo cáo lọc mà không làm ảnh hưởng đến bảng gốc. Tuy nhiên, Advanced Filter vẫn là công cụ thủ công — không cập nhật tự động khi dữ liệu thay đổi và yêu cầu người dùng thiết lập vùng điều kiện phức tạp.

Công thức hàm FILTER trong Excel

Hàm FILTER là bước tiến lớn trong Excel, thuộc nhóm hàm dynamic array (mảng động) được giới thiệu từ Excel 365. Cú pháp cơ bản: =FILTER(array, include, [if_empty]) trong đó array là vùng dữ liệu muốn lọc, include là mảng boolean (TRUE/FALSE) xác định điều kiện lọc, và if_empty là giá trị trả về khi không có kết quả.

Điểm khác biệt cốt lõi giữa hàm FILTER và bộ lọc truyền thống là FILTER trả về kết quả dưới dạng mảng động trong các ô được chọn. Khi dữ liệu nguồn thay đổi hoặc điều kiện lọc được điều chỉnh, kết quả tự động cập nhật mà không cần thao tác thủ công. Cơ chế này dựa trên tính năng spilling của Excel — mảng kết quả "tràn" sang các ô xung quanh nếu không gian đủ, tạo ra một vùng kết quả động.

Cơ chế xử lý của FILTER hoạt động như sau: thứ nhất, Excel đánh giá mảng điều kiện include, trả về TRUE cho các hàng thỏa mãn, FALSE cho các hàng không thỏa mãn. Thứ hai, Excel so sánh từng giá trị trong mảng include với các hàng tương ứng trong array, chỉ giữ lại các hàng có include là TRUE. Thứ ba, kết quả được trả về trong vùng ô đã chọn, tự động điều chỉnh kích thước theo số lượng kết quả.

Điều kiện lọc có thể là biểu thức so sánh đơn giản như =FILTER(A2:D100, C2:C100>500, "Không có kết quả") — lọc các hàng có giá trị cột C lớn hơn 500. Nó cũng có thể phức tạp hơn với hàm điều kiện lồng như =FILTER(A2:D100, (C2:C100>500)*(B2:B100="Hà Nội")) — kết hợp hai điều kiện với toán tử nhân (đại diện cho AND logic). Toán tử cộng (+) được dùng cho OR logic, ví dụ (C2:C100>500)+(C2:C100<0) trả về các hàng có giá trị lớn hơn 500 hoặc nhỏ hơn 0.

Lợi ích lớn nhất của hàm FILTER là khả năng lồng các điều kiện phức tạp trong một công thức duy nhất. Thay vì phải tạo vùng điều kiện riêng như Advanced Filter, bạn có thể sử dụng hàm logic như IF, AND, OR, hoặc các hàm chuỗi như LEFT, RIGHT, SEARCH để tạo điều kiện lọc tùy chỉnh trực tiếp trong công thức.

Đội ngũ biên tập Trang Chia Sẻ Thông Tin Công Nghệ Smartphone nhận thấy hàm FILTER đặc biệt hữu ích khi làm việc với các báo cáo phân tích cần cập nhật thường xuyên. Ví dụ, khi quản lý danh mục sản phẩm, bạn có thể dùng FILTER để tự động lọc các sản phẩm tồn kho thấp mà không cần thao thủ công mỗi khi dữ liệu thay đổi.

Một số lưu ý hàm FILTER

Khi làm việc với hàm FILTER, cần lưu ý các đặc tính liên quan đến dynamic array và cách Excel xử lý mảng động. Hiểu rõ các đặc điểm này giúp tránh lỗi phổ biến và tối ưu hiệu suất.

Lưu ý quan trọng nhất là hàm FILTER chỉ có sẵn trong Excel 365, Excel 2021 và phiên bản web Office 365. Các phiên bản Excel cũ hơn (Excel 2019, 2016, 2010) không hỗ trợ dynamic array, do đó sẽ trả về lỗi #SPILL! hoặc #NAME?. Nếu file Excel của bạn cần tương thích với các phiên bản cũ, nên cân nhắc sử dụng hàm INDEX-MATCH kết hợp IF hoặc bộ lọc truyền thống thay vì FILTER.

Lỗi #SPILL! xuất hiện khi vùng kết quả của FILTER bị chặn bởi dữ liệu khác trong các ô xung quanh. Cơ chế spilling của Excel yêu cầu các ô bên dưới và bên phải ô chứa công thức phải trống để kết quả mảng động. Nếu có bất kỳ ô nào trong vùng dự kiến bị chiếm dụng, Excel trả về lỗi SPILL và hiển thị đường viền xanh quanh vùng bị chặn.

Để khắc phục lỗi SPILL, bạn cần xóa hoặc di chuyển dữ liệu đang chiếm chỗ trong vùng kết quả dự kiến. Excel có tính năng Select Obstructing Cells — khi nhấn vào lỗi SPILL, Excel tự động chọn các ô đang gây cản trở. Tính năng này giúp nhanh chóng xác định và xử lý vấn đề, đặc biệt hữu ích khi làm việc với các bảng lớn và phức tạp.

Lỗi #CALC! xuất hiện khi có tham chiếu vòng (circular reference) trong công thức FILTER — ví dụ, hàm FILTER tham chiếu đến chính vùng kết quả của nó. Điều này tạo ra vòng lặp vô hạn vì Excel không thể xác định khi nào công thức sẽ hoàn tất tính toán. Để tránh lỗi này, đảm bảo vùng dữ liệu nguồn (array) và vùng kết quả là hai vùng tách biệt hoàn toàn.

Hiệu suất của FILTER cũng cần lưu ý khi làm việc với dữ liệu lớn. Cơ chế tính toán mảng động của Excel yêu cầu tính toán lại toàn bộ công thức FILTER mỗi khi bất kỳ ô nào trong vùng tham chiếu thay đổi. Với bảng dữ liệu hàng chục nghìn dòng, điều này có thể gây chậm khi nhập liệu. Trong trường hợp này, cân nhắc dùng bộ lọc truyền thống hoặc Power Query để xử lý dữ liệu lớn, sau đó dùng FILTER cho các báo cáo tổng hợp đã được tinh gọn.

Một lưu ý khác là hàm FILTER trả về mảng động, không thể chỉnh sửa từng ô trong kết quả. Nếu bạn cần sửa đổi kết quả lọc (ví dụ, thêm ghi chú, chỉnh số liệu), phải copy kết quả và paste as values (chuyển đổi thành giá trị tĩnh) trước khi chỉnh sửa. Tính năng này đảm bảo tính nhất quán của dữ liệu động nhưng cũng gây hạn chế trong một số tình huống.

Bài tập ví dụ hàm FILTER

Để hiểu rõ cách ứng dụng hàm FILTER vào thực tế, hãy xem xét các ví dụ từ cơ bản đến nâng cao với dữ liệu giả định về quản lý nhân sự.

Ví dụ 1: Lọc danh sách nhân viên theo phòng ban. Giả sử bạn có bảng dữ liệu từ A2:D100 với các cột: Mã nhân viên (A), Họ tên (B), Phòng ban (C), Lương (D). Để lọc tất cả nhân viên thuộc phòng "Kế toán", dùng công thức: =FILTER(A2:D100, C2:C100="Kế toán", "Không có nhân viên nào"). Cơ chế lọc ở đây: Excel so sánh từng giá trị trong vùng C2:C100 với chuỗi "Kế toán", trả về TRUE cho các ô khớp, sau đó lọc các hàng tương ứng trong vùng A2:D100.

Ví dụ 2: Lọc nhân viên có lương cao hơn mức trung bình. Cộng thức: =FILTER(A2:D100, D2:D100>AVERAGE(D2:D100), "Không có kết quả"). Cơ chế hoạt động: Hàm AVERAGE tính giá trị trung bình của cột lương, sau đó FILTER so sánh từng lương cá nhân với giá trị trung bình đó. Điều này hữu ích trong phân tích nhân sự để nhanh chóng xác định nhóm nhân viên có mức thu nhập cao hơn mặt bằng chung của công ty.

Ví dụ 3: Lọc nhân viên thuộc phòng Kế toán VÀ có lương trên 15 triệu. Công thức: =FILTER(A2:D100, (C2:C100="Kế toán")*(D2:D100>15000000), "Không có kết quả"). Toán tử nhân (*) đại diện cho logic AND — cả hai điều kiện phải đúng cùng lúc. Cơ chế đánh giá: Excel tạo hai mảng boolean — mảng đầu tiên từ so sánh phòng ban, mảng thứ hai từ so sánh lương, sau đó nhân hai mảng này. Kết quả là mảng mới chỉ có TRUE ở vị trí cả hai điều kiện đều khớp.

Ví dụ 4: Lọc nhân viên gia nhập công ty trong năm 2025. Giả sử cột E là ngày gia nhập. Công thức: =FILTER(A2:E100, YEAR(E2:E100)=2025, "Không có nhân viên mới"). Hàm YEAR trích xuất năm từ mỗi ngày trong vùng E2:E100, sau đó FILTER so sánh với 2025. Điều này hữu ích khi cần báo cáo nhân sự mới mỗi năm, tự động cập nhật khi có nhân viên mới gia nhập mà không cần chỉnh sửa bộ lọc thủ công.

Ví dụ nâng cao: Lọc nhân viên có tên chứa chữ "Thị". Công thức: =FILTER(A2:D100, ISNUMBER(SEARCH("Thị", B2:B100)), "Không có kết quả"). Hàm SEARCH tìm chuỗi "Thị" trong từng tên của cột B. Nếu tìm thấy, SEARCH trả về vị trí của chuỗi; nếu không tìm thấy, trả về lỗi #VALUE!. Hàm ISNUMBER chuyển đổi kết quả thành TRUE (nếu tìm thấy) hoặc FALSE (nếu không tìm thấy). Cơ chế này cho phép lọc theo chuỗi con, không cần so sánh đúng hoàn toàn — rất hữu ích khi làm việc với dữ liệu văn bản không đồng nhất.

Theo quan điểm của Trang Chia Sẻ Thông Tin Công Nghệ Smartphone, hàm FILTER đặc biệt mạnh mẽ khi kết hợp với các hàm date/time (TODAY, EOMONTH) để tạo các báo cáo tự động theo thời gian thực. Ví dụ, lọc các hợp đồng hết hạn trong 30 ngày tới giúp quản lý tài chính chủ động lên kế hoạch gia hạn mà không cần kiểm tra thủ công từng hợp đồng.

Câu hỏi thường gặp

Hàm FILTER có hoạt động trên Excel 2019 không? Không. Hàm FILTER chỉ có sẵn trên Excel 365, Excel 2021 và phiên bản web Office 365. Excel 2019 và các phiên bản cũ hơn không hỗ trợ dynamic array, do đó sẽ trả về lỗi #NAME?.

Lỗi #CALC! khi dùng FILTER là do gì? Lỗi #CALC! thường xuất hiện khi có tham chiếu vòng (circular reference) — công thức FILTER tham chiếu đến vùng kết quả của chính nó. Để khắc phục, đảm bảo vùng dữ liệu nguồn và vùng kết quả là hai vùng tách biệt hoàn toàn.

Tại sao hàm FILTER trả về #SPILL!? Lỗi #SPILL! xảy ra khi vùng kết quả dự kiến của FILTER bị chặn bởi dữ liệu khác trong các ô xung quanh. Excel cần không gian trống để hiển thị mảng động. Xóa hoặc di chuyển dữ liệu đang chiếm chỗ trong vùng kết quả để khắc phục.

Có thể lọc theo nhiều điều kiện với FILTER không? Có. Dùng toán tử * cho logic AND (cả hai điều kiện phải đúng) hoặc + cho logic OR (bất kỳ điều kiện nào đúng cũng được). Ví dụ: (C2:C100="Kế toán")*(D2:D100>15000000) lọc nhân viên phòng Kế toán có lương trên 15 triệu.

FILTER có thể lọc dữ liệu từ nhiều sheet khác nhau không? Có thể, nhưng cần dùng hàm như INDIRECT hoặc CHOOSE để tham chiếu đến vùng dữ liệu từ sheet khác. Tuy nhiên, việc này làm phức tạp công thức và giảm hiệu suất. Thường tốt nhất gộp dữ liệu vào một sheet trước khi dùng FILTER.