Cách sửa lỗi #REF! trong Excel chi tiết đơn giản nhất

Lỗi #REF! hiển thị 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 đè.

Lỗi #REF! thường gặp ở những hàm như: AVERAGE, SUM, VLOOKUP, SUMPRODUCT, INDEX,..

Nguyên nhân và cách sửa lỗi #REF! trong Excel

1. Lỗi #REF! xuất hiện khi xóa cột

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, đây là nguyên nhân gây nên lỗi #REF!

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

Nếu bạn muốn xóa cột B, C hoặc D thì việc xóa cột này sẽ gây ra lỗi #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 hiện có nội dung là =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 không thể giải quyết, vì vậy excel sẽ trả về giá trị #REF! lỗi. Đây là lý do chính khiến bạn không nên 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.

2. Lỗi #REF! do các ô tham chiếu 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 này tương tự cũng xảy ra khi bạn sử dụng hàm HLOOKUP

3. INDEX có tham chiếu hàng hoặc cột không chính xác

Đối với hàm INDEX, nếu bạn cần trả về giá trị ở hàng hoặc cột vượt qua khoảng tham chiếu thì cũng gây lỗi #REF! trong excel.

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

4. Tham chiếu sổ làm việc đã đóng với hàm INDIRECT

Nguyên nhân: Thường xảy ra khi sử dụng hàm Indirect để tham chiếu tới một đối tượng bên ngoài bảng tính đang làm việc và bảng tính được tham chiếu đang đóng.

Cách khắc phục: Mở bảng tính được tham chiếu hoặc thay đổi phương pháp tham chiếu.

Với một số nguyên nhân thường gặp và cách khắc phục lỗi #REF! ở trên hy vọng bạn sẽ thao tác thuận lợi với các hàm trên trang tính Excel. Ngay cả bạn mới làm quen với Excel hoặc khi bạn đã có kinh nghiệm sử dụng trang tính này, trong quá trình thực hiện vẫn có thể xảy ra một số lỗi thường gặp và nếu biết được giải pháp cho những lỗi này bạn sẽ tiết kiệm thời gian cũng như thao tác trên Excel thuận tiện và dễ dàng hơn.