Có thể bạn quan tâm: Hướng Dẫn Sử Dụng Excel: Từ Cơ Bản Đến Nâng Cao Cho Người Mới Bắt Đầu
1. Giới thiệu chung về lỗi #N/A
Trong quá trình sử dụng Microsoft Excel, người dùng thường gặp phải các thông báo lỗi khác nhau như #DIV/0!, #VALUE!, #REF!, và #N/A. Trong số đó, lỗi #N/A (Not Available) là một trong những lỗi gây khó chịu nhất vì nó không chỉ xuất hiện trong các công thức tính toán mà còn xuất hiện trong các hàm tra cứu, hàm thống kê và thậm chí trong các biểu đồ. Để xử lý lỗi này một cách hiệu quả, trước hết chúng ta cần hiểu rõ nó là gì, tại sao lại xuất hiện và làm sao để ngăn ngừa hoặc khắc phục.
1.1. Định nghĩa lỗi #N/A
- #N/A là viết tắt của “Not Available” – tức là “Không khả dụng”. Khi Excel trả về giá trị này, nó đang thông báo rằng dữ liệu mà công thức yêu cầu không tồn tại trong phạm vi được chỉ định.
- Khác với các lỗi như #DIV/0! (phép chia cho 0) hay #VALUE! (kiểu dữ liệu không hợp lệ), lỗi #N/A thường liên quan đến thiếu dữ liệu hoặc không tìm thấy giá trị trong quá trình tra cứu.
1.2. Tầm quan trọng của việc xử lý lỗi #N/A
- Ảnh hưởng đến tính chính xác: Khi một công thức trả về #N/A, các công thức phụ thuộc vào nó cũng sẽ nhận giá trị lỗi, làm sai lệch toàn bộ báo cáo.
- Gây khó chịu cho người dùng: Khi xem bảng tính, một dải ô chứa #N/A khiến người xem khó hiểu, đặc biệt là khi không biết nguyên nhân.
- Khi tạo biểu đồ: Các biểu đồ sẽ bỏ qua các điểm dữ liệu có lỗi #N/A, dẫn tới biểu đồ không đầy đủ hoặc sai lệch.
Vì vậy, việc nhận diện, phân tích và khắc phục lỗi #N/A là một kỹ năng thiết yếu cho mọi người dùng Excel, từ người mới bắt đầu đến các chuyên gia phân tích dữ liệu.
Có thể bạn quan tâm: Cách Chỉnh Ngày Tháng Năm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Nguyên nhân phổ biến gây ra lỗi #N/A
2.1. Hàm VLOOKUP, HLOOKUP, LOOKUP, XLOOKUP, MATCH không tìm thấy giá trị
Đây là nguyên nhân điển hình nhất. Khi bạn sử dụng các hàm tra cứu và không có giá trị khớp trong bảng dữ liệu, Excel sẽ trả về #N/A.
Ví dụ:
=VLOOKUP("Sản phẩm A", A2:B10, 2, FALSE)
Nếu “Sản phẩm A” không tồn tại trong cột A2:A10, kết quả sẽ là #N/A.
2.2. Lỗi trong tham chiếu bảng hoặc phạm vi
- Tham chiếu sai phạm vi: Khi phạm vi tra cứu không bao gồm cột chứa giá trị cần trả về.
- Tham chiếu tới ô trống: Nếu công thức tham chiếu tới một ô trống và kết quả của hàm yêu cầu một giá trị số hoặc văn bản, có thể dẫn tới #N/A.
2.3. Dữ liệu không đồng nhất về kiểu
- Số và văn bản: Khi bạn so sánh một số dạng “123” (văn bản) với 123 (số), Excel sẽ không coi chúng là bằng nhau, dẫn tới #N/A trong các hàm tra cứu.
- Khoảng trắng thừa: Dữ liệu có khoảng trắng ở đầu hoặc cuối (space) sẽ không khớp trong tra cứu.
2.4. Các hàm thống kê và mảng trả về lỗi
- INDEX/MATCH trong mảng: Khi hàm INDEX không thể tìm thấy vị trí hợp lệ do MATCH trả về #N/A, toàn bộ công thức sẽ trả về #N/A.
- AGGREGATE, SMALL, LARGE: Khi các hàm này không tìm thấy giá trị thỏa mãn điều kiện, chúng cũng có thể trả về #N/A.
2.5. Các hàm liên quan tới thời gian và ngày tháng
- Khi sử dụng VLOOKUP hoặc XLOOKUP để tra cứu ngày tháng, nếu định dạng ngày tháng không nhất quán (ví dụ: “2026/06/01” vs “01-06-2026”), Excel sẽ không nhận ra khớp và trả về #N/A.
2.6. Lỗi trong các hàm mảng động (Dynamic Array)
- Trong các phiên bản Excel hỗ trợ Dynamic Array (Office 365, Excel 2026), các hàm như FILTER, UNIQUE, SORT có thể trả về #N/A nếu không có dữ liệu thỏa mãn điều kiện lọc.
2.7. Lỗi do các hàm tùy chỉnh (User Defined Functions – UDF)
- Nếu một hàm VBA tùy chỉnh trả về CVErr(xlErrNA), Excel sẽ hiển thị #N/A. Điều này thường xảy ra khi lập trình viên muốn thông báo “không có dữ liệu”.
Có thể bạn quan tâm: Hàm Viết Hoa Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Cách phát hiện nhanh lỗi #N/A trong bảng tính
3.1. Sử dụng tính năng “Go To Special”
- Nhấn
F5→ “Special” → Chọn “Formulas” → Tick “Errors”. - Excel sẽ đánh dấu tất cả các ô có lỗi, trong đó bao gồm #N/A.
Mẹo: Khi muốn lọc chỉ #N/A, bạn có thể dùng Conditional Formatting (định dạng có điều kiện) với công thức
=ISNA(A1).
3.2. Dùng hàm ISNA và IFERROR
- ISNA(value): Trả về TRUE nếu
valuelà #N/A, ngược lại trả về FALSE. - IFERROR(value, value_if_error): Thay thế mọi lỗi (bao gồm #N/A) bằng giá trị tùy ý.
=IFERROR(VLOOKUP(A2, $D$2:$E$100, 2, FALSE), "Không tồn tại")
3.3. Kiểm tra bằng “Error Checking”
- Formulas → Error Checking → Trace Error: Công cụ này giúp tìm nguồn gốc của lỗi trong công thức.
3.4. Sử dụng “Evaluate Formula”
- Formulas → Evaluate Formula: Cho phép bước từng bước thực thi công thức, giúp nhận ra tại đâu giá trị #N/A xuất hiện.
4. Các phương pháp khắc phục lỗi #N/A
4.1. Kiểm tra và chuẩn hoá dữ liệu nguồn
- Loại bỏ khoảng trắng thừa: Dùng hàm
TRIMhoặc Find & Replace (Ctrl+H) để xóa khoảng trắng. - Chuyển đổi kiểu dữ liệu: Dùng
VALUEđể chuyển văn bản số sang số, hoặcTEXTđể chuyển số sang văn bản. - Đảm bảo định dạng ngày tháng đồng nhất: Sử dụng
DATEVALUEhoặc định dạng ô để chuẩn hoá.
4.2. Sử dụng tham số FALSE trong VLOOKUP/HLOOKUP
Có thể bạn quan tâm: Ký Hiệu Phi (φ) Trong Excel: Cách Tạo, Ứng Dụng Và Mẹo Thủ Thuật
Khi bạn muốn tra cứu chính xác, luôn dùng FALSE (hoặc 0) trong đối số cuối cùng:
=VLOOKUP(A2, $D$2:$E$100, 2, FALSE)
Nếu bỏ qua, Excel sẽ thực hiện tra cứu gần đúng, đôi khi dẫn tới lỗi #N/A khi dữ liệu không được sắp xếp.
4.3. Sử dụng hàm IFNA (Excel 2026 trở lên)
IFNA(value, value_if_na) chỉ xử lý #N/A, không ảnh hưởng tới các lỗi khác.
=IFNA(VLOOKUP(A2, $D$2:$E$100, 2, FALSE), "Không tìm thấy")
4.4. Kết hợp IFERROR và IFNA
Nếu muốn bảo vệ công thức khỏi mọi lỗi, nhưng vẫn muốn xử lý riêng #N/A, bạn có thể lồng nhau:
=IFERROR(IFNA(VLOOKUP(A2, $D$2:$E$100, 2, FALSE), "Không tồn tại"), "Lỗi khác")
4.5. Sử dụng XLOOKUP (Excel 365/2026)
XLOOKUP có đối số tùy chọn cho giá trị không tìm thấy, giúp tránh lỗi #N/A ngay từ đầu.
=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "Không tồn tại", 0)
- Tham số thứ 4 là value_if_not_found (giá trị trả về nếu không tìm thấy).
- Tham số thứ 5 là match_mode (
0= khớp chính xác).
4.6. Kiểm tra phạm vi tra cứu
- Đảm bảo cột tra cứu (cột thứ nhất trong VLOOKUP) có chứa giá trị bạn đang tìm.
- Nếu cần tra cứu sang cột bên trái, hãy dùng INDEX/MATCH hoặc XLOOKUP.
=INDEX($E$2:$E$100, MATCH(A2, $D$2:$D$100, 0))
4.7. Sử dụng hàm FILTER với IFERROR (Dynamic Array)
Khi muốn lọc dữ liệu và trả về “Không có dữ liệu” nếu không có kết quả:
=IFERROR(FILTER($B$2:$B$100, $A$2:$A$100 = D2), "Không có kết quả")
4.8. Tránh lỗi trong biểu đồ
- Loại bỏ hoặc đổi màu các ô lỗi bằng cách áp dụng Conditional Formatting để hiển thị giá trị rỗng (
""). - Khi tạo biểu đồ, bỏ qua các ô có lỗi bằng cách chọn Select Data → Hidden and Empty Cells → Gaps.
4.9. Kiểm tra công thức VBA
Nếu bạn sử dụng macro, hãy kiểm tra đoạn mã trả về CVErr(xlErrNA). Thay vì trả về lỗi, bạn có thể trả về chuỗi rỗng hoặc một thông báo tùy chỉnh.
If IsError(myResult) Then myResult = "Không có dữ liệu"
End If
5. Các ví dụ thực tế và cách áp dụng
5.1. Ví dụ 1: Tra cứu giá bán sản phẩm
Bảng dữ liệu (Sheet1):
| Mã SP | Tên SP | Giá |
|---|---|---|
| P001 | Bút | 5 |
| P002 | Vở | 12 |
| P003 | Thước | 3 |
Yêu cầu: Từ danh sách đơn hàng (Sheet2) tra cứu giá.
=VLOOKUP(A2, Sheet1!$A$2:$C$4, 3, FALSE)
- Khi
A2là “P004” (không tồn tại), công thức trả về #N/A. - Sửa bằng
IFNA:
=IFNA(VLOOKUP(A2, Sheet1!$A$2:$C$4, 3, FALSE), "Giá chưa cập nhật")
5.2. Ví dụ 2: Đánh giá điểm trung bình sinh viên
Bảng điểm (Sheet3):
| Mã SV | Toán | Văn | Anh |
|---|---|---|---|
| SV01 | 8 | 7 | 9 |
| SV02 | 6 | 5 | 7 |
| SV03 | 8 | 6 |
Công thức trung bình:
=AVERAGE(B2:D2)
- Đối với SV03, cột Toán trống, Excel vẫn tính trung bình, không trả về #N/A.
- Tuy nhiên, nếu dùng
VLOOKUPđể lấy điểm, có thể gặp #N/A nếu mã sinh viên không tồn tại trong danh sách.
5.3. Ví dụ 3: Sử dụng XLOOKUP để tránh #N/A
=XLOOKUP("P005", Sheet1!$A$2:$A$4, Sheet1!$C$2:$C$4, "Không tồn tại", 0)
Kết quả: “Không tồn tại” thay vì #N/A.
5.4. Ví dụ 4: Lọc danh sách khách hàng chưa mua trong 30 ngày
=IFERROR(FILTER($A$2:$A$100, $B$2:$B$100 < TODAY()-30), "Tất cả khách hàng đều mua gần đây")
Nếu không có khách hàng nào thỏa mãn, hàm FILTER trả về #N/A, được xử lý bằng IFERROR.
6. Những lưu ý quan trọng khi làm việc với lỗi #N/A
| Lưu ý | Mô tả |
|---|---|
| Kiểm tra dữ liệu đầu vào | Đảm bảo không có khoảng trắng, lỗi định dạng hoặc dữ liệu trùng lặp. |
| Sử dụng hàm phù hợp | XLOOKUP và FILTER (đối với Excel mới) giúp giảm thiểu lỗi #N/A. |
Không dùng IFERROR một cách vô tội | IFERROR che giấu mọi lỗi, khiến bạn không nhận ra vấn đề thực sự (ví dụ: lỗi #DIV/0!). |
| Kiểm tra phạm vi tra cứu | Đảm bảo cột tra cứu luôn nằm ở vị trí đầu tiên (đối với VLOOKUP) hoặc sử dụng INDEX/MATCH. |
| Đối xử với lỗi trong biểu đồ | Tránh để lỗi #N/A làm “đứt” đường biểu đồ; dùng NA() để tạo khoảng trống có kiểm soát. |
| Ghi chú trong file | Thêm comment hoặc đánh dấu màu cho các ô có thể gây lỗi, giúp người dùng khác hiểu nguyên nhân. |
| Kiểm tra phiên bản Excel | Một số hàm mới (XLOOKUP, FILTER) chỉ khả dụng trong Office 365/Excel 2026+. Nếu dùng phiên bản cũ, cần dự phòng bằng các hàm truyền thống. |
7. Quy trình chuẩn để xử lý lỗi #N/A trong dự án thực tế
- Xác định vị trí lỗi
Sử dụng “Go To Special” → “Formulas” → “Errors”.
Kiểm tra công thức
Dùng “Evaluate Formula” để xem từng bước tính toán.
Kiểm tra dữ liệu nguồn
Loại bỏ khoảng trắng, chuẩn hoá kiểu dữ liệu, kiểm tra ngày tháng.
Thay đổi công thức
- Nếu là VLOOKUP/HLOOKUP → chuyển sang XLOOKUP hoặc INDEX/MATCH.
Thêm
IFNAhoặcIFERRORđể xử lý giá trị không tồn tại.Kiểm tra lại toàn bộ bảng
Dùng “Conditional Formatting” với công thức
=ISNA(A1)để hiển thị các ô lỗi còn lại.Cập nhật tài liệu
Ghi chú trong file (comment, sheet “Notes”) để người dùng biết cách xử lý lỗi trong tương lai.
Kiểm thử
- Thêm test case (ví dụ: nhập một giá trị không tồn tại) để xác nhận công thức trả về thông báo mong muốn, không phải #N/A.
8. Kết luận
Lỗi #N/A trong Excel không chỉ là một thông báo “không có dữ liệu”, mà còn là cảnh báo về việc dữ liệu đầu vào hoặc công thức chưa được tối ưu. Bằng cách:
- Hiểu rõ nguyên nhân (tra cứu không tìm thấy, dữ liệu không đồng nhất, phạm vi sai, …),
- Sử dụng các công cụ phát hiện (Go To Special, Conditional Formatting, Evaluate Formula),
- Áp dụng các hàm xử lý lỗi (
IFNA,IFERROR,XLOOKUP,FILTER), - Chuẩn hoá dữ liệu (loại bỏ khoảng trắng, đồng nhất kiểu dữ liệu),
bạn sẽ giảm thiểu đáng kể sự xuất hiện của #N/A, nâng cao độ tin cậy và chuyên nghiệp của báo cáo Excel. Hãy luôn nhớ rằng, lỗi không phải là vấn đề cuối cùng mà là cơ hội để cải thiện quy trình làm việc và chất lượng dữ liệu.
Tip cuối cùng: Khi tạo một mô hình hay báo cáo mới, hãy thiết kế sẵn các công thức với xử lý lỗi (ví dụ: dùng
IFNAngay từ đầu). Điều này giúp giảm thời gian bảo trì và tránh những bất ngờ không mong muốn khi dữ liệu thực tế thay đổi.
Chúc bạn làm việc hiệu quả và luôn “không còn #N/A” trong các file Excel của mình!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.