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

Biên tập bởi Nguyễn Thị Kim Chi

Cập nhật 5 tháng trước

1.113

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é!

Hướng dẫn được thực thi trên máy tính HP hệ quản lý và điều hành Windows 10 với phiên bản Excel năm nay. Bạn hoàn toàn có thể thực thi tựa như trên các phiên bản Excel khác như 2007, 2010, 2013 và 2019 .

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

Lỗi # VALUE ! là lỗi thường được hiển thị và thông tin giá trị tác dụng bạn tính được bị lỗi. Lỗi # VALUE ! xảy ra thường có nhiều nguyên do nhưng phần đông lỗi này ta thường thấy là quy 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 chừng trắng là do khi nhập công thức hàm quét phải ô trống không có tài liệu hoặc tài liệu ẩn mà không thấy .
Cách sửa : Bạn phải tìm và vô hiệu những khoảng chừ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 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 list chứa văn bản hay kí tự đặc biệt quan trọng 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 đơn cử để khắc phục lỗi # VALUE ! chứa văn bản ký tự đặc biệt quan trọng :

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 số lượng .

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 loại sản phẩm đều có giá trị giống nhau là 200, dựa theo những số liệu sẵn có ta hoàn toàn có thể tính được lệch giá một cách đơn thuần để nộp báo cáo giải trình 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 giải trình bạn làm hoàn toàn có thể dễ nhìn hơn. Công thức để tính lệch giá lúc này như sau : C2 = IF ( ISERROR ( B2 ), 0, B2 * 200 ) hay C2 = IFERROR ( B2 * 100,0 ) .
Khi đó hiệu quả ta thu được : Cột lệch giá tạo ra đã ổn và giảm những lỗi, tuy nhiên nếu báo cáo giải trình chưa thực sự ổn nếu cột số lượng vẫn Open 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.

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 thiết yếu .
Sự cố : Đối với tham đối số mở màn sẽ được đặt thành không ( 0 ) .
Nếu tham đối số khởi đầu là một tham đối tùy chỉnh, nếu bạn bỏ lỡ 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ố khởi đầ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 ) mở màn 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ẽ Open 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 tác dụng 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, hoàn toàn có thể vô tình chỉ định một số ít nhỏ hơn 1 nhằm mục đích 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 tiên phong 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, …

[info]Công thức đúng của hàm VLOOKUP: = VLOOKUP (lookup_value, ‘[tên tệp] tên trang tính’! Table_array, col_index_num, FALSE)

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 mừng để lại phản hồi bên dưới để Điện máy XANH tương hỗ bạn nhé !

Source: https://thomaygiat.com
Category custom BY HOANGLM with new data process: Hỏi Đáp