#REF! là một lỗi hay gặp trong Excel. Lỗi này khá nghiêm trọng và trong nhiều trường hợp việc sửa lỗi này khiến cho bạn mất nhiều thời kì và công sức. Vậy lỗi #REF là gì? Vì sao chúng ta lại bị lỗi #REF!? Cách khắc phục lỗi này như thế nào? Hãy cùng Học Excel Online tìm hiểu trong bài viết này.
1. Ý nghĩa của lỗi #REF!
REF là tên viết tắt của từ Reference, tức là Tham chiếu. Do đó trong mọi trường hợp xuất hiện lỗi này đều hiểu là lỗi tham chiếu. Một số nguyên nhân chủ yếu gây ra lỗi #REF!
- Tham chiếu tới Một file đang chưa được mở nên ko tham chiếu được.
- Đối tượng cần tham chiếu ko với 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
2. Cách khắc phục một số trường hợp cụ thể
a. Tham chiếu bị mất bởi bị xóa, bị ghi đè
Trong ví dụ trên chúng ta thấy cột C chứa dữ liệu năm 2017 đã bị xóa, dẫn tới kết quả ở cột Tổng bị lỗi #REF! (bị mất tham chiếu ở cột năm 2017) Việc này rất hay xảy ra bởi chúng ta hay với thói quen sử dụng thao tác Delete cả cột, cả hàng thay vì chỉ xóa dữ liệu trong cột, hàng đó. Ngoài ra việc cut dữ liệu rồi paste trong Excel cũng gây ra lỗi tham chiếu lúc chúng ta Paste đè vào Một vùng dữ liệu khác đang được tham chiếu tới. Ví dụ như sau:
Sau lúc thực hiện thao tác Cut và paste vào cột C thì xảy ra lỗi tham chiếu ở cột E
Cách khắc phục:
- Nếu lỡ tay vừa thực hiện xong hành động trên thì chúng ta với thể bấm phím Ctrl + Z để hoàn tác (trở lại lúc trước lúc thực hiện thao tác đó)
- Nếu phát hiện muộn, bạn với thể đóng file excel mà ko lưu. Sau đó mở lại file.
- Một lúc đã lưu file chứa lỗi tham chiếu thì ko thể sửa lại được nữa. Lúc đó chúng ta đành phải tự rà soát lại dữ liệu và sửa lại bằng tay.
b. Tham chiếu ko với sẵn, vượt quá phạm vi
Thường xảy ra lúc chúng ta tham chiếu tới những đối tượng là col_index_num, row_index_num trong một số công thức vượt quá phạm vi vùng cần tham chiếu. Ví dụ: Trong bảng trên chúng ta thấy vùng A2:E4 chỉ với 5 cột, trong lúc đó đối tượng Col_index_num trong hàm vlookup lại tính theo trị giá cột thứ 6, vượt quá phạm vi của vùng dữ liệu cần tham chiếu. Do đó kết quả trả về là lỗi #REF! Cách khắc phục:
- Sử dụng công thức tại đối tượng col_index_num hoặc row_index_num trong những công thức dò tìm, tham chiếu để tránh lỗi lúc nhập bằng tay (kết quả công thức sẽ tự thay đổi lúc bảng tham chiếu thay đổi). Ví dụ: Sử dụng hàm Match() để tiêu dùng cho row_index_num, tiêu dùng hàm column() để tiêu dùng cho col_index_num
- Sửa lại bằng tay nếu như bạn ko biết tiêu dùng những hàm ở trên
c. Tham chiếu tới Một file đang đóng Thường xảy ra lúc 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ở Workbook được tham chiếu hoặc thay đổi phương pháp tham chiếu (ko sử dụng hàm Indirect mà thay bằng hàm khác ít gặp lỗi hơn)
Tương tự là chúng ta đã với thể hiểu rõ về lỗi #REF! trong Excel rồi. Kỳ vọng là chúng ta sẽ tránh được việc xảy ra lỗi này cũng như nếu gặp phải lỗi này thì chúng ta cũng với thể biết cách sửa được nó. Ngoài ra Học Excel Online xin giới thiệu với những bạn “Khóa học Excel từ cơ bản tới tăng dành cho người đi làm“. Đây là Một khóa học rất đầy đủ tri thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng những phương tiện, những hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel. Chi tiết khóa học xem tại:
--- Cập nhật: 15-03-2023 --- nongdanmo.com tìm được thêm bài viết Lỗi #ref trong excel là gì ? Cách khắc phục lỗi và ví dụ minh họa từ website mega.com.vn cho từ khoá báo lỗi ref trong excel.
Nguyên nhân và cách khắc phục lỗi #REF! trong Excel
Lúc thao tác trên Excel, lỗi #REF! xảy ra khá phổ biến gây cản trở người tiêu dùng thực hiện những thuật toán. Thỉnh thoảng việc thực hiện sai cú pháp hoặc lỗi lúc thao tác với cột, hàng hoặc với những ô 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
Để với loạ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ị lúc công thức tham chiếu tới ô ko hợp thức. Lỗi ko thể tham chiếu trong excel thường xuyên xảy ra nhất lúc những ô 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 lúc xóa cột
Việc xóa cột hoặc xóa hàng sẽ khiến cho cho tham chiếu tới 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ề trị giá #REF! cho ô F2.
Cách khắc phục:
+ Bạn với 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 phương tiện.
+ Nên sử dụng tham chiếu dải ô thay vì chọn từng ô riêng lẻ. Bạn với thể thay hàm trên bằng =SUM(C2:E2). Lúc đó, nếu lúc bạn xóa một cột bất kỳ từ tham chiếu, Excel cũng sẽ tự động điều chỉnh công thức cho thích hợp.
Tham chiếu theo dải thay vì ô riêng lẻ
2. Lỗi #REF! do những ô tham chiếu ko với sẵn, vượt quá phạm vi
Lúc 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 với của bảng tham chiếu thì sẽ gây ra lỗi #REF!
Lỗi #REF! do những ô tham chiếu ko với 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 to 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 lúc bạn sử dụng hàm HLOOKUP.
3. INDEX với tham chiếu hàng hoặc cột ko chuẩn xác
Đối với hàm INDEX, nếu bạn cần trả về trị giá ở 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ề trị giá trong cột thứ 5 và hàng thứ 5 nhưng dải ô chỉ với 4 hàng và 4 cột.
INDEX với tham chiếu hàng hoặc cột ko chuẩn xác
Cách khắc phục:
Bạn cần điều chỉnh lại trị giá trả về ở hàng hoặc cột sao cho thuộc phạm vi dải tham chiếu thích hợp. Ví dụ: =INDEX(B2:E5;4;4) sẽ trả về kết quả hợp thức.
Trị giá 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
Lúc thao tác với hàm INDIRECT, lỗi #REF! sẽ xảy ra lỗi lúc:
+ ref_text ko phải là một tham chiếu ô hợp thức.
+ Lúc tham chiếu tới ô 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 tới đ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 tới 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:
+ Rà soát đối số ref_text của hàm INDIRECT.
+ Đảm bảo tham chiếu tới ô ko vượt quá giới hạn hàng hoặc cột với trước đó.
+ Mở bảng tính Excel được tham chiếu tới.
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 kỳ vọng bạn sẽ thao tác thuận lợi với những hàm trên trang tính Excel. Ngay cả bạn mới làm quen với Excel hoặc lúc bạn đã với kinh nghiệm sử dụng trang tính này, trong quá trình thực hiện vẫn với 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 kì cũng như thao tác trên Excel thuận tiện và tiện lợi hơn.
Đừng quên truy cập website mega.com.vn để cập nhật những bài viết mới nhất liên quan tới những hàm và lỗi thường gặp trong Excel bạn nhé!
Sửa lỗi #num trong excel đơn thuầ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 Hai 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
--- Cập nhật: 16-03-2023 --- nongdanmo.com tìm được thêm bài viết Cách sửa lỗi #REF! trong Excel chi tiết đơn giản nhất từ website www.dienmayxanh.com cho từ khoá báo lỗi ref trong excel.
Lỗi #REF! trong Excel xảy ra lúc công thức tham chiếu tới ô ko hợp thức. Điều này thường xuyên xảy ra nhất lúc những ô đượ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ư những giải pháp khắc phục hiệu quả.
1Lỗ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 cho tham chiếu tới 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 những cột B, C, hoặc D sẽ khiến cho cho trị giá 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ề trị giá #REF! cho ô E2.
Giải pháp:
- Nếu bạn vô tình xóa cột ko mong muốn, bạn với 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 phương tiện.
- Ư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 với thể thay hàm trên bằng =SUM(B2:D2). Lúc đó, kể cả lúc bạn xóa một cột bất kỳ từ tham chiếu, Excel cũng sẽ tự động điều chỉnh công thức cho thích hợp.
2VLOOKUP với tham chiếu dải ô ko chuẩn xác
Hàm VLOOKUP giúp tìm kiếm dữ liệu theo hàng dọc và trả về trị giá 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 với 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 trị giá trả về ở cột thứ 5, nhưng dải tham chiếu A:D chỉ với 4 cột. Trường hợp tương tự cũng xảy ra lúc bạn sử dụng hàm HLOOKUP.
Giải pháp:
- Điều chỉnh dải tham chiếu to 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)
3INDEX với tham chiếu hàng hoặc cột ko chuẩn xác
Tương tự, đối với hàm INDEX, nếu bạn cần trả về trị giá ở 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ề trị giá trong cột thứ 5 và hàng thứ 5 nhưng dải ô chỉ với 4 hàng và 4 cột.
Giải pháp: Điều chỉnh lại trị giá 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 thức.
4Tham 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 ko phải là một tham chiếu ô hợp thức.
- Tham chiếu tới ô 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 tới đang đóng. Nếu công thức INDIRECT của bạn tham chiếu tới 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:
- Rà soát đối số ref_text của hàm INDIRECT.
- Đảm bảo tham chiếu tới ô ko vượt quá giới hạn hàng hoặc cột.
- Mở bảng tính Excel được tham chiếu tới.
Mời bạn tham khảo phần mềm Microsoft đang kinh doanh tại Điện máy XANH
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.
--- Cập nhật: 18-03-2023 --- nongdanmo.com tìm được thêm bài viết 4 nguyên nhân dẫn đến lỗi #REF! trong Excel và cách xử lý nhanh chóng từ website cellphones.com.vn cho từ khoá báo lỗi ref trong excel.
Lỗi #REF! trong Excel là một trong những lỗi cơ bản nhất là người tiêu dùng hay gặp phải. Tuy nhiên cách khắc phục tình trạng này lại vô cùng đơn thuần. Theo dõi bài viết để tìm hiểu nhé!
- Lỗi #### trong excel khiến cho bạn khó chịu? Hãy tìm hiểu nguyên nhân và cách khắc phục ngay!
- Hướng dẫn nhanh cách giãn dòng trong Excel cực chuẩn
Lúc sử dụng phương tiện Excel việc xảy ra lỗi là điều ko thể tránh khỏi và lỗi #REF! là một lỗi phổ biến. Cho nên dù đã bị dính lỗi #ref! trong excel hay chưa thì bạn cũng nên tìm hiểu và ghi nhớ những cách sửa lỗi sau đây nhé.
Lỗi #REF! trong Excel là gì?
Lỗi #REF! trong Excel được hiểu là lỗi tham chiếu lúc bạn tham chiếu tới những ô dữ liệu ko hợp thức. Những lỗi này xảy ra thường là bạn đã tham chiếu tới những ô bị xóa hoặc vượt quá trị giá cho phép.
Những lỗi #REF! trong Excel và cách khắc phục
Với 4 nguyên nhân chính dẫn tới lỗi #REF! đó là:
- Lỗi do cột tham chiếu bị xóa.
- Lỗi do những ô tham chiếu vượt quá trị giá cho phép.
- Lỗi từ việc sao chép từ bảng dữ liệu này sang bảng dữ liệu khác.
- Lỗi do tham chiếu tới hàm đóng INDIRECT.
Lỗi #REF! do cột tham chiếu bị xóa
Nếu như bạn vô tình xóa nhầm cột mà bạn đang tham chiếu tới thì lỗi #REF! sẽ hiển thị. Để khắc phục, bạn chỉ cần thêm lại cột và những trị giá trong cột là với thể sửa lỗi.
Bước 1: Trong bảng dữ liệu sau, thử xóa cột D. Trên bảng ngay tức thì hiển thị lỗi #REF!.
Bước 2: Sửa lỗi bằng cách ấn Ctrl + Z để quay lại hoặc thêm cột cũ bằng cách thủ công.
Lỗi do những ô tham chiếu vượt quá trị giá cho phép
Lúc bạn tham chiếu với số lượng ô to hơn số lượng ô đã khai báo thì hàm sẽ bị lỗi #REF!. Để sửa bạn chỉ cần xóa bớt trị giá và tham chiếu tới đúng số ô với trong bảng.
Bước 1: Vẫn bảng dữ liệu trên, ta sử dụng hàm VLOOKUP tham chiếu tới 5 cột. Tuy nhiên trong bảng dữ liệu chỉ với 4 cột, do vậy hàm hiện ra lỗi #REF!.
Bước 2: Sửa số cột từ 5 thành 4 là hàm sẽ trả về kết quả đúng.
Lỗi từ việc sao chép từ bảng dữ liệu này sang bảng dữ liệu khác
Thỉnh thoảng việc sao chép dữ liệu từ file Excel này sang file khác cũng dẫn tới việc lỗi #REF! trong Excel. Với nguyên nhân này, bạn chỉ cần so sánh file vừa sao chép với file gốc và chỉnh lại dữ liệu sao cho khớp là xong.
Bước 1: Thực hiện sao chép bảng dữ liệu sang file Excel mới.
Bước 2: Trên màn hình hiện thử lỗi #REF!, ta so sánh hai file với nhau rồi chỉnh lại dữ liệu. Kết quả đúng sẽ hiển thị trên bảng Excel.
Lỗi do tham chiếu tới hàm đóng INDIRECT
Trong Excel hàm INDIRECT là hàm đóng và những dữ liệu bên ngoài hàm này ko thể tham chiếu vào bên trong hàm được. Do đó, muốn tham chiếu tới hàm này bạn cần mở lại hàm và thực hiện tham chiếu lại.
Bước 1: Nhập bảng dữ liệu và tham chiếu tới những ô như hình.
Bước 2: Màn hình hiển thị lỗi #REF!, bạn cần mở lại hàm INDIRECT và thực hiện tham chiếu lại.
Một số lỗi cú pháp thường gặp trong excel
Ngoài lỗi #REF! ra thì bạn cũng cần lưu ý một số lỗi sau đây để tránh bị mắc phải lúc sử dụng Excel:
- Lỗi #N/A: Lỗi ko tìm thấy kết quả hoặc thông tin một kết quả ko với sẵn.
- Lỗi #VALUE!: Lỗi định dạng dữ liệu sai.
- Lỗi #NAME?: Lỗi viết sai tên hàm.
- Lỗi #NUM!: Lỗi hàm chứa trị giá số ko hợp thức.
- Lỗi #DIV/0!: Lỗi chia cho 0.
Tạm kết sửa lỗi #ref! trong excel
Trên đây, Sforum đã san sẻ cho độc giả những lưu ý về lỗi #REF! trong Excel. Kỳ vọng với bài viết này, độc giả sẽ ko còn băn khoăn mỗi lúc gặp lỗi này nữa. Chúc bạn thành công!
- Xem thêm những bài viết: Thủ thuật Windows, Thủ thuật Excel cơ bản.
