Cách sửa lỗi #N/A trong Excel hiệu quả

Trung tâm tin học tại Thanh Hóa

Trung tâm đào tạo kế toán thực tế- tin học văn phòng ATC liên tục mở các khóa Học tin học văn phòng tại Thanh Hóa,

giúp các bạn học viên có nhu cầu học tin học văn phòng ở Thanh Hóa có thêm nhiều cơ hội học tập, trau dồi thêm nhiều kỹ năng hữu dụng, áp dụng sát thực tế công việc.

ATC được đánh giá là trung tâm tin học văn phòng tại Thanh Hóa có số lượng học viên đông đảo.

và thường xuyên nhận cơn mưa lời khen của học viên sau mỗi khóa học.

Không chỉ chuyên đào tạo, ATC chúng tôi còn hướng đến giá trị chia sẻ kiến thức hữu ích.

Với mong muốn phổ cập tới động đảo mọi người những kiến thức hay ho của tin học văn phòng.

Cùng theo dõi trang của ATC để thường xuyên cập nhật các kiến thức hay các bạn nhé!

Trong bài viết hôm nay, ATC xin chia sẻ cùng các bạn về chủ để:

Cách sửa lỗi #N/A trong Excel hiệu quả, có ví dụ minh họa

Lỗi #N/A! là lỗi thường gặp trong Excel và nếu bạn chưa biết cách khắc phục. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về lỗi #N/A! cũng như cách sửa khi gặp phải lỗi này nhé.

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

N/A trong tiếng anh có thể là [Not Available] hoặc [No Answer] hoặc [Not Applicable]. Cả 3 từ trên đều có thể hiểu là không sẵn có hoặc không thể áp dụng.

Hay có thể hiểu lỗi #N/A nghĩa 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, dẫn đến việc tính toán, hoàn thành công thức sẽ không có kết quả như ý muốn.

1. Ví dụ về lỗi #N/A! trong Excel

#N/A! trong Excel là lỗi được trả về khi công thức không tìm thấy giá trị.

2. Một số hàm dễ mắc phải lỗi #N/A! trong Excel

Các hàm có thể dễ mắc lỗi #N/A! như:VLOOKUP, HLOOKUP, IF,…

II. Nguyên nhân gây lỗi #N/A! và cách khắc phục

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

Nguyênnhân:Do hàm tính không tìm thấy giá trị. Nghĩa là trong vùng tìm kiếm bạn nhập vào không có dữ liệu để truy suất.

Ví dụ:Tìm sản phẩm Xoài trong bảng dữ liệu sau:

Bước 1:Mình nhập hàm=VLOOKUP(“XOÀI”,A2:D6,4,0)vào bảng tính Excel.

Bước 2:Sau đó bạnnhấn phím Enter. Vì bảng dữ liệu không có tên cần tìm nên Excel sẽ xuất hiện lỗi #N/A! như hình.

Cách khắc phục:Kiểm tra dữ liệu tính toán của hàm và chỉnh sửa lại. Ở đây mình chỉnh lại sản phẩm cần tìm là Sầu Riêng với công thức=VLOOKUP(“Sầu Riêng”,B2:D6,2,0)nên Excel trả kết quả như hình.

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

Nguyên nhân:Khi 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 thì vùng tìm kiếm sẽ thay đổi theo.

Ví dụ:Như bảng minh họa trên mình sử dụng công thức để tìm sản phẩm Sầu Riêng 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.

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

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

Bước 2:Trên bàn phím bạnấn phím F4. Excel sẽ tự động cố định vùng dữ liệu được chọn như hình. 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

Trường hợp 1:Đối vớihàm MID.

Nguyên nhân:Thường gặp lỗi #N/A! 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:Trong bảng tính mình nhập hàm=HLOOKUP(MID(A3,4,1),$C$9:$F$10,2,0)như hình.

Trong đó:

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.

Bước 2:Minh nhấn phím Enter thì Excel báo lỗi như hình.

Cách khắc phục:Có 2 cách khắc phục lỗi do nguyên nhân này gây ra là:

Chuyển kết quả của hàm Mid về dạng số bằnghàm Value.
Định dạng trùng khớp giữa dữ liệu tra cứu và kết quả hiển thị.

Ở đây, mình sử dụng cách hàm Value để xử lý lỗi trên. Bằng cách thêm hàm Value vào như sau=HLOOKUP(VALUE(MID(A3,4,1)),$C$9:$F$10,2,0).

Sau đó bạn ấn phím Enter. Excel sẽ hiển thị kết quả như hình.

Trường hợp 2:Đối vớihàm LEFT.

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

Bước 1:Mình nhập vào bảng tính Excel công thức hàm Vlookup:=VLOOKUP(LEFT(B3,3),E8:F11,2,0)như hình.

Giải thích hàm:

VLOOKUP:Là tên hàm tính toán dò tìm dữ liệu.
LEFT(A3,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.

Bước 2:Bạnấn phím Enter.Excel sẽ hiển thị kết quả như hình.

Cách khắc phục:Để khắc phục lỗi này bạ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ự mà ở đây mình lấy giá trị bằng 3 như sau=VLOOKUP(LEFT(A3,2),HANGSANXUAT,2,0). Sau khi bạn sửa lại giá trị thì Excel hiển thị kết quả như hình.

Trường hợp 3:Đối vớihàm RIGHT.

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

Bước 1:Mình nhập vào bảng tính Excel công thức hàm Vlookup:=VLOOKUP(RIGHT(A3,2),D7:E11,2,0)như hình.

Trong đó:

RIGHT(A3,2):Lấy 2 kí tự ở bên phải của ô A3 (cụ thể ở đây là SS).
D7:E11:Vị trí của BẢNG THÔNG TIN chứa mã hàng sản xuất.
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.

Bước 2:Bạnnhấn phím Enter. Excel sẽ hiển thị kết quả như hình.

Ở đây mình đã kiểm tra giá trị cần lấy và hàm tính toàn là đúng vậy mà Excel vẫn báo 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.

Cách khắc phục:Ở đây mình kiểm tra và xóa bỏ khoảng trắng trong mã hàng SX ở bảng thông tin và Excel hiển thị kết quả như hình.

4. Tại sao 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

Nguyên nhân:Lỗi #N/A xuất hiện ở trường hợp này 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:

Cách đơn giản nhất để khắc phục lỗi nãy là 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 thì bạn xóa các dấu cách thừa đi. Làm như vậy cho cả 2 giá trị.