Lỗi

VALUE là một lỗi thường gặp trong Excel nhưng nhiều bạn không biết cách xử lý. Trong bài viết này, Gitiho sẽ giúp bạn chỉ ra nguyên nhân và hướng dẫn cách sửa lỗi

VALUE nhanh chóng và hiệu quả. Hãy cùng theo dõi bài viết nhé!

XEM NHANH BÀI VIẾT

Lỗi Value trong Excel là gì?

Lỗi

VALUE! là cách để Excel thông báo: “Đã xảy ra lỗi với công thức bạn vừa nhập” hoặc “Đã xảy ra lỗi với ô bạn đang tham chiếu tới.” Lỗi này báo rất chung chung và khó có thể tìm thấy nguyên nhân chính xác cho lỗi.

Chúng mình sẽ giải thích một số nguyên nhân phổ biến, cùng giải pháp khắc phục cho lỗi

VALUE ngay dưới đây.

3 cách sửa lỗi

VALUE trong Excel hiệu quả

Sửa lỗi

VALUE với các khoảng trắng trong ô trống Excel

Trên thực tế chúng ta không thể thấy được các khoảng trắng. Nhiều khi công thức Excel của bạn liên quan tới các ô có chứa khoảng trắng bị ẩn mà bạn không phát hiện được, bạn nghĩ đó là ô trống nên sẽ gặp lỗi

VALUE Excel.

Để khắc phục sự cố này các bạn chỉ cần loại bỏ các khoảng trắng ẩn bằng 2 cách như sau:

Cách 1: Xử lý các ô trống có chứa dấu cách

Bước 1: Tìm kiếm các khoảng trống

Chọn các ô được tham chiếu, sau đó nhấn tổ hợp Ctrl + H để mở hộp thoại thay thế.

Bước 2: Loại bỏ các khoảng trống ẩn

Trong hộp thoại Find and Replace bạn nhập dấu cách trong ô Find what và trong Replace with bạn không nhập gì. Sau đó nhấn Replace All để loại bỏ tất cả các khoảng trắng ẩn.

Xem thêm: Cách khắc phục lỗi excel the name already exists nhanh chóng

Cách 2: Sử dụng bộ lọc

Ngoài ra , bạn có thể có loại bỏ các ký tự ẩn, các khoảng trắng mà bạn không phát hiện bằng cách sau:

Bước 1: Mở hộp thoại lọc các ký tự ẩn

Trên vùng dữ liệu mà bạn đã chọn các bạn chọn Data => Filter để mở bộ lọc.

Bước 2: Tiến hành lọc

Chọn biểu tượng lọc trên tiêu đề cột và bỏ chọn trong ô Select All, sau đó đánh dấu trong hộp kiểm Blanks hoặc những hộp kiểm không có nội dung gì.

Bước 3: Xuất hiện kết quả lọc

Các bạn chọn các ô trống và nhấn phím Delete. Như vậy các bạn đã xóa mọi ký tự ẩn trong các ô.

Bước 4: Xóa bộ lọc

Xóa bộ lọc bằng cách nhấn chọn biểu tượng lọc trên ô tiêu đề và chọn Clear Filter From …

Như vậy các bạn sẽ được kết quả như sau:

Xem thêm: Làm sao để sửa lỗi Excel ẩn dòng không thể Unhide.

Sửa lỗi

VALUE! khi có văn bản hay ký tự đặc biệt trong Excel

Nếu danh sách dữ liệu rất dài thật khó để biết ô nào chứa văn bản hay ký tự đặc biệt. Nếu có ô chứa văn bản hay các ký tự đặc biệt sẽ tạo ra lỗi

VALUE!

Để sửa lỗi

VALUE chúng ta cần loại bỏ các kí tự đặc biệt theo các bước sau:

Bạn sử dụng hàm ISTEXT để tìm kiếm ô có chứa văn bản hay ký tự đặc biệt để có thể sửa lỗi

VALUE!

