Công Thức Mảng Trong Excel: CTRL+SHIFT+ENTER Và Dynamic Arrays

1. Công thức mảng là gì? #

Công thức mảng (array formula) là công thức xử lý NHIỀU giá trị cùng lúc thay vì từng ô một. Nó có thể trả về 1 kết quả duy nhất hoặc NHIỀU kết quả (mảng).

Có 2 loại:

• CSE Arrays (legacy): Nhập bằng Ctrl+Shift+Enter — hoạt động trên mọi phiên bản Excel

• Dynamic Arrays (2019/365): Tự spill kết quả ra nhiều ô — chỉ có Excel 365/2019+

2. CSE Arrays — Ctrl+Shift+Enter #

2.1. Ví dụ 1: Tổng có điều kiện phức tạp #

Tính tổng doanh số của nhân viên nữ trong phòng Kinh doanh:

{=SUM((A2:A100="Kinh doanh")*(B2:B100="Nữ")*C2:C100)}

Nhập: gõ công thức → Ctrl+Shift+Enter (KHÔNG phải Enter thường). Excel thêm {} tự động.

2.2. Cách hoạt động #

• (A2:A100="Kinh doanh") → mảng TRUE/FALSE (1/0)

• (B2:B100="Nữ") → mảng TRUE/FALSE (1/0)

• Nhân 2 mảng → chỉ ô thỏa CẢ 2 điều kiện = 1

• Nhân tiếp với C2:C100 → chỉ tổng giá trị thỏa điều kiện

2.3. Ví dụ 2: Đếm giá trị unique #

Đếm số tên khác nhau trong cột A:

{=SUM(1/COUNTIF(A2:A100,A2:A100))}

2.4. Ví dụ 3: Giá trị lớn nhất có điều kiện #

Doanh số cao nhất của phòng Kinh doanh:

{=MAX(IF(A2:A100="Kinh doanh",C2:C100))}

2.5. Lưu ý CSE #

• PHẢI nhấn Ctrl+Shift+Enter

• Không thể sửa 1 ô trong vùng CSE array

• Chậm trên dữ liệu lớn

• Khó debug

3. Dynamic Arrays — Excel 365/2019+ #

3.1. Spill Range #

Dynamic arrays TỰ ĐỘNG trả nhiều kết quả ra các ô bên dưới/bên phải. Vùng này gọi là spill range — được viền xanh nhạt.

Nhập công thức 1 ô → kết quả tràn ra nhiều ô. Không cần Ctrl+Shift+Enter.

3.2. Toán tử # (Spill Reference) #

Tham chiếu toàn bộ spill range:

=SUM(A1#)

A1# = toàn bộ kết quả spill bắt đầu từ A1.

4. Hàm FILTER #

Lọc dữ liệu theo điều kiện — trả mảng kết quả:

=FILTER(A2:D100, B2:B100="Kinh doanh", "Không có dữ liệu")

4.1. Nhiều điều kiện #

AND (cả 2): dùng dấu *

=FILTER(A2:D100, (B2:B100="KD")*(C2:C100>1000000))

OR (1 trong 2): dùng dấu +

=FILTER(A2:D100, (B2:B100="KD")+(B2:B100="MKT"))

5. Hàm SORT #

Sắp xếp mảng:

=SORT(A2:D100, 3, -1)

5.1. Kết hợp SORT + FILTER #

=SORT(FILTER(A2:D100, B2:B100="KD"), 3, -1)

Lọc phòng KD rồi sắp xếp theo doanh số giảm dần.

6. Hàm UNIQUE #

Trả danh sách giá trị duy nhất:

=UNIQUE(B2:B100)

6.1. Tùy chọn #

=UNIQUE(B2:B100, FALSE, TRUE)

7. Hàm SEQUENCE #

Tạo dãy số tự động:

=SEQUENCE(10)          → 1, 2, 3, ..., 10
=SEQUENCE(5, 3)        → ma trận 5 hàng × 3 cột
=SEQUENCE(12, 1, 0, 100)  → 0, 100, 200, ..., 1100

7.1. Ứng dụng: Tạo danh sách ngày #

=SEQUENCE(30, 1, DATE(2024,1,1), 1)

Tạo 30 ngày từ 01/01/2024.

8. Hàm SORTBY #

Sắp xếp theo cột NGOÀI mảng:

=SORTBY(A2:B100, C2:C100, -1)

Sắp xếp cột A:B theo giá trị cột C giảm dần.

9. Hàm RANDARRAY #

Tạo mảng số ngẫu nhiên:

=RANDARRAY(5, 3, 1, 100, TRUE)

Ma trận 5×3 số nguyên từ 1 đến 100.

10. Kết hợp Dynamic Arrays #

10.1. Dashboard tự động #

=LET(
    data, A2:D100,
    dept, "Kinh doanh",
    filtered, FILTER(data, INDEX(data,,2)=dept),
    sorted, SORT(filtered, 3, -1),
    sorted
)

Dùng hàm LET để đặt tên biến → code sạch, dễ đọc.

11. Mẹo Array Formulas #

1. Ưu tiên Dynamic Arrays nếu có Excel 365+

2. Ctrl+Shift+Enter chỉ cần cho Excel 2016 trở xuống

3. Spill error #SPILL!: Ô bên dưới/bên phải không trống → xóa dữ liệu cản đường

4. Debug CSE: Chọn phần công thức → F9 → xem kết quả trung gian → Esc

5. LET giúp tránh tính toán lặp trong công thức phức tạp

12. Tổng kết #

Dynamic Arrays là cuộc cách mạng lớn nhất trong Excel kể từ Pivot Table. Với FILTER, SORT, UNIQUE, SEQUENCE, bạn có thể tạo báo cáo động chỉ bằng công thức — không cần VBA, không cần Power Query cho những tác vụ đơn giản.