Hướng dẫn cách sửa lỗi #Ref! trong excel

Nếu bạn nào thường xuyên làm việc với Excel, thì ít nhất các bạn cũng từng bắt gặp các lỗi xảy ra, khi thực hiện các lệnh trong Excel chẳng hạn như #Value, #Rep!, #N/A…Hôm nay, Tinvanphong.com sẽ giới thiệu đến các bạn cách sửa lỗi ref trong excel. Lỗi #REF là thông báo Excel hiển thị khi một công thức tham chiếu đến một ô không không hợp lệ. Điều này xảy ra thường xuyên nhất khi các ô được tham chiếu bởi công thức bị xóa hoặc bị dán lại.

Hướng dẫn cách sửa lỗi Ref trong excel

I.Ý nghĩa của lỗi #REF! trong Excel

1.Ý nghĩa

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.

2.Một số nguyên nhân chủ yếu gây ra lỗi #REF!

1. Tham chiếu tới 1 file đang chưa được mở nên không tham chiếu được.
2. Hàm đang cố gắng tham chiếu đến các ô không tồn tại.
3. Đối tượng cần tham chiếu bị mất bởi việc xóa cột, xóa hàng chứa đối tượng đó, do bị ghi đè bởi việc copy paste (Sao chép/ Dán).

II.Lỗi #REF! xảy ra do việc xóa cột

Ví dụ sau, sử dụng công thức = SUM (B2,C2,D2) trong cột E.

Nếu bạn xóa bất kì cột nào trong bảng dữ liệu, ví dụ như chúng ta cố tình xóa cột C thì công thức lúc này sẽ là  = SUM (B2, # REF !, C2), nó sẽ gây ra lỗi #REF!. Khi bạn sử dụng tham chiếu ô rõ ràng như thế này (trong đó bạn tham chiếu từng ô riêng lẻ, được phân tách bằng dấu phẩy) và xóa một hàng hoặc cột được tham chiếu, Excel không thể giải quyết vấn đề đó, vì vậy nó trả về lỗi #REF! lỗi. Đây là lý do chính tại sao việc sử dụng tham chiếu ô, rõ ràng trong các hàm không được khuyến khích.

Xử lí lỗi #REF! bằng 1 trong những cách như sau.

-Nếu các bạn vô tình xóa các hàng hoặc cột, các bạn có thể nhấp ngay vào nút hoàn tác trên Thanh công cụ hoặc nhấn tổ hợp phím CTRL + Z để khôi phục chúng.

-Điều chỉnh công thức để công thức sử dụng tham chiếu phạm vi thay vì các ô riêng lẻ, như = SUM (B2:D2). Bây giờ, các bạn có thể xóa bất kỳ cột nào trong phạm vi tổng và Excel sẽ tự động điều chỉnh công thức.

-Nếu phát hiện ra lỗi này, bạn nên tắt file Excel và không lưu file, sau đó mới mở lại file.

II.Lỗi #REF!  tham chiếu dải ô không chính xác

Hàm VLOOKUP, giúp tìm kiếm dữ liệu theo hàng dọc và trả về giá trị tương ứng theo hàng ngang trong một bảng.

Trường hợp các bạn sử dụng hàm VLOOKUP,  với số thứ tự của cột lấy dữ liệu vượt quá số cột hiện có của bảng tham chiếu thì sẽ gây ra lỗi #REF!.

Chúng ta có ví dụ sau đây.

Trong bảng trên, chúng ta thấy vùng A2:E4 chỉ có 5 cột, trong khi đó đối tượng Col_index_num trong hàm vlookup, lại tính theo giá trị cột thứ 6, vượt quá phạm vi của vùng dữ liệu cần tham chiếu. Vì vậy mà nó trả về kết quả là lỗi #REF!.

Cách xử lí như sau.

-Sử dụng công thức tại tham số col_index_num hoặc row_index_num trong các công thức tra cứu, tham chiếu để tránh lỗi khi nhập bằng tay, kết quả công thức sẽ tự động thay đổi khi bảng tham chiếu thay đổi.

-Thực hiện sửa lỗi bằng tay, khi các bạn không biết sử dụng các hàm trên.

Lưu ý: nếu các bạn sử dụng hàm INDEX, tham chiếu hàng hoặc cột không hợp lệ, nó cũng trả về lỗi #REF!.

III.Lỗi #REF! xảy ra khi tham chiếu tới file Excel đang đóng

Nguyên nhân: Khi các bạn sử dụng hàm, để tham chiếu tới một đối tượng bên ngoài bảng tính, đang làm việc và file Excel chứa đối tượng đó đang đóng.

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

Với bài viết này, Tinvanphong.com hy vọng các bạn đã nắm được cách sửa lỗi REF trong Excel. Hy vọng là các bạn sẽ tránh được việc xảy ra lỗi này, và nếu có gặp phải lỗi này thì các bạn cũng có thể biết cách sửa được nó. Chúc các bạn thành công nhé.

Tham khảo thêm các bài viết sau

–Hướng dẫn sử dụng hàm Transpose trong Excel

–Cách sử dụng hàm iserror trong excel

–Hướng dẫn sử dụng hàm average trong excel