TL;DR: Cách khắc phục trong 10 giây
Lỗi #VALUE! thường có nghĩa là Google Sheets hiểu lầm các con số của bạn là Văn bản (Text). Bạn không thể thực hiện phép toán trên chữ cái, vì vậy công thức bị lỗi. Hãy thử các cách khắc phục nhanh sau:
- Sửa định dạng: Bôi đen các con số của bạn và chọn Định dạng > Số > Số.
- Mẹo toán học: Bao bọc dải ô trong hàm
VALUE()bằng cách sử dụngSUMPRODUCT:=SUMPRODUCT((A2:A10="Tiêu chí")*VALUE(B2:B10)) - Mẹo "Nhân với 1": Trong một cột mới, sử dụng
=C2*1. Điều này buộc Sheets phải nhận diện giá trị là một con số.
Thông báo lỗi chi tiết
Di chuột qua góc đỏ của ô tính. Bạn có thể sẽ thấy cảnh báo cụ thể này:
#VALUE! (Function SUMIF parameter 3 expects number values. But '...' is a text and cannot be coerced to a number.)
Tại sao lỗi này xảy ra?
Google Sheets thường dự đoán kiểu dữ liệu rất tốt. Tuy nhiên, các định dạng ẩn có thể làm hỏng logic này. Nếu sum_range của bạn chứa các giá trị trông giống số nhưng lại được lưu trữ dưới dạng chuỗi (string), SUMIF sẽ không thể thực hiện lệnh.
Dưới đây là những nguyên nhân phổ biến nhất:
- Dữ liệu nhập bị lỗi: Dữ liệu từ tệp CSV hoặc phần mềm kế toán thường bao gồm các dấu nháy đơn "ma" (ví dụ:
'500) khiến các con số bị giữ ở dạng văn bản. - Trích xuất từ Web: Sao chép dữ liệu từ một trang web thường đi kèm với các ký tự ẩn như
(khoảng trắng không ngắt). - Ghi đè thủ công: Một đồng nghiệp có thể đã thiết lập cột thành "Văn bản thuần túy" trước khi nhập dữ liệu.
- Dấu phẩy và Dấu chấm: Nếu trang tính của bạn được đặt theo định dạng vùng Châu Âu,
1,234.56có thể bị đọc là văn bản thay vì1.234,56.
Cách khắc phục
Cách 1: Sử dụng SUMPRODUCT để chuyển đổi tức thì
Nếu bạn không muốn thay đổi dữ liệu nguồn, hãy sử dụng SUMPRODUCT. Không giống như SUMIF, hàm này xử lý các thao tác mảng linh hoạt hơn. Nó có thể chuyển đổi văn bản thành số ngay trong khi tính toán.
=SUMPRODUCT((A2:A100="Doanh số")*VALUE(B2:B100))
Công thức này kiểm tra chữ "Doanh số" ở cột A, chuyển đổi cột B thành số thực bằng hàm VALUE, sau đó cộng chúng lại.
Cách 2: Loại bỏ các khoảng trắng ẩn
Đôi khi một con số không phải là số vì nó có khoảng trắng ở cuối (ví dụ: "100 "). Bạn có thể làm sạch toàn bộ dải ô cùng lúc bằng cách sử dụng TRIM và CLEAN.
=ARRAYFORMULA(SUMIF(A2:A100, "Hoàn thành", VALUE(TRIM(B2:B100))))
Cách 3: Khắc phục hàng loạt bằng "Dán đặc biệt"
Đây là cách nhanh nhất để sửa hàng nghìn hàng mà không cần thêm công thức mới. Nó buộc Google Sheets phải đánh giá lại mọi ô.
- Nhập số
1vào bất kỳ ô trống nào và sao chép nó (Ctrl+C). - Bôi đen cột chứa các con số "giả" đang gây ra lỗi.
- Nhấp chuột phải và chọn Dán đặc biệt > Chỉ dán giá trị.
- Nhấp chuột phải lần nữa và chọn Dán đặc biệt > Nhân.
Google Sheets nhân mọi ô với 1. Vì bạn không thể nhân văn bản, Sheets buộc phải chuyển đổi nội dung sang định dạng số.
Cách 4: Căn chỉnh các dải ô của bạn
Kiểm tra kỹ xem dải ô tiêu chí và dải ô tính tổng của bạn có kích thước bằng nhau hay không. Nếu tiêu chí của bạn là A2:A50 nhưng dải ô tính tổng là B2:B40, công thức thường sẽ lỗi hoặc trả về kết quả sai. Đảm bảo cả hai dải ô đều bắt đầu và kết thúc ở cùng một số hàng.
Cách xác minh kết quả
Bạn không chắc liệu nó có hoạt động không? Hãy nhìn vào cách căn lề. Theo mặc định, Google Sheets căn lề số sang bên phải và văn bản sang bên trái. Nếu các con số của bạn nằm sát lề trái của ô, chúng vẫn đang là văn bản.
Bạn cũng có thể chạy một bài kiểm tra nhanh trong một ô trống:
=ISNUMBER(B2)
Nếu kết quả trả về FALSE, bạn vẫn còn vấn đề về định dạng cần giải quyết.
Đọc thêm
- Hỗ trợ Google Sheets: Hướng dẫn hàm SUMIF
- Làm chủ hàm VALUE để làm sạch dữ liệu
- Nâng cao: Sử dụng REGEXREPLACE để loại bỏ ký hiệu tiền tệ khỏi số