TIN CÔNG NGHỆ, TIN CÔNG TY, TIN TỨC
Xử lý lỗi #VALUE trong Excel
Th9
Xử lý lỗi #VALUE trong Excel
Excel là 1 công cụ đối cần thiết đối với văn phòng, học tập. Trong khi bạn đang thao tác thực hiện các hàm, nhưng công thức lại ra lỗi #VALUE! trong Excel. Vậy cách xử lý lỗi #VALUE trong Excel là gì? Cùng với Thiên Sơn Computer theo dõi bài này nhé.
1.Lỗi #VALUE! ở Excel là gì?
Lỗi #VALUE! thường được hiển thị và thông báo giá trị kết quả người dùng tính đã bị lỗi. Lỗi #VALUE! xảy ra sẽ có nhiều nguyên nhân nhưng phần lớn là quá trình nhập công thức hoặc nhập ô tham chiếu bị lỗi.
1 số hàm thường gặp lỗi #VALUE! là: SUM, TRANSPOSE, VLOOKUP, 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 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 bị ẩn.
Bạn tìm và loại bỏ những khoảng trắng và kiểm tra công thức hàm
Cách xử lý , khắc phục
Khắc phục dấu cách trong khoảng trắng
B1: Tìm những ô có khoảng trắng > Chọn những ô tham chiếu > Ấn Ctrl + H để mở hộp thay thế.
B2: Trong hộp Find and Replace > Nhập ô Find What dấu cách > Trong mục Replace with bạn không nhập > Nhấn Replace All để bỏ các khoảng trắng.
Dùng bộ lọc
B1: Chọn toàn bộ vùng dữ liệu bị lỗi, trỏ chuột lên thanh tiêu đề > Chọn Data > Filter.
B2: Click chuột và mũi tên góc phải bên dưới của ô dữ liệu đầu tiên > Chọn hết các ô và không chọn ô Blanks > OK.
B3: Loại bỏ khoảng trắng và bạn kiểm tra lại hàm. Excel sẽ hiện lên kết quả.
3. Lỗi #VALUE! khi có chứa văn bản hoặc kí tự đặc biệt
Lỗi #VALUE! khi danh sách chứa văn bản hoặc kí tự đặc biệt, khi bạn nhập vào ô mà bạn sử dụng để tính hàm có chứa các ký tự.
Khắc phục: Tìm vào loại bỏ các kí tự đặc biệt , kiểm tra lại những công thức của hàm.
B1: Tạo thêm 1 cột TEST ở ngay bên cạnh cột mà bạn muốn tính > Sử dụng hàm ISTEXT để kiểm tra.
B2: Ấn Enter > Kéo trỏ chuột tới vị trí còn lại để hiện lên kết quả kiểm tra quá trình sao chép.
B3: Bạn kiểm tra và chỉnh sửa lại những kết quả TRUE. Sau đó chọn Excel sẽ hiện lên kết quả như dưới đây là thành công.
4. 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 ” + ” và dấu ” * ” khi người dùng sử dụng toán tổng là cộng từ con số.
Sau khi nhập công thức thì hàm Tổng báo có lỗi #VALUE! như dưới đây:
Khắc phục: Sử dụng hàm SUM để quét lại những dữ liệu cần tính.
Chúng tôi ví dụ sẽ dùng hàm =SUM(E2:E6) nên ta có như hình :
5. Lỗi #VALUE! xảy ra ở 1 số hàm
Lỗi #VALUE! khi kết hợp với hàm SUM, AVERAGE với hàm dữ liệu ISERROR, IF, IFERROR.
Trong chỗ tính tổng hoặc tính trung bình có lỗi #VALUE!
Khắc phục: Dùng công thức kết hợp giữa 2 hàm là hàm IF và ISERROR hay IFERROR.
Ví dụ: ={AVERAGE(IF(ISERROR(B2:D2,””,B2:D2))}
Công thức áp dụng Excel 2007 trở lên nhé.
Lỗi #VALUE! khi bạn dùng các hàm như: SUMIF, SUMIFS, COUNTIF, COUNTIFS
Đây cũng là 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 1 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 được mở sẽ không có lỗi.
Khi tắt file đi chỉ mở 1 file tính toán thì lỗi #VALUE! sẽ xuất hiện ở tất cả các ô liên quan tới hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS.
Khắc phục: Mở 2 file cùng 1 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ần có điều kiện
Trong quá trình lập hàm chưa đủ theo trình tự và tạo ra khoảng trống trong bài dẫn đến thiếu sót và tạo lỗi #VALUE!
Khắc phục 1: Những lỗi mặt hằng nào bị lỗi số lượng thì trả kết quả giá trị là 0.
Khi giá trị 0 thì báo cáo bạn làm sẽ dễ nhìn hơn. Công thức tính doanh thu lúc này là: C2= IF(ISERROR(B2),0,B2*200) hoặc C2= IFERROR(B2*100,0).
Kết quả: Cột doanh thu tạo ra đã giảm ra những lỗi, tuy nhiên nếu báo cáo chưa thực sự ổn và xảy ra những lỗi #VALUE
Khắc phục 2: Sử dụng conditional formatting để làm ẩn các lỗi trong Excel.
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á. Chọn bôi đen những vùng dữ liệu cần ẩn lỗi rồi chọn Conditional formatting > New Rules.
Khi dùng bảng thiết lập thông tin có điều kiện thì nhập trong phần Use a formula to determines which cells to format :
- Iserror: Công thức xác định những ô đã được định dạng.
- Formatting: Có thể chọn màu chữ là màu trắng.
Lỗi #VALUE! khi dùng hàm DAYS
Trong quá trình thực hiện hàm DAYS thì 1 số ô thời gian sẽ báo lỗi #VALUE!. Lỗi này là do không có sự đồng nhất về định dạng Excel với ngày giờ trên máy tính. Nên bạn định dạng ngày tháng trên hệ thống máy tính.
Khắc phục:
B1: Bạn hãy truy cập vào Control Panel trên máy.
B2: Chọn Clock and Region để sửa ngày tháng trên phần mềm Excel.
B3: Bạn chọn mục Date and Time.
B4: Chọn mục Change date and time để thay đổi thời gian trên máy tính.
B5: Bạn điều chỉnh ngày giờ phù hợp và > OK.
B6: Trở lại Excel, nhập thời gian và thực hiện các hàm ngày tháng.
Lỗi #VALUE! khi dùng hàm VLOOKUP
Đối với ký tự tham chiếu đang có nhiều hơn 255 ký tjw. Tham đối chỉ mục cột chứa văn bản hoặc nhỏ hơn 0.
Khắc phục: Làm gọn giá trị hoặc kết hợp hàm INDEX và MATCH để làm giải pháp thay thế. Bạn dùng hàm INDEX và MATCH trong Excel để tra cứu những giá trị có nhiều hơn 255 ký tự. Dùng hàm INDEX và MATCH chính là dùng công thức mảng.
Lỗi tham đối chỉ mục cột chứa văn bản hoặc nhỏ hơn 0. Lỗi #VALUE! sẽ hiện ra 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. Bạn muốn trả về tìm kết quả trùng khớp, Điều này thường xảy ra do những lỗi #VALUE! trong Excel như đánh máy trong tham số chỉ mục cột.
Bạn hãy nhớ giá trị tối thiểu trong tham đối chỉ mục cột là 1, 1 là cột tìm kiếm, 2 là cột đầu tiên bên phải của cột tìm kiếm. Nếu bạn tì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 và lần lượt.
