Lỗi #VALUE trong excel và cách sửa ở Hàm IF, Sumif, Vlookup, Average,…

Nguồn bài viết: Lỗi #VALUE trong excel và cách sửa ở Hàm IF, Sumif, Vlookup, Average,…

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

Hy vọng nó sẽ giúp ích cho các bạn để bài báo cáo của mình trở nên chuyên nghiệp hơn.

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

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

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

Và #Value là một lỗi rất chung chung nên rất khó để biết ngay nguyên nhân gây ra lỗi.

Để tìm ra sai sót, chúng ta phải có các phương pháp và cách thức kiểm tra.

Trong bài viết này, chúng ta hãy thử xử lý một số trường hợp thường gặp với Trường.

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 giá trị trong hàm Sum, Average kết hợp Iseror + IF hoặc Iferror

Lý do:

Trong vùng tổng, giá trị trung bình chứa lỗi #Value. Tham khảo hình sau:

Làm thế nào để khắc phục:

Sử dụng công thức mảng kết hợp với 2 nhóm hàm: Nếu + Cấp phát hoặc Lệnh iferror

Ví dụ, chúng ta cần tính tổng doanh thu của tất cả các mặt hàng, cách làm như sau:

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

Ghi chú:

+ Công thức này chỉ áp dụng cho excel 2007 trở lên, đối với excel 2003 bạn phải sử dụng cách 2 bên dưới.

+ Sau khi nhập công thức tính tổng, bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter. Vì đây là công thức mảng.

= {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 có giá trị nào trong phạm vi B2: B5 là giá trị lỗi hay không, khi đó excel sẽ trả về kết quả trống. Như vậy, các giá trị để tính tổng cuối cùng của hàm sum sẽ loại bỏ tất cả các giá trị lỗi.

Tương tự, chúng 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

Bạn có thể tải file excel mẫu ở cuối bài viết này.

2.2. Gặp lỗi #VALUE khi sử dụng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS cho một tệp khác bị đóng

Đây là trường hợp thường gặp khi chúng ta sử dụng hàm sum, hàm đếm có điều kiện trên tệp tin khác.

Tại thời điểm chúng ta thực hiện các công thức này, cả tệp tính toán và tệp chứa dữ liệu tính toán đều đang mở nên không có lỗi nào xuất hiện.

Nhưng khi chúng ta tắt tệp và chỉ mở tệp tính toán, bạn có thể gặp lỗi #VALUE trong tất cả các ô liên quan đến các hàm SUMIF, SUMIFs, COUNTIF, COUNTIFs.

Giải pháp duy nhất cho đến nay là:

Mở cùng lúc cả hai tệp rồi nhấn F9 lần nữa (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, sử 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 vào số lượng, chúng ta sẽ tính doanh thu đơn giản như sau:

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

Sau đó, hãy nghĩ đến một trong hai tùy chọn sau:

a / Bẫy lỗi sao cho mọi mặt hàng có sai số về số lượng, kết quả bằng 0.

Như vậy tờ báo sẽ đẹp 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ả chúng tôi nhận được như sau:

Cột doanh thu thì ổn nhé các bạn, nhưng báo cáo vẫn chưa thực sự ổn khi cột số lượng vẫn hiển thị ô lỗi #VALUE. Và giải pháp thứ 2 là thứ bạn cần.

b / Sử dụng định dạng có điều kiện để ẩn tất cả các lỗi trong excel

Thay vì phải sử dụng công thức ở cột doanh thu, với cách này chúng ta chỉ cần nhập công thức doanh thu = số lượng * đơn giá.

=> Xem thêm: Tóm tắt Tất cả về Định dạng có Điều kiện – Định dạng có Điều kiện

Sau đó, chúng tôi đánh dấu vùng dữ liệu để ẩn lỗi và sau đó chọn Định dạng có điều kiện> Quy tắc mới.

Trong bảng cài đặt tham số cho định dạng có điều kiện, bạn nhập vào Sử dụng công thức để xác định ô cần định dạng như hình bên dưới:

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

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

Báo cáo bán hàng của bạn về cơ bản đã hoàn thành và ở trạng thái tốt.

Những thủ thuật này bạn sẽ không học được trong các trường học chính thức, nhưng chúng sẽ rất hữu ích trong quá trình thực hành của bạn.

Chúng tôi hy vọng rằng bạn sẽ thích nó.

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

Những bài viết liên quan : Thủ Thuật Exel

Mọi sự sao chép, trích dẫn vui lòng ghi rõ nguồn: congthucexcel.com

from congthucexcel.com https://ift.tt/MFhulDC
via Congthucexcel.com