Trược đây, chung ta chỉ biết nguyên nhân dẫn đến Lỗi #VALUE! khi bạn dùng công thức yêu cầu dạng số nhưng dữ liệu bạn nhập lại là khoảng trắng hoặc ký tự hoặc văn bản. Trong bài hôm nay, hãy cùng Blog Hướng Dẫn Công Nghệ, đi sâu hơn về những khả năng gây ra lỗi này và cách sửa lỗi #VALUE trong excel chính xác nhất.
Nội dung chính:
- Lỗi #VALUE! trong Excel là gì?
- Lỗi #VALUE! khi ô trống tham chiếu chứa khoảng trắng
- Lỗi #VALUE! khi danh sách chứa văn bản hay kí tự đặc biệt
- Lỗi #VALUE! khi dùng các dấu toán học
- Lỗi #VALUE! xảy ra ở một số hàm
Lỗi #VALUE! trong Excel là gì?
Lỗi #VALUE! có thể coi như là một thông báo “Đã xảy ra lỗi với cách nhập công thức của bạn. Hoặc đã xảy ra lỗi với các ô bạn đang tham chiếu tới.” Có rất nhiều nguyên nhân dẫn đến lỗi này, nên khi xuất hiện lỗi này chúng ta chỉ biết được chung chung chứ không rõ là mình đã sai ở đâu. Để tìm được chỗ mình đã sai và có thể sửa được lỗi này thì hãy theo dõi phần dưới nhé:
Một số hàm thường gặp lỗi #VALUE! là: AVERAGE, SUM, TRANSPOSE, VLOOKUP, SUMPRODUCT, FIND, SEARCH,…
Lỗi #VALUE! khi ô trống tham chiếu chứa khoảng trắng
Lỗi #VALUE! thường xảy ra vì công thức của bạn liên quan tới các ô khác có chứa khoảng trắng hay thậm chí là phức tạp hơn: các khoảng trắng ẩn. Các khoảng trắng này có thể làm cho ô có vẻ như trống, trong khi thực tế là chúng không trống.
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ụ: Ở hình bên dưới, mình dùng hàm SUM
Cách khắc phục
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 tìm kiếm và thay thế lên.
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. Có nghĩa là bạn tìm dấu cách đơn và Thay thế bằng, xóa mọi thứ có thể nằm trong hộp.
Đôi khi, còn có ký tự ẩn khác ngoài các khoảng trắng có thể làm cho ô có vẻ như trống, trong khi ô thực sự không trống. Dấu nháy đơn trong ô có thể tạo ra hiện tượng này. Để xử lý vấn đề này ta phải dùng đến 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:BẤm vào mũi tên phía 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. Nếu thành công thì kết quả của công thức sẽ thay thế cho lỗi đó. Trường hợp bạn có nhiều công thức tính, và vẫn xuất hiện lỗi #Value, hãy lập lại quy trình cho các ô khác được công thức cảu bạn tham chiếu tới.
Lỗi #VALUE! khi danh sách chứa văn bản hay kí tự đặc biệt
Văn bản hoặc các ký tự đặc biệt trong ô có thể tạo ra lỗi #VALUE!. Nhưng nếu danh sách các ô mà bạn tham chiếu quá nhiều thì làm sao mà kiểm tra nổi. Lúc này chúng ta cần một hàm để tìm ra các ô gây ra lỗi đó.
Giải pháp: Sử dụng hàm ISTEXT để kiểm tra các ô. Hãy chú ý rằng hàm này sẽ không giải quyết được lỗi mà chỉ tìm ra được ô gây ra lỗi đó mà thôi.
Các bước thực hiện như sau:
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, để chứa giá trị trả về sau khi thực hiện hàm ISTEXT > Sử dụng hàm ISTEXT để kiểm tra.
Bước 2: Nhấn Enter >SAu đó nhấn giữ chuột vào cuối ô vừa xuất hiện kết quả, và kéo tiếp xuống những ô còn lại cần kiểm tra.
Bước 3: Sau khi kiểm tra thì tất cả các ô đều ổn, trừ những ô có kết quả TRUE. Điều bạn cần làm là tìm những ô này và xóa đi kí tự văn hả hoặc kí tự đặc biệt thì cũng sẽ giải quyết được lỗi #VALUE!.
Lỗ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: Hãy dùng hàm SUM trong mục đích tính tổng như vầy. Trong ví dụ này sử dụng hàm: =SUM(E2:E6) nên ta có hình minh họa như sau:
Lỗ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.
Ví dụ: ={AVERAGE(IF(ISERROR(B2:D2,””,B2:D2))}
Lỗi #VALUE! khi dùng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS
Nguyên nhân: Đây là trường hợp thường xảy ra trong các công thức tính hàm tổng, hàm đếm có điều kiện trên 1 File khác. Nếu cả file tính toán và file chứa dữ liệu tính đều mở thì sẽ không có lỗi.
Nhưng nếu chỉ mở 1 file tính toán thì sẽ có lỗi #VALUE! tại những ô liên quan đến hàm SUMIF, SUMIFS, COUNTIF, COUNTIF
Cách khắc phục: Mở đồng thời 2 file, sau đó nhận phím F9 để Excel cập nhật lại công thức tính.
Lỗi #VALUE! khi dùng hàm IF, ISERROR hoặc định dạng có điều kiện
Nguyên nhân: 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à xuất hiện lỗi #VALUE!.
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 sau:
Giải pháp số 1: Những 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.
Thay công thức ở cột Doanh thu bằng 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!. Nguyên nhân chính dẫn đến lỗi này do định dạng ngày giờ của máy tính khác với trong Excel. Để sửa lỗi này cần chỉnh lại định dạng ngày giờ của máy tính.
Xem thêm 2 cách cài đặt giờ trên Win 10 nhanh chóng
Cách sửa lỗi đơn giản:
Bước 1: Mở 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à lỗi #VALUE! sẽ biến mất.
Lỗi #VALUE! khi dùng hàm tìm kiếm FIND và SEARCH
-
Hàm FIND và SEARCH rất giống nhau. Cả hai đều có chung 1 cơ chế hoạt động – định vị một ký tự hoặc một chuỗi văn bản trong một chuỗi văn bản khác. Sự khác biệt giữa hai hàm này là hàm FIND có phân biệt chữ hoa/thường, còn SEARCH thì không phân biệt chữ hoa/thường. Vì vậy, nếu bạn không muốn so khớp cách viết hoa trong chuỗi văn bản thì sử dụng hàm SEARCH.
Không tìm được giá trị trong tham đối văn_bản_tìm_kiếm trong chuỗi trong_văn_bản
Nếu hàm không thể tìm thấy văn bản cần tìm trong chuỗi văn bản đã xác định, hàm sẽ đưa ra kết quả #VALUE! lỗi.
Giải pháp là đổi cú pháp khi dùng.
Ví dụ:
.=FIND(“gloves”;”Gloves (Youth)”;1)
Sẽ đưa thông tin #VALUE! vì không có “gloves” nào trùng khớp trong chuỗi nhưng có “Gloves”. Hãy nhớ rằng định dạng chữ FIND có dạng chữ hoa/thường, vì vậy hãy đảm bảo giá trị find_text có kết quả khớp chính xác trong chuỗi ở within_text đối số.
Tuy nhiên, hàm SEARCH này sẽ trả về giá trị 1, vì hàm không phân biệt chữ hoa/thường:
-
=SEARCH(“gloves”;”Gloves (Youth)”;1)
Tham đối số_bắt_đầu được đặt thành không (0)
- =FIND(“s”;”Functions and formulas”;25)
Tìm kiếm “s” trong chuỗi “Functions and formulas” (trong_văn_bản) bắt đầu từ ký tự thứ 25 (số_bắt_đầu) nhưng trả về lỗi #VALUE! vì chuỗi chỉ có 22 ký tự.
Giải pháp: Sửa số bắt đầu nếu cần.
Lỗi #VALUE! khi dùng hàm VLOOKUP
Khi dùng hàm VLOOKUP bạn có lẽ sẽ gặp phải lỗi này khá nhiều lần.
Tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự
Giải pháp: 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.
Tham đối số_chỉ_mục_cột chứa văn bản hoặc nhỏ hơn 0
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,…
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)
Trong bài viết này, Blog Hướng Dẫn Công Nghệ đã đưa ra tất cả các giả thuyết có thể gây ra lỗ #VALUE mà người dùng Excel có thể mắc phải. Hy vọng bài viết trên về cách sửa lỗi #VALUE trong Excel có ích với mọi người. Cảm ơn vì đã theo dõi.
