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é.

Mục đích sử dụng hàm logic IF là gì?

Dùng hàm IF, một trong các hàm logic để trả về một giá trị nếu điều kiện đúng và trả về giá trị khác nếu điều kiện sai. Ví dụ: =IF(A2>B2,"Vượt dự toán","OK") =IF(A2=B2,B4-A4,"")

Hàm if tối đa bao nhiêu điều kiện?

Vui lòng lưu ý rằng hàm IFS cho phép bạn kiểm tra tối đa 127 điều kiện khác nhau. Tuy nhiên, chúng tôi không khuyến khích bạn lồng quá nhiều điều kiện với câu lệnh IF hoặc IFS. Điều này là do nhiều điều kiện cần được nhập theo đúng thứ tự và có thể rất khó để xây dựng, kiểm tra và cập nhật.

Tại sao Dung hàm Iferror?

Bạn có thể dùng hàm IFERROR để tìm và xử lý các lỗi trong công thức. IfERROR trả về một giá trị do bạn chỉ định nếu một công thức định trị lỗi; nếu không, nó trả về kết quả của công thức.

Hàm if có cú pháp như thế nào?

Hàm IF trong Excel có công thức thế nào Hàm IF trong Excel có công thức là \=IF(logical_test,[value_if_true],[value_if_false]). Trong đó: logical_test: Điều kiện bạn muốn kiểm tra (bắt buộc). value_if_true: Giá trị bạn muốn trả về nếu kết quả của logical_test là ĐÚNG/TRUE (bắt buộc).