Chuyện Gì Vừa Xảy Ra
Bạn vừa xóa một hàng hoặc cột, và giờ đây một số ô hiển thị #REF! thay vì con số. Excel đang báo cho bạn biết rằng một công thức đang trỏ đến một ô không còn tồn tại. Thông báo lỗi đầy đủ là:
#REF! - The formula contains invalid cell references
Tin tốt là: hầu như lúc nào cũng có thể khắc phục trong chưa đầy năm phút khi bạn biết cần tìm ở đâu.
Nguyên Nhân Lỗi
Excel theo dõi các tham chiếu công thức theo vị trí. Bạn viết =A1+B1, xóa toàn bộ cột A, và Excel không thể định tuyến lại tham chiếu đó — nó chỉ đóng dấu #REF! vào chỗ đó, để lại cho bạn kết quả =#REF!+B1.
Các nguyên nhân phổ biến nhất:
- Xóa một hàng hoặc cột mà công thức đang tham chiếu trực tiếp
- Cắt và dán các ô vào vùng mà các công thức khác phụ thuộc vào
- Dùng
INDIRECT()với địa chỉ cố định không còn tồn tại - Sao chép công thức sang vị trí mà các tham chiếu tương đối vượt ra ngoài phạm vi (ví dụ: hàng -1)
Bước 1 — Tìm Tất Cả Ô Có Lỗi #REF!
Đừng tìm thủ công. Tính năng tìm kiếm tích hợp của Excel sẽ định vị mọi công thức bị lỗi chỉ trong vài giây.
- Nhấn Ctrl+F để mở hộp thoại Tìm kiếm.
- Nhập
#REF!vào ô tìm kiếm. - Nhấp Find All. Excel liệt kê mọi ô bị ảnh hưởng ở cuối hộp thoại.
- Nhấp vào bất kỳ kết quả nào để nhảy đến ô đó. Giữ Ctrl và nhấp vào nhiều kết quả để chọn tất cả cùng lúc.
Muốn dùng hoàn toàn bằng bàn phím? Hãy dùng Go To Special:
- Nhấn Ctrl+G → nhấp Special.
- Chọn Formulas → bỏ tích tất cả trừ Errors → nhấp OK.
Mọi ô có lỗi sẽ được tô sáng.
Bước 2 — Đọc Công Thức Bị Lỗi
Nhấp vào ô có #REF! và nhìn lên thanh công thức. Bạn sẽ thấy nội dung tương tự như:
=SUM(A2:#REF!)
hoặc
=VLOOKUP(D2,#REF!,2,FALSE)
Chuỗi #REF! đánh dấu chính xác phần nào bị lỗi. Điều đó cho bạn biết cái gì đã bị xóa — và cần tìm thay thế ở đâu.
Bước 3 — Hoàn Tác Nếu Có Thể
Vừa xóa nhầm? Nhấn Ctrl+Z ngay lập tức. Đây là cách nhanh nhất để khôi phục. Chỉ cần đảm bảo bạn chưa lưu file — một khi đã lưu, lịch sử hoàn tác sẽ mất. Sau khi khôi phục, hãy truy vết sự phụ thuộc để bạn có thể cấu trúc lại công thức trước khi thực hiện việc xóa một lần nữa.
Bước 4 — Sửa Tham Chiếu Thủ Công
Không thể hoàn tác? Có thể file đã được lưu, hoặc việc xóa là có chủ ý. Dù thế nào, bạn cũng cần tự trỏ lại công thức.
Ví dụ: Bạn đã xóa cột B, vốn chứa 49 hàng dữ liệu doanh thu hàng tháng. Một công thức ở cột E trước đây là:
=SUM(B2:B50)
giờ hiển thị:
=SUM(#REF!)
Dữ liệu doanh thu đã dịch sang cột C sau khi xóa. Hãy sửa lại:
=SUM(C2:C50)
Nhấp vào ô, chỉnh sửa trực tiếp trên thanh công thức, thay #REF! bằng vùng đúng, rồi nhấn Enter.
Bước 5 — Sửa Nhiều Ô Bằng Find & Replace
Thay thế hàng loạt hoạt động tốt khi tất cả các tham chiếu bị lỗi đều cần trỏ đến cùng một vùng mới. Nếu các công thức trên 30 ô đều mất cùng một cột, hãy sửa tất cả trong một lần:
- Nhấn Ctrl+H để mở Find & Replace.
- Trong ô Find what, nhập phần bị lỗi — ví dụ:
#REF!hoặcSheet2!#REF!. - Trong ô Replace with, nhập tham chiếu đúng — ví dụ:
C2:C50. - Nhấp Replace All.
Lưu ý: Mỗi công thức cần bị lỗi theo cùng một cách thì phương pháp này mới hoạt động chính xác. Khi các tham chiếu khác nhau từng ô, hãy sửa từng ô một.
Bước 6 — Xử Lý Trường Hợp Đặc Biệt Với INDIRECT()
INDIRECT() xây dựng tham chiếu từ chuỗi văn bản, điều đó có nghĩa là nó hoàn toàn bỏ qua cơ chế tự cập nhật của Excel. Xem công thức này:
=INDIRECT("Sheet2!B" & ROW())
Xóa cột B trên Sheet2 và Excel hoàn toàn không biết rằng chuỗi "Sheet2!B" đã lỗi thời. Không có cảnh báo #REF! nào xuất hiện — công thức chỉ âm thầm trả về dữ liệu sai hoặc lỗi khi chạy. Bạn phải tự cập nhật chuỗi thủ công:
=INDIRECT("Sheet2!C" & ROW())
Đây chính xác là lý do tại sao INDIRECT() nên là phương án cuối cùng — các phụ thuộc ẩn của nó rất dễ bị phá vỡ và khó phát hiện.
Các Bước Kiểm Tra
Sau khi áp dụng các bản sửa lỗi, hãy kiểm tra nhanh:
- Nhấn Ctrl+F, tìm
#REF!— bạn muốn thấy kết quả "No matches found". - Kiểm tra ngẫu nhiên các ô đã sửa để xem giá trị có đúng không. Kết quả bằng không hoặc một con số bất thường lớn có thể có nghĩa là tham chiếu đang trỏ sai chỗ.
- Với các named range: mở Formulas → Name Manager và kiểm tra cột Refers To. Bất kỳ tên nào hiển thị
#REF!cần được xóa hoặc cập nhật.
Phòng Ngừa Cho Lần Sau
Ba thói quen dưới đây sẽ giúp bạn tránh khỏi lỗi #REF! mãi mãi.
Dùng Named Range Thay Vì Địa Chỉ Ô
Đặt tên cho vùng dữ liệu của bạn trong Formulas → Define Name, sau đó viết:
=SUM(SalesData)
thay vì:
=SUM(B2:B50)
Sắp xếp lại toàn bộ trang tính và chỉ cần cập nhật một thứ duy nhất — định nghĩa named range. Mọi công thức sử dụng nó sẽ tự động điều chỉnh theo.
Dùng Tables (Ctrl+T)
Chuyển đổi vùng dữ liệu của bạn thành Excel Table. Các tham chiếu cột trong Table như [@[Sales]] sẽ đi theo dữ liệu dù bạn di chuyển chúng đến đâu.
=SUM(Table1[Sales])
Với dữ liệu có cấu trúc và các cột nhất quán, đây là phương pháp đáng tin cậy nhất hiện có.
Kiểm Tra Các Phụ Thuộc Trước Khi Xóa
Trước khi xóa bất kỳ cột hoặc hàng nào, hãy nhấp vào nó và vào Formulas → Trace Dependents. Excel sẽ vẽ các mũi tên màu xanh đến mọi công thức đang tham chiếu đến nó. Nếu có các phụ thuộc? Hãy cấu trúc lại các công thức đó trước, rồi mới xóa.
Những Điều Cần Nhớ
#REF!luôn có nghĩa là Excel đã mất dấu thứ gì đó bị xóa hoặc vượt ra ngoài phạm vi. Thanh công thức cho bạn thấy chính xác phần nào bị lỗi.- Ctrl+Z ngay sau khi xóa là cách thoát nhanh nhất — hãy dùng trước khi lưu file.
- Các địa chỉ cố định như
B2:B50rất dễ vỡ. Excel Tables và Named Ranges mới là giải pháp lâu dài. INDIRECT()là cái khó lường nhất — nó không cảnh báo bạn cho đến khi có sự cố thực sự xảy ra khi chạy.