Nếu bạn thường xuyên làm việc với các công thức trong Microsoft Excel, bạn chắc hẳn đã từng gặp phải lỗi #VALUE!. Lỗi này khá 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ể kích hoạt lỗi này. Đó là vì khi cộng hoặc trừ, Excel mong đợi bạn chỉ sử 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 sử dụng dữ liệu chính xác. Tuy nhiên, điều này không phải lúc nào cũng khả thi, nên trong bài viết này chúng tôi sẽ hướng dẫn bạn một số phương pháp để khắc phục lỗi #VALUE! trong Microsoft Excel.
Ngoài ra, hãy xem qua các mẹo và thủ thuật Excel yêu thích của chúng tôi để nâng cao năng suất làm việc với ít đau đầu hơn.
Nguyên nhân gây ra lỗi #VALUE!
Có một số lý do khiến lỗi #VALUE! xảy ra khi bạn sử dụng công thức trong Excel. Dưới đây là một số nguyên nhân phổ biến:
- Kiểu dữ liệu không mong đợi. Giả sử bạn đang dùng 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 bảng tính chứa các kiểu dữ liệu khác nhau. Khi đó Excel sẽ không thể thực thi công thức và bạn sẽ nhận được lỗi #VALUE!.
- Ký tự khoảng trắng. Có thể bạn thấy một ô trống, nhưng thực tế nó chứa ký tự khoảng trắng. Dù nhìn có vẻ trống, Excel vẫn nhận diện khoảng trắng và không thể xử lý công thức.
- Ký tự ẩn. Tương tự như khoảng trắng, các ký tự ẩn có thể gây ra vấn đề. Một ô có thể chứa các ký tự ẩn hoặc không in được ngăn chặn tính toán công thức.
- Cú pháp công thức không đúng. Nếu bạn thiếu một phần của công thức hoặc đặt sai thứ tự, đối số của hàm sẽ không chính xác. Điều này khiến Excel không nhận diện được công thức và không thể xử lý.
- Định dạng ngày sai. Nếu bạn 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ẽ gặp khó khăn trong hiểu giá trị của chúng. Đó là vì các ngày tháng sẽ được chương trình xử lý như chuỗi văn bản thay vì ngày tháng hợp lệ.
- Kích thước phạm vi không tương thích. Nếu công thức của bạn 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, nó sẽ không thể thực hiện được.
Khi bạn tìm ra nguyên nhân gây ra lỗi #VALUE!, bạn sẽ quyết định được cách khắc phục. Hãy cùng xem xét từng trường hợp cụ thể và học cách loại bỏ lỗi #VALUE!.
Khắc phục 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 kiểu 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 bạn, bạn phải đảm bảo rằng không có ô tham chiếu nào sử dụng kiểu dữ liệu không đúng.
Ví dụ, bạn đang dùng công thức tính toán số. Nếu có chuỗi văn bản trong một trong các ô tham chiếu, 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 được chọn.
Ví dụ điển hình là khi bạn cố gắng thực hiện phép tính toán học đơn giản như cộng hoặc 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.
- Sử 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ể sử dụng hàm PRODUCT: =PRODUCT(B2,C2).
Hàm này sẽ bỏ qua các ô chứa khoảng trống, kiểu dữ liệu không đúng hoặc giá trị logic. Nó sẽ cho kết quả như thể giá trị 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, kết quả trả về sẽ là 0. Sử dụng công thức sau:
=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2*C2,0)
Khắc phục lỗi #VALUE! do khoảng trắng và ký tự ẩn
Một số công thức không thể hoạt động nếu một số ô được lấp đầy bởi các ký tự ẩn hoặc khoảng trắng. Dù nhìn có vẻ trống, chúng có thể chứa khoảng trắng hoặc thậm chí ký tự không in được. Excel coi khoảng trắng như ký tự văn bản, và như trường hợp kiểu dữ liệu khác nhau, điều này có thể gây ra lỗi #VALUE! trong Excel.
Trong ví dụ trên, các ô C2, B7 và B10 có vẻ trống nhưng chứa một vài khoảng trắng, gây ra lỗi #VALUE! khi chúng ta cố gắng nhân chúng.
Để khắc phục lỗi #VALUE!, bạn phải đảm bảo các ô thực sự trống. Chọn ô và nhấn phím DELETE trên bàn phím để xóa bất kỳ ký tự ẩn hoặc khoảng trắng nào.
Bạn cũng có thể sử dụng hàm Excel bỏ qua giá trị văn bản. Một hàm như vậy là hàm SUM:
=SUM(B2:C2)
Khắc phục lỗi #VALUE! do phạm vi không tương thích
Nếu bạn đang sử dụng các hàm chấp nhận nhiều phạm vi trong đối số của chúng, chúng sẽ không hoạt động nếu các phạm vi đó không cùng kích thước và hình dạng. Nếu đó là trường hợp, công thức của bạn 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à 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ẽ nhận được lỗi #VALUE!.
Bạn 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 của bạn sẽ không gặp vấn đề khi tính toán.
Khắc phục lỗi #VALUE! do định dạng ngày sai
Microsoft Excel có thể nhận diện nhiều định dạng ngày khác nhau. Nhưng bạn có thể đang dùng định dạng mà Excel không thể nhận diện là giá trị ngày. Trong trường hợp đó, nó sẽ coi đó là chuỗi văn bản. Nếu bạn thử dùng các 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 các định dạng ngày không đúng sang định dạng đúng.
Khắc phục lỗi #VALUE! do cú pháp công thức không đúng
Nếu bạn đang dùng cú pháp công thức sai khi cố gắng tính toán, kết quả sẽ là lỗi #VALUE!. May mắn thay, Microsoft Excel có Công cụ Kiểm tra (Auditing Tools) giúp bạn với các công thức. Bạn sẽ tìm thấy chúng trong nhóm Formula Auditing trên Ribbon. Cách sử dụng như sau:
- Chọn ô chứa công thức trả về lỗi #VALUE!.
- Mở tab Formulas trên Ribbon.
- Trong nhóm Formula Auditing, tìm và chọn Error Checking (Kiểm tra lỗi) hoặc Evaluate Formula (Đánh giá công thức).
Excel sẽ phân tích công thức bạn dùng trong ô đó, và nếu tìm thấy lỗi cú pháp, nó sẽ được làm nổi bật. Lỗi cú pháp được phát hiện có thể dễ dàng sửa chữa.
Ví dụ, nếu bạn dùng =FILTER(A2:B12,A2:A10="Milk"), bạn sẽ nhận được lỗi #VALUE! vì giá trị phạm vi không đúng. Để tìm vị trí vấn đề trong công thức, nhấn 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 sẽ khắc phục được lỗi #VALUE!.
Khắc phục lỗi #VALUE! trong hàm XLOOKUP và VLOOKUP
Nếu bạn cần tìm kiếm và truy xuất dữ liệu từ bảng tính hoặc sổ làm việc Excel, bạn thường dùng hàm XLOOKUP hoặc "người 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 của lỗi #VALUE! trong XLOOKUP là kích thước không tương thích 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 dùng công thức: =XLOOKUP(D2,A2:A12,B2:B13), kết quả sẽ là lỗi #VALUE! vì mảng tra cứu và mảng trả về chứa số hàng khác nhau.
Điều chỉnh công thức thành: =XLOOKUP(D2,A2:A12,B2:B12).
Sử 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 nói đến 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 thông báo văn bản có ý nghĩa hơn. Giả sử bạn muốn tính ngày đến trong ví dụ dưới đây, và muốn thay thế lỗi #VALUE! do định dạng ngày sai bằng thông báo "Kiểm tra ngày".
Bạn sẽ dùng công thức sau: =IFERROR(B2+C2,"Kiểm tra ngày").
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.
Cùng kết quả có thể đạt được nếu bạn dùng kết hợp hàm IF và ISERROR:
=IF(ISERROR(B2+C2),"Kiểm tra ngày",B2+C2).
Công thức này sẽ trước tiên kiểm tra xem kết quả trả về có phải lỗi hay không. Nếu là lỗi, nó sẽ trả về đối số đầu tiên (Kiểm tra ngày), nếu không, nó sẽ trả về đối số thứ hai (B2+C2).
Nhược điểm duy nhất của hàm IFERROR là nó sẽ bắt tất cả các loại lỗi, không chỉ lỗi #VALUE!. Nó không phân biệt được các lỗi như #N/A, #DIV/0, #VALUE! hay #REF!.
Excel, với kho tàng hàm và tính năng phong phú, cung cấp vô số khả năng để quản lý và phân tích dữ liệu. Hiểu và chinh phục lỗi #VALUE! trong Microsoft Excel là kỹ năng thiết yếu trong thế giới phù phép bảng tính. Những rắc rối nhỏ này có thể gây bực bội, nhưng 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.