Cách sửa lỗi #VALUE trong Excel chỉ trong 1 nốt nhạc dễ dàng

Trong quá trình sử dụng Excel, việc xuất hiện thông báo lỗi #VALUE! không phải là hiếm gặp. Bạn có thể đọc bài viết dưới đây để nắm rõ hơn về nguyên nhân và các giải pháp khắc phục lỗi #VALUE trong Excel hay gặp nhất hiện nay nhé. Toàn bộ mọi bước làm sẽ được mô phỏng với hình ảnh chi tiết nhất.

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

Lỗi #VALUE trong Excel thường xảy ra để chỉ ra rằng có sự cố trong kết quả trả về của phép tính bạn thực hiệ. Nguyên nhân phát sinh lỗi này rất đa dạng. Tuy nhiên, thường thì nó xuất phát từ việc nhập sai công thức hoặc lỗi tham chiếu tới các ô trong bảng tính.

Các hàm thường gặp phải lỗi VALUE trong Excel bao gồm: AVERAGE, SUM, TRANSPOSE, VLOOKUP, SUMPRODUCT, FIND, SEARCH,…

2. Tổng hợp các lỗi #VALUE trong Excel và cách khắc phục

Như chúng ta đã biết, có rất nhiều các lỗi #VALUE trong Excel xuất hiện khi chúng ta sử dụng. Vậy làm thế nào để khắc phục chúng triệt để nhất? Dưới đây chính là các trường hợp hay gặp, nguyên nhân và cách sửa chi tiết nhất cho mọi người cùng nắm.

2.1. Bị lỗi #VALUE do giữa các giá trị có khoảng trắng

Nguyên nhân gây lỗi #VALUE trong Excel này: Khi điền công thức, hàm quét qua ô trống trong Excel có dữ liệu ẩn nhưng không thấy được.

Ví dụ: Dùng hàm SUM giống hình ảnh bên dưới.

Cách sửa lỗi: Tìm khoảng trắng và xoá bỏ nó. Tiếp theo, check lại công thức dùng hàm của bạn. Dưới đây là những bước chi tiết hơn cho các bạn:

Bước 1: Bạn hãy lựa chọn vùng thông tin có vấn đề trên bảng tính. Kế tiếp, đưa con trỏ lên phần thanh tiêu đề và chọn “DATA” > Click vào “Filter”.

Bước 2: Mọi người bấm vào biểu tượng mũi tên ở góc dưới cùng bên phải của ô tính đầu tiên > Chọn mục “Blanks” từ menu và ấn “OK”. Sau đó, các ô không chứa dữ liệu sẽ hiện ra.

Bước 3: Bạn cần xoá đi những khoảng trống và tiến hành kiểm tra công thức một lần nữa. Cuối cùng, Excel sẽ cho ra kết quả chính xác như hình ảnh là xong.

2.2. Lỗi #VALUE do chứa những ký tự đặc biệt

Nguyên nhân gây ra lỗi #VALUE trong Excel này: Có ô dữ liệu chứa các ký tự đặc biệt khi người dùng nhập lệnh.

Ví dụ: Hàm SUM trong hình ảnh ở bên dưới đã chọn vô bảng tham chiếu có chứa những ký tự nên sẽ xuất hiện lỗi #VALUE trong Excel như hình.

Cách sửa lỗi: Hãy xác định ký tự đó và xoá nó ra. Tiếp đến, kiểm lại công thức bên trong của hàm. Dưới đây là chi tiết bước làm cho chúng ta:

Bước 1: Tạo một cột Test (kiểm tra) với các ô trong cột có sử dụng hàm ISTEXT. Cụ thể, ô này được tham chiếu từ ô E2 trong sheet.

Bước 2: Nhấn Enter rồi sau đó kéo chuột tới góc dưới cùng của ô đã nhập hàm xuống dọc theo cột để nhân bản hàm đó cho chúng. Mình thực hiện công thức này cho tới ô F6.

Bước 3: Bạn kiểm tra và điều chỉnh các kết quả dựa vào chữ TRUE. Kết quả mà Excel đưa ra là giống với hình ảnh ở dưới.

2.3. Bị lỗi #VALUE khi dùng các dấu trong toán học (+, *)

Nguyên nhân gây ra lỗi: Trường hợp này hay mắc phải khi chúng ta dùng toán tử, nghĩa là cộng từng số lại với nhau.

Ví dụ: Mình điền phép cộng như hình ở dưới đây để cộng giá trị trong các ô này lại với nhau.

Khi đã điền xong công thức và nhấn Enter thì lỗi #VALUE trong Excel liền xuất hiện.

Cách sửa lỗi: Mọi người hãy dùng hàm SUM rồi quét chọn vùng dữ liệu cần tính tổng. Tại đây, mình áp dụng với công thức đó là =SUM(E2:E6) nên kết quả trả về không bị lỗi nữa như hình dưới đây.

2.4. Lỗi trừ ngày dạng văn bản

