Lỗi #N/A trong excel là gì? Nguyên nhân xuất hiện lỗi #N/A?

Excel lỗi #N/A – nguyên nhân và cách khắc phục hiệu quả

Khi thao tác với các hàm trên Excel, lỗi #N/A xuất hiện khiến cho cả người mới sử dụng lẫn người dùng excel chuyên nghiệp bị cản trở vì không ít người dùng chưa biết nguyên nhân vì đâu xuất hiện lỗi này trên bảng tính. Việc biết được do đâu gây nên lỗi này sẽ giúp bạn có cách khắc phục nhanh chóng để tránh thao tác với các hàm bị gián đoạn.

• Lỗi #N/A trong excel là gì và lỗi này có thể xuất hiện ở loại hàm nào?
• Nguyên nhân và cách khắc phục hiệu quả lỗi #N/A trong excel?

Cùng Mega tìm hiểu về vấn đề excel lỗi #N/A trong bài viết dưới đây.

 

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

 

I. Lỗi #N/A trong excel là gì? Nguyên nhân xuất hiện lỗi #N/A?

Trong tiếng Anh N/A được viết tắt từ cụm từ Not Available hay No Answer có nghĩa là không tồn tại, không có sẵn hay không thể áp dụng.

Trong Excel lỗi #N/A được hiểu là trong công thức của bạn có chứa nội dung không có sẵn trong vùng dữ liệu thuộc Excel, từ đó dẫn đến việc tính toán không thực hiện được và báo lỗi.

Một số hàm thường bị lỗi #N/A như VLOOKUP, HLOOKUP, IF, ...

Trước khi tìm hiểu cụ thể về cách khắc phục lỗi #N/A trong excel cùng điểm qua một số nguyên nhân chính gây nên lỗi này. Lỗi #N/A trong excel xuất hiện khi:

+ Các công thức được sử dụng 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.

+ Ô tham chiếu có khoảng trống hay bị thừa ô.

+ Sử dụng cách chọn tìm kiếm tương đương thay vì tìm chính xác trong hàm VLOOKUP.

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

 

 

II. Cách khắc phục hiệu quả lỗi #N/A trong excel?

1. Lỗi #N/A! do vùng tìm kiếm không đầy đủ

Được hiểu là do hàm tính không tìm thấy giá trị, trong vùng tìm kiếm bạn nhập vào không có dữ liệu để excel truy suất được.

Ví dụ: Trong bảng excel dưới, tìm mặt hàng Máy lạnh (đây là mặt hàng không có trong vùng tìm kiếm).

Thực hiện nhập cú pháp: =VLOOKUP("Máy lạnh",A1:D6,4,0)

 

Thực hiện cú pháp trên

 

Khi bạn nhấn phím Enter. Vì bảng dữ liệu không có dữ liệu mặt hàng “Máy lạnh” cần tìm nên Excel sẽ xuất hiện lỗi #N/A! như hình dưới.

 

Excel lỗi #N/A

 

Cách khắc phục:

Bạn cần kiểm tra dữ liệu tính toán của hàm một cách chính xác và chỉnh sửa lại. Ở đây nếu chỉnh lại mặt hàng cần tìm là Máy rửa chén (đây là dữ liệu có trong vùng dữ liệu) với công thức =VLOOKUP("Máy rửa chén",B1:D6,2,0), Excel sẽ trả kết quả chính xác như hình bên dưới.

 

Tìm kiếm dữ liệu có trong vùng tìm kiếm

 

 Excel trả kết quả chính xác

 

 

2. Lỗi #N/A! do không cố định vùng tìm kiếm khi sao chép công thức

Khi bạn không cố định vùng tìm kiếm thì công thức copy trong Excel sẽ tự động lấy dữ liệu từ ô đầu tiên xuống cho các ô còn lại, từ đó vùng tìm kiếm sẽ thay đổi theo.

Ví dụ với bảng excel trên, sử dụng công thức để tìm mặt hàng Máy rửa chén nhưng không cố định vùng dữ liệu. Khi mình copy xuống dưới thì Excel sẽ báo lỗi như hình.

 

Lỗi #N/A! do không cố định vùng tìm kiếm

 

Cách khắc phục:

Bạn cần cố định lại vùng dữ liệu trong công thức để khắc phục lỗi này.

+ Trong vùng dữ liệu chọn ở công thức tính. Bạn bôi đen vùng dữ liệu đó. Ở đây mình chọn bôi đen ô B1:D6 như hình.

 

Cố định vùng dữ liệu

 

+ Bạn nhấn phím F4. Lúc này Excel sẽ tự động cố định vùng dữ liệu được chọn, sau đó bạn có thể copy mà không sợ dữ liệu bị thay đổi.

 

3. Lỗi #N/A! khi sử dụng hàm VLOOKUP, HLOOKUP kết hợp với MID, LEFT, RIGHT

*Đối với hàm MID

Lỗi #N/A! xuất hiện khi định dạng không trùng khớp giữa kết quả hiển thị và dữ liệu tra cứu.

Ví dụ: Dùng hàm HLOOKUP để tìm điểm khu vực dựa và số báo danh của sinh viên.

Bước 1: Thực hiện nhập cú pháp =HLOOKUP(MID(A3,4,1),$C$9:$F$10,2,0)

