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

Trong làm việc với bảng tính, việc trích xuất dữ liệu theo điều kiện là nhu cầu thường xuyên. Thay vì phải sử dụng tính năng Filter thủ công hay Advanced Filter phức tạp, hàm FILTER trong Excel (thuộc nhóm hàm mảng động) đã thay đổi hoàn toàn cách chúng ta xử lý dữ liệu. Bài viết này, Starttrain sẽ giúp bạn làm chủ hàm FILTER từ A-Z.

Hàm FILTER trong Excel là gì?

Hàm FILTER trong Excel là một hàm mảng động (Dynamic Array) mạnh mẽ, được dùng để trích xuất các phần tử từ một vùng dữ liệu dựa trên các điều kiện xác định. Nguyên lý hoạt động của hàm này là lấy tập dữ liệu gốc mà bạn cung cấp, sau đó lọc và trả về tất cả các kết quả thỏa mãn tiêu chí trong công thức một cách tự động.

Lợi ích khi sử dụng hàm FILTER trong Excel:

Tiết kiệm thời gian: Giảm bớt các thao tác lọc và tìm kiếm dữ liệu thủ công rườm rà.
Xử lý đa điều kiện: Có khả năng lọc dữ liệu cực nhanh ngay cả khi kết hợp nhiều điều kiện phức tạp cùng lúc.
Tính cập nhật cao: Kết quả trả về sẽ tự động hiển thị đầy đủ cả dữ liệu cũ và mới khi vùng dữ liệu nguồn thay đổi (nếu vẫn thỏa mãn điều kiện).

Lưu ý về phiên bản: Hàm FILTER hiện chỉ hỗ trợ trên các phiên bản Microsoft Excel 365 và Excel 2019 trở lên (bao gồm cả Excel 2021). Các phiên bản Excel cũ hơn sẽ không hiển thị hàm này trong bảng tính.

Cú pháp hàm FILTER trong Excel

Cú pháp cơ bản của hàm FILTER như sau:

=FILTER(array, include, [if_empty])

Trong đó:

array (Bắt buộc): Đây là vùng dữ liệu hoặc mảng nguồn mà bạn muốn thực hiện lọc. Vùng này có thể bao gồm một cột, nhiều cột hoặc toàn bộ bảng dữ liệu.
include (Bắt buộc): Đây là tiêu chí lọc dữ liệu.
- Nó phải là một mảng logic (đúng/sai) có kích thước (số dòng hoặc số cột) tương ứng với vùng array.
- Ví dụ: Nếu array là A2:C10, thì include có thể là B2:B10=”X”.
- Bạn có thể kết hợp nhiều điều kiện tại đây bằng toán tử * (tương đương hàm AND) hoặc + (tương đương hàm OR).
if_empty (Tùy chọn): Đây là giá trị mà hàm sẽ trả về nếu không có dòng nào trong vùng array thỏa mãn điều kiện include.
- Nếu bỏ qua đối số này và không có kết quả nào được tìm thấy, Excel sẽ trả về lỗi #CALC!.
- Thông thường, người dùng hay để là “Không tìm thấy”, “N/A” hoặc để trống “”.

Cơ chế hoạt động của mảng động:

Hàm FILTER trong Excel là một hàm “Spill” (tràn). Điều này có nghĩa là bạn chỉ cần nhập công thức vào một ô duy nhất, và kết quả sẽ tự động tràn xuống các ô bên dưới và sang các ô bên cạnh để hiển thị toàn bộ danh sách kết quả lọc được.

Các ví dụ sử dụng hàm FILTER trong Excel từ cơ bản đến nâng cao

Để minh họa, chúng ta sử dụng một bảng dữ liệu bán hàng tại vùng A2:D10 bao gồm các cột: Ngày (A), Nhân viên (B), Khu vực (C), Doanh số (D).

Lọc theo một điều kiện đơn giản

Tình huống: Quản lý muốn xem tất cả các đơn hàng được thực hiện tại khu vực “Miền Bắc” để đánh giá hiệu quả kinh doanh của vùng này.

Công thức: =FILTER(A2:D10, C2:C10="Miền Bắc", "Không có dữ liệu")

Giải thích chi tiết:

A2:D10: Là toàn bộ bảng dữ liệu bạn muốn trích xuất thông tin.
C2:C10=”Miền Bắc”: Excel sẽ quét qua cột Khu vực. Mỗi ô nào có giá trị là “Miền Bắc” sẽ được trả về giá trị TRUE, ngược lại là FALSE. Hàm FILTER chỉ giữ lại những dòng ứng với giá trị TRUE.
“Không có dữ liệu”: Nếu trong bảng không có dòng nào thuộc “Miền Bắc”, Excel sẽ hiện dòng chữ này thay vì báo lỗi.

