Lỗi #N/A trong Excel là gì? Cách khắc phục, sửa lỗi cực dễ

Lỗi N/A trong Excel là một trong những lỗi phổ biến khiến công thức trả về kết quả sai hoặc không hiển thị dữ liệu như mong muốn. Lỗi này thường phát sinh khi sử dụng các hàm tra cứu như VLOOKUP, HLOOKUP, MATCH hoặc khi thực hiện phép tính trên vùng dữ liệu đã có ô báo lỗi.

Lỗi #N/A trong Excel là gì? Các hàm thường gặp lỗi #N/A

Lỗi N/A trong Excel là viết tắt của “Not Available”, nghĩa là không tìm thấy giá trị phù hợp theo yêu cầu của công thức. Khi xuất hiện lỗi này, Excel đang thông báo rằng dữ liệu cần tìm không tồn tại hoặc công thức không thể đối chiếu chính xác.

lỗi N/A trong Excel thường xuất hiện trong các hàm tra cứu và tham chiếu dữ liệu như:

• Lỗi #N/A trong hàm VLOOKUP: Xảy ra khi giá trị cần dò tìm không xuất hiện ở cột đầu tiên của vùng dữ liệu được chọn để tra cứu.
• Lỗi NA trong Excel hàm Vlookup: Xảy ra do không cố định vùng dữ liệu khiến bảng dò tìm bị dịch chuyển khi kéo công thức.
• Lỗi #N/A trong Excel hàm HLOOKUP: Tương tự như Vlookup nhưng xảy ra theo hàng ngang khi hàm không tìm thấy giá trị ở hàng đầu tiên của vùng tham chiếu.
• Lỗi #N/A trong hàm MATCH: Hàm không tìm thấy vị trí chính xác của giá trị cần tìm trong mảng đơn dòng hoặc đơn cột.
• Lỗi n a trong hàm Sum: Hàm SUM vốn không tự sinh ra lỗi N/A trong Excel, nhưng nếu trong vùng ô bạn muốn tính tổng chứa dù chỉ một ô bị lỗi #N/A trước đó, toàn bộ kết quả của hàm SUM cũng sẽ bị kéo theo thành #N/A.

Nguyên nhân gây lỗi #N/A trong các hàm

Dưới đây là những lý do phổ biến khiến Excel hiển thị lỗi #N/A, đặc biệt khi làm việc với bảng dữ liệu lớn.

• Quên cố định vùng dữ liệu (F4): Khi kéo công thức, vùng tham chiếu bị trượt theo, khiến giá trị cần tìm nằm ngoài vùng tìm kiếm.
• Khoảng trắng thừa: Excel phân biệt "Mã A" và "Mã A " — chỉ một dấu cách thừa đầu/cuối cũng gây lỗi.
• Xung đột kiểu dữ liệu: Giá trị tìm kiếm ở dạng Text nhưng bảng tra cứu lưu dạng Number (hoặc ngược lại).
• Dùng sai hàm cắt chuỗi (LEFT, RIGHT, MID): Kết quả các hàm này luôn là Text. Nếu dò vào bảng chứa dữ liệu Số, hàm sẽ báo lỗi.
• Sai vị trí cột/hàng tìm kiếm:
  • VLOOKUP: Giá trị tìm kiếm phải nằm ở cột đầu tiên bên trái vùng chọn.
  • HLOOKUP: Giá trị tìm kiếm phải nằm ở hàng đầu tiên trên cùng vùng chọn.
• Sai tham số kiểu tìm kiếm:
  • VLOOKUP/HLOOKUP: Để FALSE nhưng dữ liệu thực tế không khớp 100%.
  • MATCH: Đặt match_type 1 hoặc -1 nhưng mảng chưa được sắp xếp tăng/giảm dần.
• Giá trị không tồn tại trong bảng nguồn: Dữ liệu cần tra cứu thực sự không có trong bảng.
• Hàm tính toán tham chiếu ô lỗi: SUM, AVERAGE... không tự sinh ra lỗi N/A trong Excel, nhưng nếu vùng tính chứa ít nhất một ô lỗi #N/A, kết quả cả công thức sẽ bị lỗi theo.

[Product_Listing categoryid="1217" propertyid="" customlink="https://cellphones.com.vn/laptop/van-phong.html" title="Tham khảo danh sách laptop văn phòng được quan tâm tại CellphoneS!"]

Cách sửa lỗi #N/A trong Excel theo từng trường hợp

Tùy vào nguyên nhân, bạn có thể áp dụng các cách xử lý phù hợp để công thức hoạt động chính xác hơn. Cách sửa lỗi N/A trong Excel được dùng nhiều nhất gồm:

Dùng IFERROR để xử lý lỗi #N/A trong Vlookup và HLOOKUP

Hàm IFERROR giúp bạn chặn trước mọi loại lỗi và thay thế mã lỗi xấu xí bằng một văn bản trống hoặc một thông báo dễ hiểu.

