Hàm FILTER, UNIQUE, SORT trong Excel và Google Sheets
Hướng dẫn hàm mảng động FILTER, UNIQUE, SORT, SORTBY trên Excel 365 và Google Sheets: lọc theo điều kiện, lấy danh sách không trùng, sắp xếp và sửa lỗi #SPILL!.
Từ Excel 365 và Excel 2021, chỉ một công thức gõ ở một ô có thể trả về cả bảng kết quả, tự “tràn” (spill) xuống các ô bên dưới. Bốn hàm đáng học nhất là FILTER (lọc theo điều kiện), UNIQUE (lấy danh sách không trùng), SORT và SORTBY (sắp xếp). Khác với bộ lọc thủ công, kết quả tự cập nhật khi dữ liệu gốc thay đổi. Google Sheets cũng có các hàm này, chỉ khác vài chi tiết.
#NAME?). Ví dụ dùng dấu phẩy; máy đặt vùng Việt Nam có thể cần dấu chấm phẩy.1. FILTER — lọc dữ liệu bằng công thức
=FILTER(vùng_dữ_liệu, điều_kiện, [giá_trị_nếu_rỗng])| Đối số | Ý nghĩa |
|---|---|
vùng_dữ_liệu | Bảng cần lấy ra, ví dụ A2:E500 |
điều_kiện | Phép so sánh trên một cột, cùng số dòng, ví dụ C2:C500="Hà Nội" |
giá_trị_nếu_rỗng | Nội dung hiện khi không có dòng nào thoả (nếu bỏ trống sẽ báo #CALC!) |
Ví dụ bảng bán hàng A:E (Ngày, Nhân viên, Chi nhánh, Sản phẩm, Thành tiền):
=FILTER(A2:E500,C2:C500="Hà Nội","Không có dữ liệu")
=FILTER(A2:E500,(C2:C500="Hà Nội")*(E2:E500>=5000000))
=FILTER(A2:E500,(C2:C500="Hà Nội")+(C2:C500="Đà Nẵng"))Nhân * nghĩa là “và”, cộng + nghĩa là “hoặc”. Thay "Hà Nội" bằng ô chứa điều kiện (ví dụ H1) để làm báo cáo đổi nhanh.
2. UNIQUE — danh sách không trùng
=UNIQUE(vùng, [theo_cột], [chỉ_xuất_hiện_một_lần])=UNIQUE(B2:B500): danh sách nhân viên, mỗi người một dòng.=UNIQUE(B2:B500,FALSE,TRUE): chỉ những tên xuất hiện đúng một lần.=COUNTA(UNIQUE(B2:B500)): đếm số khách hàng khác nhau.
Kết hợp với SUMIFS để làm bảng tổng hợp nhanh: cột G là =UNIQUE(B2:B500), cột H là =SUMIFS(E2:E500,B2:B500,G2#) — ký hiệu G2# nghĩa là “toàn bộ vùng tràn bắt đầu từ G2”.
3. SORT và SORTBY — sắp xếp bằng công thức
=SORT(vùng, [cột_sắp_xếp], [thứ_tự], [theo_cột])
=SORTBY(vùng, vùng_tiêu_chí_1, [thứ_tự_1], ...)thứ_tự: 1 tăng dần, -1 giảm dần.
=SORT(A2:E500,5,-1): sắp theo cột thứ 5 (Thành tiền) giảm dần.=SORTBY(A2:C60,C2:C60,-1,A2:A60,1): bảng điểm sắp theo điểm giảm dần, cùng điểm thì theo tên A→Z.=SORT(UNIQUE(B2:B500)): danh sách nhân viên không trùng, theo ABC.=TAKE(SORT(A2:E500,5,-1),5): top 5 đơn hàng lớn nhất (TAKE có trên Excel 365).
Lưu ý sắp xếp tên tiếng Việt theo “tên” chứ không theo “họ” cần tách tên trước — xem sắp xếp tên theo ABC.
4. Lỗi #SPILL! và cách sửa
Công thức mảng động cần một vùng trống đủ rộng để tràn kết quả. Khi bị chặn, Excel báo #SPILL!; bấm vào ô lỗi, Excel sẽ khoanh viền nét đứt vùng cần dùng.
| Nguyên nhân | Cách sửa |
|---|---|
| Có dữ liệu, kể cả dấu cách, trong vùng tràn | Xoá nội dung các ô đó |
| Vùng tràn có ô đã gộp (Merge) | Bỏ gộp ô |
| Công thức nằm trong Excel Table (Ctrl+T) | Đặt công thức ra ngoài Table |
| Kết quả vượt mép trang tính, hoặc tham chiếu cả cột A:A làm mảng quá lớn | Giới hạn vùng, ví dụ A2:A5000 |
5. Khác biệt trên Google Sheets
- FILTER: mỗi điều kiện là một đối số riêng (tự hiểu là “và”), không có đối số “nếu rỗng”:
=FILTER(A2:E500,C2:C500="Hà Nội",E2:E500>=5000000). BọcIFERRORđể thay thông báo khi không có kết quả. - SORT: cú pháp
=SORT(vùng, cột, TRUE/FALSE, ...)— TRUE là tăng dần. Sheets không có SORTBY, nhưng đối số cột của SORT có thể là một vùng bên ngoài, ví dụ=SORT(A2:A60,C2:C60,FALSE). - UNIQUE: dùng giống Excel.
- Khi bị chặn, Sheets báo
#REF!kèm thông báo kết quả mảng không mở rộng được vì sẽ ghi đè dữ liệu — xử lý như #SPILL!.
QUERY còn mạnh hơn — xem các hàm đặc trưng của Google Sheets.