Bước 1: Tìm kiếm văn bản hoặc kí tự đặc biệt

Tạo 1 cột cạnh cột dữ liệu các bạn cần kiểm tra, trong ô đầu tiên của cột nhập công thức hàm =ISTEXT(C6)

Bước 2: Sao chép công thức hàm ISTEXT() xuống tất cả các ô còn lại, hàm sẽ trả về giá trị TRUE nếu ô có chứa văn bản hay ký tự đặc biệt, trả về FALSE nếu trong ô không chứa văn bản hay ký tự đặc biệt.

Bước 3: Tiếp theo bạn chọn đến những ô TRUE và xóa văn bản hay các ký tự đặc biệt đó đi thì hàm sẽ không còn lỗi

VALUE!

Cuối cùng, các bạn cũng cần xóa cột mà bạn tạo ở bước 1, và kết quả sẽ không còn báo lỗi

VALUE!

Xem thêm: Lỗi Excel chỉ hiện công thức không hiện kết quả và cách khắc phục

Kết luận

Bài viết trên đây là những hướng dẫn chi tiết cách sửa lỗi

VALUE trong Excel. Hy vọng bài viết này đã giúp tìm ra cách khắc phục khi tính toán trong trang tính Excel báo lỗi

VALUE.

Trong bài viết tới, chúng tôi sẽ hướng dẫn bạn sửa lỗi

REF trong Excel, một lỗi tham chiếu phổ biến mà người dùng thường gặp phải. Mong bạn sẽ chú ý đón xem!

Bài viết dưới đây, Học Excel Online sẽ hướng dẫn các bạn xử lý sự cố hàm Vlookup không hoạt động trong Excel 2016, 2013, 2010, 2007 và 2003. Ngoài ra, Học Excel Online sẽ hướng dẫn cách khắc phục các lỗi thường gặp hàm Vlookup như

N/A,

NAME VÀ

VALUE và các hạn chếcủa Vlookup.

Trong các bài viết trước chúng ta đã tìm hiểu những khía cạnh khác nhau của hàm Vlookup trong Excel. Tuy nhiên hàm Vlookup vẫn còn rất nhiều sự phức tạp và hạn chế nên có rất nhiều vấn đề về lỗi khác nhau.

Trong bài viết này sẽ giải thích đơn giản về các thông báo lỗi

N/A,

NAME VÀ

VALUE của VLOOKUP cũng như các giải pháp để xử lý.

Xem nhanh

Sửa lỗi VLOOKUP

N/A trong Excel

Trong công thức hàm Vlookup, thông báo lỗi

N/A (nghĩa là Not Available – “không có sẵn”) được hiển thị khi Excel không thể tìm thấy giá trị tra cứu. Có thể xuất phát bởi một số lý do sau:

1. Lỗi đánh máy hoặc sắp xếp sai giá trị tra cứu

Lỗi xếp nhầm chữ thường xảy ra khi bạn làm việc với lượng dữ liệu lớn bao gồm hàng ngàn dòng hoặc khi giá trị tra cứu được gõ trực tiếp vào công thức.

2. Lỗi # N/A trong tìm kiếm gần đúng bằng hàm VLOOKUP

Nếu bạn đang sử dụng một công thức với đối sánh gần đúng (đối số range_lookup là TRUE hoặc bỏ qua), công thức Vlookup của bạn có thể trả về lỗi # N/A trong hai trường hợp:

• Nếu giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu.
• Nếu cột tra cứu không được sắp xếp theo thứ tự tăng dần.

3. Lỗi # N/A trong tìm kiếm chính xác hàm VLOOKUP

Nếu bạn đang tìm kiếm với đối sánh chính xác (đối số range_lookup được đặt thành FALSE) và không tìm thấy giá trị chính xác, thì lỗi

N/A cũng được trả về.

4. Cột tra cứu không phải là cột bên trái của bảng dữ liệu

