Sửa Lỗi #VALUE! Trong Excel: Sai Kiểu Dữ Liệu Trong Công Thức

Tóm Tắt Nhanh

Lỗi #VALUE! có nghĩa là Excel nhận được kiểu dữ liệu không đúng — thường là văn bản ở chỗ cần số. Bốn điều cần kiểm tra đầu tiên:

Chuỗi văn bản lẫn vào phép tính số
Khoảng trắng ẩn trong ô (trông có vẻ trống nhưng không phải)
Ngày tháng được lưu dưới dạng văn bản thay vì giá trị ngày thực
Công thức mảng không được xác nhận bằng Ctrl+Shift+Enter (Excel 2019 và cũ hơn)

Cách khắc phục nhanh: bọc công thức bằng IFERROR() để tạm ẩn lỗi trong khi tìm nguyên nhân. Dùng VALUE() để ép văn bản thành số.

Điều Gì Đang Xảy Ra

Khi Excel hiển thị #VALUE!, không phải Excel bị lỗi — nó hiểu công thức hoàn toàn đúng. Vấn đề nằm ở dữ liệu bạn đưa vào. Hãy hình dung Excel đang nói: "Tôi biết cách tính toán này, nhưng bạn đưa cho tôi chữ thay vì số."

Các nguyên nhân phổ biến:

Tính toán trên văn bản: =A1 + B1 trong khi A1 chứa "năm" thay vì 5
Ô chỉ chứa khoảng trắng: Ô có một dấu cách trông có vẻ trống nhưng không — Excel coi nó là văn bản
Ngày định dạng văn bản: CSV và dữ liệu từ API thường nhập ngày dưới dạng chuỗi như "2024-01-15" thay vì giá trị ngày thực của Excel
Sai kiểu đối số: Truyền một vùng ô vào hàm chỉ nhận một giá trị đơn — =LEN(A1:A10) là ví dụ điển hình
Ký tự không phải số trong dữ liệu số: "$1,200" hay "1.5kg" trông như số nhưng Excel không thể tính toán với chúng

Chẩn Đoán Vấn Đề

Bước 1 — Tìm ô gây lỗi

Nhấp vào ô đang hiển thị #VALUE!. Sau đó vào Công thức → Kiểm tra lỗi → Truy vết lỗi. Các mũi tên màu xanh sẽ xuất hiện, trỏ vào từng ô đang đưa dữ liệu vào công thức của bạn. Kiểm tra từng ô một.

Bước 2 — Kiểm tra xem số có đang được lưu dạng văn bản không

Chọn ô nghi vấn. Một tam giác nhỏ màu xanh lá ở góc trên bên trái là dấu hiệu Excel đang cảnh báo số được lưu dạng văn bản. Để kiểm tra chi tiết hơn, đoạn VBA này cho bạn biết chính xác kiểu dữ liệu của ô:

Sub CheckCellType()
    Dim c As Range
    Set c = ActiveCell
    MsgBox "Type: " & TypeName(c.Value) & ", IsText: " & (c.NumberFormat = "@")
End Sub

Bước 3 — Tìm khoảng trắng ẩn

Bên cạnh ô nghi vấn, chạy công thức này ở cột trống:

=LEN(A1)

Ô thực sự trống sẽ trả về 0. Nếu bạn thấy 1 hoặc cao hơn, có gì đó đang ẩn — một dấu cách, khoảng trắng không ngắt dòng, hoặc ký tự xuống dòng thừa từ dữ liệu nhập vào.

Các Cách Sửa Lỗi

Cách 1 — Chuyển văn bản thành số bằng VALUE()

Có "123" đang nằm trong ô dưới dạng văn bản? Bọc nó lại:

=VALUE(A1) + B1

Không muốn dùng hàm? Các thủ thuật toán học này ép văn bản-số trực tiếp:

=A1 * 1
=A1 + 0
=--A1

Dấu trừ kép (--) là cách viết tắt phổ biến nhất. Nó đảo giá trị thành âm rồi đảo lại, ép chuyển đổi kiểu dữ liệu như một tác dụng phụ.

Cách 2 — Loại bỏ khoảng trắng ẩn bằng TRIM() và CLEAN()

TRIM() xóa khoảng trắng đầu, cuối và khoảng trắng đôi. CLEAN() mạnh hơn — nó loại bỏ các ký tự không in được như ký tự xuống dòng len lỏi vào từ dữ liệu nhập. Kết hợp cả hai:

=VALUE(TRIM(CLEAN(A1)))

Một trường hợp ngoại lệ: khoảng trắng không ngắt dòng (ASCII 160, phổ biến trong dữ liệu cào từ web) sẽ thoát được TRIM(). Bạn cần SUBSTITUTE() cho trường hợp này:

=SUBSTITUTE(A1, CHAR(160), "")

Cách 3 — Sửa ngày tháng định dạng văn bản

Ngày nhập dưới dạng chuỗi như "2024-01-15" cần DATEVALUE() để trở thành giá trị ngày thực:

=DATEVALUE(A1)

Sau đó định dạng ô kết quả thành kiểu Ngày tháng. Với ngày kiểu Mỹ ("01/15/2024"), thêm hàm TEXT() bao ngoài:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))

Còn một mẹo không cần công thức: chọn cột đó, vào Dữ liệu → Văn bản thành Cột, rồi nhấn Hoàn tất mà không thay đổi gì. Excel phân tích lại các giá trị khi thoát và thường tự sửa ngày dạng văn bản.

Cách 4 — Dùng IFERROR() để ẩn lỗi trong khi debug

=IFERROR(A1 + B1, 0)
=IFERROR(VLOOKUP(D1, A:B, 2, 0), "Không tìm thấy")

Hàm này trả về giá trị dự phòng thay vì hiển thị lỗi. Hữu ích để bỏ chặn các ô phụ thuộc trong khi bạn tìm nguyên nhân gốc. Đừng để nó là bản sửa cố định — nó che giấu vấn đề thực sự.

Cách 5 — Xử lý SUM() an toàn khi có ô văn bản

SUM() tự động bỏ qua các ô văn bản. Toán tử + thì không:

-- Báo lỗi nếu ô nào đó chứa văn bản:
=A1 + A2 + A3

-- Bỏ qua ô văn bản một cách an toàn:
=SUM(A1:A3)

-- Cũng bỏ qua ô lỗi (hữu ích cho dữ liệu lộn xộn):
=AGGREGATE(9, 6, A1:A3)

Chỉ cần đổi từ + sang SUM() thôi đã giải quyết được một số lượng đáng ngạc nhiên các lỗi #VALUE!.

Cách 6 — Dán Đặc Biệt để chuyển đổi cả cột

Cần chuyển đổi hàng loạt một cột văn bản-số? Không cần viết công thức:

Nhập 1 vào một ô trống bất kỳ và sao chép
Chọn cột văn bản-số
Chuột phải → Dán Đặc Biệt → Nhân
Excel nhân từng ô với 1, chuyển đổi văn bản thành số thực ngay tại chỗ

Xác Nhận Đã Sửa Xong

Sau khi áp dụng cách sửa, một vài kiểm tra nhanh để xác nhận đã thực sự hoạt động:

Lỗi #VALUE! đã biến mất và hiển thị kết quả thực
Số căn phải trong ô (văn bản mặc định căn trái)
=ISNUMBER(A1) trả về TRUE
=ISTEXT(A1) trả về FALSE với những gì phải là số

Công thức chẩn đoán này phân loại từng ô trong một lần:

=IF(ISNUMBER(A1), "Số", IF(ISTEXT(A1), "Văn bản", IF(ISBLANK(A1), "Trống", "Khác")))

Tài Liệu Tham Khảo

Microsoft Support — Cách sửa lỗi #VALUE!
Các hàm Excel: IFERROR, ISERROR, VALUE, TRIM, CLEAN, DATEVALUE
Power Query như một phương án thay thế để sửa hàng loạt kiểu dữ liệu khi nhập