#REF! nghĩa là công thức tham chiếu đến một ô không có ở đó. Nguyên nhân thường gặp là một hàng, cột hoặc trang tính đã bị xóa: khi cột C bị xóa, Excel viết lại =B2*C2 thành =B2*#REF!, và từ đó kết quả luôn là #REF!. Nhấn Ctrl+Z (Cmd+Z trên Mac) ngay sau khi xóa để lấy lại cả cột lẫn công thức.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! Công thức tham chiếu đến một ô không tồn tại.Cột số lượng đã bị xóa rồi được gõ lại, nhưng công thức vẫn ghi #REF!: Excel không bao giờ tự sửa một tham chiếu đã mất. Bấm vào D2, thay #REF! bằng C2 rồi nhấn Enter. Cả cột thay đổi theo, và D2 hiện 12.
#REF! xuất hiện trong công thức như thế nào
Excel ghi #REF! vào công thức mỗi khi một ô mà công thức dùng biến mất:
| Bạn đã làm gì | =B2*C2 ở D2 trở thành |
|---|---|
| Xóa cột C | =B2*#REF! |
| Xóa hàng 2 | công thức bị xóa cùng hàng của nó; công thức ở các hàng khác trỏ đến hàng 2 nhận #REF! |
| Xóa trang tính mà công thức tham chiếu | =#REF!B2*2 (với công thức như =Prices!B2*2) |
| Cắt một ô rồi dán đè lên một ô mà công thức dùng | #REF! thay cho tham chiếu bị ghi đè |
Xóa các ô bên trong một vùng thì an toàn: =SUM(B2:D2) thành =SUM(B2:C2) khi cột C bị xóa. Xóa ô đầu hoặc ô cuối của vùng chỉ làm vùng thu nhỏ lại. Vì vậy =SUM(B2:D2) an toàn hơn =B2+C2+D2, công thức sẽ thành =B2+#REF!+C2.
Vì sao VLOOKUP trả về #REF!
Đối số thứ ba của VLOOKUP đếm số cột bên trong vùng bảng. Nếu nó lớn hơn số cột trong vùng, kết quả là #REF!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! Công thức tham chiếu đến một ô không tồn tại.A2:C6 có ba cột, nên cột 4 không tồn tại. Đổi 4 thành 3 và F2 hiện 25. Lỗi này hay gặp nhất sau khi xóa một cột khỏi bảng tra cứu: vùng thu nhỏ lại, còn số cột viết cứng trong công thức thì không. XLOOKUP hoặc INDEX kết hợp MATCH tránh được lỗi này vì chúng chỉ rõ cột trả về, như =XLOOKUP(E2,A2:A6,C2:C6). Xem VLOOKUP để biết các đối số còn lại.
#REF! với INDEX và OFFSET
INDEX trả về #REF! khi số hàng hoặc số cột nằm ngoài vùng, còn OFFSET trả về lỗi này khi dịch lên trên hàng 1 hoặc sang trái cột A.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! Công thức tham chiếu đến một ô không tồn tại.A2:A6 có năm điểm số, nên INDEX(A2:A6,6) là #REF! trong khi INDEX(A2:A6,3) trả về 95. Hàng 0 không tồn tại, nên OFFSET(A2,-2,0) là #REF!, còn OFFSET(A2,4,0) rơi vào A6: 81. Khi vị trí đến từ một công thức khác (một hàm MATCH, một hàm COUNT), hãy kiểm tra công thức đó trước. Xem thêm ở trang INDEX.
INDIRECT cũng trả về #REF! khi chuỗi văn bản của nó không phải địa chỉ hợp lệ (=INDIRECT("ZZZ1"), vì cột cuối cùng là XFD) hoặc trỏ vào một workbook đang đóng.
#REF! khi sao chép công thức
Tham chiếu tương đối di chuyển theo công thức. Sao chép đủ xa lên trên hoặc sang ngang thì tham chiếu sẽ rơi ra ngoài trang tính:
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
Điều tương tự xảy ra khi một công thức sao chép sang trang tính hoặc workbook khác trỏ đến những ô không tồn tại ở đó. Hãy khóa các ô không được di chuyển bằng $ (=$B$2*2), hoặc sao chép nội dung công thức từ thanh công thức thay vì sao chép ô. Trang tham chiếu tuyệt đối giải thích dấu $.
Tìm và xóa mọi #REF! trong workbook
- Nhấn Ctrl+F (Cmd+F trên Mac), gõ
#REF!, mở Options, đặt Look in thành Formulas rồi bấm Find All. Danh sách hiện mọi công thức có tham chiếu bị hỏng. - Để sửa nhiều chỗ cùng lúc, dùng Ctrl+H (Control+H trên Mac): tìm
#REF!và thay bằng tham chiếu đúng, nhưng chỉ khi mọi kết quả tìm được đều cần cùng một ô. - Kiểm tra Formulas > Name Manager: một tên có cột Refers To hiện
#REF!sẽ làm hỏng mọi công thức dùng tên đó. - Nếu dữ liệu đã xóa không còn và công thức cũng không cần nữa, hãy chọn các ô rồi thay công thức bằng giá trị (Copy, rồi Home > Paste > Values). Giá trị lỗi vẫn là lỗi, nên sau đó hãy xóa các ô đó.
Sửa một phép tra cứu trả về #REF!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
Đến lượt bạn: =VLOOKUP(E2,A2:C6,4,FALSE) đã trả về #REF!. Hãy viết ở F2 một phép tra cứu chạy đúng, trả về số lượng tồn kho của sản phẩm ở E2.
Mọi phép tra cứu trả về 60 ở đây và thay đổi theo dữ liệu đều đạt: VLOOKUP với cột 3, =XLOOKUP(E2,A2:A6,C2:C6) hoặc =INDEX(C2:C6,MATCH(E2,A2:A6,0)).
Câu hỏi thường gặp
#REF! trong Excel nghĩa là gì?
Công thức trỏ đến một ô không tồn tại. Thường gặp nhất là một hàng, cột hoặc trang tính mà công thức dùng đã bị xóa, và Excel thay tham chiếu đó bằng #REF!, nên =B2*C2 thành =B2*#REF!. VLOOKUP và INDEX cũng trả về #REF! khi số cột hoặc số hàng lớn hơn vùng dữ liệu.
Làm sao để sửa #REF! sau khi xóa một cột?
Nhấn Ctrl+Z (Cmd+Z trên Mac) ngay để hoàn tác thao tác xóa. Nếu đã quá muộn, bấm vào công thức và thay #REF! bằng ô mà công thức cần dùng, rồi kéo công thức xuống lại.
Vì sao VLOOKUP trả về #REF!?
Số cột lớn hơn số cột trong vùng bảng. =VLOOKUP(E2,A2:C6,4,FALSE) yêu cầu cột thứ 4 của một vùng chỉ có 3 cột. Hãy dùng 3, hoặc mở rộng vùng thành A2:D6.
Làm sao để tìm mọi lỗi #REF! trong một workbook?
Nhấn Ctrl+F (Cmd+F trên Mac), tìm #REF!, đặt Look in thành Formulas rồi bấm Find All. Excel liệt kê mọi công thức chứa tham chiếu bị hỏng. Kiểm tra cả Formulas > Name Manager: các tên có thể trỏ đến #REF! sau khi xóa.
Làm sao để tránh #REF! khi xóa hàng hoặc cột?
Tham chiếu đến vùng thay vì từng ô riêng lẻ. =SUM(B2:D2) thu lại thành =SUM(B2:C2) khi cột C hoặc D bị xóa, còn =B2+C2+D2 thành =B2+#REF!+C2. Các hàm tra cứu chỉ rõ cột trả về, như =XLOOKUP(E2,A2:A6,C2:C6), vẫn đúng khi bạn chèn cột hoặc xóa những cột mà chúng không dùng.
