Nói không với lỗi VALUE trong Excel chỉ với vài bước đơn giản

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.

Lỗi #VALUE! khi sử dụng hàm DAYS

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

Lỗi #VALUE! trong các hàm dò tìm VLOOKUP

• 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ên nhâ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 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

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à

Các vấn đề với phép trừ

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).

Sử dụng hàm DATEDIF khi trừ ngày tháng

Các cách khắc phục lỗi VALUE trong Excel khác

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ỏ

• 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.

Lỗi #REF!

Lỗi #REF! do xoá vùng tham chiếu trong công thức

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

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