Tại sao công thức trong excel trả kết quả ref

Đôi khi làm việc với một bảng có rất nhiều công thức, tuy nhiên vì chúng ta xóa đi một cột nào đó, hoặc sử không biết tại sao khi dùng hàm Vlookup hay Index mà kết quả hàm trả về lại là #REF!, bài viết sau đây sẽ giúp chúng ta có cách nhìn rõ hơn và giải pháp khắc phục

Lỗi #REF! xảy khi cột chứa ô được tham chiếu bởi công thức bị xóa hoặc bị vượt quá số cột được xác định trước trong hàm

Ví dụ1 - TÍNH TỔNG xảy ra lỗi #REF!

Nguyên nhân là do việc xóa cột, ví dụ sau đây sử dụng công thức P8=O8+N8+M8

Công thức sử dụng tham chiếu ô rõ ràng như =O8+N8+M8 có thể gây ra lỗi #REF! nếu một cột bị xóa, chẳng hạn bạn xóa bỏ cột O, N hoặc M nó sẽ gây ra lỗi #REF!. Tôi sẽ xóa bỏ cột N (L2), và công thức trả về =N8+#REF!+M8.

Giải pháp

Bạn có thể sử dụng =SUM(M8:O8) cho tổng của hàng thay vì cộng từng địa chỉ ô, và kết quả sẽ không bị lỗi nếu ta xóa cột bất kỳ và kết quả khắc phục

Ví dụ 2 - VLOOKUP lỗi #REF!

với tham chiếu dải ô không chính xác hay nói cách khác là cột tham chiếu vượt quá vùng tìm kiếm
Trong ví dụ sau đây, =VLOOKUP(B8;DMVT!$A$8:$D$2200;6;0) 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 6 nhưng dải ô tham chiếu lại là A:D, chỉ là 4 cột.

Tại sao công thức trong excel trả kết quả ref

Giải pháp

Điều chỉnh phạm vi vùng tìm kiếm lớn hơn hoặc giảm cột tra cứu giá trị để khớp với phạm vi tham chiếu như  sau  =VLOOKUP(B8;DMVT!$A$8:$D$2200;2;0), kết quả nhu sau:

Tại sao công thức trong excel trả kết quả ref

Ví dụ 3- INDEX lỗi #REF!

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(J8:M21;1;5) trả về lỗi #REF! do dải ô của hàm INDEX là 14 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ứ 1.

Tại sao công thức trong excel trả kết quả ref

Giải pháp

Điều chỉnh tham chiếu hàng hoặc cột để chúng bên trong phạm vi tra cứu chỉ mục.=INDEX(J8:M21;1;4) sẽ trả về kết quả hợp lệ.

Tại sao công thức trong excel trả kết quả ref

Chúc các bạn thành công

Cách sửa lỗi REF trong Excel chi tiết có video hướng dẫn, Lỗi #REF! trong Excel là một lỗi bạn thường hay gặp nhật khi tính toán trong Excel, đây là một lỗi khá khó chịu và rất khó xác định được vấn đề, tuy nhiên nếu bạn nắm được các vấn đề được liệt kê trong bài viết này bạn có thể dễ dàng sửa lỗi #REF! trong Excel một cách hiệu quả nhất, trong bài viết này mình sẽ hướng dẫn các bạn chi tiết về lỗi và cách sửa trong từng trường hợp cụ thể.

Excel đã quá quen thuộc với những ai đang sử dụng máy tính đặc biệt là đối với dân văn phòng, Excel là ứng dụng không thể thiếu để giúp các bạn có thể tính toán và lập báo cáo một cách nhanh và chuẩn xác nhất.

Lỗi #REF! trong Excel là gì?

#REF! là tên viết tắt của từ Reference, tức là Tham chiếu. Khi bạn thấy thông báo lỗi này tức là dữ liệu của bạn tham chiếu đến một nơi nào đó không hợp lệ (không có dữ liệu, dữ liệu nằm ngoài vùng hoặc dữ liệu không đúng định dạng).

Nguyên nhân chủ yếu gây ra lỗi #REF!

  • Tham chiếu tới 1 file đang chưa được mở nên không tham chiếu được.
  • Đối tượng cần tham chiếu không có trong vùng tham chiếu.
  • Đối tượng cần tham chiếu bị mất bởi việc xóa cột, xóa hàng chứa đối tượng đó, do bị ghi đè bởi việc copy paste, dữ liệu định dạng trong vùng dữ liệu không hợp lệ

Từ những nguyên nhân trên sẽ có các phương pháp khác nhau để bạn có thể sửa lỗi REF một cách hiệu quả nhất.

