9 Cách sửa lỗi #VALUE! trong Excel chi tiết và hiệu quả 2024

Microsoft Excel là một phần mềm bảng tính phổ biến và mạnh mẽ, đồng thời là công cụ tuyệt vời để lưu trữ và thao tác dữ liệu. Tuy nhiên, việc gặp lỗi #VALUE! trong Excel là điều khá thường gặp đối với người dùng. Hãy cùng chúng tôi khám phá nguyên nhân gây ra lỗi này và các cách khắc phục nhanh chóng.

Lỗi #VALUE! trong Excel là gì?

Việc xảy ra lỗi là điều không thể tránh khỏi trong quá trình sử dụng công thức và hàm Excel. Lỗi #VALUE! là một trong số các lỗi thường gặp nhất đối với người dùng. 

Báo lỗi #VALUE! trong Excel xuất hiện khi một giá trị không phải là loại giá trị mong đợi. Điều này có thể xảy ra khi các ô được để dấu khoảng trắng, khi một hàm mong đợi một số cụ thể nhưng cho ra một giá trị văn bản, hoặc khi ngày tháng được Excel đánh giá là văn bản. 

Lỗi #VALUE! thường khá phức tạp vì một số hàm tự động bỏ qua dữ liệu không hợp lệ. Chẳng hạn, hàm SUM chỉ bỏ qua các giá trị văn bản nhưng phép cộng hoặc trừ thông thường với toán tử cộng (+) hoặc trừ (-) sẽ trả về lỗi #VALUE! nếu có bất kỳ giá trị nào là văn bản.

Các ví dụ dưới đây sẽ giúp bạn hình dung rõ hơn về vấn đề này để từ đó có cách xử lý lỗi #VALUE! trong Excel phù hợp.

• Ví dụ 1: Giá trị văn bản không mong muốn

Trong ví dụ bên dưới, ô C3 có chứa văn bản “NA” và F2 trả về lỗi #VALUE!:

• Ví dụ 2: Ký tự khoảng trắng sai

Đôi khi, một ô có một hoặc nhiều ký tự khoảng trắng bị sai sẽ đưa ra lỗi #VALUE! trong Excel, được minh họa như hình bên dưới:

• Ví dụ 3: Loại đối số hàm không như mong đợi

Lỗi #VALUE! trong Excel cũng có thể phát sinh khi các đối số của hàm không phải là loại được mong đợi. Trong ví dụ dưới đây, hàm NETWORKDAYS được thiết lập để tính số ngày làm việc giữa hai ngày. Trong ô C3, “apple” không phải là ngày hợp lệ, vì vậy hàm NETWORKDAYS không thể tính ngày làm việc và trả về lỗi #VALUE!:

• Ví dụ 4: Ngày được lưu dưới dạng văn bản

Đôi khi một bảng tính sẽ chứa ngày tháng không hợp lệ vì chúng được lưu trữ dưới dạng văn bản. Trong ví dụ bên dưới, hàm EDATE được sử dụng để tính ngày hết hạn 3 tháng sau khi mua. Công thức ở C3 trả về lỗi #VALUE! trong Excel vì ngày B3 được lưu dưới dạng văn bản (tức là không được nhận dạng chính xác dưới dạng ngày):

Các nguyên nhân chính tạo ra lỗi #VALUE! trong Excel

Phần giới thiệu ở trên đã điểm qua một số nguyên nhân có thể dẫn đến lỗi #VALUE! trong Excel. Các lỗi này mang tính chung chung và có thể xảy ra đối với bất kỳ hàm hoặc công thức nào. Hãy cùng chúng tôi điểm lại những nguyên nhân chính giải thích cho sự xuất hiện của lỗi #VALUE! trong Excel:

