Lỗi VALUE trong Excel là lỗi thường gặp trong Excel và nếu bạn chưa biết cách khắc phục. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về lỗi VALUE trong Excel cũng như cách khắc phục khi gặp phải lỗi này nhé.
Mục Lục
Khi sử dụng bảng tính Excel, thi thoảng bạn sẽ thấy lỗi #VALUE xuất hiện. Điều này có nghĩa là giá trị kết quả của bạn bị lỗi trong quá trình tính toán.
Có rất nhiều nguyên nhân xuất hiện lỗi #VALUE, nhưng đa phần đều là do bạn gặp lỗi trong quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.
Bạn có thể dễ mắc lỗi #VALUE khi sử dụng các hàm như sau: VLOOKUP, FIND, AVERAGE, SUM, SEARCH, TRANSPOSE…
Tìm hiểu thêm: Lỗi word bị cách chữ
Cách sửa lỗi #value trong Excel
Lỗi #value trong hàm Sum, Average kết hợp hàm Iseror + IF hoặc Iferror
Trong vùng tính tổng và cách tính trung bình có chứa lỗi #Value trong excel. Cách khắc phục lỗi #Value: Dùng công thức trong mảng kết hợp với 2 nhóm hàm: If + Isseror hoặc Iferror. Chẳng hạn, ta cần tính tổng số lượng bán được của tất cả các mặt hàng, cách thực hiện như sau:
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.
Loại bỏ lỗi VALUE khi thực hiện tính toán, dùng hàm IF, ISERROR hoặc Iferror hoặc định dạng có điều kiện.
Trong ví dụ trên, các mặt hàng đều có giá trị giống nhau là 200 và dựa trên số lượng thì ta sẽ tính được doanh thu một cách đơn giản như sau: Nhưng, nếu ta nộp cho sếp một bản báo cáo với lỗi value như hình trên là không ổn một chút nào.
Xem thêm: Lỗi dính chữ trong word
Khi đó, bạn hãy nghĩ tới 1 trong 2 phương án như sau:
Bẫy lỗi để cứ mặt hàng nào bị lỗi số lượng thì trả về kết quả giá trị là 0.
Như vậy báo cáo sẽ nhìn ổn hơn rất nhiều. Công thức tính doanh thu lúc đó như sau: C2= IF(ISERROR(B2),0,B2*200) hay C2= IFERROR(B2*100,0)
Kết quả ta thu được như sau: Cột doanh thu đã ổn sau đó các bạn, nhưng bản báo cáo vẫn chưa thực sự tốt khi mà cột số lượng vẫn còn xuất hiện trong ô chứa lỗi value. Và giải pháp thứ 2 là điều bạn cần biết.
Sử dụng conditional formatting để ẩn mọi 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 Value ở một số hàm Excel khác
Lỗi #VALUE! khi sử dụng hàm DAYS
Khi thực hiện hàm DAY thì một số thời gian sẽ báo lỗi value trong excel, một vài thời gian sẽ cho ra kết quả. Lỗi excel này là do định dạng ngày tháng trong máy tính với lỗi Excel không đồng nhất, dẫn tới các tình trạng trên. Chính vì thế bạn cần thay đổi định dạng ngày tháng trên hệ thống. Báo lỗi value trong Excel
Bước 1: Đầu tiên bạn hãy truy cập vào Control Panel sau đó nhấn chọn vào phần Clock, Language, and Region. Để chỉnh sửa ngày tháng trong phần mềm Excel. Chuyển sang giao diện mới theo phần Region click tiếp vào mục Change date, time, or number formats.
Bước 2: Xuất hiện tại bảng thay đổi các định dạng như là: ngày tháng trong máy tính. Tại phần Language preferences phần Short date và Long date người sử dụng điều chỉnh về kiểu hiển thị như hình. Cuối cùng nhấn vào Apply để có thể lưu lại thay đổi đó.
Bước 3: Quay lại Excel rồi nhập lại thời gian và thực hiện các hàm ngày tháng, sẽ thấy kết quả chính xác, không báo lỗi như trước đây.