Công thức Excel: Cách sửa lỗi #SPILL! lỗi -

Mục lục

Tóm lược

Lỗi #SPILL xảy ra khi phạm vi tràn bị chặn bởi thứ gì đó trên trang tính. Giải pháp thường là xóa phạm vi tràn của bất kỳ dữ liệu cản trở nào. Xem bên dưới để biết thêm thông tin và các bước giải quyết.

Giải trình

Giới thiệu về sự tràn và #SPILL! lỗi

Với sự ra đời của Mảng động trong Excel, các công thức trả về nhiều giá trị sẽ "tràn" các giá trị này trực tiếp lên trang tính. Hình chữ nhật bao quanh các giá trị được gọi là "phạm vi tràn". Khi dữ liệu thay đổi, phạm vi tràn sẽ mở rộng hoặc thu hẹp khi cần thiết. Bạn có thể thấy các giá trị mới được thêm vào hoặc các giá trị hiện có biến mất.

Video: Tràn và phạm vi tràn

Lỗi #SPILL xảy ra khi phạm vi tràn bị chặn bởi thứ gì đó trên trang tính. Đôi khi điều này được mong đợi. Ví dụ: bạn đã nhập một công thức, hy vọng nó sẽ tràn, nhưng dữ liệu hiện có trong trang tính đang cản trở. Giải pháp chỉ là xóa phạm vi tràn của bất kỳ dữ liệu cản trở nào.

Tuy nhiên, đôi khi lỗi có thể không mong muốn và do đó gây nhầm lẫn. Đọc bên dưới để biết lỗi này có thể gây ra như thế nào và bạn có thể làm gì để giải quyết.

Hành vi tràn là bản địa

Điều quan trọng là phải hiểu rằng hành vi tràn là tự động và tự nhiên. Trong Excel động (hiện chỉ dành cho Office 365 Excel) bất kỳ công thức nào, ngay cả một công thức đơn giản không có hàm, đều có thể làm tràn kết quả. Mặc dù có nhiều cách để ngăn một công thức trả về nhiều kết quả, nhưng không thể vô hiệu hóa bản thân việc đổ với cài đặt chung.

Tương tự, không có tùy chọn nào trong Excel để "vô hiệu hóa lỗi #SPILL. Để sửa lỗi #SPILL, bạn sẽ phải điều tra và giải quyết nguyên nhân gốc rễ của sự cố.

Khắc phục # 1 - xóa phạm vi tràn

Đây là trường hợp đơn giản nhất để giải quyết. Công thức sẽ đổ nhiều giá trị, nhưng thay vào đó nó trả về lỗi #SPILL! bởi vì có cái gì đó cản đường. Để giải quyết lỗi, hãy chọn bất kỳ ô nào trong phạm vi tràn để bạn có thể thấy ranh giới của nó. Sau đó, di chuyển dữ liệu chặn đến một vị trí mới hoặc xóa toàn bộ dữ liệu. Lưu ý rằng các ô trong phạm vi tràn phải trống, vì vậy hãy chú ý đến các ô có chứa các ký tự ẩn, như dấu cách.

Trong màn hình bên dưới, dấu "x" đang chặn phạm vi tràn:

Một trong những "x" bị loại bỏ, hàm UNIQUE tràn ra kết quả bình thường:

Khắc phục # 2 - thêm ký tự @

Trước Mảng động, Excel đã âm thầm áp dụng một hành vi được gọi là "giao điểm ngầm định" để đảm bảo rằng một số công thức nhất định có khả năng trả về nhiều kết quả chỉ trả về một kết quả duy nhất. Trong Excel mảng không động, các công thức này trả về kết quả bình thường mà không có lỗi. Tuy nhiên, trong một số trường hợp nhất định, cùng một công thức được nhập trong Dynamic Excel có thể tạo ra lỗi #SPILL. Ví dụ: trong màn hình bên dưới, ô D5 chứa công thức này, được sao chép xuống:

=$B$5:$B$10+3

Công thức này sẽ không gây ra lỗi, chẳng hạn như Excel 2016 vì sự giao nhau ngầm sẽ ngăn công thức trả về nhiều kết quả. Tuy nhiên, trong Dynamic Excel, công thức tự động trả về nhiều kết quả tràn vào trang tính và các kết quả này đụng vào nhau, vì công thức được sao chép từ D5: D10.

Một giải pháp là sử dụng ký tự @ để kích hoạt giao lộ ngầm định như sau:

= @$B$5:$B$10+3

Với thay đổi này, mỗi công thức lại trả về một kết quả duy nhất và lỗi #SPILL biến mất.

Lưu ý: điều này giải thích một phần lý do tại sao bạn có thể đột nhiên thấy ký tự "@" xuất hiện trong các công thức được tạo trong các phiên bản Excel cũ hơn. Điều này được thực hiện để duy trì tính tương thích. Vì các công thức trong các phiên bản Excel cũ hơn không thể tràn vào nhiều ô, nên dấu @ được thêm vào để đảm bảo hành vi tương tự khi công thức được mở trong Dynamic Excel.