Tìm hiểu nguyên nhân và cách sửa lỗi #N/A trong Excel hiệu quả

Lỗi #N/A trong Excel có thể sửa bằng cách kiểm tra lại phạm vi dữ liệu, cố định vùng tra cứu với phím F4, hoặc sử dụng hàm IFERROR, ISNA để thay thế lỗi bằng giá trị khác. Bài viết này sẽ giúp bạn hiểu rõ nguyên nhân của lỗi #N/A và hướng dẫn các cách sửa lỗi hiệu quả để công thức hoạt động chính xác.

Nguyên nhân và cách sửa lỗi #N/A trong Excel

Lỗi #N/A trong Excel là thông báo cho biết giá trị hoặc dữ liệu mà công thức yêu cầu không tồn tại hoặc không thể tìm thấy. Tên viết tắt “N/A” có thể hiểu là Not Available, No Answer hoặc Not Applicable, nghĩa là “không có dữ liệu phù hợp” hoặc “không thể áp dụng”.

Lỗi này thường xảy ra khi bạn sử dụng các hàm tra cứu như VLOOKUP, HLOOKUP, MATCH hoặc INDEX nhưng giá trị cần tìm không xuất hiện trong vùng dữ liệu tham chiếu. Khi đó, Excel không thể trả về kết quả hợp lệ, nên hiển thị lỗi #N/A để báo rằng phép tính không có dữ liệu phù hợp để xử lý.

Lỗi #N/A trong Excel xuất hiện khi công thức không thể tìm thấy giá trị cần tra cứu trong phạm vi dữ liệu đã chỉ định.

Ví dụ về lỗi #N/A trong Excel khi công thức không thể tìm thấy giá trị tra cứu

Một số hàm trong Excel thường phát sinh lỗi #N/A là những hàm có chức năng tra cứu hoặc so sánh dữ liệu. Cụ thể gồm:

• VLOOKUP và HLOOKUP: Xảy ra khi giá trị cần tìm không xuất hiện trong bảng dữ liệu tham chiếu.
• MATCH: Báo lỗi #N/A nếu không tìm được vị trí của giá trị tra cứu.
• INDEX: Có thể trả về #N/A khi được kết hợp với hàm MATCH và đối số tra cứu bị lỗi.
• IF (hoặc IFERROR, IFNA): Không trực tiếp gây lỗi #N/A, nhưng thường được dùng để xử lý hoặc ẩn lỗi này khi xuất hiện trong công thức khác.

Nguyên nhân: Lỗi này xảy ra khi hàm tra cứu không thể tìm thấy giá trị trong phạm vi dữ liệu mà bạn chỉ định. Nói cách khác, vùng tìm kiếm được nhập không chứa giá trị cần truy xuất, khiến Excel không thể trả về kết quả hợp lệ.

Ví dụ: Giả sử bạn muốn tìm thông tin sản phẩm Măng cụt trong bảng dữ liệu từ ô A2:D6.

Bước 1: Nhập công thức: =VLOOKUP("Măng cụt",A2:D6,4,0) vào Excel.

Nhập công thức tìm thông tin sản phẩm “Măng cụt” vào Excel

Bước 2: Sau đó nhấn Enter, giá trị cần tìm (“Măng cụt”) không có trong cột tra cứu của vùng dữ liệu A2:D6, vì vậy Excel sẽ hiển thị lỗi #N/A để báo rằng không tìm thấy giá trị phù hợp.

Excel hiện lỗi #N/A vì không tìm thấy sản phẩm “Măng cụt”

Cách khắc phục: Hãy kiểm tra lại dữ liệu và chỉnh sửa tên sản phẩm cho đúng. Ví dụ, nếu sản phẩm thực tế là Nhãn, bạn nhập lại công thức: =VLOOKUP("Nhãn";B2:D6;2;0). Lúc này, Excel sẽ trả về kết quả chính xác tương ứng với dòng chứa “Nhãn” trong bảng dữ liệu.

Nhập lại đúng tên sản phẩm, kết quả hàm Excel trả về chính xác

Nguyên nhân: Khi công thức không cố định vùng dữ liệu, Excel sẽ tự động thay đổi vùng tra cứu cho các ô còn lại khi bạn sao chép công thức. Kết quả là Excel không thể tìm thấy giá trị cần tra cứu trong bảng dữ liệu, gây ra lỗi #N/A.

Ví dụ: Giả sử bạn sử dụng công thức để tìm sản phẩm “Măng cụt” trong bảng dữ liệu, nhưng không cố định vùng tra cứu. Khi sao chép công thức xuống các ô khác, Excel sẽ thay đổi vùng tra cứu, gây ra lỗi #N/A.

Lỗi #N/A do không cố định vùng tra cứu khi sao chép công thức

Cách khắc phục:

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

