Khắc phục lỗi #SPILL! khi dùng mảng động - Thủ phạm có thể là các bảng Excel!

Chào các bạn,

Dạo gần đây mình hay gặp lỗi #SPILL! khi làm việc với các hàm mảng động như FILTER, SORT, UNIQUE. Ban đầu mình cứ nghĩ do mình nhập sai công thức, hoặc vùng dữ liệu có vấn đề. Sau một hồi loay hoay thì mình phát hiện ra một thủ phạm khá 'nguy hiểm' mà có thể nhiều người cũng hay gặp phải, đó chính là Bảng Excel (Excel Table).

Cụ thể là khi bạn dùng các hàm mảng động để trả về kết quả vào một vùng ô, nhưng ngay bên dưới hoặc bên cạnh vùng kết quả đó lại tồn tại một Bảng Excel (được tạo bằng cách bôi đen dữ liệu rồi nhấn Ctrl + T hoặc vào Insert > Table), thì Excel sẽ không cho phép mảng động 'tràn' dữ liệu ra. Nó sẽ báo lỗi #SPILL!.

Cách khắc phục rất đơn giản:

• Cách 1: Xóa bỏ Bảng Excel đó đi. Nếu bạn vẫn muốn giữ định dạng bảng, hãy bôi đen vùng dữ liệu của bảng, copy (Ctrl + C), rồi paste sang một vùng trống khác bằng Paste Special > Values (Alt + E + S + V). Sau đó, bạn có thể định dạng lại vùng dữ liệu này thành bảng nếu muốn.
• Cách 2: Di chuyển Bảng Excel đó sang một vị trí khác mà không ảnh hưởng đến vùng kết quả của mảng động.
• Cách 3: Đảm bảo rằng vùng dữ liệu mà hàm mảng động trả về kết quả không bị 'chồng lấn' lên bất kỳ Bảng Excel nào.

Đây là một lỗi khá 'khó chịu' và dễ gây nhầm lẫn. Hy vọng chia sẻ này giúp ích được cho các bạn khi gặp phải tình trạng tương tự.

Chúc các bạn thành công!

Cảm ơn bạn đã chia sẻ kinh nghiệm quý báu này! Lỗi #SPILL! đúng là gây khó chịu thật, mà thủ phạm là Excel Table thì quả thật là bất ngờ.

Mình cũng từng gặp tình huống tương tự, cứ loay hoay mãi không hiểu sao công thức không chạy. Hóa ra là do có một cái bảng nhỏ nào đó chen vào. Đúng là đôi khi những thứ tưởng chừng đơn giản lại là nguyên nhân gây lỗi.

Bạn có thể chia sẻ thêm về cách nhận biết hoặc phòng tránh việc Excel Table gây ảnh hưởng đến các hàm mảng động không? Ví dụ như có quy tắc nào khi đặt bảng hay khi dùng hàm mảng không?

Đúng là đôi khi những "nhân vật" quen thuộc như Excel Table lại là "thủ phạm" không ngờ tới cho lỗi #SPILL!. Kinh nghiệm bạn chia sẻ rất hữu ích, mình cũng từng bị lỗi này và loay hoay mãi không biết nguyên nhân.

Để phòng tránh, mình thường có thói quen xóa hết các định dạng bảng (Table) không cần thiết trước khi áp dụng các hàm mảng động. Hoặc nếu vẫn cần bảng đó, mình sẽ đảm bảo vùng kết quả của hàm mảng động không bị trùng lấn hay nằm chồng lên bất kỳ ô nào thuộc bảng đó, kể cả các hàng/cột được thêm tự động.

Có lẽ cách tốt nhất là tạo một khoảng trống an toàn giữa vùng dữ liệu nguồn và vùng chứa kết quả của hàm mảng động, để tránh mọi xung đột có thể xảy ra.