Lỗi #ref trong excel là gì ? Cách khắc phục lỗi và ví dụ minh họa

Nguyên nhân và cách khắc phục lỗi #REF! trong Excel

Khi thao tác trên Excel, lỗi #REF! xảy ra khá phổ biến gây cản trở người dùng thực hiện các thuật toán. Đôi khi việc thực hiện sai cú pháp hoặc lỗi khi thao tác với cột, hàng hoặc với các ô tham chiếu cũng là nguyên nhân gây ra lỗi này.

• Lỗi #REF! là gì? Lỗi #REF! thường gặp ở những loại hàm nào trong Excel?
• Nguyên nhân và cách khắc phục lỗi #REF! trong Excel

Để có cái nhìn chi tiết hơn về lỗi #REF! trong Excel, cùng Mega tìm hiểu trong bài viết hôm nay.

 Nguyên nhân và cách khắc phục lỗi #REF! trong Excel

I. Lỗi #REF! là gì? Lỗi #REF! thường gặp ở những loại hàm nào trong Excel

#REF! là tên viết tắt của từ Reference, tức là Tham chiếu. Vậy nên nếu lỗi này xuất hiện trong trường hợp nào thì trường hợp đó được hiểu là lỗi tham chiếu.

Lỗi #REF! trong Excel thường được hiển thị khi công thức tham chiếu đến ô không hợp lệ. Lỗi không thể tham chiếu trong excel thường xuyên xảy ra nhất khi các ô nằm trong công thức tham chiếu bị xóa hoặc bị dán đè lên.

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

II. Nguyên nhân và cách khắc phục 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!

Bảng Excel minh họa

Với bảng excel trên, nếu bạn xóa cột C, D hoặc E thì cột F sẽ bị lỗi như hình dưới đây.

Lỗi #REF! xuất hiện

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

Cách khắc phục:

+ Bạn có thể khôi phục cột đã vô tình xóa 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ụ.

+ Nên sử dụng tham chiếu dải ô thay vì chọn từng ô riêng lẻ. Bạn có thể thay hàm trên bằng =SUM(C2:E2). Khi đó, nếu 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.

Tham chiếu theo dải thay vì ô riêng lẻ

2. Lỗi #REF! do các ô tham chiếu không có sẵn, vượt quá phạm vi

Khi bạn thao tác nhập lệnh bị vượt quá phạm vị dữ liệu khai báo, Excel sẽ trả về lỗi #REF!.

Ví dụ 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!

Lỗi #REF! do các ô tham chiếu không có sẵn, vượt quá phạm vi

Cách khắc phục:

+ Cần điều chỉnh dải tham chiếu lớn hơn, ví dụ: =VLOOKUP(B2,B2:E5,5,FALSE)

Mở rộng vùng tham chiếu

+ Giảm số cột tra cứu để khớp với dải tham chiếu hiện tại, ví dụ: =VLOOKUP(B2;B2:E5;4;FALSE)

Giảm cột tra cứu từ 5 còn 4

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

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.

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

Cách khắc phục:

Bạn cần đ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 phù hợp. Ví dụ: =INDEX(B2:E5;4;4) sẽ trả về kết quả hợp lệ.

 Giá trị trả về ở hàng hoặc cột sao cho thuộc phạm vi dải tham chiếu

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

Khi thao tác với hàm INDIRECT, lỗi #REF! sẽ xảy ra lỗi khi:

+ ref_text không phải là một tham chiếu ô hợp lệ.

+ Khi 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. Bảng tính hoặc một trang tính Excel khác phải được mở để công thức INDIRECT tham chiếu đến một bảng tính đó.

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

Cách sửa lỗi #ref! trong excel ở trường hợp này là bạn cần:

+ 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 có trước đó.

+ Mở bảng tính Excel được tham chiếu đến.

III. Tổng kết

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.

Đừng quên truy cập website mega.com.vn để cập nhật các bài viết mới nhất liên quan đến các hàm và lỗi thường gặp trong Excel bạn nhé!

Xem thêm>>>

Sửa lỗi #num trong excel đơn giản, chi tiết và nhanh chóng nhất

Công thức và cách sử dụng hàm SQRT - hàm căn bật 2 trong excel hiệu quả

Tổng hợp những hàm cơ bản trong Excel thường được sử dụng nhiều nhất

copyright © mega.com.vn