Nếu đã từng dùng phần mềm Excel chắc hẳn bạn đã từng nhìn thấy lỗi REF trong Excel, đây là lỗi khá phổ biến trong Excel và thường mất nhiều thời gian cũng như công sức để khắc phục và xử lý nó. Vây nên, ở bài viết này chúng tôi sẽ hướng dẫn các bạn cách sửa lỗi REF trong Excel nhanh chóng và đơn giản nhất.
Mục Lục
Hướng dẫn cách khắc phục lỗi REF trong Excel
Lỗi REF! nghĩa là gì?
REF là từ viết tắt của REFERENCE, nghĩa Tiếng Việt là Tham chiếu. Do nên, mọi ô tính hiển thị lỗi này, có nghĩa là bạn đang mắc phải lỗi tham chiếu.
Nguyên nhân: Lỗi #REF!
- Tham chiếu tới 1 file Excel khác (bảng tính khác) mà file đó chưa được mở nên lỗi không tham chiếu được.
- Đối tượng cần tham chiếu không có trong vùng tham chiếu.
- Đối tượng cần tham chiếu bị mất, do bạn đã thực hiện xóa cột, hàng chứa đối tượng đó, hay do bị ghi đè bởi lệnh Copy/Paste (Sao chép/ Dán)
Tìm hiểu thêm: Lỗi protected view trong excel
Lỗi REF trong Excel xảy ra do việc xóa cột
Thông thường, việc xóa cột hoặc xóa hàng sẽ khiến cho tham chiếu đến cột hoặc hàng đó bị mất.
Giải pháp:
- Nếu bạn vô tình xóa cột không mong muốn, bạn có thể khôi phục lại bằng cách nhấn tổ hợp phím Ctrl+Z hoặc nhấn vào biểu tượng Hoàn tác trên thanh công cụ.
- Ưu tiên sử dụng tham chiếu dải ô thay vì chọn từng ô riêng lẻ. Chẳng hạn bạn có thể thay hàm trên bằng =SUM(B2:D2). Khi đó, kể cả khi bạn xóa một cột bất kỳ trong khoảng tham chiếu, Excel cũng sẽ tự động điều chỉnh công thức cho phù hợp.
Khi tham chiếu không có sẵn hay vượt quá phạm vi
Nguyên nhân: Trong một số công thức hàm của Excel buộc bạn phải điền các tham số như col_index_num, row_index_num. Việc điền quá phạm vi tham chiếu của các đối tượng này cũng sẽ làm lỗi #REF! xuất hiện
Xem thêm: Lỗi N/A trong Excel
Cách khắc phục:
- 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ự thay đổi khi bảng tham chiếu thay đổi). Ví dụ: Sử dụng hàm MATCH để dùng cho row_index_num, dùng hàm COLUMN để dùng cho col_index_num.
- Thực hiện sửa lỗi bằng tay khi bạn không biết sử dụng các hàm trên.
Khi tham chiếu tới file Excel đang đóng
Nguyên nhân: Bạn sử dụng hàm để tham chiếu tới 1 đố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 (file Excel) đượ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)
Cách Xóa Lỗi #REF trong Excel
Để xóa lỗi #REF khỏi bảng tính, bạn có thể sử dụng chức năng Find và Replace. Sử dụng phím tắt Ctrl + H để mở hộp thoại, sau đó nhập #REF! vào khung Find và khung Replace để trống.
Bạn có thể thực hiện các thay đổi với Find và Replace, hoặc sử dụng Replace All để thay thế tất cả các lỗi #REF cùng lúc.
Lỗi REF Với Hàm VLOOKUP
Lỗi #REF! cũng xảy ra với hàm VLOOKUP, khi một cột được chỉ định không chính xác. Trong ảnh màn hình dưới đây, hàm VLOOKUP trả về lỗi #REF! vì không có cột 3 trong phạm vi (phạm vi là B3:C7):
Khi chỉ mục cột được đặt thành giá trị chính xác là 2 thì lỗi #REF! sẽ không còn nữa, hàm VLOOKUP sẽ hoạt động đúng cách:
Lưu ý: nếu 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.
Lưu ý: bạn cũng có thể thấy lỗi #REF với hàm INDEX khi tham chiếu hàng hoặc cột khôg hợp lệ.
Bẫy Lỗi #REF! Với Hàm IFERROR
Trong hầu hết các trường hợp, việc bẫy lỗi #REF! trong một công thức không có ý nghĩa gì, vì lỗi #REF! không phải là lỗi quan trọng. Tuy nhiên, nếu muốn bạn cũng có thể sử dụng hàm IFERROR để bắt lỗi #REF trong trường hợp nếu đang xây dựng các tham chiếu động với hàm INDIRECT.
Bài viết đã giúp bạn tìm hiểu nguyên nhân gây ra lỗi REF trong Excel cũng như giải pháp khắc phục hiệu quả. Mong rằng bạn sẽ thực hiện thành công.