Khắc phục lỗi #SPILL! khi dùng mảng động trong Excel

Chào mọi người,

Dạo này mình đang làm việc với các hàm mảng động mới của Excel (như FILTER, SORT, UNIQUE) và gặp phải một lỗi khá khó chịu: #SPILL!. Lỗi này xuất hiện khi một công thức mảng động cố gắng trả về nhiều kết quả nhưng lại bị chặn bởi dữ liệu hoặc ô trống ở các ô bên cạnh.

Mình đã thử nhiều cách nhưng đôi khi vẫn loay hoay mãi. Ví dụ, khi mình dùng hàm FILTER để lấy danh sách các mặt hàng còn tồn kho, nhưng lại quên xóa bớt dữ liệu ở các cột bên cạnh, là y như rằng bị báo lỗi #SPILL!.

Sau một hồi tìm hiểu và thử nghiệm, mình đúc kết được một vài kinh nghiệm để xử lý lỗi này:

• Kiểm tra vùng dữ liệu trả về: Đảm bảo rằng các ô bên dưới và bên phải nơi bạn nhập công thức là trống. Đôi khi chỉ cần xóa một vài ô là công thức chạy bình thường.
• Sử dụng IFERROR: Bọc công thức mảng động của bạn trong IFERROR để xử lý các trường hợp lỗi, ví dụ: =IFERROR(FILTER(A1:A10, B1:B10>0), "Không có kết quả"). Điều này không khắc phục gốc rễ lỗi nhưng giúp bảng tính trông gọn gàng hơn.
• Tách công thức: Nếu mảng trả về quá lớn, hãy cân nhắc chia nó thành nhiều phần nhỏ hơn hoặc sử dụng các hàm khác để xử lý.
• Sử dụng XLOOKUP thay thế: Trong nhiều trường hợp, XLOOKUP có thể thay thế các hàm như FILTER hoặc VLOOKUP mà không gặp lỗi tràn mảng.

Các bạn đã gặp lỗi này bao giờ chưa? Có cao kiến nào khác để xử lý hiệu quả hơn không? Cùng thảo luận nhé!

Công thức FILTER của mình đơn giản thôi, kiểu như FILTER(B2:B100, C2:C100="Còn hàng") để lấy tên sản phẩm còn hàng. Vấn đề là đôi khi khu vực bên phải kết quả nó lại có mấy cái ô bị khóa hoặc có dữ liệu cũ mình quên xóa.

Cái vụ đảm bảo khu vực kết quả trống hoàn toàn thì mình cũng biết, nhưng nhiều khi file nó lộn xộn quá, tìm ra hết mấy ô "cứng đầu" đó cũng mệt.

Có cách nào để Excel tự động "nhảy" sang ô trống tiếp theo nếu ô hiện tại bị chặn không nhỉ? Kiểu như nó thông minh hơn một chút ấy.

Lỗi #SPILL! này đúng là gây đau đầu thật! Mình cũng từng gặp trường hợp tương tự khi dùng FILTER để lọc dữ liệu. Cách đơn giản nhất mình hay áp dụng là đảm bảo khu vực kết quả trả về của mảng động phải trống hoàn toàn. Nếu có bất kỳ dữ liệu nào, dù là ký tự trắng, cũng có thể gây ra lỗi.

Ngoài ra, bạn có thể thử giảm bớt phạm vi dữ liệu trả về của hàm mảng động. Ví dụ, nếu hàm FILTER của bạn trả về 10 cột nhưng bạn chỉ cần 5 cột, hãy chỉ định rõ 5 cột đó trong công thức.

Bạn có thể chia sẻ cụ thể hơn về công thức FILTER bạn đang dùng không? Biết đâu có cách tối ưu hơn cho trường hợp của bạn.