Khi gặp lỗi VALUE trong Excel này, mọi người hãy xem lại thật kỹ những khoảng trắng nằm trong quãng là khắc phục được. Cách làm là như sau:

Bước 1: Kích đúp chuột vào dãy ngày hiển thị bị lỗi để kiểm tra xem có khoảng không thừa nào không. Nếu phát hiện lỗi thì tiếp tục thực hiện bước tiếp theo.

Bước 2: Bấm chọn mục “Dữ liệu” > Chọn “Văn bản thành cột”.

Bước 3: Sau đó, bấm “Tiếp theo” > Chọn vào “Định dạng dữ liệu cột” > Nhấn chọn “Ngày” để áp dụng là hoàn thành.

3. Cách sửa lỗi #VALUE trong Excel đối với các hàm thông dụng

Ngoài những vấn đề trên, nhiều người dùng cũng gặp vô vàn các lỗi #VALUE trong Excel khi dùng các hàm. Cụ thể là như thế nào thì bạn có thể xem bên dưới để tìm ra nguyên nhân và cách khắc phục chúng thật dễ cùng mình nhé.

3.1. Kết hợp hàm AVERAGE, SUM với các hàm dữ liệu IF, IFERROR, ISERROR

Nguyên nhân gây lỗi: Có lỗi VALUE trong Excel nằm ở vùng tính trung bình hay tính tổng.

Cách sửa lỗi: Sử dụng công thức để kết hợp 2 hàm lại đó là IF với ISERROR hay IFERROR.

