5 Cách sửa lỗi #REF! trong Excel cực đơn giản mà bạn nên biết

Lỗi #REF! là một trong những lỗi tham chiếu phổ biến nhất trong Excel và thường khiến bảng tính trả về kết quả sai hoặc không thể xử lý dữ liệu. Bài viết này sẽ giúp bạn hiểu từng nguyên nhân gây lỗi, cách nhận biết và cách sửa theo từng tình huống cụ thể để thao tác Excel chính xác hơn.

Cách sửa lỗi #REF! trong Excel cực đơn giản

Lỗi #REF! là viết tắt của Reference, nghĩa là lỗi tham chiếu trong Excel. Khi thông báo này xuất hiện, Excel đang báo rằng công thức không thể tìm thấy ô hoặc vùng dữ liệu mà nó cần truy cập.

Nguyên nhân phổ biến nhất là bạn đã xóa, cắt hoặc dán đè lên những ô nằm trong công thức, khiến công thức trong ô tham chiếu không còn hợp lệ.

Một số hàm dễ xuất hiện lỗi #REF! khi vùng tham chiếu bị xóa hoặc thay đổi gồm có: SUM, AVERAGE, VLOOKUP, SUMPRODUCT, INDEX, IFERROR và nhiều hàm xử lý dữ liệu dựa trên địa chỉ ô khác.

Khi bạn lỡ xóa một cột nằm trong vùng tham chiếu của công thức, Excel sẽ lập tức báo lỗi #REF! vì công thức không còn tìm thấy địa chỉ ô cần thiết.

Lỗi #REF! khi công thức mất cột tham chiếu

Để khắc phục đúng cách, bạn nên kiểm tra lại công thức đang dùng, xác định vị trí tham chiếu bị mất, sau đó chỉnh sửa hoặc khôi phục lại dữ liệu tương ứng.

Cách làm gợi ý:

• Bước 1: Nhìn vào ô báo lỗi và mở công thức để xem phần tham chiếu nào đang hiển thị #REF!.
• Bước 2: Đối chiếu với bảng dữ liệu để xác định cột hoặc vùng bị xóa hoặc thay đổi.
• Bước 3: Thêm lại cột cần thiết (nếu bị xóa) hoặc chỉnh lại vùng tham chiếu trong công thức sao cho khớp với dữ liệu hiện tại.
• Bước 4: Nếu thao tác vừa thực hiện, bạn có thể dùng Ctrl + Z để phục hồi nhanh cột đã xóa.

Xác định cột hoặc vùng dữ liệu bị xóa gây lỗi #REF!

Lỗi #REF! cũng có thể xảy ra khi công thức yêu cầu truy xuất đến nhiều cột hơn so với phạm vi dữ liệu thực tế. Điều này thường gặp ở các hàm như VLOOKUP, nơi bạn khai báo chỉ số cột lớn hơn số cột thật sự có trong bảng.

Ví dụ:

Bước 1: Trong bảng trên, nếu bạn dùng VLOOKUP và đặt chỉ số cột trả về là 5, nhưng vùng dữ liệu chỉ có 4 cột, Excel sẽ báo lỗi #REF! vì không tìm thấy cột thứ 5 để trả kết quả.

Lỗi #REF! do VLOOKUP yêu cầu số cột lớn hơn phạm vi bảng

Bước 2: Chỉnh lại chỉ số cột từ 5 xuống 4, tương ứng với số cột thực tế trong bảng, công thức sẽ hoạt động bình thường.

Điều chỉnh chỉ số cột phù hợp để công thức VLOOKUP hoạt động đúng

Khi sử dụng hàm INDEX, nếu bạn yêu cầu hàm trả về giá trị ở một hàng hoặc cột nằm ngoài phạm vi của vùng dữ liệu đã chọn, Excel sẽ báo lỗi #REF!.

Ví dụ:

Bước 1: Bạn nhập tham số để lấy dữ liệu tại hàng 5 và cột 5, trong khi vùng tham chiếu chỉ có 4 hàng và 4 cột. Do vượt quá giới hạn nên công thức lập tức trả về #REF!.

