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

Chào mọi người,

Mình vừa gặp một tình huống khá khó chịu với hàm SUBTOTAL trong Excel. Khi mình dùng SUBTOTAL để tính tổng một cột, nhưng trong cột đó lại có một vài ô chứa công thức trả về lỗi (ví dụ: #DIV/0!, #VALUE!), thì hàm SUBTOTAL lại trả về lỗi #CALC! thay vì bỏ qua các ô lỗi đó.

Theo mình tìm hiểu thì đây là một lỗi khá phổ biến khi dữ liệu đầu vào của SUBTOTAL có chứa các giá trị không phải là số hoặc là lỗi.

Nguyên nhân:

• Hàm SUBTOTAL mặc định sẽ tính toán trên tất cả các ô trong phạm vi được chọn, bao gồm cả các ô có công thức trả về lỗi.
• Khi một ô trong phạm vi trả về lỗi, toàn bộ phép tính của SUBTOTAL sẽ bị ảnh hưởng và trả về lỗi #CALC!.

Cách khắc phục:

Để giải quyết vấn đề này, chúng ta có thể sử dụng một mảng phụ kết hợp với hàm IFERROR hoặc AGGREGATE.

1. Sử dụng IFERROR kết hợp mảng phụ:

Chúng ta có thể tạo một mảng phụ bằng cách dùng IFERROR để thay thế các lỗi bằng 0 (hoặc một giá trị khác tùy ý), sau đó mới dùng SUBTOTAL để tính tổng.

Ví dụ, nếu bạn muốn tính tổng cột B từ B2 đến B10 và có thể có lỗi, bạn có thể dùng công thức sau:

=SUBTOTAL(9, IF(ISERROR(B2:B10), 0, B2:B10))

Lưu ý: Đây là công thức mảng, bạn cần nhập bằng tổ hợp phím Ctrl + Shift + Enter (trên các phiên bản Excel cũ) hoặc chỉ cần Enter (trên Excel có hỗ trợ mảng động).

2. Sử dụng hàm AGGREGATE (khuyến khích):

Hàm AGGREGATE mạnh mẽ hơn và có thể xử lý các lỗi một cách trực tiếp.

Cú pháp của AGGREGATE cho phép bạn chỉ định cách xử lý các lỗi:

=AGGREGATE(function_num, options, array, [k])

Để thay thế SUBTOTAL(9, ...), bạn dùng:

=AGGREGATE(9, 5, B2:B10)

Trong đó:

• 9 là mã cho hàm SUM.
• 5 là tùy chọn để bỏ qua các giá trị lỗi.

Công thức này sẽ tự động bỏ qua các ô chứa lỗi trong phạm vi tính toán mà không cần dùng Ctrl+Shift+Enter.

Hy vọng chia sẻ này giúp ích cho các bạn khi gặp lỗi #CALC! với SUBTOTAL.

Chào bạn,

Mình hiểu vấn đề bạn đang gặp phải với hàm SUBTOTAL và lỗi #CALC!. Đúng là khi có lỗi trong phạm vi tính toán, SUBTOTAL sẽ bị ảnh hưởng.

Để khắc phục tình trạng này, bạn có thể thử dùng kết hợp hàm AGGREGATE. Hàm này cho phép bạn chỉ định loại lỗi mà nó sẽ bỏ qua. Ví dụ, để tính tổng và bỏ qua các lỗi, bạn có thể dùng cú pháp như sau:

=AGGREGATE(9, 6, [phạm vi])

Trong đó:

• 9 là mã cho hàm SUM.
• 6 là mã để bỏ qua các giá trị lỗi.

Bạn thử xem cách này có hiệu quả không nhé!

Chào bạn,

Cảm ơn bạn đã chia sẻ về lỗi #CALC! khi dùng SUBTOTAL. Đúng là trường hợp này khá phiền phức khi dữ liệu có lỗi. Cách dùng AGGREGATE mà bạn vừa đề cập rất hay và hiệu quả đấy!

Ngoài ra, một cách khác cũng có thể áp dụng là dùng mảng tạm thời. Bạn có thể thử dùng công thức mảng (Ctrl+Shift+Enter) như sau:

=SUM(IFERROR(Phạm_vi_dữ_liệu,0))

Công thức này sẽ chuyển các ô lỗi thành 0 trước khi tính tổng, giúp SUBTOTAL hoạt động bình thường.

Bạn thấy cách nào tiện lợi hơn?

Chào bạn,

Mình cũng từng gặp trường hợp tương tự khi dùng SUBTOTAL. Đúng là khi có ô lỗi trong phạm vi, hàm sẽ "dở chứng" ngay.

Cách dùng AGGREGATE mà bạn chia sẻ rất chuẩn và là giải pháp tối ưu nhất cho vấn đề này. Nó vừa ngắn gọn, dễ hiểu lại xử lý triệt để các loại lỗi. Cảm ơn bạn đã gợi ý nhé!

Còn một cách khác cũng khá hay, nếu bạn muốn "dọn dẹp" dữ liệu trước khi tính tổng thì có thể dùng IFERROR lồng vào SUM trước, sau đó mới dùng SUBTOTAL trên kết quả của SUM. Tuy nhiên, cách này hơi dài dòng hơn so với AGGREGATE.