Thay Thế Dynamic Array Cho Excel Cũ

FILTER, UNIQUE, SORT, XLOOKUP — các hàm Dynamic Array chỉ có trên Excel 365. Nhưng 70% doanh nghiệp vẫn dùng Excel 2016/2019. Bài viết hướng dẫn cách đạt kết quả tương tự bằng công thức cũ — giúp bạn không bị giới hạn bởi phiên bản Excel.

Vấn Đề Tương Thích Dynamic Array #

Dynamic Array Functions (DAF) là cuộc cách mạng của Excel 365: 1 công thức trả về nhiều giá trị, tự tràn (spill) vào vùng ô xung quanh. Nhưng:

• Excel 2019 trở xuống không hỗ trợ Dynamic Arrays

• File chứa FILTER, UNIQUE, SORT sẽ hiển thị lỗi #NAME? trên Excel cũ

• Nhiều doanh nghiệp, trường học vẫn dùng Excel 2016

Dưới đây là cách thay thế từng hàm DAF phổ biến bằng công thức cũ.

Thay Thế FILTER — Lọc Dữ Liệu #

Excel 365: FILTER #

=FILTER(A2:C100, B2:B100="IT")
// Lọc tất cả nhân viên phòng IT, trả về mảng

Excel cũ: INDEX + SMALL + IF (Ctrl+Shift+Enter) #

// Bước 1: Đếm kết quả
=COUNTIF(B2:B100, "IT")

// Bước 2: Công thức Array (nhập Ctrl+Shift+Enter):
// Ô E2 (cột Tên):
=IFERROR(
  INDEX($A$2:$A$100,
    SMALL(
      IF($B$2:$B$100="IT", ROW($A$2:$A$100)-ROW($A$2)+1),
      ROW()-ROW($E$2)+1
    )
  ),
  ""
)
// Copy xuống, mỗi ô trả về 1 kết quả
// Lặp cho cột B, C tương tự (thay $A$ thành $B$, $C$)

Thay Thế SORT — Sắp Xếp #

Excel 365: SORT #

=SORT(A2:C100, 3, -1)
// Sắp xếp theo cột 3 (doanh thu), giảm dần

Excel cũ: INDEX + MATCH + RANK hoặc SMALL/LARGE #

// Cách 1: Sắp xếp doanh thu giảm dần bằng LARGE:
// D2 (helper column — xếp hạng):
=RANK(C2, $C$2:$C$100)

// E2 (kết quả sắp xếp):
=INDEX($A$2:$A$100, MATCH(ROWS($E$1:E1), $D$2:$D$100, 0))
// Copy xuống → danh sách tên theo thứ tự doanh thu giảm dần

// Cách 2: Sắp xếp tên A-Z bằng COUNTIF:
// D2 (helper):
=COUNTIF($A$2:$A$100, "<"&A2) + 1
// E2:
=INDEX($A$2:$A$100, MATCH(ROWS($E$1:E1), $D$2:$D$100, 0))

Thay Thế UNIQUE — Giá Trị Duy Nhất #

Excel 365: UNIQUE #

=UNIQUE(A2:A100)
// Trả về danh sách giá trị không trùng lặp

Excel cũ: INDEX + MATCH + COUNTIF #

// Ctrl+Shift+Enter:
=IFERROR(
  INDEX($A$2:$A$100,
    MATCH(0,
      COUNTIF($D$1:D1, $A$2:$A$100),
      0
    )
  ),
  ""
)
// D1 = header, D2 nhập công thức, copy xuống
// Mỗi ô tìm giá trị chưa xuất hiện trong các ô phía trên

Thay Thế SORTBY — Sắp Xếp Theo Cột Khác #

Excel 365: SORTBY #

=SORTBY(A2:A100, C2:C100, -1)
// Sắp xếp tên theo doanh thu giảm dần

Excel cũ: Hàm phụ + INDEX/MATCH #

// Tương tự SORT, dùng helper column RANK trên cột sort-by:
// D2 = RANK(C2, $C$2:$C$100)  (cột doanh thu)
// E2 = INDEX($A$2:$A$100, MATCH(ROWS($E$1:E1), $D$2:$D$100, 0))

Thay Thế XLOOKUP #

// Excel 365:
=XLOOKUP(G2, A2:A100, C2:C100, "Not found")

// Excel cũ — INDEX/MATCH + IFERROR:
=IFERROR(INDEX(C2:C100, MATCH(G2, A2:A100, 0)), "Not found")
// Hoàn toàn tương đương về kết quả

Bảng Tóm Tắt Thay Thế #

CSE = Ctrl+Shift+Enter (legacy array formula).

Câu Hỏi Thường Gặp (FAQ) #

SEQUENCE có thể thay bằng gì? #

Dùng ROW(INDIRECT("1:" & n)) để tạo dãy 1 đến n. Ví dụ: ROW(INDIRECT("1:10")) = {1;2;3;...;10}. Hoặc tạo helper column 1, 2, 3...

Tại sao phải nhấn Ctrl+Shift+Enter? #

Excel cũ yêu cầu CSE (legacy array formula) khi công thức xử lý mảng. Excel 365 tự hiểu — chỉ cần Enter. Nếu quên CSE, kết quả sẽ sai hoặc trả lỗi #VALUE!.

Nếu cần tương thích, có nên tránh Dynamic Array hoàn toàn? #

Không. Dùng DAF khi team đã lên Excel 365. Chỉ dùng thay thế khi file PHẢI chia sẻ với người dùng Excel cũ. Kiểm tra bằng: File → Info → Check for Issues → Check Compatibility.

Tổng Kết #

Dynamic Array Functions tuyệt vời nhưng không phải lúc nào cũng khả dụng. Bằng cách kết hợp INDEX, MATCH, SMALL, RANK, COUNTIF, và Ctrl+Shift+Enter, bạn có thể đạt kết quả tương tự trên mọi phiên bản Excel. Hãy nhớ: công thức cũ dài hơn, nhưng hoàn toàn có khả năng thay thế.