Có thể bạn đã biết, một trong những hạn chế đáng kể nhất của hàm VLOOKUP là nó không thể dò tìm phía bên trái cột tra cứu, do đó cột tra cứu luôn luôn là cột bên trái ngoài cùng trong bảng tra cứu. Trong thực tế, chúng ta thường quên đi điều này và dẫn đến việc hàm VLOOKUP không hoạt động vì lỗi N/A.

Sửa lỗi VLOOKUP

N/A trong Excel

Giải pháp: Nếu không thể tái cấu trúc dữ liệu để cột tra cứu là cột bên trái, bạn có thể sử dụng kết hợp các hàm INDEX và MATCH để thay thế linh hoạt cho hàm VLOOKUP. Bạn sẽ tìm thấy thông tin chi tiết và ví dụ về công thức trong hướng dẫn – Cách dùng hàm INDEX / MATCH để tra cứu các giá trị bên trái.

5. Số được định dạng văn bản

Một lỗi N/A khác của hàm VLOOKUP là do các con số được định dạng dưới dạng văn bản, bảng chính hoặc bảng tra cứu.

Điều này thường xảy ra khi bạn nhập dữ liệu từ một cơ sở dữ liệu bên ngoài hoặc do bạn đã gõ dấu nháy đơn trước số.

Số được định dạng văn bản

Các số cũng có thể được lưu trữ ở định dạng Chung. Trong trường hợp này, có một dấu hiệu đáng chú ý – các số được căn chỉnh ở phía bên trái của ô.

Giải pháp: Nếu đây chỉ là một số duy nhất, chỉ cần nhấp vào biểu tượng lỗi và chọn ” Convert to number ” từ danh sách tùy chọn.

Số được định dạng văn bản 1

Nếu nhiều số bị ảnh hưởng, hãy chọn tất cả, nhấp chuột phải vào vùng chọn, sau đó chọn Format Cells>Number tab>Number và nhấp OK.

6. Các khoảng trống lớn hoặc dấu đầu dòng

Đây là nguyên nhân khó phát hiện nhất vì mắt thường khó có thể phát hiện ra những khoảng trống thừa đó, đặc biệt là khi làm việc với các bảng lớn nơi hầu hết các mục nhập nằm dưới cuộn.

Trường hợp 1: Khoảng trắng trong cột dò tìm (với công thức VLOOKUP)

Nếu các khoảng trống dư thừa xuất hiện trong bảng chính của bạn, bạn có thể sử dụng công thức Vlookup bằng cách lồng đối số lookup_value với hàm TRIM:

\=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

Lỗi khoảng trắng trong cột dò tìm

Trường hợp 2: Khoảng trắng trong cột tra cứu

Nếu các khoảng trắng thừa xuất hiện trong cột tra cứu, không có cách nào để tránh lỗi VLOOKUP

N/A. Thay vì hàm VLOOKUP, bạn có thể sử dụng công thức mảng với sự kết hợp của các hàm INDEX / MATCH và TRIM:

\=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))

Vì đây là một công thức mảng, đừng quên nhấn Ctrl + Shift + Enter:

Lỗi khoảng trắng trong cột tra cứu

Lỗi

VALUE trong công thức VLOOKUP

Microsoft Excel hiển thị lỗi

VALUE! lỗi nếu một giá trị được trong công thức không đúng. Đối với hàm VLOOKUP, lỗi

VALUE thường mắc phải do 2 lý do sau đây.

