Khắc phục lỗi #VALUE! khi dùng hàm SUM khi có lẫn ký tự văn bản

Chào các anh chị em trong diễn đàn,

Dạo này mình hay làm việc với các file excel tổng hợp số liệu từ nhiều nguồn khác nhau, và thường xuyên gặp phải lỗi #VALUE! khi dùng hàm SUM. Nguyên nhân là do trong vùng dữ liệu cần tính tổng có lẫn một vài ô chứa ký tự văn bản, dù là rất nhỏ.

Ví dụ, mình có một bảng dữ liệu bán hàng và muốn tính tổng doanh thu. Tuy nhiên, có một vài ô nhập liệu bị sai, ví dụ như nhập nhầm 1.000.000 thay vì 1000000, hoặc có ô lại ghi chú (đã hủy).

Việc tìm thủ công từng ô lỗi này với file vài trăm, vài nghìn dòng thì đúng là ác mộng. Mình đã thử nhiều cách nhưng chưa thấy cái nào thực sự hiệu quả và nhanh chóng.

Gần đây, mình có xem được một video hướng dẫn trên Youtube (mình không nhớ tên kênh, chỉ nhớ nội dung) về cách khắc phục lỗi này bằng cách kết hợp hàm SUMPRODUCT với hàm ISNUMBER. Cách này khá hay, nó sẽ chỉ lấy những ô nào là số để tính tổng, bỏ qua các ô văn bản.

Công thức mình hay dùng là:

=SUMPRODUCT(--ISNUMBER(A1:A100))

Tuy nhiên, cách này chỉ đếm số lượng ô chứa số. Để tính tổng thì mình sửa lại thành:

=SUMPRODUCT((ISNUMBER(A1:A100))*(A1:A100))

Hoặc một cách khác cũng khá hiệu quả là dùng hàm AGGREGATE.

=AGGREGATE(9,6,A1:A100)

Trong đó:

• Số 9 là mã cho hàm SUM.
• Số 6 là tùy chọn bỏ qua các lỗi (bao gồm cả lỗi #VALUE!).
• A1:A100 là vùng dữ liệu cần tính tổng.

Cách này rất gọn và hiệu quả. Mình muốn chia sẻ lại cho anh em nào đang gặp vấn đề tương tự.

Có anh em nào có cách nào khác hay hơn, hoặc có kinh nghiệm xử lý các file dữ liệu

Hay quá bạn ơi, vụ lỗi #VALUE! này đúng là ám ảnh với ai làm nhiều số liệu thật.

Mình thì hay gặp kiểu có dấu phẩy ngăn cách hàng nghìn này nè, đúng là khó chịu thật. Thay vì dò từng ô, bạn thử dùng hàm SUMPRODUCT kết hợp với SUBSTITUTE xem sao. Nó sẽ tự động loại bỏ các ký tự không phải số trước khi tính tổng.

Ví dụ, nếu vùng dữ liệu của bạn là A1:A10, bạn có thể thử công thức:

=SUMPRODUCT(SUBSTITUTE(A1:A10,",",""))

Hoặc nếu có thêm ghi chú, bạn có thể lồng thêm hàm IFERROR để bỏ qua các ô không thể chuyển đổi thành số.

Hay quá bạn ơi, vụ lỗi #VALUE! này đúng là ám ảnh với ai làm nhiều số liệu thật.

Mình thì hay gặp kiểu có dấu phẩy ngăn cách hàng nghìn này nè, đúng là khó chịu thật. Thay vì dò từng ô, bạn thử dùng hàm SUMPRODUCT kết hợp với SUBSTITUTE xem sao. Nó sẽ tự động loại bỏ các ký tự không phải số trước khi tính tổng.

Ví dụ, nếu vùng dữ liệu của bạn là A1:A10, bạn có thể thử công thức:

=SUMPRODUCT(SUBSTITUTE(A1:A10,",",""))

Hoặc nếu có thêm ghi chú, bạn có thể lồng thêm hàm IFERROR để bỏ qua các ô không thể chuyển đổi thành số.

Cách của bạn có vẻ cũng hiệu quả, bạn có thể chia sẻ thêm ví dụ cụ thể về cách bạn xử lý các ô ghi chú (đã hủy) không? Mình tò mò muốn học hỏi thêm!