6 lỗi Công thức #VALUE! Excel thường gặp và Cách sửa

Nếu bạn thường xuyên làm việc với các công thức trong Microsoft Excel , chắc chắn bạn đã gặp phải lỗi #VALUE. Lỗi này có thể thực sự gây khó chịu vì nó rất chung chung. Ví dụ: việc thêm giá trị văn bản vào công thức số có thể gây ra lỗi này. Đó là vì khi bạn cộng hoặc trừ, Excel yêu cầu bạn chỉ dùng số.

Cách dễ nhất để xử lý lỗi #VALUE là luôn đảm bảo không có lỗi đánh máy trong công thức và bạn luôn dùng đúng dữ liệu. Nhưng điều này không phải lúc nào cũng thực hiện được nên trong bài viết này, mình sẽ giúp bạn tìm hiểu một số phương pháp mà bạn có thể dùng để xử lý lỗi #VALUE trong Microsoft Excel.

Điều gì gây ra lỗi #VALUE

Có một số lý do khiến lỗi #VALUE có thể xảy ra khi bạn đang dùng công thức trong Excel. Đây là một số:

1. Loại dữ liệu không mong đợi . Giả sử bạn đang dùng một công thức hoạt động với một loại dữ liệu cụ thể nhưng một ô hoặc nhiều ô trong trang tính chứa các loại dữ liệu khác nhau. Khi đó Excel sẽ không thể chạy công thức và bạn sẽ gặp lỗi #VALUE.
2. Ký tự ‘Khoảng trắng’ . Có thể bạn nhìn thấy một ô trống nhưng thực ra nó chứa ký tự khoảng trắng. Mặc dù về mặt trực quan, ô đó trống nhưng Excel sẽ nhận ra khoảng trống và sẽ không thể xử lý công thức.
3. Character vô hình . Tương tự như dấu cách, các ký tự vô hình có thể gây ra sự cố. Một ô có thể chứa các ký tự bị ẩn hoặc không in được, ngăn cản việc tính toán công thức.
4. Cú pháp công thức sai . Nếu bạn thiếu một phần của công thức hoặc bạn đặt nó sai thứ tự thì đối số của hàm sẽ không chính xác. Điều đó có nghĩa là Excel sẽ không thể nhận dạng và xử lý công thức đó.
5. Format ngày sai . Nếu bạn đang làm việc với ngày tháng nhưng chúng được nhập dưới dạng văn bản thay vì số, Excel sẽ khó hiểu giá trị của chúng. Đó là vì ngày tháng sẽ được chương trình coi là chuỗi văn bản thay vì ngày hợp lệ.
6. Kích thước phạm vi không tương thích . Nếu công thức cần tính toán nhiều phạm vi tham chiếu đến các kích thước hoặc hình dạng khác nhau thì công thức đó sẽ không thể thực hiện được.

Khi tìm thấy nguyên nhân gây ra lỗi #VALUE, bạn sẽ có thể quyết định cách khắc phục lỗi đó. Bây giờ chúng ta hãy xem xét từng trường hợp cụ thể và tìm hiểu cách loại bỏ lỗi #VALUE.

Sửa lỗi #VALUE do kiểu dữ liệu không hợp lệ

Một số công thức Microsoft Excel được thiết kế để chỉ hoạt động với một loại dữ liệu nhất định. Nếu bạn nghi ngờ đây là nguyên nhân gây ra lỗi #VALUE trong trường hợp của mình, bạn sẽ phải đảm bảo không có ô nào được tham chiếu dùng loại dữ liệu không chính xác.

Ví dụ: bạn đang dùng công thức tính toán các con số. Nếu có một chuỗi văn bản trong một trong các ô được tham chiếu thì công thức sẽ không hoạt động. Thay vì kết quả, bạn sẽ thấy lỗi #VALUE trong ô trống đã chọn.

Ví dụ hoàn hảo là khi bạn đang cố gắng thực hiện một phép tính toán đơn giản như phép cộng hoặc phép nhân và một trong các giá trị không phải là số.

