Lỗi #REF! trong Excel – Nguyên nhân và giải pháp khắc phục hiệu quả

Lỗi #REF! trong Excel xảy ra khi công thức tham chiếu đến ô không hợp lệ. Điều này thường xuyên xảy ra nhất khi các ô được công thức tham chiếu bị xóa hoặc bị dán đè. Bài viết sau sẽ giúp bạn tìm hiểu nguyên nhân cụ thể gây ra lỗi #REF! cũng như các giải pháp khắc phục hiệu quả.

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

Ví dụ sau đây sử dụng công thức =SUM(B2;C2;D2) ở cột E

Nếu bạn xóa cột B, C hoặc D, việc xóa cột này sẽ dẫn đến #REF! lỗi. Trong trường hợp này, chúng tôi sẽ xóa cột C (Doanh số 2007) và công thức lúc này có nội dung =SUM(B2,#REF!,C2). Khi bạn sử dụng tham chiếu ô rõ ràng như thế này (nơi bạn tham chiếu từng ô riêng lẻ, được phân tách bằng dấu phẩy) và xóa hàng hoặc cột được tham chiếu, Excel sẽ không thể xử lý, do đó, sẽ trả về lỗi #REF! lỗi. Đây là lý do chính khiến không khuyến khích sử dụng tham chiếu ô rõ ràng trong hàm.

Giải pháp

• Nếu bạn vô tình xóa các hàng hoặc cột, bạn có thể bấm ngay nút Hoàn tác trên Thanh công cụ Truy nhập Nhanh (hoặc nhấn CTRL+Z) để khôi phục hàng hoặc cột đó.
• Điều chỉnh công thức để công thức sử dụng tham chiếu dải ô thay vì ô riêng lẻ, chẳng hạn như =SUM(B2:D2). Bây giờ, bạn có thể xóa bất cứ cột nào trong dải ô tổng và Excel sẽ tự động điều chỉnh công thức. Bạn cũng có thể sử dụng công thức =SUM(B2:B5) để tính tổng các hàng.

Lỗi #REF! xảy ra khi VLOOKUP với tham chiếu dải ô không chính xác

Trong ví dụ sau đây, =VLOOKUP(A8;A2:D5;5;FALSE) sẽ trả về lỗi #REF! vì công thức này đang tìm kiếm giá trị để trả về từ cột 5 nhưng dải ô tham chiếu lại là A:D, chỉ là 4 cột

Giải pháp

• Điều chỉnh cho dải ô lớn hơn hoặc giảm giá trị tra cứu cột để khớp với dải ô tham chiếu. =VLOOKUP(A8;A2:E5;5;FALSE) sẽ là dải ô tham chiếu hợp lệ, =VLOOKUP(A8;A2:D5;4;FALSE) cũng vậy.

Lỗi #REF! xảy ra khi INDEX có tham chiếu hàng hoặc cột không chính xác

Trong ví dụ này, công thức =INDEX(B2:E5;5;5) trả về lỗi #REF! do dải ô của hàm INDEX là 4 hàng và 4 cột nhưng công thức đang yêu cầu trả về giá trị trong cột thứ 5 và hàng thứ 5.

Giải pháp

• Điều chỉnh tham chiếu hàng hoặc cột sao cho tham chiếu nằm trong dải ô tra cứu INDEX. =INDEX(B2:E5;4;4) sẽ trả về kết quả hợp lệ.

Lỗi #REF! xảy ra khi tham chiếu sổ làm việc đã đóng với hàm INDIRECT

Thông thường công thức hàm INDIRECT sẽ trả về lỗi #REF! trong ba trường hợp sau:

• ref_text không phải là một tham chiếu ô hợp lệ.
• Tham chiếu đến ô vượt quá giới hạn dải tham chiếu (Giới hạn của hàng là 1,048,576 hoặc giới hạn cột là 16,384).
• Bảng tính hoặc trang tính Excel được tham chiếu đến đang đóng. Nếu công thức INDIRECT của bạn tham chiếu đến một bảng tính hoặc một trang tính Excel khác, thì bảng tính đó phải được mở.

Giải pháp

• Kiểm tra đối số ref_text của hàm INDIRECT.
• Đảm bảo tham chiếu đến ô không vượt quá giới hạn hàng hoặc cột.
• Mở bảng tính Excel được tham chiếu đến.

Sự cố về OLE

Nếu bạn đã sử dụng một liên kết Liên kết và Nhúng Đối tượng (OLE) trả về một giá #REF!, sau đó khởi động chương trình mà liên kết đang gọi.

Lưu ý: OLE là công nghệ mà bạn có thể sử dụng để chia sẻ thông tin giữa các chương trình.

Sự cố DDE

Nếu bạn đã sử dụng chủ đề Dữ liệu Exchange động (DDE) trả về một nội #REF! , trước tiên hãy kiểm tra để đảm bảo rằng bạn đang tham chiếu chủ đề chính xác. Nếu bạn vẫn đang nhận được thông báo lỗi #REF! , hãy kiểm tra hộp kiểm Trung tâm Tin cậy Cài đặt nội dung ngoài như được nêu trong mục Chặn hoặc bỏ chặn nội dung bên ngoài trong Office liệu.

Lưu ý: Dữ liệu Exchange động (DDE)là giao thức được thiết lập để trao đổi dữ liệu giữa các chương trình Microsoft Windows nền tảng Điện thoại.

Sự cố về Macro

Nếu macro nhập vào trang tính một hàm tham chiếu đến ô ở phía trên hàm, trong khi ô chứa hàm nằm ở hàng 1 thì hàm sẽ trả về lỗi #REF! vì không có ô nào ở bên trên hàng 1. Kiểm tra hàm xem tham đối có tham chiếu đến ô hoặc dải ô không hợp lệ hay không. Có thể cần đến việc chỉnh sửa macro trong Trình soạn thảo Visual Basic (VBE) để xem xét trường hợp đó.

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.

Các hàm thường dùng khi học excel kế toán, kiểm toán

Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung đơn giản nhất