Ví dụ: Áp dụng công thức là: ={AVERAGE(IF(ISERROR(B2:D2,””,B2:D2))}

Lưu ý: Cách khắc phục với công thức hàm này chỉ tương thích với các phiên bản Excel từ 2007 trở về sau.

3.2. Hàm SUMIF, SUMIFS, COUNTIF và COUNTIFS

Nguyên nhân gây lỗi: Đây là những hàm phổ biến được dùng để tính tổng trên Excel cùng với hàm đếm có thêm điều kiệnt trên 1 tệp nào khác. Nếu như cả file bạn đang dùng để nhập công thức với file tham chiếu đều đang bật thì lỗi không xảy ra.

Tuy nhiên, nếu như chỉ có mở file đang làm phép tính mà tắt file tham chiếu thì sẽ có lỗi #VALUE trong Excel. Nó sẽ xuất hiện tại toàn bộ các ô nào có công thức chứa hàm COUNTIF, COUNTIFS, SUMIF, SUMIFS trên file Excel đó.

Cách sửa lỗi: Người dùng cần mở đồng thời 2 file đó cùng lúc khi thao tác > Bấm phím F9 để những lỗi VALUE trong Excel này được thay bằng công thức mới khác.

3.3. Dùng hàm IF, ISERROR hay định dạng có điều kiện

Nguyên nhân gây lỗi: Hàm được lập chưa đủ theo thứ tự cũng như trong ô có chứa các khoảng trống và gây ra lỗi #VALUE trong Excel.

Ví dụ: Mọi hàng hoá đề sở hữu giá trị tương đương là 200. Dựa trên các con số sẵn có, bạn có thể hoàn toàn tính được doanh thu để phục vụ cho việc báo cáo mà không hề bị lỗi với những mẹo bên dưới đây:

Phương án 1: Các lỗi về số lượng hàng hoá nào thì giá trị trả về sẽ là 0.

Khi giá trị xuất hiện là 0 thì chúng ta sẽ dễ dàng quan sát được báo cáo. Sau đây là công thức được dùng để tính doanh thu: C2= IF(ISERROR(B2),0,B2*200) hoặc C2= IFERROR(B2*100,0).

Phương án 2: Dùng conditional formatting để ẩn các lỗi gặp phải trên Excel.

Mọi người có thể thay công thức tại cột doanh thu với công thức điền vào là: Doanh thu = Số lượng * Đơn giá. Kế tiếp, chúng ta tô đen tại những vùng dữ liệu muốn ẩn lỗi > Bấm Conditional formatting > Chọn New Rules.

Mọi người hãy điền mục Use a formula to determines which cells to format giống với dưới đây nếu dùng bảng thiết lập thông tin có điều kiện:

Iserror: Công thức với mục đích xác định các ô định dạng.
Formatting: Lựa chọn màu trắng làm màu chữ.

3.4. Hàm DAYS

Nguyên nhân gây lỗi: Có lỗi #VALUE trong Excel xuất hiện trong lúc dùng hàm DAYS. Nguyên nhân của nó là định dạng thiết lập trên Excel không thống nhất với hệ thống ngày tháng năm của máy tính. Do đó, chúng ta sẽ cài đặt lại phần nền trên máy tính.

Cách sửa lỗi:

Bước 1: Trước tiên, mọi người truy cập vào phần Control Panel.

Bước 2: Sau đó, bấm vào Clock and Region để thiết lập ngày tháng hiện trên Excel.

Bước 3: Tiếp đến, mọi người bấm Date and Time.

Bước 4: Nhấn tiếp vào nút Change date and time để sửa đổi thời gian hiển thị trên máy tính.

Bước 5: Sau cùng, mọi người chỉnh sửa lại phần ngày giờ cho thích hợp > Bấm vào OK.

Bước 6: Quay về file Excel, bạn điền lại thời gian và thao tác lại các lệnh về ngày tháng và sẽ không gặp lỗi nữa.

3.5. Hàm SEARCH và FIND

Ví dụ: Ta có một hàm khắc phục đó là: =FIND(“gloves”;”Gloves (Youth)”;1).

Trong trường hợp này, quá trình tìm kiếm sẽ không thành công và có thông báo lỗi #VALUE trong Excel xuất hiện do không tìm thấy từ “gloves” vì bài chỉ chứa “Gloves”. Điều quan trọng là hàm FIND nhận ra được cả sự khác biệt giữa chữ hoa và chữ thường nên yêu cầu phải có độ chính xác tuyệt đối giữa thông tin cần tìm và chuỗi tham chiếu.

Tuy là thế, hàm SEARCH sẽ cho ra kết quả 1 cho cùng trường hợp trên vì nó không quan tâm đến sự khác nhau giữa chữ hoa và chữ thường. Chính xác đó là: =SEARCH(“gloves”, “Gloves (Youth)”, 1).

Để giải quyết vấn đề này, bạn cần chỉnh sửa cú pháp cho phù hợp.

Vấn đề: Đối với tham số đầu tiên được thiết lập thành không (0).

Trường hợp tham số bắt đầu là tham đối tuỳ biến, Excel thường mặc định giá trị này là 1. Tuy nhiên, nếu bạn cố tình đặt giá trị này là 0 trong cú pháp thì sẽ dẫn đến lỗi #VALUE trong Excel.

Cách sửa lỗi: Nếu không cần thiết thì không dùng tham số bắt đầu hay có thể đặt sang giá trị chính xác và thích hợp.

Vấn đề: Tham đối số khởi đầu lớn hơn so với tham đối nằm trong văn bản.

Ví dụ như hàm: =FIND(“s”;”Functions and formulas”;25). Hàm này nghĩa là hãy tìm kiếm ký tự “s” có chứa trong chuỗi Functions and formulas trên văn bản kể từ ký tự đứng thứ 25. Tuy nhiên, lúc đó thì kết quả trả về sẽ là #VALUE bởi số lượng ký tự trong chuỗi này chỉ là 22.

3.6. Hàm VLOOKUP

Nguyên nhân gây lỗi: Tham đối ký tự tham chiếu có chứa cao hơn 255 ký tự. Tham đối chỉ mục ở cột có chứa văn bản hay bé hơn 0.

Cách khắc phục lỗi #VALUE trong Excel này: Làm cho giá trị thu gọn lại hay kết hợp 2 hàm lại là INDEX và MATCH để thay thế. 2 hàm này được dùng trên Excel với mục đích tra cứu các giá trị cao hơn 255 ký tự. Khi dùng hàm MATCH và INDEX là chúng ta dùng công thức mảng.

Sẽ có thông báo lỗi #VALUE! khi tham số chỉ mục cột bé hơn 1 chỉ mục là số của cột nằm bên phải cột tìm kiếm nếu như mong muốn của bạn là trả về kết quả trùng khớp. Lỗi này thường phát sinh là do các lỗi VALUE trong Excel, chẳng hạn như đánh máy trong tham số đối chỉ mục cột sẽ đôi khi vô tình chỉ định số nào đó nhỏ hơn 1 để làm giá trị chỉ mục.

Mọi người hãy lưu ý rằng giá trị thấp nhất nằm trong tham đối chỉ mục cột đó là 1. Trong số đó, 1 chính là cột tìm kiếm, 2 là cột đầu tiên ở phía bên phải của cột tìm kiếm,… Vì thế, nếu mọi người tìm tại cột A thì tham chiếu của cột A là số 1, còn của cột B là 2 và tương tự của cột C là 3,…

4. Tổng kết

Hy vọng là bài viết này đã giúp mọi người có thể khắc phục được lỗi #VALUE trong Excel nhanh chóng. Chúc bạn dễ dàng dùng được Excel mà không gặp khó khăn nào nhé.

Đừng quên liên tục theo dõi Dchannel của hệ thống cửa hàng Di Động Việt để cập nhật hết toàn bộ những gì mới nhất về công nghệ. Xin cám ơn các bạn vì đã đọc bài chia sẻ này của mình.

Khi lựa chọn Di Động Việt, khách hàng sẽ nhận được “CHUYỂN GIAO GIÁ TRỊ VƯỢT TRỘI” hơn cả chính hãng. Chúng tôi luôn tận tâm và trách nhiệm trong việc mang đến các giá trị và lợi ích cao nhất cho mỗi khách hàng. Đồng thời đảm bảo quyền lợi cho người dùng khi sử dụng sản phẩm chính hãng với giá hợp lý.

Xem thêm:

Di Động Việt