Mảng Động Trong Excel Là Gì Và Cách Sửa Lỗi #SPILL

Mảng động là thay đổi lớn nhất của Excel trong mười năm trở lại đây. Hiểu đơn giản: một công thức có thể tự trả về nhiều ô kết quả thay vì chỉ một ô. Bạn gõ công thức vào một ô, Excel tự điền kết quả ra các ô xung quanh. Hiện tượng đó gọi là tràn, và lỗi #SPILL là khi việc tràn bị chặn lại.

Bài này giải thích cơ chế, sau đó xử lý lỗi #SPILL theo từng nguyên nhân cụ thể.

Mảng động hoạt động thế nào?

Trước đây, muốn lấy danh sách không trùng từ một cột, bạn phải dùng công thức phức tạp rồi kéo xuống hàng trăm dòng. Với mảng động, bạn gõ =UNIQUE(A2:A500) vào một ô duy nhất, và Excel tự đổ toàn bộ kết quả xuống bên dưới.

Vùng kết quả đó được gọi là vùng tràn. Nó có ba đặc điểm cần nhớ:

• Chỉ có một ô chứa công thức thật, là ô trên cùng bên trái. Các ô còn lại chỉ hiển thị kết quả, bấm vào sẽ thấy công thức mờ đi.
• Vùng tràn tự co giãn theo dữ liệu nguồn. Thêm dòng vào nguồn thì kết quả tự dài ra, không phải kéo lại công thức.
• Vùng tràn có viền xanh nhạt bao quanh khi bạn chọn ô công thức.

Chính đặc điểm thứ hai là lý do mảng động đáng giá: bảng báo cáo tự cập nhật khi dữ liệu thay đổi, không cần ai nhớ kéo công thức.

Những hàm thuộc nhóm mảng động

Nhóm hàm cốt lõi gồm:

• FILTER - lọc dữ liệu theo điều kiện, xem hàm FILTER trong Excel.
• UNIQUE, SORT, SORTBY - lấy giá trị duy nhất và sắp xếp, xem hàm UNIQUE, SORT và SORTBY.
• SEQUENCE, RANDARRAY - tạo dãy số và dữ liệu ngẫu nhiên, xem hàm SEQUENCE và RANDARRAY.
• XLOOKUP - tra cứu, có thể trả về nhiều cột cùng lúc, xem hàm XLOOKUP trong Excel.

Cả nhóm này chỉ có từ Office 2021 trở lên. Đây là khác biệt lớn nhất giữa bản 2019 và 2021, được so sánh chi tiết tại so sánh Office 2016, 2019, 2021 và 2024.

Mở file mảng động bằng Office 2016 hoặc 2019 thì sao?

Công thức sẽ hiển thị lỗi #NAME?. Excel không nhận ra tên hàm vì phiên bản đó chưa có hàm ấy.

Tình huống hay gặp nhất là ở văn phòng, khi mỗi máy cài một bản Office khác nhau. Một người làm file bằng bản 2021 gửi cho đồng nghiệp dùng bản 2016, và người kia mở ra thấy lỗi tràn lan dù file hoàn toàn bình thường.

Không có cách nào thêm nhóm hàm này vào bản cũ. Giải pháp là chuyển công thức sang cách viết cũ, hoặc nâng lên key Office 2021 trở lên. Nếu cả phòng dùng chung file, nên thống nhất một phiên bản để tránh tình trạng mỗi máy thấy một kiểu.

Một biến thể khác của vấn đề tương thích là dấu @ tự xuất hiện trong công thức khi mở file giữa hai bản khác nhau. Đây không phải lỗi gõ nhầm, chi tiết ở bài lỗi công thức Excel thường gặp.

Lỗi #SPILL là gì?

Lỗi #SPILL xuất hiện khi công thức muốn tràn kết quả ra nhưng vùng tràn bị chặn. Excel không tự ghi đè lên dữ liệu có sẵn, nên nó dừng lại và báo lỗi. Có năm nguyên nhân, xếp theo mức độ phổ biến.

