Bài viết này sẽ giúp bạn hiểu tất cả các nguyên nhân gây ra lỗi #SPILL! cũng như các giải pháp để khắc phục chúng trong Excel 365.
#SPILL! là một loại lỗi Excel mới chủ yếu xảy ra khi một công thức tạo ra nhiều kết quả tính toán cố gắng hiển thị kết quả đầu ra của nó trong một phạm vi tràn nhưng phạm vi đó đã chứa một số dữ liệu khác.
Dữ liệu chặn có thể là bất kỳ thứ gì bao gồm, giá trị văn bản, các ô đã hợp nhất, một ký tự khoảng trắng hoặc thậm chí khi không có đủ chỗ để trả về kết quả. Giải pháp rất đơn giản, xóa phạm vi của bất kỳ dữ liệu chặn nào hoặc chọn một mảng ô trống không chứa bất kỳ loại dữ liệu nào trong đó.
Lỗi này thường xảy ra khi tính toán công thức mảng động, vì công thức mảng động là công thức xuất kết quả thành nhiều ô hoặc một mảng. Hãy xem xét chi tiết hơn và hiểu những gì gây ra lỗi này trong Excel và cách giải quyết nó.
Xem nhanh
Nguyên nhân gây ra lỗi #SPILL! ?
Kể từ khi ra mắt mảng động vào năm 2018, các công thức Excel có thể xử lý nhiều giá trị cùng một lúc và trả về kết quả trong nhiều ô. Mảng động là mảng có thể thay đổi kích thước cho phép các công thức trả về nhiều kết quả cho một dải ô trên trang tính dựa trên một công thức được nhập trong một ô.
Khi một công thức mảng động trả về nhiều kết quả, các kết quả này sẽ tự động tràn sang các ô lân cận. Hành vi này được gọi là ‘tràn’ trong Excel. Và phạm vi ô mà kết quả tràn vào được gọi là ‘phạm vi tràn’. Phạm vi tràn sẽ tự động mở rộng hoặc thu hẹp dựa trên các giá trị nguồn.
Nếu một công thức đang cố gắng điền vào một dải ô có nhiều kết quả nhưng bị chặn bởi một thứ gì đó trên dải ô đó, thì lỗi #SPILL! sẽ xảy ra.
Excel hiện có 9 hàm sử dụng chức năng mảng động để giải quyết vấn đề, bao gồm:
- SEQUENCE
- FILTER
- TRANSPOSE
- SORT
- SORTBY
- RANDARRAY
- UNIQUE
- XLOOKUP
- XMATCH
Công thức mảng động chỉ có sẵn trong Excel 365 và nó hiện không được hỗ trợ bởi bất kỳ phần mềm Excel offline nào (tức là Microsoft Excel 2016, 2019).
Lỗi #SPILL! không chỉ do cản trở dữ liệu, có một số lý do khiến bạn có thể gặp lỗi nó. Hãy để chúng tôi khám phá các tình huống khác nhau mà bạn có thể gặp phải lỗi này và cách khắc phục chúng.
Phạm vi tràn không trống
Một trong những nguyên nhân chính gây ra lỗi tràn là phạm vi tràn không rỗng. Ví dụ: nếu bạn đang cố gắng hiển thị 10 kết quả, nhưng nếu có bất kỳ dữ liệu nào trong bất kỳ ô nào trong vùng tràn, công thức sẽ trả về lỗi #SPILL! .
Ví dụ 1:
Trong ví dụ dưới đây, chúng ta đã nhập hàm TRANSPOSE trong ô C2 để chuyển đổi phạm vi ô dọc (B2: B5) thành phạm vi ngang (C2: F2). Thay vì chuyển cột thành một hàng, Excel sẽ hiển thị cho chúng ta lỗi #SPILL! .
Và khi bạn nhấp vào ô công thức, bạn sẽ thấy một đường viền gạch ngang màu xanh lam cho biết vùng hay phạm vi tràn (C2: F2) cần thiết để hiển thị kết quả như hình dưới đây. Ngoài ra, bạn sẽ nhận thấy một dấu hiệu cảnh báo màu vàng với một dấu chấm than trên đó.
Để hiểu lý do đằng sau lỗi, hãy nhấp vào biểu tượng cảnh báo bên cạnh lỗi và xem thông báo ở dòng đầu tiên được đánh dấu bằng màu xám. Như bạn có thể thấy, nó cho biết ‘phạm vi tràn không trống’ ở đây.
Vấn đề ở đây là các ô trong phạm vi tràn D2 và E2 có các ký tự văn bản (không trống).
Giải pháp
Giải pháp rất đơn giản, hoặc xóa dữ liệu (di chuyển hoặc xóa) nằm trong phạm vi tràn hoặc di chuyển công thức đến một vị trí khác nơi không có vật cản.
Ngay sau khi bạn xóa hoặc di chuyển phần tắc nghẽn, Excel sẽ tự động điền vào các ô với kết quả của công thức. Ở đây, khi chúng ta xóa văn bản trong D2 và E2, công thức sẽ chuyển cột thành hàng như dự định.
Ví dụ 2:
Trong ví dụ dưới đây, mặc dù phạm vi tràn xuất hiện trống, công thức vẫn hiển thị lỗi #SPILL! . Đó là bởi vì phạm vi tràn có một ký tự khoảng trắng vô hình ở một trong các ô.
Thật khó để xác định các ký tự khoảng trắng hoặc bất kỳ ký tự ẩn nào khác ẩn trong những ô có vẻ như trống. Để tìm những ô có dữ liệu không mong muốn như vậy, hãy nhấp vào Lỗi Floatie (dấu hiệu cảnh báo) và chọn ‘chọn ô gây cản trở’ từ menu và nó sẽ đưa bạn đến ô có chứa dữ liệu cản trở.
Như bạn có thể thấy, trong ảnh chụp màn hình bên dưới, ô E2 có hai ký tự khoảng trắng. Khi xóa những dữ liệu đó, bạn sẽ nhận được đầu ra phù hợp.
Đôi khi, ký tự ẩn có thể là văn bản được format với cùng màu phông chữ với màu tô của ô hoặc giá trị ô được format tùy chỉnh bằng mã số ;;;. Khi bạn tùy chỉnh format giá trị ô ;;;, nó sẽ ẩn bất kỳ thứ gì trong ô đó, bất kể màu phông chữ hoặc màu ô.
Phạm vi tràn chứa các ô được hợp nhất
Đôi khi, lỗi #SPILL! xảy ra khi phạm vi tràn chứa các ô đã hợp nhất. Công thức mảng động không hoạt động với các ô đã hợp nhất. Để khắc phục điều này, tất cả những gì bạn phải làm là hủy hợp nhất các ô trong phạm vi tràn hoặc di chuyển công thức sang một phạm vi khác không có ô đã hợp nhất.
Trong ví dụ dưới đây, mặc dù phạm vi tràn là trống (C2: CC8), công thức trả về lỗi tràn. Đó là do các ô C4 và C5 được hợp nhất.
Để đảm bảo rằng các ô đã hợp nhất là lý do khiến bạn gặp lỗi, hãy nhấp vào dấu hiệu cảnh báo và xác minh nguyên nhân.
Giải pháp
Để hủy hợp nhất các ô, hãy chọn các ô đã hợp nhất, sau đó trên tab ‘trang chủ’, nhấp vào nút ‘hợp nhất & căn giữa’ và chọn ‘hủy hợp nhất các ô’.
Nếu bạn gặp khó khăn trong việc xác định các ô đã hợp nhất trong bảng tính lớn của mình, hãy nhấp vào tùy chọn ‘chọn các ô cản trở’ từ menu dấu hiệu cảnh báo để chuyển đến các ô đã hợp nhất.
Phạm vi tràn trong bảng
Công thức mảng tràn không được hỗ trợ trong bảng Excel. Công thức mảng động chỉ nên được nhập vào một ô riêng lẻ. Nếu bạn nhập công thức mảng bị tràn trong bảng hoặc khi vùng tràn nằm trong bảng, bạn sẽ gặp lỗi #SPILL!. Khi điều này xảy ra, hãy thử chuyển đổi bảng thành một phạm vi bình thường hoặc di chuyển công thức ra ngoài bảng.
Ví dụ: khi chúng tôi nhập công thức phạm vi tràn sau đây trong bảng Excel, chúng tôi sẽ gặp lỗi #SPILL! trong mọi ô của bảng, không chỉ ô công thức. Đó là vì Excel tự động sao chép bất kỳ công thức nào được nhập trong bảng vào mọi ô trong cột của bảng.
Ngoài ra, bạn sẽ gặp lỗi #SPILL! khi một công thức cố gắng làm đổ kết quả trong bảng. Trong ảnh chụp màn hình bên dưới, khu vực tràn nằm trong bảng hiện có, vì vậy chúng tôi gặp lỗi #SPILL!.
Để xác nhận nguyên nhân đằng sau lỗi này, hãy nhấp vào dấu hiệu cảnh báo và xem lý do lỗi.
Giải pháp
Để khắc phục lỗi, bạn cần phải hoàn nguyên bảng Excel về phạm vi. Để làm điều đó, hãy bấm chuột phải vào bất kỳ đâu trong bảng, bấm vào ‘bảng’, rồi chọn tùy chọn ‘chuyển đổi sang phạm vi’. Ngoài ra, bạn có thể nhấp chuột trái vào bất kỳ vị trí nào trong bảng, sau đó chuyển đến tab ‘thiết kế bảng’ và chọn tùy chọn ‘chuyển đổi sang phạm vi’.
Phạm vi tràn không xác định
Nếu Excel không thể thiết lập kích thước của mảng bị tràn, nó sẽ gây ra lỗi #SPILL!. Đôi khi, công thức cho phép một mảng động thay đổi kích thước giữa mỗi lần tính toán. Nếu kích thước của mảng động liên tục thay đổi trong quá trình tính toán trôi qua và không cân bằng, điều đó sẽ gây ra lỗi #SPILL!.
Loại lỗi này thường được kích hoạt khi sử dụng các hàm như hàm RAND, RANDARRAY, RANDBETWEEN, OFFSET và INDIRECT.
Ví dụ: khi chúng tôi sử dụng công thức dưới đây trong ô B3, chúng tôi nhận được lỗi #SPILL!
=SEQUENCE(RANDBETWEEN(1, 500))Trong ví dụ này, hàm RANDBETWEEN trả về một số nguyên ngẫu nhiên giữa các số từ 1 đến 500 và đầu ra của nó liên tục thay đổi. Và hàm SEQUENCE không biết có bao nhiêu giá trị để tạo ra trong một mảng tràn. Do đó, lỗi #SPILL.
Bạn cũng có thể xác nhận nguyên nhân của lỗi bằng cách nhấp vào dấu hiệu cảnh báo.
Giải pháp
Để sửa lỗi cho công thức này, lựa chọn duy nhất của bạn là sử dụng một công thức khác cho phép tính của bạn.
Phạm vi tràn quá lớn
Đôi khi, bạn có thể thực thi một công thức xuất ra một phạm vi tràn quá lớn đối với trang tính để xử lý và nó có thể mở rộng ra ngoài các cạnh của trang tính. Khi điều đó xảy ra, bạn có thể nhận được lỗi #SPILL!. Để sữa lỗi này, bạn có thể thử tham chiếu đến một phạm vi cụ thể hoặc một ô thay vì toàn bộ cột hoặc sử dụng ký tự ‘@’ để bật giao điểm ngầm định.
Trong ví dụ bên dưới, chúng tôi đang cố gắng tính toán 20% số doanh thu trong cột A và trả về kết quả trong cột B, nhưng thay vào đó, chúng tôi gặp lỗi #SPILL! .
Công thức trong B3 tính toán 20% giá trị trong A3, sau đó 20% giá trị trong A4,… Nó tạo ra hơn một triệu kết quả (1.048.576) và đổ tất cả chúng vào cột B bắt đầu từ ô B3, nhưng nó sẽ đến cuối trang tính. Không có đủ dung lượng để hiển thị tất cả các kết quả đầu ra, do đó, chúng tôi gặp lỗi #SPILL.
Như bạn có thể thấy nguyên nhân của lỗi này là ‘Phạm vi tràn quá lớn’.
Các giải pháp:
Để giải quyết vấn đề này, hãy thử thay đổi toàn bộ cột bằng một phạm vi có liên quan hoặc một tham chiếu ô đơn hoặc thêm toán tử ‘@’ để thực hiện giao nhau ngầm.
Cách khắc phục 1 : Bạn có thể thử giới thiệu phạm vi thay vì toàn bộ cột. Ở đây, chúng tôi thay đổi toàn bộ phạm vi A: A bằng A3: A11 trong công thức và công thức sẽ tự động điền kết quả vào phạm vi.
Khắc phục 2: Thay thế toàn bộ cột chỉ bằng tham chiếu ô trên cùng một hàng (A3), sau đó sao chép công thức xuống phạm vi bằng cách sử dụng chốt điền.
Khắc phục 3: Bạn cũng có thể thử thêm toán tử ‘@’ trước tham chiếu để thực hiện giao nhau ngầm. Điều này sẽ chỉ hiển thị đầu ra trong ô công thức.
Sau đó, sao chép công thức từ ô B3 sang phần còn lại của phạm vi.
Lưu ý: Khi bạn đang chỉnh sửa công thức bị tràn, bạn chỉ có thể chỉnh sửa ô đầu tiên trong vùng hoặc phạm vi tràn. Bạn có thể thấy công thức trong các ô khác của phạm vi tràn, nhưng chúng sẽ chuyển sang màu xám và không thể cập nhật được.
Hết bộ nhớ
Nếu bạn thực thi một công thức mảng bị tràn khiến Excel hết bộ nhớ, điều đó có thể gây ra lỗi #SPILL!. Trong những trường hợp đó, hãy thử tham chiếu đến một mảng hoặc dải ô nhỏ hơn.
Không được công nhận / dự phòng
Bạn cũng có thể gặp lỗi #SPILL! ngay cả khi Excel không nhận ra hoặc không thể đối chiếu nguyên nhân gây ra lỗi. Trong những trường hợp như vậy, hãy kiểm tra lại công thức của bạn và đảm bảo rằng tất cả các tham số của các hàm đều chính xác.
