Lỗi #VALUE Trong Excel – Cách Khắc Phục Đơn Giản Và Hiệu Quả

Trong quá trinh làm việc với Excel chắc hẳn có lúc các bạn sẽ gặp phải lỗi #VALUE! mà các bạn không biết sửa lỗi này như thế nào. Có rất nhiều nguyên nhân dẫn đến lỗi, và trong bài viết dưới đây Hoamitech.com sẽ đưa ra một số lỗi #VALUE! hay gặp nhất trong Excel và cách khắc phục những lỗi đó. Cùng theo dõi nhé!

Lỗi #VALUE! trong Excel 

Lỗi #VALUE! là lỗi hiển thị giá trị kết quả bị lỗi trong quá trình tính toán.

Lỗi #VALUE! trong Excel thường có nhiều nguyên nhân. Phần lớn thường gặp là do quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.

Các hàm có thể dễ mắc lỗi #VALUE! như: AVERAGE, SUM, TRANSPOSE, VLOOKUP, SUMPRODUCT, FIND, SEARCH,…

Nguyên nhân và cách khắc phục lỗi VALUE

Lỗi #VALUE! do có khoảng trắng giữa các giá trị

Nguyên nhân: Khi nhập công thức hàm quét phải ô trống có chứa dữ liệu ẩn mà không thấy.

Ví dụ: Như hàm SUM ở hình minh họa.

Cách khắc phục: Tìm và loại bỏ khoảng trắng. Sau đó, kiểm tra lại công thức hàm. Ở đây mình hướng dẫn cụ thể qua các bước minh họa.

Bước 1: Quét chọn vùng dữ liệu bảng tính bị lỗi. Sau đó di chuyển con trỏ chuột lên thanh tiêu đề chọn DATA và chọn tiếp vào Filter.

Bước 2: Nhấp chuột vào dấu mũi tên góc phải bên dưới ô dữ liệu đầu tiên. Sau đó chọn (Blanks) và nhấn OK. Các ô chứa khoảng trống sẽ xuất hiện.

Bước 3: Loại bỏ khoảng trắng và kiểm tra lại hàm. Excel sẽ hiển thị kết quả như hình.

Lỗi do có các ký tự đặc biệt

Nguyên nhân: Khi bạn thao tác nhập lệnh có ô dữ liệu chứa phải ký tự.

Ví dụ: Như hàm SUM ở hình minh họa mình đã chọn phải dãy tham chiếu chứa ô ký tự nên Excel báo lỗi #VALUE! như hình.

Cách khắc phục: Tìm và loại bỏ ký tự. Sau đó, kiểm tra lại công thức hàm. Ở đây mình hướng dẫn cụ thể qua các bước minh họa.

Bước 1: Bạn tạo cột kiểm tra (Test) bên cột giá trị tính toán bằng hàm ISTEXT. Ở đây mình tạo với ô tham chiếu E2.

Bước 2: Bạn nhấn phím Enter và dùng trỏ chuột kéo kết quả vừa hiển thị xuống tới cuối ô dữ liệu muốn kiểm tra để sao chép. Ở đây mình kéo xuống đến ô F6.

Bước 3: Bạn kiểm tra và chỉnh sửa lại kết quả theo chữ TRUE. Excel sẽ hiển thị kết quả như hình.

Lỗi #VALUE! khi sử dụng các dấu toán học (+, *)

Nguyên nhân: Thường gặp khi sử dụng toán tử là cộng từng con số.

Ví dụ: Như trong hình minh họa, mình nhập cộng từng ô dữ liệu với nhau.

Sau khi nhập công thức như hình trên thì Excel báo lỗi #VALUE!

Cách khắc phục: Bạn sử dụng hàm SUM quét chọn vùng dữ liệu cần tính. Ở đây mình thêm vào công thức hàm SUM là =SUM(E2:E6) nên Excel sẽ hiển thị kết quả như hình minh họa.

Cách khắc phục lỗi trong Excel khi sử dụng hàm 

Lỗi #value trong hàm Sum, Average kết hợp hàm Iseror + IF hoặc Iferror

Cách 1:
={SUM (IFERROR(B2:B5,””))} => Kết quả trả về là: 680

Lưu ý: Công thức này chỉ áp dụng cho excel 2007 trở lên còn đối với excel 2003 thì phải dùng theo cách 2 dưới đây.
Sau khi nhập công thức sum các bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter.

Cách 2: ={SUM(IF(ISERROR(B2:B5),””,B2:B5))} => Kết quả trả về là: 680. Logic tính toán của công thức: Hàm if và hàm Iserror sẽ xác định xem giá trị nào trong vùng B2:B5 là giá trị lỗi excel sẽ trả về kết quả là trống. Như vậy các giá trị để tính tổng sau cùng của hàm sum trong excel sẽ loại bỏ được toàn bộ giá trị lỗi. Tương tự như vậy thì ta có thể thay thế cú pháp hàm Sum bằng với hàm Average như sau:
={AVERAGE(IFERROR(B2:B5,””))} = 227 hay ={AVERAGE(IF(ISERROR(B2:B5),””,B2:B5))} = 227

Gặp lỗi #VALUE khi dùng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS sang một file khác bị đóng

Đây chính là trường hợp phổ biến mà ta thường dùng hàm để tính tổng, hàm đếm có điều kiện trên một file khác. Tại thời điểm mà ta lập các công thức này thì cả file tính toán lẫn file chứa dữ liệu tính toán đều được mở nên không thấy lỗi value gì xuất hiện.

Tuy nhiên khi ta tắt file đi và chỉ mở mỗi file tính toán thì bạn có thể gặp phải lỗi #Value ở tất cả các ô liên quan tới hàm SUMIF, SUMIFs, COUNTIF, COUNTIFs trong Excel. Giải pháp duy nhất đến hiện tại trong lỗi Value là: Mở cùng một lúc cả 2 file sau đó nhấn lại F9 (nếu cần) để lỗi value trong excel cập nhật lại công thức.

Sử dụng conditional formatting để ẩn lỗi trong excel

Thay vì phải dùng công thức ở cột doanh thu, ở cách này ta chỉ cần nhập công thức doanh thu = số lượng * đơn giá. Sau đó ta sẽ bôi đen các vùng dữ liệu cần ẩn lỗi rồi chọn Conditional formatting > New Rules.

Trong bảng thiết lập thông số cho việc định dạng có điều kiện thì bạn nhập trong phần Use a formula to determines which cells to format như sau:

• Công thức để xác định ô được định dạng: Iserror(B2)
• Formating: Bạn chọn màu chữ là Trắng

Lời kết 

Trên đây là bài viết về cách khắc phục lỗi #VALUE! trong Excel mà Hoamitech.com đã tổng hợp và muốn chia sẻ đến bạn. Hy vọng bài viết đã giúp bạn hiểu rõ hơn cũng như phần nào giúp bạn biết được cách khắc phục lỗi #VALUE! này trong Excel. Nếu bạn có bất kỳ thắc mắc gì, hãy để lại bình luận phía dưới nhé!

Hồng Nhung – Tổng hợp và Edit