1. Có dữ liệu chắn đường tràn

Đây là nguyên nhân chiếm phần lớn số ca. Ô nào đó trong vùng tràn đang chứa dữ liệu, kể cả một dấu cách vô hình.

Cách xử lý: bấm vào ô báo lỗi, Excel sẽ khoanh viền đứt nét vùng mà nó định tràn tới. Nhìn theo viền đó để tìm ô có dữ liệu, xóa đi là xong. Nếu không thấy gì, rất có thể ô đó chứa dấu cách - chọn cả vùng rồi bấm Delete để chắc chắn.

2. Vùng tràn vượt quá giới hạn bảng tính

Xảy ra khi bạn đặt công thức ở gần cuối sheet, hoặc tham chiếu cả cột trong một hàm sinh ra mảng. Ví dụ dùng SEQUENCE với số lượng lớn hơn số dòng còn lại.

Cách xử lý: chuyển công thức lên vị trí cao hơn, hoặc thu hẹp vùng tham chiếu về đúng phạm vi dữ liệu thay vì chọn cả cột.

3. Công thức nằm trong bảng Table

Bảng được định dạng dạng Table không cho phép công thức tràn ra ngoài phạm vi của nó. Đây là hạn chế cố hữu chứ không phải lỗi.

Cách xử lý: đưa công thức ra ngoài bảng, hoặc chuyển bảng về dạng vùng dữ liệu thường bằng Table Design → Convert to Range.

4. Vùng tràn có ô bị trộn

Ô đã merge không nhận kết quả tràn. Cần bỏ trộn ô trong vùng tràn bằng Merge & Center.

5. Không xác định được kích thước vùng tràn

Xảy ra với các hàm cho kết quả thay đổi liên tục như hàm lấy số ngẫu nhiên. Excel không biết trước cần bao nhiêu ô nên từ chối tràn. Cách xử lý là chỉ định rõ số dòng và số cột thay vì để hàm tự quyết.

Cách tham chiếu tới một vùng tràn

Nếu bạn muốn dùng kết quả của một công thức mảng động trong công thức khác, đừng chọn thủ công từng ô vì vùng đó sẽ co giãn. Thay vào đó dùng dấu # sau địa chỉ ô chứa công thức.

Ví dụ công thức nằm ở ô D2 và tràn xuống tới D50, bạn viết =SUM(D2#) để cộng toàn bộ vùng tràn. Khi dữ liệu nguồn dài thêm, công thức SUM tự tính đúng mà không cần sửa. Đây là cách làm gọn nhất khi xây bảng báo cáo tự cập nhật, xem thêm ở bài tạo dashboard trong Excel.

Mảng động có làm file nặng thêm không?

Ngược lại là đằng khác. Một công thức FILTER thay cho hàng trăm công thức lặp lại thường làm file nhẹ và chạy nhanh hơn. Nếu file Excel của bạn đang chậm vì quá nhiều công thức, viết lại bằng nhóm hàm mảng động là một hướng đáng thử, cùng với các cách khác ở bài xử lý file Excel chậm và nặng.

Câu hỏi thường gặp

Làm sao biết Excel của tôi có mảng động hay không?

Gõ thử =SEQUENCE(5) vào một ô trống. Nếu ra dãy số từ 1 đến 5 xếp dọc thì bản của bạn có. Nếu báo #NAME? thì không có.

Xóa được từng ô trong vùng tràn không?

Không. Vùng tràn là một khối, muốn bỏ phải xóa ô chứa công thức gốc ở góc trên bên trái.

Lỗi #SPILL và lỗi #REF! khác nhau thế nào?

#SPILL là công thức đúng nhưng không có chỗ để đổ kết quả. #REF! là công thức trỏ tới ô không còn tồn tại, thường do xóa dòng hoặc cột.

Gửi file cho người dùng Excel bản cũ thì nên làm gì?

Sao chép vùng kết quả rồi dán lại dưới dạng giá trị trước khi gửi. Người nhận sẽ thấy số liệu đúng, chỉ là không tự cập nhật được nữa.