Lỗi #N/A trong Excel là gì, nguyên nhân và cách khắc phục

Lỗi #N/A trong Excel là gì, tìm hiểu lỗi #NAME trong Excel, nguyên nhân và cách khắc phục 2 lỗi này trong hàm VLOOKUP, hàm HLOOKUP, hàm SUM, hàm INDEX và hàm SUMIF. Đọc ngay bài viết sau để tìm hiểu thông tin chi tiết!

Nội Dung Chính

Lỗi #N/A trong Excel là gì?

N/A được viết tắt bởi cụm từ trong tiếng anh là not available có nghĩa là không tồn tại hay không có sẵn. Khi sử dụng Excel bạn thường bắt gặp lỗi #N/A mà chưa biết cách khắc phục như thế nào cho hiệu quả. Đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng cần tra cứu nên báo lỗi #N/A. Lỗi này thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu. Một số hàm thường bị lỗi #N/A như VLOOKUP, HLOOKUP, MATCH, IF,…

Lỗi #N/A trong Excel là gì?

Nguyên nhân lỗi #N/A trong Excel:

Công thức mảng tham chiếu một dải ô không có cùng số hàng hoặc cột như dải ô chứa công thức mảng.
Sử dụng phương pháp chọn tìm kiếm tương đương thay vì tìm chính xác trong hàm VLOOKUP.
Có khoảng trống thừa ở ô tham chiếu.
Khi sử dụng các công thức trong Excel không thể tìm thấy giá trị được tham chiếu. Những nội dung, giá trị không có sẵn trong vùng dữ liệu nên bạn không thể tính toán hay sử dụng được.

Nguyên nhân lỗi #N/A trong Excel hàm VLOOKUP

Nguyên nhân lỗi #N/A trong Excel hàm VLOOKUP như sau:

– Các khoảng trống lớn hoặc dấu đầu dòng. Đây là nguyên nhân rõ ràng nhất của lỗi VLOOKUP #N/A vì mắt người không thể nhìn thấy những không gian đặc biệt, nhấ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 nằm dưới thanh cuộn.

– Số được định dạng dưới dạng văn bản. Đ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ố. 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, trong cột dò tìm hoặc vùng tra cứu.

– Cột tra cứu không phải là cột bên trái của bảng dữ liệ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. 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 dò tìm.

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

– Lỗi đánh máy hoặc sắp xếp nhầm trong 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 hàng hoặc khi giá trị tra cứu được gõ trực tiếp vào công thức.

– 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 và không tìm thấy giá trị chính xác, cũng sẽ xuất hiện lỗi #N/A.

Cách khắc phục lỗi #N/A trong VLOOKUP

Cách khắc phục lỗi #N/A trong VLOOKUP như sau:

– Lỗi #N/A khi sử dụng hàm VLOOKUP kết hợp với MID: Chuyển kết quả của hàm MID về dạng số bằng hàm VALUE như sau: =VLOOKUP((VALUE(MID(A3,4,1)),$C$9:$F$10,2,0).

– Lỗi #N/A do không cố định vùng tìm kiếm khi copy công thức: Chỉnh sửa lại công thức bằng cách cố định vùng dữ liệu.

– Lỗi #N/A do vùng tìm kiếm không đầy đủ: Kiểm tra dữ liệu tính toán của hàm và sửa lại. Ta thay sản phẩm tìm kiếm là Sầu riêng với công thức =VLOOKUP(“Sầu riêng”,B2:D6,2,0).

Lỗi #N/A trong Excel hàm HLOOKUP và cách khắc phục

Nguyên nhân lỗi #N/A trong Excel hàm HLOOKUP và cách khắc phục như sau:

Lỗi xảy ra vì vấn đề ở vùng tìm kiếm: Xác định lại vùng tìm kiếm.

Lỗi #N/A do có khoảng trống thừa trong các ô: Cách khắc phục chính là bạn có thể sử dụng hàm TRIM để loại bỏ mọi khoảng trống ở đầu hoặc cuối.

Lỗi #N/A do có nhiều dữ liệu khác nhau: Nếu giá trị tra cứu và dữ liệu nguồn là các loại dữ liệu khác nhau thì sẽ gây nên lỗi #N/A. Cách khắc phục chính là bạn có thể kiểm tra định dạng ô bằng cách chọn một ô hoặc dải ô, rồi bấm chuột phải và chọn Format Cell > Number (hoặc nhấn Ctrl + 1) và thay đổi định dạng số, nếu cần. Hãy đảm bảo rằng các dữ liệu của bạn cùng loại với nhau.

