Lỗi #SPILL! xuất hiện khi một công thức mảng động (dynamic array) cần trả kết quả ra nhiều ô nhưng vùng đó không trống hoặc không cho phép tràn. Nguyên nhân phổ biến nhất là có dữ liệu, kể cả một dấu cách, nằm trong vùng tràn. Chọn ô lỗi, bấm biểu tượng cảnh báo và chọn Select Obstructing Cells để Excel chỉ ra ô đang chặn, xoá hoặc dời ô đó là xong. Lỗi này chỉ có trên Excel cho Microsoft 365, Excel 2021 và các bản mới hơn.
| Thông báo khi bấm biểu tượng cảnh báo | Nguyên nhân | Cách sửa |
|---|---|---|
| Spill range isn't blank | Vùng tràn có dữ liệu | Select Obstructing Cells, xoá hoặc dời đi |
| Spill range has merged cells | Vùng tràn có ô gộp | Bỏ gộp ô (Merge & Center > Unmerge Cells) |
| Spill range in table | Công thức nằm trong Excel Table | Chuyển bảng về vùng thường hoặc đặt công thức ra ngoài bảng |
| Spill range is unknown | Kích thước kết quả thay đổi liên tục (hàm biến động như RANDARRAY, OFFSET) | Cố định kích thước kết quả |
| Spill range is too big | Kết quả vượt mép trang tính | Tham chiếu ô đơn thay vì cả cột, dùng toán tử @ |
| Out of memory | Công thức quá lớn | Thu nhỏ vùng tham chiếu |
Công thức "tràn" là gì
Từ Excel cho Microsoft 365 và Excel 2021, một công thức có thể trả về nhiều giá trị và tự điền vào các ô lân cận. Ví dụ =SORT(A2:A20) trả về 19 giá trị đã sắp xếp, tự điền xuống 19 ô bên dưới. Vùng này gọi là vùng tràn (spill range), có viền xanh khi chọn. Các hàm hay tràn: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, XLOOKUP trả về nhiều cột, và bất kỳ phép tính trên cả một vùng như =A2:A20*2.
Trong Excel 2019 trở về trước, các công thức này phải nhập bằng Ctrl + Shift + Enter hoặc chỉ trả về một ô. Nên file cũ mở trên Excel mới đôi khi phát sinh #SPILL!.
Cách sửa từng nguyên nhân
Công thức trong bài dùng dấu chấm phẩy (;) ngăn cách đối số, theo định dạng vùng Việt Nam. Máy để định dạng vùng Mỹ thì thay bằng dấu phẩy.
1. Vùng tràn không trống
Đây là lý do gặp nhiều nhất.
- Chọn ô báo #SPILL!. Excel vẽ viền nét đứt quanh vùng mà công thức định tràn ra.
- Bấm biểu tượng cảnh báo hình thoi vàng, chọn Select Obstructing Cells.
- Xoá nội dung các ô được chọn (Delete), hoặc cắt dán sang chỗ khác.
- Nếu ô trông trống mà vẫn chặn, ô đó có thể chứa dấu cách, chuỗi rỗng "" từ công thức khác, hoặc chữ trắng trên nền trắng. Chọn ô và nhìn thanh công thức.
2. Vùng tràn có ô gộp
Công thức mảng không tràn vào ô gộp. Chọn vùng, vào Home > Merge & Center, chọn Unmerge Cells. Muốn tiêu đề trông như gộp ô mà không gộp, dùng Format Cells > Alignment > Horizontal: Center Across Selection.
3. Công thức nằm trong Excel Table
Theo Microsoft, Excel Table (bảng tạo bằng Ctrl + T) không hỗ trợ công thức mảng tràn. Hai cách:
- Đặt công thức ra ngoài bảng, tham chiếu vào bảng như bình thường.
- Hoặc chuyển bảng về vùng thường: chọn một ô trong bảng, vào Table Design > Convert to Range. Bạn mất các tính năng của bảng như tự mở rộng, tên cột trong công thức.
4. Kết quả quá lớn, tràn ra khỏi trang tính
Lỗi hay gặp khi viết công thức tham chiếu cả cột trong một ô, ví dụ đặt ở ô C2 công thức =VLOOKUP(A:A; E:F; 2; FALSE). Excel mới hiểu là "tra cho mọi ô trong cột A", tức hơn một triệu kết quả, không đủ chỗ tràn từ hàng 2 xuống.
Sửa theo một trong ba cách:
- Chỉ tham chiếu ô trên cùng hàng: =VLOOKUP(A2; E:F; 2; FALSE), rồi kéo công thức xuống như Excel cũ.
- Tham chiếu đúng vùng dữ liệu: =VLOOKUP(A2:A500; E:F; 2; FALSE) và để nó tự tràn.
- Dùng toán tử @ để ép trả về một giá trị theo hàng đang đứng: =VLOOKUP(@A:A; E:F; 2; FALSE).
5. Kích thước kết quả không xác định
Hàm biến động như RANDARRAY, RANDBETWEEN hay OFFSET làm kích thước kết quả thay đổi mỗi lần tính lại. Excel không xác định được vùng tràn. Cố định số hàng, số cột trong đối số, ví dụ =RANDARRAY(10; 1) thay vì để nó phụ thuộc hàm biến động khác.
Tham chiếu tới cả vùng tràn
Muốn dùng lại toàn bộ kết quả tràn ở công thức khác, thêm dấu # sau ô đầu. Ví dụ công thức tràn nằm ở E2 thì =SUM(E2#) cộng toàn bộ vùng tràn, tự cập nhật khi vùng dài ra hay ngắn lại.
Khi mở file trên máy khác
Nếu bạn gửi file cho người dùng Excel 2016, 2019, các công thức tràn sẽ hiện dạng công thức mảng cũ (trong ngoặc nhọn) và có thể không cập nhật đúng. Nếu người nhận dùng Excel bản cũ, cân nhắc viết công thức theo kiểu một ô rồi kéo xuống.
Câu hỏi thường gặp
Excel bị lỗi hàm vlookup ra #SPILL! là sao?
Thường do bạn tham chiếu cả cột ở đối số giá trị cần tìm, ví dụ A:A. Đổi thành ô cùng hàng như A2, hoặc thêm @ trước A:A, rồi kéo công thức xuống.
Excel bị lỗi spill có phải do file bị hỏng không?
Không. #SPILL! chỉ là thông báo công thức không có chỗ để điền kết quả. File vẫn bình thường, sửa vùng tràn là xong.
Tại sao Excel trên máy tôi báo #SPILL! mà máy đồng nghiệp không báo?
Máy đồng nghiệp có thể dùng Excel 2019 trở về trước, không có mảng động, nên công thức chỉ trả về một ô. Excel mới hiểu công thức theo cách khác và cố tràn kết quả ra nhiều ô.
Excel bị lỗi you cannot change part of an array có liên quan không?
Có liên quan. Thông báo này xuất hiện khi bạn sửa hoặc xoá một ô nằm trong vùng kết quả của công thức mảng. Hãy chọn ô đầu chứa công thức để sửa hoặc xoá cả công thức, không sửa từng ô trong vùng tràn.