Lỗi #VALUE trong excel là gì? Trường sẽ hướng dẫn các bạn cách sửa lỗi value trong Hàm IF, Sumif, Sumifs Vlookup, Average, …

Rất mong rằng sẽ giúp ích được cho bạn làm báo cáo chuyên nghiệp hơn.

1. Lỗi VALUE trong excel là gì?

Khi viết hàm trong excel, Bạn nhận được các thông báo lỗi #Value, #Ref,… ta có thể hiểu đó là:

Phần thông báo của excel cho ta biết được rằng công thức bạn vừa nhập đã bị lỗi ở đâu đó: Sai cú pháp, tham chiếu có vấn đề,…

Và #Value là một lỗi rất chung chung do vậy khó biết được ngay lập tức lỗi là do đâu.

Để tìm ra lỗi thì ta phải có những thủ thuật, biện pháp test (phép thử).

Trong bài viết này hãy cùng Trường thử xử lý ở một số trường hợp phổ biến nhé.

2. Cách sửa lỗi #value trong Hàm IF, Sumif, Sumifs Vlookup, Average, chứa khoảng trắng …

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

Nguyên nhân:

Trong vùng tính tổng, tính trung bình có chứa lỗi #Value. Tham khảo hình sau:

Cách khắc phục:

Dùng công thức mảng kết hợp với 2 nhóm hàm: If + Isseror hoặc Iferror

Ví dụ, 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 làm 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, đối với excel 2003 thì phải dùng 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. Vì đây là công thức mảng bạn ạ.

• 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à Iserror sẽ xác định xem giá trị nào trong vùng B2:B5 là giá trị lỗi thì 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 sẽ loại bỏ được toàn bộ giá trị lỗi.

Tương tự như vậy, ta có thể thay thế cú pháp hàm Sum bằng hàm Average như sau:

={AVERAGE(IFERROR(B2:B5,””))} = 227

={AVERAGE(IF(ISERROR(B2:B5),””,B2:B5))} = 227

Các bạn có thể download file excel mẫu ở phía cuối của bài viết này.

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

Đây là trường hợp phổ biến khi ta 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 ta lập các công thức này thì cả file tính toán và file chứa dữ liệu tính toán đều được mở nên không thấy lỗi gì xuất hiện.

Nhưng 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.

Giải pháp duy nhất đến hiện tại là:

Mở cùng một lúc cả 2 file rồi nhấn lại F9 (nếu cần) để excel cập nhật lại công thức.

2.3. Loại bỏ lỗi #VALUE khi 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á giống nhau là 200 và dựa trên số lượng ta sẽ tính được doanh thu một cách đơn giản như sau:

Tuy nhiên, nếu ta nộp cho sếp một báo cáo với lỗi toe toét như hình trên là không ổn một chút nào.

Khi đó, bạn hãy nghĩ tới 1 trong 02 phương án sau:

a/ Bẫy lỗi để cứ mặt hàng nào bị lỗi số lượng thì trả về kết quả là 0.

Như vậy bá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)

Hoặc

C2= IFERROR(B2*100,0)

Kết quả ta thu được như sau:

Cột doanh thu đã ổn rồi đó các bạn, nhưng 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 ô chứa lỗi #VALUE. Và giải pháp thứ 2 là điều bạn cần.

b/ 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á.

=> Xem thêm: Tổng hợp Tất cả về Định dạng có điều kiện – Conditional Formatting

Sau đó ta bôi đen 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 bạn nhập trong phần Use a formula to determines which cells to format như hì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

Và đây là kết quả ta đạt được: Mọi ô có kết quả là #Value sẽ không hiện lên báo cáo vì màu chữ là màu trắng bạn ạ.

Báo cáo bán hàng của bạn về cơ bản đã hoàn tất và đẹp vết mặt hình thức rồi đó bạn.

Các thủ thuật này bạn sẽ không học được ở các trường lớp chính quy, nhưng nó lại rất có ích trong việc thực tế của bạn.

Trường hi vọng rằng các bạn sẽ thấy thích thú.

Nếu thấy hay thì đừng ngại Like, Share hoặc Thả tim trên Fanpage của CLB Excel Word Powerpoint bạn nhé.