Gỡ rối lỗi #CALC! khi dùng SUBTOTAL với dữ liệu có công thức trả về lỗi

Chào mọi người,

Hôm nay mình muốn chia sẻ về một lỗi khá khó chịu mà mình vừa gặp phải khi làm việc với file Excel lớn, đó là lỗi #CALC!. Lỗi này thường xuất hiện khi hàm SUBTOTAL được sử dụng để tính toán trên một phạm vi dữ liệu mà trong đó có chứa các ô trả về lỗi (ví dụ: #VALUE!, #DIV/0!, #REF!...).

Cụ thể, tình huống của mình là mình dùng SUBTOTAL để tính tổng một cột, nhưng cột đó lại có chứa các công thức tính toán khác mà đôi khi trả về lỗi do điều kiện không thỏa mãn hoặc dữ liệu đầu vào không hợp lệ. Khi đó, hàm SUBTOTAL thay vì bỏ qua các ô lỗi này thì lại trả về #CALC!, làm ảnh hưởng đến kết quả cuối cùng.

Sau khi tìm hiểu và thử nghiệm, mình thấy có một vài cách để khắc phục:

• Sử dụng hàm IFERROR hoặc IFNA bọc các công thức con: Đây là cách triệt để nhất. Bằng cách bọc các công thức trong cột dữ liệu gốc bằng IFERROR hoặc IFNA, bạn có thể chỉ định một giá trị trả về (ví dụ: 0 hoặc để trống) khi công thức con bị lỗi. Khi đó, SUBTOTAL sẽ không gặp phải lỗi nữa. Ví dụ: Thay vì =CongThucGoc(A1), bạn dùng =IFERROR(CongThucGoc(A1), 0).
• Sử dụng hàm AGGREGATE: Hàm AGGREGATE mạnh mẽ hơn SUBTOTAL ở chỗ nó cho phép bạn chỉ định loại lỗi nào mà hàm sẽ bỏ qua. Để thay thế SUBTOTAL, bạn có thể dùng AGGREGATE với tùy chọn 9 (SUM) và tùy chọn 6 (Ignore error values). Công thức sẽ có dạng: =AGGREGATE(9, 6, RangeDoiChieu).

Mình thấy cách dùng AGGREGATE khá gọn gàng và hiệu quả trong trường hợp này. Tuy nhiên, nếu bạn quen thuộc với IFERROR thì nó cũng là một giải pháp tốt.

Có ai đã từng gặp lỗi này và có cách xử lý nào khác không, chia sẻ thêm cho mọi người cùng biết nhé!

Chào bạn,

Mình cũng từng gặp tình huống tương tự khi dùng SUBTOTAL. Đúng là khi có lỗi trong phạm vi tính toán thì hàm này dễ bị "kẹt" theo.

Ngoài cách dùng IFERROR với mảng như bạn kia gợi ý, mình hay có thói quen tạo một cột phụ để "dọn dẹp" lỗi trước. Cụ thể là dùng IFERROR(ô_chứa_công_thức, 0) cho cột phụ, rồi mới SUBTOTAL trên cột này. Cách này tuy thêm một cột nhưng nhìn đỡ rối hơn, dễ kiểm soát lỗi.

Bạn có muốn chia sẻ thêm về cách bạn xử lý không? Hoặc có trường hợp nào phức tạp hơn không?

Chào bạn, cảm ơn bạn đã chia sẻ về lỗi #CALC! này. Đúng là khi dữ liệu đầu vào có lỗi thì các hàm tổng hợp như SUBTOTAL cũng dễ bị ảnh hưởng.

Để xử lý trường hợp này, bạn có thể thử kết hợp SUBTOTAL với IFERROR. Ví dụ, nếu bạn đang dùng SUBTOTAL(9, A1:A10) để tính tổng, bạn có thể sửa lại thành SUBTOTAL(9, IF(ISERROR(A1:A10), 0, A1:A10)). Tuy nhiên, cách này cần dùng với Ctrl+Shift+Enter (cho mảng cũ) hoặc chỉ cần Enter (cho mảng động).

Một cách khác là bạn có thể tạo một cột phụ để xử lý các lỗi trước khi dùng SUBTOTAL. Cột phụ này sẽ dùng IFERROR để trả về 0 nếu công thức gốc bị lỗi, còn không thì trả về giá trị gốc. Sau đó, dùng SUBTOTAL trên cột phụ này.

Bạn đã thử cách nào chưa, hay có tình huống cụ thể nào muốn chia sẻ thêm không?

Đúng là lỗi #CALC! với SUBTOTAL khá phiền phức khi dữ liệu gốc có lỗi. Mình cũng hay gặp tình huống này.

Ngoài các cách dùng IFERROR mà mọi người đã chia sẻ, mình còn có một cách khác muốn thử nghiệm, đó là dùng kết hợp AGGREGATE. Hàm này có một tùy chọn để bỏ qua các lỗi, rất tiện lợi.

Ví dụ, nếu bạn muốn tính tổng (chức năng 9) và bỏ qua các lỗi, bạn có thể dùng:

=AGGREGATE(9, 6, A1:A10)

Số 6 ở đây là mã để bỏ qua các lỗi. Bạn nào thử cách này chưa?