Lọc với nhiều điều kiện (Kết hợp điều kiện AND)

Tình huống: Bạn cần tìm các đơn hàng mà nhân viên tên “Nam” thực hiện riêng tại khu vực “Miền Bắc” để tính thưởng doanh số cụ thể.

Công thức: =FILTER(A2:D10, (B2:B10="Nam") * (C2:C10="Miền Bắc"), "Không tìm thấy")

Giải thích chi tiết:

Trong Excel, toán tử nhân * tương đương với điều kiện VÀ (AND).
Biểu thức (B2:B10=”Nam”) trả về một mảng 0 và 1. Tương tự với (C2:C10=”Miền Bắc”).
Khi nhân hai mảng này với nhau, chỉ có những dòng thỏa mãn cả hai điều kiện (1 * 1 = 1) mới được hàm FILTER trích xuất.

Lọc với nhiều điều kiện (Kết hợp điều kiện OR)

Tình huống: Công ty đang chạy chiến dịch tổng kết cho cả hai khu vực là “Miền Bắc” và “Miền Trung”. Bạn cần lọc ra danh sách đơn hàng thuộc một trong hai khu vực này.

Công thức: =FILTER(A2:D10, (C2:C10="Miền Bắc") + (C2:C10="Miền Trung"), "Không tìm thấy")

Giải thích chi tiết:

Toán tử cộng + tương đương với điều kiện HOẶC (OR).
Nếu một dòng thỏa mãn “Miền Bắc” HOẶC thỏa mãn “Miền Trung”, kết quả cộng lại sẽ lớn hơn 0 (TRUE).
Cơ chế này giúp bạn mở rộng phạm vi lọc cho nhiều giá trị khác nhau trong cùng một cột hoặc trên các cột khác nhau.

Kết hợp cả điều kiện AND và OR

Tình huống: Bạn muốn lọc ra danh sách các đơn hàng có doanh số lớn hơn 50 triệu đồng tại một trong hai khu vực là “Miền Bắc” hoặc “Miền Nam”.

Công thức: =FILTER(A2:D10, ((C2:C10="Miền Bắc") + (C2:C10="Miền Nam")) * (D2:D10 > 50000000), "Không tìm thấy")

Giải thích chi tiết:

Ở đây chúng ta sử dụng dấu ngoặc đơn để nhóm các điều kiện lại với nhau.
((C2:C10=”Miền Bắc”) + (C2:C10=”Miền Nam”)): Phần này giải quyết điều kiện OR (Khu vực là Bắc hoặc Nam).
* (D2:D10 > 50000000): Phần này áp dụng điều kiện AND cho kết quả của nhóm trên với tiêu chí Doanh số > 50 triệu.
Việc nắm vững cách kết hợp này giúp bạn xử lý những yêu cầu báo cáo phức tạp mà không cần dùng đến các cột phụ.

Tìm hiểu thêm: Hàm LOOKUP trong Excel là gì? Cách sử dụng và ví dụ chi tiết

Các lỗi thường gặp khi dùng hàm FILTER trong Excel

Trong quá trình sử dụng hàm FILTER trong Excel, bạn có thể gặp phải một số thông báo lỗi phổ biến. Hiểu rõ nguyên nhân sẽ giúp bạn khắc phục chúng một cách nhanh chóng.

Lỗi #CALC!

Đây là lỗi phổ biến nhất khi dùng hàm FILTER trong Excel. Lỗi này xuất hiện khi điều kiện bạn đặt ra trong phần include không khớp với bất kỳ dòng dữ liệu nào trong vùng array.

Nguyên nhân: Do dữ liệu thực tế không có giá trị bạn cần tìm hoặc do sai sót trong việc nhập điều kiện (ví dụ: sai chính tả, thừa dấu cách).
Cách khắc phục: Bạn hãy sử dụng đối số thứ ba [if_empty]. Ví dụ: =FILTER(A2:D10, C2:C10=”Miền Tây”, “Không có dữ liệu”). Thay vì báo lỗi, Excel sẽ hiển thị dòng chữ “Không có dữ liệu”.

Lỗi #SPILL!

Vì FILTER là hàm mảng động, nó cần một khoảng trống đủ lớn để “tràn” kết quả ra các ô xung quanh. Nếu vùng không gian này bị cản trở, lỗi #SPILL! sẽ xuất hiện.

Nguyên nhân: Có dữ liệu khác đang nằm trong vùng mà hàm định trả kết quả về, hoặc trong vùng đó có các ô đang bị gộp (Merge Cells).
Cách khắc phục: Hãy kiểm tra các ô bên dưới và bên phải ô nhập công thức. Bạn cần xóa bỏ các dữ liệu đang chặn đường hoặc hủy bỏ chế độ Merge Cells để tạo khoảng trống cho mảng dữ liệu hiển thị.

