Cách sửa lỗi #VALUE trong Excel

Trong quá trình làm việc và học tập, Excel là một công cụ không thể thiếu đối với mọi người. Đôi khi, bạn đang thực hiện các hàm, công thức lại xảy ra những lỗi #VALUE! trong Excel. Vậy làm sao để khắc phục được tình trạng này? Hãy cùng Điện máy XANH tìm hiểu cách sửa lỗi #VALUE trong Excel nhé!

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

Lỗi #VALUE! là lỗi thường được hiển thị và thông báo giá trị kết quả bạn tính được bị lỗi. Lỗi #VALUE! xảy ra thường có nhiều nguyên nhân nhưng phần lớn lỗi này ta thường thấy là quá trình nhập công thức hoặc bạn nhập ô tham chiếu bị lỗi.

Một số hàm thường gặp lỗi #VALUE! là: AVERAGE, SUM, TRANSPOSE, VLOOKUP, SUMPRODUCT, FIND, SEARCH,...

2 Lỗi #VALUE! khi ô trống chứa khoảng trắng

Lỗi  #VALUE! khi ô trống chứa khoảng trắng là do khi nhập công thức hàm quét phải ô trống không có dữ liệu hoặc dữ liệu ẩn mà không thấy.

Cách sửa: Bạn phải tìm và loại bỏ những khoảng trắng sau đó kiểm tra công thức hàm.

Ví dụ: Khi sử dụng hàm SUM ở hình minh họa dưới dây:

Cách khắc phục

Xử lý dấu cách trong khoảng trắng

Bước 1: Tìm những ô để khoảng trắng > Chọn những ô tham chiếu > Nhấn Ctrl + H để mở hộp thay thế.

Bước 2: Trong hộp Find and Replace > Nhập vào ô Find What dấu cách > Trong mục Replace with bạn không nhập gì cả > Nhấn Replace All để loại bỏ tất cả các khoảng trắng.

Dùng bộ lọc

Bước 1: Chọn toàn bộ vùng dữ liệu bị lỗi, trỏ chuột lên thanh tiêu đề > Chọn Data > Chọn Filter.

Bước 2: Nhấp chuột vào mũi tên ở góc phải bên dưới của ô dữ liệu đầu tiên > Chọn tất cả các ô và không chọn ô Blanks > Nhấn OK.

Bước 3: Loại bỏ được khoảng trắng và bạn hãy kiểm tra lại hàm. Excel sẽ hiện ra kết quả.

3Lỗi #VALUE! khi danh sách chứa văn bản hay kí tự đặc biệt

Lỗi #VALUE! khi danh sách chứa văn bản hay kí tự đặc biệt là khi bạn nhập vào ô mà bạn sử dụng để tính hàm có những ký tự. 

Cách khắc phục: Tìm và loại bỏ những ký tự đặc biệt sau đó kiểm tra lại những công thức của hàm.

Dưới đây là các bước cụ thể để khắc phục lỗi #VALUE! chứa văn bản ký tự đặc biệt:

Bước 1: Tạo thêm một cột TEST ở ngay bên cạnh cột mà bạn muốn tính toán > Sử dụng hàm ISTEXT để kiểm tra.

Bước 2: Nhấn Enter > Kéo trỏ chuột tới những vị trí còn lại để hiện ra kết quả kiểm tra quá trình sao chép.

Bước 3: Bạn nên kiểm tra và chỉnh sửa lại những kết quả TRUE. Sau đó Excel sẽ hiện lại kết quả như hình là bạn đã thành công.

4Lỗi #VALUE! khi dùng các dấu toán học

Lỗi #VALUE! khi dùng các dấu toán học thường xảy ra với dấu "+" và dấu * khi bạn sử dụng toán tử là cộng từng con số.

Sau khi nhập công thức thì hàm Tổng báo hiệu có lỗi #VALUE! như sau:

Cách khắc phục: Sử dụng hàm SUM để quét lại tất cả những dữ liệu cần tính. Trong ví dụ này sử dụng hàm: =SUM(E2:E6) nên ta có hình minh họa như sau:

5Lỗi #VALUE! xảy ra ở một số hàm