Lỗi #N/A trong Excel hàm HLOOKUP và cách khắc phục

Lỗi #N/A do giá trị tham chiếu không tồn tại: Trong trường hợp này, không có giá trị “chuỗi” nào được liệt kê trong bảng tra cứu, do đó hàm VLOOKUP trả về lỗi #N/A. Nguyên nhân phổ biến nhất của lỗi #N/A là với các hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH khi một công thức không thể tìm thấy giá trị được tham chiếu. Cách khắc phục chính là hãy đảm bảo rằng giá trị tra cứu tồn tại trong dữ liệu nguồn hoặc sử dụng trình khắc phục lỗi như IFERROR trong công thức.

Lỗi #N/A do sự khó nhận diện chữ và con số của Excel: Giải pháp trong trường hợp này cần chuyển đổi kết quả của MID thành con số, nên chúng ta dùng hàm VALUE để sửa lỗi này như sau: =HLOOKUP(VALUE(MID(A2,2,1)),$B$7:$E$8,2,0). Định dạng kết quả của hàm MID (hay LEFT hoặc RIGHT) luôn là văn bản, nhưng khu vực tìm kiếm chỉ đưa kết quả là con số.

Lỗi #N/A do chưa cố định vùng tìm kiếm, hai là giới hạn vùng tìm kiếm chưa đủ rộng: Giải pháp đơn giản là giới hạn lại vùng tìm kiếm và cố định vùng tìm kiếm bằng ký tự “$”.

Nguyên nhân lỗi #N/A trong hàm SUM và cách khắc phục

Nguyên nhân lỗi #N/A trong hàm SUM và cách khắc phục như sau:

Sử dụng toán tử so sánh sai trong hàm SUM: Cách khắc phục chính là tính tổng các giá trị nếu một giá trị khớp chính xác trong phạm vi tiêu chí, bạn không cần sử dụng dấu “=”. Bạn chỉ cần viết giá trị đó hoặc cung cấp tham chiếu của giá trị đó tại vị trí của tiêu chí.

Sai định dạng ngày tháng trong công thức: Cách khắc phục là hãy kiểm tra và sửa định dạng ngày trước khi sử dụng. Đôi khi chúng ta nhập dữ liệu từ các nguồn khác, ngày tháng không được nhập ở các định dạng đúng. Vì vậy, nếu công thức của bạn bao gồm ngày tháng trong đó, hãy kiểm tra xem nó có đúng định dạng không. Bạn cần đặc biệt cẩn thận khi làm việc với ngày tháng trong Excel. Chúng rất dễ lộn xộn.

SUM không hoạt động do lỗi cú pháp: Xem lại cú pháp thực hiện của hàm SUM và chỉnh sửa lại sao cho đúng.

Nguyên nhân lỗi #N/A trong hàm SUM và cách khắc phục

Lỗi #N/A trong hàm SUMIF và cách khắc phục

Nếu hàm SUMIF trả về lỗi #N/A hoặc các lỗi khác tương tự, rất có thể công thức hàm SUMIF Excel mà bạn đang sử dụng chưa chính xác. Để đánh giá công thức, đầu tiên chọn công thức hàm Excel của bạn và click chọn tab Formula trên thanh Ribbon. Tại đây tìm và chọn tùy chọn Evaluate Formula, tùy chọn này nằm trong mục Auditing.

Lỗi #N/A trong hàm SUMIF không phải là lỗi sai hàm, sai công thức mà chỉ là không tìm thấy đối tượng cần tìm. Do đó cách khắc phục thường dùng là biện luận trước trường hợp không tìm thấy đối tượng cần tìm. Nếu không tìm thấy thì trả về rỗng chứ không thực hiện công thức.

Cách khắc phục lỗi #N/A trong hàm SUMIF như sau:

Kiểm tra xem nếu lookup_value không tồn tại thì sẽ thông báo ngay rằng Giá trị cần tìm không tồn tại thay vì sử dụng ngay công thức (ô F9 báo kết quả là Không tồn tại thay cho lỗi #N/A).
Nếu lookup_value không tồn tại (là 1 ô trống) thì trả về rỗng ngay, không cần tới công thức.

Lỗi #N/A trong hàm INDEX và cách khắc phục

Hàm INDEX nói một cách đơn giản là hàm lấy giá trị của ô trong Excel. Có hai dạng công thức của hàm INDEX dạng mảng và dạng tham chiếu.

Công thức chung của hàm INDEX: =INDEX(array, row_num,[column_num])

Trong đó:

Array: Một phạm vi ô hoặc một mảng.
Row_num: Một hàng thuộc phạm vi ô hoặc mảng đã khai báo ở array.
Column_num: Một cột thuộc phạm vi ô hoặc mảng đã khai báo ở array.

Nguyên nhân: Lỗi #N/A trong hàm INDEX xuất hiện do bạn đã nhập sai dải ô hoặc không tìm thấy giá trị.

Cách khắc phục: Khi bạn sử dụng một mảng trong hàm INDEX, bạn cần nhấn Ctrl + Shift + Enter trên bàn phím. Excel sẽ tự động đặt công thức vào trong dấu mũ cong {}. Nếu bạn cố gắng tự nhập dấu ngoặc vuông, bảng Excel hiển thị công thức dưới dạng văn bản.

Lưu ý: Nếu bạn có phiên bản hiện tại của Microsoft 365, thì bạn chỉ cần nhập công thức vào ô đầu ra, rồi nhấn Enter để xác nhận công thức là công thức mảng động. Nếu không, công thức phải được nhập dưới dạng công thức mảng thừa tự bằng cách chọn dải ô đầu ra trước, nhập công thức vào ô đầu ra, rồi nhấn Ctrl + Shift + Enter để xác nhận. Excel chèn dấu ngoặc nhọn ở đầu và cuối công thức giúp bạn.

Lỗi #NAME trong Excel là gì?

Trong Excel, bạn có thể gặp phải một số thông báo lỗi khi nhập các lệnh hàm sai, và lỗi #NAME là một trong những lỗi phổ biến mà hẳn là những người học Excel đã từng gặp phải ít nhất 1 lần. Nói đơn giản và dễ hiểu nhất thì lỗi #NAME xuất hiện khi bạn nhập công thức nhưng bị sai tên hàm, sai định danh khi mà Excel không truy soát được dữ liệu. Do vậy, tùy vào mỗi trường hợp bị sai ở đâu mà bạn sẽ có cách khắc phục khác nhau.

Lỗi #NAME trong Excel là gì?

Nguyên nhân và cách khắc phục các lỗi #NAME trong Excel thường gặp như sau:

Chưa bật hỗ trợ hàm: Một hàm không có sẵn thường được sử dụng có thể kể đến như =VND(Số tiền). Do đó, bạn cần bật hỗ trợ hàm trong Add-ins thì mới có thể sử dụng hàm được. Chẳng hạn hàm có thể được hỗ trợ bởi tính năng Add-ins bên ngoài workbook hoặc đó là hàm UDF tự viết (còn gọi là code) không có sẵn trong workbook bạn đang thao tác.

Thiếu dấu hai chấm: Excel sẽ không hiểu nếu bạn chọn vùng tham chiếu mà không có đánh dấu đầu và cuối bằng dấu “:”. (Ví dụ, thay vì công thức sai là =MIN(C16C22) thì bạn hãy thêm vào dấu “:” để đúng cú pháp =MIN(C16:C22))

Lỗi nhập sai công thức tham chiếu: Hãy điều chỉnh vị trí chứa dữ liệu phù hợp.

Nhập sai phạm vi dữ liệu cũng có thể báo lỗi #NAME: Nhập lại đúng phạm vi, hệ thống sẽ trả về đúng kết quả cần truy xuất.

Trên đây là toàn bộ thông tin lỗi #N/A trong Excel là gì, tìm hiểu lỗi #NAME trong Excel, nguyên nhân và cách khắc phục 2 lỗi này trong hàm VLOOKUP, hàm HLOOKUP, hàm SUM, hàm INDEX, hàm SUMIF. Hy vọng bài viết này hữu ích cho cuộc sống của bạn, cảm ơn bạn đã đọc bài viết này!

Xem thêm: Cách đếm ngày trong Excel và tính số ngày từ ngày đến ngày trong Excel

Office -

Lỗi #N/A trong Excel là gì, nguyên nhân và cách khắc phục