Với:

MID(A3,4,1): Lấy 1 kí tự thứ 4 ở ô A3 (cụ thể là số 1).

$C$9:$F$10: Bảng chứa khu vực thi và điểm khu vực.

2: Lấy điểm khu vực.

0: Lấy giá trị chính xác tuyệt đối.

 

Lỗi #N/A! khi sử dụng hàm HLOOKUP kết hợp với MID

 

Bước 2: Nhấn phím Enter Excel sẽ báo lỗi như hình dưới.

 

Excel báo lỗi #N/A

 

Cách khắc phục:

Lỗi #N/A trong trường hợp này có 2 cách khắc phục:

• Một là, chuyển kết quả của hàm MID về dạng số bằng hàm VALUE
• Hai là, định dạng trùng khớp giữa dữ liệu tra cứu và kết quả hiển thị.

Ở đây, chúng ta sẽ sử dụng hàm VALUE với cú pháp =HLOOKUP(VALUE(MID(A3,4,1)),$C$9:$F$10,2,0).

Chuyển kết quả của hàm MID về dạng số bằng hàm VALUE

 

Tiếp đến bạn nhấn phím Enter, kết quả sẽ hiển thị như hình dưới.

 

Kết quả như hình trên

 

*Đối với hàm LEFT

Ví dụ: Điền tên hãng vào mã hàng tương ứng.

Bước 1: Nhập vào bảng tính Excel cú pháp =VLOOKUP(LEFT(B3,3),E8:F11,2,0) 

Với:

VLOOKUP: Là tên hàm tính toán dò tìm dữ liệu.

LEFT(B3,3): Là lệnh hàm tìm dữ liệu ô A3 với 3 ký tự.

E8:F11: Là vùng dữ liệu tìm kiếm.

 

Sử dụng kết hợp với hàm LEFT

 

Tiếp đến khi nhấn phím Enter, kết quả sẽ hiển thị lỗi như hình dưới.

 

Bảng tính hiện lỗi #N/A

 

Cách khắc phục:

Bạn cần kiểm tra và chỉnh lại giá trị trong hàm LEFT vì ô bảng tính chỉ chứa 2 ký tự nhưng ở đây lấy giá trị bằng 3 với cú pháp: =VLOOKUP(LEFT(B3,2),$E$8:$F$11,2,0). Excel sẽ trả kết quả đúng như hình dưới.

 

 

Kết quả trả về đúng

 

*Đối với hàm RIGHT

Nhập cú pháp =VLOOKUP(RIGHT(B3,2),$E$8:$F$11,2,0)

Với:

RIGHT(B3,2): Lấy 2 kí tự ở bên phải của ô A3 (cụ thể ở đây là SS).

E8:F11: Vị trí của Thông tin sản phẩm chứa mã hàng.

2: Vị trí lấy ở cột tên hãng.

0: Chọn kiểu giá trị chính xác tuyệt đối.

 

 

 

Sử dụng kết hợp với hàm RIGHT

 

Khi bạn nhấn Enter, Excel sẽ hiển thị lỗi #N/A

 

Nguyên nhân là vì trong điều kiện cần lấy ở bảng thông tin có chứa khoảng trắng nên dù giá trị cần lấy và hàm tính đã đúng thì Excel vẫn báo lỗi.

Cách khắc phục:

Bạn cần kiểm tra và xóa bỏ khoảng trắng trong Mã hàng ở bảng Thông tin sản phẩm. Lúc này Excel sẽ hiển thị kết quả đúng.

 

Kết quả trả về đúng

 

Lưu ý: Lỗi #N/A vẫn xảy ra khi có điều kiện tìm kiếm trong vùng điều kiện.

Đối với trường hợp này lỗi xuất hiện là do trong điều kiện tìm kiếm hoặc giá trị tương ứng trong vùng điều kiện tìm kiếm có thừa một hoặc một số dấu cách ở đằng sau.

Cách khắc phục:

+ Bạn chọn lần lượt từng giá trị. Giá trị dùng làm điều kiện và giá trị tương ứng trong vùng điều kiện.

+ Nhấn phím F2 nếu thấy dấu nháy chuột nằm cách xa ký tự cuối cùng một hoặc một vài dấu cách, bạn thực hiện xóa các dấu cách thừa đi. Thực hiện tương tự cho cả 2 giá trị.

 

III. Tổng kết

Lỗi #N/A trong Excel xảy ra sẽ làm gián đoạn khi người dùng thao tác với hàm trên bảng tính. Việc phát hiện nguyên nhân đúng sẽ giúp bạn giải quyết lỗi này một cách nhanh chóng và hạn chế nhất những cản trở liên quan đến lỗi excel. Hy vọng bài viết này sẽ giúp bạn khắc phục thành công lỗi #N//A trong Excel.

Đừng quên truy cập website mega.com.vn để cập nhật các bài thủ thuật mới nhất về lỗi và hàm phổ biến trên Microsoft Excel bạn nhé!

 

Xem thêm >>>

Nguyên nhân và cách khắc phục lỗi #DIV/0! trong Excel

Lỗi #ref trong excel là gì ? Cách khắc phục lỗi và ví dụ minh họa

Nguyên nhân lỗi #Name trong excel và cách khắc phục 

copyright © mega.com.vn