Bước 1: Chọn vùng dữ liệu trong công thức. Bạn bôi đen vùng dữ liệu mà bạn muốn cố định, ví dụ, ô B2:D6.

Bước 2: Nhấn phím F4 trên bàn phím. Excel sẽ tự động thêm dấu $ vào vùng dữ liệu, ví dụ: $B$2:$D$6. Đây là cách giúp cố định vùng dữ liệu và bạn có thể sao chép công thức mà không gặp phải vấn đề thay đổi vùng tra cứu.

Sau khi thực hiện, công thức sẽ không thay đổi khi bạn sao chép xuống các ô khác, giúp tránh lỗi #N/A.

Dùng phím F4 để cố định vùng tra cứu

Trường hợp 1: Lỗi khi sử dụng hàm MID

Nguyên nhân: Lỗi #N/A xuất hiện khi dữ liệu cần tra cứu không khớp về định dạng với kết quả trả về từ hàm MID.

Ví dụ: Giả sử bạn dùng hàm HLOOKUP để tìm điểm khu vực dựa trên số báo danh sinh viên. Bạn sử dụng công thức sau: =HLOOKUP(MID(A3;4;1);$C$9:$F$10;2;0)

• MID(A3; 4; 1): Lấy 1 ký tự thứ 4 từ ô A3.
• $C$9:$F$10: Vùng dữ liệu tìm kiếm.
• 2: Cột trả về điểm khu vực.
• 0: Lấy giá trị chính xác tuyệt đối.

Dùng hàm HLOOKUP để tìm điểm khu vực

Khi nhập công thức và nhấn Enter, Excel báo lỗi #N/A.

Nhấn Enter và kết quả trả về báo lỗi #N/A

Cách khắc phục: Kiểm tra lại định dạng dữ liệu trong bảng và trong ô tính. Để tránh lỗi này, bạn có thể sử dụng hàm VALUE để chuyển kết quả từ hàm MID sang dạng số.

Bước 1: Sửa lại công thức như sau: =HLOOKUP(VALUE(MID(A3;4;1));$C$9:$F$10;2;0)

Sửa lại thành công thức đúng

Bước 2: Sau khi thay đổi, Excel sẽ trả về kết quả chính xác.

Kết quả trả về chính xác sau khi đổi công thức

Trường hợp 2: Lỗi khi sử dụng hàm LEFT

Ví dụ: Giả sử bạn muốn điền tên hãng vào mã lô hàng tương ứng sử dụng hàm VLOOKUP kết hợp với LEFT: =VLOOKUP(LEFT(B3;3);E8:F11;2;0)

• LEFT(B3; 3): Lấy 3 ký tự đầu từ ô B3.
• E8:F11: Vùng dữ liệu cần tra cứu.

Sử dụng hàm VLOOKUP kết hợp hàm LEFT

Khi thực hiện, Excel sẽ báo lỗi #N/A.

Thực hiện xong công thức, Excel báo lỗi #N/A

Cách khắc phục: Lỗi xảy ra khi số ký tự bạn trích xuất từ hàm LEFT không khớp với dữ liệu trong bảng. Khi đó, bạn cần chỉnh sửa lại số ký tự trong công thức để khớp với dữ liệu trong bảng.

Bước 1: Nhập công thức: =VLOOKUP(LEFT(A3;2);C8:D11;2;0)

Khắc phục lỗi bằng cách chỉnh sửa và nhập lại công thức đúng

Bước 2: Nhấn Enter và kết quả hàm VLOOKUP trả về chính xác.

Nhấn Enter và kết quả trả về chính xác hãng TOSHIBA

Trường hợp 3: Lỗi khi sử dụng hàm RIGHT

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

Sử dụng hàm RIGHT kết hợp với hàm VLOOKUP: =VLOOKUP(RIGHT(A3;2); C8:D11;2;0)

• RIGHT(A3; 2): Lấy 2 ký tự ở phía bên phải của ô A3.
• C8:D11: Vùng chứa bảng thông tin.
• 2: Vị trí lấy ở cột tên hãng.
• 0: Lấy giá trị chính xác tuyệt đối.

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

Kết quả sẽ trả về lỗi #N/A mặc dù dữ liệu trong ô tính và bảng thông tin đúng.

Kết quả sẽ trả về lỗi #N/A mặc dù đã nhập công thức đúng

Nguyên nhân: Lỗi này có thể do bảng dữ liệu chứa các ký tự khoảng trắng không nhìn thấy trong mã hàng, gây lỗi trong việc so khớp dữ liệu.

Nguyên nhân chủ yếu do khoảng trắng không nhìn thấy trong mã hàng

Cách khắc phục: Kiểm tra và xóa khoảng trắng thừa trong bảng dữ liệu, đặc biệt là trong các mã hàng. Sau khi loại bỏ khoảng trắng, Excel sẽ hiển thị kết quả chính xác.

