#SPILL có nghĩa là gì trong Excel? Đó là một lỗi xảy ra khi một công thức không thể điền nhiều ô với kết quả được tính toán. Để tìm hiểu điều gì có thể gây ra lỗi này và cách bạn có thể giải quyết nó, vui lòng tiếp tục đọc.
Hãy tưởng tượng điều này: bạn đã nâng cấp lên Microsoft Office 365 với các bản cập nhật Excel mới nhất và vì lý do nào đó mà các công thức đã thử và đã kiểm tra mà bạn đã sử dụng trong nhiều năm đột nhiên ngừng hoạt động. Dù bạn làm gì, hiện bạn đang gặp lỗi #SPILL. Có ai biết nó có nghĩa là gì không? Tất nhiên, chúng tôi làm và bạn sẽ nhận được giải pháp của mình trong giây lát 
#SPILL có nghĩa là gì trong Excel?
Trước khi đi sâu vào các trường hợp sử dụng cụ thể, chúng ta hãy tìm hiểu chung về “tràn” trong Excel.
Với sự ra mắt của mảng động trong Excel 365, bất kỳ công thức nào tạo ra nhiều kết quả tính toán sẽ tự động “tràn” các kết quả đó vào các ô lân cận. Xin lưu ý, bất kỳ công thức nào, ngay cả những công thức không được thiết kế ban đầu để xử lý và xuất mảng. Một dải ô chứa kết quả được gọi là phạm vi tràn. Và nếu có thứ gì đó trên trang tính ngăn việc điền vào phạm vi đó, thì lỗi #SPILL sẽ xảy ra.
Phần lớn, hành vi này là dễ hiểu và có thể đoán trước được. Ví dụ: nếu công thức của bạn dự kiến trả về nhiều hơn một giá trị, nhưng có một số dữ liệu khác trong các ô gần đó, chỉ cần xóa dữ liệu đó và lỗi sẽ biến mất.
Nhưng đôi khi lý do không quá rõ ràng và do đó khó hiểu. Có lẽ, một hoặc nhiều ô trong vùng tràn chứa một khoảng trắng hoặc một ký tự không in mà mắt người không nhìn thấy được. Hoặc cùng một công thức được sao chép trên toàn bộ cột sẽ chặn một phạm vi tràn. Hoặc, bạn có thể gặp phải một trong số rất ít tính năng không hỗ trợ mảng động. Để khắc phục lỗi, bạn sẽ phải điều tra từng trường hợp riêng lẻ và xác định gốc rễ của vấn đề.
Cách sửa lỗi #SPILL! lỗi trong Excel
Như đã đề cập ở trên, #SPILL! lỗi có thể do nhiều lý do khác nhau. Để biết chính xác gốc rễ của vấn đề, hãy nhấp vào biểu tượng cảnh báo (hình thoi màu vàng với dấu chấm than) và đọc thông báo ở dòng đầu tiên được đánh dấu bằng màu xám:
Sau khi bạn xác định được nguyên nhân, vui lòng tìm ví dụ tương ứng bên dưới với hướng dẫn chi tiết về cách giải quyết trường hợp cụ thể đó.
Phạm vi tràn không trống
Lý do: Khu vực được điền kết quả công thức chứa các ô không trống.
Giải pháp: Xóa phạm vi tràn dự kiến.
Trong trường hợp đơn giản nhất, chỉ cần nhấp vào ô công thức và bạn sẽ thấy một đường viền đứt nét cho biết ranh giới phạm vi tràn – bất kỳ dữ liệu nào bên trong nó đều là một trở ngại. Vì vậy, hãy xóa dữ liệu hiện có khỏi khu vực tràn hoặc di chuyển công thức đến một vị trí khác nơi không bị tắc nghẽn.
Tuy nhiên, trong một số tình huống, phạm vi tràn có thể nhìn trống rỗng, nhưng thực tế không phải vậy. Sự cố có thể nằm ở các ký tự ẩn, chẳng hạn như khoảng trắng ẩn trong một số ô hoặc các công thức trả về một chuỗi trống.
Để phát hiện các ô như vậy, hãy nhấp vào dấu hiệu cảnh báo và bạn sẽ thấy giải thích này – Phạm vi tràn không trống. Bên dưới nó, có một số tùy chọn. Nhấp chuột Chọn các ô cản trởvà Excel sẽ hiển thị cho bạn những ô nào ngăn công thức bị tràn.
Trong ảnh chụp màn hình bên dưới, ô cản trở là A6, chứa một chuỗi trống (“”) được trả về bởi công thức.
Để xóa các ô chặn, hãy chuyển đến Trang Chủ tab> Chỉnh sửa nhóm và nhấp vào Thông thoáng > Làm sạch tất cả.
Phạm vi tràn chứa các ô đã hợp nhất
Lý do: Tràn không hoạt động với các ô đã hợp nhất.
Giải pháp: Hủy hợp nhất các ô trong vùng bị tràn hoặc di chuyển công thức đến một vị trí khác không có ô đã hợp nhất.
Trong trường hợp có một hoặc nhiều ô đã hợp nhất trong một mảng tràn dự kiến, thông báo lỗi sau sẽ được hiển thị: Phạm vi tràn đã hợp nhất ô.
Nếu bạn gặp khó khăn trong việc phát hiện các ô đã hợp nhất một cách trực quan, hãy sử dụng Chọn các ô cản trở tùy chọn để chuyển đến các ô có vấn đề:
Phạm vi tràn trong bảng
Lý do: Mảng động không được hỗ trợ trong bảng Excel.
Giải pháp: 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 để cho phép nó tràn ra. Không rõ tại sao các công thức mảng động không hoạt động từ bên trong Bảng Excel (có thể do cú pháp cụ thể của tài liệu tham khảo có cấu trúc), nhưng dù sao thì hai thứ rất hữu ích này cũng không ăn nhập với nhau 
Để xác nhận nguyên nhân gốc rễ của vấn đề, hãy nhấp vào biểu tượng có dấu chấm than và bạn sẽ thấy văn bản này ở dòng đầu tiên – Phạm vi tràn trong bảng.
Trong trường hợp này, điều tốt nhất bạn có thể làm là chuyển đổi bảng thành phạm vi. Đối với điều này, bấm chuột phải vào bất kỳ đâu trong bảng, sau đó bấm Bàn > Chuyển đổi sang Phạm vi. Ngoài ra, bạn có thể di chuyển công thức của mình ra ngoài ranh giới bảng.
Phạm vi tràn không xác định
Lý do: Excel không thể thiết lập kích thước của mảng tràn.
Giải pháp: Cố gắng tìm ra một công thức khác cho nhiệm vụ của bạn.
Khi sử dụng các chức năng dễ bay hơi như RANDARRAY, RAND hoặc là RANDBETWEEN kết hợp với các hàm mảng động, lỗi #SPILL có thể xảy ra do mảng được trả về bởi một hàm dễ bay hơi thay đổi giữa các tính toán của bảng tính và hàm “trình bao bọc” không thể xác định kích thước của nó.
Trong tình huống như vậy, một thông báo lỗi cho biết – Phạm vi tràn không xác định.
Ví dụ: công thức sau ném lỗi #SPILL! lỗi vì đầu ra RANDBETWEEN liên tục thay đổi và SỰ NỐI TIẾP không biết có bao nhiêu giá trị để tạo:
=SEQUENCE(RANDBETWEEN(1,100))
Phạm vi tràn quá lớn
Lý do: Excel không thể xuất ra một mảng tràn vì nó vượt ra ngoài các cạnh của bảng tính.
Giải pháp: Thay vì tính toán toàn bộ các cột, hãy tham chiếu một dải ô đã sử dụng, một ô đơn lẻ hoặc thêm toán tử @ để thực hiện giao nhau ngầm.
Đây là trường hợp phức tạp nhất và tùy thuộc vào mục tiêu của bạn và cấu trúc của trang tính, bạn có thể cần điều chỉnh các công thức của mình theo cách khác nhau. Ở đây, chúng tôi sẽ trình bày một cách tiếp cận chung trên một ví dụ rất đơn giản.
Giả sử bạn có một danh sách các số trong cột B và trong cột C, bạn muốn trả lại 10% các số đó.
Trong Excel 2019 truyền thống trở về trước, công thức sau hoạt động mà không gặp trở ngại nào (ở phần bên trái của hình ảnh bên dưới, nó nằm trong các ô từ C2 đến C7):
=B:B*10%
Trong Excel 365 động, cùng một công thức sẽ kích hoạt lỗi #SPILL.
Nguyên nhân của lỗi là Phạm vi tràn quá lớn.
Tại sao công thức bị hỏng trong Excel mới? Bởi vì giao lộ ngầm không còn được thực hiện âm thầm trong nền. Thuật ngữ này nghe có vẻ bí ẩn, nhưng logic đằng sau nó khá đơn giản – giảm nhiều giá trị xuống một giá trị duy nhất.
Trong các phiên bản trước 365, đó là hành vi mặc định của Excel – vì một ô chỉ có thể chứa một giá trị, nên một công thức buộc phải trả về một kết quả duy nhất. Trong ví dụ của chúng tôi, mặc dù chúng tôi cung cấp toàn bộ cột (B: B), Excel chỉ xử lý một giá trị trên cùng một hàng với công thức. Vì vậy, công thức trong C2 tính 10% giá trị trong B2, công thức trong C3 tính 10% giá trị trong B3, v.v.
Trong mảng động Excel, hành vi mặc định là khác – bất kỳ công thức nào có khả năng trả về nhiều kết quả sẽ tự động đưa chúng vào trang tính. Trong trường hợp của chúng tôi, Excel nhân mỗi ô trong cột B với 10%, nhận được hơn một triệu kết quả (chính xác hơn là 1,048,576), cố gắng xuất tất cả chúng trong cột C bắt đầu bằng C2, nhưng đến cuối lưới trang tính – do đó a Lỗi #SPILL. Đoán xem điều gì xảy ra nếu chúng ta đưa công thức vào C1? Đúng, nó sẽ hoạt động vì lần này có đủ ô để lấp đầy – phạm vi tràn sẽ chiếm chính xác 1.048.576 ô!
Giải pháp 1. Tham chiếu đến phạm vi thay vì cột
Đây là một cách tiếp cận rất hợp lý – thay vì tham chiếu toàn bộ các cột, hãy chỉ tham chiếu đến dữ liệu có liên quan. Ngoài việc sửa lỗi #SPILL, nó cũng giúp tiết kiệm tài nguyên Excel và cải thiện hiệu suất.
Đối với tập dữ liệu mẫu của chúng tôi, công thức là:
=B2:B10*10%
Công thức được nhập ngay trong một ô (C2). Kết quả là một mảng động tự động tràn vào nhiều ô:
Ghi chú. Vì mảng động không được hỗ trợ trong bảng Excel, giải pháp này chỉ hoạt động trong phạm vi bình thường.
Giải pháp 2. Tính toán một ô duy nhất và sao chép công thức xuống
Đây là cách tiếp cận truyền thống – tham chiếu một ô trên cùng một hàng và sao chép công thức xuống.
Trong trường hợp của chúng tôi, công thức dưới đây chuyển đến C2, sau đó bạn kéo nó xuống qua C10:
=B2*10%
Không giống như các công thức mảng động, kiểu công thức thông thường này hoạt động trong các phạm vi và bảng tốt như nhau:
Giải pháp 3. Áp dụng giao điểm ngầm định
Cách tiếp cận này phức tạp hơn một chút – sử dụng toán tử giao lộ ngầm (@) đã được giới thiệu trong Excel 365 như một phần của bản cập nhật mảng động.
Trong công thức của bạn, hãy chèn ký tự @ vào nơi bạn muốn giảm một mảng hoặc một dải ô xuống một giá trị duy nhất, ví dụ: trước một tham chiếu dải ô / cột / hàng, hàm hoặc biểu thức. Nhập công thức vào ô trên cùng, sau đó kéo công thức xuống qua nhiều ô nếu cần.
Đối với tập dữ liệu mẫu của chúng tôi, công thức có dạng sau:
=@B:B*10%
Vì kết quả là một giá trị duy nhất, không phải là một mảng động, công thức này có thể được sử dụng cả trong phạm vi và bảng. Tuy nhiên, trong trường hợp thứ hai, một tham chiếu có cấu trúc sẽ là một giải pháp thanh lịch và hiệu quả hơn:
=[@Sales]*10%
Ở một mức độ nào đó, điều này giải thích tại sao ký tự “@” thỉnh thoảng xuất hiện trong các công thức được tạo trong các phiên bản cũ hơn – để duy trì tính tương thích. Bởi vì trong Excel trước động, công thức không thể tràn vào nhiều ô, toán tử giao nhau ngầm đảm bảo hành vi tương tự khi công thức được nhập trong mảng động Excel.
Lời khuyên cá nhân của tôi là sử dụng cách tiếp cận này một cách cẩn thận vì nó vô hiệu hóa một tính năng mới tuyệt vời của Excel rất hiệu quả và hữu ích trong nhiều trường hợp.
Đó là cách khắc phục sự cố và sửa lỗi #SPILL trong Excel. Trong bài viết sắp tới, chúng tôi sẽ điều tra và giải quyết lỗi này trong một số công thức phổ biến như VLOOKUP, INDEX MATCH, SUMIF, v.v. Cảm ơn bạn đã đọc và hãy theo dõi ketoanmvb hàng ngày nhé !
The post #SPILL! lỗi trong Excel – nó có nghĩa là gì và cách khắc phục appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | Kế Toán MVB.
source https://ketoanmvb.com/spill-loi-trong-excel-no-co-nghia-la-gi-va-cach-khac-phuc.html
Không có nhận xét nào:
Đăng nhận xét