Khắc phục lỗi #SPILL! khi dùng SUMIFS với mảng động

Chào mọi người,

Dạo này mình hay làm việc với các file Excel có dữ liệu lớn và bắt đầu thử nghiệm với các hàm mảng động (Dynamic Arrays). Tuy nhiên, mình gặp phải một lỗi khá khó chịu là #SPILL! khi kết hợp hàm SUMIFS với mảng động.

Cụ thể, mình có một bảng dữ liệu bán hàng và muốn tính tổng doanh thu theo nhiều điều kiện khác nhau. Công thức ban đầu của mình trông như thế này:

=SUMIFS(DoanhThu, NgayBan, ">=2023-01-01", NgayBan, "<=2023-12-31", SanPham, A2:A10)

Khi A2:A10 là một dải ô chứa nhiều sản phẩm, mình mong đợi kết quả trả về một mảng các tổng doanh thu tương ứng với từng sản phẩm. Tuy nhiên, Excel lại báo lỗi #SPILL!.

Sau khi tìm hiểu, mình nhận ra rằng hàm SUMIFS truyền thống không hỗ trợ trả về mảng kết quả trực tiếp khi các tham số điều kiện là mảng. Lỗi #SPILL! xuất hiện vì Excel không thể

Chào bạn,

Lỗi #SPILL! khi dùng SUMIFS với mảng động thường xảy ra khi kết quả trả về của hàm mong đợi là một mảng nhưng lại không có đủ không gian để "tràn" ra. Trong trường hợp của bạn, khi bạn dùng A2:A10 làm một trong các tiêu chí của SUMIFS, Excel hiểu rằng bạn muốn tính tổng cho từng sản phẩm trong dải A2:A10, và mỗi kết quả này cần một ô riêng để hiển thị. Tuy nhiên, SUMIFS bản thân nó không trả về mảng động, nên nó không thể tự động "tràn" ra nhiều ô như bạn mong đợi.

Để khắc phục điều này, bạn có thể cân nhắc sử dụng SUMPRODUCT hoặc kết hợp SUMIFS với một hàm khác tạo mảng điều kiện, ví dụ như BYROW (nếu bạn đang dùng Excel 365) để xử lý từng dòng trong mảng điều kiện A2:A10.

Bạn có thể chia sẻ thêm về phiên bản Excel bạn đang sử dụng không? Điều này sẽ giúp mình đưa ra hướng dẫn cụ thể hơn.

Chào bạn,

Lỗi #SPILL! này đúng là hơi "nhức đầu" khi làm với mảng động thật. Mình hiểu ý bạn muốn tính tổng doanh thu cho từng sản phẩm trong dải A2:A10.

Cách mình hay làm với tình huống này là dùng hàm SUMPRODUCT. Bạn thử thay công thức cũ bằng: ``excel =SUMPRODUCT(DoanhThu,--(NgayBan>=DATE(2023,1,1)),--(NgayBan