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

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

Thông thường, 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.

Chẳng hạn như trong ví dụ trên, nếu bạn xóa một trong các cột B, C, hoặc D sẽ khiến cho giá trị của cột E bị lỗi #REF!.

Cụ thể hơn, nếu cột C bị xóa , lúc này hàm SUM ở cột E sẽ trở thành =SUM(B2,#REF!,D2) và từ đó trả về giá trị #REF! cho ô E2.

Giải pháp:

• Nếu bạn vô tình xóa cột không mong muốn, bạn có thể khôi phục lại bằng cách nhấn tổ hợp phím Ctrl+Z hoặc nhấn vào biểu tượng Hoàn tác trên thanh công cụ.
• Ưu tiên sử dụng tham chiếu dải ô thay vì chọn từng ô riêng lẻ. Chẳng hạn bạn có thể thay hàm trên bằng =SUM(B2:D2). Khi đó, kể cả khi bạn xóa một cột bất kỳ trong khoảng tham chiếu, Excel cũng sẽ tự động điều chỉnh công thức cho phù hợp.

VLOOKUP với tham chiếu dải ô không chính xác

Hàm VLOOKUP giúp tìm kiếm dữ liệu theo hàng dọc và trả về giá trị tương ứng theo hàng ngang trong một bảng.

Trường hợp bạn sử dụng hàm VLOOKUP với số thứ tự của cột lấy dữ liệu vượt quá số cột hiện có của bảng tham chiếu thì sẽ gây ra lỗi #REF!

Cụ thể hơn, trong ví dụ trên, hàm VLOOKUP đang tìm kiếm giá trị trả về ở cột thứ 5, nhưng dải tham chiếu A:D chỉ có 4 cột. Trường hợp tương tự cũng xảy ra khi bạn sử dụng hàm HLOOKUP.

Giải pháp:

• Điều chỉnh dải tham chiếu lớn hơn, ví dụ: =VLOOKUP(A8;A2:E5;5;FALSE).
• Giảm số cột tra cứu để khớp với dải tham chiếu hiện tại, ví dụ: =VLOOKUP(A8;A2:D5;4;FALSE)

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

Tương tự, đố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!.

Cụ thể, trong trường hợp trên, công thức là =INDEX(B2:E5;5;5) yêu cầu trả về giá trị trong cột thứ 5 và hàng thứ 5 nhưng dải ô chỉ có 4 hàng và 4 cột.

Giải pháp: Điều chỉnh lại giá trị trả về ở hàng hoặc cột sao cho thuộc phạm vi dải tham chiếu. Ví dụ: =INDEX(B2:E5;4;4) sẽ trả về kết quả hợp lệ.

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.

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.