Excel báo lỗi Spill không phải là vấn đề quá lớn nhưng hiện tại vẫn có nhiều người mắc kẹt với sự cố này. Bạn đừng vội lo vì Trùm Thủ Thuật đã cung cấp các thông tin hữu ích giúp bạn fix lỗi.
Hiểu một cách đơn giản nhất, lỗi #SPILL! xuất hiện khi một công thức tạo ra nhiều kết quả nhưng không xuất tất cả kết quả này trên trang tính được. Đây là tình trạng thường có mặt trên Microsoft Office 365 với các bản cập nhật Excel mới nhất vì nó tạo ra các mảng động. Thông tin chi tiết về trường hợp Excel báo lỗi Spill sẽ được Trùm Thủ Thuật tiếp tục chia sẻ ở phần tiếp theo.
Thông tin chi tiết về tình trạng Excel báo lỗi Spill
#Spill là lỗi gì vậy nhỉ? Trong phiên bản Excel 365 đã cho ra mắt mảng động. Khi đó, mọi công thức tạo ra nhiều kết quả đều tự động “tràn” các kết quả vào các ô lân cận. Trong đó, một phạm vi ô chứa kết quả được gọi là phạm vi tràn. Và nếu như phạm vi tràn này không bị bỏ trống để điều kết quả, thì lỗi #Spill sẽ xảy ra.
Ví dụ cụ thể như sau: Ở đây admin có hàm UNIQUE kiểm tra danh sách các mục và trả về các giá trị duy nhất của danh sách. Và ô B2 cho đến B4 sẽ làm phạm vi tràn.
Lúc này, phạm vi tràn không trống để điền kết quả của hàm UNIQUE nên lỗi sẽ như hình dưới đây.
Nguyên nhân gây ra lỗi #SPILL trong Excel
- Phạm vi tràn không để trống (tức là các ô này đã có dữ liệu trước nên kết quả không xuất ra được).
- Phạm vi tràn đã có một số ô được hợp nhất.
- Phạm vi tràn nằm trong bảng cũng có khả năng là nguyên nhân gây ra lỗi. Vì mảng động không hoạt động trong bảng của Excel.
- Phạm vi tràn quá lớn, vượt quá ranh giới của bảng tính.
- Không thiết lập được phạm vi tràn cũng là lý do phổ biến gây ra lỗi #Spill.
Cách xác định nguyên nhân gây ra lỗi tràn
Để có cách khắc phục tình trạng #Spill chính xác, chúng ta cần phải tìm ra “thủ phạm” gây ra sự cố mà bạn đang phải đối diện. Thao tác này cũng vô cùng đơn giản. Ngay khi thông báo lỗi xuất hiện, bạn chỉ cần nhấn vào biểu tượng lỗi màu vàng, có dấu chấm than bên trong (bên cạnh lỗi #Spill) > Xem tùy chọn đầu tiên trong danh sách thả xuống là được.
Ví dụ như ảnh minh họa ở trên: Có thể thấy là trong phạm vi tràn đã có các dữ liệu (chữ ABC trong ô B4) > Do đó, Excel đã xác định lỗi là phạm vi tràn không trống.
CÓ THỂ BẠN CẦN
Cách khắc phục lỗi #SPILL trong Excel chi tiết từ A đến Z
Sau khi đã xác định được nguyên nhân gây ra lỗi, bạn hãy áp dụng cách khắc phục tương ứng để khắc phục sự cố này nhé!
Trường hợp 1 – Phạm vi tràn không trống
Đối với nguyên nhân là do phạm vi tràn đã có dữ liệu nên kết quả không thể xuất ra được. Cách khắc phục cực kỳ đơn giản là bạn chỉ cần xóa dữ liệu “cản trở” này đi, để ô trống là được.
Lưu ý: Có một số trường hợp các ô trong phạm vi tràn có vẻ trống nhưng vẫn xảy ra lỗi. Nguyên nhân là do có chứa các ký tự vô hình. Để kiểm tra, bạn có thể nhấp vào cảnh báo màu vàng có dấu chấm than bên cạnh lỗi > Nhấn chọn vào dòng “Select Obstructing Cells” > Excel sẽ hiển thị cho bạn các ô nào ngăn công thức tràn.
Trường hợp 2 – Phạm vi tràn có chứa các ô bị hợp nhất
Nếu các ô bị hợp nhất trong phạm vi tràn, kết quả cũng không xuất ra được và gây ra lỗi #Spill. Để fix lỗi, bạn chỉ cần bỏ hợp nhất các ô này là xong. Tương tự, nếu như bạn không biết ô nào đã bị hợp nhất. Bạn nhấn vào biểu tượng cảnh báo như các hướng dẫn trên bên cạnh thông báo lỗi > Lúc này, bạn nhấn chọn Select Obstructing Cells để xác định ô cản trở > Hủy hợp nhất các ô này là có thể giải quyết vấn đề.
Trường hợp 3 – Phạm vi tràn nằm trong bảng
Như admin đã chia sẻ từ đầu, mảng động không được hỗ trợ trong bảng Excel. Vì vậy, nếu phạm vi tràn nằm trong bảng thì lỗi #Spill sẽ xảy ra. Cách duy nhất bạn có thể khắc phục trường hợp này là chuyển đổi bảng thành một phạm vi bình thường hoặc đặt công thức bên ngoài bảng.
Nếu bạn muốn đặt bảng thành phạm vi bình thường > Bạn cần nhấn chuột phải vào bất cứ nơi nào của bảng > Chọn Tables > Convert to Range. Sau đó, bạn áp dụng lại công thức thì lỗi #Spill sẽ không diễn ra nữa.
Trường hợp 4 – Phạm vi tràn không xác định
Đối với trường hợp này, Excel không thiết lập được những kích thước của mảng tràn nên mới gây ra lỗi. Ví dụ, khi bạn sử dụng các hàm RANDARRAY, RAND hoặc RANDBETWEEN kết hợp với các hàm mảng động thì sự cố #Spill có thể xuất hiện. Vì một trong số những hàm đó bị “bay hơi” và làm thay đổi giữa các phép tính nên không thể xác định được kích thước của hàm. Lúc này, thông báo lỗi sẽ là “Spill range is unknown”.
Để khắc phục tình trạng này, giải pháp tối ưu là bạn nên tìm một công thức khác.
Trường hợp 5 – Phạm vi tràn quá lớn
Với trường hợp này thì Excel không thể xuất ra mảng tràn vì nó đã vượt ra ngoài các cạnh của bảng tính. Trường hợp này khá “khó nhai”, bạn cần phải tùy chỉnh công thức của mình theo cách khác phụ thuộc vào mục tiêu cũng như cấu trúc của trang tính mình đang làm việc.
Tùy theo trường hợp cụ thể bạn đang gặp phải, người dùng có thể cân nhắc các biện pháp như sau:
- Thay vì tham chiếu toàn bộ cột, bạn nên thử tham chiếu dữ liệu liên quan. Điều này vừa giúp fix lỗi #Spill, vừa tiết kiệm tài nguyên và nâng cao hiệu suất cho Excel.
- Thực hiện tính toán cho một ô rồi tiến hành sao chép công thức xuống.
- Sử dụng giao lộ ngầm định trong Excel và toán tử @ (cách này hơi phức tạp nên bạn tìm hiểu kỹ hơn nhé).
Mẹo chẩn đoán nhanh
- Nhấp vào ô có #SPILL! và bạn xem tooltip vùng tràn dự kiến (Excel sẽ viền vùng).
- Nếu thấy ô gộp/ô có dữ liệu trong vùng viền thì nên xoá/bỏ gộp là hết.
- Thử tạm đặt công thức ở góc trên cùng trái của một vùng trống hoàn toàn (ví dụ ở một sheet mới). Nếu chạy được thì chắc chắn lỗi do bị cản vùng tràn bạn nha.
Trùm Thủ Thuật đã chia sẻ chi tiết nguyên nhân cũng như cách khắc phục khi Excel báo lỗi Spill. Mong rằng thông tin của mình sẽ giúp anh em fix lỗi này suôn sẻ.
