18 lượt xem

Hướng dẫn sửa lỗi VALUE trong Excel với từng hàm cơ bản | Educationuk-vietnam.org

Lỗi VALUE là một cách để Excel báo cáo: “Đã xảy ra lỗi.” Bạn có thể cần thử một hoặc nhiều giải pháp mới để khắc phục lỗi cụ thể của mình. Hãy cùng 123job tìm hiểu nguyên nhân và cách khắc phục qua bài viết dưới đây nhé!

Cách sửa lỗi giá trị trong hàm Excel như: IF, Sumif, Sumifs Vlookup, Average … Lỗi này sẽ rất chung chung và khó tìm ra nguyên nhân chính xác gây ra lỗi. Trong bài viết này, chúng tôi sẽ trình bày một số nguyên nhân phổ biến, cùng với các giải pháp cho lỗi giá trị. Tôi hy vọng nó sẽ giúp bạn làm cho báo cáo của bạn chuyên nghiệp hơn. Lỗi VALUE là một lỗi phổ biến thường xuyên xảy ra trong Excel. Vậy bạn đã biết cách sửa lỗi trong Excel chưa? Bạn có thể tham khảo bài viết dưới đây.

I. Lỗi VALUE trong excel là gì?

Khi bạn viết hàm trong excel, bạn sẽ gặp các thông báo lỗi Value, #Ref,… chúng ta có thể hiểu là:

Lỗi VALUE trong hàm excel là gì?

Lỗi VALUE trong hàm excel là gì?

Phần thông báo lỗi excel cho chúng ta biết công thức bạn vừa nhập có lỗi ở đâu đó như: Sai cú pháp, tham chiếu có vấn đề, công thức không chính xác … Và lỗi #Value là một lỗi rất chung chung. Do đó, rất khó để biết ngay lỗi là gì. Để có thể tìm ra lỗi trong excel, chúng ta cần phải có những phương pháp và cách thức kiểm tra.

II. Cách sửa lỗi #value trong Excel

1. Lỗi #value trong Sum, hàm trung bình kết hợp Iseror + IF hoặc Iferror

Lý do:
Trong vùng tính tổng và trung bình, có lỗi #Value trong excel. Cách sửa lỗi #Value: Sử dụng công thức trong mảng kết hợp với 2 bộ hàm: If + Issei hoặc Iferror. Ví dụ, chúng ta cần tính tổng doanh thu của tất cả các mặt hàng, cách thực hiện như sau:

Phương pháp 1:
= {SUM (IFERROR (B2: B5, “”))} => Kết quả trả về là: 680

Lưu ý: Công thức này chỉ áp dụng cho excel 2007 trở lên, và cho excel 2003, nó nên được sử dụng trong phương pháp thứ hai bên dưới.
Sau khi nhập công thức tính tổng, bạn cần nhấn Ctrl + Shift + Enter.

Cách 2: = {SUM (IF (ISERROR (B2: B5), “”, B2: B5))} => Kết quả trả về là: 680. Logic tính công thức: Hàm if và hàm Iserror sẽ xác định xem giá trị nào trong phạm vi B2: B5 là giá trị lỗi excel sẽ trả về kết quả trống. Như vậy, các giá trị để tính tổng cuối cùng của hàm sum trong excel sẽ loại bỏ tất cả các giá trị lỗi. Tương tự, chúng ta có thể thay thế cú pháp của hàm Sum bằng hàm Average như sau:
= {AVERAGE (IFERROR (B2: B5, “”))} = 227 hoặc = {AVERAGE (IF (ISERROR (B2: B5), “”, B2: B5))} = 227

READ  Giấy chứng nhận đăng ký đầu tư là gì? | Educationuk-vietnam.org

2. Gặp lỗi #VALUE khi sử dụng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS trong một tệp đã đóng khác

Đây là trường hợp phổ biến mà chúng ta hay sử dụng hàm tính tổng, hàm đếm có điều kiện ở tệp tin khác. Tại thời điểm chúng ta thực hiện các công thức này, cả tệp tính toán và tệp chứa dữ liệu tính toán đều đang mở nên không xuất hiện lỗi giá trị.

Tuy nhiên, khi chúng tôi vô hiệu hóa tệp và chỉ mở tệp tính toán, bạn có thể gặp lỗi #Value trong tất cả các ô liên quan đến các hàm SUMIF, SUMIFs, COUNTIF, COUNTIFs trong Excel. Giải pháp duy nhất cho đến nay đối với lỗi giá trị là: Mở cả hai tệp cùng một lúc rồi nhấn F9 một lần nữa (nếu cần) để cho phép lỗi giá trị trong excel cập nhật lại công thức.

