Bài viết này sẽ giúp bạn hiểu hết nguyên nhân của #FAR! cũng như các giải pháp khắc phục chúng trong Excel 365.

#TRÀN! 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 phép tính cố gắng hiển thị đầ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ấu ngắt có thể là bất kỳ thứ gì, bao gồm giá trị văn bản, ô được hợp nhất, ký tự khoảng trắng hoặc thậm chí khi không có đủ khoảng trống để trả về kết quả. Giải pháp rất đơn giản, loại bỏ phạm vi dữ liệu rình mò 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 theo 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ả ra nhiều ô hoặc một mảng. Hãy xem xét chi tiết hơn và hiểu nguyên nhân gây ra lỗi này trong Excel và cách giải quyết nó.

1. Điều gì gây ra #SPILL! ?

Kể từ khi giới thiệu phạm vi độ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 ô. Phạm vi động là phạm vi có thể thay đổi kích thước cho phép công thức trả về nhiều kết quả cho một phạm vi ô trong trang tính dựa trên 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ả, kết quả 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à từ đó kết quả chảy ra đượ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ị tài nguyên.

Nếu một công thức đang cố điền vào một dải ô có nhiều kết quả, nhưng bị chặn bởi thứ gì đó trong dải ô đó, lỗi #SPILL! sẽ xảy ra.

Excel hiện có 9 hàm sử dụng hàm mảng động để giải bài toán, bao gồm:

• trình tự
• LỌC
• hoán vị
• ĐIỀU
• VẬY THEO
• RANDARRAY
• ĐỘC NHẤT
• Xlookup
• CHỈ XMATCH

Công thức nhóm động chỉ sẵn dùng trong Excel 365 và hiện không được hỗ trợ bởi bất kỳ phần mềm Excel ngoại tuyến nào (ví dụ: Microsoft Excel 2016, 2019).

Lỗi #FART! không chỉ là chặn dữ liệu, có một số lý do khiến bạn có thể gặp lỗi. Hãy xem xét 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.

2. Mảng tràn không rỗ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 trố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ụ sau, chúng ta đã nhập hàm TRANSPOSE trong ô C2 để chuyển đổi một dải ô theo chiều dọc (B2:B5) thành một dải ô theo chiều ngang (C2:F2). Thay vì chuyển đổi cột thành hàng, Excel sẽ hiển thị lỗi #SPILL! .

Và khi click vào ô công thức, bạn sẽ thấy đường viền màu xanh nét đứt thể hiện vùng hoặc phạm vi tràn (C2:F2) cần thiết để hiển thị kết quả như hình bên dưới. Bạn cũng sẽ nhận thấy một dấu hiệu cảnh báo màu vàng có dấu chấm than trên đó.

Để hiểu lý do xảy ra 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 màu xám. Như bạn có thể thấy, nó nói ‘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ó ký tự văn bản (không trống).

Giải pháp

Giải pháp rất đơn giản, 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 nơi khác không có chướng ngại vật.

Ngay sau khi bạn loại bỏ hoặc di chuyển khóa, Excel sẽ tự động điền vào các ô bằng 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 đổi các cột thành các hàng như dự định.

Ví dụ 2:

Trong ví dụ bên dưới, mặc dù phạm vi tràn xuất hiện trống, nhưng công thức vẫn hiển thị lỗi #SPILL! . Điều này là do phạm vi tràn có một ký tự không gian ẩn trong một trong các ô.

Rấ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 các ô dường như trống rỗng. Để tìm các ô 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 ô vi phạm’ từ menu và nó sẽ đưa bạn đến ô chứa dữ liệu gây nhiễu.

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 bạn loại bỏ dữ liệu đó, bạn sẽ nhận được kết quả chính xác.

Đôi khi, ký tự ẩn có thể được định dạng văn bản có cùng màu phông chữ với màu tô của ô hoặc giá trị ô được định dạng mã với mã ;;;. Khi bạn tùy chỉnh định dạng giá trị ô ;;;, nó sẽ ẩn mọi thứ trong ô đó, bất kể màu phông chữ hay màu ô.

3. Phạm vi tràn chứa các ô được nối

Đôi khi #SPILL! xảy ra khi phạm vi tràn chứa các ô được nối. Công thức mảng động không hoạt động với cá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 liên kế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ó các ô được hợp nhất.

Trong ví dụ sau, mặc dù phạm vi tràn trống (C2:CC8), công thức trả về lỗi tràn. Điều này là do các ô C4 và C5 đã hợp nhất.

