Mỗi mã lỗi công thức Excel chỉ về một loại nguyên nhân: #N/A là không tìm thấy, #VALUE! là sai kiểu dữ liệu, #REF! là tham chiếu đã mất, #NAME? là Excel không nhận ra tên hàm hay tên vùng, #DIV/0! là chia cho 0, #SPILL! là kết quả mảng không có chỗ tràn. Đọc đúng mã lỗi là đi được nửa đường, nửa còn lại là tìm xem ô nào, phần nào của công thức gây ra nó.
Bài viết gồm bảng ý nghĩa các mã lỗi, quy trình năm bước dùng công cụ có sẵn trong Excel để lần ra nguồn lỗi, cách gửi thông tin cho AI để nó chẩn đoán thay vì viết lại công thức một cách đoán mò, và khi nào bọc IFERROR là che giấu vấn đề chứ không phải giải quyết.
Các mã lỗi thường gặp và nguyên nhân hay gặp nhất
Bảng dưới gom các mã lỗi bạn sẽ gặp trong công việc hằng ngày. Cột cuối là nơi nên nhìn đầu tiên, vì đó là nguyên nhân chiếm phần lớn các trường hợp trong bảng tính văn phòng.
| Mã lỗi | Excel đang nói gì | Nên kiểm đầu tiên |
|---|---|---|
| #N/A | Không tìm thấy giá trị cần dò | Khoảng trắng thừa, số lưu dạng chữ, mã thật sự chưa có trong bảng tra |
| #VALUE! | Phép tính nhận sai kiểu dữ liệu | Ô chữ trong phép cộng trừ, ngày dạng chữ, các vùng trong SUMIFS hay XLOOKUP lệch kích thước |
| #REF! | Tham chiếu trỏ vào ô không còn tồn tại | Có ai đã xoá dòng, cột hoặc trang tính mà công thức đang dùng |
| #NAME? | Không nhận ra tên | Gõ sai tên hàm, chữ thiếu ngoặc kép, tên vùng chưa đặt, hàm không có ở phiên bản này |
| #DIV/0! | Chia cho 0 hoặc ô trống | Dòng chưa có số liệu ở mẫu số |
| #SPILL! | Kết quả mảng không tràn ra được | Vùng tràn có ô không trống, có ô gộp, hoặc công thức nằm trong Table |
| #NUM! | Giá trị số không hợp lệ | Phép tính cho kết quả quá lớn, hàm lặp không hội tụ, ngày ngoài phạm vi |
| ##### | Không phải lỗi công thức | Cột quá hẹp để hiện số, hoặc ngày giờ ra kết quả âm |
Đáng nhớ nhất là #NAME? khi mở tệp trên máy khác. Bạn dùng XLOOKUP, FILTER hay UNIQUE trên Microsoft 365, đồng nghiệp mở bằng Excel 2016, mọi ô dùng hàm đó hiện #NAME?. Công thức không sai, chỉ là phiên bản bên kia không có hàm. Dấu hiệu nhận ra: trong thanh công thức, tên hàm có thể hiện kèm tiền tố lạ như _xlfn.
Quy trình năm bước lần ra nguồn lỗi
Thay vì sửa công thức theo cảm tính, bạn có thể đi theo một trình tự cố định. Các công cụ dưới đây đều có sẵn trong thẻ Formulas (Công thức), nhóm Formula Auditing.
- Xem lỗi xuất phát từ đâu. Một ô lỗi thường kéo theo mọi ô dùng nó. Chọn ô lỗi, bấm Trace Precedents để Excel vẽ mũi tên đến các ô mà công thức đang dùng. Nếu một ô nguồn cũng đang lỗi, đi ngược về ô đó trước.
- Dùng Evaluate Formula. Công cụ này tính công thức từng bước, mỗi lần bấm Evaluate là một phần được thay bằng giá trị. Bước nào hiện ra lỗi chính là phần gây lỗi.
- Thử từng phần bằng F9. Trong thanh công thức, bôi đen một phần như hàm MATCH bên trong, nhấn F9 để xem giá trị phần đó. Xem xong nhấn Esc, nếu nhấn Enter công thức sẽ bị thay bằng giá trị.
- Kiểm kiểu dữ liệu ô nguồn. Ở ô trống bên cạnh, gõ ISNUMBER, ISTEXT và LEN cho ô nguồn. Một mã hàng trông là 125 mà ISTEXT trả TRUE, hay LEN trả 4 cho chuỗi ba ký tự, là đã thấy thủ phạm.
- Thu hẹp bằng dữ liệu tối thiểu. Chép công thức sang một trang trống, gõ tay hai ba dòng dữ liệu đơn giản. Nếu công thức chạy đúng với dữ liệu gõ tay, vấn đề nằm ở dữ liệu thật; nếu vẫn lỗi, vấn đề nằm ở công thức.
Năm bước này thường mất chưa đến mười phút và cho bạn câu trả lời chính xác hơn việc thử viết lại công thức nhiều lần. Kết quả của các bước, nhất là bước bốn, cũng là thông tin quý để gửi cho AI nếu bạn vẫn cần trợ giúp.
Lỗi #N/A: đừng vội nghĩ công thức sai
#N/A xuất hiện chủ yếu ở các hàm dò tìm như VLOOKUP, XLOOKUP, MATCH. Phần lớn trường hợp, công thức hoàn toàn đúng nhưng hai bên dữ liệu không khớp nhau tuyệt đối: bên này "SP-001", bên kia "SP-001 " có dấu cách cuối; bên này số 1205, bên kia chữ "1205"; bên này gạch nối thường, bên kia gạch ngang dài do dán từ tệp Word. Excel không đoán ý, nó chỉ so từng ký tự.
Cách xác nhận nhanh: lấy ô cần tìm và ô bạn tin là tương ứng trong bảng tra, gõ =A2=DanhMuc!A15 ở một ô trống. Nếu kết quả là FALSE dù mắt nhìn thấy giống nhau, hãy so LEN của hai ô rồi đến ISNUMBER. Đa số trường hợp lộ ra ngay ở bước này.
Một dạng #N/A khác là dữ liệu thật sự thiếu: mã hàng mới chưa có trong danh mục, nhân viên mới chưa có trong bảng nhân sự. Đây là thông tin có ích chứ không phải lỗi cần giấu. Thay vì để #N/A hay bọc IFERROR thành số 0, hãy cho hiện một dòng chữ rõ ràng như "Chưa có trong danh mục" để người dùng bảng biết cần bổ sung. Ngược lại, có một loại sai còn đáng sợ hơn #N/A: VLOOKUP thiếu đối số FALSE trả về giá của một mã khác mà không báo gì. Một bảng không có #N/A nào chưa chắc đã đúng.
#VALUE!, #REF! và #DIV/0!: nguyên nhân và cách xử lý
#VALUE! do chữ lẫn trong phép tính. Phép cộng trừ nhân chia gặp ô chứa chữ, ví dụ cột tiền có ô ghi "chưa chốt" hay "1.200.000đ", sẽ báo #VALUE!. Hàm SUM thì bỏ qua chữ, nhưng dấu cộng thì không. Cách xử lý gốc là tách ghi chú sang cột riêng và giữ cột tiền chỉ chứa số.
#VALUE! do vùng lệch kích thước. SUMIFS, COUNTIFS, XLOOKUP đều đòi các vùng có cùng số dòng. Khi sửa tay một vùng từ 500 thành 600 mà quên vùng kia, lỗi xuất hiện. Dùng Table giúp tránh hẳn loại lỗi này vì tên cột luôn có cùng độ dài.
#REF! do xoá dòng, cột hoặc trang tính. Khi bạn xoá cột mà công thức đang tham chiếu, Excel thay tham chiếu bằng #REF! và không tự khôi phục. Nếu vừa xoá xong, Ctrl+Z là cách nhanh nhất. Nếu đã lưu, bạn cần biết công thức gốc trỏ vào đâu, từ phiên bản cũ của tệp hoặc từ một ô cùng cột chưa bị hỏng.
#REF! do sao chép công thức ra ngoài phạm vi. Công thức tham chiếu tương đối kiểu "ô bên trên ba dòng", chép lên dòng đầu trang tính sẽ trỏ ra ngoài bảng. Kiểm lại dấu $ trên các tham chiếu cần đứng yên.
#DIV/0! ở dòng chưa có dữ liệu. Tỷ lệ hoàn thành hay đơn giá bình quân thường chia cho một ô chưa nhập. Xử lý hợp lý là kiểm mẫu số trước: =IF(C2=0,"",B2/C2), thay vì để lỗi hiện khắp bảng hay bọc IFERROR che mọi thứ.
Gửi lỗi cho AI thế nào để nó chẩn đoán đúng
Khi tự lần không ra, AI là người trợ giúp tốt, với điều kiện bạn đưa cho nó những gì bạn đang thấy chứ không chỉ "công thức của mình lỗi". Một yêu cầu đủ thông tin gồm:
- Nguyên văn công thức, dán từ thanh công thức chứ không gõ lại.
- Mã lỗi hiện ra, và lỗi xuất hiện ở mọi dòng hay chỉ ở một số dòng.
- Phiên bản Excel và dấu phân cách máy đang dùng.
- Mô tả cột liên quan và kiểu dữ liệu, kèm kết quả ISNUMBER, ISTEXT, LEN của ô gây nghi ngờ.
- Một hai dòng dữ liệu giả định tái hiện được lỗi, thay tên người và số tiền thật bằng giá trị giả.
- Yêu cầu rõ: "Hãy chẩn đoán nguyên nhân trước, giải thích vì sao, rồi mới đề xuất sửa; đừng bọc IFERROR để che lỗi."
Câu cuối có tác dụng thật. Khi không được dặn, câu trả lời phổ biến của chatbot cho mọi lỗi là bọc IFERROR, và ô lỗi biến mất trong khi nguyên nhân vẫn còn nguyên. Yêu cầu chẩn đoán trước buộc nó đi qua các khả năng như bạn vừa làm ở năm bước trên.
Nếu tệp chứa dữ liệu nhân sự, khách hàng hay công nợ, đừng chụp màn hình cả bảng gửi cho chatbot. Công thức, mô tả cột và dữ liệu giả là đủ cho hầu hết trường hợp.
#SPILL! và #NAME?: hai lỗi của hàm mới
Từ Microsoft 365 và Excel 2021, nhiều hàm trả về cả một vùng kết quả, gọi là mảng động. Công thức FILTER hay UNIQUE đặt ở một ô sẽ tự tràn xuống các ô bên dưới. Nếu đường tràn bị chặn bởi một ô có dữ liệu, kể cả một dấu cách vô hình, Excel báo #SPILL! ở ô công thức. Bấm vào ô lỗi, Excel vẽ khung nét đứt quanh vùng cần trống và cho tuỳ chọn chọn ô đang chặn. Xoá nội dung ô chặn, gỡ ô gộp trong vùng tràn, hoặc chuyển công thức ra ngoài Table, vì mảng động không tràn được bên trong Table.
Lỗi #NAME? đi kèm hàm mới vì lý do ngược lại: tệp tạo trên bản mới, mở trên bản cũ không có hàm đó. Có một trường hợp khác cũng hay gặp là AI viết tên hàm không tồn tại, ghép từ hàm của Google Sheets hay của phần mềm khác. Nếu nghi ngờ, gõ dấu bằng và vài chữ cái đầu của tên hàm vào ô, xem danh sách gợi ý của Excel có hàm đó không. Không có trong danh sách thì hoặc phiên bản của bạn không hỗ trợ, hoặc hàm không có thật.
IFERROR: dùng đúng chỗ và lạm dụng
IFERROR(giá trị, giá trị khi lỗi) bắt mọi loại lỗi và thay bằng nội dung bạn chọn. Nó hữu ích ở bảng đã hoàn thiện, dữ liệu đã được kiểm, và bạn chỉ muốn báo cáo trông sạch khi có vài trường hợp hợp lệ như mẫu số bằng 0. Nhưng vì bắt mọi lỗi, IFERROR cũng che luôn những lỗi bạn cần thấy: tên trang tính bị đổi, vùng bị xoá, hàm không có ở phiên bản bên kia. Một bảng báo cáo đầy số 0 do IFERROR có thể trông bình thường trong khi một nửa công thức đã hỏng.
Nguyên tắc an toàn hơn là bắt đúng loại lỗi mình dự kiến. Với hàm dò tìm, dùng IFNA chỉ bắt #N/A, hoặc đối số "không tìm thấy" có sẵn của XLOOKUP. Với phép chia, kiểm mẫu số bằng IF. Các lỗi còn lại cứ để hiện ra, vì chúng là tín hiệu có điều gì đó trong bảng đã thay đổi. Nếu bạn nhận một tệp đã bọc IFERROR khắp nơi, cách kiểm nhanh là tạm thời gỡ IFERROR ở một vài cột, xem có lỗi gì đang bị giấu.
Câu hỏi thường gặp
Vì sao công thức đúng trên máy mình nhưng lỗi trên máy đồng nghiệp?
Nguyên nhân hay gặp nhất là khác phiên bản: hàm mới như XLOOKUP, FILTER, UNIQUE, LET không có trên Excel cũ và hiện #NAME?. Nguyên nhân thứ hai là công thức tham chiếu tệp khác đang mở trên máy bạn nhưng không có trên máy đồng nghiệp. Hỏi phiên bản của họ trước khi sửa công thức.
Làm sao tìm hết các ô đang lỗi trong một trang tính lớn?
Dùng Go To Special: nhấn Ctrl+G, chọn Special, chọn Formulas và chỉ đánh dấu Errors. Excel sẽ chọn mọi ô công thức đang báo lỗi. Bạn có thể tô màu chúng để rà một lượt, hoặc đếm bằng công thức kết hợp SUMPRODUCT và ISERROR trên vùng dữ liệu.
Tam giác xanh ở góc ô có phải lỗi không?
Không hẳn. Đó là cảnh báo của chức năng kiểm lỗi nền, ví dụ số lưu dạng chữ hay công thức khác các ô bên cạnh. Nhiều cảnh báo vô hại, nhưng cảnh báo số lưu dạng chữ thường là nguyên nhân của #N/A và tổng bị hụt, nên đáng xem kỹ.
Có nên dùng ISERROR thay IFERROR không?
ISERROR chỉ trả TRUE hoặc FALSE cho biết ô có lỗi hay không, hữu ích khi bạn muốn đếm hay đánh dấu dòng lỗi để rà soát. IFERROR thay thế giá trị lỗi bằng nội dung khác. Mục đích khác nhau: một cái để tìm vấn đề, một cái để hiển thị gọn.
Lỗi vòng tròn tham chiếu là gì?
Đó là khi công thức tham chiếu đến chính ô chứa nó, trực tiếp hoặc qua một chuỗi ô khác, ví dụ ô tổng nằm trong chính vùng được cộng. Excel cảnh báo và thường hiện số 0. Thẻ Formulas có mục Error Checking chỉ ra ô đang gây vòng tròn để bạn sửa.
Mở tệp CSV xong công thức nào cũng lỗi thì sao?
Tệp CSV chỉ lưu giá trị, không lưu công thức hay định dạng, và khi mở trên máy cài vùng Việt Nam, số và ngày dễ bị hiểu sai. Hãy nhập CSV qua chức năng Get Data hoặc Text Import để chọn kiểu từng cột, rồi lưu thành tệp Excel trước khi viết công thức.