Cho dù đó là lỗi định dạng đơn giản hay vấn đề pháp phức tạp hơn, việc biết cách sửa lỗi #VALUE trong Excel là điều cần thiết đối với bất kỳ ai muốn tạo bảng tính không có lỗi và đảm bảo tính chính xác của dữ liệu.
Khi làm việc với Excel việc gặp lỗi là điều thường gặp. Một trong những lỗi khó chịu nhất là #VALUE, lỗi này xuất hiện khi một công thức hoặc hàm không thể xử lý dữ liệu. Lỗi này có thể do một số yếu tố gây ra, bao gồm cú pháp sai, tham chiếu ô không khớp hoặc ký tự ẩn.
Trong bài viết này, chúng tôi sẽ khám phá các nguyên nhân khác nhau gây ra lỗi #VALUE và cung cấp các mẹo về cách khắc phục.
Xem thêm: Cách gộp 2 cột Họ và Tên trên Excel không mất nội dung
Lỗi #VALUE xuất hiện trong Excel khi nào?
Lỗi #VALUE trong Excel thường do các nguyên nhân sau:
- Định dạng dữ liệu không cụ thể: Nếu một hàm Excel yêu cầu một kiểu dữ liệu cụ thể, chẳng hạn như số hoặc văn bản và một ô chứa một kiểu dữ liệu khác.
- Cú pháp công thức sai: Khi các đối số của hàm không đúng, bị thiếu hoặc sai thứ tự.
- Khoảng trắng: Nếu công thức tham chiếu đến một ô có chứa ký tự khoảng trắng. Nhìn bề ngoài, những ô như vậy trông hoàn toàn trống rỗng.
- Ký tự ẩn: Đôi khi, các ô có thể chứa các ký tự ẩn hoặc không in được khiến công thức không thể tính toán chính xác.
- Ngày được định dạng dưới dạng văn bản: Khi ngày được nhập ở định dạng mà Excel không hiểu được, chúng sẽ được coi là chuỗi văn bản chứ không phải là ngày hợp lệ.
- Phạm vi có kích thước không tương thích . Khi công thức tham chiếu đến một vài phạm vi không có cùng kích thước hoặc hình dạng, Excel không thể tính toán công thức và đưa ra lỗi.
Như bạn thấy, lỗi #VALUE trong Excel có thể do nhiều yếu tố khác nhau gây ra. Bằng cách hiểu được nguyên nhân gốc rễ, bạn có thể dễ dàng tìm ra biện pháp khắc phục phù hợp hơn.
Xem thêm: Cách sử dụng hàm INDEX và MATCH tra cứu trong Excel
Cách khắc phục lỗi #VALUE trong Excel
Khi bạn xác định được nguyên nhân gây ra lỗi, hãy sử dụng các bước khắc phục sự cố thích hợp để giải quyết vấn đề.
Kiểm tra xem kiểu dữ liệu có hợp lệ không
Để tránh lỗi #VALUE trong Excel, hãy xác minh rằng loại dữ liệu trong ô được tham chiếu là chính xác. Nếu một công thức hoặc hàm yêu cầu dữ liệu số, hãy đảm bảo ô chứa số chứ không phải văn bản.
Một ví dụ điển hình là các phép toán như cộng và nhân. Khi một trong các giá trị bạn cộng hoặc nhân không phải là số, sẽ xảy ra lỗi #VALUE:
Để khắc phục lỗi, bạn có thể sử dụng một trong các tùy chọn sau:
- Nhập các giá trị số còn thiếu.
- Sử dụng các hàm Excel tự động bỏ qua các giá trị văn bản.
- Viết câu lệnh IF tương ứng với logic kinh doanh của bạn.
Trong ví dụ này, bạn có thể sử dụng hàm PRODUCT:
=PRODUCT(B3,C3)
Nếu một trong các ô được tham chiếu chứa văn bản, giá trị logic hoặc trống thì ô đó sẽ bị bỏ qua. Kết quả giống như bạn nhân giá trị kia với 1.
Ngoài ra, bạn có thể xây dựng câu lệnh IF như thế này:
=IF(AND(ISNUMBER(B3), ISNUMBER(C3)), B3*C3, 0)
Công thức này chỉ nhân hai ô nếu cả hai giá trị đều là số và trả về 0 nếu một trong hai ô chứa giá trị không phải là số.
Xóa khoảng trắng và ký tự ẩn
Trong một số công thức, một ô có khoảng trắng sai hoặc ký tự ẩn cũng có thể gây ra lỗi #VALUE, như thể hiện trong ảnh chụp màn hình bên dưới:
Nhìn bề ngoài, các ô như D3, B7 và C14 có thể hoàn toàn trống rỗng. Tuy nhiên, chúng có chứa một hoặc nhiều khoảng trắng hoặc ký tự không in được.
Trong Excel, ký tự khoảng trắng được coi là văn bản và nó có khả năng kích hoạt lỗi #VALUE. Trên thực tế, đây chỉ là một trường hợp khác của ví dụ trước, vì vậy nó có thể được sửa theo cách tương tự:
- Đảm bảo rằng các ô có vấn đề thực sự trống. Để thực hiện việc này, hãy chọn ô và nhấn nút Delete để loại bỏ bất kỳ ký tự ẩn nào trong đó.
- Sử dụng hàm Excel bỏ qua các giá trị văn bản, chẳng hạn như hàm SUM thay vì phép tính cộng số học.
Kiểm tra phạm vi tương thích
Nhiều hàm Excel chấp nhận nhiều phạm vi trong đối số của chúng yêu cầu các phạm vi đó phải có cùng kích thước và hình dạng. Nếu không, công thức sẽ báo lỗi #VALUE.
Ví dụ: hàm FILTER dẫn đến lỗi #VALUE khi đối số bao gồm và mảng có kích thước không tương thích. Ví dụ:
=FILTER(A3:B20, A3:A22="Apple")
Khi các tham chiếu phạm vi được thay đổi tương ứng, lỗi sẽ biến mất:
=FILTER(A3:B20, A3:A20="Apple")
Đảm bảo ngày tháng không được định dạng dưới dạng văn bản
Trong Excel, ngày tháng thường được định dạng dưới dạng giá trị số. Tuy nhiên, thay vào đó, một số ngày trong bảng tính của bạn có thể được định dạng dưới dạng chuỗi văn bản. Khi điều này xảy ra, Excel sẽ trả về #VALUE! nếu bạn cố gắng thực hiện các phép tính hoặc thao tác vào những ngày này vì không thể cộng, trừ hoặc tính toán các giá trị văn bản bằng cách nào đó.
Để giải quyết vấn đề này, bạn cần chuyển đổi ngày có định dạng văn bản thành ngày Excel hợp lệ.
Kiểm tra lỗi cú pháp công thức
Một nguyên nhân có thể khác gây ra lỗi #VALUE trong Excel có thể là lỗi cú pháp trong công thức của bạn. Công cụ Formula Auditing của Excel có thể giúp bạn xác định và khắc phục các sự cố đó.
- Chọn ô có công thức đang tạo ra lỗi #VALUE.
- Trên tab Formulas, trong nhóm Formula Auditing, nhấp vào Evaluate Formula hoặc Error Checking.
Excel sẽ duyệt qua từng phần công thức một, hiển thị kết quả của từng bước. Nếu có lỗi cú pháp, Excel sẽ đánh dấu phần cụ thể của công thức gây ra lỗi. Khi bạn đã phát hiện ra lỗi cú pháp, hãy sửa nó và đánh giá lại công thức để đảm bảo rằng nó hiện đang hoạt động như mong đợi.
Ví dụ: hãy xem xét công thức sau trong tập dữ liệu bên dưới:
=SORT(CHOOSECOLS(A3:B20, 3))
Lỗi #VALUE xảy ra do đối số col_num (3) của hàm CHOOSECOLS lớn hơn tổng số cột trong mảng được tham chiếu (2).
Việc đặt đối số cuối cùng thành 2 sẽ giải quyết được vấn đề và trả về kết quả mong muốn – cột Tổng được sắp xếp từ nhỏ nhất đến lớn nhất:
Lỗi #VALUE trong Excel XLOOKUP và VLOOKUP
Hàm VLOOKUP và hàm XLOOKUP thường được sử dụng trong Excel để tìm kiếm và truy xuất dữ liệu trùng khớp. Tuy nhiên, cả hai hàm đều có thể tạo ra lỗi #VALUE trong một số trường hợp nhất định.
Một nguyên nhân phổ biến gây ra lỗi #VALUE trong XLOOKUP là khi kích thước của mảng tra cứu và mảng trả về không thể so sánh được.
Ví dụ: bạn không thể tìm kiếm trong mảng ngang và trả về giá trị từ mảng dọc. Ngoài ra, mảng tra cứu không thể lớn hơn hoặc nhỏ hơn mảng trả về. Nếu có bất kỳ sự không khớp nào về kích thước của các mảng này, XLOOKUP sẽ không thể thực hiện tra cứu và trả về lỗi #VALUE.
Ví dụ: công thức XLOOKUP bên dưới trả về lỗi #VALUE vì mảng tra cứu và trả về chứa số hàng khác nhau:
=XLOOKUP(D3, A3:A20, B3:B22)
Việc điều chỉnh tham chiếu return_array sẽ giải quyết được lỗi:
=XLOOKUP(D3, A3:A20, B3:B20)
Trong VLOOKUP, hai lý do phổ biến gây ra lỗi #VALUE là khi giá trị tra cứu vượt quá 255 ký tự và khi đối số col_index_num nhỏ hơn 1.
Loại bỏ lỗi #VALUE bằng hàm IFERROR
Để loại bỏ lỗi #VALUE trong bảng Excel, bạn có thể sử dụng hàm IFERROR trong Excel các phiên bản 2007-2010-2013-2016-2019-2021-365 hoặc tổ hợp IF ISERROR ở các phiên bản trước.
Giả sử bạn đang sử dụng công thức DATEDIF để tìm sự khác biệt giữa ngày trong B3 và C3:
=DATEDIF(B3, C3, "d")
Nếu một hoặc cả hai ngày không hợp lệ thì công thức sẽ dẫn đến lỗi #VALUE. Để khắc phục, hãy gói công thức cốt lõi của bạn vào hàm IFERROR như thế này:
=IFERROR(DATEDIF(B3, C3, "d"), "Invalid date!")
Nếu Excel không nhận ra giá trị ô được tham chiếu là ngày, hàm IFERROR sẽ chỉ ra điều đó một cách rõ ràng.
Đó là cách phát hiện và sửa lỗi #VALUE trong Excel. Tìm hiểu nguyên nhân và sử dụng các kỹ thuật khắc phục sự cố thích hợp, bạn có thể nhanh chóng đưa bảng tính excel của mình trở lại đúng hướng.