3. Loại bỏ lỗi VALUE khi thực hiện tính toán, sử dụng hàm IF, ISERROR hoặc Iferror hoặc định dạng có điều kiện.

Trong ví dụ trên, tất cả các mặt hàng đều có giá trị như nhau là 200 và dựa vào số lượng, chúng ta sẽ tính được doanh thu một cách đơn giản như sau: Nhưng nếu chúng ta gửi cho sếp một bản báo cáo với giá trị sai số như trong hình trên, nó không tốt chút nào.

Khi đó, hãy xem xét một trong hai lựa chọn sau:

3.1. Đặt lỗi để bất kỳ mặt hàng nào có lỗi số lượng đều trả về giá trị 0.
Bằng cách này, báo cáo sẽ trông đẹp hơn nhiều. Công thức tính thu nhập tại thời điểm đó như sau: C2 = IF (ISERROR (B2), 0, B2 * 200) hoặc C2 = IFERROR (B2 * 100,0)

Kết quả chúng ta nhận được như sau: Cột thu nhập sau này đã ổn, nhưng báo cáo vẫn chưa thực sự tốt khi cột số tiền vẫn xuất hiện trong ô chứa lỗi giá trị. Và giải pháp thứ hai là những gì bạn cần biết.

3.2. Sử dụng định dạng có điều kiện để ẩn bất kỳ lỗi nào trong excel
Thay vì sử dụng công thức trong cột doanh thu, với cách này chúng ta chỉ cần nhập công thức doanh thu = số lượng * giá mỗi đơn vị. Tiếp theo, chúng tôi sẽ đánh dấu các vùng dữ liệu cần ẩn lỗi và sau đó chọn Định dạng có điều kiện> Quy tắc mới.

Trong bảng cài đặt tham số cho định dạng có điều kiện, hãy nhập vào phần Sử dụng công thức để xác định ô cần định dạng như sau:

  • Công thức xác định ô được định dạng: Iserror (B2)
  • Định dạng: Bạn chọn màu chữ là màu trắng
READ  Slot là gì? Slot nghĩa là gì, Sử dụng Slot đúng cách | Educationuk-vietnam.org

Và đây là kết quả mà chúng ta nhận được: Mọi ô trong kết quả đều bị lỗi Giá trị sẽ không xuất hiện trong báo cáo vì màu chữ là màu trắng.

III. Lỗi giá trị trong một số hàm Excel khác

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

Trong khi chạy hàm DAY, một lúc nào đó nó sẽ báo lỗi giá trị trong excel, một lúc nào đó nó sẽ cho kết quả. Lỗi excel này là do định dạng ngày tháng trên máy tính bị lỗi Excel không thống nhất dẫn đến tình trạng trên. Do đó, bạn cần thay đổi định dạng ngày tháng trong hệ thống. Báo cáo lỗi giá trị trong Excel

Bước 1:
Đầu tiên, hãy chuyển đến Bảng điều khiển và sau đó nhấp vào phần Thời gian, Ngôn ngữ và Khu vực. Để chỉnh sửa ngày tháng trong phần mềm Excel. Chuyển sang giao diện mới trong phần Khu vực và nhấp vào Thay đổi định dạng ngày, giờ hoặc số.

Bước 2:
Hiển thị ở dạng thay đổi bảng như: ngày tháng trong máy tính. Trong phần Tùy chọn ngôn ngữ, trong phần Ngày ngắn và Ngày dài, người dùng điều chỉnh kiểu hiển thị như hình. Cuối cùng bấm Áp dụng để có thể lưu thay đổi đó.

Lưu ý khi máy tính là Windows 8 trở xuống thì bạn thực hiện theo các thao tác khác. Đầu tiên, nhấp vào ngày ở cuối màn hình, sau đó nhấp vào Thay đổi cài đặt ngày và giờ… trong khung thời gian. Để bật tắt hộp thoại Date and Time xuất hiện, bạn bấm vào Change Date and Time… Cửa sổ Customize Format sẽ xuất hiện khi bạn chọn tab Date và điều chỉnh thông số Ngày ngắn và Ngày dài như trên.