Xóa khoảng trắng thừa trong bảng dữ liệu, kết quả trả về chính xác

Nguyên nhân: Lỗi #N/A xảy ra khi có dấu cách thừa phía sau giá trị cần tìm kiếm hoặc giá trị tương ứng trong vùng điều kiện. Điều đó làm cho Excel không thể so khớp chính xác, vì trong khi các ký tự khác giống nhau, Excel coi dấu cách là một ký tự khác biệt.

Cách khắc phục:

• Chọn từng giá trị: Chọn lần lượt giá trị cần tìm và giá trị tương ứng trong vùng điều kiện tìm kiếm.
• Sử dụng phím F2: Khi bạn nhấn phím F2 để chỉnh sửa giá trị trong ô, bạn có thể nhìn thấy các dấu cách thừa nếu có. Nếu thấy có dấu cách phía sau chuỗi, hãy xóa nó đi.
• Lặp lại cho tất cả các giá trị: Làm tương tự cho cả hai giá trị, giá trị cần tìm và giá trị trong vùng dữ liệu tìm kiếm.

Lồng hàm ISNA vào công thức:

Bạn có thể kết hợp hàm ISNA với các hàm tìm kiếm như VLOOKUP, HLOOKUP, hoặc MATCH. Cụ thể, hàm ISNA sẽ kiểm tra xem kết quả có phải là lỗi #N/A hay không. Nếu có, bạn có thể thay thế lỗi đó bằng một giá trị khác mà bạn chỉ định.

Công thức minh họa: Giả sử bạn sử dụng hàm VLOOKUP và gặp phải lỗi #N/A, bạn có thể sửa lỗi bằng cách lồng hàm ISNA như sau:

=IF(ISNA(VLOOKUP(RIGHT(A3;3); C8:D11;2;0)); "Không tìm thấy"; VLOOKUP(RIGHT(A3;3); C8:D11;2;0))

• ISNA(VLOOKUP(RIGHT(A3;3); C8:D11;2;0) kiểm tra nếu kết quả của VLOOKUP là lỗi #N/A.
• Nếu có lỗi, hàm IF sẽ trả về chuỗi "Không tìm thấy" thay vì #N/A.
• Nếu không có lỗi, công thức sẽ trả về kết quả của VLOOKUP.

Hàm ISNA sẽ kiểm tra xem kết quả có phải là lỗi #N/A hay không

Để ẩn lỗi #N/A trong Excel, bạn có thể sử dụng hàm IFERROR. Hàm này giúp xử lý các lỗi trong công thức, trả về giá trị thay thế khi gặp lỗi.

Công thức tổng quát của IFERROR là:

=IFERROR(value, value_if_error)

Trong đó:

• value: Là giá trị hoặc công thức mà bạn muốn kiểm tra lỗi.
• value_if_error: Là giá trị hoặc thông báo thay thế sẽ được trả về nếu value gặp phải lỗi.

Cách sử dụng hàm IFERROR kết hợp với VLOOKUP

Ví dụ, khi bạn sử dụng hàm VLOOKUP để tra cứu dữ liệu và có thể gặp lỗi #N/A nếu không tìm thấy kết quả, bạn có thể áp dụng IFERROR để thay thế lỗi này bằng giá trị khác như "Không tìm thấy" hoặc giá trị rỗng.

Công thức ví dụ:

=IFERROR(VLOOKUP(RIGHT(A3; 2); C8:D11; 2; FALSE); "Không tìm thấy")

Giải thích:

• Hàm VLOOKUP sẽ tìm kiếm giá trị trong ô A3 trong phạm vi từ C8 đến D11.
• Nếu không tìm thấy kết quả, lỗi #N/A sẽ được trả về. Khi đó, hàm IFERROR sẽ thay thế lỗi này bằng thông báo "Không tìm thấy".

Hàm IFERROR giúp xử lý các lỗi trong công thức, trả về giá trị thay thế khi gặp lỗi

Lỗi #N/A trong Excel có thể khiến công việc của bạn bị gián đoạn, nhưng với những giải pháp đơn giản như kiểm tra vùng dữ liệu, cố định vùng tham chiếu, hay sử dụng các hàm hỗ trợ như ISNA hoặc IFERROR, bạn hoàn toàn có thể khắc phục hiệu quả. Hãy áp dụng những cách sửa lỗi mà bài viết đã chia sẻ để công thức Excel luôn hoạt động chính xác và hiệu quả.

Đừng quên theo dõi chuyên mục Kinh nghiệm của Siêu Thị Điện Máy - Nội Thất Chợ Lớn để biết thêm nhiều thủ thuật Excel hữu ích.

Để tiện theo dõi và chỉnh sửa tài liệu mọi lúc, bạn có thể cân nhắc sử dụng điện thoại hoặc máy tính bảng. Tham khảo ngay các mẫu thiết bị chính hãng tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn.