Tìm hiểu lỗi REF trong Excel là gì? Cách sửa lỗi #REF trong Excel?

“Lỗi REF trong Excel là gì? Cách sửa lỗi #REF trong Excel ra sao?” là những câu hỏi trăn trở của nhiều người khi dùng Excel để tính toán, tìm kiếm dữ liệu. Bạn không biết cách khắc phục? Đừng gấp gáp mà bỏ qua bài thủ thuật sau đây của FASTCARE nhé!

Đối với một công cụ mong cầu sự chính xác đến từng dấu chấm phẩy như Excel việc gặp lỗi trong quá trình thực hiện chắc chắn sẽ chẳng thể tránh khỏi. Và một trong những lỗi phổ biến mà bạn có thể vô tình gặp rắc rối phải kể đến là lỗi #REF!

Và dù gặp hay chưa, cách tốt nhất hãy tìm hiểu và ghi nhớ “lỗi REF trong Excel là gì"? Cách sửa lỗi #REF trong Excel như thế nào?” để không cần tốn quá nhiều thời gian cũng như công sức khắc phục bạn nhé!

Có thể bạn cần:

• Học ngay cách chuyển đổi từ file Excel sang Word dễ làm nhất
• Hướng dẫn cách chèn chữ ký vào Excel đơn giản, làm được ngay

Giải thích lỗi REF trong Excel là gì?

Trước hết bạn cần biết REF chính là viết tắt của từ tiếng Anh “Reference”, dịch ra chính là Tham chiếu. Từ đó thắc mắc #REF trong Excel nghĩa là gì cũng rất dễ dàng để giải thích. Đây chính là lỗi xoay quanh một “nhân vật chủ chốt” - ô tham chiếu. 

Khi công thức tham chiếu đến ô không hợp lệ, ô tham chiếu bị lỗi, Excel sẽ trả về dòng thông báo lỗi #REF để bạn kiểm tra lại các dữ liệu cho chính xác. Thông thường, lỗi #REF trong Excel dễ bắt gặp khi bạn sử dụng một số hàm phổ thông như: VLOOKUP, SUM, AVERAGE, INDEX, IFERROR,...

Cách sửa lỗi #REF trong Excel ở mọi trường hợp

Sau khi đã hiểu được lỗi #REF trong Excel là gì, cách sửa lỗi #REF trong Excel sẽ còn phụ thuộc vào từng nguyên nhân cụ thể. Ở bài viết này FASTCARE sẽ tổng hợp tới bạn 3 nguyên nhân cốt yếu gây ra tình trạng báo lỗi #REF trong Excel bao gồm:

✤ Lỗi #REF xảy ra do cột tham chiếu bị xóa, ghi đè dữ liệu.

✤ Lỗi #REF xảy ra do giá trị ở các ô tham chiếu vượt quá giá trị cho phép hoặc không có sẵn.

✤ Lỗi #REF xảy ra từ việc sao chép từ bảng dữ liệu này sang bảng dữ liệu khác.

Trường hợp 1 - Lỗi do cột tham chiếu bị ghi đè hoặc xóa mất

Nguyên nhân các cột tham chiếu bị mất hoặc ghi đè có thể xuất phát từ việc trong quá trình nhập dữ liệu bạn đã vô tình xóa mất một cột khiến công thức chứa ô tham chiếu không thể tìm được giá trị mong muốn.

Cách khắc phục lỗi #REF nhanh chóng 

Để khắc phục được lỗi #REF trong trường hợp này có 2 cách:

Cách 1: Sử dụng tổ hợp phím Ctrl + Z hoặc biểu tượng Hoàn tác trên thanh công cụ trong trường hợp bạn đã không để ý xóa cột ở thao tác trước đó.

Cách 2: Thêm cột, điền dữ liệu lại một lần nữa một cách thủ công để trả về đúng ô tham chiếu có trong hàm.

Ví dụ, với trường hợp dưới đây, mình muốn tính tổng điểm 3 môn khối A của các học sinh nên dùng hàm SUM. Đầu tiên là điểm của Loan sẽ sử dụng công thức =SUM(B2,C2,D2) đáng lý ra sẽ cho ra kết quả 24.

Tuy nhiên, không hiểu lý do vì sao mà cột D bị xóa mất nên khi chúng ta ấn Enter, Excel đã hiển thị lỗi #REF!