Bước 3: Quay lại Excel sau đó nhập lại thời gian và thực hiện các hàm ngày tháng, bạn sẽ thấy kết quả chính xác, không bị lỗi như trước.

2. Lỗi #VALUE! trong các hàm FIND / FINDB và SEARCH / SEARCHB

Sự cố: Không thể tìm thấy giá trị trong đối số text_search_string in_text_string
Nếu hàm Excel không thể tìm thấy văn bản cần tìm trong chuỗi văn bản được chỉ định, thì hàm sẽ phát sinh lỗi VALUE.

Ví dụ, một hàm như: = FIND (“găng tay”; “Găng tay (Thanh niên)”; 1)

Nó sẽ xuất hiện lỗi #VALUE! Vì không có kết quả phù hợp nào cho “găng tay” trong chuỗi, chỉ có “Găng tay”. Hãy nhớ rằng hàm FIND có thể phân biệt chữ hoa chữ thường, vì vậy hãy đảm bảo rằng giá trị trong search_text khớp chính xác với chuỗi trong đối số in_text.

Tuy nhiên, hàm TÌM KIẾM này sẽ trả về giá trị 1 vì hàm không phân biệt chữ hoa chữ thường:

READ  Soạn bài Tình yêu và thù hận | Educationuk-vietnam.org

= TÌM KIẾM (“găng tay”; “Găng tay (Thanh niên)”; 1)

Giải pháp: Sửa cú pháp nếu cần.
Vấn đề: Đối số bắt đầu sẽ được đặt thành không (0)
Đối số bắt đầu là một đối số tùy chọn và nếu bạn bỏ qua nó, giá trị mặc định sẽ được giả định là 1. Tuy nhiên, nếu đối số có trong cú pháp và giá trị được đặt thành 0, bạn sẽ gặp phải lỗi VALUE trong Excel . Giải pháp: Loại bỏ đối số bắt đầu nếu nó không được yêu cầu hoặc đặt nó thành giá trị chính xác thích hợp.

Lỗi giá trị trong một số hàm trong Excel

Lỗi giá trị trong một số hàm trong Excel

Vấn đề: Đối số bắt đầu lớn hơn đối số trong văn bản
Ví dụ về tìm hàm: = FIND (“s”; ”Hàm và công thức”; 25). Tìm kiếm “s” trong chuỗi “Hàm và công thức” (in_text) bắt đầu từ ký tự thứ 25 (start_number) nhưng trả về lỗi #VALUE! vì chuỗi chỉ có 22 ký tự.

3. Lỗi #VALUE! trong hàm tra cứu VLOOKUP

Vấn đề: Đối số giá trị tìm kiếm có hơn 255 ký tự.

Giải pháp: Cắt bớt giá trị hoặc sử dụng kết hợp các hàm INDEX và MATCH để thay thế. Sử dụng các hàm INDEX và MATCH trong Excel để tìm kiếm các giá trị có hơn 255 ký tự. Đây là một công thức nhóm. Do đó, hãy nhấn Enter (chỉ khi bạn có Microsoft 365) hoặc Ctrl + Shift + Enter.

Vấn đề: Đối số chỉ mục cột chứa văn bản hoặc nhỏ hơn 0.

Lỗi VALUE xảy ra khi bạn chỉ định chỉ mục cột nhỏ hơn 1. Chỉ mục cột là số cột ở bên phải của cột tra cứu mà bạn muốn trả về nếu tìm thấy kết quả phù hợp. Sự cố này có thể do lỗi giá trị trong excel, chẳng hạn như: lỗi đánh máy trong đối số chỉ mục cột hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục (thường gặp nếu một hàm Excel khác được đặt bên trong hàm Vlookup để trả về một số , chẳng hạn như “0”, làm đối số chỉ mục cột). Giá trị tối thiểu trong đối số chỉ mục cột là 1, trong đó 1 là cột tra cứu, 2 là cột đầu tiên bên phải cột tra cứu … Vì vậy, nếu bạn đang tìm kiếm trong cột A, số 1 sẽ là tham chiếu đến cột, 2 là cột B, 3 là cột C …

IV. Sự kết luận

Với những lời khuyên này, bạn sẽ không học được trong các trường học chính thức, nhưng chúng sẽ rất hữu ích trong việc thực hành của bạn. Hy vọng qua bài viết này sẽ giúp bạn có thêm thông tin và cách sửa lỗi giá trị trong Excel với từng hàm cơ bản. Chúc may mắn

Bài viết cùng chủ đề: