Đánh giá lequocthai.com:
Giả sử bạn có sẵn một bảng tính từ vài tháng trước. Bạn mở nó ra và bắt đầu làm việc. Trong khi cập nhật dữ liệu, đột nhiên bạn thấy một vài công thức cũ hiển thị lỗi #REF!. Cách khắc phục? Chỉ cần sửa lại tham chiếu cho đúng.
Lỗi #REF! khá dễ để xử lý. Trong hướng dẫn này, bạn sẽ tìm hiểu nguyên nhân gây ra lỗi #REF! và cách khắc phục lỗi này. Nếu bạn cảm thấy chưa rõ, chúng ta sẽ cùng tìm hiểu về lỗi #REF!, từ khái niệm cho đến cách sửa chữa.
Lỗi #REF! trong Excel là gì?
Lỗi #REF! là một thông báo lỗi trong Excel, cho biết một hoặc nhiều tham chiếu được dùng trong công thức của bạn không hợp lệ. Ví dụ: nếu bạn tính tổng các ô từ C1 đến C5 [=C1+C2+C3+C4+C5] trong ô C6, sau đó xóa bất kỳ ô nào từ C1 đến C5, công thức trong ô C6 sẽ trả về lỗi #REF!. Để hiểu rõ hơn, hãy tham khảo ví dụ 1.
Tại sao lỗi này xuất hiện?
Lỗi #REF! xảy ra khi bạn tham chiếu đến một thành phần (ô, cột, hàng, trang tính) trên bảng tính hoặc sổ làm việc không còn tồn tại. Thông thường, bạn gặp lỗi này vì đã tham chiếu đến một ô, cột, hàng hoặc trang tính, rồi sau đó xóa chúng đi khỏi sổ làm việc. Bạn cũng có thể thấy lỗi #REF! khi sao chép một công thức có tham chiếu tương đối sang một bảng tính khác, nơi những tham chiếu đó trở nên không hợp lệ.
Ví dụ
Dưới đây là một số ví dụ điển hình về lỗi #REF!. Mặc dù còn nhiều trường hợp khác, nhưng các giải pháp chúng tôi thảo luận ở đây sẽ giúp bạn xử lý mọi lỗi #REF! trên bảng tính của mình.
- Tham chiếu đã bị xóa
Khi bạn xóa một ô, cột, hàng hoặc trang tính mà bạn đã tham chiếu trong một công thức nào đó, bạn sẽ thấy lỗi #REF!. Lý do rất đơn giản: thành phần bạn tham chiếu đến trong công thức không còn tồn tại, và Excel không thể sử dụng dữ liệu đó để tính toán nữa.
Ví dụ: giả sử bạn có số liệu bán hàng trong cột B. Bạn muốn tính tổng, vì vậy sử dụng công thức sau:
=
B2
+
B3
+
B4
+
B5
+
B6
Bạn nhận được kết quả và mọi thứ hoạt động tốt. Bây giờ, bạn chọn và nhấp chuột phải vào hàng 6, rồi xóa tất cả số liệu ở đó. Bạn sẽ thấy ngay kết quả thay đổi thành lỗi #REF!.
Giải pháp? Có hai cách. Bạn có thể sửa công thức để tham chiếu đến một tập dữ liệu thực sự tồn tại trên bảng tính. Hoặc bạn có thể nhập lại dữ liệu vào các ô đã được tham chiếu trong công thức.
- Sao chép công thức có tham chiếu ô tương đối
Khi bạn sao chép một công thức sang một ô khác và thấy lỗi #REF!, thì gần như chắc chắn là do tham chiếu tương đối. Bạn không chắc điều này có nghĩa là gì? Hãy thử ngay trên bảng tính của bạn. Nhập một vài số vào một cột, và dùng hàm SUM để tính tổng chúng.
Sau đó, sao chép hàm SUM và dán nó vào một ô khác ở một cột xa.
Xuất hiện lỗi #REF! rồi phải không? Đó là vì Excel giả định rằng khi bạn dán công thức sang một cột khác, bạn cũng muốn tham chiếu đến dữ liệu tương ứng trong cột đó. Tuy nhiên, cách khắc phục lại cực kỳ đơn giản.
Tất cả những gì bạn cần làm là chuyển các tham chiếu của mình thành tham chiếu tuyệt đối. Trước tiên, hoàn tác hành động dán bằng cách nhấn Ctrl + Z để loại bỏ lỗi #REF!. Bây giờ, hãy thêm dấu đô la ($) vào các tham chiếu ô. Công thức của bạn sẽ trông như thế này:
=
SUM
($B$2:$B$6)
Đơn giản phải không?
- Lỗi #REF! với Hàm VLOOKUP
Chúng ta đều biết lỗi #REF! là lỗi tham chiếu. Tuy nhiên, có một chút khác biệt trong cách lỗi này xuất hiện khi bạn dùng hàm VLOOKUP. Hãy sử dụng dữ liệu từ ví dụ trước và áp dụng hàm VLOOKUP.
Đây là công thức chúng ta sẽ dùng:
=
VLOOKUP
(D4,A2:B6,3,0)
Nhận được lỗi #REF! rồi chứ?
Đúng vậy. Hãy thử nghĩ xem lý do tại sao.
Được rồi, lý do là bạn đã tham chiếu đến cột thứ 3 (đối số `col_index_num`), trong khi mảng dữ liệu bạn cung cấp (A2:B6) chỉ có 2 cột. Cột thứ 3 không tồn tại trong phạm vi này, vì vậy hàm trả về lỗi #REF!.
Một lần nữa, cách khắc phục rất đơn giản.
Chỉ cần đổi đối số thứ ba từ 3 thành 2 là xong.
Còn gì dễ hơn không?
Các giải pháp khác cho Lỗi #REF!
Trong hầu hết trường hợp, bạn có thể khắc phục lỗi #REF! bằng các phương pháp đã thảo luận. Tuy nhiên, vẫn có một số tình huống phức tạp hơn.
Ví dụ: nếu bạn xóa một trang tính khỏi sổ làm việc và có nhiều lỗi #REF! rải rác trên các bảng tính khác do tham chiếu đến trang tính đó thì sao? Thì không còn cách nào khác, bạn buộc phải sửa từng lỗi bằng cách cập nhật lại tham chiếu cho chính xác.
- Xóa Lỗi #REF! khỏi Bảng tính
Nếu bạn muốn loại bỏ hoàn toàn các lỗi #REF! thay vì sửa chúng, bạn có thể dùng tính năng Tìm và Thay thế.
Để làm điều này, nhấn Ctrl + H, nhập #REF! vào ô “Tìm gì”. Để trống ô “Thay thế bằng” và nhấp vào “Thay thế Tất cả”.
- Bẫy Lỗi #REF!
Thông thường, không cần thiết phải dùng hàm IFERROR để bẫy lỗi #REF!. Tuy nhiên, trong một số trường hợp, bạn có thể muốn dùng nó. Ví dụ: nếu bạn gặp lỗi #REF! do sử dụng các tham chiếu động với hàm INDIRECT, thì hàm IFERROR có thể hữu ích. Để tìm hiểu cách bẫy lỗi, bạn có thể đọc hướng dẫn chi tiết về hàm IFERROR của chúng tôi.
Đó là tất cả các cách bạn có thể giải quyết lỗi #REF! trên bảng tính. Một lần nữa, việc khắc phục lỗi #REF! trong đa số trường hợp là rất đơn giản. Trong những trường hợp hiếm hoi phức tạp hơn, như khi dùng hàm INDIRECT, bạn sẽ cần vận dụng kỹ năng Excel sâu hơn một chút.
Với sự đồng hành của chúng tôi, bạn có thể làm chủ mọi thứ trên bảng tính của mình. Trong khi bạn xử lý các lỗi #REF!, chúng tôi sẽ chuẩn bị thêm những hướng dẫn hữu ích khác cho bạn.
