FILTER, UNIQUE và SORT là các hàm mảng động: một công thức đặt ở một ô sẽ trả về cả một danh sách và tự tràn xuống các ô bên dưới, tự cập nhật khi dữ liệu gốc thay đổi. FILTER lọc các dòng thoả điều kiện, UNIQUE lấy giá trị không trùng, SORT sắp xếp. Ba hàm này có trong Microsoft 365, Excel 2021 trở về sau và Excel trên web; Excel 2019 trở về trước không có.
So với bấm bộ lọc bằng tay, công thức mảng động không làm thay đổi bảng gốc, nhiều người xem cùng lúc nhiều kết quả lọc khác nhau, và danh sách luôn mới. Bài viết giải thích cú pháp, đi qua ví dụ giả định lọc đơn chưa giao theo kho, cách ghép ba hàm, cách làm danh sách thả xuống tự cập nhật, và các bẫy về phiên bản, lỗi tràn, dữ liệu khi người khác mở tệp.
Mảng động là gì và vì sao nó thay đổi cách làm bảng
Trước đây, mỗi ô Excel chứa một công thức trả về một giá trị. Muốn có danh sách 50 đơn chưa giao, bạn phải lọc tay rồi sao chép, hoặc viết công thức mảng phức tạp kéo xuống 50 dòng. Mảng động thay đổi điều này: công thức ở ô H2 trả về 50 giá trị, Excel tự đổ vào H2 đến H51, gọi là vùng tràn. Ngày mai còn 32 đơn, vùng tràn tự co lại 32 dòng.
Vùng tràn có một khung xanh mảnh khi bạn chọn ô bất kỳ trong nó. Chỉ ô đầu tiên chứa công thức; các ô còn lại hiện giá trị nhưng không sửa được. Để tham chiếu cả vùng tràn trong công thức khác, bạn viết địa chỉ ô đầu kèm dấu thăng, ví dụ H2#. Tham chiếu này tự co giãn theo vùng tràn, rất tiện cho danh sách thả xuống hay để đếm số dòng kết quả bằng ROWS(H2#).
Đổi lại, mảng động cần chỗ trống. Nếu vùng tràn đụng một ô có dữ liệu, công thức báo #SPILL!. Mảng động cũng không tràn được bên trong Table. Vì vậy cách bố trí hợp lý là để dữ liệu gốc ở một trang tính, dạng Table, còn các danh sách lọc bằng công thức đặt ở trang tính khác, mỗi danh sách có khoảng trống phía dưới.
Ví dụ giả định: lọc đơn chưa giao theo từng kho
Một công ty thu mua cà phê ở Lâm Đồng có Table tên tblDon gồm các cột MaDon, NgayDat, Kho, KhachHang, SoKg, TrangThai. Mỗi sáng, thủ kho từng điểm cần xem danh sách đơn chưa giao của kho mình, xếp theo ngày đặt cũ nhất lên trước.
- Ở trang tính LocDon, ô B1 nhập tên kho cần xem. Ô này là chỗ duy nhất người dùng thay đổi.
- Tại ô A3 gõ: =FILTER(tblDon,(tblDon[Kho]=B1)*(tblDon[TrangThai]<>"Đã giao"),"Không có đơn chờ"). Hai điều kiện nhân với nhau nghĩa là "và"; đối số cuối hiện nội dung thay thế khi không có dòng nào.
- Bọc thêm SORT để xếp theo ngày: =SORT(FILTER(...),2,1). Số 2 là cột thứ hai trong kết quả (NgayDat), số 1 là tăng dần.
- Dòng 2 gõ tiêu đề cột bằng tay hoặc tham chiếu tiêu đề của Table, vì FILTER chỉ trả phần dữ liệu.
- Kiểm: lọc tay Table gốc theo cùng kho và trạng thái, so số dòng với =ROWS(A3#). Hai con số phải bằng nhau.
Muốn lọc theo "hoặc", ví dụ đơn của kho B1 hoặc kho B2, dùng dấu cộng giữa hai điều kiện thay cho dấu nhân. Khi trộn cả "và" lẫn "hoặc", đặt ngoặc cẩn thận: ((Kho=B1)+(Kho=B2))*(TrangThai<>"Đã giao"). Thiếu ngoặc là sai logic mà không báo lỗi, nên đây là chỗ đáng để AI viết và bạn kiểm bằng số dòng.
UNIQUE, SORT, SORTBY và cách ghép chúng
UNIQUE lấy giá trị không trùng. =UNIQUE(tblDon[KhachHang]) trả về danh sách khách, mỗi khách một lần. Đối số thứ ba đặt TRUE thì chỉ lấy những giá trị xuất hiện đúng một lần, hữu ích để tìm khách chỉ mua một lần. UNIQUE coi "Anh Tuấn" và "anh Tuấn" là một, nhưng "Anh Tuấn " có dấu cách cuối là khác.
SORT sắp theo cột trong kết quả. SORT(mảng, cột thứ mấy, 1 hoặc -1). Với danh sách một cột, chỉ cần SORT(UNIQUE(...)) là có danh sách khách xếp theo vần. Lưu ý thứ tự chữ có dấu tiếng Việt theo cách Excel sắp có thể không đúng thứ tự từ điển tiếng Việt ở một số chữ cái.
SORTBY sắp theo một cột không hiển thị. Khi bạn muốn danh sách khách xếp theo tổng số kg giảm dần nhưng không hiện cột số kg, SORTBY nhận mảng cần sắp và một mảng khác làm tiêu chí. Thường dùng kèm một công thức tính tổng cho từng khách.
Ghép ba hàm. =SORT(UNIQUE(FILTER(tblDon[KhachHang],tblDon[Kho]=B1))) cho danh sách khách không trùng của một kho, xếp theo vần. Đọc từ trong ra ngoài: lọc theo kho, bỏ trùng, sắp xếp. Khi công thức ghép dài hơn, LET giúp đặt tên từng bước cho dễ đọc.
Đếm kết quả. =ROWS(UNIQUE(...)) đếm số giá trị không trùng, cách gọn để trả lời "tháng này có bao nhiêu khách khác nhau". Nếu FILTER bên trong không có kết quả, kết quả đếm có thể gây hiểu lầm, nên đặt nội dung thay thế rõ ràng.
Danh sách thả xuống tự cập nhật
Một ứng dụng rất thiết thực của mảng động là danh sách thả xuống trong ô nhập liệu. Trước đây, danh sách chọn tên kho hay tên khách trong Data Validation thường trỏ vào một vùng cố định, thêm kho mới là phải sửa vùng. Với mảng động, bạn đặt =SORT(UNIQUE(tblDon[Kho])) ở một ô trong trang tính phụ, ví dụ ô Z2, rồi trong Data Validation chọn kiểu List và nguồn là =$Z$2#. Thêm kho mới vào dữ liệu, danh sách tự có thêm dòng.
Cách này cũng giúp giữ dữ liệu sạch từ khâu nhập: người dùng chọn tên có sẵn thay vì gõ tay, tránh được những biến thể như "Kho Đà Lạt", "kho Đà Lạt", "Kho DL" mà sau này phải ngồi chuẩn hoá. Lưu ý nguồn danh sách lấy từ chính dữ liệu đã nhập, nên nếu trong dữ liệu đã có tên sai, tên sai cũng xuất hiện trong danh sách chọn. Với các danh mục chuẩn như kho, phòng ban, nhóm hàng, tốt hơn là giữ một bảng danh mục riêng làm nguồn.
Dùng LET để công thức ghép dài vẫn đọc được
Khi bạn ghép SORT, UNIQUE, FILTER với vài điều kiện, công thức nhanh chóng dài hai ba dòng, lặp lại cùng một biểu thức nhiều lần. Hàm LET, có cùng thời với mảng động trong Microsoft 365 và Excel 2021, cho phép đặt tên cho từng bước tính rồi dùng lại tên đó. Cú pháp là LET(tên 1, giá trị 1, tên 2, giá trị 2, ..., biểu thức cuối cùng).
Ví dụ giả định cho bài toán khách không trùng của một kho: =LET(kho, B1, ds, FILTER(tblDon[KhachHang], tblDon[Kho]=kho, ""), SORT(UNIQUE(ds))). Đọc lên gần như văn xuôi: lấy kho ở ô B1, lọc danh sách khách của kho đó, rồi bỏ trùng và sắp xếp. Nếu sau này cần thêm điều kiện trạng thái, bạn chỉ sửa đúng một chỗ ở bước ds.
LET còn giúp tính nhanh hơn khi một biểu thức nặng được dùng nhiều lần, vì Excel chỉ tính nó một lần. Khi nhờ AI viết công thức mảng động dài, bạn có thể yêu cầu thẳng: "viết lại bằng LET, đặt tên từng bước bằng chữ không dấu dễ hiểu". Kết quả dễ kiểm hơn nhiều, vì bạn có thể tạm thay biểu thức cuối bằng một tên trung gian như ds để xem riêng bước đó trả về gì.
Lọc bằng tay, Advanced Filter và hàm FILTER
Không phải lúc nào FILTER cũng là lựa chọn tốt nhất. Bảng dưới so sánh ba cách lọc phổ biến để bạn chọn theo tình huống.
| Tiêu chí | Bộ lọc bấm tay | Advanced Filter | Hàm FILTER |
|---|---|---|---|
| Phiên bản | Mọi phiên bản | Mọi phiên bản | Microsoft 365, Excel 2021 trở về sau |
| Tự cập nhật khi dữ liệu đổi | Phải bấm lại | Phải chạy lại | Tự động |
| Ảnh hưởng bảng gốc | Ẩn dòng trên bảng gốc | Có thể chép kết quả ra chỗ khác | Không ảnh hưởng |
| Nhiều người xem nhiều kiểu lọc | Khó, mỗi người lọc một kiểu đè nhau | Được nếu chép ra vùng riêng | Dễ, mỗi danh sách một công thức |
| Sửa trực tiếp trên kết quả | Được, sửa thẳng vào bảng gốc | Không, kết quả là bản chép | Không, kết quả là công thức |
| Hợp với | Tra nhanh, sửa dữ liệu | Lọc phức tạp trên bản cũ | Báo cáo, danh sách xem hằng ngày |
Hàng thứ năm là điểm hay gây bất ngờ: kết quả FILTER chỉ để xem. Nếu thủ kho muốn đánh dấu "Đã giao" ngay trên danh sách lọc, họ không làm được; việc cập nhật phải diễn ra ở bảng gốc. Khi thiết kế, nói rõ với người dùng chỗ nào là chỗ nhập, chỗ nào là chỗ xem.
Bẫy thường gặp và cách nhờ AI xử lý
Mảng động gọn nhưng có vài đặc điểm dễ gây lỗi, nhất là khi tệp đi qua nhiều người. Những điều cần nắm:
- #SPILL!: vùng tràn có ô không trống, có ô gộp, hoặc công thức nằm trong Table. Bấm vào ô lỗi để Excel chỉ ra ô đang chặn.
- #CALC!: FILTER không tìm được dòng nào mà bạn chưa ghi đối số nội dung thay thế. Luôn điền đối số thứ ba.
- Người khác mở bằng bản cũ: các ô dùng hàm mảng động thường báo #NAME? hoặc không còn tự cập nhật. Hỏi phiên bản của mọi người dùng tệp trước khi dựng.
- Tham chiếu vào vùng tràn bằng địa chỉ cố định như H2:H51 sẽ sai khi danh sách dài ra. Dùng H2# thay thế.
- Tốc độ: hàng chục công thức FILTER trên bảng hàng chục nghìn dòng làm tệp chậm. Giới hạn số công thức, hoặc gom dữ liệu bằng Power Query nếu chỉ cần cập nhật theo ngày.
- Khi nhờ AI viết: nói rõ tên Table, tên cột, phiên bản Excel, nói "và" hay "hoặc" giữa các điều kiện, và yêu cầu có đối số nội dung khi không có kết quả. Đưa tên khách giả định, không dán danh sách khách thật.
Sau khi có công thức, kiểm bằng số dòng: so ROWS của vùng tràn với số dòng bạn đếm được khi lọc tay cùng điều kiện. Logic "và", "hoặc" sai thường không báo lỗi gì, chỉ lộ ra qua số dòng.
Câu hỏi thường gặp
Excel 2019 có hàm FILTER không?
Không. FILTER, UNIQUE, SORT, SORTBY, SEQUENCE có trong Microsoft 365, Excel 2021 trở về sau và Excel trên web. Excel 2019 trở về trước không có. Trên bản cũ, bạn có thể dùng Advanced Filter, PivotTable hoặc công thức INDEX kết hợp SMALL và IF, phức tạp hơn nhiều.
Có sửa được dữ liệu trong vùng tràn của FILTER không?
Không. Các ô trong vùng tràn hiện kết quả của công thức ở ô đầu, gõ đè vào bất kỳ ô nào sẽ làm công thức báo #SPILL!. Muốn sửa, sửa ở bảng gốc. Nếu cần một bản kết quả cố định để chỉnh tiếp, sao chép vùng tràn rồi dán dạng giá trị ở chỗ khác.
FILTER có lọc theo đoạn chữ chứa trong ô được không?
Được, kết hợp ISNUMBER và SEARCH làm điều kiện: SEARCH tìm vị trí đoạn chữ, không thấy thì báo lỗi, ISNUMBER đổi thành đúng sai. SEARCH không phân biệt hoa thường; FIND thì có. Cách này hay dùng để lọc đơn theo tên sản phẩm có chứa một từ khoá.
UNIQUE có loại được trùng do khác dấu cách hay viết hoa không?
UNIQUE coi chữ hoa và chữ thường là giống nhau, nhưng dấu cách thừa, khác bảng mã gõ dấu hay viết tắt khác nhau thì vẫn coi là khác. Nếu danh sách UNIQUE ra nhiều biến thể của cùng một tên, vấn đề nằm ở dữ liệu gốc và cần làm sạch trước.
Mảng động có dùng được trong Google Sheets không?
Google Sheets có FILTER, UNIQUE, SORT từ lâu và cũng tràn kết quả xuống các ô dưới, nhưng cú pháp và hành vi một số chỗ khác Excel, ví dụ cách viết nhiều điều kiện trong FILTER. Khi chuyển công thức giữa hai nền tảng, kiểm lại trên vài dòng.