Tóm tắt nhanh (TL;DR)
- Lỗi #SPILL! hiện ra khi một công thức trả về nhiều giá trị nhưng Excel không có đủ chỗ trống để đổ kết quả ra các ô bên cạnh. Ô công thức chỉ hiện đúng chuỗi #SPILL!, không hiện kết quả nào.
- Có sáu nguyên nhân: vùng đổ đã có ô mang nội dung, vùng đổ chạm ô gộp, công thức nằm trong bảng Excel, vùng đổ vượt biên trang tính, công thức đổi kích thước liên tục, và file quá nặng tới mức hết bộ nhớ.
- Cách sửa nhanh nhất cho nguyên nhân phổ biến nhất: bấm vào ô báo lỗi, mở cảnh báo, chọn Select Obstructing Cells để nhảy tới ô đang cản, rồi xoá hoặc chuyển ô đó đi. Công thức tự đổ lại, không phải nhập lại từ đầu.
- Ba nguyên nhân nằm ở cách bố trí bảng hoặc phạm vi tham chiếu: ô gộp, bảng Excel, và tham chiếu cả cột làm vùng đổ chạm biên trang tính. Phải dọn chỗ hoặc thu hẹp vùng tham chiếu mới hết lỗi.
- Phòng lỗi rẻ hơn sửa: chừa vùng trống dưới công thức, bỏ ô gộp ở vùng dữ liệu, và đặt công thức mảng ra ngoài bảng.
Lỗi #SPILL trong Excel là gì?
#SPILL! là lỗi Excel trả về khi một công thức mảng động muốn đổ nhiều giá trị ra các ô lân cận mà vùng đó không trống hoặc không đủ chỗ. Khi gặp lỗi này, ô chứa công thức chỉ hiện đúng chuỗi #SPILL!, còn kết quả thật không hiện ở đâu cả.

