Khắc phục lỗi #CALC! khi tính toán với hàm SUBTOTAL

Chào mọi người,

Mình thường xuyên làm việc với các bảng dữ liệu lớn và hay sử dụng hàm SUBTOTAL để tính toán các giá trị đã được lọc. Tuy nhiên, dạo gần đây mình hay gặp phải lỗi #CALC! khi dùng hàm này, đặc biệt là khi có các ô trống xen kẽ hoặc khi sử dụng các hàm tính toán phức tạp bên trong SUBTOTAL.

Sau một hồi tìm tòi và thử nghiệm, mình phát hiện ra nguyên nhân phổ biến là do:

• Các ô trống trong vùng dữ liệu mà SUBTOTAL đang tham chiếu đến.
• Sử dụng các hàm như AVERAGE, MAX, MIN, STDEV,... bên trong SUBTOTAL mà gặp phải các giá trị không hợp lệ (như text hoặc lỗi).
• Các phép tính bên trong SUBTOTAL dẫn đến kết quả không xác định.

Cách mình thường khắc phục là:

1. Đảm bảo vùng dữ liệu tham chiếu của SUBTOTAL không có ô trống hoặc xử lý các ô trống đó (ví dụ: dùng IFERROR để điền giá trị 0 hoặc một giá trị mặc định khác).
2. Kiểm tra kỹ các hàm tính toán lồng bên trong SUBTOTAL, đảm bảo chúng trả về giá trị số hợp lệ.
3. Nếu là phép tính phức tạp, nên tách ra thành các bước nhỏ hơn để dễ dàng gỡ lỗi.

Ví dụ, thay vì:

=SUBTOTAL(9, A1:A10)

Nếu bạn biết có thể có ô trống, bạn có thể sửa thành:

=SUBTOTAL(9, IF(A1:A10"", A1:A10, 0))

(Lưu ý: Công thức mảng này cần nhấn Ctrl+Shift+Enter trên các phiên bản Excel cũ).

Hoặc sử dụng AGGREGATE, hàm này mạnh mẽ hơn và có thể bỏ qua lỗi:

=AGGREGATE(9, 5, A1:A10)

Số 5 trong AGGREGATE có nghĩa là bỏ qua các hàng chứa lỗi và các hàng trống.

Không biết mọi người có gặp lỗi này và có cách khắc phục nào khác hiệu quả hơn không? Chia sẻ với mình nhé!

Chào bạn,

Mình cũng từng gặp lỗi #CALC! này khi làm việc với SUBTOTAL, khá khó chịu thật. Theo kinh nghiệm của mình, ngoài các nguyên nhân bạn nêu, đôi khi lỗi này còn xuất hiện khi có các giá trị lỗi khác (như #DIV/0!, #N/A...) nằm trong vùng dữ liệu mà SUBTOTAL đang tính toán.

Cách đơn giản nhất mình hay làm là dùng IFERROR để "bao" lại các hàm tính toán bên trong SUBTOTAL, ví dụ: =SUBTOTAL(9, IF(ISNUMBER(A1:A10), A1:A10, "")) hoặc =SUBTOTAL(9, IFERROR(A1:A10, 0)).

Bạn thử kiểm tra xem vùng dữ liệu của mình có chứa các loại lỗi khác không nhé. Nếu vẫn không được, bạn có thể chia sẻ thêm cấu trúc bảng và công thức cụ thể bạn đang dùng để mọi người xem xét kỹ hơn không?