• Giá trị tra cứu vượt quá 255 ký tự

  Nên nhớ VLOOKUP không thể tra cứu các giá trị chứa 256 ký tự trở lên. Nếu các giá trị tra cứu của bạn vượt quá giới hạn này thì sẽ xuất hiện lỗi VALUE:

  Lỗi

  VALUE trong công thức VLOOKUP

  Giải pháp : Sử dụng công thức INDEX / MATCH tương tự để thay thế. Trong ví dụ trên, hàm INDEX / MATCH sau đây hoạt động hoàn hảo:

  \=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))

  Giải pháp lỗi

  VALUE trong công thức VLOOKUP

  • Không cung cấp đường dẫn đầy đủ đến bảng tính tra cứu

    Nếu bạn lấy dữ liệu từ một bảng tính khác, bạn phải bao gồm đường dẫn đầy đủ đến tệp đó. Chính xác hơn, bạn phải kèm theo tên của tệp (bao gồm cả phần mở rộng) trong dấu ngoặc vuông [], và sau đó chỉ định tên của trang tính và dấu chấm than. Ngoài ra, bạn cũng cần phải đặt chúng trong dấu nháy đơn trong trường hợp tệp hoặc tên bảng tính có chứa khoảng trống.

    Đây là cấu trúc của công thức hoàn chỉnh để thực hiện vlookup từ một sổ làm việc khác:

    \=VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE)

    Một công thức thực sự có thể trông như sau:

    \=VLOOKUP($A$2,'[New Prices.xls]Sheet1′!$B:$D,3,FALSE)

    Công thức trên sẽ tìm kiếm giá trị ô A2 trong cột B của Sheet1 tệp “New Prices” và trả về một giá trị phù hợp tại cột D.

    Nếu bất kỳ phần tử nào của đường dẫn bị thiếu, công thức VLOOKUP của bạn sẽ không hoạt động và trả lại lỗi

    VALUE (trừ khi bảng tính tra cứu hiện đang mở).

    • Đối số col_index_num nhỏ hơn 1

      Khó có trường hợp mà nhập một số nhỏ hơn “1” để xác định cột giá trị trả về. Tuy nhiên, nó có thể xảy ra nếu đối số này được trả về bởi một số hàm Excel khác được lồng trong công thức Vlookup của bạn.

      Vì vậy, nếu đối số col_index_num nhỏ hơn 1, công thức Vlookup của bạn sẽ trả về lỗi

      VALUE!.

      Nếu đối số col_index_num lớn hơn số cột trong mảng bảng được chỉ định, công thức Vlookup trả về lỗi

      REF! lỗi.

      Lỗi VLOOKUP

      NAME

      Đây là trường hợp dễ nhất – lỗi NAME xuất hiện nếu bạn vô tình viết sai chính tả tên của hàm.

      Giải pháp là rõ ràng – hãy kiểm tra chính tả:)

      VLOOKUP không hoạt động (hạn chế, sự cố và giải pháp)

      Ngoài việc có một cú pháp khá phức tạp, hàm VLOOKUP có nhiều hạn chế đáng kể so với bất kỳ hàm Excel khác. Do những hạn chế này, công thức Vlookup thường mang lại kết quả không như mong đợi. Dưới đây bạn sẽ tìm thấy các giải pháp cho một vài tình huống phổ biến khi VLOOKUP không thành công.

      • Vlookup không phân biệt chữ hoa chữ thường

        Hàm VLOOKUP không phân biệt chữ hoa và chữ thường. Vì vậy, nếu bảng của bạn có nhiều mục tương tự chỉ khác nhau chữ hoa, chữ thường, công thức Vlookup sẽ trả về giá trị được tìm thấy đầu tiên bất kể trường hợp nào.

        Giải pháp : Sử dụng một hàm Excel khác có thể thực hiện tra cứu theo chiều dọc (LOOKUP, SUMPRODUCT, INDEX / MATCH) kết hợp với hàm EXACT để có thể hỗ trợ bạn tra cứu. Bạn có thể tìm thấy các giải thích chi tiết và ví dụ về công thức trong hướng dẫn này – 4 cách để tạo vlookup phân biệt chữ hoa chữ thường trong Excel .

        • VLOOKUP trả về giá trị tìm thấy đầu tiên

          Như bạn đã biết, hàm VLOOKUP trả về giá trị đầu tiên nó tìm thấy trong cột trả về phù hợp với giá trị tra cứu. Tuy nhiên, bạn có thể buộc nó trả về cái thứ 2, 3, 4 hoặc bất kỳ trường hợp nào mà bạn muốn. Nếu bạn cần phải nhận được tất cả các giá trị, bạn sẽ phải sử dụng kết hợp các hàm INDEX, SMALL và ROW.

          Giải pháp: Các ví dụ công thức có sẵn để tải về tại đây:

          • Nhận biết các lần xuất hiện thứ 2, 3, 4, v.v …
          • Nhận tất cả các lần xuất hiện trùng lặp của giá trị tra cứu.

          • Một cột mới được chèn vào hoặc gỡ bỏ khỏi hàng

            Các công thức VLOOKUP sẽ không hoạt động khi một cột mới bị xóa khỏi hoặc được thêm vào bảng tra cứu. Điều này xảy ra do cú pháp của hàm VLOOKUP yêu cầu bạn cung cấp toàn bộ bảng cũng như một số nhất định cho biết bạn muốn trả về dữ liệu từ cột nào. Đương nhiên, cả bảng và số của cột trả về đều thay đổi khi bạn xóa một cột hiện có hoặc chèn một cột mới.

            Giải pháp: INDEX / MATCH được dùng để giải quyết vấn đề này. Trong hàm INDEX & MATCH, bạn chỉ định các cột tra cứu và cột trả kết quả riêng biệt, kết quả là bạn có thể xóa hoặc chèn nhiều cột tùy ý mà không cần phải cập nhật mọi công thức vlookup liên quan .

            • Các ô tham chiếu thay đổi khi sao chép công thức đến ô khác

              Giải pháp: Luôn sử dụng tham chiếu ô tuyệt đối (với dấu $) trong vùng chọn, ví dụ: $A$2: $C$100 hoặc $A:$C. Trong thanh công thức, bạn có thể nhanh chóng chuyển đổi giữa các loại tham chiếu khác nhau bằng cách nhấn F4.

              Hàm VLOOKUP với hàm IFERROR / ISERROR

              Nếu bạn không muốn để người dùng thấy tất cả các thông báo lỗi N/A, VALUE hoặc NAME, bạn có thể trả lại ô trống hoặc hiển thị nội dung của riêng bạn. Bạn có thể làm điều này bằng cách lồng công thức VLOOKUP của bạn trong hàm IFERROR trong Excel 2013, 2010 và 2007 hoặc hàm IF / ISERROR trong các phiên bản Excel trước đó.

              Sử dụng VLOOKUP với IFERROR

              Cú pháp của hàm IFERROR rất đơn giản:

              IFERROR (giá trị, giá trị_if_error)

              Bạn tiến hành nhập giá trị để kiểm tra lỗi trong đối số thứ nhất, và trong đối số thứ 2 bạn chỉ định giá trị trả về nếu lỗi xảy ra.

              Ví dụ, công thức IFERROR / VLOOKUP trả về một ô trống khi không tìm thấy giá trị tra cứu:

              \=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),””)

              Nếu bạn thích hiển thị nội dung mà mình muốn thay vì những biểu hiện lỗi thông thường, hãy đánh chúng vào dấu ngoặc kép như thế này:

              \=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),”Oops, no match is found. Please try again!”)

              Sử dụng hàm VLOOKUP với ISERROR

              Hàm IFERROR có ở phiên bản Excel 2007, trong các phiên bản Excel trước bạn sẽ phải sử dụng kết hợp hàm IF và ISERROR như thế này:

              \=IF(ISERROR(VLOOKUP formula), “Your message if any”, VLOOKUP formula)

              Ví dụ: đây là công thức IF / ISERROR / VLOOKUP tương tự với công thức IFERROR / VLOOKUP ở trên:

              \=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)), “”, VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

              Trên đây là những lỗi hay gặp trong hàm Vlookup. Học Excel Online hy vọng thông qua những hướng dẫn trên bạn có thể xử lý tất cả các lỗi có thể xảy ra của hàm Vlookup.