Xử lý lỗi #CALC! khi dùng SUBTOTAL với dữ liệu có công thức trả về lỗi

Chào các anh chị, hôm nay em muốn chia sẻ một tình huống khá khó chịu mà em vừa gặp phải với hàm SUBTOTAL.

Chả là em đang làm báo cáo tổng hợp, có sử dụng SUBTOTAL để tính tổng các cột. Tuy nhiên, trong file của em có một số ô chứa công thức khác mà đôi khi nó trả về lỗi (ví dụ: #DIV/0!, #VALUE!). Khi em lọc dữ liệu hoặc ẩn/hiện dòng, hàm SUBTOTAL của em nó cứ báo lỗi #CALC!.

Em tìm hiểu thì biết là lỗi này xảy ra khi SUBTOTAL cố gắng tính toán trên một phạm vi có chứa giá trị lỗi. Vậy làm sao để khắc phục ạ?

Em đã thử một số cách như sau:

• Sử dụng hàm IFERROR bọc quanh công thức trả về lỗi: Cách này hiệu quả nếu mình có thể sửa trực tiếp các công thức gây lỗi. Tuy nhiên, nếu các công thức lỗi đó đến từ file khác hoặc quá phức tạp thì hơi khó.

• Sử dụng AGGREGATE thay thế SUBTOTAL: Hàm AGGREGATE có một tham số cho phép bỏ qua các lỗi. Cú pháp ví dụ để tính tổng, bỏ qua lỗi là: =AGGREGATE(9, 6, A1:A10). Trong đó, số 9 là mã cho hàm SUM, còn số 6 là mã để bỏ qua các giá trị lỗi.

Em thấy dùng AGGREGATE là cách giải quyết triệt để nhất mà không cần sửa các công thức gốc. Có anh chị nào có kinh nghiệm xử lý lỗi này hoặc có cách nào hay hơn không, chia sẻ cho em với ạ!

Chào bạn,

Tình huống bạn gặp với hàm SUBTOTAL khá phổ biến đó. Để khắc phục lỗi #CALC! khi có lỗi trong phạm vi tính toán, bạn có thể kết hợp SUBTOTAL với hàm IFERROR.

Ví dụ, nếu bạn đang dùng =SUBTOTAL(9, A1:A10), bạn có thể sửa lại thành:

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

Hoặc một cách khác là dùng AGGREGATE, hàm này có khả năng bỏ qua các lỗi:

=AGGREGATE(9, 6, A1:A10)

Trong đó, số 9 là chức năng SUM, và số 6 là tùy chọn bỏ qua các lỗi.

Bạn thử cách nào thấy tiện lợi hơn nhé!

Một cách xử lý #CALC! khá hay mà mình hay dùng là kết hợp SUBTOTAL với IFERROR.

Thay vì dùng =SUBTOTAL(9, Vùng_Dữ_Liệu), bạn thử sửa thành: ``html =SUBTOTAL(9, IFERROR(Vùng_Dữ_Liệu, 0)) ` Cách này sẽ thay thế các ô bị lỗi trong Vùng_Dữ_Liệu bằng số 0, giúp SUBTOTAL` tính toán bình thường mà không báo lỗi #CALC!.

Bạn xem cách này có phù hợp với file của bạn không nhé!

Mình cũng từng gặp trường hợp tương tự với SUBTOTAL và lỗi #CALC!. Đúng là khá khó chịu khi dữ liệu có lỗi mà lại ảnh hưởng đến hàm tổng hợp.

Ngoài cách dùng IFERROR hoặc AGGREGATE mà các bạn đã chia sẻ, mình còn có một cách khác cũng khá hiệu quả, đó là dùng SUMIFS để lọc riêng các giá trị không bị lỗi trước khi tính tổng.

Ví dụ, nếu bạn muốn tính tổng cột A, bạn có thể thử: =SUBTOTAL(9, SUMIFS(A1:A10, A1:A10, "#DIV/0!", A1:A10, "#VALUE!")) (Lưu ý là bạn cần thêm các điều kiện cho từng loại lỗi có thể xảy ra).

Cách này hơi dài dòng một chút nhưng đôi khi nó giúp mình kiểm soát rõ hơn dữ liệu nào được tính tổng. Bạn thấy cách nào tiện hơn cho trường hợp của mình?