Lỗi công thức Excel #N/A, #VALUE!, #REF!: cách sửa nhanh
Giải thích nguyên nhân và cách sửa các lỗi công thức Excel thường gặp: #N/A, #VALUE!, #REF!, #NAME?, #DIV/0!, #NUM!, #SPILL!, ##### và tham chiếu vòng.
Thấy ô Excel hiện #N/A, #VALUE! hay #REF! đừng vội xoá công thức — mỗi mã lỗi đều cho biết khá rõ chỗ hỏng. Bài này giải thích từng lỗi, nguyên nhân hay gặp nhất và cách sửa, kèm bảng tóm tắt để tra nhanh. Mẹo chung: bấm vào ô lỗi, biểu tượng dấu chấm than bên cạnh có mục Help on this Error và Show Calculation Steps giúp xem công thức hỏng ở bước nào.
1. Bảng tóm tắt
| Lỗi | Ý nghĩa | Nguyên nhân hay gặp |
|---|---|---|
#N/A | Không tìm thấy | VLOOKUP/MATCH không thấy giá trị |
#VALUE! | Sai kiểu dữ liệu | Cộng trừ chữ, ngày dạng text |
#REF! | Tham chiếu không còn | Xoá dòng/cột/sheet đang được dùng |
#NAME? | Excel không hiểu tên | Gõ sai tên hàm, thiếu ngoặc kép |
#DIV/0! | Chia cho 0 | Mẫu số trống hoặc bằng 0 |
#NUM! | Số không hợp lệ | Ngày đầu lớn hơn ngày cuối, căn số âm |
#SPILL! | Không tràn được kết quả | Vùng tràn có dữ liệu, ô gộp |
##### | Không đủ chỗ hiển thị | Cột hẹp, ngày âm |
| Circular Reference | Tham chiếu vòng | Công thức tham chiếu chính ô của nó |
2. #N/A — không tìm thấy giá trị
Gặp ở VLOOKUP, XLOOKUP, MATCH. Nguyên nhân: mã cần tìm thật sự không có; có khoảng trắng thừa (“NV01 ”); một bên là số, bên kia là số dạng chữ; quên đối số FALSE/0 để khớp chính xác; vùng tra cứu không cố định $ nên bị trượt khi kéo.
Cách sửa: làm sạch bằng TRIM, đồng nhất kiểu dữ liệu (xem hàm TEXT và VALUE), cố định vùng bằng F4. Khi “không tìm thấy” là chuyện bình thường, bọc IFERROR(…,"Không có") — xem hàm IFERROR.
3. #VALUE! — sai kiểu dữ liệu
Xảy ra khi phép tính gặp chữ: =A2+B2 mà B2 là “500k”, ngày sinh dạng text khiến DATEDIF lỗi, hoặc vùng trong công thức mảng không cùng kích thước. Kiểm tra từng ô tham chiếu: số dạng chữ thường căn trái. Dùng SUM(A2:B2) thay vì A2+B2 nếu muốn bỏ qua ô chữ.
4. #REF! — tham chiếu bị mất
Bạn xoá cột C trong khi công thức =B2*C2 đang dùng nó, công thức thành =B2*#REF!. Cách sửa: nhấn Ctrl + Z ngay nếu vừa xoá; nếu không, sửa lại địa chỉ trong công thức. Cũng gặp khi VLOOKUP đòi cột thứ 5 trong vùng chỉ có 4 cột, hoặc file liên kết bị đổi tên, di chuyển.
5. #NAME? — Excel không nhận ra tên
- Gõ sai tên hàm:
=VLOKUP,=SUMIF S. - Chữ trong công thức thiếu ngoặc kép:
=IF(A2=Đạt,…)phải là"Đạt". - Dùng hàm mới (XLOOKUP, FILTER, LAMBDA…) trên Excel 2019 trở về trước.
- Hàm tự viết bằng VBA nhưng file lưu .xlsx hoặc macro bị chặn.
Mẹo: gõ tên hàm bằng chữ thường; nếu Excel tự đổi sang chữ hoa khi Enter tức là đúng tên.
6. #DIV/0!, #NUM!, #SPILL!
#DIV/0!: mẫu số bằng 0 hoặc trống. Dùng =IF(C2=0,"",B2/C2) — chi tiết trong bài cách tính phần trăm. AVERAGE của vùng toàn ô trống cũng gây lỗi này.
#NUM!: số không hợp lệ cho hàm — DATEDIF với ngày bắt đầu lớn hơn ngày kết thúc, SQRT của số âm, kết quả quá lớn, hoặc hàm lặp như IRR không hội tụ.
#SPILL!: công thức mảng động (FILTER, UNIQUE, SORT…) không có chỗ trống để trả kết quả. Xoá dữ liệu chắn đường, bỏ gộp ô, hoặc đặt công thức ra ngoài Excel Table. Xem hàm FILTER, UNIQUE, SORT.
7. ##### và tham chiếu vòng
Ô hiện #####
Không phải lỗi công thức: cột quá hẹp để hiển thị số hoặc ngày. Nhấp đúp vào mép phải tiêu đề cột để tự giãn. Nếu giãn rồi vẫn #####, ô chứa ngày/giờ âm (ví dụ lấy ngày trước trừ ngày sau) — kiểm tra lại thứ tự phép trừ.
Circular Reference (tham chiếu vòng)
Khi công thức trực tiếp hoặc gián tiếp dùng chính ô của nó (ví dụ D10 là =SUM(D2:D10)), Excel hiện cảnh báo There are one or more circular references… và kết quả thường là 0.
- Vào Formulas → Error Checking → Circular References để xem ô gây vòng lặp (thanh trạng thái góc dưới cũng ghi địa chỉ ô).
- Sửa vùng tham chiếu, ví dụ
=SUM(D2:D9). - Lặp lại đến khi mục Circular References bị mờ.
8. Không phải lỗi nhưng hay bị nhầm
Công thức hiện nguyên văn thay vì kết quả, hoặc sửa số liệu mà kết quả không đổi — đó là vấn đề định dạng Text hoặc chế độ tính toán thủ công, xem Excel không nhảy công thức. Trên Google Sheets, các mã lỗi gần như giống Excel; di chuột vào ô lỗi để xem lời giải thích, riêng lỗi tràn mảng hiển thị là #REF!.