Một số hàm có thể dễ mắc lỗi #REF! như: AVERAGE, SUM, VLOOKUP, SUMPRODUCT, INDEX, IFERROR,…

Sửa lỗi REF trong một số trường hợp cụ thể.

Lỗi #REF! do dữ liệu tham chiếu bị mất hoặc bị ghi đè

Do cột tham chiếu, ô tham chiếu bị mất dữ liệu hoặc bị ghi đè, chèn dữ liệu khác. Ở đây mình minh họa với cột tham chiếu dữ liệu bị mất.

Giả sử mình có bảng số liệu như hình bên dưới.

Tại sao công thức trong excel trả kết quả ref

Hiện tại bảng số liệu đang chuẩn xác, mình sẽ tiến hành xóa cột “D” (cột điểm lý), thì lúc này cột Tổng điểm sẽ xuất hiện lỗi #REF! như hình bên dưới.

Tại sao công thức trong excel trả kết quả ref

Cách khắc phục: Hãy kiểm tra hàm tính toán bị mất dữ liệu ở ô tham chiếu nào và bổ sung vào là được (hoặc bạn có thể xóa ô tham chiếu dữ liệu dư này đi). Ở đây mình sẽ bổ sung thêm cột dữ liệu điểm Lý nên Excel sẽ trả kết quả chính xác cho bạn như hình bên dưới.

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 công thức bị vượt quá phạm vi dữ liệu khai báo nên Excel sẽ trả về lỗi #REF!.

Giả sử mình có bảng số liệu như hình dưới, mình muốn lấy điểm của học sinh tên “Nam” mình sẽ dùng hàm VLOOKUP để dò dữ liệu điểm của học sinh tên “Nam” tương ứng.

Như bạn thấy hàm VLOOKUP ở hình minh họa mình đã chọn dãy tham chiếu chứa 4 cột (B2:E6), nhưng mình lại nhập lệnh tìm kiếm cho cột thứ 5 như trong hình.

Tại sao công thức trong excel trả kết quả ref

Sau khi khai báo lệnh và nhấn Enter Excel báo lỗi #REF! như hình.

Tại sao công thức trong excel trả kết quả ref

Cách khắc phục: Kiểm tra và chỉnh sửa lại đúng với số lượng dữ liệu cần tìm kiếm trong hàm. Ở đây bạn có thể chỉnh lấy từ cột 1 đến 4 do muốn lấy là cột điểm toán nên mình chỉnh sửa lại thành cột 2 nên Excel cho ra kết quả như hình.

Tại sao công thức trong excel trả kết quả ref

Lỗi #REF! do copy dữ liệu từ file Excel này sang file Excel khác

Thường gặp khi sao chép dữ liệu từ bảng Excel này qua Excel khác mà ô chứa dữ liệu không trùng khớp.

Giả sử mình đang nhập liệu bảng tính ở cột A đến E và tính tổng ở cột F (cột F có công thức tính tổng) như hình dưới.

Tại sao công thức trong excel trả kết quả ref

Sau đó mình sao chép nội dung qua một File Excel khác, tuy nhiên mình lại dán từ cột C đến cột G và lúc này công thức tính tổng sẽ ở cột H, lúc này sẽ xảy ra lỗi #REF!.

Tại sao công thức trong excel trả kết quả ref

Cách khắc phục: Bạn kiểm tra lại vùng dữ liệu sao chép sao cho trùng khớp với file gốc hoặc bạn phải chỉnh lại công thức sao cho khớp với vùng dữ liệu mới là được.

Ở đây mình chỉnh lại ô dữ liệu sao chép nên Excel sẽ hiển thị kết quả như hình minh họa.

Tại sao công thức trong excel trả kết quả ref

Lỗi #REF! do tham chiếu tới 1 file đang đóng

Thường xảy ra khi sử dụng hàm Indirect để tham chiếu tới 1 đối tượng bên ngoài workbook đang làm việc và workbook đượ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.

//Bạn là người mới học Excel, hãy tham khảo 20 mẹo Excel hữu ích sẽ giúp ích cho bạn trong việc sử dụng Excel rất lớn đấy!

Xem video sửa lỗi REF trong Excel đơn giản

Kết luận

Như vậy mình đã hướng dẫn khá chi tiết về lỗi #REF! là gì? những nguyên nhân gây ra lỗi #REF!? các phương pháp sửa lỗi REF trong từng trường hợp cụ thể khá chi tiết rồi đấy? bạn có hay bị lỗi #REF! trong quá trình sử dụng Excel hay không? hãy để lại bình luận của bạn bên dưới nếu có phương pháp khắc phục lỗi #REF! khác? chúc các bạn vui vẻ.