Lỗi #VALUE!

Lỗi này xảy ra khi các tham số đầu vào không có sự tương thích về mặt kích thước hoặc cấu trúc mảng.

Nguyên nhân: Thông thường là do vùng dữ liệu array và vùng điều kiện include có số lượng dòng không bằng nhau. Ví dụ: Bạn chọn array là A2:D10 (9 dòng) nhưng điều kiện lại chọn C2:C11 (10 dòng).
Cách khắc phục: Kiểm tra lại công thức và đảm bảo rằng độ dài của vùng điều kiện phải khớp hoàn toàn với số lượng dòng của vùng dữ liệu cần lọc.

Tổng hợp các lỗi trong Excel thường gặp và cách khắc phục

Kết hợp FILTER với các hàm khác

Sức mạnh thực sự của hàm FILTER trong Excel nằm ở khả năng kết hợp linh hoạt với các hàm mảng động khác. Dưới đây là những tổ hợp hàm phổ biến giúp bạn tạo ra những bảng dữ liệu thông minh.

Lọc và tự động sắp xếp (FILTER + SORT)

Thông thường, dữ liệu sau khi lọc có thể nằm lộn xộn. Để kết quả trả về chuyên nghiệp hơn, bạn nên kết hợp với hàm SORT.

Công thức: =SORT(FILTER(A2:D10, C2:C10="Miền Bắc"), 4, -1)

Giải thích:

Hàm FILTER sẽ lấy ra các dòng thuộc Miền Bắc trước.
Sau đó, hàm SORT bao bên ngoài sẽ sắp xếp kết quả đó dựa trên cột thứ 4 (Doanh số) theo thứ tự giảm dần (-1).

Lọc danh sách không trùng lặp (FILTER + UNIQUE)

Khi bạn muốn lọc ra danh sách các đối tượng duy nhất thỏa mãn điều kiện, tổ hợp này là lựa chọn số 1.

Ví dụ: Lấy danh sách tên các nhân viên có doanh số trên 100 triệu (không lặp tên).

Công thức: =UNIQUE(FILTER(B2:B10, D2:D10 > 100000000))

Giải thích:

FILTER trích xuất danh sách tên nhân viên (Cột B) thỏa mãn điều kiện doanh số.
UNIQUE sẽ loại bỏ các tên bị lặp lại, chỉ trả về mỗi cái tên một lần duy nhất.

Lọc theo từ khóa chứa một phần văn bản (FILTER + ISNUMBER + SEARCH)

Đây là kỹ thuật nâng cao để tìm kiếm dữ liệu kiểu “Search box”. Bạn có thể lọc dữ liệu chỉ bằng cách nhập một vài chữ cái.

Ví dụ: Lọc tất cả nhân viên mà trong tên có chứa chữ “An” (như An, Bình An, Thùy An). Công thức: =FILTER(A2:D10, ISNUMBER(SEARCH(“An”, B2:B10)), “Không tìm thấy”)

Giải thích:

SEARCH(“An”, B2:B10) tìm vị trí của chữ “An” trong từng ô của cột tên. Nếu thấy, nó trả về số; nếu không, trả về lỗi.
ISNUMBER chuyển kết quả thành TRUE (nếu là số) hoặc FALSE (nếu lỗi). Đây chính là mảng điều kiện cho hàm FILTER.

Chỉ hiển thị các cột mong muốn (FILTER + CHOOSECOLS)

Đôi khi bảng dữ liệu gốc có 20 cột, nhưng bạn chỉ muốn kết quả lọc trả về 2 cột là “Tên nhân viên” và “Doanh số”.

Công thức: =CHOOSECOLS(FILTER(A2:D10, C2:C10="Miền Nam"), 2, 4)

Giải thích:

Sau khi FILTER lọc ra dữ liệu Miền Nam, hàm CHOOSECOLS sẽ chỉ bốc ra cột thứ 2 và cột thứ 4 để hiển thị, giúp bảng tính gọn gàng hơn.

Xem thêm: Học Excel nâng cao cho người đi làm để làm chủ dữ liệu

Kết luận

Hàm FILTER trong Excel không chỉ là một công cụ lọc dữ liệu thông thường mà còn là giải pháp tối ưu để xây dựng các báo cáo động và linh hoạt. Việc nắm vững cách kết hợp các toán tử logic cùng các hàm mảng động khác sẽ giúp bạn nâng cao hiệu suất làm việc và xử lý dữ liệu chuyên nghiệp hơn rất nhiều.

Nâng tầm kỹ năng phân tích và bứt phá sự nghiệp cùng khóa học Excel for Business Analytics tại Starttrain.