#SPILL! nghĩa là một công thức trả về nhiều giá trị (một danh sách hoặc một bảng) nhưng Excel không có chỗ để ghi chúng: ít nhất một ô trong vùng mà kết quả cần không trống. =UNIQUE(A2:A6) bên dưới cần ba ô, từ C2 đến C4, và C4 đang chứa một chữ x. Xóa C4 và danh sách sẽ xuất hiện.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Unique list | |
| 2 | Apple | #SPILL! | |
| 3 | Pear | ||
| 4 | Apple | x | |
| 5 | Plum | ||
| 6 | Pear |
#SPILL! Kết quả cần nhiều ô hơn số ô trống. Xóa các ô đang cản.Bấm vào C2: ghi chú bên dưới lưới cho biết vấn đề. Sau đó bấm vào C4 và nhấn Delete. Ba sản phẩm trả tràn vào C2:C4, và một đường viền đánh dấu vùng trả tràn. Gõ gì đó vào C3, lỗi sẽ quay lại. Excel cũng hoạt động như vậy: các hàm trả về mảng, như FILTER, UNIQUE, SORT, SEQUENCE và TEXTSPLIT, chỉ chạy khi toàn bộ vùng trả tràn của chúng còn trống. Chúng cần Excel 2021 trở lên (TEXTSPLIT: Microsoft 365 hoặc Excel 2024); các phiên bản cũ hơn hiện #NAME? cho các hàm này, nên ở đó không bao giờ có #SPILL! (xem UNIQUE để biết về chính hàm này).
Cách sửa lỗi #SPILL!
- Bấm vào ô có
#SPILL!. Trong Excel, một đường viền nét đứt hiện vùng mà kết quả muốn lấp đầy. - Bấm biểu tượng cảnh báo cạnh ô đó và chọn Select Obstructing Cells. Excel chọn mọi ô đang cản đường.
- Nhấn Delete, hoặc chuyển các ô đó đi chỗ khác (cắt rồi dán).
Nếu các ô cản đường chứa dữ liệu bạn cần, hãy chuyển công thức đi: đặt nó vào một cột hoặc hàng mà mọi ô bên dưới và bên cạnh đều trống.
#SPILL! khi các ô trông có vẻ trống
Trường hợp khó hiểu nhất: vùng trả tràn trông trống trơn, nhưng Excel vẫn báo #SPILL!. Một ô chứa một dấu cách, hoặc một công thức trả về chuỗi rỗng "", không phải ô trống, và nó chặn vùng trả tràn giống như một giá trị.
| A | B | C | |
|---|---|---|---|
| 1 | Numbers | Numbers | |
| 2 | #SPILL! | #SPILL! | |
| 3 | |||
| 4 |
#SPILL! Kết quả cần nhiều ô hơn số ô trống. Xóa các ô đang cản.A2 cần A2:A4, và A4 chứa một dấu cách. C2 cần C2:C3, và C3 chứa ="". Bấm vào A4 hoặc C3 để xem bên trong có gì, xóa nó đi, và các số sẽ trả tràn. Trong Excel, Select Obstructing Cells tìm được những ô này ngay cả khi không thấy gì bên trong. Chữ trắng trên nền trắng cũng bị ẩn theo cách tương tự.
#SPILL! khi công thức này trả tràn vào công thức khác
Hai công thức trả tràn có thể chặn lẫn nhau, hoặc một công thức ai đó gõ ở bên dưới có thể nằm trong vùng trả tràn của công thức phía trên.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Dept | IT | Sales | |
| 2 | Ana | IT | #SPILL! | Ben | |
| 3 | Ben | Sales | Dee | ||
| 4 | Cy | IT | 5 | ||
| 5 | Dee | Sales | |||
| 6 | Eve | IT |
#SPILL! Kết quả cần nhiều ô hơn số ô trống. Xóa các ô đang cản.Danh sách IT cần ba ô, D2:D4, và D4 đang chứa một công thức COUNTA. Danh sách Sales ở E2 có đủ hai ô cần thiết nên vẫn chạy. Chuyển công thức COUNTA xuống D6 (hoặc ô bất kỳ bên dưới danh sách) và tên các nhân viên IT sẽ trả tràn. Hãy chừa chỗ cho danh sách dài ra: nếu sau này có thêm nhân viên IT thứ tư, kết quả cần thêm một ô.
#SPILL! với VLOOKUP và cả cột
Một nguyên nhân phổ biến trong các công thức viết cho Excel cũ là giá trị tra cứu là cả một cột:
=VLOOKUP(A:A,Prices!A:B,2,FALSE) #SPILL! (one result for every row of the sheet)
=VLOOKUP(A2,Prices!A:B,2,FALSE) one result, fill it down
=VLOOKUP(A2:A100,Prices!A:B,2,FALSE) 100 results that spill
=VLOOKUP(@A:A,Prices!A:B,2,FALSE) one result, the value on the formula's own row
A:A có 1.048.576 ô, nên công thức đầu tiên yêu cầu 1.048.576 kết quả, và từ hàng 2 trở xuống không còn đủ hàng: menu cảnh báo cho biết vùng trả tràn vượt quá cạnh của trang tính. Cách viết này đến từ Excel cũ, vốn âm thầm chỉ dùng giá trị ở cùng hàng với công thức. Excel 365 giữ hành vi đó trong các workbook cũ bằng cách hiện công thức là =VLOOKUP(@A:A,...), nhưng cùng công thức đó gõ mới sẽ trả tràn cả cột. Điều tương tự xảy ra với =A:A*2 và mọi công thức tính toán trên cả cột. Hãy dùng một ô rồi kéo xuống, một vùng có kích thước thật, hoặc @. Xem thêm về phép tra cứu ở trang VLOOKUP.
Mỗi hàng một kết quả thay vì trả tràn
Công thức tính toán trên một vùng cũng trả tràn. Thường thì đó là điều bạn muốn, nhưng đôi khi bạn lại cần mỗi hàng một công thức.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Spilled +10% | Filled +10% |
| 2 | Jan | 100 | 110 | 110 |
| 3 | Feb | 120 | 132 | 132 |
| 4 | Mar | 90 | 99 | 99 |
| 5 | Apr | 140 | 154 | 154 |
Cả hai cột đều hiện 110, 132, 99 và 154. C2 chứa một công thức, còn C3:C5 là vùng trả tràn của nó: bấm vào C3, ghi chú dưới lưới cho biết ô này được trả tràn từ C2. Gõ vào C4 và C2 thành #SPILL!. D2:D5 là bốn công thức riêng biệt, nên từng ô có thể sửa độc lập và không gì chặn được chúng. Dùng cách thứ hai khi người khác sẽ gõ đè lên từng kết quả.
#SPILL! trong bảng, với ô đã gộp, hoặc khi không biết kích thước
Những nguyên nhân này phụ thuộc vào workbook, không phải vào công thức:
- Bên trong một bảng Excel (Insert > Table): bảng không hỗ trợ kết quả trả tràn, nên FILTER hoặc UNIQUE trong một cột của bảng hiện
#SPILL!. Hãy đặt công thức bên ngoài bảng, hoặc chọn bảng rồi chọn Table Design > Convert to Range. - Ô đã gộp trong vùng trả tràn: chọn chúng rồi chọn Home > Merge & Center > Unmerge Cells, hoặc chuyển công thức đi.
- Spill range is unknown: kích thước kết quả thay đổi mỗi lần tính lại, như
=SEQUENCE(RANDBETWEEN(1,10)). Excel không trả tràn một kết quả có kích thước biến động. Hãy cho nó một kích thước cố định. - Spill range is too big hoặc extends beyond the worksheet's edge: kết quả sẽ vượt quá hàng hoặc cột cuối cùng. Out of memory: mảng quá lớn để tính. Với cả ba trường hợp, hãy thu nhỏ các vùng, thường là đổi từ cả cột sang đúng phần dữ liệu thật.
Trả về một giá trị để không gì chặn được
Khi chỉ cần một con số từ một danh sách, như có bao nhiêu sản phẩm khác nhau, hãy bọc hàm trả tràn trong một hàm trả về một giá trị duy nhất. Một giá trị duy nhất không bao giờ trả tràn, nên không ô nào cản đường được.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Different products | |||
| 2 | Apple | ||||
| 3 | Pear | ||||
| 4 | Apple | x | |||
| 5 | Plum | ||||
| 6 | Pear | ||||
| 7 | Apple |
Đến lượt bạn: E2 cần cho biết có bao nhiêu sản phẩm khác nhau trong A2:A7. Một công thức =UNIQUE(A2:A7) đơn thuần sẽ trả tràn vào chữ x ở E4. Hãy viết một công thức ở E2 trả về số lượng đó.
COUNTA đếm các giá trị mà UNIQUE trả về và cho lại một con số. Cách làm này cũng dùng được với =INDEX(SORT(A2:A7),1) để lấy giá trị đầu tiên của danh sách đã sắp xếp, =INDEX(FILTER(...),1) để lấy kết quả khớp đầu tiên, hoặc =SUM(FILTER(...)) để tính tổng.
Câu hỏi thường gặp
#SPILL! trong Excel nghĩa là gì?
Một công thức trả về nhiều hơn một giá trị (mảng động) nhưng Excel không ghi được chúng vào các ô bên dưới hoặc bên cạnh, vì ít nhất một ô trong đó không trống, đã bị gộp, hoặc nằm trong một bảng. Xóa hoặc di chuyển thứ đang cản đường và kết quả sẽ xuất hiện.
Vì sao vẫn bị #SPILL! dù các ô trông có vẻ trống?
Một ô trông trống vẫn có thể chứa một dấu cách, một công thức trả về "", hoặc văn bản tô màu trắng. Bất kỳ thứ nào trong số đó cũng chặn vùng trả tràn. Bấm biểu tượng cảnh báo cạnh lỗi và chọn Select Obstructing Cells, rồi nhấn Delete.
Làm sao để sửa #SPILL! với VLOOKUP?
Giá trị tra cứu là cả một cột hoặc một vùng, như =VLOOKUP(A:A,D:E,2,FALSE), nên Excel cố trả về một kết quả cho mỗi hàng của trang tính. Hãy dùng một ô rồi kéo xuống, =VLOOKUP(A2,D:E,2,FALSE), hoặc một vùng có kích thước thật, =VLOOKUP(A2:A100,D:E,2,FALSE).
Làm sao để công thức không trả tràn trong Excel?
Hãy để nó trả về một giá trị. Đặt @ trước một vùng để chỉ lấy giá trị ở cùng hàng với công thức (=@A2:A10*2), hoặc bọc kết quả trong một hàm trả về một giá trị, như =COUNTA(UNIQUE(A2:A10)) hoặc =INDEX(SORT(A2:A10),1).
Công thức trả tràn có đặt được trong bảng Excel không?
Không. Công thức trả tràn sẽ hiện #SPILL! khi nằm trong bảng. Hãy đặt nó vào một ô bên ngoài bảng, hoặc chuyển bảng thành vùng thường bằng Table Design > Convert to Range.
