Excel bị lỗi công thức? Nguyên nhân và cách sửa tất cả các lỗi thường gặp

Làm việc với Excel, ai cũng từng ít nhất một lần gặp tình trạng excel bị lỗi công thức. Công thức không chạy, hiển thị ký tự lạ, hoặc cho ra kết quả sai lệch khiến công việc bị gián đoạn và mất thời gian. Bài viết này sẽ phân tích toàn bộ các nguyên nhân, từ lỗi cú pháp, tham chiếu, cho đến lỗi logic, đồng thời hướng dẫn bạn cách xử lý triệt để từng loại lỗi. Những kiến thức dưới đây được đúc kết từ thực tế xử lý hàng nghìn tình huống Excel trong doanh nghiệp, giúp bạn tự tin giải quyết mọi vấn đề liên quan đến công thức.

Những nguyên nhân chính khiến excel bị lỗi công thức

Hiểu rõ bản chất của lỗi là bước đầu tiên để khắc phục. Công thức Excel có thể sai vì nhiều lý do khác nhau, từ đơn giản như gõ sai tên hàm, đến phức tạp như tham chiếu vòng tròn.

• Sai cú pháp hàm: Dấu phẩy, dấu ngoặc đặt sai vị trí, thiếu đối số bắt buộc.
• Tham chiếu ô bị lỗi: Ô được tham chiếu đã bị xóa, hoặc vùng dữ liệu không hợp lệ.
• Kiểu dữ liệu không tương thích: Ví dụ, cố gắng thực hiện phép toán với ô chứa chữ.
• Tên vùng hoặc tên hàm bị sai chính tả: Excel không nhận diện được hàm nếu viết sai (ví dụ: viết “SUM” thành “SUN”).
• Định dạng ô che giấu lỗi: Đôi khi công thức đúng nhưng ô hiển thị kết quả sai do định dạng số, ngày tháng không phù hợp.
• Liên kết đến file ngoài bị đứt: Khi công thức tham chiếu đến workbook khác nhưng file đó đã bị di chuyển hoặc xóa.
• Cài đặt tính toán thủ công: Nếu Excel để chế độ Manual Calculation, công thức không tự động cập nhật.

Các loại lỗi công thức Excel phổ biến và cách xử lý



Excel có một số mã lỗi tiêu chuẩn hiển thị ngay trong ô. Nhận biết đúng mã lỗi giúp bạn xác định nguyên nhân nhanh chóng.

Lỗi #VALUE!

Lỗi này xuất hiện khi công thức gặp kiểu dữ liệu không phù hợp. Ví dụ, bạn dùng hàm SUM với một ô có chứa chữ “abc”, hoặc cố tình nhân một ô chứa văn bản với số.

Nguyên nhân phổ biến:

• Đối số của hàm là ô chứa văn bản nhưng hàm yêu cầu số.
• Toán tử số học (+, -, *, /) áp dụng cho ô chứa văn bản.
• Hàm ngày tháng nhận dữ liệu không hợp lệ.

Cách xử lý: Kiểm tra từng ô trong công thức bằng cách nhấn F2 (Edit mode) và xem Highlight Range. Chuyển các ô chứa văn bản sang số (nếu có thể) bằng cách nhân với 1 hoặc dùng hàm VALUE. Sử dụng hàm IFERROR để che giấu lỗi tạm thời khi cần.

Lỗi #REF!

Lỗi tham chiếu này xảy ra khi công thức tham chiếu đến một ô hoặc vùng đã bị xóa, bị dán đè, hoặc không tồn tại. Đây là lỗi phổ biến sau khi xóa hàng, cột hoặc sheet.

Ví dụ: Công thức =A1+B1, sau đó bạn xóa cột B. Kết quả sẽ thành =A1+#REF!.

Cách xử lý: Không thể phục hồi tham chiếu đã mất trừ khi bạn Undo (Ctrl+Z) ngay lập tức. Cách tốt nhất là kiểm tra lại công thức và tham chiếu đến đúng ô. Nếu file đã lưu, bạn phải sửa thủ công từng công thức. Sử dụng Find & Replace để tìm tất cả #REF! và thay thế bằng giá trị đúng.

Lỗi #DIV/0!

Lỗi chia cho 0 hoặc chia cho ô trống. Công thức =A1/B1 sẽ báo lỗi nếu B1 bằng 0 hoặc không có dữ liệu.

Cách xử lý: Kiểm tra giá trị mẫu số. Sử dụng hàm IF để kiểm tra điều kiện trước khi chia, ví dụ: =IF(B1=0,0,A1/B1). Hoặc dùng IFERROR để hiển thị thông báo thân thiện hơn.

Lỗi #NAME?

Lỗi này xuất hiện khi Excel không nhận diện được tên hàm, tên vùng, hoặc chuỗi văn bản không được đặt trong dấu ngoặc kép.

Nguyên nhân:

• Gõ sai tên hàm, ví dụ =SUMM(A1:A10) thay vì =SUM.
• Thiếu dấu nháy đơn khi tham chiếu tên sheet có khoảng trắng.
• Quên đặt dấu ngoặc kép cho chuỗi văn bản, ví dụ =IF(A1=Đúng,1,0) thiếu dấu nháy.

Cách xử lý: Kiểm tra chính tả hàm, sử dụng Formula AutoComplete (gõ vài ký tự đầu, Excel gợi ý). Đảm bảo tất cả tên sheet có khoảng trắng đều được đặt trong dấu nháy đơn. Với chuỗi văn bản, luôn dùng dấu ngoặc kép (“”).

Lỗi #N/A

Lỗi “Not Available” thường gặp khi dùng các hàm tìm kiếm như VLOOKUP, HLOOKUP, MATCH, XLOOKUP không tìm thấy giá trị cần tra cứu.

Nguyên nhân: Giá trị lookup không tồn tại trong bảng tra, hoặc bảng tra không được sắp xếp đúng (với VLOOKUP range_lookup = TRUE).

Cách xử lý: Kiểm tra lại dữ liệu nguồn. Sử dụng hàm IFERROR hoặc IFNA để xử lý lỗi, ví dụ: =IFNA(VLOOKUP(A1,Sheet2!A:C,3,0),”Không tìm thấy”). Đảm bảo cột đầu tiên trong bảng tra khớp với giá trị lookup.

Lỗi #NULL!

Lỗi này ít gặp, xảy ra khi bạn dùng toán tử giao (dấu cách) giữa hai vùng mà chúng không giao nhau. Ví dụ: =SUM(A1:A10 C1:C10) – nếu hai dải ô không có điểm chung, Excel báo lỗi NULL.

Cách xử lý: Kiểm tra lại dấu phẩy hoặc dấu cách trong công thức. Phần lớn trường hợp bạn nên dùng dấu phẩy để liệt kê các vùng riêng biệt, thay vì dấu cách (toán tử giao).

Lỗi #####

Khi ô hiển thị toàn dấu thăng (####), nhiều người nghĩ đó là lỗi công thức, nhưng thực chất là do cột quá hẹp để hiển thị nội dung số hoặc ngày tháng.

Cách xử lý: Kéo rộng cột hoặc double-click vào đường biên cột để tự động điều chỉnh. Nếu vẫn không hết, có thể do số âm mà định dạng là ngày tháng – hãy kiểm tra lại định dạng ô.

Lỗi Circular Reference (Tham chiếu vòng tròn)

Đây là lỗi đặc biệt, xuất hiện khi công thức tham chiếu đến chính ô chứa công thức đó, tạo vòng lặp vô tận. Excel hiển thị cảnh báo trên thanh trạng thái và thường yêu cầu bạn sửa.

Nguyên nhân: Vô tình nhập =A1+1 vào chính ô A1, hoặc công thức dây chuyền A1=B1, B1=C1, C1=A1.

Cách xử lý: Vào Tab Formulas > Error Checking > Circular References. Excel sẽ chỉ ra ô gây lỗi. Bạn cần sửa lại logic công thức để loại bỏ vòng lặp. Trong một số trường hợp đặc biệt (tính lặp), Các đối số có đúng kiểu dữ liệu không? Dùng phím F9 để tính thử từng phần của công thức (chọn phần muốn tính và nhấn F9).

• Sử dụng Evaluate Formula: Vào Formulas > Evaluate Formula. Tính từng bước để thấy chính xác Excel xử lý ra sao.
• Xem Error Checking: Tab Formulas > Error Checking > Trace Error cho thấy đường dẫn tham chiếu.
• Kiểm tra định dạng ô: Nếu công thức đúng nhưng kết quả hiển thị lạ, hãy vào Format Cells (Ctrl+1) để xem ô có bị định dạng Text hay không.
• Kiểm tra cài đặt tính toán: Vào Formulas > Calculation Options, đảm bảo là Automatic. Nếu đang Manual, nhấn F9 để tính lại.

Sử dụng các công cụ hỗ trợ sửa lỗi trong Excel

Bên cạnh kiểm tra thủ công, Excel tích hợp sẵn một số công cụ mạnh mẽ để tìm và xử lý lỗi.

Ngoài ra,

Có thể do chế độ Calculation được đặt thành Manual. Vào Formulas > Calculation Options > Automatic. Cũng kiểm tra xem ô có định dạng Text không – nhấn Ctrl+1, nếu là Text, đổi sang Number rồi enter lại công thức.

Lỗi #VALUE! xuất hiện khi cộng ô có chứa số nhưng có khoảng trắng?

Đúng. Khoảng trắng thừa khiến ô trở thành văn bản. Hàm SUM bỏ qua văn bản nhưng nếu bạn dùng dấu cộng (+), nó sẽ báo lỗi. Sử dụng hàm TRIM để loại bỏ khoảng trắng, hoặc dùng SUM thay vì toán tử.

Làm sao để tìm tất cả ô có lỗi trong một sheet?

Nhấn F5 hoặc Ctrl+G, chọn Special, chọn Formulas và tích Errors. Excel sẽ chọn tất cả ô lỗi.

Rất khó để phục hồi hoàn toàn. Cách duy nhất là mở bản sao lưu hoặc phiên bản trước của file (Right-click file > Properties > Previous Versions). Hoặc sửa từng công thức bằng tay. Đó là lý do cần thận trọng khi xóa cột/hàng.

Lỗi ##### có phải là lỗi công thức không?

Không. Đây chỉ là lỗi hiển thị do cột quá hẹp. Kéo rộng cột hoặc giảm cỡ chữ. Nếu vẫn còn, kiểm tra định dạng ngày tháng có thể không phù hợp với giá trị âm.

Kết luận

Excel bị lỗi công thức là vấn đề phổ biến nhưng hoàn toàn có thể kiểm soát nếu bạn hiểu rõ bản chất từng loại lỗi và áp dụng quy trình kiểm tra có hệ thống. Từ lỗi cú pháp đơn giản như #NAME? cho đến lỗi tham chiếu vòng tròn phức tạp, mỗi lỗi đều có cách xử lý riêng. Quan trọng nhất là không vội vàng che giấu lỗi bằng IFERROR, mà phải truy tìm nguyên nhân gốc rễ. Với những hướng dẫn chi tiết và các công cụ hỗ trợ sẵn có trong Excel, bạn hoàn toàn có thể tự mình sửa lỗi và ngăn ngừa chúng tái diễn. Hãy biến việc xử lý lỗi thành cơ hội để hiểu sâu hơn về cách Excel vận hành, từ đó nâng cao hiệu suất và độ chính xác trong công việc hàng ngày.