Công thức mảng Array Formula trong Excel

Trong Excel, Array Formula (Công thức mảng) là một công cụ mạnh mẽ cho phép bạn thực hiện nhiều phép tính trên một hoặc nhiều tập hợp giá trị (vùng dữ liệu) cùng một lúc, thay vì chỉ thực hiện trên từng ô riêng lẻ.
Dưới đây là những điều bạn cần biết để làm chủ công thức mảng:

1. Lợi ích của Công thức mảng

Tiết kiệm thời gian: Không cần phải sao chép (copy-paste) hay kéo chuột công thức xuống hàng nghìn dòng.

Dễ bảo trì: Nếu cần thay đổi công thức, bạn chỉ cần sửa tại một ô duy nhất (ô đầu tiên), các kết quả còn lại sẽ tự động cập nhật.

Tính nhất quán: Tránh sai sót do vô tình xóa hoặc sửa công thức ở một dòng nào đó trong danh sách.

2. Cách sử dụng

(Phiên bản mới 365 / 2021+)

Excel hiện nay sử dụng cơ chế “Dynamic Arrays” (Mảng động). Bạn không cần dùng hàm đặc biệt nào cả.

Cách làm: Chỉ cần chọn vùng dữ liệu thay vì một ô.

Ví dụ: =A2:A100 * B2:B100

Kết quả sẽ tự động “tràn” (spill) xuống các ô bên dưới.

3. Ứng dụng thực tế

Trong Excel hiện đại (Microsoft 365 hoặc Excel 2021+), công thức mảng đã trở nên cực kỳ mạnh mẽ nhờ cơ chế Mảng động (Dynamic Arrays). Dưới đây là các ví dụ thực tế mà bạn có thể áp dụng ngay vào công việc:

1. Lọc dữ liệu tự động với hàm FILTER

Thay vì dùng chức năng Filter thủ công, bạn có thể dùng công thức để tự động trích xuất danh sách theo điều kiện.

Bài toán: Bạn có danh sách bán hàng và muốn lọc ra tất cả các đơn hàng của nhân viên tên “An”.

Công thức:
=FILTER(A2:C100, B2:B100=”An”)

Kết quả: Excel sẽ tự động tạo ra một bảng mới chỉ bao gồm các dòng có tên “An”. Khi bạn thêm dữ liệu mới vào bảng gốc, bảng lọc này sẽ tự cập nhật.

2. Lấy danh sách duy nhất với hàm UNIQUE

Bài toán: Bạn có một cột gồm 1.000 dòng tên khách hàng, trong đó nhiều tên bị lặp lại. Bạn muốn lấy ra danh sách các khách hàng khác nhau (không trùng lặp).

Công thức:
=UNIQUE(A2:A1000)

Kết quả: Một danh sách rút gọn các tên duy nhất sẽ hiện ra ngay lập tức.

3. Sắp xếp dữ liệu tự động với hàm SORT

Bài toán: Bạn muốn danh sách doanh thu (Cột B) tự động sắp xếp từ cao xuống thấp.

Công thức:
=SORT(A2:B100, 2, -1)
(Số 2 là cột doanh thu, -1 là sắp xếp giảm dần).

Kết quả: Khi bạn thay đổi số liệu doanh thu, thứ tự các hàng sẽ tự động nhảy lại cho đúng.

4. Kết hợp “Bộ ba quyền lực”: UNIQUE + SORT + FILTER

Đây là cách dùng mảng nâng cao để tạo ra báo cáo chuyên nghiệp chỉ với 1 ô công thức.

Bài toán: Lấy danh sách khách hàng duy nhất của khu vực “Miền Bắc” và sắp xếp theo thứ tự bảng chữ cái.

Công thức:
=SORT(UNIQUE(FILTER(A2:A100, B2:B100=”Miền Bắc”)))

5. Tính tổng nhiều điều kiện (Dùng mảng thay cho SUMIFS)

Bài toán: Tính tổng thành tiền cho các sản phẩm thuộc loại “Điện tử” VÀ có số lượng trên 10.

Công thức:
=SUM((B2:B100=”Điện tử”) * (C2:C100>10) * (D2:D100))
(Trong công thức mảng, dấu * tương đương với điều kiện VÀ).

4. Một số lưu ý quan trọng

