Lỗi #REF!, Tham Chiếu Vòng Và Dấu @ Trong Công Thức Excel

Ba lỗi dưới đây có chung một đặc điểm: công thức nhìn thì đúng nhưng Excel vẫn không chạy, hoặc chạy ra kết quả lạ. Hiểu nguyên nhân sẽ nhanh hơn nhiều so với thử sửa mò.

Lỗi #REF! - tham chiếu không còn tồn tại

Lỗi này nghĩa là công thức đang trỏ tới một ô đã bị xóa. Nguyên nhân gần như luôn là bạn hoặc ai đó xóa dòng, xóa cột, hoặc xóa cả sheet mà công thức đang tham chiếu.

Điểm khó chịu: Excel thay luôn địa chỉ ô bằng chữ #REF! trong công thức, nên bạn không biết trước đó nó trỏ tới đâu.

Cách xử lý:

• Nếu vừa xóa nhầm, bấm Ctrl+Z ngay để hoàn tác. Đây là cách duy nhất lấy lại được tham chiếu gốc.
• Nếu đã lưu và đóng file, phải sửa tay từng công thức. Dùng Ctrl+F tìm chuỗi #REF! để rà hết.
• Phòng ngừa: trước khi xóa dòng hay cột, bấm Ctrl+[ trên ô công thức để xem nó đang trỏ tới đâu.

Lỗi tham chiếu vòng - Circular Reference

Xảy ra khi một công thức tham chiếu tới chính ô chứa nó, trực tiếp hoặc qua một chuỗi ô khác. Ví dụ ô C5 chứa công thức có cộng cả C5 vào.

Excel hiện cảnh báo ở thanh trạng thái phía dưới, kèm địa chỉ ô gây lỗi. Nếu không thấy địa chỉ, vào Formulas → Error Checking → Circular References, danh sách các ô có vấn đề nằm ở đó.

Ba nguyên nhân thường gặp:

• Chọn nhầm vùng khi dùng SUM. Kéo vùng cộng bao luôn cả ô chứa công thức.
• Chuỗi tham chiếu vòng qua nhiều ô. A trỏ tới B, B trỏ tới C, C trỏ ngược về A. Loại này khó tìm hơn, phải lần theo từng ô bằng Trace Precedents.
• Công thức tính chiết khấu hoặc thuế dựa trên kết quả cuối. Đây là bài toán thật sự cần lặp, không phải lỗi.

Với trường hợp thứ ba, bật tính toán lặp tại File → Options → Formulas → Enable iterative calculation. Chỉ bật khi bạn biết rõ mình đang làm gì, vì tính năng này che mọi cảnh báo tham chiếu vòng khác trong file.

Dấu @ tự xuất hiện trong công thức

Bạn mở một file và thấy công thức biến thành dạng =@SUM(A1:A10) hoặc =SUM(@A1:A10) dù bạn không gõ dấu @ nào.

Đây không phải lỗi gõ nhầm. Dấu @ là cách Excel bản mới đánh dấu rằng công thức này đang chạy theo kiểu cũ, tức là chỉ lấy một giá trị thay vì cả mảng.

Nó xuất hiện khi file được tạo trên bản Excel không có mảng động rồi mở bằng bản có mảng động, hoặc ngược lại. Hai phiên bản hiểu khác nhau về việc một công thức nên trả về một giá trị hay nhiều giá trị, và dấu @ là cách Excel giữ nguyên hành vi cũ để kết quả không đổi.

Có cần xóa không? Thường là không. Công thức vẫn cho kết quả đúng như file gốc. Chỉ xóa khi bạn cố ý muốn công thức trả về cả mảng, khi đó bỏ dấu @ đi và chấp nhận kết quả sẽ tràn ra nhiều ô, xem mảng động và lỗi #SPILL.

Lỗi #NAME? khi mở file từ máy khác

Liên quan trực tiếp tới vấn đề phiên bản ở trên. Nếu file dùng các hàm chỉ có ở bản mới mà máy bạn dùng bản cũ, mọi ô chứa hàm đó sẽ hiện #NAME?.

Các hàm hay gây ra tình huống này: XLOOKUP, FILTER, UNIQUE, SORT, LET trên máy dùng Office 2016 hoặc 2019; TEXTJOIN, CONCAT, IFS trên máy dùng Office 2016.

Không có cách thêm hàm vào bản cũ. Hai hướng xử lý: người gửi dán kết quả dưới dạng giá trị trước khi gửi, hoặc thống nhất phiên bản trong nhóm. Bản có đủ nhóm hàm mảng động thấp nhất là key Office 2021, đối chiếu chi tiết tại so sánh Office 2016, 2019, 2021 và 2024.

Công cụ dò lỗi có sẵn trong Excel

Ba công cụ ở tab Formulas mà ít người dùng:

• Trace Precedents - vẽ mũi tên chỉ ra ô nào đang cấp dữ liệu cho công thức này.
• Trace Dependents - ngược lại, chỉ ra ô nào đang phụ thuộc vào ô này. Dùng trước khi xóa dòng để tránh tạo lỗi #REF!.
• Evaluate Formula - chạy công thức từng bước một, xem giá trị trung gian ở mỗi bước. Đây là công cụ hiệu quả nhất khi công thức dài mà kết quả sai không rõ lý do.

Câu hỏi thường gặp

Lỗi #REF! có tự sửa được không?

Không. Excel không lưu lại thông tin tham chiếu cũ nên phải sửa tay.

Tham chiếu vòng có làm sai số liệu không?

Có. Excel trả về 0 cho ô đó, kéo theo mọi công thức phụ thuộc đều sai.

Xóa hết dấu @ trong file bằng cách nào?

Dùng Ctrl+H thay thế chuỗi @ trong công thức, nhưng hãy sao lưu file trước vì kết quả có thể thay đổi.