Vùng đổ mà công thức muốn chiếm (viền gạch đứt) dừng lại ngay khi gặp một ô đã có nội dung.
Cách Excel báo lỗi rất cụ thể: bấm vào ô #SPILL!, bạn thấy một viền gạch đứt bao quanh vùng mà công thức định chiếm. Viền đó gọi là vùng đổ. Nếu viền trùm lên một ô đã có chữ, có số, có ô gộp, hoặc chạm mép trang tính, Excel dừng lại và báo lỗi thay vì ghi đè dữ liệu của bạn. Đây là hành vi có chủ ý: Excel không xoá dữ liệu để lấy chỗ cho công thức.
Ba khái niệm dưới đây giải thích toàn bộ lỗi này. Hiểu đúng ba khái niệm thì phần sửa chỉ còn là thao tác.
📊 Bảng: Ba khái niệm cần hiểu trước khi sửa #SPILL
| Khái niệm | Nghĩa | Dấu hiệu trên màn hình |
|---|---|---|
| Công thức mảng động | Công thức có thể trả về nhiều giá trị cùng lúc, số dòng và số cột tuỳ dữ liệu đầu vào | Gõ Enter là kết quả tràn ra nhiều ô, không cần kéo công thức |
| Vùng đổ | Tập ô mà công thức định ghi kết quả vào | Viền gạch đứt hiện ra khi bấm vào ô công thức |
| Toán tử # | Cách viết tắt để tham chiếu cả vùng đổ, ví dụ A2# nghĩa là toàn bộ kết quả từ A2 trở xuống | Dùng trong công thức khác mà không cần biết vùng đổ dài bao nhiêu |
Một điểm dễ nhầm: công thức mảng động không phải công thức mảng cũ nhập bằng Ctrl+Shift+Enter. Công thức mảng cũ có kích thước cố định, luôn chiếm đúng số ô đã chọn trước. Công thức mảng động tự quyết định kích thước theo dữ liệu, nên mới sinh ra chuyện đổ hay không đổ được. Excel vẫn nhận công thức mảng cũ để mở file cũ, nhưng không khuyến khích dùng nữa (Nguồn: Microsoft Support, Dynamic array formulas and spilled array behavior).
Vì sao lỗi #SPILL chỉ xuất hiện ở bản Excel có mảng động?
Mảng động được Microsoft công bố ngày 24/09/2018 tại hội nghị Ignite, đi kèm bộ hàm mới như FILTER, SORT, SORTBY, UNIQUE, SEQUENCE và RANDARRAY (Nguồn: Microsoft 365 Blog, Bringing AI to Excel). Trước đó, một công thức chỉ trả về một giá trị tại một ô; muốn ra nhiều giá trị thì phải chọn sẵn cả vùng rồi nhấn Ctrl+Shift+Enter.
Vì vậy, cùng một công thức có thể chạy tốt trên file cũ và báo #SPILL trên file mới. Ví dụ quen thuộc: công thức tra cứu theo cả cột. Trên bản Excel cũ, máy tự hiểu là "lấy giá trị cùng dòng với ô đang đứng". Trên bản có mảng động, máy hiểu là "tra cả cột, trả về toàn bộ kết quả", và kết quả đó không có chỗ đổ.
Khi mở file được viết trên bản có mảng động bằng bản Excel cũ hơn, công thức có thể hiện dấu @ ở đầu hoặc bị hiểu thành công thức mảng cũ. Chiều ngược lại cũng có: file cũ mở bằng bản mới sẽ thấy dấu @ do Excel tự thêm vào để giữ đúng ý định ban đầu (Nguồn: Microsoft Support, Excel functions that return ranges or arrays). Dấu @ này không phải lỗi, nó là toán tử ép công thức trả về một giá trị duy nhất.
Có một giới hạn ít người để ý: mảng động chỉ chạy được giữa hai file khi cả hai file cùng mở. Đóng file nguồn là công thức liên kết trả về #REF! khi tính lại (Nguồn: Microsoft Support, Dynamic array formulas and spilled array behavior). Nếu file của bạn có liên kết chéo kiểu này, hãy kiểm tra trước khi đổ lỗi cho công thức.
Về giới hạn trang tính: một sheet có 1.048.576 dòng và 16.384 cột (Nguồn: Microsoft Support). Con số này quan trọng vì một trong sáu nguyên nhân gây #SPILL là vùng đổ chạm biên trang tính, và nó chỉ xảy ra khi công thức tham chiếu cả cột.
Sáu nguyên nhân gây #SPILL và dấu hiệu nhận biết
Bảng dưới đây gom sáu nguyên nhân theo đúng cách Microsoft phân loại. Cột cuối là việc nên làm đầu tiên, chi tiết từng loại ở các mục sau.
📊 Bảng: Sáu nguyên nhân gây #SPILL và việc nên làm đầu tiên
| Nguyên nhân | Dấu hiệu nhận biết | Việc làm đầu tiên |
|---|---|---|
| Vùng đổ có ô đã mang nội dung | Viền gạch đứt dừng đúng tại một ô có chữ, số hoặc công thức | Bấm cảnh báo, chọn Select Obstructing Cells, xoá ô cản |
| Vùng đổ chạm ô gộp | Viền gạch đứt đi qua một ô rộng do gộp nhiều cột | Bỏ gộp các ô đó, hoặc chuyển công thức sang chỗ khác |
| Công thức nằm trong bảng Excel | Ô công thức nằm trong vùng có kiểu bảng, tên cột ở hàng đầu | Đưa công thức ra ngoài bảng, hoặc chuyển bảng thành vùng thường |
| Vùng đổ vượt biên trang tính | Công thức tham chiếu cả cột, ví dụ A:A, và đặt ở dòng gần cuối | Đổi sang tham chiếu đúng vùng dữ liệu |
| Công thức đổi kích thước liên tục | Công thức có RAND, RANDBETWEEN hoặc RANDARRAY | Thay phần ngẫu nhiên bằng giá trị cố định |
| Hết bộ nhớ | File nặng, nhiều sheet, công thức lớn, máy chậm rõ rệt | Thu nhỏ vùng tham chiếu, lọc dữ liệu trước khi tính |