Lỗi “Đè dữ liệu” (#REF! hoặc #SPILL!): Công thức mảng cần một khoảng không gian trống để hiển thị kết quả. Nếu có bất kỳ dữ liệu nào nằm chắn đường “tràn” của mảng, công thức sẽ báo lỗi. Bạn chỉ cần xóa dữ liệu cản trở đó đi.

Vùng trống: Nếu bạn dùng dải ô mở rộng như A2:A, công thức sẽ tính toán đến tận cuối bảng tính. Để tránh hiện kết quả ở các dòng trống, bạn nên kết hợp với hàm IF:
=ARRAYFORMULA(IF(A2:A=””, “”, A2:A * B2:B))
(Nếu ô A trống thì để trống, ngược lại mới tính toán).

5. Excel bản cũ (2019 trở về trước)

Dùng Excel bản cũ (2019, 2016 hoặc cũ hơn) giống như làm việc trong một “căn bếp” chưa có máy móc tự động. Bạn vẫn có thể làm được những việc phi thường, nhưng phải dùng đúng kỹ thuật CSE (Ctrl + Shift + Enter).

Dưới đây là hướng dẫn chi tiết cách “ép” Excel bản cũ xử lý mảng:

1. Cách nhận biết Công thức mảng CSE

Khi bạn nhấn Ctrl + Shift + Enter, Excel sẽ tự động bao bọc công thức của bạn trong cặp ngoặc nhọn { }.

Ví dụ: {=SUM(A1:A10*B1:B10)}

Lưu ý: Bạn không thể tự gõ cặp ngoặc này từ bàn phím, phải để Excel tự thêm vào thông qua tổ hợp phím CSE.

2. Hai kiểu dùng mảng phổ biến ở bản cũ

Kiểu A: Xuất kết quả ra 1 ô duy nhất (Phổ biến nhất)
Dùng khi bạn muốn tính toán phức tạp nhưng chỉ cần trả về một con số tổng quát.

Bài toán: Tính tổng doanh thu của những mặt hàng có giá trị trên 500.000đ mà không cần cột phụ.

Công thức: =SUM(IF(B2:B10 > 500000, B2:B10, 0))

Cách làm: Gõ xong công thức -> Nhấn Ctrl + Shift + Enter.

Kiểu B: Xuất kết quả ra một vùng (Thay cho FILTER/UNIQUE)
Vì bản cũ không có hàm FILTER tự tràn, bạn phải “đặt chỗ” trước cho kết quả.

Bài toán: Bạn muốn nhân cột Số lượng với Đơn giá cho 10 dòng.

Cách làm:

Dùng chuột bôi đen sẵn vùng C2:C11.

Gõ công thức: =A2:A11 * B2:B11 (không nhấn Enter vội).

Nhấn Ctrl + Shift + Enter.

Kết quả sẽ lấp đầy 10 ô bạn đã chọn.

3. Cách “giả lập” hàm UNIQUE và FILTER (Kỹ thuật khó)

Đây là lý do tại sao người dùng Excel cũ rất sợ hoặc rất nể công thức mảng, vì nó trông như “ma trận”.

Giả lập FILTER (Lấy danh sách thỏa mãn điều kiện):
Để lọc ra danh sách nhân viên phòng “Sale”, bạn phải dùng hàm INDEX kết hợp SMALL và IF:
=INDEX($A$2:$A$10, SMALL(IF($B$2:$B$10=”Sale”, ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1, “”), ROW(A1)))

Giải thích: Nó dò từng dòng, nếu là “Sale” thì ghi nhớ số dòng, sau đó lấy số dòng nhỏ nhất (hàng 1), nhỏ nhì (hàng 2)… để đưa vào hàm INDEX.

Thực hiện: Nhấn CSE, sau đó kéo công thức xuống đến khi hiện lỗi #NUM! (hết dữ liệu).

Giả lập UNIQUE (Lấy danh sách không trùng):
=INDEX($A$2:$A$10, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$10), 0))

Thực hiện: Nhấn CSE và kéo xuống.

4. Những “nỗi khổ” của bản cũ bạn cần lưu ý:

Không tự tràn: Nếu vùng bạn chọn trước quá ngắn, bạn mất dữ liệu. Nếu quá dài, bạn dính lỗi #N/A.

Không thể sửa từng ô: Nếu bạn đã dùng CSE cho một vùng (như ví dụ Kiểu B), bạn không thể xóa hoặc sửa nội dung của chỉ một ô trong vùng đó. Bạn phải bôi đen cả vùng đó mới sửa được.

Nặng máy: Excel cũ mà dùng quá nhiều công thức mảng (CSE) lồng hàm IF và SUMPRODUCT trên hàng chục nghìn dòng sẽ khiến file bị treo hoặc mở cực chậm.