Xử lý lỗi #VALUE! #N/A trong Excel có thể làm nhanh nếu bạn nhận đúng “loại lỗi”: #VALUE! thường liên quan kiểu dữ liệu/đối số sai, còn #N/A chủ yếu xảy ra khi hàm tra cứu không tìm thấy giá trị.
Trong thực tế, bạn sẽ gặp #VALUE! khi cộng trừ với “số” đang là văn bản, khi ngày tháng bị lệch định dạng, hoặc khi công thức dùng hàm chuỗi nhưng tham số không đúng kiểu.
Với #N/A, tình huống phổ biến nhất là VLOOKUP/XLOOKUP/MATCH không tìm thấy khóa tra cứu, hoặc vùng tra cứu/kiểu so khớp bị chọn sai.
Giới thiệu ý mới: Dưới đây là quy trình chẩn đoán theo “nguyên nhân → cách sửa”, kèm ví dụ công thức và mẹo phòng tránh để bạn xử lý triệt để, không chỉ “che lỗi”.
Lỗi #VALUE! và #N/A trong Excel nghĩa là gì?
#VALUE! là lỗi Excel báo “công thức không thể xử lý giá trị/kiểu dữ liệu đang tham chiếu”; #N/A là “không có giá trị phù hợp để trả về”, thường gặp khi tra cứu. Tiếp theo, hãy phân biệt nhanh bằng dấu hiệu và bối cảnh phát sinh.
Dấu hiệu nhận biết #VALUE! trong 10 giây
#VALUE! thường xuất hiện ngay sau thao tác tính toán (cộng/trừ/nhân/chia) hoặc sau khi bạn ghép chuỗi, tách chuỗi, chuyển đổi ngày-số. Vì vậy, chỉ cần nhìn “động tác” của công thức là bạn đoán được nhóm nguyên nhân. Cụ thể hơn, lỗi hay đi kèm dữ liệu “trông giống số” nhưng thực ra là text.
- Phép toán số học với ô có dấu nháy ‘ hoặc có khoảng trắng ẩn.
- Hàm chuỗi (LEFT/RIGHT/MID/FIND/SEARCH) bị tham số “độ dài/điểm bắt đầu” không hợp lệ.
- Hàm ngày (DATE/DATEVALUE) gặp định dạng ngày không nhận diện được.
Dấu hiệu nhận biết #N/A trong 10 giây
#N/A thường xuất hiện ở cột “kết quả tra cứu”: tên sản phẩm, mã hàng, giá, phòng ban… Ngược lại với #VALUE!, bạn hay thấy #N/A khi “khóa tra cứu” không tồn tại hoặc quy tắc so khớp bị sai. Tiếp theo, bạn chỉ cần kiểm tra 3 điểm: giá trị tra cứu, vùng tra cứu và kiểu so khớp.
- Khóa tra cứu không có trong bảng nguồn.
- Khác kiểu dữ liệu (mã “00123” là text nhưng bảng nguồn là số 123).
- Chọn nhầm cột trả về hoặc vùng dò tìm bị lệch.
Vì sao nên xử lý “đúng gốc” thay vì chỉ ẩn lỗi?
Che lỗi bằng IFERROR/IFNA giúp bảng tính “đẹp” hơn, nhưng nếu dữ liệu gốc sai, kết quả phân tích sẽ lệch âm thầm. Để minh họa mức độ phổ biến của sai sót, theo nghiên cứu của ICAEW từ Excel Community, vào 07/2024, tài liệu về rà soát bảng tính nêu rằng có thể tới khoảng 90% bảng tính chứa sai sót khi tổng hợp các nghiên cứu về lỗi bảng tính.
Vì sao Excel báo lỗi #VALUE!?
Có 5 nhóm nguyên nhân #VALUE! phổ biến: kiểu dữ liệu không đúng, ký tự ẩn, tham số hàm sai, tham chiếu vùng không tương thích, và dữ liệu ngày-số bị lệch chuẩn. Sau đây, bạn sẽ khoanh vùng theo “nhìn dữ liệu → kiểm tra công thức”.
Nhóm 1: Số đang là văn bản (text) nên phép tính bị “đứng hình”
Trường hợp kinh điển là ô nhìn giống 120.000 nhưng có dấu nháy ‘ ở đầu, có dấu cách cuối, hoặc được nhập bằng định dạng khác. Vì vậy, hãy thử chuyển đổi có kiểm soát: =VALUE(A2) hoặc =NUMBERVALUE(A2) (khi có dấu phân tách). Tiếp theo, dùng Paste Special → Values để “cố định” dữ liệu đã chuẩn.
Nhóm 2: Khoảng trắng/ký tự ẩn làm công thức hiểu sai
Khoảng trắng đầu-cuối, ký tự xuống dòng, ký tự không in được… có thể khiến CONCAT/VLOOKUP/so sánh chuỗi trượt. Để bắt đầu, dùng =TRIM(A2) để bỏ khoảng trắng thừa và =CLEAN(A2) để dọn ký tự không in được. Sau đó, bạn mới tính toán/tra cứu lại trên cột đã “làm sạch”.
Nhóm 3: Tham số hàm chuỗi sai hoặc trả về giá trị không hợp lệ
Khi FIND/SEARCH không tìm thấy chuỗi con, hoặc LEFT/RIGHT/MID nhận độ dài âm/0 sai logic, Excel có thể trả về lỗi kéo theo. Vì vậy, hãy bọc điều kiện trước khi cắt chuỗi, ví dụ: kiểm tra vị trí với =IFERROR(FIND(“-“,A2),0) rồi mới MID. Tiếp theo, ưu tiên IFERROR để “hạ cánh mềm” nhưng vẫn giữ log kiểm tra ở cột phụ.
Nhóm 4: Mảng/vùng tham chiếu không cùng kích thước
Khi bạn cộng hai vùng khác số hàng/cột, hoặc dùng công thức mảng mà vùng điều kiện và vùng tính không đồng bộ, #VALUE! rất dễ xuất hiện. Để hiểu rõ hơn, hãy so lại kích thước vùng: số hàng, số cột, và điểm neo $ có đúng chưa. Sau đó, thu nhỏ phạm vi về cùng kích thước rồi mở rộng dần.
Nhóm 5: Dữ liệu ngày-tháng không được Excel nhận diện
Excel có thể hiểu “01/02/2025” theo ngày/tháng hoặc tháng/ngày tùy thiết lập vùng. Vì vậy, nếu DATEVALUE trả về lỗi, hãy chuẩn hóa bằng cách tách ngày-tháng-năm rồi ghép lại bằng =DATE(year,month,day). Tiếp theo, định dạng hiển thị (Format Cells) chỉ là “áo ngoài”; điều quan trọng là giá trị nền phải là số serial của ngày.
Cách xử lý lỗi #VALUE! theo từng tình huống công thức
Có 6 bước xử lý #VALUE! hiệu quả: xác định ô nguồn, làm sạch dữ liệu, chuyển kiểu dữ liệu, sửa tham số hàm, kiểm tra vùng tham chiếu, rồi mới dùng IFERROR để hiển thị thân thiện. Tiếp theo là các “case” thường gặp kèm hướng sửa nhanh.
Case 1: Cộng/trừ bị #VALUE! vì có ô rỗng dạng “”
Nhiều file dùng công thức trả về chuỗi rỗng “” trong cột số, khiến phép cộng hiểu là text. Để bắt đầu, thay vì cộng trực tiếp, ưu tiên hàm tổng có khả năng “bỏ qua” text: =SUM(C10,E10,G10). Tiếp theo, nếu cần giữ “” để hiển thị, hãy chuyển “” thành 0 ở cột phụ trước khi cộng.
Case 2: AVERAGE/SUM bị #VALUE! do vùng có ô lỗi
Nếu vùng tham chiếu có #VALUE!, phép tính tổng hợp sẽ “lây lỗi”. Vì vậy, hãy cân nhắc công thức bỏ qua lỗi (tùy phiên bản Excel), hoặc dùng IF/ISERROR để lọc trước khi tính. Tiếp theo, tách vùng dữ liệu sạch và vùng dữ liệu thô sẽ giúp bạn kiểm soát tốt hơn.
Case 3: Ghép chuỗi OK nhưng tách chuỗi lại #VALUE!
Khi bạn tách theo dấu phân cách mà dữ liệu có dòng thiếu dấu “-” hoặc “/”, FIND/SEARCH sẽ fail. Để minh họa, dùng =IFERROR(TEXTBEFORE(A2,”-“),A2) (nếu có) hoặc bọc IFERROR quanh FIND. Sau đó, bạn mới xử lý phần còn lại.
Bảng dưới đây giúp bạn nhận diện nhanh “triệu chứng #VALUE! → nguyên nhân → cách sửa” để chọn đúng thao tác trong 1–2 phút.
| Triệu chứng | Nguyên nhân hay gặp | Cách xử lý nhanh | Hàm gợi ý |
|---|---|---|---|
| Cộng trừ ra #VALUE! | Số là text / có khoảng trắng | Chuẩn hóa dữ liệu rồi tính | VALUE, NUMBERVALUE, TRIM |
| Hàm chuỗi lỗi | Không tìm thấy ký tự phân cách | Bọc lỗi và đặt giá trị dự phòng | IFERROR, FIND/SEARCH |
| AVERAGE/SUM bị lỗi | Vùng có ô lỗi hoặc “” | Bỏ qua lỗi / tách vùng sạch | IFERROR, ISERROR, SUM |
| Ngày tháng lỗi | Định dạng vùng/chuẩn ngày khác | Tách-ngày-tháng-năm rồi DATE | DATE, DATEVALUE |
Vì sao xuất hiện lỗi #N/A khi tra cứu?
#N/A xuất hiện khi hàm tra cứu không tìm thấy giá trị cần dò trong bảng nguồn, hoặc thiết lập tra cứu (vùng/kiểu khớp/cột trả về) không đúng. Bên cạnh đó, khác kiểu dữ liệu giữa hai bảng cũng là thủ phạm hàng đầu.
Khóa tra cứu “có vẻ giống nhau” nhưng thực ra khác nhau
Ví dụ mã “00123” (text) và 123 (number) nhìn gần như trùng, nhưng Excel coi là hai giá trị khác. Vì vậy, hãy chuẩn hóa: hoặc đưa cả hai về text (TEXT), hoặc đưa về số (VALUE/NUMBERVALUE). Tiếp theo, dùng TRIM/CLEAN để loại bỏ khoảng trắng ẩn trước khi tra cứu.
Vùng tra cứu bị lệch hoặc cột trả về chọn sai
VLOOKUP yêu cầu cột khóa nằm bên trái vùng dò; nếu bạn chọn nhầm vùng, kết quả sẽ ra #N/A hoặc sai lệch. Để bắt đầu, khóa tra cứu phải nằm ở cột đầu của table_array, còn col_index_num phải trỏ đúng cột cần trả về. Tiếp theo, cố định vùng bằng dấu $ để tránh “trôi vùng” khi kéo công thức.
Chế độ so khớp (approximate vs exact) không phù hợp
Nếu bạn dùng VLOOKUP với so khớp gần đúng mà bảng không sắp xếp đúng, kết quả có thể sai hoặc #N/A tùy tình huống. Vì vậy, khi không chắc, hãy dùng so khớp chính xác: tham số cuối là FALSE. Tiếp theo, cân nhắc XLOOKUP vì mặc định dễ kiểm soát hơn trong nhiều trường hợp.
Cách xử lý lỗi #N/A trong VLOOKUP/XLOOKUP/MATCH
Cách xử lý #N/A hiệu quả gồm 5 bước: xác nhận khóa tồn tại, chuẩn hóa kiểu dữ liệu, sửa vùng dò, chọn đúng chế độ so khớp, và dùng IFNA/IFERROR để hiển thị có kiểm soát. Tiếp theo là công thức mẫu bạn có thể áp dụng ngay.
Ưu tiên IFNA khi bạn chỉ muốn “bắt” riêng lỗi #N/A
Nếu bạn muốn chỉ xử lý trường hợp không tìm thấy (mà không che các lỗi khác), IFNA là lựa chọn đúng. Ví dụ: =IFNA(VLOOKUP(D2,$D$6:$E$8,2,FALSE),”Không tìm thấy”). Tiếp theo, bạn có thể trả về rỗng “” để báo cáo gọn hơn, nhưng vẫn nên có cột “log” cho các bản in quan trọng.
Dùng IFERROR khi bạn muốn “bắt tất cả” lỗi và trả về giá trị dự phòng
IFERROR sẽ thay thế mọi lỗi (#N/A, #VALUE!, #DIV/0!…) bằng giá trị bạn chỉ định, nên tiện nhưng dễ che mất lỗi dữ liệu. Ví dụ: =IFERROR(XLOOKUP(A2,KeyRange,ReturnRange),””). Tiếp theo, dùng IFERROR cho giao diện báo cáo, còn phần xử lý dữ liệu nên sửa từ gốc.
Mẹo “bắt đúng vùng”: thử tra cứu bằng MATCH trước
Khi nghi ngờ vùng dò, bạn có thể dùng MATCH để kiểm tra vị trí tồn tại của khóa: =MATCH(A2,KeyRange,0). Nếu MATCH trả về #N/A, lỗi nằm ở khóa/kiểu dữ liệu; nếu MATCH ra số, lỗi nằm ở vùng trả về hoặc công thức ghép. Tiếp theo, bạn mới viết lại XLOOKUP/VLOOKUP với vùng chuẩn.
Gợi ý dùng NA() khi bạn muốn đánh dấu thiếu dữ liệu có chủ đích
Trong một số báo cáo, bạn muốn ô “thiếu dữ liệu” không bị tính nhầm như 0. Khi đó, NA() tạo ra #N/A có chủ đích để công thức phụ thuộc biết là “không có giá trị”. Tiếp theo, bạn xử lý hiển thị bằng IFNA ở lớp báo cáo.
Quy trình 7 bước khoanh vùng và sửa lỗi trong vài phút
Có 7 bước chuẩn để xử lý nhanh: xác định ô lỗi, truy vết tham chiếu, hiển thị công thức, đánh giá từng phần, làm sạch dữ liệu, sửa công thức, rồi kiểm tra lại bằng mẫu dữ liệu cực trị. Sau đây là checklist thực chiến bạn có thể làm theo lần lượt.
Bước 1–2: Xác định điểm phát sinh và truy vết nguồn dữ liệu
Hãy bắt đầu từ ô báo lỗi, rồi lần theo các ô tham chiếu trực tiếp. Ví dụ, nếu lỗi nằm ở cột kết quả tra cứu, bạn cần kiểm tra cột khóa và bảng nguồn trước khi động vào công thức. Tiếp theo, nếu lỗi nằm ở phép tính, hãy kiểm tra kiểu dữ liệu từng ô tham gia phép toán.
Bước 3–4: Hiển thị công thức và “đánh giá” từng đoạn
Bật hiển thị công thức (Ctrl+`) giúp bạn nhìn hàng loạt công thức để tìm chỗ lệch. Sau đó, dùng Evaluate Formula (Đánh giá công thức) để xem đoạn nào trả về lỗi trước. Tiếp theo, khi đã khoanh đúng đoạn lỗi, bạn sửa tối thiểu ở đúng chỗ thay vì viết lại toàn bộ.
Bước 5: Làm sạch dữ liệu trước khi sửa công thức
Với #VALUE! và #N/A, lỗi dữ liệu (text/space/ẩn) thường là gốc. Vì vậy, tạo 1 cột “Clean” dùng TRIM/CLEAN/VALUE/NUMBERVALUE rồi cho công thức chính tham chiếu vào cột sạch. Tiếp theo, bạn sẽ thấy lỗi biến mất bền vững hơn so với chỉ bọc IFERROR.
Bước 6–7: Sửa công thức và kiểm thử bằng dữ liệu cực trị
Hãy kiểm thử bằng các trường hợp: ô rỗng, có khoảng trắng, ký tự đặc biệt, mã có số 0 ở đầu, dữ liệu rất dài, và giá trị không tồn tại trong bảng. Tiếp theo, nếu bảng tính là báo cáo định kỳ, bạn nên thêm điều kiện hiển thị (IFNA/IFERROR) ở lớp trình bày để tránh người xem hoang mang.
Cách phòng tránh #VALUE!/#N/A ngay từ khâu nhập và chuẩn hóa dữ liệu
Có 4 nguyên tắc phòng tránh cốt lõi: chuẩn hóa định dạng nhập, khóa quy tắc dữ liệu, tách “dữ liệu thô” khỏi “dữ liệu tính”, và chuẩn hóa khóa tra cứu. Để hiểu rõ hơn, bạn hãy áp dụng theo từng lớp dữ liệu dưới đây.
Chuẩn hóa “khóa tra cứu” trước khi dựng công thức
Hãy quyết định khóa là text hay number ngay từ đầu, nhất là mã có số 0 đầu (00123). Ví dụ, nếu chọn khóa là text, bạn giữ định dạng Text và dùng TEXT để đồng bộ; nếu chọn khóa là số, bạn chuyển đồng loạt bằng VALUE/NUMBERVALUE. Tiếp theo, mọi hàm tra cứu sẽ ổn định hơn và giảm #N/A rõ rệt.
Tách 2 lớp: cột “Clean” và cột “Result”
Lớp Clean chỉ làm sạch: TRIM/CLEAN/VALUE/chuẩn ngày; lớp Result mới tính toán/tra cứu. Vì vậy, khi phát sinh #VALUE!, bạn biết cần nhìn lớp Clean trước. Tiếp theo, mô hình 2 lớp này cũng giúp bạn debug nhanh khi file phình lớn.
Đặt quy tắc nhập liệu để ngăn sai từ đầu
Dùng Data Validation để giới hạn kiểu dữ liệu (số, ngày, danh sách), và định nghĩa rõ dấu phân cách (., ,) theo chuẩn nhóm làm việc. Tiếp theo, với file thu thập từ nhiều nguồn, hãy có bước import/convert cố định thay vì copy-paste trực tiếp.
Chọn hàm tra cứu phù hợp với mục tiêu
VLOOKUP đơn giản nhưng có hạn chế về chiều tra cứu và cấu trúc bảng; XLOOKUP linh hoạt hơn, còn MATCH hữu ích để kiểm tra tồn tại. Vì vậy, hãy chọn hàm theo nhu cầu: tra cứu nhanh, tra cứu nhiều cột, hay kiểm thử khóa. Tiếp theo, khi cần hiển thị thân thiện, dùng IFNA/IFERROR đúng chỗ.
Mẹo làm việc nhóm để giảm lỗi khi luân chuyển file
Muốn giảm phát sinh lỗi khi chuyển file qua lại, bạn cần thống nhất chuẩn dữ liệu, chuẩn công thức, và quy trình cập nhật. Dưới đây là 4 mẹo thực dụng, phù hợp cả file nhỏ lẫn báo cáo định kỳ.
Thống nhất phiên bản Excel và vùng thiết lập (locale)
Nếu người A dùng dấu “,” cho thập phân còn người B dùng “.”, dữ liệu có thể thành text và kéo theo #VALUE!. Vì vậy, trước khi chia sẻ, hãy thống nhất locale hoặc chuyển đổi dữ liệu về định dạng trung tính (ví dụ lưu số dạng number thật). Tiếp theo, các công thức ngày-tháng cũng sẽ ít “lệch” hơn.
Tạo “cột kiểm soát” thay vì chỉ bọc IFERROR
Bạn nên có một cột flag báo tình trạng: “OK / Thiếu khóa / Sai kiểu dữ liệu” để người nhận file biết đang thiếu gì. Điều này đặc biệt hữu ích khi bạn cần kiểm tra lỗi công thức Excel theo chuỗi xử lý. Tiếp theo, lớp báo cáo có thể dùng IFNA/IFERROR để hiển thị gọn, nhưng lớp kiểm soát vẫn giữ nguyên trạng thái thật.
Dùng lưu trữ đám mây để tránh ghi đè và lệch bản
Khi làm việc nhóm, thay vì gửi qua lại nhiều bản, hãy ưu tiên OneDrive/SharePoint để hạn chế bản trùng, từ đó chia sẻ file Excel tránh lỗi xung đột trong quá trình cập nhật. Bên cạnh đó, lịch sử phiên bản giúp bạn quay lại bản đúng khi công thức bị sửa nhầm.
Chuẩn hóa cấu trúc bảng và đặt tên vùng
Đặt tên vùng (Named Range) và dùng Table giúp công thức ít “trượt vùng” khi thêm dòng/cột. Hơn nữa, cách này làm file dễ hiểu hơn cho người mới, nhất là khi file nằm trong hệ sinh thái phần mềm văn phòng quen thuộc. Tiếp theo, bạn giảm phụ thuộc vào copy-paste thủ công—nguồn gây lỗi rất phổ biến trong nhóm người dùng phần mềm thông dụng.
FAQ về lỗi #VALUE! và #N/A
Các câu hỏi dưới đây giải quyết nhanh những tình huống “khó chịu” nhất khi bạn đã sửa công thức nhưng lỗi vẫn quay lại.
Có nên dùng IFERROR để “ẩn hết” mọi lỗi không?
Có thể dùng cho lớp hiển thị báo cáo, nhưng không nên dùng để che lỗi ở lớp dữ liệu vì IFERROR sẽ thay mọi lỗi bằng giá trị dự phòng và làm bạn khó phát hiện nguyên nhân thật.
IFNA khác gì IFERROR khi xử lý #N/A?
IFNA chỉ xử lý riêng lỗi #N/A; IFERROR xử lý tất cả lỗi. Vì vậy, IFNA phù hợp khi bạn muốn “không tìm thấy thì trả về rỗng”, còn các lỗi khác vẫn hiện để bạn sửa.
VLOOKUP trả #N/A dù “nhìn thấy” giá trị trong bảng, vì sao?
Thường là do khác kiểu dữ liệu (text vs number) hoặc có khoảng trắng/ký tự ẩn. Hãy thử TRIM/CLEAN và chuẩn hóa khóa bằng VALUE/NUMBERVALUE hoặc TEXT trước khi tra cứu.
#VALUE! xuất hiện khi nhập dữ liệu từ file khác, xử lý thế nào nhanh?
Hãy tạo cột Clean để chuyển đổi kiểu dữ liệu (TRIM/CLEAN/VALUE/NUMBERVALUE), rồi cho công thức chính tham chiếu sang cột đó. Cách này ổn định hơn so với sửa trực tiếp trên dữ liệu thô.
Gợi ý tải Excel chính chủ: Nếu bạn cần cài/đồng bộ Excel để đảm bảo tương thích hàm và tính năng, xem trang Microsoft 365 Excel tại: https://www.microsoft.com/microsoft-365/excel