• Loại dữ liệu không mong đợi: Giả sử bạn đang sử dụng một công thức hoạt động với một kiểu dữ liệu cụ thể, nhưng một ô hoặc nhiều ô trong trang tính có 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 báo lỗi #VALUE!.
• Ký tự khoảng trắng: Có thể bạn nhìn thấy một ô trống nhưng thực ra nó có 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.
• Ký tự 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ố lỗi #VALUE! trong Excel. 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.
• Cú pháp công thức không chính xác: Bạn có thể gặp lỗi #VALUE! trong Excel phép nhân, hàm VLOOKUP hoặc nhiều công thức khác nếu không nhập chính xác công thức, đặt sai thứ tự. Điều đó có nghĩa là Excel sẽ không thể nhận dạng và xử lý công thức đó.
• Định dạng ngày sai: Như đã đề cập trong phần trước, 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ông thể hiểu được giá trị và cho ra lỗi ##VALUE!. Đó 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ệ.
• Kích thước phạm vi không tương thích: Nếu công thức đưa ra 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.

Hướng dẫn chi tiết các cách sửa lỗi #VALUE! trong Excel

Khi tìm thấy nguyên nhân gây ra lỗi, bạn có thể quyết định cách khắc phục lỗi #VALUE! trong Excel. Trong phần này, chúng tôi sẽ giới thiệu tới người dùng một số cách cơ bản và hiệu quả để sửa lỗi thường gặp này. 

Lỗi #VALUE! do kiểu dữ liệu không hợp lệ

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

• Ví dụ: bạn đang sử 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 xuất hiện tại một trong các ô được tham chiếu, công thức sẽ không hoạt động. Thay vì kết quả, bạn sẽ nhận lỗi #VALUE! trong Excel.

Ví dụ hoàn hảo là khi người dùng đ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 #VALUE! trong Excel này:

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

Trong ví dụ cụ thể trên đây, chúng ta có thể sử 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.

Người dùng cũng có thể xây dựng câu lệnh IF để nhân 2 ô nếu cả 2 ô đều chứa giá trị số. Nếu không, nó sẽ trả về giá trị bằng 0. Công thức sử dụng như sau: =IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2*C2,0).

Sửa lỗi #VALUE! trong Excel do dấu cách và ký tự ẩn

Một số công thức không thể hoạt động nếu một số ô chứa đầy ký tự hoặc khoảng trắng bị ẩn hoặc không nhìn thấy được. Như đã đề cập, tuy nhìn bề ngoài thì các ô này trông như để 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 xem 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! trong Excel.

Để 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ể sử 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! trong Excel do phạm vi không tương thích

Phạm vi không tương thích là một trong những nguyên nhân phổ biến dẫn đến lỗi #VALUE! trong Excel. 

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

Người dùng 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! trong Excel do định dạng ngày không đúng

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

Cách duy nhất để giải quyết lỗi #VALUE! trong Excel đối với tình huống này là chuyển đổi định dạng ngày không chính xác sang định dạng đúng.

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

Nếu bạn đang sử dụng sai cú pháp công thức khi cố gắng thực hiện các phép tính, thì kết quả sẽ là lỗi #VALUE! trong Excel. May mắn thay, Microsoft Excel có Auditing Tool hỗ trợ kiểm tra các công thức. Bạn sẽ tìm thấy công cụ này trong nhóm Formula Auditing ở phần ribbon. Dưới đây là cách sử dụ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 chọn Error Checking hoặc Evaluate Formula

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

Ví dụ: nếu bạn đang sử dụng =FILTER(A2:B12,A2:A10=”Milk”), lỗi #VALUE! xuất hiện do 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 Error Checking và đọc kết quả từ hộp thoại.

Sửa cú pháp công thức thành =FILTER(A2:B12,A2:A12=”Milk”) và bạn đã khắc phục được lỗi #VALUE! trong Excel.

Lỗi #VALUE! trong Excel với các hàm VLOOKUP và XLOOKUP

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