Lỗi #VALUE! khi kết hợp hàm SUM, AVERAGE với hàm dữ liệu ISERROR, IF hoặc IFERROR

Nguyên nhân: Trong vùng tính tổng hoặc tính trung bình có lỗi #VALUE! trong excel.

Cách khắc phục: Dùng công thức kết hợp giữa hai hàm đó là hàm IF và ISERROR hoặc IFERROR.

Chẳng hạn: ={AVERAGE(IF(ISERROR(B2:D2,””,B2:D2))} 

Lưu ý: Công thức này chỉ áp dụng cho Excel 2007 trở lên.

Lỗi #VALUE! khi dùng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS 

Nguyên nhân: Đây cũng là một trong những trường hợp phổ biến dùng để tính hàm tổng, hàm đếm có điều kiện trên một file khác. Trong trường hợp cả file tính toán và file chứa dữ liệu tính toán đều được mở thì lỗi không xuất hiện .

Nhưng khi ta tắt file đi chỉ mở một file tính toán thì lỗi #VALUE! sẽ xuất hiện ở tất cả những ô liên quan tới hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS trong Excel. 

Cách khắc phục: Phải mở 2 file cùng một lúc sau đó nhận phím F9 để lỗi VALUE trong excel cập nhật công thức mới.

Lỗi #VALUE! khi dùng hàm IF, ISERROR hoặc định dạng có điều kiện

Nguyên nhân: Trong quá trình lập hàm chưa đầy đủ theo trình tự và tạo ra khoảng trống trong bài dẫn đến sự thiếu sót và tạo lỗi #VALUE!.

Chẳng hạn, ví dụ các mặt hàng đều có giá trị giống nhau là 200, dựa theo những số liệu sẵn có ta có thể tính được doanh thu một cách đơn giản để nộp báo cáo về cho sếp không bị những lỗi toe toét như sau:

Giải pháp số 1: Những lỗi mặt hằng nào bị lỗi số lượng thì trả về kết quả giá trị là 0.

Khi trả giá trị về 0 thì báo cáo bạn làm có thể dễ nhìn hơn. Công thức để tính doanh thu lúc này như sau: C2= IF(ISERROR(B2),0,B2*200) hay C2= IFERROR(B2*100,0).

Khi đó kết quả ta thu được: Cột doanh thu tạo ra đã ổn và giảm những lỗi, tuy nhiên nếu báo cáo chưa thực sự ổn nếu cột số lượng vẫn xuất hiện những lỗi #VALUE. 

Giải pháp số 2: Sử dụng conditional formatting để làm ẩn những lỗi trong Excel.

Chúng ta có thể thay thế công thức ở cột doanh thu bằng cách nhập công thức: Doanh thu = Số lượng * Đơn giá. Tiếp đó, bạn hãy bôi đen các vùng dữ liệu cần ẩn lỗi rồi chọn Conditional formatting > New Rules.

Khi sử dụng bảng thiết lập thông tin có điều kiện thì bạn nên nhập trong phần Use a formula to determines which cells to format như sau:

• Iserror: Công thức nhằm xác định những ô được định dạng.
• Formatting: Bạn có thể chọn màu chữ là màu trắng.

Lỗi #VALUE! khi dùng hàm DAYS

Nguyên nhân: Trong quá trình thực hiện hàm DAYS thì một số ô thời gian sẽ báo lỗi #VALUE!. Lỗi này là do sự không đồng nhất về định dạng trên Excel với hệ thống ngày giờ của máy tính. Chính vì vậy, bạn nên định dạng ngày tháng trên hệ thống máy tính.

Cách khắc phục

Bước 1: Đầu tiên, bạn hãy truy cập vào Control Panel trên máy tính.

Bước 2: Chọn Clock and Region để chỉnh sửa ngày tháng trên phần mềm Excel.

Bước 3: Sau đó, bạn chọn mục Date and Time. 

Bước 4: Chọn vào mục Change date and time để thay đổi thời gian trên máy tính.

Bước 5: Cuối cùng, bạn chỉ cần điều chỉnh ngày giờ phù hợp > Chọn OK.

Bước 6: Trở lại Excel, nhập lại thời gian và thực hiện các hàm ngày tháng và không còn lỗi xuất hiện nữa.

Lỗi #VALUE! khi dùng hàm tìm kiếm FIND và SEARCH

Ví dụ: Một hàm khắc phục cụ thể như sau: =FIND("gloves";"Gloves (Youth)";1). 

Khi bạn tìm kiếm thông tin sẽ đưa ra kết quả #VALUE! bởi vì không có kết quả khớp với gloves mà trong bài chỉ có Gloves. Lưu ý ở hàm FIND thì có khả năng nhận diện được cả chữ hoa và chữ thường, vì vậy bạn phải đảm bảo các giá trị, thông tin tìm kiếm phải chính xác với chuỗi văn bản được tham chiếu.

Tuy nhiên, đối với hàm SEARCH sẽ trả về giá trị một, hàm SEARCH không phân biệt chữ hoa và chữ thường. Cụ thể: =SEARCH("gloves";"Gloves (Youth)";1).

Cách khắc phục là bạn cần sửa cú pháp khi cần thiết. 

Sự cố: Đối với tham đối số bắt đầu sẽ được đặt thành không (0).

Nếu tham đối số bắt đầu là một tham đối tùy chỉnh, nếu bạn bỏ qua bước này thường nó sẽ mặc định giả định là 1. Tuy nhiên, nếu giá trị tham đối được đặt trong cú pháp và giá trị được đặt thành 0 thì bạn sẽ gặp lỗi #VALUE! trong Excel. 

Cách khắc phục: Không cần sử dụng tham số bắt đầu nếu không bắt buộc hoặc có thể lựa chọn đặt thành giá trị thích hợp, chính xác.

Sự cố: Tham đối số bắt đầu lớn hơn tham đối trong văn bản.

Có thể hiểu qua ví dụ hàm Find: =FIND(“s”;”Functions and formulas”;25). Hàm có nghĩa là tìm kiếm ký tự "s" trong chuỗi Functions and formulas (trong văn bản) bắt đầu từ ký tự số 25. Tuy nhiên, hàm sẽ trả lại lỗi #VALUE! vì trong chuỗi chỉ có 22 ký tự.

Lỗi #VALUE! khi dùng hàm VLOOKUP

Nguyên nhân: Trong tham đối ký tự tham chiếu đang có nhiều hơn 255 ký tự. Tham đối chỉ mục cột chứa văn bản hay nhỏ hơn 0.

Cách khắc phục: Làm gọn lại giá trị hoặc có thể kết hợp hàm INDEX và MATCH để làm giải pháp thay thế. Bạn có thể sử dụng hàm INDEX và MATCH trong Excel để tra cứu những giá trị có nhiều hơn 255 ký tự. Sử dụng hàm INDEX và MATCH chính là sử dụng công thức mảng.

Lỗi tham đối chỉ mục cột chứa văn bản hay nhỏ hơn 0. Lỗi #VALUE! sẽ xuất hiện khi tham số chỉ mục cột nhỏ hơn 1 chỉ mục chính là số của cột bên phải cột tìm kiếm nếu bạn muốn trả về tìm kết quả trùng khớp. Sự cố này thường xảy ra do những lỗi value trong Excel như đánh máy trong tham số đối chỉ mục cột, có thể vô tình chỉ định một số nhỏ hơn 1 nhằm làm giá trị chỉ mục. 

Bạn nên nhớ giá trị tối thiểu trong tham đối chỉ mục cột là 1, trong đó 1 là cột tìm kiếm, 2 chính là cột đầu tiên bên phải của cột tìm kiếm,... Cho nên nếu bạn tìm kiếm trong cột A thì số 1 sẽ là tham chiếu của cột A, 2 là tham chiếu của cột B, 3 là tham chiếu của cột C,...

Xem thêm: Hàm VLOOKUP trong Excel: Cách sử dụng hàm VLOOKUP, có ví dụ cụ thể[/info]

Trên đây là cách sửa lỗi #VALUE trong excel. Nếu bạn còn bất kỳ thắc nào cần được giải đáp, bạn vui lòng để lại bình luận bên dưới để Điện máy XANH hỗ trợ bạn nhé!