• Công thức gốc: =Vlookup(B13,A1:C11,3,0)
• Công thức sửa lỗi: =IFERROR(Vlookup(B13,A1:C11,3,0),"Data Not Found")
• Giải thích: Nếu hàm Vlookup chạy mượt mà, Excel trả về kết quả đúng. Nếu xảy ra lỗi #N/A, Excel sẽ tự động hiển thị chữ "Không tìm thấy" thay vì hiện mã lỗi.

Dùng hàm ISNA kết hợp IF để kiểm tra và thay thế lỗi

Nếu bạn chỉ muốn xử lý riêng lỗi N/A trong Excel mà không muốn làm ảnh hưởng đến các lỗi khác (như #REF!, #NAME!), kết hợp IF và ISNA là lựa chọn hoàn hảo. Hàm ISNA sẽ trả về TRUE nếu ô đó bị lỗi #N/A.

• Công thức: =IF(ISNA(MATCH(B2;$F$14:$F$18;0));"Không tìm thấy";"")
• Cách hoạt động: Nếu tên sản phẩm ở ô B2 không tồn tại trong danh sách F14:F18 → hiện dòng “Không tìm thấy”. Nếu có tồn tại → ô sẽ trống.

Cách sửa lỗi #N/A trong hàm SUM khi tham chiếu vùng chứa lỗi

Để tính tổng trong trường hợp có chứa ô lỗi, bạn cần thay thế SUM bằng các hàm như AGGREGATE hoặc SUMIF nhằm bỏ qua giá trị lỗi N/A trong Excel, đảm bảo kết quả tính toán chính xác.

Cách 1: Dùng AGGREGATE

• Công thức: =AGGREGATE(9, 6, C2:C10) (Trong đó: 9 đại diện cho hàm SUM, 6 là tham số ra lệnh bỏ qua các giá trị lỗi trong vùng dữ liệu B2:B10).

Cách 2: Dùng SUMIF

• Công thức: =SUMIF(A1:A14, "<>#N/A") (Công thức này chỉ cộng những ô có giá trị khác với mã lỗi #N/A).

Cách ẩn hoặc chuyển lỗi #N/A về 0 trong bảng tính

Để thực hiện việc ẩn lỗi n a trong Excel nhằm mục đích in ấn đẹp mắt hoặc giữ nguyên định dạng số để tính toán tiếp, bạn chỉ cần đưa giá trị thay thế về khoảng trắng "" hoặc số 0.

• Ẩn hoàn toàn (để ô trống): =IFERROR(Vlookup(B13, A1:C11,3, 0), "")
• Chuyển lỗi N/A trong Excel về số 0: =IFERROR(Vlookup(B13, A1:C11,3, 0), 0)

Lưu ý: Tùy theo cài đặt dấu phân cách của máy tính, dấu “,” có thể thay thế bằng “;” và ngược lại.

Mẹo phòng tránh lỗi #N/A khi xây dựng công thức Excel

Để hạn chế phát sinh lỗi N/A trong Excel, bạn nên chuẩn hóa dữ liệu và thiết lập công thức một cách chính xác, nhất quán ngay từ đầu.

Chuẩn hóa dữ liệu đầu vào trước khi dùng hàm tra cứu

Trước khi dùng Vlookup, MATCH hoặc HLOOKUP, hãy đảm bảo:

• Đồng nhất kiểu dữ liệu: Đảm bảo cột chứa giá trị tìm kiếm và cột đầu tiên của vùng chứa dữ liệu gốc cùng định dạng. Bạn có thể bôi chọn cột, vào thẻ Home → mục Number kiểm tra xem chúng đều là General, Text hay Number.
• Chuyển đổi số dạng Text thành Số thực: Nếu góc ô có dấu tam giác xanh lá cây biểu thị số đang bị lưu dạng Text, hãy bôi chọn các ô đó, bấm vào biểu tượng chấm than màu vàng xuất hiện bên cạnh và chọn Convert to Number.

Dùng TRIM và CLEAN để loại bỏ ký tự ẩn gây lỗi

Hàm TRIM loại bỏ khoảng trắng thừa ở đầu, cuối và giữa chuỗi. Hàm CLEAN xóa các ký tự không in được (ký tự ẩn) mà mắt thường không nhìn thấy. Khi kết hợp cả hai, dữ liệu được chuẩn hóa hoàn toàn trước khi tra cứu hoặc tính toán, giúp hạn chế lỗi N/A trong Excel phát sinh do khoảng trắng hoặc ký tự ẩn lẫn trong ô dữ liệu.

Công thức: =TRIM(CLEAN(A1)) (trong đó A1 là ô chứa dữ liệu cần làm sạch).

Lỗi #N/A trong Excel hoàn toàn có thể xử lý nhanh nếu bạn hiểu đúng nguyên nhân và áp dụng đúng công thức. Hy vọng những thủ thuật tin học văn phòng trên sẽ giúp bạn hạn chế tối đa lỗi N/A trong Excel khi làm việc với dữ liệu và các hàm tra cứu.

Lưu ý: Một số hình ảnh trong bài viết được biên tập và tổng hợp từ các nguồn khác nhau