Nguyên nhân và cách khắc phục lỗi #REF! trong EXCEL

Cập nhật ngày 10, Tháng Ba, 2020, Thuộc Office, Thủ thuật Excel, Tin học văn phòng

Tổng quan nguyên nhân lỗi #REF!

REF là tên viết tắt của từ Reference, tức là Tham chiếu. Do đó trong mọi trường hợp xuất hiện lỗi này đều hiểu là lỗi tham chiếu. Tức là trong công thức có tham chiếu đến một giá trị không hợp lệ hoặc không tồn tại. Thường do các nguyên nhân chủ yếu sau đây:

  • Đối tượng tham chiếu đến đã bị xóa (thường do xóa cột hoặc hàng chứa tham chiếu đó) làm cho công thức không thể tham chiếu đến địa chỉ theo công thức.
  • Đối tượng cần tham chiếu không có trong vùng tham chiếu (thường gặp trong các hàm dò tìm)
  • Tham chiếu đến một file đã bị đóng nên công thức không thể tham chiếu đến được

Sau đây Trungtamtinhoc.edu.vn sẽ giới thiệu từng trường hợp cụ thể.

nguyen-nhan-va-cach-khac-phuc-loi-ref-trong-excel

Đối tượng tham chiếu đến đã bị xóa

Ví dụ: công thức =SUM(A2,B2,C2,D2)

sum

Khi chọn cột D và nhấn xóa cột thì công thức sẽ trở thành =SUM(A2,B2,C2,#REF!) và kết quả sẽ trả về lỗi #REF!

hàm sum

Cách khắc phục

  • Nhấn nút hoàn tác trên thanh công cụ hoặc ấn tổ hợp Ctrl + Z để quay lại bước vừa xóa cột hoặc hàng.
  • Trong công thức tính tổng như trên bạn không nên sử dụng kiểu ghi lẻ từng ô mà bạn hãy chọn vùng tính =SUM(A2:D2) như vậy dù bạn có xóa cột hoặc hàng thì công thức vẫn hiểu và tính đúng
sum
  • Nếu bạn phát hiện muộn và không thể sửa lại được nữa thì hãy đóng file và không lưu.

Đối tượng cần tham chiếu không có trong vùng tham chiếu

Trong các hàm dò tìm như hàm VLOOKUP, HLOOKUP, INDEX nếu bạn lấy tham chiếu vượt ngoài giới hạn vùng dò tìm thì sẽ trả về lỗi #REF!

Ví dụ: bài toán sau đây sử dụng hàm VLOOKUP để điền số Thuế của từng sản phẩm dựa trên bảng thuế cho sẵn.

Như vậy bảng thuế cho sẵn là bảng dò tìm có 2 cột và sẽ tham chiếu đến đây. Ta nhập công thức =VLOOKUP(B2,$E$10:$F$13,3,0) nghĩa là tham chiếu đến cột thứ 3 trong bảng tham chiếu E10:F13 vì bảng tham chiếu này không có cột số 3 nên kết quả trả về sẽ là lỗi #REF!

Vlookup

*Lưu ý: dấu $ là để cố định hàng và/hoặc cột, trong trường hợp dùng chung một bảng tham chiếu cho tất các các ô tính thì ta cần phải cố định hàng và cột của bảng tham chiếu để khi coppy công thức đến các ô cần tính khác sẽ không bị chạy bảng tham chiếu.

Cách khắc phục: điều chỉnh lại số cột/hàng tham chiếu đến sao cho nằm trong phạm vi bảng tham chiếu. Như ví dụ trên công thức sửa lại sẽ là =VLOOKUP(B2,$E$10:$F$13,2,0)

vlookup

Tương tự đối với các hàm dò tìm khác.

Tham chiếu đến một file đã bị đóng

Thường xảy ra khi sử dụng hàm INDIRECT để tham chiếu tới 1 đối tượng bên ngoài file đang làm việc và file được tham chiếu hiện tại đang đóng.

Cách khắc phục: mở file được tham chiếu hoặc thay đổi phương pháp tham chiếu (không sử dụng hàm INDIRECT mà thay bằng hàm khác ít gặp lỗi hơn).

Trên đây là bài viết Trungtamtinhoc.edu.vn về nguyên nhân và cách khắc phục lỗi #REF! trong EXCEL, hy vọng sẽ giúp ích được nhiều cho quý bạn đọc




Click Xem thêm: , , , , ,