Sáu nguyên nhân gây #SPILL, mỗi nguyên nhân là một kiểu vật cản khác nhau trên cùng một trang tính.
Có một trường hợp thứ bảy đang ở giai đoạn thử nghiệm: bảng Pivot bị #SPILL khi vùng mở rộng của nó vướng dữ liệu, ô gộp hoặc biên trang tính. Microsoft ghi rõ tính năng này mới dành cho người dùng chương trình Insider (Nguồn: Microsoft Support, How to correct a PivotTable #SPILL! error). Nếu file của bạn chưa bật chương trình đó thì không gặp trường hợp này.
Cách sửa #SPILL khi vùng đổ bị chặn
Đây là nguyên nhân phổ biến nhất, và cũng dễ sửa nhất. Có hai kiểu chặn: chặn nhìn thấy được và chặn không nhìn thấy được.
Kiểu 1: ô cản nhìn thấy được
- Bấm vào ô đang hiện #SPILL!.
- Nhìn viền gạch đứt để biết vùng đổ dự kiến rộng tới đâu.
- Bấm vào biểu tượng cảnh báo nhỏ cạnh ô, chọn Select Obstructing Cells. Excel nhảy thẳng tới ô đang cản.
- Xoá nội dung ô đó, hoặc cắt ô đó chuyển sang chỗ khác.
- Bấm Enter ở ô công thức. Kết quả tự đổ ra, không phải nhập lại.
Nếu không thấy biểu tượng cảnh báo, bạn tự chọn vùng theo viền gạch đứt rồi xoá từng ô một. Cách này chậm hơn nhưng chắc chắn, nhất là khi vùng đổ rộng vài chục dòng.
Kiểu 2: ô cản không nhìn thấy được
Ô trông như trống nhưng thực ra có nội dung. Ba trường hợp hay gặp:
📊 Bảng: Ba trường hợp ô trông trống nhưng vẫn cản vùng đổ
| Trường hợp | Vì sao vẫn tính là có nội dung | Cách kiểm |
|---|---|---|
| Công thức trả về chuỗi rỗng, ví dụ IF sai điều kiện thì trả về hai dấu nháy | Ô chứa công thức, không phải ô trống | Bấm vào ô, xem thanh công thức |
| Một dấu cách hoặc ký tự xuống dòng lọt vào khi copy từ web | Ký tự vẫn là dữ liệu | Dùng hàm LEN để đếm ký tự trong ô |
| Ô được tô màu hoặc có viền nhưng không có giá trị | Trường hợp này không gây lỗi; chỉ nội dung mới cản | Nếu tô màu mà vẫn lỗi thì tìm nguyên nhân khác |
Mẹo kiểm nhanh: đặt một ô trống bên cạnh, gõ công thức đếm ký tự của ô nghi vấn. Ô trả về 0 là trống thật, lớn hơn 0 là có nội dung. Cách này nhanh hơn bấm từng ô khi vùng đổ dài.
Kiểu 3: kéo công thức xuống chính vùng đổ của nó
Đây là lỗi do thao tác, không phải do công thức. Bạn đặt FILTER hoặc UNIQUE ở ô C2, kết quả đổ xuống C2 tới C40. Sau đó bạn kéo góc phải dưới của C2 xuống C41 để "cho chắc". Thao tác đó ghi một công thức mới vào C3, nằm ngay trong vùng đổ, và Excel báo #SPILL!.
Cách sửa: bấm Ctrl+Z để hoàn tác thao tác kéo, rồi để yên. Công thức mảng động không cần kéo xuống. Nó tự dài ra khi dữ liệu nguồn tăng, miễn là phía dưới còn trống.
Cách sửa khi vùng đổ chạm ô gộp
Ô gộp là vật cản cứng. Một ô gộp nhiều cột chiếm chỗ của nhiều ô thật, và mảng động không đổ xuyên qua được (Nguồn: Microsoft Support). Dấu hiệu: viền gạch đứt đi qua một ô rộng bất thường, thường là ô tiêu đề của bảng.
Có hai cách xử lý, chọn theo việc bạn cần:
📊 Bảng: Bỏ ô gộp hay canh giữa qua vùng chọn
| Cách | Làm thế nào | Đánh đổi |
|---|---|---|
| Bỏ gộp ô | Chọn ô gộp, bấm Merge and Center để tắt gộp | Mất hiệu ứng chữ chạy ngang nhiều cột |
| Canh giữa qua vùng chọn | Chọn các ô, mở Format Cells, tab Alignment, chọn Center Across Selection | Giữ được hình thức, không tạo vật cản cho mảng động |
Cách thứ hai đáng dùng khi ô gộp chỉ để trình bày. Nó cho cảm giác chữ nằm giữa nhiều cột nhưng thực chất không gộp ô nào, nên mảng động vẫn đổ qua được.
Một lưu ý về thứ tự: nếu ô gộp nằm ở hàng tiêu đề và bạn bỏ gộp, hãy kiểm tra lại công thức in ấn và vùng đặt tên. Nhiều file dùng ô gộp làm mốc cho vùng in, bỏ gộp xong thì khổ in lệch.
Cách sửa khi công thức nằm trong bảng Excel
Công thức mảng động không chạy được bên trong bảng Excel. Microsoft ghi thẳng: công thức mảng không được hỗ trợ trong bảng, hãy chuyển công thức ra ngoài bảng hoặc chuyển bảng thành vùng thường (Nguồn: Microsoft Support, How to correct a #SPILL error). Lý do: mỗi dòng trong bảng được thiết kế để tính một giá trị, trong khi mảng động muốn trả về nhiều giá trị.
Ba cách xử lý, theo thứ tự nên thử:
- Đưa công thức ra ngoài bảng. Giữ bảng làm nơi nhập dữ liệu, đặt vùng kết quả ở một vùng trống bên phải hoặc bên dưới. Cách này giữ nguyên lợi ích của bảng: tự thêm dòng, tham chiếu theo tên cột.
- Chuyển bảng thành vùng thường. Chọn một ô trong bảng, vào Table Design, chọn Convert to Range. Sau đó công thức mảng đặt được ngay trong vùng dữ liệu. Đánh đổi: mất tham chiếu cấu trúc và mất tự động mở rộng.
- Viết lại thành công thức một giá trị. Nếu kết quả chỉ cần một con số, bọc công thức trong một hàm tổng hợp như SUM, COUNT hoặc AVERAGE. Khi đó công thức trả về một giá trị, không cần đổ, và chạy được trong bảng.
Cách 3 hay bị bỏ qua nhưng thường là cách đúng. Ví dụ bạn muốn đếm số đơn hàng thoả một điều kiện: dùng COUNTIFS trả về một số, không cần FILTER rồi đếm.
Một điểm cần nhớ khi chọn cách 2: sau khi chuyển bảng thành vùng, công thức tham chiếu theo tên cột như TenKhach sẽ mất hiệu lực. Nếu file có nhiều công thức kiểu đó, hãy ghi lại danh sách trước khi chuyển, hoặc chỉ chuyển một phần bảng.
Cách sửa khi vùng đổ vượt biên trang tính
Lỗi này xảy ra khi công thức tham chiếu cả cột và đặt ở vị trí không đủ chỗ phía dưới. Ví dụ đặt ở ô E2 công thức tra cứu theo cả cột A. Bản Excel không có mảng động chỉ tra một giá trị ở dòng 2. Bản có mảng động tra cả cột, trả về 1.048.576 kết quả, và chạm đáy trang tính (Nguồn: Microsoft Support).
Ba cách sửa, theo đúng ba cách Microsoft đưa ra:
📊 Bảng: Ba cách sửa khi vùng đổ vượt biên trang tính
| Cách | Công thức | Phù hợp khi |
|---|---|---|
| Tham chiếu đúng vùng dữ liệu | VLOOKUP với vùng A2:A7 thay cho A:A | Muốn kết quả tự đổ thành danh sách |
| Tham chiếu đúng ô cùng dòng rồi kéo xuống | VLOOKUP với A2 thay cho A:A | Làm việc trong bảng Excel |
| Ép về một giá trị bằng toán tử @ | Thêm @ trước vùng tham chiếu | Cần giữ đúng hành vi của file cũ |
Trong thực tế, cách 1 là cách nên dùng. Tham chiếu cả cột trong công thức mảng không chỉ gây #SPILL mà còn làm file chậm, vì Excel phải tính cho hơn một triệu dòng dù dữ liệu chỉ có vài trăm dòng.
Quy tắc dễ nhớ: trong công thức mảng, không bao giờ tham chiếu cả cột. Luôn chọn đúng vùng dữ liệu, hoặc dùng bảng và tham chiếu theo tên cột.
Cách sửa khi công thức đổi kích thước liên tục
Excel cần biết vùng đổ rộng bao nhiêu để đặt kết quả. Nếu kích thước đổi sau mỗi lần tính, Excel không chốt được và trả #SPILL!. Ví dụ điển hình là tạo dãy số có độ dài ngẫu nhiên: hàm SEQUENCE bọc quanh RANDBETWEEN.
Lỗi do kích thước thay đổi xảy ra khi công thức trả về mảng có số dòng hoặc số cột không ổn định giữa các lượt tính. Ba hàm hay bị nhắc trong trường hợp này là RAND, RANDBETWEEN và RANDARRAY, nhưng chỉ khi chúng được dùng để quyết định kích thước mảng, ví dụ làm đối số cho SEQUENCE. Các hàm như OFFSET, INDIRECT hay TODAY không trả về kết quả khác nhau sau mỗi lượt tính, nên hiếm khi gây đúng lỗi này; riêng OFFSET và INDIRECT vẫn có thể gây lỗi nếu chúng trả về một vùng có kích thước thay đổi (Nguồn: Microsoft Support, How to correct a #SPILL error).
Cách sửa: cố định kích thước. Thay vì để hàm ngẫu nhiên quyết định số dòng, hãy cho nó một con số cố định hoặc một ô tham chiếu mà bạn kiểm soát.
📊 Bảng: Cố định kích thước mảng để hết #SPILL
| Trước | Sau | Vì sao hết lỗi |
|---|---|---|
| SEQUENCE bọc RANDBETWEEN | SEQUENCE(20) | Số dòng cố định là 20 |
| RANDARRAY không tham số | RANDARRAY(10,3) | Số dòng và số cột cố định |
| SEQUENCE theo ô độ dài | SEQUENCE(B1) với B1 là số bạn nhập | Kích thước chỉ đổi khi bạn đổi ô B1, không đổi giữa các lượt tính |
Nếu bạn cần dãy số ngẫu nhiên để bốc thăm hoặc chia ca, hãy tạo một lần rồi dán giá trị. Cách này còn có lợi thế là kết quả không đổi khi mở lại file, phù hợp với việc cần lưu lại để đối chiếu.
Cách xử lý khi Excel báo hết bộ nhớ
Trường hợp này ít gặp nhưng dễ nhận ra: máy chậm hẳn, quạt chạy to, file mở lâu, và ô công thức báo #SPILL! dù vùng bên dưới trống. Nguyên nhân là công thức yêu cầu Excel dựng một mảng lớn hơn khả năng bộ nhớ còn lại (Nguồn: Microsoft Support).
Bốn bước giảm tải, làm từ bước rẻ nhất:
- Thu nhỏ vùng tham chiếu từ cả cột về đúng vùng dữ liệu.
- Lọc bớt dữ liệu nguồn trước khi tính, thay vì tính trên toàn bộ rồi mới lọc.
- Bỏ các cột công thức không còn dùng, đặc biệt là cột chứa công thức mảng lớn.
- Chuyển phần xử lý nặng sang Power Query, chỉ đưa kết quả đã gọn về sheet.
Với file có nhiều năm dữ liệu, cách hiệu quả nhất thường là tách file theo năm. Một file chỉ giữ dữ liệu năm nay sẽ nhẹ hơn nhiều so với file gộp năm năm, trong khi việc tra cứu vẫn làm được bằng cách mở đúng file cần.
Bảng chẩn đoán nhanh: nhìn triệu chứng, chọn cách sửa
Khi không chắc lỗi thuộc loại nào, đối chiếu theo bảng dưới. Thứ tự câu hỏi được xếp từ khả năng gặp cao tới thấp.
📊 Bảng: Sáu câu chẩn đoán nhanh khi chưa rõ nguyên nhân
| Câu hỏi | Nếu câu trả lời là có | Việc cần làm |
|---|---|---|
| Ngay dưới ô công thức có dữ liệu hoặc công thức khác không? | Có | Xoá hoặc chuyển ô đó đi |
| Vùng đổ có đi qua ô gộp không? | Có | Bỏ gộp, hoặc canh giữa qua vùng chọn |
| Công thức có nằm trong bảng Excel không? | Có | Đưa ra ngoài bảng, hoặc chuyển bảng thành vùng |
| Công thức có tham chiếu cả cột không? | Có | Đổi sang vùng dữ liệu cụ thể |
| Công thức có RAND, RANDBETWEEN hay RANDARRAY không? | Có | Cố định kích thước |
| File có nặng bất thường và máy chậm không? | Có | Thu nhỏ vùng tính, tách file theo năm |
Nếu cả sáu câu đều trả lời không mà vẫn lỗi, hãy kiểm tra xem có phải bạn vừa kéo công thức xuống chính vùng đổ của nó. Đây là một dạng của nguyên nhân đầu tiên: chính công thức vừa kéo đã chiếm chỗ trong vùng đổ, nên Excel coi là có ô cản.
Bốn thói quen bố trí bảng để không gặp #SPILL
Sửa lỗi mất vài phút, phòng lỗi mất vài giây lúc thiết kế. Bốn thói quen dưới đây áp dụng được cho mọi file có dùng công thức mảng.

Chừa vùng đệm phía dưới công thức mảng và giữ vùng đổ tách khỏi bảng dữ liệu.
Một, chừa vùng đệm dưới công thức. Khi đặt FILTER hoặc SORT ở một ô, chừa phía dưới ít nhất bằng số dòng dữ liệu dự kiến trong một năm. Đừng đặt công thức mảng ngay trên một bảng khác.
Hai, giữ công thức mảng ngoài bảng Excel. Bảng để nhập dữ liệu, vùng đổ để riêng. Cách này còn giúp bạn in báo cáo mà không lẫn dữ liệu gốc.
Ba, bỏ ô gộp ở vùng dữ liệu. Ô gộp chỉ nên dùng cho tiêu đề trang, không dùng cho hàng tiêu đề bảng. Nếu cần hình thức chữ chạy ngang, dùng canh giữa qua vùng chọn.
Bốn, đặt tên vùng nguồn hoặc dùng bảng cho dữ liệu gốc. Khi vùng nguồn là một bảng có tên, công thức mảng tự dài theo số dòng mới mà không cần sửa.
Có thêm một mẹo nhỏ: dùng toán tử # khi cần tham chiếu kết quả của một công thức mảng khác. Ví dụ công thức đặt ở A2, muốn đếm toàn bộ kết quả thì viết COUNT(A2#). Cách này không cần biết vùng đổ dài bao nhiêu và tự cập nhật khi dữ liệu đổi.
#SPILL khác gì các lỗi Excel khác?
Nhiều người nhầm #SPILL với lỗi công thức sai. Bảng dưới đây phân biệt nhanh năm lỗi hay gặp cùng nhau.
📊 Bảng: Phân biệt #SPILL với năm lỗi Excel hay gặp
| Lỗi | Nghĩa | Khác #SPILL ở đâu |
|---|---|---|
| #SPILL! | Công thức đúng nhưng không đổ được kết quả | Không phải sửa công thức, phải dọn chỗ |
| #REF! | Tham chiếu tới ô hoặc sheet đã bị xoá | Công thức trỏ sai địa chỉ, không liên quan chỗ trống |
| #VALUE! | Kiểu dữ liệu không khớp, ví dụ lấy chữ chia cho số | Công thức sai kiểu, không phải vướng chỗ |
| #NAME? | Excel không nhận ra tên hàm hoặc tên vùng | Thường do gõ sai tên, hoặc file mở bằng bản Excel cũ |
| #CALC! | Công thức mảng không có kết quả để trả về, ví dụ FILTER không tìm thấy dòng nào | Công thức chạy đúng nhưng rỗng, không phải bị chặn |
Nếu file của bạn đang gặp nhiều lỗi cùng lúc, cách xử lý theo thứ tự: sửa #REF! trước vì nó làm sai địa chỉ, sau đó tới #NAME?, rồi mới tới #SPILL!. Bài cẩm nang sửa lỗi công thức Excel gom cách xử lý theo nhóm lỗi; các bài riêng cho từng mã nằm ở lỗi #VALUE, lỗi #REF, lỗi #NAME, lỗi #DIV/0 và lỗi #NUM.
Một lưu ý về #CALC!: lỗi này mới xuất hiện cùng mảng động, và nó thường đi kèm với #SPILL! khi bạn dùng FILTER. Phân biệt đơn giản: #CALC! nghĩa là công thức không có kết quả nào để trả về; #SPILL! nghĩa là có kết quả nhưng không có chỗ đặt.
Khi nào nên dừng sửa công thức và chuyển sang ứng dụng web?
#SPILL! là lỗi kỹ thuật, sửa được trong vài phút. Nhưng có một nhóm vấn đề khác thường bị nhầm là lỗi công thức, trong khi gốc rễ nằm ở cách cả nhóm cùng làm việc trên một file.
Ba dấu hiệu cho thấy vấn đề không còn ở công thức:
- Nhiều người cùng nhập vào một file, và không ai biết bản nào là bản mới nhất.
- Cùng một vùng đổ bị người này xoá, người kia kéo công thức xuống, dẫn tới lỗi lặp lại mỗi tuần.
- Cần phân quyền: người bán hàng chỉ được xem đơn của mình, kế toán xem toàn bộ, chủ chỉ xem tổng.
Trong ba trường hợp đó, sửa công thức chỉ chữa được triệu chứng. Cách xử lý gốc là tách phần nhập liệu ra khỏi phần tính toán, hoặc chuyển phần nhập sang một ứng dụng web có phân quyền. Bài biến file Excel thành app mô tả cách chuyển mà không phải nhập lại dữ liệu cũ.
Cần nói rõ để tránh hiểu lầm: với một người nhập và vài chục dòng dữ liệu, Excel là lựa chọn đúng và rẻ. Chỉ khi số người cùng nhập tăng hoặc cần kiểm soát ai sửa gì, việc chuyển đổi mới đáng chi phí.
Câu hỏi thường gặp
Tại sao công thức đúng mà Excel vẫn báo #SPILL?
Vì #SPILL! không phải lỗi của công thức. Công thức đã tính ra kết quả, nhưng vùng cần ghi kết quả đang bị chiếm. Việc cần làm là dọn vùng đó, không phải sửa công thức. Dấu hiệu để phân biệt: bấm vào ô, nếu thấy viền gạch đứt và kết quả không hiện ở đâu thì đúng là lỗi chỗ trống.
Xoá ô cản rồi mà vẫn còn #SPILL thì sao?
Kiểm tra ba thứ theo thứ tự: ô vừa xoá có thực sự trống không (dùng LEN để đếm ký tự), vùng đổ có đi qua ô gộp nào không, và công thức có nằm trong bảng Excel không. Ba nguyên nhân này không tự hết khi xoá một ô.
Có cách nào tắt hẳn mảng động để không gặp #SPILL không?
Không có nút tắt cho toàn bộ file. Cách duy nhất để giữ hành vi cũ là thêm toán tử @ vào trước vùng tham chiếu trong từng công thức. Cách này chỉ nên dùng khi bạn cố ý muốn công thức trả về một giá trị.
Vùng đổ tự dài ra khi thêm dữ liệu, có cần làm gì không?
Không cần. Đó là điểm mạnh của mảng động: thêm dòng dữ liệu nguồn thì vùng kết quả tự dài, miễn phía dưới còn trống. Chỉ cần chừa đủ vùng đệm, đừng đặt bảng khác ngay bên dưới.
Làm sao biết vùng đổ dài bao nhiêu dòng?
Bấm vào ô công thức, viền gạch đứt cho biết chính xác vùng đổ hiện tại. Muốn đếm bằng số, dùng công thức đếm trên toán tử #, ví dụ đếm số dòng của kết quả đặt ở A2 bằng ROWS(A2#).
File .xls có dùng được công thức mảng động không?
Không. Định dạng .xls cũ không lưu được công thức mảng động đầy đủ, nên hãy lưu file sang .xlsx trước khi dùng FILTER, SORT hay UNIQUE. Nhưng định dạng file chỉ là một điều kiện: bản Excel đang mở file cũng phải hỗ trợ mảng động thì công thức mới chạy. File .xlsx mở bằng bản Excel quá cũ vẫn có thể hiện dấu @ hoặc lỗi #NAME?.
Làm sao biết bản Excel đang dùng có mảng động?
Gõ thử công thức =SEQUENCE(3) vào một ô trống. Nếu ba ô liên tiếp hiện 1, 2, 3 thì bản Excel có mảng động. Nếu báo #NAME? thì bản đó chưa hỗ trợ. Cách thử này không làm thay đổi dữ liệu, xoá ô sau khi thử là xong.
Vì sao thêm dòng dữ liệu mà vùng đổ không tự dài?
Thường là do ngay dưới vùng đổ đã có nội dung, nên Excel không còn chỗ để nới ra và trả #SPILL! thay vì tự dài. Kiểm tra bằng cách bấm vào ô công thức, xem viền gạch đứt có dừng trước một ô có dữ liệu không. Nguyên nhân thứ hai là vùng nguồn được tham chiếu bằng địa chỉ cố định thay vì cả bảng, nên thêm dòng ngoài vùng đó không được tính.
Lỗi #SPILL có làm mất dữ liệu không?
Không. Excel không ghi đè lên dữ liệu đang có, nên khi báo #SPILL! thì dữ liệu cũ vẫn nguyên. Điều từng gây mất dữ liệu là thao tác xoá ô cản mà không kiểm tra, hoặc xoá cả vùng để "cho sạch" rồi lưu file. Trước khi xoá, hãy xem ô đó có công thức hay dữ liệu cần giữ không.
Tóm lại
Lỗi #SPILL! gọn lại là một câu: công thức có kết quả nhưng không có chỗ đặt. Sáu nguyên nhân chia làm ba nhóm. Nhóm dọn chỗ gồm ô cản và ô gộp. Nhóm phải đổi cách đặt hoặc cách viết công thức gồm bảng Excel, tham chiếu cả cột, và công thức đổi kích thước. Nhóm cuối là hết bộ nhớ, phải giảm tải dữ liệu. Thói quen phòng lỗi chỉ gồm bốn việc: chừa vùng đệm, giữ công thức ngoài bảng, bỏ ô gộp ở vùng dữ liệu, và không tham chiếu cả cột trong công thức mảng.
📥 File Excel của bạn đang phải dùng nhiều công thức mảng, nhiều người cùng nhập và hay lỗi khi thêm dòng? Gửi mô tả ngắn về file đang dùng và số người cùng nhập, để được tư vấn hướng làm gọn nhất trước khi quyết định.
Gửi file phân tích miễn phí →Tác giả: Exceltoapp.vn