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! 

banner-fastcare-blog-desktop-tet-thay-pin-chua-850x200

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é!

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

Có thể bạn cần:

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,...

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

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 sửa lỗi REF trong Excel

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.

Lỗi do cột tham chiếu bị ghi đè hoặc xóa mất

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ỗi do cột tham chiếu bị ghi đè hoặc xóa mất bước 1

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ỗi do cột tham chiếu bị ghi đè hoặc xóa mất bước 2

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ỗi do cột tham chiếu bị ghi đè hoặc xóa mất bước 3

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ý.

Lỗi do cột tham chiếu bị ghi đè hoặc xóa mất bước 4

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.

Lỗi do ô tham chiếu vượt quá phạm vi hoặc không có sẵn bước 1

Để 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.

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

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.

Lỗi do tham chiếu tới 1 file đang trong trạng thái đóng

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.

banner-fastcare-blog-desktop-tet-phu-kien-qua-tot-850x200

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!