Đánh giá lequocthai.com:
Sau khi Microsoft giới thiệu mảng động, các công thức có thể trả về nhiều giá trị. Trong những trường hợp này, kết quả sẽ “tràn” (spill) ra trên bảng tính, và vùng ô chứa các giá trị trả về được gọi là “spill range” (vùng tràn).
Tình huống phổ biến nhất gây ra lỗi #SPILL! là có vật cản trong spill range hoặc tham chiếu được dùng cho công thức vượt quá giới hạn của bảng tính.
Trong hướng dẫn này, chúng tôi sẽ giải thích #SPILL! error là gì, tại sao nó xảy ra và cách khắc phục.
#SPILL! Error là gì trong Excel?
Lỗi #SPILL! thường xuất hiện khi một công thức tạo ra nhiều kết quả nhưng không thể hiển thị chúng trên bảng tính. Để hiểu rõ lỗi này, trước tiên bạn cần nắm các khái niệm “spilling” và “spill range”. Ngoài việc có ô bị chặn, còn có một số tình huống khác khiến bạn gặp lỗi #SPILL!.
Thông thường, chúng ta nhập công thức và nhận kết quả trong cùng một ô, sau đó kéo công thức sang các ô khác. Nhưng nếu một công thức duy nhất phải trả về hơn một kết quả thì sao?
Thuật ngữ “spill” mô tả hành vi các công thức trả về nhiều kết quả sẽ tự động “tràn” chúng vào các ô liền kề. Kết quả sẽ chiếm số ô cần thiết để hiển thị; ví dụ, nếu một công thức cần 5 ô, nó sẽ lấp đầy 5 ô, bao gồm ô chứa công thức.
Vùng giá trị mà công thức mảng trả về và tràn vào bảng tính được gọi là “spill range”.
Thông thường, spill range được đánh dấu bằng viền xanh đứt đoạn. Khi công thức gây lỗi, ô sẽ hiển thị biểu tượng lỗi; nhấp vào biểu tượng này để nhận trợ giúp và các tùy chọn xử lý.
Nguyên nhân gây #SPILL! Error trong Excel
Dưới đây là một số nguyên nhân phổ biến và cách khắc phục tương ứng.
1. Spill Range bị chặn
Đây là trường hợp cơ bản và phổ biến nhất: không có đủ không gian trên bảng tính để hiển thị các giá trị trả về. Ví dụ, nếu công thức dự kiến trả về hai giá trị nhưng chỉ có một ô trống bên cạnh, lỗi #SPILL! sẽ xuất hiện.
Lỗi sẽ biến mất sau khi loại bỏ vật cản và vùng tràn sẽ được lấp đầy bởi kết quả dự kiến.
Ví dụ 1 – Spill Range bị chặn
Trong ví dụ này, chúng ta có danh sách họ và tên trong cột A và B. Để chuyển dữ liệu từ dạng dọc sang dạng ngang, có thể dùng hàm TRANSPOSE. Công thức sau sẽ chuyển danh sách trong cột A sang hàng 1:
=TRANSPOSE(A2:A7)
Công thức được nhập ở ô D1. Với 6 tên trong cột A, công thức cần 6 ô để hiển thị kết quả, tức từ D1 tới I1. Tuy nhiên, ô I1 đã có dữ liệu, khiến spill range bị chặn và gây lỗi #SPILL!.
Giải pháp 1: Xóa hoặc di chuyển dữ liệu khỏi ô I1. Khi ô I1 trống, kết quả sẽ tràn tới I1 và lỗi sẽ biến mất.
Giải pháp 2: Nếu không muốn xóa dữ liệu, bạn có thể di chuyển ô chứa công thức. Vì spill range phía trước D1 có ô không trống, hãy chuyển công thức xuống một vài ô, ví dụ đổi vị trí từ D1 thành D5; kết quả sẽ tràn tự do tới I5.
Có nhiều trường hợp khác gây spill range bị chặn, chẳng hạn giá trị, ký tự đặc biệt, ký tự ẩn hoặc công thức trả về ô trống.
Ví dụ 2 – Vật cản ẩn
Khác với ô chặn hiện có, lỗi cũng có thể do ô bị chặn nhưng không hiển thị. Nguyên nhân có thể là màu chữ trắng, hoặc định dạng tùy chỉnh `;;;` khiến giá trị biến mất.
Trong ví dụ dưới, chúng ta có độ tuổi của một nhóm người và dùng công thức FILTER để liệt kê những người từ 25 tuổi trở lên:
=FILTER(A2:B11,B2:B11>=25)
Công thức đưa ra lỗi #SPILL! nhưng không có vật cản hiển thị.
Giải pháp: Nhấp vào biểu tượng lỗi và chọn “Select Obstructing Cells” (Chọn ô gây chặn). Excel sẽ đánh dấu các ô có dữ liệu. Ô D7 chứa chữ “Block” nhưng màu chữ là trắng; xóa dữ liệu sẽ giải quyết lỗi.
Tương tự, ô D9 chứa chữ “Block 2” màu đen nhưng được định dạng để ẩn. Kiểm tra bằng cách chuột phải → Format Cells (Định dạng Ô) → tab Number (Số) → Custom (Tự chọn) → xem `;;;` trong ô Type. Đó là ô được định dạng để giá trị không hiển thị.
Giải pháp đơn giản: xóa giá trị hoặc di chuyển công thức tới vị trí có đủ không gian.
2. Ô đã hợp nhất trong Spill Range
Lỗi #SPILL! cũng xuất hiện khi spill range chứa ô đã hợp nhất. Nhấp vào biểu tượng cảnh báo và kiểm tra “Spill range has a merged cell” (Vùng tràn có ô hợp nhất) để xác nhận.
Ví dụ – Spill Range chứa ô hợp nhất
Trong ví dụ này, chúng ta có danh sách sản phẩm và danh mục tương ứng. Để lấy danh sách các danh mục duy nhất, dùng hàm UNIQUE:
=UNIQUE(B2:B10)
Do ô D4 được hợp nhất với D5 và D6, một phần của spill range bị chặn, gây lỗi #SPILL!.
Giải pháp: Bỏ hợp nhất các ô trong vùng tràn bằng cách chọn ô hợp nhất → tab Home (Trang chủ) → nhóm Alignment (Căn chỉnh) → nút Merge & Center (Hợp nhất & Căn giữa) (bấm lại để bỏ hợp nhất). Nếu dữ liệu lớn, nhấp vào biểu tượng lỗi → Obstructing Cells (Ô gây chặn) để tự động tìm ô gây vấn đề, sau đó bỏ hợp nhất.
Khi không còn ô hợp nhất, lỗi sẽ biến mất.
3. Spill Range không xác định được
Khi công thức mảng động kết hợp với các hàm biến động (volatile) như RAND, RANDARRAY, RANDBETWEEN, … sẽ gây lỗi #SPILL! vì Excel không thể xác định trước kích thước của spill range.
Ví dụ – Dùng SEQUENCE & RANDBETWEEN
Công thức sau sẽ gây lỗi:
=SEQUENCE(RANDBETWEEN(1,300))
Vì giá trị trả về của RANDBETWEEN thay đổi mỗi lần tính lại, hàm SEQUENCE không biết cần trả về bao nhiêu số, dẫn đến spill range không xác định và lỗi #SPILL!.
Giải pháp: Thay đổi công thức sang dạng có độ dài cố định, hoặc tránh dùng hàm volatile trong mảng động.
4. Spill Range vượt quá giới hạn của bảng tính
Khi công thức trả về quá nhiều giá trị, spill range có thể vượt ra ngoài ranh giới của worksheet, gây lỗi #SPILL!.
Ví dụ 1 – Spill range quá lớn
Giả sử muốn tăng lương của mọi nhân viên 10%:
=B:B*10%+B:B
Công thức này được nhập ở ô D2, nhưng để trả về toàn bộ cột B (1.048.576 hàng) Excel cần một hàng nữa (hàng 1.048.577) để hiển thị kết quả – điều không tồn tại, vì vậy xuất hiện #SPILL!.
Giải pháp 1: Đặt công thức ở ô E1; kết quả sẽ tràn được (tuy nhiên sẽ gặp #VALUE! vì B1 không phải số).
Giải pháp 2: Thay vì tham chiếu toàn cột B:B, hãy giới hạn phạm vi thực tế, ví dụ B2:B12:
=B2:B12*10%+B2:B12
Kết quả sẽ tràn từ D2 tới D12.
Giải pháp 3: Chỉ tham chiếu ô đầu tiên của dữ liệu (B2), nhập công thức ở D2, sau đó kéo công thức xuống tới D12.
5. Implicit intersection (@)
Toán tử “@” (implicit intersection) chuyển một mảng hoặc phạm vi thành một giá trị duy nhất. Khi một hàm làm tràn phạm vi tới rìa worksheet, bạn có thể dùng “@” để giảm về một giá trị duy nhất, rồi sao chép công thức.
Ví dụ – Implicit intersection (@)
Công thức VLOOKUP sau trả về toàn cột A, khiến spill range vượt quá giới hạn:
=VLOOKUP(A:A,A:B,1,FALSE)
Excel gợi ý dùng “@”.
Sau khi chấp nhận đề xuất, Excel sẽ thêm “@” và trả về giá trị của ô A2 (ô chứa công thức). Bạn có thể kéo công thức để điền các ô còn lại.
6. Kết quả tràn vượt quá giới hạn cột
Excel có 1.048.576 hàng và 16.384 cột. Công thức sau sẽ tràn vượt qua giới hạn cột và gây lỗi:
=SEQUENCE(1,16385)
Giải pháp: Giảm số cột xuống 16.384 hoặc ít hơn, ví dụ:
=SEQUENCE(1,16384)Đảm bảo nhập công thức tại cột đầu tiên để tràn tới cột cuối cùng mà không cần thêm cột.
7. Bảng (Table) trong Excel với mảng động
Mảng động không hoạt động tốt trong Excel Tables. Khi dùng công thức mảng trong bảng, sẽ xuất hiện lỗi #SPILL! và biểu tượng lỗi sẽ ghi “Spill range in table”.
Ví dụ – Công thức mảng trong Table
Giả sử có danh sách tên trong một Table và muốn sắp xếp cột Name theo thứ tự alphabet:
=SORT([[Name ]])Lỗi #SPILL! xuất hiện. Nhấp vào biểu tượng lỗi để thấy lý do “Spill range in table”.
Giải pháp 1: Sử dụng công thức bên ngoài Table, tham chiếu tới Table và cột:
=SORT(Table1[[Name ]])Nếu đặt công thức ở ô F5 (thay vì D5), kết quả sẽ hiển thị mà không lỗi.
Giải pháp 2: Nếu Table gây ra vấn đề, chuyển Table thành một phạm vi thông thường: chuột phải vào bất kỳ ô nào trong Table → Table → Convert to Range (Chuyển đổi thành phạm vi).
Sau khi Table biến thành phạm vi, công thức sẽ không còn lỗi và sắp xếp tên theo thứ tự alphabet:
8. Các trường hợp khác
Ngoài các tình huống trên, bạn cũng có thể gặp #SPILL! nếu Excel hết bộ nhớ. Khi đó, hãy thử dùng phạm vi nhỏ hơn.
Kết luận
Tránh sử dụng các tham chiếu hoặc tính toán có thể tạo spill range vượt quá kích thước bảng tính, hoặc di chuyển ô mục tiêu lên các hàng/cột sớm hơn để có đủ không gian.
Hy vọng sau hướng dẫn này, bạn sẽ có thể khắc phục mọi lỗi #SPILL! trong các tình huống khác nhau. Khi bạn giải quyết các lỗi này, chúng tôi sẽ tiếp tục cung cấp thêm các tutorial để giúp trải nghiệm Excel của bạn luôn mượt mà như bơ.
