Dynamic Array Excel: UNIQUE, SORT, FILTER, SEQUENCE — Công Thức Tràn

1. Dynamic Array Là Gì? Spill Range Hoạt Động Thế Nào? #

Dynamic Arrays (Excel 365/2021) thay đổi cách viết công thức. Trước đây, 1 công thức = 1 kết quả. Giờ, 1 công thức có thể trả về MẢNG kết quả tự động tràn (spill) sang các cell lân cận. Không cần Ctrl+Shift+Enter, không cần copy formula xuống dưới.

6 hàm dynamic array mới: UNIQUE, SORT, SORTBY, FILTER, SEQUENCE, RANDARRAY. Kết hợp chúng với nhau tạo ra solutions cực mạnh mà trước đây cần VBA.

2. UNIQUE — Lọc Giá Trị Không Trùng #

// Lọc danh sách khách hàng unique
=UNIQUE(A2:A100)

// Unique theo nhiều cột (tổ hợp unique)
=UNIQUE(A2:C100)  // Trả về rows unique qua cả 3 cột

// Tìm giá trị chỉ xuất hiện 1 lần (exactly_once)
=UNIQUE(A2:A100, FALSE, TRUE)  // Tham số 3 = exactly once

3. SORT Và SORTBY — Sắp Xếp Mà Không Thay Đổi Data Gốc #

// Sắp xếp doanh thu giảm dần
=SORT(A2:D100, 4, -1)  // Cột 4, giảm dần (-1)

// SORTBY: sắp theo cột khác (linh hoạt hơn)
=SORTBY(A2:C100, D2:D100, -1)  // Sort A:C theo D giảm dần

// Sắp 2 cấp: theo Region ASC, Revenue DESC
=SORTBY(A2:D100, B2:B100, 1, D2:D100, -1)

4. FILTER — Lọc Data Với Điều Kiện Phức Tạp #

FILTER thay thế Advanced Filter hoàn toàn. Kết quả là dynamic array, tự cập nhật khi data thay đổi. Hỗ trợ AND (*) và OR (+) logic.

// Lọc đơn hàng > 1M
=FILTER(A2:E100, E2:E100 > 1000000)

// Lọc AND: Sales + > 1M
=FILTER(A2:E100, (B2:B100="Sales") * (E2:E100>1000000))

// Lọc OR: Sales hoặc Marketing
=FILTER(A2:E100, (B2:B100="Sales") + (B2:B100="Marketing"))

// Giá trị mặc định khi không có kết quả
=FILTER(A2:E100, E2:E100>10000000, "Không có dữ liệu")

5. SEQUENCE — Tạo Chuỗi Số Tự Động #

=SEQUENCE(10)          // 1,2,3...10 (cột dọc)
=SEQUENCE(3, 4)        // 3×4 matrix: {{1,2,3,4},{5,6,7,8},{9,10,11,12}}
=SEQUENCE(12, 1, 1, 1) // 1-12 (tháng)

// Tạo danh sách ngày trong tháng
=SEQUENCE(DAY(EOMONTH(TODAY(),0)), 1, DATE(2024,3,1))  // 1/3 → 31/3

// Tạo giờ từ 8:00 đến 17:00
=SEQUENCE(10, 1, TIME(8,0,0), TIME(1,0,0))

6. Kết Hợp Các Hàm: Power Combos #

// Top 5 khách hàng doanh thu cao nhất (sorted, filtered)
=SORT(FILTER(A2:D100, D2:D100>1000000), 4, -1)

// Danh sách unique + sorted
=SORT(UNIQUE(A2:A100))

// Dropdown từ unique values (Data Validation source)
=SORT(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

7. Spill Range Reference (#) #

Dùng # để reference toàn bộ spill range. Nếu UNIQUE ở G2 trả về 10 giá trị, G2# = G2:G11. Rất hữu ích cho COUNTIF, SUMIF tham chiếu kết quả dynamic: =COUNTIF(A:A, G2#) đếm cho từng giá trị unique.

8. Lưu Ý Tương Thích #

Dynamic Arrays CHỈ có trong Excel 365 và Excel 2021+. Gửi file cho người dùng bản cũ → formula lỗi #NAME?. Giải pháp: convert result thành giá trị tĩnh (Paste Values) trước khi share. Google Sheets hỗ trợ tương tự nhưng không cần #.

9. Kết Luận #

Dynamic Arrays biến Excel thành công cụ xử lý dữ liệu tier khác. FILTER + SORT + UNIQUE combo thay thế hoàn toàn Advanced Filter và helper columns. Bắt đầu với FILTER (dùng nhiều nhất), sau đó kết hợp UNIQUE và SORT để tạo solutions tự động cập nhật.