Gửi Email
Liên hệ Zalo
Nội dung bài viết
Lỗi #REF trong Excel có thể xảy ra do nhiều nguyên nhân khác nhau, gây tác động rất lớn đến cho người dùng và làm giảm đi hiệu quả trong quá trình sử dụng phần mềm Microsoft Excel. Vậy lỗi #REF trong Excel là gì? Lỗi #REF trong Excel có thể xảy ra do nguyên nhân nào? Và các cách sửa lỗi #REF trong Excel nhanh chóng và hiệu quả nhất là gì? Hãy cùng với MSO giải đáp các câu hỏi này ngay trong nội dung bài viết dưới đây nhé.
Lỗi #REF trong Excel là gì?
Lỗi #REF trong Excel là một lỗi tham chiếu, thường xảy ra khi sử dụng một công thức tham chiếu đến một ô hoặc vùng tham chiếu không được tồn tại nữa. Điều này thường xảy ra khi bạn bạn xóa một ô hoặc phạm vi ô mà công thức đang sử dụng, hay khi tham chiếu trong công thức đến một bảng hoặc dải ô đã bị di chuyển. Hoặc cũng có thể là do đã xảy ra lỗi trong liên kết giữa các tệp khi mà tệp tham chiếu bị di chuyển hoặc bị đổi tên.
Để khắc phục được lỗi tham chiếu này, bạn hãy kiểm tra lại công thức của mình xem có xảy ra những điều này chúng tôi đã đề cập phía trên. Hãy tiếp tục theo dõi những phần nội dung tiếp theo trong bài viết này để nắm rõ nguyên nhân cụ thể, cũng như là biết được cách sửa lỗi #REF trong Excel chi tiết để đảm bảo khắc phục lỗi một cách nhanh chóng và hiệu quả nhất nhé.
Nguyên nhân và cách sửa lỗi #REF trong Excel hiệu quả nhất
Dưới đây là thông tin chi tiết về những nguyên nhân thường xảy ra dẫn đến xuất hiện lỗi #REF trong quá trình mà người dùng sử dụng phần mềm bảng tính Microsoft Excel, đồng thời là hướng dẫn cách khắc phục chi tiết:
Sửa lỗi #REF trong Excel do dữ liệu tham chiếu bị mất hoặc ghi đè
Nguyên nhân: Lỗi #REF trong Excel có thể xảy ra khi người dùng thực hiện xóa ô, cột hoặc hàng tham chiếu. Khi bạn xóa bất kỳ ô, cột, hàng hãy dải ô nào công thức đang tham chiếu đến, hệ thống phần mềm Microsoft Excel sẽ không thể tìm thấy dữ liệu tham chiếu cần thiết để tính toán và sẽ trả về kết quả hiển thị lỗi #REF!.
Ví dụ: Ô B9 chứa công thức =VLOOKUP(A3,A1:E6,5) đang tham chiếu đến bảng dữ liệu A1:E6 để trả về kết quả điểm xếp hạng của sinh viên Lý Minh Long.
Tuy nhiên, cột tham chiếu E bị mất dữ liệu, nên công thức trên đã trả về kết quả là lỗi #REF! tại ô B9. Bạn có thể thấy được như trong hình sau đây:
Cách khắc phục:
Để khắc phục lỗi REF trong Excel do dữ liệu tham chiếu bị mất như ví dụ trên đây, bạn hãy thực hiện như sau:
Bước 1: Đầu tiên, bạn hãy kiểm tra xem hàm tính toán đang xảy ra lỗi #REF! đang bị bị mất dữ liệu hay bị ghi đè dữ liệu ở ô tham chiếu nào.
Như trong ví dụ này, bảng tính đang bị mất dữ liệu ở cột thứ 5 – cột E (cột chứa dữ liệu “Vị trí xếp hạng”), nên dẫn đến việc công thức hàm VLOOKUP phía trên không thể trả về kết quả chính xác mà lại trả về kết quả lỗi #REF!.
Bước 2: Để khắc phục lỗi này, bạn chỉ cần bổ sung các dữ liệu vào vị trí cột đã bị mất (Cột E) trong bảng tính Excel của mình để hàm tham chiếu được chính xác.
Lưu ý: Bạn có thể bổ sung dữ liệu bị thiết bằng cách sử dụng tổ hợp phím Ctrl + Z trong trường hợp bạn mới vừa làm mất trong thao tác trước đó hoặc bạn cũng có thể thêm những dữ liệu đó theo cách thủ công.
Ngay sau khi bạn đã thêm dữ liệu vào vùng tham chiếu, ngay lập tức hàm tính toán đó sẽ trả về kết quả cụ thể mà không phải là lỗi #REF!.
Sửa lỗi #REF trong Excel do tham chiếu không có sẵn/vượt quá phạm vi
Nguyên nhân: Khi công thức tham chiếu đến một ô hoặc vùng dữ liệu vượt quá phạm vi hoặc nằm ngoài phạm vi hiện có, hệ thống phần mềm Excel cũng sẽ trả về kết quả là lỗi tham chiếu #REF!.
Ví dụ: Bạn đang cần trả về kết quả Điểm thi đua của sinh viên Lý Minh Long bằng cách sử dụng hàm VLOOKUP để tham chiếu đến vị trí ô D3 (tức vị trí cột số 4 trong bảng dữ liệu).
Tuy nhiên, trong công thức bạn lại tham chiếu đến vị trí cột số 6 của bảng tính và vượt ra ngoài phạm vị bảng dữ liệu tham chiếu, nên kết quả của công thức này đã tải về lỗi #REF!.
Như hình minh hoạt dưới đây:
Cách khắc phục:
Để sửa lỗi #REF! trong Excel đối với trường hợp này, bạn chỉ cần kiểm tra vị trí tham chiếu và chỉnh sửa lại đúng với tham chiếu cần tìm kiếm trong hàm tính toán của mình là được. Cách thức thực hiện chi tiết để khắc phục, bạn hãy làm theo những bước như sau:
– Bước 1: Bạn hãy kiểm tra lại vị trí tham chiếu của hàm.
Để tìm được điểm thi đua của sinh viên Lý Minh Long, bạn cần phải sử dụng hàm VLOOKUP với cột tham chiếu là cột điểm thi đua (tức là cột thứ 4). Tuy nhiên, hàm đang được sử dụng lại tham chiếu đến cột F (cột thứ 6) nên kết quả đang trả về là lỗi #REF! .
Công thức đang sai: =VLOOKUP(A3,A1:E6,4)
– Bước 2: Để khắc phục lỗi này, bạn chỉ cần sửa lại vị trí cột tham chiếu theo đúng với cột Điểm thi đua (tức cột thứ 4) là xong.
Ở đây, công thức được sửa lại sẽ là: =VLOOKUP(A3,A1:E6,4)
Sau đó, kết quả sẽ trả về đúng số điểm thi đua của sinh viên Lý Minh Long là 9.
Sửa lỗi #REF trong Excel do di chuyển dữ liệu
Nguyên nhân: Khi bạn sao chép hoặc di chuyển dữ liệu từ một bảng tính này sang bảng tính khác mà không cập nhật ô hoặc vùng tham chiếu, công thức đó cũng sẽ được hệ thống phần mềm Microsoft Excel sẽ không thể nhận diện được tham chiếu và trả về lỗi tham chiếu #REF!.
Ví dụ: Bạn đang muốn tham chiếu đến bảng tính có tên là “Trang_tính4” để lấy dữ liệu Mã sinh viên của sinh viên “Trần Nguyễn Minh Anh”. Trong đó, Mã sinh viên của sinh viên Trần Nguyễn Minh Anh hiện đang ở ô A3 của bảng tính “Trang_tính4”.
Lúc này, công thức cần phải sử dụng sẽ là: =Trang_tính4!A3
Tuy nhiên, sau đó bạn đã di chuyển bảng tính “Trang_tính4” và đổi tên thành “Trang_tính6” tại Workbook của mình. Và công thức mà bạn đang sử dụng phía trên sẽ trả về kết quả là lỗi #REF!.
Cách khắc phục:
Để khắc phục lỗi trong trường hợp này, bạn hãy làm như sau:
– Bước 1: Đầu tiên, bạn cần kiểm tra lại bảng tính mà công thức của mình được tham chiếu đến hiện tại đang được đổi tên thành gì.
Trong ví dụ này, bảng tính đã được đổi tên thành “Trang_tính6”.
– Bước 2: Lúc này, bạn chỉ cần sửa lại bảng tính được tham chiếu trong công thức của mình thành tên bảng tính đó hiện tại.
Trong ví dụ này, công thức được viết lại như sau:
=Trang_tính6!A3
Và kết quả sau khi công thức được viết lại sẽ cho ra một kết quả tham chiếu chính xác chứ không phải là lỗi #REF!.
Sửa lỗi #REF trong Excel do liên kết đến tệp bên ngoài
Nguyên nhân: Lỗi trong liên kết giữa các tệp Excel cũng sẽ xảy ra khi công thức của bạn đang tham chiếu đến một tệp Excel bên ngoài, nhưng tệp Excel đó đã bị đổi tên hoặc di chuyển hay đã bị xóa. Hệ thống phần mềm Microsoft Excel sẽ không thể nhận diện được công thức của bạn và sẽ trả về kết quả lỗi tham chiếu #REF!.
Ví dụ: Bạn có công thức ='[File.xlsx]Sheet1′!A1 trong bảng tính hiện tại, và tệp tên là “File.xlsx” đã bị đổi tên hoặc không còn tồn tại. Lúc này, phần mềm Microsoft Excel không thể tìm kiếm được tệp dữ liệu đó và sẽ trả về lỗi #REF!.
Cách khắc phục: Bạn chỉ cần kiểm tra và sửa lại tên của tệp mà công thức của bạn đang tham chiếu đến thành tên của tệp đó hiện tại.
Cách sửa lỗi #REF trong Excel do tham chiếu tới hàm INDIRECT
Nguyên nhân: Trong phần mềm Microsoft Excel, hàm INDIRECT được gọi là hàm đóng bởi vì nếu người dùng sử dụng tham chiếu đến các dữ liệu bên ngoài thì hàm INDIRECT sẽ không thể tham chiếu vào được và trả về kết quả lỗi #REF!.
Cách khắc phục: Vì thế, bắt buộc bạn cần phải mở lại bảng tính được tham chiếu đến để hàm này có thể hoạt động và thực hiện tham chiếu lại để có thể thực hiện tham chiếu đến các dữ liệu đó bằng hàm này.
Sửa lỗi #REF trong Excel do hàm INDIRECT không hợp lệ
Nguyên nhân: Một trong những nguyên nhân khác nữ ít a có thể dẫn đến lỗi #REF trong Excel đó chính là khi xảy ra lỗi hàm INDIRECT khi bạn đang sử dụng hàm này. Hàm INDIRECT thường yêu cầu tham chiếu được sử dụng trong công thức phải chính xác, nếu tham chiếu được sử dụng trong công thức hàm INDIRECT bị xóa hoặc sai cú pháp, hệ thống phần mềm Excel cũng sẽ trả về lỗi #REF!.
Ví dụ: Bảng một file Excel, bạn có bảng tính Sheet1 như sau:
Tại ô A1 của bảng tính Sheet5, bạn đang sử dụng hàm INDIRECT để tham chiếu đến ô B6 tại bảng tính Sheet1, với công thức như sau:
=INDIRECT(“Sheet1!B6”)
Tuy nhiên, sau đó, bạn đã xóa đi bảng tính Sheet1, kết quả của công thức hàm INDIRECT tại ô A1 của Sheet5 sẽ trả về là lỗi #REF! như hình dưới đây:
Cách khắc phục: Với lỗi trên đây, bạn hãy kiểm tra là công thức hàm INDIRECT của bạn đang tham chiếu đến phần dữ liệu nào. Sau đó, bạn chỉ cần thêm lại phần dữ liệu bị mất là có thể sửa được lỗi này rồi đó.
Bên cạnh đó, bạn hãy sử dụng theo các phương pháp tham chiếu khác để nhận được kết quả mong muốn mà không phải là lỗi #REF!.
Sửa lỗi #REF trong Excel do Copy/Paste
Nguyên nhân: Việc sao chép và dán không đúng cách cũng sẽ khiến cho bạn gặp phải lỗi #REF!. Ví dụ như khi bạn sao chép hoặc cắt dữ liệu từ một dải ô có công thức chứa tham chiếu, sau đó dán nó vào một nơi khác, tham chiếu có thể bị mất và công thức tham chiếu đó sẽ trả về kết quả #REF!.
Ví dụ: Cho bảng dữ liệu đánh giá thi đua của lớp học như sau và đang cần tính Tổng điểm thi đua của cả lớp.
Lúc này, công thức được sử dụng sẽ là: =SUM(D2:D6)
Tuy nhiên, sau đó bạn đã sao chép ô công thức trên và dán vào một file dữ liệu khác, khi đó ô được dán sẽ hiển thị và trả về lỗi #REF!.
Cách khắc phục: Việc khắc phục và sửa lỗi REF trong Excel do copy dữ liệu không chính xác, bạn chỉ cần kiểm tra để đối chiếu xem dữ liệu được sao chép và dán mà bạn vừa thực hiện có được trùng khớp với các dữ liệu của file dữ liệu gốc hay không. Nếu không, bạn hãy chỉnh sửa lại theo đúng như với dữ liệu gốc là có thể khắc phục được lỗi tham chiếu REF ngay lập tức.
Trong ví dụ này, mình đã chỉnh lại công thức và dữ liệu theo đúng như ô được sao chép nên Excel sẽ hiển thị kết quả như hình minh họa.
Sửa lỗi #REF trong Excel do tham chiếu đến vùng không hợp lệ
Nguyên nhân: Khi công thức của bạn trong bảng tính Excel chứa tham chiếu đến một ô hoặc một vùng tham chiếu không hợp lệ, chẳng hạn như một bảng đã bị xóa hoặc không còn tồn tại nữa. Hệ thống phần mềm Excel cũng sẽ trả về cho bạn kết quả là lỗi tham chiếu #REF!.
Cách khắc phục: Việc sửa lỗi tham chiếu #REF! trong Excel do tham chiếu hàng hoặc cột bị sai cũng được thực hiện tương tự như đối với cách sửa lỗi REF trong Excel do ô tham chiếu vượt quá phạm vi tham chiếu hoặc phạm vi tham chiếu không có sẵn. Bạn hãy tham khảo hướng dẫn khắc phục lỗi mà chúng tôi đã cung cấp ở trong phần đó để có thể khắc phục được lỗi này.
Trên đây là những nguyên nhân cơ bản dẫn đến việc kết quả công thức hoặc hàm của bạn trả về lỗi tham chiếu #REF! và dựa theo từng nguyên nhân dẫn đến xảy ra lỗi #REF! trong Excel đó, chúng tôi cũng đã cung cấp hướng dẫn cách thức khắc phục chi tiết. Qua đó giúp bạn có thể có thể sửa lỗi #REF trong Excel một cách nhanh chóng và hiệu quả nhất.
Bạn có thể tham khảo theo những thao tác được chúng tôi hướng dẫn cách sửa lỗi #REF! trong Excel phía trên đây để có thể sửa được lỗi và nhận về kết quả như mong muốn. Hãy tiếp tục theo dõi bài viết này của chúng tôi để biết được thêm thông tin về những loại hàm thường gặp lỗi #REF trong Excel nhất khi sử dụng nhé.
Những loại hàm thường gặp lỗi #REF! trong Excel
Lỗi #REF! trong Excel có thể xảy ra khi sử dụng nhiều loại hàm khác nhau, đặc biệt là những hàm Excel hoạt động phụ thuộc vào tham chiếu ô hoặc dải ô. Bằng cách nắm rõ được những loại hàm thường gặp lỗi #REF trong Excel nhất cũng sẽ là một trong những cơ sở để giúp bạn đảm bảo cho mình giảm thiểu tối đa tình trạng gặp phải lỗi tham chiếu này trong suốt quá trình sử dụng phần mềm Excel để phân tích, xử lý dữ liệu. Dưới đây là một số loại hàm Excel thường xảy ra lỗi này nhất mà bạn cần nắm rõ khi sử dụng:
- Hàm tra cứu và tham chiếu: Các hàm tra cứu và tham chiếu như VLOOKUP, HLOOKUP, INDEX và MATCH là những hàm thường gặp phải lỗi #REF! khi sử dụng nhất. Bởi nếu không may vùng dữ liệu tham chiếu bị xóa hoặc bị sai sót trong công thức, các hàm này sẽ trả về cho người dùng kết quả là lỗi #REF!.
- Hàm tính toán và phân tích: Những hàm tính toàn như SUM, AVERAGE, COUNT, MIN và MAX cũng là những hàm thường hay xảy ra lỗi tham chiếu, đặc biệt là trong trường hợp nếu các ô được sử dụng trong những hàm này bị xóa hoặc công thức không chính xác, chúng cũng sẽ trả về cho người dùng kết quả là lỗi tham chiếu ref.
- Hàm liên kết giữa các bảng tính hoặc tệp: Những hàm liên kết như IMPORTRANGE và các công thức liên kết đến những tệp khác bên ngoài tệp Excel hiện tại mà bạn đang sử dụng cũng là những hàm và công thức thường gặp lỗi tham chiếu #REF nhất khi sử dụng. Lỗi này đặc biệt xảy ra khi mà nguồn dữ liệu mà hàm công thức này liên kết bị xóa hoặc bị thay đổi.
- Hàm tham chiếu gián tiếp: Những hàm tham chiếu gián tiếp như INDIRECT là hàm thường xảy ra lỗi REF nhất khi sử dụng, đặc biệt xảy ra trong trường hợp khi mà ô hoặc vùng dữ liệu được hàm này tham chiếu đến không tồn tại hoặc bị xóa.
Việc kiểm tra cẩn thận các tham chiếu được sử dụng trong các hàm nêu trên và đảm bảo các dữ liệu mà những hàm này tham chiếu đến không bị thay đổi đột ngột sẽ là một cách phòng tránh hiệu quả nhất lỗi REF trong Excel.
Để khắc phục lỗi tham chiếu này trong Excel, bạn hãy kiểm tra lại công thức chứa lỗi #REF! và xác định xem ô hoặc phạm vi nào bị xóa hoặc di chuyển. Nếu bạn lỡ xóa ô, bạn hãy kiểm tra lịch sử hoặc sử dụng phím tắt Ctrl + C để hoàn tác lại. Đối với những liên kết giữa các tệp, bạn hãy cập nhật đường dẫn liên kết thường xuyên để đảm bảo liên kết đó luôn đúng.
Hãy tiếp tục theo dõi bài viết này cả chúng tôi để biết được cách tìm lỗi REF trong Excel nhanh chóng nhất để khắc phục được lỗi này một cách kịp thời và hiệu quả nhất nhé.
Cách tìm lỗi REF trong Excel nhanh nhất
Bên cạnh việc nắm rõ những thao tác để sửa lỗi REF trong Excel, bạn cũng cần phải đảm bảo rằng toàn bộ những lỗi này đều được nhận diện để giúp cho bảng tính Excel của mình không còn xuất hiện bất cứ lỗi tham chiếu REF nào khác. Để tìm lỗi REF trong Excel một cách nhanh chóng nhất, bạn có thể tham khảo và thử thực hiện theo các phương pháp được chúng tôi đề cập phía dưới đây:
- Sử dụng tính năng “Kiểm tra lỗi”: Trên thanh công cụ, chọn tab Công thức (Formulas) >> Điều hướng đến nhóm Kiểm tra công thức (Formula Auditing) >> Nhấp chọn Kiểm tra lỗi (Error Checking). Ngay sau đó, Excel sẽ tự động quét toàn bộ trang tính hiện tại của bạn rồi liệt kê các ô chứa lỗi (bao gồm cả lỗi #REF!) và cung cấp hướng dẫn để giúp bạn khắc phục.
- Sử dụng chức năng Tìm kiếm (Find): Nhấn phím Ctrl + F để mở hộp thoại Tìm kiếm >> Nhập từ khóa “#REF!” vào hộp tìm kiếm >> Nhấn chọn nút Tìm tiếp (Find Next). Ngay sau đó, Excel sẽ điều hướng để dẫn bạn đến từng ô tính có chứa lỗi này, từ đó giúp bạn dễ dàng xác định và sửa lỗi.
- Kiểm tra ô và công thức bị ảnh hưởng: Để xem qua toàn bộ các ô tính có hiển thị lỗi #REF! trên trang tính hiện tại của mình, bạn hãy mở thanh công thức Show Formulas để xem chính xác công thức của ô đang gặp lỗi, từ đó xác định tham chiếu nào bị xóa hoặc di chuyển.
- Xem lịch sử chỉnh sửa (Track Changes): Hãy bật tính năng Show Changes để theo dõi được những thay đổi được thực hiện trên trang tính của mình, điều này sẽ giúp bạn nắm được những thay đổi nào dẫn đến lỗi. Để mở tính năng Show Changes, bạn hãy vào tab Review >> nhấn chọn mục Show Changes và xem chế độ theo dõi lịch sử chỉnh sửa.
- Sử dụng chức năng “Trace Precedents/Dependents)”: Chức năng này sẽ hiển thị các ô liên quan đến công thức và giúp bạn có thể truy tìm công thức để dễ dàng tìm các tham chiếu bị lỗi. Để mở tính năng này, bạn hãy chọn ô chứa lỗi #REF! >> đi đến tab Formulas (Công thức) >> sử dụng công cụ Trace Precedents hoặc Trace Dependents để tìm các tham chiếu bị lỗi.
Bằng cách kết hợp các phương pháp này, bạn sẽ có thể nhanh chóng xác định được lỗi REF! và có thể bắt đầu các cách sửa lỗi #REF! trong Excel để đảm bảo khắc phục lỗi này một cách nhanh chóng và hiệu quả hơn.
Một số lỗi cú pháp thường gặp khác trong Excel
Bên cạnh lỗi #REF!, thì trong quá trình sử dụng phần mềm Microsoft Excel để phân tích và xử lý dữ liệu, bạn sẽ còn có thể bắt gặp một số lỗi cú pháp khác. Một số lỗi cú pháp thường gặp khác trong phần mềm Microsoft Excel bao gồm như:
- Lỗi #N/A: Lỗi này thường xuất hiện khi công thức mà bạn nhập vào bảng tính không tìm thấy kết quả hoặc thông báo một kết quả không có sẵn.
- Lỗi #VALUE!: Lỗi này thường xuất hiện khi định dạng dữ liệu mà bạn nhập vào bảng tính không chính xác hoặc bị sai.
- Lỗi #NAME?: Lỗi này thường xuất hiện khi công thức mà bạn nhập vào bảng tính đang bị viết sai tên hàm.
- Lỗi #NUM!: Lỗi này thường xuất hiện khi công thức mà bạn đang nhập vào bảng tính chứa các giá trị kiểu số không hợp lệ.
- Lỗi #DIV/0!: Lỗi này thường xuất hiện khi công thức mà bạn đang nhập vào bảng tính có chứa dữ liệu chia cho 0.
Lời kết
Trong bài viết chi tiết phía trên, MSO đã cung cấp cho bạn những thông tin quan trọng có liên quan đến lỗi cú pháp #REF!, bao gồm các nguyên nhân thường gặp dẫn đến lỗi này và các cách khắc phục để giúp bạn sửa lỗi #REF trong Excel một cách hiệu quả nhất. Bên cạnh đó là những thông tin hướng dẫn khác để giúp bạn có thể tìm kiếm đến lỗi này một cách nhanh chóng nhất.
Nếu bạn có bất cứ câu hỏi gì hoặc là những thắc mắc liên quan đến phần mềm Microsoft Excel, bạn có thể liên hệ ngay qua các thông tin dưới đây để được MSO hướng dẫn và giải đáp kịp thời.
———————————————————
Fanpage: MSO.vn – Microsoft 365 Việt Nam
Hotline: 024.9999.7777