Nguyên nhân phổ biến nhất gây ra lỗi #VALUE! trong Excel với hàm VLOOKUP 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 mảng trả về.

• Ví dụ: nếu bạn đang sử 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ề kết quả chứa số hàng khác nhau. Do đó, hãy điều chỉnh lại công thức: =XLOOKUP(D2,A2:A12,B2:B12).

Lỗi #VALUE! trong Excel với hàm SUMIF/SUMIFS

Trong ví dụ bên dưới, hàm SUMIFS với cột C8 đã tạo ra lỗi #VALUE!. Đây là di phạm vi Sum và phạm vi Criteria không khớp nhau, tức phạm vi tổng là C24 đến D48 (gồm 2 cột và 24 hàng) nhưng phạm vi tiêu chí là B24 đến B48 (gồm 1 cột và 24 hàng).

Trong trường hợp này, bạn nên sửa B24 thành A24 để đảm bảo có 2 cột và 24 hàng, khớp với phạm vi tổng. Như vậy kết quả cho ra sẽ là giá trị như mong muốn thay vì lỗi #VALUE! trong Excel như ban đầu.

Tóm lại, cách giải quyết nếu gặp lỗi #VALUE! trong Excel khi sử dụng hàm SUMIFS (và SUMIF) chính là đảm bảo phạm vi Sum và phạm vi Criteria có kích thước và hình dạng phù hợp.

Lỗi #VALUE! trong Excel với hàm COUNT/COUNTIF

Khi hàm COUNTIFS tham chiếu đến một phạm vi nằm trong một workbook khác đã bị đóng, kết quả trả về là lỗi #VALUE! thay vì số ô mà người dùng đang mong đợi nhận được.

Trong ví dụ dưới đây, thay vì vì công thức trả về số lượng sản phẩm màu đỏ, lỗi #VALUE1 xuất hiện do công thức đã tham chiếu đến một phạm vi trong workbook khác có tên Product Colours – vốn đã bị đóng.

Giải pháp cần thực hiện khi lỗi #VALUE! trong Excel xảy ra là mở workbook đã đóng rồi nhấn F9 trên bàn phím để công thức tính toán lại các tham số chính xác. Sau đó, người dùng sẽ có được số đếm chính xác như trong ví dụ dưới đây.

Sử dụng hàm IFERROR hoặc IF để giải quyết lỗi

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

• Ví dụ: bạn có thể sử 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à thay thế lỗi #VALUE! trong Excel do định dạng ngày không chính xác, bằng thông báo “Check the date”.

Bạn sẽ sử dụng công thức =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.

Nếu tất cả các phương pháp nêu trên vẫn không giúp bạn khắc phục lỗi #VALUE! trong Excel, hãy liên hệ nhận trợ giúp ngay bằng cách nhắn vào Livechat bên dưới để được nhóm chuyên viên kỹ thuật hỗ trợ.

HVN Group – với cương vị là đối tác ủy quyền của Microsoft – cung cấp cho các doanh nghiệp Việt Nam đa dạng các gói giải pháp Microsoft 365 với mức chi phí phù hợp. Hơn nữa, các chuyên gia với nhiều năm nghiên cứu về dịch vụ sẽ mang đến cho bạn cẩm nang sử dụng các ứng dụng trong hệ sinh thái Microsoft một cách hiệu quả và thông minh, trong đó bao gồm Excel. 

Tổng kết

Hy vọng các thông tin trong bài của HVN Group đã giúp bạn xác định được nguyên nhân lỗi #VALUE! trong Excel, từ đó có thể áp dụng giải pháp xử lý phù hợp. Nếu có bất kỳ câu hỏi liên quan đến nội dung nêu trên, hãy kết nối ngay với các chuyên gia của HVN – Hệ sinh thái kiến tạo doanh nghiệp 4.0 – bằng cách gọi đến số Hotline: 024.9999.7777 để được hỗ trợ giải đáp và tư vấn nhanh chóng.