Nói không với lỗi VALUE trong Excel chỉ với vài bước đơn giản
bởi Cody Nguyễn
15-02-2022
21 phút để đọc
Lỗi VALUE là một trong những lỗi thường gặp nhất trong Excel. Đối với lỗi này thì có rất nhiều nguyên nhân nhưng chủ yếu là do quá trình nhập liệu. Tuy nhiên, rất nhiều bạn bị rơi vào trường hợp không biết phải xử lý như thế nào khi gặp lỗi này. Vì vậy, Edumall sẽ giải thích cho bạn value trong Excel là lỗi gì, những nguyên nhân và “bí quyết” để xử lý lỗi VALUE trong Excel và một số lỗi khác như #NAME, #REF, #NULL,.. nhanh chóng và hiệu quả.
Lỗi VALUE trong Excel là gì?
Lỗi #VALUE! là lỗi hiển thị giá trị kết quả bị lỗi trong quá trình tính toán. Lỗi này có nhiều nguyên nhân nhưng phần lớn là do sai sót trong quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.
Các hàm có thể dễ bị lỗi VALUE trong excel: AVERAGE, SUM, TRANSPOSE, VLOOKUP, SUMPRODUCT, FIND, SEARCH,…
Cách sửa lỗi VALUE trong Excel
Lỗi #value trong hàm Sum, Average kết hợp hàm Iseror + IF hoặc Iferror
Nguyên nhân: Do các ô trung vùng tính tổng và tính trung bình bị lỗi #Value
Cách khắc phục lỗi #Value: Dùng công thức trong mảng kết hợp với 2 nhóm hàm: If + Isseror hoặc Iferror.
Ví dụ: Muốn tính tổng số hàng bán được, ta thực hiện
Cách 1: ={SUM (IFERROR(B2:B5,””))} => Kết quả trả về là: 680 (Công thức này chỉ áp dụng cho excel 2007 trở lên)
Cách 2: ={SUM(IF(ISERROR(B2:B5),””,B2:B5))} => Kết quả trả về là: 680. (Sau khi nhập công thức sum các bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter)
Ý nghĩa: Hàm if và hàm Iserror sẽ xác định xem giá trị nào trong vùng B2:B5 là giá trị lỗi và sẽ cho kết quả là trống. Khi đó, để tính tổng bằng hàm SUM, Excel sẽ nhận giá trị của ô đó là 0 và hoàn toàn bỏ qua nó
Tương tự như vậy thì ta có thể thay thế cú pháp hàm Sum bằng với hàm Average như sau:
={AVERAGE(IFERROR(B2:B5,””))} = 227
={AVERAGE(IF(ISERROR(B2:B5),””,B2:B5))} = 227
Lỗi #VALUE khi dùng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS sang một file khác bị đóng
Nguyên nhân: Do dùng hàm để tính tổng, hàm đếm có điều kiện trên một file khác và lúc nhập công thức thì cả file tính toán lẫn file chứa dữ liệu đều được mở nên sẽ không thấy lỗi. Nhưng khi chỉ mở mỗi file tính toán thì sẽ gặp lỗi #Value ở tất cả các ô liên quan tới hàm SUMIF, SUMIFs, COUNTIF, COUNTIFs
Giải pháp: Mở cùng một lúc cả 2 file sau đó nhấn lại F9 (nếu cần) để lỗi value trong excel cập nhật lại công thức.
Nguyên nhân: Định dạng ngày tháng trong máy tính và trong Excel không đồng nhất
Cách khắc phục: Thay đổi định dạng ngày tháng trên hệ thống
Bước 1: Vào Control Panel > chọn vào phần Clock, Language, and Region
Bước 2: Nhấn vào Change date, time, or number formats ở phía dưới Region để mở hộp thoại chỉnh sửa định dạng thời gian.
Bước 3: Chọn thẻ Format > Đến phần Short date > Chọn kiểu định dạng đúng với dữ liệu ngày tháng bạn muốn nhập (Ở đây là yyyy-MM-dd) > Nhấn Apply > OK.
Lỗi #VALUE! trong các hàm FIND/FINDB và SEARCH/SEARCHB
Sự cố: 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
Nguyên nhân: Do không có kết quả khớp với yêu cầu. Lỗi này có thể do quá trình nhập dữ liệu hay nhập tìm kiếm sai chính tả hoặc do Excel có phân biệt chữ hoa/thường.
Cách khắc phục: Đảm bảo giá trị tìm kiếm văn bản khớp chính xác với chuỗi trong tham đối trong văn bản.
Sự cố: Hàm SEARCH trả về giá trị là 1, bởi vì
Nguyên nhân: Do hàm không phân biệt chữ hoa/thường: =SEARCH(“gloves”;”Gloves (Youth)”;1)
Giải pháp: Sửa cú pháp khi cần thiết.
Sự cố: Tham đối số bắt đầu sẽ được đặt thành không (0)
Nguyên nhân: tham đối tùy chọn được mặc định là 1 trong trường hợp bị bỏ qua nhưng trong cú pháp, tham đối hiện diện trong cú pháp và giá trị được đặt thành 0.
Giải pháp: Loại bỏ tham đối số bắt đầu nếu không bắt buộc hoặc đặ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
Ví dụ hàm Find: =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ự.
Bạn muốn học Excel nhưng không biết mới là nơi dạy tốt và chất lượng? Bạn sắp ra trường nhưng lại không biế tý công thức gì về Excel? Hãy đăng ký ngay Khóa học Excel từ cơ bản đến nâng cao để biến nỗi ám ảnh Excel trở thành điểm mạnh của mình nhé!
Sự cố: Tham đối giá trị tra cứu đang có nhiều hơn 255 ký tự.
Giải pháp: Rút ngắn lại giá trị hay sử dụng kết hợp hàm INDEX và MATCH như là một giải pháp để thay thế để tra cứu các giá trị có nhiều hơn 255 ký tự.
Sự cố: Tham đối số chỉ mục cột chứa văn bản hay nhỏ hơn 0.
Nguyên nhân: Xảy ra khi bạn tham đối chỉ mục cột nhỏ hơn 1.
Giải pháp: Chỉ mục Cột chính là số của cột ở bên phải cột tìm kiếm mà bạn muốn trả về nếu tìm ra kết quả trùng khớp nên nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 là cột B, 3 là cột C…
Một số lỗi #VALUE khác
Lỗi #VALUE! do có khoảng trắng giữa các giá trị
Nguyênnhân: Khi nhập công thức hàm quét phải ô trống có chứa dữ liệu ẩn mà không thấy.
Ví dụ: Như hàm SUM ở hình minh họa.
Cách khắc phục: Tìm và loại bỏ khoảng trắng. Sau đó, kiểm tra lại công thức hàm.
Bước 1: Chọn ô được tham chiếu
Bước 2: Trên tab Home > Find & Select > Replace
Bước 3: Xuất hiện hộp thoại, ở mục Find What nhập một khoảng trắng đơn, ở mục Replace with xóa mọi thứ có thể nằm trong hộp.
Bước 4: Chọn Replace All
Nếu sau khi thực hiện nhưng vẫn chưa xử lý được lỗi VALUE, hãy thực hiện tiếp các bước sau
Bước 5: Chọn vùng dữ liệu bảng tính bị lỗi > Vào thẻ Date > Filter
Bước 6: Nhấn chuột vào mũi tên hướng xuống ở góc phải ô dữ liệu đầu tiên > Chọn Blanks và mọi hộp kiểm không có bất kỳ nội dung gì bên cạnh > OK. Lúc này, các ô chứa khoản trống xuất hiện
Bước 7: Dùng phím Delete để xóa mọi ký tự ẩn trong các ô.
Và đây là kết quả sau khi đã sửa lỗi
Lỗi #VALUE! do có các ký tự đặc biệt
Nguyên nhân: Khi bạn thao tác nhập lệnh có ô dữ liệu chứa phải ký tự.
Ví dụ: Như hàm SUM ở hình minh họa mình đã chọn phải dãy tham chiếu chứa ô ký tự nên Excel báo lỗi #VALUE! như hình.
Cách khắc phục: Tìm và loại bỏ ký tự. Sau đó, kiểm tra lại công thức hàm.
Bước 1: Dùng hàm ISTEXT để kiểm tra xem có ký tự chữ nào không
Bước 2: Bạn nhấn phím Enter và dùng trỏ chuột kéo kết quả vừa hiển thị xuống tới cuối ô dữ liệu muốn kiểm tra để sao chép.
Bước 3: Theo công thức này, ô nào có ký tự chữ sẽ được báo TRUE. Vì vậy, hãy dựa vào kết quả được trả về để sửa cho đúng
Và đây là kết quả sau khi đã sửa lỗi
3.3. Lỗi #VALUE! khi sử dụng các dấu toán học (+, *)
Nguyên nhân: Thường gặp khi sử dụng toán tử là cộng từng con số.
Ví dụ: Như trong hình minh họa, mình nhập cộng từng ô dữ liệu với nhau.
Sau khi nhập công thức như hình trên thì Excel báo lỗi #VALUE!
Cách khắc phục: Thay vì tính bằng dấu “+” thì hãy tính bằng hàm SUM với công thức =SUM(E2:E6). Khi đó, kết quả sẽ là
Ngoài các nguyên nhân trên, có thể lý do của lỗi VALUE đến từ vấn đề với các phép trừ. Vì vậy, hãy thử các cách dưới đây xem sao nhé!
Trừ một tham chiếu ô từ một ô khác
Nhập hai giá trị vào hai ô riêng biệt. Ở ô thứ ba, trừ một tham chiếu ô từ một ô khác. Trong ví dụ này, ô D2 chứa số tiền được lập ngân sách và ô E2 chứa số tiền thực tế. F2 chứa công thức =D2-E2.
Sử dụng công thức SUM với các số dương và số âm
Nhập một giá trị dương vào một ô và một giá trị âm vào ô còn lại. Ở ô thứ ba, sử dụng hàm SUM để cộng hai ô với nhau. Ở ví dụ này, ô D6 chứa số tiền được lập ngân sách và ô E6 chứa số tiền thực tế dưới dạng một số âm. F6 chứa công thức =SUM(D6,E6).
Nếu bạn đang sử dụng Windows thì bạn có thể gặp phải lỗi #VALUE! ngay cả khi thực hiện công thức trừ cơ bản nhất thì hãy thử xử lý bằng các giải pháp dưới đây:
Bước 1: Thực hiện một lượt kiểm tra nhanh: Mở cửa sổ làm việc mới, nhập số 2 vào ô A1. Nhập số 4 vào ô B1. Sau đó, hãy nhập công thức này =B1-A1 vào ô C1. Nếu bạn nhận được lỗi #VALUE! , đi tới bước tiếp theo. Nếu bạn không gặp lỗi, hãy thử các giải pháp khác trên trang này.
Bước 2: Trong Windows, hãy mở panel điều khiển cho Khu vực của bạn.
Windows 10: Bấm vào Bắt đầu, nhập Khu vực, rồi bấm vào panel điều khiển Khu vực.
Windows 8: Ở màn hình Bắt đầu, nhập Khu vực, bấm vào Thiết đặt, rồi bấm vào Khu vực.
Windows 7: Bấm vào Bắt đầu, nhập Khu vực, rồi bấm vào Khu vực và ngôn ngữ.
Bước 3: Trên tab Định dạng, bấm vào Thiết đặt bổ sung.
Bước 4: Tìm Dấu tách danh sách. Nếu Dấu tách danh sách được đặt là dấu trừ, hãy thay đổi dấu tách đó thành một ký hiệu khác. Ví dụ: dấu phẩy là dấu tách danh sách phổ biến. Dấu chấm phẩy cũng là dấu tách danh sách phổ biến.
Bước 5: Bấm vào OK.
Bước 6: Mở sổ làm việc của bạn. Nếu một ô có chứa lỗi #VALUE! , hãy bấm đúp vào để chỉnh sửa.
Bước 7: Nếu xuất hiện dấu phẩy ở nơi nên là dấu trừ cho phép trừ, hãy thay đổi những dấu phẩy đó thành dấu trừ.
Bước 8: Nhấn ENTER.
Bước 9: Lặp lại quy trình này cho các ô khác chứa lỗi.
Sử dụng hàm DATEDIF khi trừ ngày tháng
Nhập hai ngày vào hai ô riêng biệt. Ở ô thứ ba, hãy sử dụng hàm DATEDIF để tìm ra chênh lệch giữa các ngày. Để biết thêm thông tin về hàm DATEDIF, hãy xem mục Tính chênh lệch giữa hai ngày.Lưu ý: Khi thực hiện cách này, ngày tháng phải được căn tự động ở bên trái. Nếu không, có nghĩa là ngày ở đây thuộc dạng văn bản và dĩ nhiên, Excel sẽ không nhận văn bản để thực hiện tính toán.
Các cách khắc phục lỗi VALUE trong Excel khác
Nếu xử lý bằng các cách trên không được thì hãy thử cách này xem sao
Bước 1: Chọn ô công thức bị báo lỗi value trong Excel
Bước 2: Công thức > Đánh giá Công thức > Đánh giá. Trong ví dụ này, bạn thấy công thức =E2+E3+E4+E5 sẽ bị ngắt do có một khoảng trắng ẩn trong ô E2 nhưng chắc chắn bạn không thể thấy được khoảng trắng đó chỉ bằng cách nhìn vào ô E2. Tuy nhiên, bạn có thể thấy được khoảng trắng đó tại đây. Khoảng trắng đó có dạng ” “.
Các lỗi công thức khác hay gặp trong Excel cơ bản
Lỗi #NUM!
Nguyên nhân:
Do công thức yêu cầu tham số là một giá trị số nhưng lại được người dùng đưa vào một giá trị không phải là số hoặc không hợp lệ hoặc
Do kết quả của phép tính trong Excel quá lớn hoặc quá nhỏ
Lỗi NUM trong Excel
Giải pháp: Sửa lại công thức để hợp lệ với yêu cầu của Excel
Lỗi #NA
Nguyên nhân: Các hàm tìm kiếm VLOOKUP, HLOOKUP, MATCH, LOOKUP không tìm được giá trị cần tìm.
Cách khắc phục: Lồng các hàm tìm kiếm với hàm xử lý lỗi IFNA hoặc IFERROR để giá trị trả về không khi bị lỗi #NA
Lỗi #DIV/0!
Nguyên nhân: Xảy ra khi trong 1 công thức Excel xuất hiện phép chia cho 0 vì trong toán học, phép chia này không thực hiện được nên Excel sẽ báo lỗi
Cách khắc phục:
Đảm bảo ràng mẫu số hoặc các ô chứa số chia không được bằng 0 hoặc rỗng
Sử dụng hàm IFERROR để kiểm soát lỗi gặp phải nếu có khả năng xảy ra trường hợp chia cho 0 trong dữ liệu của bạn.
Ví dụ có bảng dữ liệu sau và cột Total được tính bằng tổng của các cột B2, C2, D2
Sau khi một cột trong công thức bị xoá, lỗi #REF! sẽ phát sinh:
Cách khắc phục:
Sử dụng phím tắt CTRL + Z để có thể lấy lại cột vừa bị xoá (chức năng Undo)
Để hạn chế lỗi #REF! nên sử dụng công thức theo cú pháp =SUM(B2:D2)
Lỗi #REF! do sử dụng giá trị cho tham số không phù hợp trong hàm VLOOKUP
Ví dụ có bảng dữ liệu dưới đây và yêu cầu tìm kiếm 5 cột nhưng vùng tra cứu chỉ có 4 cột nên kết quả là trả về lỗi REF
Cách khắc phục: Sửa lại công thức VLOOKUP cho phù hợp =VLOOKUP(A8,A2:D5,4,FALSE)
Lỗi #REF! do sử dụng tham số Row hoặc Column của hàm Index không phù hợp
Ví dụ ở bảng số liệu bên dưới, tham số ROW và COLUMN đều là 5, vượt quá kích thước của vùng dữ liệu B2:E5 nên đã gây ra lỗi #REF!
Cách khắc phục: Sửa lại công thức cho phù hợp: =INDEX(B2:E5,4,4)
Lỗi #REF! khi sử dụng hàm Indirect tham chiếu tới file excel đóng
Lỗi này xảy ra khi tham chiếu tới một file Excel đang đóng trong hàm Indirect, hàm này sẽ trả về lỗi #REF!
Cách khắc phục: Mở file Excel đang đóng.
Lỗi #NAME
Lỗi #NAME? do viết sai tên hàm
Ví dụ: Hàm dưới đây nhập sai công thức COUNTIF
Lỗi #NAME do viết sai hàm
Cách khắc phục: Kiểm tra lại tên hàm đã được viết đúng chưa và sử dụng tính năng Function Arguments như hình dưới
Lỗi #NAME! khi name sử dụng chưa được khai báo trong Excel
Ví dụ: tên “Profit” trong công thức sau đây chưa được khai báo trong Excel
Cách khắc phục: Khai báo tên này trong Excel sử dụng chức năng Name Manager
Lỗi #NAME! khi name sử dụng bị viết sai trong Excel
Ví dụ: Chữ “Profit” ở công thức dưới bị viết sai chính tả
Cách khắc phục: Sửa lại tên này trong Excel sử dụng chức năng Name Manager
Lỗi #NAME! do dữ liệu kiểu chữ không có dấu ngoặc kép bao quanh
Ví dụ: Chữ “has” ở công thức dưới chưa được đặt trong dấu ngoặc kép
Cách khắc phục: kiểm tra lại công thức và bổ sung ngoặc kép đúng chỗ
Lỗi #NAME! do thiếu dấu” : “ngăn cách giữa địa chỉ vùng trong Excel
Ví dụ: Công thức B2B12 bị viết nhầm -> Phải là B2:B12 mới đúng
Cách khắc phục: kiểm tra lại công thức và bổ sung dấu hai chấm đúng chỗ
Lỗi #NAME! do sử dụng 1 hàm hỗ trợ bởi Addin nhưng Addin chưa được cài đặt
Hàm có thể được hỗ trợ bởi Addin hoặc là 1 hàm UDF tự viết và không có trong Workbook bạn đang làm việc
Cách khắc phục: Cài đặt bổ sung Addin hoặc Code của UDF
Lỗi #NULL
Lỗi #NULL! xảy ra khi công thức lấy phần giao nhau giữa 2 vùng không giao nhau. Ví dụ như sau:
Như vậy, bài viết đã chia sẻ cho bạn cách để Nói không với lỗi VALUE trong Excel chỉ với vài bước đơn giản, và xử lý triệt để các lỗi như #NAME, #REF, #NULL,… Hy vọng những kiến thức này sẽ giúp ích cho bạn trong công việc cũng như học tập. Chúc bạn thành công nhé!
Tác giả
Cody Nguyễn
Cody Nguyễn là du học sinh Mỹ với hơn 10 năm kinh nghiệm trong lĩnh vực giáo dục, tập trung vào các kỹ năng phát triển con người. Các bài viết của Cody cho siêu thị khóa học trực tuyến Edumall đã thu hút hàng triệu lượt xem và giúp cho hàng triệu học viên cải thiện kỹ năng cá nhân và thành công hơn trong công việc và cuộc sống.
-800x450.jpg "Mở Control Panel > Click vào Clock & Region")
-800x450.jpg "Nhấn vào Change date, time, or number formats")
-800x450.jpg "Chọn đúng kiểu định dạng cần nhập vào trong Excel")
.jpg "Lỗi #VALUE! do có khoảng trắng.")
.jpg "Chọn DATA và chọn Filter.")
.jpg "Khắc phục lỗi #VALUE!.")
.jpg "Lỗi #VALUE! do có các ký tự đặc biệt.")
.jpg "Tạo cột kiểm tra ISTEXT(B2).")
.jpg "Dùng trỏ chuột kéo sao chép kết quả để kiểm tra.")
.jpg "Khắc phục lỗi #VALUE!.")
.jpg "Cộng từng ô dữ liệu.")
.jpg "Lỗi #VALUE!.")
.jpg "Khắc phục lỗi #VALUE!.")
Nếu bạn đang sử dụng Windows thì bạn có thể gặp phải lỗi #VALUE! ngay cả khi thực hiện công thức trừ cơ bản nhất thì hãy thử xử lý bằng các giải pháp dưới đây:
