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ị. Hi vọng sẽ giúp ích được cho các bạn trong việc làm báo cáo chuyên nghiệp hơn. Lỗi VALUE là một lỗi phổ biến thường 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ẽ nhận được thông báo lỗi Value, #Ref, v.v., có thể hiểu là:
Lỗi VALUE trong hàm excel là gì?
Phần thông báo lỗi của excel cho chúng ta biết công thức bạn vừa nhập bị lỗi ở đâu đó như: Sai cú pháp, tham chiếu có vấn đề, sai công thức … Và lỗi #Value là một lỗi rất chung chung. Vì vậy, rất khó để biết ngay đó là lỗi gì. Để có thể tìm ra lỗi excel, chúng ta phải có các phương pháp và biện pháp kiểm tra.
II. Cách sửa lỗi #value trong Excel
1. Lỗi #value trong hàm Sum, Average kết hợp Iseror + IF hoặc Iferror
Nguyên nhân: Trong vùng tính tổng và trung bình bị 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 nhóm 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:
Cách 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, còn đối với excel 2003 thì phải sử dụng theo phương pháp thứ 2 bên dưới. Sau khi nhập công thức tính tổng, bạn phải 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 toán của công thức: Hàm if và hàm Iserror sẽ xác định xem giá trị nào trong khoảng B2: B5 là giá trị lỗi excel sẽ trả về kết quả là 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 cú pháp 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
2. Gặp lỗi #VALUE khi sử dụng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS cho 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 trê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 có lỗi giá trị nào xuất hiện.
Tuy nhiên, khi chúng ta tắt tệp và chỉ mở mỗi tệp tính toán, bạn có thể gặp lỗi #Value ở 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 lần nữa (nếu cần) để 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, 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 toán doanh thu đơn giản như sau: Nhưng nếu chúng ta gửi cho sếp một báo cáo với giá trị lỗi toe Wide như hình trên thì không. tốt ở tất cả.
Sau đó, hãy nghĩ đến một trong hai tùy chọn sau:
3.1. Bẫy lỗi để mọi mặt hàng có lỗi số lượng đều trả về giá trị bằng 0. Điều này làm cho báo cáo trông đẹp hơn nhiều. Công thức tính doanh thu 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ả ta nhận được như sau: Cột doanh thu sau đó đã ổn rồi các bạn ạ, tuy nhiên báo cáo vẫn chưa thực sự tốt khi cột số lượng vẫn xuất hiện trong ô chứa giá trị lỗi. 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 tất cả các lỗi trong excel Thay vì phải sử dụng công thức trong cột doanh số, với cách này chúng ta chỉ cần nhập công thức doanh số bán hàng = số lượng * đơn giá. Sau đó chúng ta sẽ đánh dấu những vùng dữ liệu cần ẩn lỗi rồi 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 hình: Bạn chọn màu chữ là Trắng
Và đây là kết quả chúng ta nhận được: Mọi ô trong kết quả là lỗi Giá trị sẽ không xuất hiện trên 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 DAYS. hàm số
Khi thực hiện hàm DAY, một lúc nào đó sẽ báo lỗi giá trị trong excel, một lúc nào đó sẽ cho kết quả. Lỗi excel này là do định dạng ngày tháng trong 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 trên hệ thống. Báo cáo lỗi giá trị trong Excel
Bước 1: Đầu tiên, bạn vào Control Panel rồi nhấn vào phần Clock, Language, and Region. Để 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: Xuất hiện bảng thay đổi định dạng như: ngày tháng trong máy tính. Trong mục Tùy chọn ngôn ngữ, tại mục 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 bên dưới màn hình, sau đó nhấp vào Thay đổi cài đặt ngày và giờ… trong khung thời gian. Để giao diện xuất hiện hộp thoại Date and time, click 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 còn lỗi như trước nữa.
2. Lỗi #VALUE! trong FIND / FINDB và SEARCH / SEARCHB. chức năng
Sự cố: không thể tìm thấy giá trị trong đối số text_search_string in_text 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 đã chỉ định, thì hàm sẽ ném ra một lỗi VALUE.
Ví dụ, một hàm như: = FIND (“găng tay”; “Găng tay (Thanh niên)”; 1)
Sẽ ném một lỗi #VALUE! lỗi, vì không có kết quả phù hợp cho “găng tay” trong chuỗi mà 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:
= 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 chính là đố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ố bị bỏ qua, giá trị mặc định sẽ được giả định là 1. có trong cú pháp và giá trị được đặt thành 0 thì bạn sẽ gặp lỗi VALUE trong Excel. Giải pháp: Loại bỏ đối số bắt đầu nếu không được yêu cầu hoặc đặt nó thành giá trị thích hợp chính xác.
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ề hàm Find: = 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ề giá trị lỗi #VALUE! vì chuỗi chỉ có 22 ký tự.
3. Lỗi #VALUE! trong hàm VLOOKUP. chức năng tra cứu
Sự cố: Đối số giá trị tra cứu có hơn 255 ký tự.
Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp các hàm INDEX và MATCH để thay thế. Sử dụng hàm INDEX và MATCH trong Excel để tra cứu các giá trị có hơn 255 ký tự. Đây là một công thức mảng. 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 được hiển thị khi bạn đối số 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 tìm kiếm 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 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 lồng 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 tìm kiếm, 2 là cột đầu tiên bên phải cột tìm kiếm … Vì vậy, nếu bạn đang tìm kiếm trong cột A, số 1 sẽ tham chiếu đến cột đó, 2 là cột B, 3 là cột C …
IV. Tổng kết
Với những mẹo này, bạn sẽ không học được ở trường lớp chính thống, nhưng nó sẽ rất hữu ích trong quá trình 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 này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.