Có một số cách để khắc phục lỗi này:

• Nhập thủ công các số còn thiếu.
• Dùng hàm Excel bỏ qua chuỗi văn bản.
• Viết câu lệnh IF.

Trong ví dụ trên, chúng ta có thể dùng hàm PRODUCT: =PRODUCT(B2,C2).

Hàm này sẽ bỏ qua các ô có khoảng trống, kiểu dữ liệu không chính xác hoặc giá trị logic. Nó sẽ cho bạn kết quả như thể giá trị được tham chiếu được nhân với 1.

Bạn cũng có thể xây dựng câu lệnh IF để nhân hai ô nếu cả hai ô đều chứa giá trị số. Nếu không, lợi nhuận sẽ bằng không. Dùng như sau:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2*C2,0)

Sửa lỗi #VALUE do dấu cách và ký tự ẩn gây ra

Một số công thức không thể hoạt động nếu một số ô chứa đầy các ký tự hoặc khoảng trắng bị ẩn hoặc không nhìn thấy được. Mặc dù nhìn bề ngoài thì các ô này trông trống nhưng chúng có thể chứa khoảng trắng hoặc thậm chí là ký tự không in được. Excel coi khoảng trắng là ký tự văn bản và như trong trường hợp các loại dữ liệu khác nhau, điều này có thể gây ra lỗi #VALUE Excel.

Trong ví dụ trên, các ô C2, B7 và B10 có vẻ trống nhưng chúng chứa một số khoảng trắng gây ra lỗi #VALUE khi mình cố gắng nhân chúng.

Để khắc phục lỗi #VALUE, bạn phải đảm bảo các ô trống. Chọn ô và nhấn phím DELETE trên bàn phím để xóa mọi ký tự hoặc dấu cách vô hình.

Bạn cũng có thể dùng hàm Excel bỏ qua các giá trị văn bản. Một trong số đó là hàm SUM:

=SUM(B2:C2)

Sửa lỗi #VALUE do phạm vi không tương thích

Nếu bạn đang dùng các hàm chấp nhận nhiều phạm vi trong đối số của chúng thì sẽ không hoạt động nếu các phạm vi đó không có cùng kích thước và hình dạng. Nếu đúng như vậy, công thức sẽ dẫn đến lỗi #VALUE. Khi bạn thay đổi phạm vi tham chiếu ô, lỗi sẽ biến mất.

Ví dụ: bạn đang dùng hàm FILTER và bạn đang cố gắng lọc phạm vi ô A2:B12 và A3:A10. Nếu bạn dùng công thức =FILTER(A2:B12,A2:A10=”Milk”), bạn sẽ gặp lỗi #VALUE.

Bạn sẽ cần thay đổi phạm vi thành A3:B12 và A3:A12. Bây giờ phạm vi có cùng kích thước và hình dạng, hàm FILTER sẽ không gặp vấn đề gì khi tính toán.

Sửa lỗi #VALUE do format ngày không chính xác

Microsoft Excel có thể nhận dạng các format ngày khác nhau. Tuy nhiên, bạn có thể đang dùng format mà Excel không thể nhận ra là giá trị ngày. Trong trường hợp đó, nó sẽ coi nó là một chuỗi văn bản. Nếu bạn thử dùng những ngày này trong công thức, chúng sẽ trả về lỗi #VALUE.

Cách duy nhất để giải quyết vấn đề này là chuyển đổi format ngày không chính xác sang format đúng.

Sửa lỗi #VALUE do cú pháp công thức sai

Nếu bạn đang dùng cú pháp công thức sai trong khi cố gắng thực hiện các phép tính thì kết quả sẽ là lỗi #VALUE. May mắn thay, Microsoft Excel có Công cụ kiểm tra sẽ giúp bạn về các công thức. Bạn sẽ tìm thấy chúng trong nhóm Kiểm tra Công thức trên dải băng. Đây là cách dùng chúng:

• Chọn ô có công thức trả về lỗi #VALUE.
• Mở tab Formulas  trong ribbon.

• Trong nhóm Formula Auditing , hãy tìm và chọn Error Checking, hoặc Evaluate Formula .

Excel sẽ phân tích công thức bạn đã dùng trong ô cụ thể đó và nếu tìm thấy lỗi cú pháp, nó sẽ được đánh dấu. Lỗi cú pháp được phát hiện có thể được sửa chữa dễ dàng.

Ví dụ: nếu bạn đang dùng =FILTER(A2:B12,A2:A10=”Milk”) thì bạn sẽ nhận được lỗi #VALUE vì các giá trị phạm vi không chính xác. Để tìm ra vấn đề nằm ở đâu trong công thức, hãy nhấp vào Kiểm tra lỗi và đọc kết quả từ hộp thoại.

Sửa cú pháp công thức để đọc =FILTER(A2:B12,A2:A12=”Milk”) và bạn sẽ sửa được lỗi #VALUE.

Sửa lỗi #VALUE trong hàm XLOOKUP và VLOOKUP trong Excel

Nếu bạn cần tìm kiếm và truy xuất dữ liệu từ trang tính Excel hoặc sổ làm việc, bạn sẽ thường dùng hàm XLOOKUP hoặc hàm kế nhiệm hiện đại của nó là hàm VLOOKUP . Các hàm này cũng có thể trả về lỗi #VALUE trong một số trường hợp.

Nguyên nhân phổ biến nhất gây ra lỗi #VALUE trong XLOOKUP là kích thước không thể so sánh được của mảng trả về. Nó cũng có thể xảy ra khi mảng LOOKUP lớn hơn hoặc nhỏ hơn mảng trả về.

Ví dụ: nếu bạn đang dùng công thức: =XLOOKUP(D2,A2:A12,B2:B13), kết quả trả về sẽ là lỗi #VALUE vì mảng tra cứu và trả về chứa số hàng khác nhau.

Điều chỉnh công thức để đọc: =XLOOKUP(D2,A2:A12,B2:B12).

Dùng hàm IFERROR hoặc IF để giải quyết lỗi #VALUE

Có những công thức bạn có thể dùng để xử lý lỗi. Khi gặp lỗi #VALUE, bạn có thể dùng hàm IFERROR hoặc kết hợp hàm IF và ISERROR.

Ví dụ: bạn có thể dùng hàm IFERROR để thay thế lỗi #VALUE bằng một tin nhắn văn bản có ý nghĩa hơn. Giả sử bạn muốn tính ngày đến trong ví dụ bên dưới và bạn muốn thay thế lỗi #VALUE do format ngày không chính xác gây ra bằng thông báo “Check the date”.

Bạn sẽ dùng công thức sau: =IFERROR(B2+C2,”Check the date”).

Trong trường hợp không có lỗi, công thức trên sẽ trả về kết quả của đối số đầu tiên.

Điều tương tự có thể đạt được nếu bạn dùng kết hợp công thức IF và ISERROR:

=IF(ISERROR(B2+C2),”Kiểm tra ngày”,B2+C2).

Công thức này trước tiên sẽ kiểm tra xem kết quả trả về có lỗi hay không. Nếu đó là lỗi, nó sẽ dẫn đến đối số đầu tiên (Check the date) và nếu không, nó sẽ dẫn đến đối số thứ hai (B2+C2).

Hạn chế duy nhất của hàm IFERROR là nó sẽ bắt được tất cả các loại lỗi chứ không chỉ lỗi #VALUE. Nó sẽ không tạo ra sự khác biệt giữa các lỗi như lỗi #N/A , #DIV/0, #VALUE hoặc #REF.

Excel, với vô số chức năng và tính năng, mang đến khả năng vô tận để quản lý và phân tích dữ liệu. Thấu hiểu và chinh phục lỗi #VALUE! trong Microsoft Excel là một kỹ năng quan trọng trong thế giới thuật toán bảng tính. Những trục trặc nhỏ này có thể khiến bạn khó chịu nhưng được trang bị kiến ​​thức và kỹ thuật từ bài viết này, bạn đã sẵn sàng để khắc phục và giải quyết chúng.