Lúc này bạn hãy ấn thử tổ hợp phím Ctrl + Z để xem có khôi phục lại được cột D đã mất không. Nếu thao tác trước đó không phải là xóa đi mất cột D, cách duy nhất mình sẽ phải điền lại bảng biểu giống như ban đầu để công thức tính được về đúng nguyên gốc.

Lưu ý nhỏ khi khắc phục lỗi #REF do mất ô tham chiếu 

Mặc dù có một số trang web có đề xuất sử dụng tham chiếu dải ô thay vì chọn ô riêng lẻ để dù bạn xóa 1 cột, công thức vẫn đưa ra kết quả mà không hiện lỗi #REF. Thế nhưng ở trường hợp này bạn cần cẩn trọng và xác định đúng được mục tiêu tính toán của mình.

Mình có thể lấy luôn ví dụ trên đây, thay vì chọn từng ô riêng lẻ như công thức =SUM(B2,C2,D2) mình đã sử dụng dải ô bằng công thức =SUM(B2:D2). Và dĩ nhiên dù khi chúng ta xóa đi mất cột C - Điểm Lý, kết quả vẫn cho ra kết quả con số mà không phải lỗi REF.

Lý giải cho điều này là bởi mặc dù bị mất đi 1 ô tham chiếu, nhưng với dạng dải tham chiếu trong hàm, khi tính toán Excel sẽ tự động điều chỉnh lại công thức chỉ để tính các ô có mặt tại bảng thành =SUM(B2:C2) . Vì vậy, kết quả mà bạn đang thấy lúc này thực chất chỉ là tổng điểm 2 môn Toán và Hóa mà không hề cộng cả điểm Lý.

Trường hợp 2 - Lỗi do ô tham chiếu vượt quá phạm vi hoặc không có sẵn

Trường hợp này xảy ra thường xuyên nhất đối với các hàm tìm kiếm, đặc biệt là VLOOKUP hay HLOOKUP. Khi bạn điền hàm với số lượng ô tham chiếu vượt quá phạm vi dữ liệu gốc hoặc tham chiếu vào những ô không có sẵn, gặp lỗi #REF là điều vô cùng hiển nhiên.

Chẳng hạn ở bảng dưới đây, để tìm kiếm điểm Hóa của Loan, mình đã sử dụng tới hàm VLOOKUP. Thế nhưng ở lệnh tìm kiếm, phần col_index_num mình lại nhập là 5 thành công thức =VLOOKUP("Lê Loan",A2:D6,5,1) trong khi vùng dữ liệu cần tham chiếu chỉ có tối đa 4 cột.

Để khắc phục việc Excel báo lỗi #REF trong trường hợp này, mục col_index_num bạn cần sửa lại thành 4, công thức lúc này sẽ là =VLOOKUP("Lê Loan",A2:D6,4,1) để hàm trả về kết quả chính xác là 7.

Trường hợp 3 - Lỗi do tham chiếu tới 1 file đang trong trạng thái đóng

Xét về tần suất xuất hiện, tình trạng lỗi trong trường hợp này thường xảy ra khá ít và chủ yếu gặp phải khi bạn sử dụng tới hàm Indirect. Đây là một hàm có độ khó cao, yêu cầu người dùng cần phải hiểu rõ được cách thức hoạt động mới có thể sử dụng mượt mà bởi nó là hàm lệnh tham chiếu tới 1 đối tượng khác bên ngoài Workbook.

Và nếu Workbook bạn tham chiếu đến đang ở trạng thái đóng thì chắc chắn bạn sẽ gặp phải lỗi REF ngay tức thì. Phương án giải quyết tốt nhất lúc này đó là bạn cần xem lại bản thân đã mở file Excel mà mình muốn tham chiếu hay chưa, để hàm có thể xác định đúng vị trí các dữ liệu.

Tuy nhiên, để giảm thiểu khả năng xuất hiện lỗi REF do nguyên nhân này, FASTCARE khuyến khích bạn nên đổi sang các hàm khác ít mắc lỗi và dễ dùng hơn như Vlookup, Hlookup, Iferror,...thay vì hàm Indirect.

Lời kết

Với những thủ thuật trên đây hy vọng bạn sẽ không còn phải ngán ngẩm hay đóng dấu hỏi trong đầu về những vấn đề như là: lỗi #REF trong Excel là gì? Cách sửa lỗi #REF trong Excel? FASTCARE mong rằng bạn sẽ có thể xử lý một cách nhẹ nhàng và nhanh chóng nhất!