Lỗi #REF! khi hàm INDEX gọi tới hàng hoặc cột vượt phạm vi dữ liệu

Bước 2: Hãy chỉnh lại chỉ số hàng hoặc cột sao cho phù hợp với kích thước thật của vùng dữ liệu. Khi hàng và cột nằm trong phạm vi hợp lệ, hàm sẽ trả kết quả bình thường.

Chỉnh lại chỉ số hàng và cột trong INDEX để trả về kết quả chính xác

Hàm INDIRECT trong Excel được xem là hàm “đóng”, nghĩa là các hàm khác không thể truy xuất trực tiếp vào vùng dữ liệu được tạo bởi INDIRECT. Vì vậy, khi bạn cố gắng tham chiếu đến kết quả của hàm này, Excel có thể báo lỗi #REF! nếu phạm vi không được mở hoặc xác định lại đúng cách.

Bước 1: Thiết lập bảng dữ liệu và sử dụng INDIRECT để tham chiếu đến ô cần thiết như ví dụ minh họa.

Lỗi #REF! khi công thức khác truy cập vùng được tạo bởi INDIRECT

Bước 2: Nếu công thức khác cố truy cập vào vùng được tạo bởi INDIRECT, Excel sẽ hiển thị lỗi #REF!. Khi đó, bạn cần mở lại hàm INDIRECT và chỉ định lại tham chiếu sao cho phù hợp thì công thức mới hoạt động bình thường.

Cách điều chỉnh hàm INDIRECT để khôi phục tham chiếu hợp lệ

Việc copy một bảng tính từ file này sang file khác đôi khi khiến công thức mất tham chiếu, dẫn đến lỗi #REF!. Nguyên nhân là do cấu trúc dữ liệu hoặc vị trí ô ở file mới không giống với file ban đầu.

Bước 1: Sao chép bảng dữ liệu từ file gốc sang một file Excel khác.

Lỗi #REF! xuất hiện khi sao chép bảng sang file mới khiến mất tham chiếu

Bước 2: Khi xuất hiện lỗi #REF!, hãy đối chiếu hai file để xem công thức đang thiếu hoặc sai tham chiếu ở vị trí nào. Sau khi chỉnh lại cho phù hợp với cấu trúc mới, bảng sẽ trả về kết quả chính xác.

Đối chiếu hai file và chỉnh lại tham chiếu để bảng trả kết quả chính xác

Bên cạnh lỗi #REF!, Excel còn xuất hiện nhiều lỗi cú pháp mà người dùng nên biết, bao gồm:

• #N/A: Xuất hiện khi hàm không tìm thấy giá trị phù hợp hoặc dữ liệu tra cứu không tồn tại.
• #VALUE!: Báo lỗi khi kiểu dữ liệu không đúng với yêu cầu của công thức.
• #NAME?: Thường xảy ra khi nhập sai tên hàm, sai phạm vi hoặc thiếu dấu ngoặc kép trong văn bản.
• #NUM!: Lỗi do giá trị số không hợp lệ hoặc công thức tạo ra kết quả vượt giới hạn.
• #DIV/0!: Thông báo xuất hiện khi công thức thực hiện phép chia cho 0 hoặc cho ô trống.

Hiểu rõ lỗi #REF! cùng các lỗi cú pháp phổ biến trong Excel giúp bạn xử lý bảng dữ liệu mượt hơn và hạn chế sai sót. Nếu muốn học thêm nhiều mẹo thao tác Excel và kinh nghiệm công nghệ hữu ích, hãy theo dõi chuyên mục Kinh nghiệm của Siêu Thị Điện Máy - Nội Thất Chợ Lớn.

Điện thoại hay máy tính bảng đều có thể trở thành công cụ hỗ trợ đắc lực khi bạn cần xem, chỉnh sửa hoặc quản lý tài liệu nhanh. Tham khảo các thiết bị chính hãng tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để chọn mẫu phù hợp.