Để đảm bảo rằng các ô được hợp nhất là nguyên nhân gây ra lỗi của bạn, 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 đầu, bấm vào nút Hợp nhất và Trung tâm, rồi 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 trang tính lớn của mình, hãy nhấp vào tùy chọn ‘giải quyết các ô gây cản trở’ từ menu dấu hiệu cảnh báo để chuyển sang các ô đã hợp nhất.

4. Phạm vi tràn trong bảng

Công thức nhóm tràn không được hỗ trợ trong bảng tính 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 một công thức mảng tràn vào một bảng, hoặc khi công thức mảng bị tràn vào một 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 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 vào bảng tính Excel, chúng tôi nhận được lỗi #SPILL! trong mọi ô của bảng, không chỉ ô công thức. Điều này là do Excel tự động sao chép bất kỳ công thức nào được nhập vào bảng vào từng ô của cột trong bảng.

Ngoài ra, bạn sẽ nhận được #FART! khi một công thức cố gắng truyền nó sẽ dẫn đến một bảng. Trong ảnh chụp màn hình sau, vùng tràn nằm trong bảng hiện có, vì vậy chúng tôi nhận được lỗi #SPILL!

Để xác nhận nguyên nhân của 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 trả lại bảng Excel về phạm vi. Để thực hiện việc này, nhấp chuột phải vào bất kỳ đâu trên bảng, nhấp vào ‘bảng’ và sau đó chọn tùy chọn ‘chuyển đổi thành chuỗi’. Ngoài ra, bạn có thể nhấp chuột trái vào bất kỳ đâu trên bảng, sau đó chuyển đến tab ‘thiết kế bảng’ và chọn tùy chọn ‘chuyển đổi thành chuỗi’.

5. Phạm vi tràn không xác định

Nếu Excel không thể đặt kích thước của mảng tràn, nó sẽ báo lỗi #SPILL! Đôi khi, các công thức cho phép một mảng động thay đổi kích thước giữa mỗi phép tính. 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 và không cân bằng, nó sẽ gây ra lỗi #SPILL!.

Loại lỗi này thường xảy ra khi sử dụng các hàm như hàm RAND, RANDARRAY, RANDBETWEEN, OFFSET và INDIRECT.

Ví dụ: khi chúng ta sử dụng công thức sau trong ô B3, chúng ta 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 trong khoảng từ 1 đến 500 và đầu ra của hàm này tiếp tục thay đổi. Và hàm SEQUENCE không biết tạo bao nhiêu giá trị 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 lỗi bằng cách nhấp vào dấu hiệu cảnh báo.

Giải pháp

Để sửa 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 để tính toán.

6. Phạm vi tràn quá lớn

Đôi khi, bạn có thể chạy một công thức cho kết quả tràn quá lớn để xử lý trang tính và có thể vượt ra ngoài các cạnh của trang tính. Khi điều này xảy ra, bạn có thể gặp lỗi #SPILL!. Để khắc phục điều này, bạn có thể thử tham chiếu một phạm vi hoặc ô cụ thể thay vì toàn bộ cột hoặc sử dụng ký tự ‘@’ để bật nhiễu xuyên âm ngầm.

Trong ví dụ bên dưới, chúng tôi đang cố gắng tính 20% doanh số bán hàng trong cột A và trả kết quả về cột B, nhưng thay vào đó, chúng tôi nhận được #SPILL! .

Công thức trong B3 tính toán 20% giá trị trong A3, sau đó là 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ả nên 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à ‘Sphere tràn quá lớn’.

Các giải pháp:

Để giải quyết vấn đề này, hãy thử thay thế toàn bộ cột bằng một phạm vi tương ứng hoặc một tham chiếu ô duy nhất hoặc thêm toán tử ‘@’ để thực hiện giao nhau ngầm định.

Cách khắc phục 1 : Bạn có thể thử nhập phạm vi thay vì nhập toàn bộ cột. Tại đây, chúng ta thay đổi toàn bộ dải A:A thành A3:A11 trong công thức và công thức sẽ tự động điền kết quả vào dải.

Khắc phục 2: Thay thế toàn bộ cột bằng chỉ tham chiếu ô trong cùng một hàng (A3), sau đó sao chép công thức bên dưới phạm vi bằng núm điều khiển đ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 tham chiếu chéo ngầm định. Đ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.

Ghi chú: Khi bạn đang chỉnh sửa công thức 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ác 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.

7. Không có bộ nhớ

Nếu bạn chạy một công thức mảng bị tràn, khiến Excel hết bộ nhớ, điều này có thể gây ra lỗi #SPILL!. Trong những trường hợp đó, hãy thử tham khảo một nhóm hoặc phạm vi nhỏ hơn.

8. Không xác định / quay lui

Bạn cũng có thể nhận được #FART! ngay cả khi Excel không nhận ra hoặc không thể khớp với 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 hàm đều chính xác.